家庭记账本（八）

3.持久化Dao层

1 package com.wang.dao;
  2 
  3 import java.math.BigDecimal;
  4 import java.sql.Connection;
  5 import java.sql.ResultSet;
  6 import java.sql.SQLException;
  7 import java.sql.Statement;
  8 import java.util.ArrayList;
  9 import java.util.List;
 10 
 11 import com.wang.pojo.HomeCost;
 12 import com.wang.utils.DBUtils;
 13 
 14 public class HomeCostDao {
 15     
 16     /**
 17      * updatesql()用来执行 insert/update/delete语句
 18      * @param sql 具体的sql语句
 19      * @return 返回-1，说明执行失败；否则为影响数据条数
 20      */
 21     public int updatesql(String sql) {
 22         Connection conn = DBUtils.getConn();//获取连接对象
 23         Statement state = null;
 24         try {
 25             state = conn.createStatement();
 26             return state.executeUpdate(sql);
 27         } catch (Exception e) {
 28             e.printStackTrace();
 29         } finally {
 30             DBUtils.close(state, conn);
 31         }
 32         return -1;       
 33     }
 34     
 35     
 36     //添加
 37     public int add(HomeCost homecost) {
 38         //insert语句，形如insert into 表名(字段1，字段2) values('值1','值2');
 39         String sql = "insert into home(name,money) values('"+ homecost.getName() 
 40             + "','" + homecost.getMoney() +  "')";
 41         return updatesql(sql);
 42     }
 43     
 44     //删除
 45     public int delete (int id) {
 46         //delete语句，形如delete from 表名 where id='值';
 47         String sql = "delete from home where id='" + id + "'";
 48         return updatesql(sql);
 49     }
 50      
 51     //修改
 52     public int update(HomeCost homecost) {
 53         //update语句，形如update 表名 set 字段1 = '值1',字段2 = '值2'where id = '值3';
 54         String sql = "update home set name='" + homecost.getName() + "', money='" 
 55                 + homecost.getMoney()+ "' where id='" + homecost.getId() + "'";
 56         return updatesql(sql);
 57         
 58     }
 59     
 60     //查询
 61     public List<HomeCost> query(String keyword) {
 62         String sql = "select * from home WHERE name LIKE '%"+keyword+"%' OR money LIKE '%"+keyword
 63                 +"%'OR date LIKE '%"+keyword+ "%'";
 64         List<HomeCost> list = new ArrayList<>();
 65         Connection conn = DBUtils.getConn();
 66         Statement state = null;
 67         ResultSet rs = null;
 68         try {
 69             state = conn.createStatement();
 70             rs = state.executeQuery(sql);
 71             while (rs.next()) {
 72                 int id = rs.getInt("id");//获取查询结果中的id
 73                 String name = rs.getString("name");//获取查询结果中的name
 74                 BigDecimal money = rs.getBigDecimal("money");//获取查询结果中的money
 75                 String date = rs.getString("date");//获取查询结果中的date
 76                 HomeCost homeCost = new HomeCost(id,name,money,date);//调用构造方法赋值
 77                 list.add(homeCost);//添加到list集合中
 78             }
 79         } catch (SQLException e) {
 80             e.printStackTrace();
 81         } finally {
 82             DBUtils.close(rs, state, conn);
 83         }
 84         return list;
 85     }
 86     
 87     //通过id找到某条信息
 88     public HomeCost getHomeCostById(int id) {
 89         String sql = "select * from home where id ='" + id + "'";
 90         Connection conn = DBUtils.getConn();
 91         Statement state = null;
 92         ResultSet rs = null;
 93         HomeCost homeCost = null;
 94         try {
 95             state = conn.createStatement();
 96             rs = state.executeQuery(sql);
 97             while (rs.next()) {
 98                 String name = rs.getString("name");
 99                 BigDecimal money = rs.getBigDecimal("money");
100                 String date = rs.getString("date");
101                 homeCost = new HomeCost(id,name,money,date);         
102             }
103         } catch (Exception e) {
104             e.printStackTrace();
105         } finally {
106             DBUtils.close(rs, state, conn);
107         }
108         return homeCost;
109     }
110     
111     /**
112      * 通过id计算该条消费记录累计消费金额
113      * @return BigDecimal类型money
114      */
115     public BigDecimal queryMoneySum(int id) {
116         String sql = "select money from home where id <="+id;
117         BigDecimal sum = new BigDecimal("0.00");
118         Connection conn = DBUtils.getConn();
119         Statement state = null;
120         ResultSet rs = null;
121         try {
122             state = conn.createStatement();
123             rs = state.executeQuery(sql);
124             while (rs.next()) {
125                 BigDecimal money = rs.getBigDecimal("money");
126                 //sum是money累加值
127                 sum = sum.add(money);
128             }
129         } catch (SQLException e) {
130             e.printStackTrace();
131         } finally {
132             DBUtils.close(rs, state, conn);
133         }
134         return sum;
135     }
136          
137     //获取全部数据  
138     public List<HomeCost> list() {
139         String sql = "select * from home";
140         List<HomeCost> list = new ArrayList<>();
141         Connection conn = DBUtils.getConn();
142         Statement state = null;
143         ResultSet rs = null;
144         try {
145             state = conn.createStatement();
146             rs = state.executeQuery(sql);
147             HomeCost homeCost = null;
148             while (rs.next()) {
149                 int id = rs.getInt("id");
150                 BigDecimal sum = queryMoneySum(id);
151                 String name = rs.getString("name");
152                 BigDecimal money = rs.getBigDecimal("money");
153                 String date = rs.getString("date");
154                 homeCost = new HomeCost(id,name,money,date,sum);
155                 list.add(homeCost);
156             }
157         } catch (SQLException e) {
158             e.printStackTrace();
159         } finally {
160             DBUtils.close(rs, state, conn);
161         }
162         return list;
163     }
164     
165     
166 }

HomeCostDao.java