package com.hanqi.dao; import java.sql.*; import java.util.*; import com.hanqi.dao.DBHelper; public class ZhiWeiDAL { / * 插入数据的方法 * @param m * @return * @throws Exception */ public int insert(ZhiWei m) throws Exception { int rtn = -1; Connection conn = DBHelper.getConnection(); PreparedStatement pst = null; if (conn != null) { try { /*String sql = "insert into T_ZHIWEI (id, zhcid, zhiw, leib,diq,rensh," + "yuex,jiesh,zhuany,xuel,jingy,sex,minage,maxage,lianxr,lianxtel,fbdate,shanch" + "email,fbdate,shanch,shenh) " + "values (SQ_T_ZHIWEI_ID.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate,'0','1') "; pst = conn.prepareStatement(sql); pst.setInt(1, m.getZhcid()); pst.setString(2, m.getZhiw()); pst.setString(3, m.getLeib()); pst.setString(4, m.getDiq()); pst.setString(5, m.getRensh()); pst.setString(6, m.getYuex()); pst.setString(7, m.getJiesh()); pst.setString(8, m.getZhuany()); pst.setString(9, m.getXuel()); pst.setString(10, m.getJingy()); pst.setString(11, m.getSex()); pst.setInt(12, m.getMinage()); pst.setInt(13, m.getMaxage()); pst.setString(14, m.getLianxr()); pst.setString(15, m.getLianxtel()); pst.setString(16, m.getEmail());*/ String sql = "insert into T_ZHIWEI (id, zhcid, zhiw, leib,fbdate,shanch,shenh) " + "values (SQ_T_ZHIWEI_ID.nextval,?,?,?,sysdate,'0','1') "; pst = conn.prepareStatement(sql); pst.setInt(1, m.getZhcid()); pst.setString(2, m.getZhiw()); pst.setString(3, m.getLeib()); rtn = pst.executeUpdate(); } catch(Exception ex) { throw ex; } finally { try { pst.close(); } catch(Exception ex){} conn.close(); } } return rtn; } //删 public int delete(int id) throws Exception { int rtn = -1; Connection conn = DBHelper.getConnection(); PreparedStatement pst = null; if (conn != null) { try { String sql = "update T_ZHIWEI set shanch = '1' where id = ? "; pst = conn.prepareStatement(sql); pst.setInt(1, id); rtn = pst.executeUpdate(); } catch(Exception ex) { throw ex; } finally { try { pst.close(); } catch(Exception ex) { } conn.close(); } } return rtn; } public int update(ZhiWei m) throws Exception { int rtn = -1; Connection conn = DBHelper.getConnection(); PreparedStatement pst = null; if (conn != null) { try { String sql = "update T_ZHIWEI set zhiw = ?, leib = ? where id = ? "; pst = conn.prepareStatement(sql); pst.setString(1, m.getZhiw()); pst.setString(2, m.getLeib()); pst.setInt(3, m.getId()); rtn = pst.executeUpdate(); } catch(Exception ex) { throw ex; } finally { try { pst.close(); } catch(Exception ex){} conn.close(); } } return rtn; } public ArrayList<ZhiWei> getList(int qyid, int pagenum, int pagerows, Map<String, String> search) throws Exception { ArrayList<ZhiWei> rtn = null; Connection conn = DBHelper.getConnection(); PreparedStatement pst = null; ResultSet rs = null; if (conn != null) { try { int maxrows = pagenum * pagerows; //遍历Map String sq = ""; Set<String> k = search.keySet(); Iterator<String> ki = k.iterator();//迭代器 while(ki.hasNext()) { String key = ki.next(); String value = search.get(key); //组合SQL switch(key) { case "id": if (value != null && value.length() > 0) { sq += " and id = " + value + " "; } break; case "zhiw": if (value != null && value.length() > 0) { sq += " and zhiw like '%" + value + "%' "; } break; } } //找sort String sort = search.get("sort"); if (sort != null) { sq += " order by " + sort + " " + search.get("order"); } String sql = "select * from (select t.*, rownum as rnum from (select * from T_ZHIWEI where zhcid = ? and shanch != '1' " + sq + ") t where rownum <= ?) where rnum > ?"; pst = conn.prepareStatement(sql); pst.setInt(1, qyid); pst.setInt(2, maxrows); pst.setInt(3, maxrows - pagerows); rs = pst.executeQuery(); if (rs != null) { rtn = new ArrayList<ZhiWei>(); while(rs.next()) { ZhiWei u = new ZhiWei(); u.setId(rs.getInt("id")); u.setZhcid(rs.getInt("zhcid")); u.setZhiw(rs.getString("zhiw")); u.setLeib(rs.getString("leib")); u.setDiq(rs.getString("diq")); u.setRensh(rs.getString("rensh")); u.setYuex(rs.getString("yuex")); u.setJiesh(rs.getString("jiesh")); u.setZhuany(rs.getString("zhuany")); u.setXuel(rs.getString("xuel")); u.setJingy(rs.getString("jingy")); u.setSex(rs.getString("sex")); u.setMinage(rs.getInt("minage")); u.setMaxage(rs.getInt("maxage")); u.setLianxr(rs.getString("lianxr")); u.setLianxtel(rs.getString("lianxtel")); u.setEmail(rs.getString("email")); u.setFbdate(rs.getString("fbdate")); u.setShanch(rs.getString("shanch")); u.setShenh(rs.getString("shenh")); rtn.add(u); } } } catch(Exception ex) { throw ex; } finally { try { rs.close(); } catch(Exception ex){} try { pst.close(); } catch(Exception ex){} conn.close(); } } return rtn; } public ArrayList<ZhiWei> getList(int qyid, int pagenum, int pagerows) throws Exception { ArrayList<ZhiWei> rtn = null; Connection conn = DBHelper.getConnection(); PreparedStatement pst = null; ResultSet rs = null; if (conn != null) { try { int maxrows = pagenum * pagerows; String sql = "select * from (select t.*, rownum as rnum from " + "(select * from T_ZHIWEI where zhcid = ? and shanch != '1' order by id desc) t " + "where rownum <= ?) where rnum > ?"; pst = conn.prepareStatement(sql); pst.setInt(1, qyid); pst.setInt(2, maxrows); pst.setInt(3, maxrows - pagerows); rs = pst.executeQuery(); if (rs != null) { rtn = new ArrayList<ZhiWei>(); while(rs.next()) { ZhiWei u = new ZhiWei(); u.setId(rs.getInt("id")); u.setZhcid(rs.getInt("zhcid")); u.setZhiw(rs.getString("zhiw")); u.setLeib(rs.getString("leib")); u.setDiq(rs.getString("diq")); u.setRensh(rs.getString("rensh")); u.setYuex(rs.getString("yuex")); u.setJiesh(rs.getString("jiesh")); u.setZhuany(rs.getString("zhuany")); u.setXuel(rs.getString("xuel")); u.setJingy(rs.getString("jingy")); u.setSex(rs.getString("sex")); u.setMinage(rs.getInt("minage")); u.setMaxage(rs.getInt("maxage")); u.setLianxr(rs.getString("lianxr")); u.setLianxtel(rs.getString("lianxtel")); u.setEmail(rs.getString("email")); u.setFbdate(rs.getString("fbdate")); u.setShanch(rs.getString("shanch")); u.setShenh(rs.getString("shenh")); rtn.add(u); } } } catch(Exception ex) { throw ex; } finally { try { rs.close(); } catch(Exception ex){} try { pst.close(); } catch(Exception ex){} conn.close(); } } return rtn; } //记录条数查询  public int getCount(int qyid) throws Exception { int rtn = 0; //获取  Connection conn = DBHelper.getConnection(); PreparedStatement pst = null; ResultSet rs = null; if (conn != null) { try { String sql = "select count(1) as c from T_ZHIWEI where zhcid = ?"; pst = conn.prepareStatement(sql); pst.setInt(1, qyid); rs = pst.executeQuery(); if (rs != null && rs.next()) { rtn = rs.getInt("c"); } } catch(Exception ex) { throw ex; } finally { try { rs.close(); } catch(Exception ex){} try { pst.close(); } catch(Exception ex){} conn.close(); } } return rtn; } }