11/27分类管理前段完成

package store.dao; import store.po.Supplier; import store.utils.Pager; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; public class SupplierDAO { public List<Supplier> findSuppliers(String name, Pager pager)throws Exception{ List<Supplier> list=new ArrayList<>(); Connection con=null; PreparedStatement ps=null; ResultSet rs=null; try { Class.forName(DB.JDBC_DRIVER); con= DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER,DB.JDBC_PASSWORD); ps=con.prepareStatement("select count(id) as total from t_supplier where name like ?"); ps.setString(1,"%"+name+"%"); rs=ps.executeQuery(); if(rs.next()){ pager.setTotal(rs.getInt("total")); } ps=con.prepareStatement("select * from t_supplier where name like ? limit ?,?"); ps.setString(1,"%"+name+"%"); ps.setInt(2,(pager.getCurrentPage()-1)*pager.getPageSize()); ps.setInt(3,pager.getPageSize()); rs=ps.executeQuery(); while (rs.next()){ Supplier s=new Supplier(); s.setId(rs.getInt("id")); s.setName(rs.getString("name")); s.setTel(rs.getString("tel")); s.setContacts(rs.getString("contacts")); s.setInfo(rs.getString("info")); list.add(s); } }catch (Exception e){ e.printStackTrace();; throw new Exception("数据库异常："+e.getMessage()); }finally { if (rs!=null) rs.close(); if (ps!=null) ps.close(); if (con!=null) con.close(); } return list; } public void save(Supplier s)throws Exception{ Connection con=null; PreparedStatement ps=null; try{ Class.forName(DB.JDBC_DRIVER); con=DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER,DB.JDBC_PASSWORD); ps=con.prepareStatement("insert into t_supplier value (null ,?,?,?,?)"); ps.setString(1,s.getName()); ps.setString(2,s.getContacts()); ps.setString(3,s.getTel()); ps.setString(4,s.getInfo()); ps.executeUpdate(); }catch (Exception e){ e.printStackTrace(); throw new Exception("数据库异常："+e.getMessage()); }finally { if (ps !=null) ps.close(); if (con !=null) con.close(); } } public void delete(int id)throws Exception{ Connection con=null; PreparedStatement ps=null; try { Class.forName(DB.JDBC_DRIVER); con=DriverManager.getConnection(DB.JDBC_URL, DB.JDBC_USER,DB.JDBC_PASSWORD); ps=con.prepareStatement("delete from t_supplier where id=?"); ps.setInt(1,id); ps.executeUpdate(); }catch (Exception e){ e.printStackTrace(); throw new Exception("数据库异常："+e.getMessage()); }finally { if(ps !=null) ps.close(); if (con !=null) con.close(); } } public Supplier findById(int id)throws Exception{ Supplier s=null; Connection con=null; PreparedStatement ps=null; ResultSet rs=null; try{ Class.forName(DB.JDBC_DRIVER); con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD); ps=con.prepareStatement("select * from t_supplier where id=? "); ps.setInt(1,id); rs=ps.executeQuery(); if (rs.next()){ s=new Supplier(); s.setId(rs.getInt("id")); s.setName(rs.getString("name")); s.setTel(rs.getString("tel")); s.setContacts(rs.getString("contacts")); s.setInfo(rs.getString("info")); } }catch (Exception e){ e.printStackTrace(); throw new Exception("数据库异常："+e.getMessage()); }finally { if (rs!=null) rs.close(); if (ps!=null) ps.close(); if (con!=null) con.close(); } return s; } public void update(Supplier s) throws Exception{ Connection con=null; PreparedStatement ps=null; try { Class.forName(DB.JDBC_DRIVER); con=DriverManager.getConnection(DB.JDBC_URL,DB.JDBC_USER,DB.JDBC_PASSWORD); ps=con.prepareStatement("update t_supplier set name=?,contacts=?,tel=?,info=? where id=?"); ps.setString(1,s.getName()); ps.setString(2,s.getContacts()); ps.setString(3,s.getTel()); ps.setString(4,s.getInfo()); ps.setInt(5,s.getId()); ps.executeUpdate(); }catch (Exception e){ e.printStackTrace(); throw new Exception("数据库异常："+e.getMessage()); }finally { if (ps!=null) ps.close(); if (con!=null) ps.close(); } } }