无框架简单网上订餐系统

package com.model; import java.sql.*; import java.util.*; import com.dao.DBManager; import com.endity.FoodInfo; import com.endity.SellFoodInfo; public class FoodDao { private Connection conn; private PreparedStatement ps; private ResultSet rs; public FoodDao() { conn=DBManager.getInstance().getConnection(); } //查询所有的食物信息 public ArrayList findAllFoods() { ArrayList list=new ArrayList(); String sql="select * from foodInfo"; try { ps=conn.prepareStatement(sql); rs=ps.executeQuery(); while(rs.next()) { FoodInfo food=new FoodInfo(); food.setFoodId(rs.getInt(1)); food.setFoodName(rs.getString(2)); food.setRemark(rs.getString(3)); food.setFoodPrice(rs.getFloat(4)); food.setFoodImage(rs.getString(5)); food.setDescription(rs.getString(6)); food.setStock(rs.getInt(7)); food.setShipment(rs.getInt(8)); list.add(food); } return list; } catch (SQLException e) { e.printStackTrace(); } /*finally { //关闭所有的操作 DBManager.closeResultSet(rs); DBManager.closeStatement(ps); DBManager.closeConnection(conn); }*/ return null; } public ArrayList findFoods(int row ,int pageId) { ArrayList list=new ArrayList(); String sql="select top "+row+" * from foodInfo where foodId not in"; sql+="(select top "+((pageId-1)*row)+" foodId from foodInfo)"; try { ps=conn.prepareStatement(sql); rs=ps.executeQuery(); while(rs.next()) { FoodInfo food=new FoodInfo(); food.setFoodId(rs.getInt(1)); food.setFoodName(rs.getString(2)); food.setRemark(rs.getString(3)); food.setFoodPrice(rs.getFloat(4)); food.setFoodImage(rs.getString(5)); food.setDescription(rs.getString(6)); list.add(food); } return list; } catch (SQLException e) { e.printStackTrace(); } /*finally { //关闭所有的操作 DBManager.closeResultSet(rs); DBManager.closeStatement(ps); DBManager.closeConnection(conn); }*/ return null; } //获取食品信息 public FoodInfo findFoodById(int foodId) { FoodInfo food=new FoodInfo(); String sql="select * from foodInfo where foodId=?"; try { ps=conn.prepareStatement(sql); ps.setInt(1,foodId); rs=ps.executeQuery(); if(rs.next()) { food.setFoodId(rs.getInt(1)); food.setFoodName(rs.getString(2)); food.setRemark(rs.getString(3)); food.setFoodPrice(rs.getFloat(4)); food.setFoodImage(rs.getString(5)); food.setDescription(rs.getString(6)); food.setStock(rs.getInt(7)); food.setShipment(rs.getInt(8)); } return food; } catch (SQLException e) { e.printStackTrace(); } return null; } //删除商品 public int delFoodById(int foodId) { int count=0; String sql="delete from foodInfo where foodId=?"; try { ps=conn.prepareStatement(sql); ps.setInt(1,foodId); count=ps.executeUpdate(); } catch (SQLException e1) { e1.printStackTrace(); } try { ps=conn.prepareStatement(sql); } catch (SQLException e) { e.printStackTrace(); } return count; } //添加商品 public int addFoodById(FoodInfo food) { int count=0; String sql="insert into foodInfo values(?,?,?,?,?,?,?,?)"; try { ps=conn.prepareStatement(sql); ps.setInt(1,food.getFoodId()); ps.setString(2,food.getFoodName()); ps.setString(3,food.getRemark()); ps.setFloat(4,food.getFoodPrice()); ps.setString(5,food.getFoodImage()); ps.setString(6,food.getDescription()); ps.setInt(7,food.getStock()); ps.setInt(8,food.getShipment()); count=ps.executeUpdate(); } catch (SQLException e1) { e1.printStackTrace(); } return count; } //修改图书信息 public int updateFood(FoodInfo food) { int count=0; String sql="update foodInfo set foodName=?,remark=?,foodPrice=?,foodImage=?,description=?,Storage=?,Sold=? where foodId=?"; try { ps=conn.prepareStatement(sql); ps.setString(1,food.getFoodName()); ps.setString(2,food.getRemark()); ps.setFloat(3,food.getFoodPrice()); ps.setString(4,food.getFoodImage()); ps.setString(5,food.getDescription()); ps.setInt(6,food.getStock()); ps.setInt(7,food.getShipment()); ps.setInt(8,food.getFoodId()); count=ps.executeUpdate(); } catch (SQLException e) { e.printStackTrace(); } return count; } //获取商品的库存信息 public ArrayList findFood() { ArrayList list=new ArrayList(); String sql="select foodName,Storage,Sold from foodInfo"; try { ps=conn.prepareStatement(sql); rs=ps.executeQuery(); while(rs.next()) { FoodInfo food=new FoodInfo(); food.setFoodName(rs.getString(1)); food.setStock(rs.getInt(2)); food.setShipment(rs.getInt(3)); list.add(food); } } catch (SQLException e) { e.printStackTrace(); } return list; } //获取单个商品的库存信息 public SellFoodInfo findSellFoodById(int foodId) { SellFoodInfo food=new SellFoodInfo(); String sql="select a.foodId, a.foodName,a.Storage,a.Sold,b.totalValue from "; sql+="foodInfo a,orderInfo b where a.foodId=? and a.foodId=b.foodId"; try { ps=conn.prepareStatement(sql); ps.setInt(1,foodId); rs=ps.executeQuery(); if(rs.next()) { food.setFoodId(rs.getInt(1)); food.setFoodName(rs.getString(2)); food.setStorage(rs.getInt(3)); food.setSold(rs.getInt(4)); food.setTotalValue(rs.getFloat(5)); } } catch (SQLException e) { e.printStackTrace(); } return food; } //添加某种商品库存 public int addSellFood(FoodInfo food) { int count=0; String sql="insert into SellInfo values(?,?,?)"; try { ps=conn.prepareStatement(sql); ps.setInt(1,food.getFoodId()); ps.setInt(2,food.getStock()); ps.setInt(3,food.getShipment()); count=ps.executeUpdate(); } catch (SQLException e1) { e1.printStackTrace(); } return count; } //查找最大Id public int findMaxOrderId() { int Id=0; String sql="select Max(orderId) from foodOrderInfo"; try { ps=conn.prepareStatement(sql); rs=ps.executeQuery(); if(rs.next()) { Id=rs.getInt(1); } } catch (SQLException e) { e.printStackTrace(); } return Id; } }