一个小的模块jsp+javabean+servlet的增删改查

package ht.customerinfo.dao.impl; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.Collection; import ht.customerinfo.dao.dao.CustomerInfoDao; import ht.customerinfo.vo.CustomerInfoModel; import ht.customerinfo.vo.CustomerInfoQueryModel; import ht.util.db.DbConn; public class CustomerInfoImpl implements CustomerInfoDao{ public boolean create(CustomerInfoModel cim) { Connection conn = null; try { conn = DbConn.getConn(); String sql = "insert into CUSTOMER_INFO values(?,?,?,?,?,?,?,?)"; PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setLong(1, cim.getCust_id()); pstmt.setInt(2, cim.getCust_step()); pstmt.setString(3, cim.getBatchId()); pstmt.setInt(4, cim.getOppor_source()); pstmt.setInt(5, cim.getOppor_state()); pstmt.setString(6, cim.getCust_name()); pstmt.setString(7, cim.getStudentName()); pstmt.setString(8, cim.getRelation()); pstmt.executeUpdate(); pstmt.close(); return true; } catch (Exception e) { e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return false; } public boolean delete(CustomerInfoModel cim) { Connection conn = null; try { conn = DbConn.getConn(); String sql = "delete from CUSTOMER_INFO where cust_id=?"; PreparedStatement pstmt=conn.prepareStatement(sql); pstmt.setLong(1, cim.getCust_id()); pstmt.executeUpdate(); pstmt.close(); return true; } catch (Exception e) { e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return false; } public CustomerInfoModel getByCustomerId(int cust_id) { Connection conn = null; CustomerInfoModel cim = new CustomerInfoModel(); try { conn = DbConn.getConn(); String sql = "select * from CUSTOMER_INFO where cust_id=?"; PreparedStatement pstmt=conn.prepareStatement(sql); pstmt.setInt(1, cust_id); ResultSet rs = pstmt.executeQuery(); Collection col = rsToModel(rs); if(col!=null && col.size()>0){ cim = (CustomerInfoModel) col.iterator().next(); } rs.close(); pstmt.close(); } catch (Exception e) { e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return cim; } public int getCount() { int count = 0; Connection conn = null; try { conn = DbConn.getConn(); String sql = "select count(*) from CUSTOMER_INFO"; PreparedStatement pstmt=conn.prepareStatement(sql); ResultSet rs = pstmt.executeQuery(); while(rs.next()){ count = rs.getInt(1); } rs.close(); pstmt.close(); } catch (Exception e) { e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return count; } public boolean update(CustomerInfoModel cim) { Connection conn=null; try { conn = DbConn.getConn(); String sql = "update CUSTOMER_INFO set cust_step=?,batchId=?,oppor_source=?,oppor_state=?,cust_name=?,studentName=?,relation=? where cust_id=?"; PreparedStatement pstmt=conn.prepareStatement(sql); pstmt.setInt(1, cim.getCust_step()); pstmt.setString(2, cim.getBatchId()); pstmt.setInt(3, cim.getOppor_source()); pstmt.setInt(4, cim.getOppor_state()); pstmt.setString(5, cim.getCust_name()); pstmt.setString(6, cim.getStudentName()); pstmt.setString(7, cim.getRelation()); pstmt.setLong(8, cim.getCust_id()); pstmt.executeUpdate(); pstmt.close(); return true; } catch (Exception e) { e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return false; } public Collection<CustomerInfoModel> getAll(int fromPage, int toPage) { Collection col=new ArrayList(); Connection conn=null; try { conn = DbConn.getConn(); String sql = "select * from (select rownum r,CUSTOMER_INFO.* from CUSTOMER_INFO) where r>=? and r<=?"; PreparedStatement pstmt=conn.prepareStatement(sql); pstmt.setInt(1, fromPage); pstmt.setInt(2, toPage); ResultSet rs=pstmt.executeQuery(); col = this.rsToModel(rs); pstmt.close(); } catch (Exception e) { e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return col; } public Collection<CustomerInfoModel> getByConditon( CustomerInfoQueryModel ciqm, int fromPage, int toPage) { Collection<CustomerInfoModel> tempCol = new ArrayList<CustomerInfoModel>(); Connection conn = null; // String sql = "select * from CUSTOMER_INFO where 1 = 1 "; String sql = ""; String condition = ""; try { conn = DbConn.getConn(); condition = this.preparedSql(sql, ciqm); System.out.println("condition===="+condition); sql = "select * from (select rownum r,CUSTOMER_INFO.* from CUSTOMER_INFO where 1=1 "+condition+" ) where r>="+fromPage+" and r<="+toPage; System.out.println("sql-----------"+sql); PreparedStatement pstmt=conn.prepareStatement(sql); this.setSqlValue(ciqm, pstmt); ResultSet rs=pstmt.executeQuery(); tempCol = rsToModel(rs); rs.close(); pstmt.close(); } catch (Exception e) { e.printStackTrace(); }finally{ try { conn.close(); } catch (SQLException e) { e.printStackTrace(); } } return tempCol; } private Collection rsToModel(ResultSet rs)throws Exception{ Collection col = new ArrayList(); while(rs.next()){ CustomerInfoModel cim = new CustomerInfoModel(); cim.setCust_id(rs.getLong("cust_id")); cim.setCust_step(rs.getInt("cust_step")); cim.setBatchId(rs.getString("batchId")); cim.setOppor_source(rs.getInt("oppor_source")); cim.setOppor_state(rs.getInt("oppor_state")); cim.setCust_name(rs.getString("cust_name")); cim.setStudentName(rs.getString("studentName")); cim.setRelation(rs.getString("relation")); col.add(cim); } rs.close(); return col; } private String preparedSql(String sql,CustomerInfoQueryModel ciqm){ if(ciqm.getCust_id()>0){ sql += " and cust_id=? "; } if(ciqm.getCust_step()>0){ sql += " and cust_step=? "; } if(ciqm.getBatchId()!=null && ciqm.getBatchId().trim().length()>0){ sql += " and batchId=? "; } if(ciqm.getOppor_source()>0){ sql += " and oppor_source=? "; } if(ciqm.getOppor_state()>0){ sql += " and oppor_state=? "; } if(ciqm.getCust_name()!=null && ciqm.getCust_name().trim().length()>0){ sql += " and cust_name like ? "; } if(ciqm.getStudentName()!=null && ciqm.getStudentName().trim().length()>0){ sql += " and studentName like ? "; } if(ciqm.getRelation()!=null && ciqm.getRelation().trim().length()>0){ sql += " and relation=? "; } return sql; } private void setSqlValue(CustomerInfoQueryModel ciqm,PreparedStatement pstmt) throws Exception{ int index = 1; if(ciqm.getCust_id()>0){ pstmt.setLong(index, ciqm.getCust_id()); index ++; } if(ciqm.getCust_step()>0){ pstmt.setInt(index, ciqm.getCust_step()); index ++; } if(ciqm.getBatchId()!=null && ciqm.getBatchId().trim().length()>0){ pstmt.setString(index, ciqm.getBatchId()); index ++; } if(ciqm.getOppor_source()>0){ pstmt.setInt(index, ciqm.getOppor_source()); index ++; } if(ciqm.getOppor_state()>0){ pstmt.setInt(index, ciqm.getOppor_state()); index ++; } if(ciqm.getCust_name()!=null && ciqm.getCust_name().trim().length()>0){ pstmt.setString(index, "%"+ciqm.getCust_name()+"%");