基于jsp的城乡信息管理系统源码.zip

package daowen.bll; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.ArrayList; import java.util.List; import daowen.data.jdbcex.DB; public class DishManager { private static DishManager instance=new DishManager(); private DishManager() { } /* * 得到实例 * */ public static DishManager getInstance(){ if(instance==null) { return new DishManager(); } else return instance; } public void addDish(Dish dish) { String SQL= "insert into dish( name, description, price, categoryid, categoryname) " + "values(?, ?, ?, ?, ?)"; DB db=new DB(); Connection con=db.getConnection(); ResultSet rs=null; Statement sm=null; PreparedStatement psm=null; try { psm=con.prepareStatement(SQL); psm.setString(1, dish.getName()); psm.setString(2, dish.getDescription()); psm.setString(3, dish.getPrice()); psm.setString(4, dish.getCategoryid()); psm.setString(5, dish.getCategoryname()); psm.executeUpdate(); //rs=db.getResultSet(SQL); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { if(rs!=null) rs.close(); if(psm!=null) psm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(db!=null) db.close(); } } public void updateDish(Dish dish) { String SQL= "update dish set name=?, description=?, price=?, categoryid=?, categoryname=? where id=? "; DB db=new DB(); Connection con=db.getConnection(); ResultSet rs=null; Statement sm=null; PreparedStatement psm=null; try { psm=con.prepareStatement(SQL); psm.setString(1, dish.getName()); psm.setString(2, dish.getDescription()); psm.setString(3, dish.getPrice()); psm.setString(4, dish.getCategoryid()); psm.setString(5, dish.getCategoryname()); psm.setString(6, dish.getId()); psm.executeUpdate(); //rs=db.getResultSet(SQL); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ try { if(rs!=null) rs.close(); if(psm!=null) psm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(db!=null) db.close(); } } /** * 分页查询 * @param pageNo 页编码 * @param pageSize 页尺寸 * @return */ public PageModel getAllDishs(int pageNo,int pageSize) { List dishlist=new ArrayList(); String SQL="select top("+pageSize +") * from [dish] where name not in (select top("+pageSize*(pageNo-1)+") name from [dish])"; System.out.println(SQL); DB db=new DB(); ResultSet rs=db.getResultSet(SQL); try { while(rs.next()) { Dish dish=new Dish(); dish.setId(new Integer(rs.getInt("id")).toString()); dish.setCategoryid(rs.getString("categoryid")); dish.setCategoryname(rs.getString("categoryname")); dish.setName(rs.getString("name")); dish.setDescription(rs.getString("description")); dish.setPrice(rs.getString("price")); dishlist.add(dish); } } catch (SQLException e) { e.printStackTrace(); } PageModel pm=new PageModel(); pm.setTotalRecord(dishlist); pm.setTotalCount(getTotalCount()); pm.setPageNo(pageNo); pm.setPageSize(pageSize); return pm; } /** * 分页查询 * @param pageNo 页编码 * @param pageSize 页尺寸 * @return */ public PageModel search(String name,int pageNo,int pageSize) { List dishlist=new ArrayList(); String SQL="select top("+pageSize +") * from [dish] where id not in (select top("+pageSize*(pageNo-1)+") id from [dish]) and name like '%"+name +"%'"; System.out.println(SQL); DB db=new DB(); ResultSet rs=db.getResultSet(SQL); try { while(rs.next()) { Dish dish=new Dish(); dish.setId(new Integer(rs.getInt("id")).toString()); dish.setCategoryid(rs.getString("categoryid")); dish.setCategoryname(rs.getString("categoryname")); dish.setName(rs.getString("name")); dish.setDescription(rs.getString("description")); dish.setPrice(rs.getString("price")); dishlist.add(dish); } } catch (SQLException e) { e.printStackTrace(); } PageModel pm=new PageModel(); pm.setTotalRecord(dishlist); pm.setTotalCount(getTotalCount()); pm.setPageNo(pageNo); pm.setPageSize(pageSize); return pm; } /** * 得到所有用户的记录个数 * @return */ public int getTotalCount() { int totalcount=0; String SQL="select count(*) from [dish]"; DB db=new DB(); ResultSet rs=db.getResultSet(SQL); try { while(rs.next()) { totalcount=rs.getInt(1); } } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ db.close(); } return totalcount; } /** * 删除用户 * @param userid */ public void deleteDish(String name){ String SQL="delete from dish where name=?"; Connection con=null; PreparedStatement psm=null; // ResultSet rs=null; DB db=new DB(); try { con=db.getConnection(); psm=con.prepareStatement(SQL); psm.setString(1, name); psm.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ if(psm!=null) try { psm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(db!=null) db.close(); } } public void deleteDishById(String id){ String SQL="delete from dish where id=?"; Connection con=null; PreparedStatement psm=null; // ResultSet rs=null; DB db=new DB(); try { con=db.getConnection(); psm=con.prepareStatement(SQL); psm.setString(1, id); psm.executeUpdate(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); }finally{ if(psm!=null) try { psm.close(); } catch (SQLException e) { // TODO Auto-generated catch block e.printStackTrace(); } if(db!=null) db.close(); } } // public PageModel search(int pageNo,int pageSize,String name) // { // List dishlist=new ArrayList(); // // String SQL="select top("+pageSize // +") * from [dish] where name not in (select top("+pageSize*(pageNo-1)+") name from [dish]) and name like %"+name+"%" ; // System.out.println(SQL); // DB db=new DB(); // ResultSet rs=db.getResultSet(SQL); // try { // while(rs.next()) // { // Dish dish=new Dish(); // dish.setCategoryid(rs.getString("categoryid")); // dish.setCategoryname(rs.getString("categoryname")); // dish.setName(rs.getString("name")); // dish.setDescription(rs.getString("description")); // dish.setPrice(rs.getString("price")); // // dishlist.add(dish); // } // } catch (SQLException e) { // // e.printStackTrace(); // } // PageModel pm=new PageModel(); // // pm.setTotalRecord(dishlist); // // pm.setTotalCount(getTotalCount()); // pm.setPageNo(pageNo); // pm.setPageSize(pageSize); // // return pm; // } // public Dish findDishById(String id) { String SQL=new String(); SQL="select * from dish where id='"+id+"'"; Connection conn = null; PreparedStatement pstmt = null; Statement st=null; ResultSet rs = null; DB db=new DB(); Dish dish = null; try { rs=db.getResultSet(SQL); if(rs.next()) { dish=new Dish(); dish.setId(rs.getString("id"