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                 //构造ItemCategory107                 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                 //构造ItemUnit114                 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             //记录到日志文件 error122             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                 //构造ItemCategory179                 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                 //构造ItemUnit185                 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             //记录到日志文件 error202             throw new ApplicationException("分页查询失败");203         }finally {204             DbUtil.close(rs);205             DbUtil.close(pstmt);206         }207         return pageModel;208     }209 210     /**211      * 根据条件取得记录数212      * @param conn213      * @param queryStr214      * @return215      */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 }