【DRP】-Dao层常用功能代码：增删改查

本系列博客内容为：做DRP系统中Dao层常用功能。
该项目采用MVC架构
C（Controller）控制器，主要职责;1、取得表单参数；2、调用业务逻辑；3、转向页面
M（Model）模型，主要职责：1、业务逻辑；2、保存数据的状态
V（View）视图，主要职责：显示
本文主要是针对于Dao层的常见使用方法：增删改查sql语句及常用操作。
  1 package com.bjpowernode.drp.basedata.dao;
  2 
  3 import java.sql.Connection;
  4 import java.sql.PreparedStatement;
  5 import java.sql.ResultSet;
  6 import java.sql.SQLException;
  7 import java.util.ArrayList;
  8 import java.util.List;
  9 
 10 import com.bjpowernode.drp.basedata.domain.Item;
 11 import com.bjpowernode.drp.util.ApplicationException;
 12 import com.bjpowernode.drp.util.DbUtil;
 13 import com.bjpowernode.drp.util.PageModel;
 14 import com.bjpowernode.drp.util.datadict.domain.ItemCategory;
 15 import com.bjpowernode.drp.util.datadict.domain.ItemUnit;
 16 
 17 public class ItemDao4OracleImpl implements ItemDao {
 18     //增加物料信息
 19     public void addItem(Connection conn, Item item) {
 20         String sql = "insert into t_items (item_no, item_name, spec, pattern, item_category_id, item_unit_id) ";
 21             sql+=" values (?, ?, ?, ?, ?, ?)";
 22         PreparedStatement pstmt = null;
 23         try {
 24             //Dao的设计粒度一般是细粒度的，如果没有特殊需求，Dao和Manager粒度可以一样，不同考虑太多
 25             //Dao的设计是比较单纯的，不应该放入过多的业务逻辑（业务规则）
 26             //如果放置了业务逻辑，有些Manager不采用此业务逻辑，这样这个Dao方法就没有复用率了
 27             //对于我们的应用来说Dao最底层的，所以应该越通用越好
 28 //            if (findItemById(conn, item.getItemNo()) != null) {
 29 //                throw new ApplicationException("物料代码已经存在，代码=" + item.getItemNo()  + "");
 30 //            }
 31             pstmt = conn.prepareStatement(sql);
 32             pstmt.setString(1, item.getItemNo());
 33             pstmt.setString(2, item.getItemName());
 34             pstmt.setString(3, item.getSpec());
 35             pstmt.setString(4, item.getPattern());
 36             pstmt.setString(5, item.getItemCategory().getId());
 37             pstmt.setString(6, item.getItemUnit().getId());
 38             pstmt.executeUpdate();
 39         }catch(SQLException e) {
 40             e.printStackTrace();
 41             //System.out.println("errorCode=" + e.getErrorCode());
 42             //System.out.println("description=" + e.getMessage());
 43 //            if (e.getErrorCode() == 1) {
 44 //                throw new ApplicationException("物料代码已经存在，代码【" + item.getItemNo()  + "】");
 45 //            }
 46             throw new ApplicationException("添加物料失败！");
 47         }finally {
 48             DbUtil.close(pstmt);
 49         }
 50     }
 51     //删除物料信息数组
 52     public void delItem(Connection conn, String[] itemNos) {
 53         StringBuffer sbString = new StringBuffer(); 
 54         for (int i=0; i<itemNos.length; i++) {
 55             sbString.append("?");
 56             if (i < (itemNos.length - 1)) {
 57                 sbString.append(",");
 58             }
 59         }
 60         String sql = "delete from t_items where item_no in(" + sbString.toString()  + ")";
 61         PreparedStatement pstmt = null;
 62         try {
 63             pstmt = conn.prepareStatement(sql);
 64             for (int i=0; i<itemNos.length; i++) {
 65                 pstmt.setString(i+1, itemNos[i]);
 66             }
 67             pstmt.executeUpdate();
 68         }catch(SQLException e) {
 69             e.printStackTrace();
 70             throw new ApplicationException("删除物料失败！");
 71         }finally {
 72             DbUtil.close(pstmt);
 73         }
 74     }
 75     //通过Id查询物料信息
 76     public Item findItemById(Connection conn, String itemNo) {
 77         StringBuffer sbSql = new StringBuffer();
 78         //第一中方法
 79         sbSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.item_category_id, ")
 80              .append("b.name as item_category_name, a.item_unit_id, c.name as item_unit_name ")
 81              .append("from t_items a, t_data_dict b, t_data_dict c ")
 82              .append("where a.item_category_id=b.id and a.item_unit_id=c.id and a.item_no=?");
 83 
 84 //        //第二中方法         
 85 //        sbSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.category as category_id, ")
 86 //             .append("(select b.name from t_data_dict b where a.category=b.id) as category_name, ")
 87 //             .append("a.unit as unit_id, ")
 88 //             .append("(select c.name from t_data_dict c where a.unit=c.id) as unit_name ")
 89 //             .append("from t_items a where a.item_no=?");
 90         
 91         //通常采用日志组件记录，如log4j, 级别：info,debug,error...
 92         
 93         PreparedStatement pstmt = null;
 94         ResultSet rs = null;
 95         Item item = null;
 96         try {
 97             pstmt = conn.prepareStatement(sbSql.toString());
 98             pstmt.setString(1, itemNo);
 99             rs = pstmt.executeQuery();
100             if (rs.next()) {
101                 item = new Item();
102                 item.setItemNo(rs.getString("item_no"));
103                 item.setItemName(rs.getString("item_name"));
104                 item.setSpec(rs.getString("spec"));
105                 item.setPattern(rs.getString("pattern"));
106                 //构造ItemCategory
107                 ItemCategory ic = new ItemCategory();
108                 ic.setId(rs.getString("item_category_id"));
109                 ic.setName(rs.getString("item_category_name"));
110                 item.setItemCategory(ic);
111             
112                 
113                 //构造ItemUnit
114                 ItemUnit iu = new ItemUnit();
115                 iu.setId(rs.getString("item_unit_id"));
116                 iu.setName(rs.getString("item_unit_name"));
117                 item.setItemUnit(iu);
118             }
119         }catch(SQLException e) {
120             e.printStackTrace();
121             //记录到日志文件 error
122             throw new ApplicationException("根据物料代码查询出错，物料代码[" + itemNo + "]");
123         }finally {
124             DbUtil.close(rs);
125             DbUtil.close(pstmt);
126         }
127         return item;
128     }
129     //根据pageNO、pageSize、condation显示很多物料信息列表
130     public PageModel findItemList(Connection conn, int pageNo, int pageSize, String condation) {
131         StringBuffer sbSql = new StringBuffer();
132 
133         //第一中方法
134 //        sbSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.item_category_id, ")
135 //             .append("b.name as item_category_name, a.item_unit_id, c.name as item_unit_name ")
136 //             .append("from t_items a, t_data_dict b, t_data_dict c ")
137 //             .append("where a.item_category_id=b.id and a.item_unit_id=c.id and a.item_no=?");
138 
139 //        //第二中方法         
140 //        sbSql.append("select a.item_no, a.item_name, a.spec, a.pattern, a.category as category_id, ")
141 //             .append("(select b.name from t_data_dict b where a.category=b.id) as category_name, ")
142 //             .append("a.unit as unit_id, ")
143 //             .append("(select c.name from t_data_dict c where a.unit=c.id) as unit_name ")
144 //             .append("from t_items a where a.item_no=?");
145         
146         sbSql.append("select * ")
147             .append("from (")
148                 .append("select i.*, rownum rn from (")
149                 .append("select a.item_no, a.item_name, a.spec, a.pattern, a.item_category_id, ")
150                 .append("b.name as item_category_name, a.item_unit_id, c.name as item_unit_name ")
151                 .append("from t_items a, t_data_dict b, t_data_dict c ")
152                 .append("where a.item_category_id=b.id and a.item_unit_id=c.id  ");
153                 if (condation != null && !"".equals(condation)) {
154                     sbSql.append(" and (a.item_no like '" + condation + "%' or a.item_name like '" + condation + "%') ");
155                 }
156                 sbSql.append(" order by a.item_no")
157                 .append(") i where rownum<=? ")
158                 .append(") ")
159                 .append("where rn >? ");
160         System.out.println("sql=" + sbSql.toString());
161                 
162         //通常采用日志组件记录，如log4j, 级别：info,debug,error...
163         PreparedStatement pstmt = null;
164         ResultSet rs = null;
165         PageModel pageModel = null;
166         try {
167             pstmt = conn.prepareStatement(sbSql.toString());
168             pstmt.setInt(1, pageNo * pageSize);
169             pstmt.setInt(2, (pageNo - 1) * pageSize);
170             rs = pstmt.executeQuery();
171             List itemList = new ArrayList();
172             while (rs.next()) {
173                 Item item = new Item();
174                 item.setItemNo(rs.getString("item_no"));
175                 item.setItemName(rs.getString("item_name"));
176                 item.setSpec(rs.getString("spec"));
177                 item.setPattern(rs.getString("pattern"));
178                 //构造ItemCategory
179                 ItemCategory ic = new ItemCategory();
180                 ic.setId(rs.getString("item_category_id"));
181                 ic.setName(rs.getString("item_category_name"));
182                 item.setItemCategory(ic);
183                 
184                 //构造ItemUnit
185                 ItemUnit iu = new ItemUnit();
186                 iu.setId(rs.getString("item_unit_id"));
187                 iu.setName(rs.getString("item_unit_name"));
188                 item.setItemUnit(iu);
189                 
190                 itemList.add(item);
191             }
192             pageModel = new PageModel();
193             pageModel.setPageNo(pageNo);
194             pageModel.setPageSize(pageSize);
195             pageModel.setList(itemList);
196             //根据条件取得记录数
197             int totalRecords = getTotalRecords(conn, condation);
198             pageModel.setTotalRecords(totalRecords);
199         }catch(SQLException e) {
200             e.printStackTrace();
201             //记录到日志文件 error
202             throw new ApplicationException("分页查询失败");
203         }finally {
204             DbUtil.close(rs);
205             DbUtil.close(pstmt);
206         }
207         return pageModel;
208     }
209 
210     /**
211      * 根据条件取得记录数
212      * @param conn
213      * @param queryStr
214      * @return
215      */
216     private int getTotalRecords(Connection conn, String condation) 
217     throws SQLException {
218         String sql = "select count(*) from t_items ";
219         if (condation != null && !"".equals(condation)) {
220             sql+="where item_no like '" + condation + "%' or item_name like '" + condation + "%' ";
221         }
222         PreparedStatement pstmt = null;
223         ResultSet rs = null;
224         int temp = 0;
225         try {
226             pstmt = conn.prepareStatement(sql);
227             rs = pstmt.executeQuery();
228             rs.next();
229             temp = rs.getInt(1);
230         }finally {
231             DbUtil.close(rs);
232             DbUtil.close(pstmt);
233         }
234         return temp;
235     }    
236     //修改物料信息
237     public void modifyItem(Connection conn, Item item) {
238         String sql = "update t_items set item_name=?, spec=?, pattern=?, item_category_id=?, item_unit_id=? ";
239         sql+=" where item_no=?";
240         PreparedStatement pstmt = null;
241         try {
242             pstmt = conn.prepareStatement(sql);
243             pstmt.setString(1, item.getItemName());
244             pstmt.setString(2, item.getSpec());
245             pstmt.setString(3, item.getPattern());
246             pstmt.setString(4, item.getItemCategory().getId());
247             pstmt.setString(5, item.getItemUnit().getId());
248             pstmt.setString(6, item.getItemNo());
249             pstmt.executeUpdate();
250         }catch(SQLException e) {
251             e.printStackTrace();
252             throw new ApplicationException("修改物料失败！");
253         }finally {
254             DbUtil.close(pstmt);
255         }
256     }
257 
258     
259 }