超市购物管理系统 （JAVA）

package cn.yizhi.dao; import java.sql.*; import java.util.*; import cn.yizhi.bean.*; public class GoodsDao { private Connection con = null; private Statement stmt = null; private PreparedStatement pstmt = null; private ResultSet rs = null; /* * 添加商品 */ public boolean insert(Goods gs) { boolean isSucc = false; String sql = "insert into goods values(?,?,?,?,?,?,?,?,default,?,?)"; try { con = DBConn.getConn(); pstmt = con.prepareStatement(sql); pstmt.setInt(1, gs.getCategoryIdfirst()); pstmt.setInt(2, gs.getCategoryIdsecond()); pstmt.setInt(3, gs.getCategoryIdthird()); pstmt.setString(4, gs.getGoodsName()); pstmt.setFloat(5, gs.getPrice()); pstmt.setInt(6, gs.getIsCommd()); pstmt.setFloat(7, gs.getSalePrice()); pstmt.setString(8, gs.getDescriptions()); pstmt.setInt(9, gs.getIsExist()); pstmt.setInt(10, gs.getSaleTotal()); int count = pstmt.executeUpdate(); if(count > 0) { isSucc = true; } } catch (SQLException se) { se.printStackTrace(); } finally { try { pstmt.close(); con.close(); } catch (SQLException se) { se.printStackTrace(); } } return isSucc; } /* * 删除商品 */ public boolean delete(Goods gs) { boolean isSucc = false; int id = gs.getGoodsId(); String sql = "delete from orderDetail where goodsId="+id; String sql1 = "delete from goods where goodsId="+id; try { con = DBConn.getConn(); con.setAutoCommit(false); stmt = con.createStatement(); stmt.executeUpdate(sql); int count = stmt.executeUpdate(sql1); con.commit(); if(count > 0) { isSucc = true; } } catch (SQLException se) { try { con.rollback(); }catch(Exception e) { } se.printStackTrace(); } finally { try { stmt.close(); con.close(); } catch (SQLException se) { se.printStackTrace(); } } return isSucc; } /* * 更新商品 */ public boolean update(Goods gs) { boolean isSucc = false; String sql = "update goods set categoryIdfirst=?,categoryIdsecond=?," + "categoryIdthird=?,goodsName=?,price=?,isCommd=?,salePrice=?," + "descriptions=?,isExist=?,saleTotal=? where goodsId=?"; try { con = DBConn.getConn(); pstmt = con.prepareStatement(sql); pstmt.setInt(1, gs.getCategoryIdfirst()); pstmt.setInt(2, gs.getCategoryIdsecond()); pstmt.setInt(3, gs.getCategoryIdthird()); pstmt.setString(4, gs.getGoodsName()); pstmt.setFloat(5, gs.getPrice()); pstmt.setInt(6, gs.getIsCommd()); pstmt.setFloat(7, gs.getSalePrice()); pstmt.setString(8, gs.getDescriptions()); pstmt.setInt(9, gs.getIsExist()); pstmt.setInt(10, gs.getSaleTotal()); pstmt.setInt(11, gs.getGoodsId()); int count = pstmt.executeUpdate(); if(count > 0) { isSucc = true; } } catch (SQLException se) { se.printStackTrace(); } finally { try { pstmt.close(); con.close(); } catch (SQLException se) { se.printStackTrace(); } } return isSucc; } /* * 更新点击率 */ public boolean update1(Goods gs) { boolean isSucc = false; String sql = "update goods set saleCount=? where goodsId=?"; try { con = DBConn.getConn(); pstmt = con.prepareStatement(sql); pstmt.setInt(1, gs.getSaleCount()); pstmt.setInt(2, gs.getGoodsId()); int count = pstmt.executeUpdate(); if(count > 0) { isSucc = true; } } catch (SQLException se) { se.printStackTrace(); } finally { try { pstmt.close(); con.close(); } catch (SQLException se) { se.printStackTrace(); } } return isSucc; } /* * 更新库存 */ public boolean update2(Goods gs) { boolean isSucc = false; String sql = "update goods set saleTotal=? where goodsId=?"; try { con = DBConn.getConn(); pstmt = con.prepareStatement(sql); pstmt.setInt(1, gs.getSaleTotal()); pstmt.setInt(2, gs.getGoodsId()); int count = pstmt.executeUpdate(); if(count > 0) { isSucc = true; } } catch (SQLException se) { se.printStackTrace(); } finally { try { pstmt.close(); con.close(); } catch (SQLException se) { se.printStackTrace(); } } return isSucc; } /* * 更新商品状态，是否有货 */ public boolean update3(Goods gs) { boolean isSucc = false; String sql = "update goods set isExist=? where goodsId=?"; try { con = DBConn.getConn(); pstmt = con.prepareStatement(sql); pstmt.setInt(1, gs.getIsExist()); pstmt.setInt(2, gs.getGoodsId()); int count = pstmt.executeUpdate(); if(count > 0) { isSucc = true; } } catch (SQLException se) { se.printStackTrace(); } finally { try { pstmt.close(); con.close(); } catch (SQLException se) { se.printStackTrace(); } } return isSucc; } /* * 查询所有商品 */ public List getCategorys(PageDiv pd) { int requestPage = pd.getRequestPage(); pd.setCurrPage(requestPage); String tabName = pd.getTabName(); int requestRecord = (requestPage - 1) * 10; List list = new ArrayList(); String sql = "select top 10 * from " + tabName + " where goodsId " + "not in(select top " + requestRecord + " goodsId from " + tabName + " order by saleCount desc) " + "order by saleCount desc"; try { con = DBConn.getConn(); stmt = con.createStatement(); rs = stmt.executeQuery(sql); while (rs.next()) { Goods gs = new Goods(); gs.setGoodsId(rs.getInt("goodsId")); gs.setGoodsName(rs.getString("goodsName")); gs.setPrice(rs.getFloat("price")); gs.setSalePrice(rs.getFloat("salePrice")); gs.setDescriptions(rs.getString("descriptions")); gs.setSaleCount(rs.getInt("saleCount")); gs.setIsExist(rs.getInt("isExist")); list.add(gs); } } catch (SQLException se) { se.printStackTrace(); } finally { try { rs.close(); stmt.close(); con.close(); } catch (SQLException se) { se.printStackTrace(); } } return list; } /* * 根据ID查询 */ public List getGoods(Goods gs) { List list = new ArrayList(); String sql = "select * from goods where 1=1"; try { con = DBConn.getConn(); pstmt = con.prepareStatement(sql); if(gs.getGoodsId() != 0) { sql = sql + " and goodsId=?"; pstmt = con.prepareStatement(sql); pstmt.setInt(1, gs.getGoodsId()); } if(gs.getCategoryIdfirst() != 0 && gs.getCategoryIdsecond() != 0 && gs.getCategoryIdthird() != 0) { sql = sql + " and categoryIdfirst=? and categoryIdsecond=? and categoryIdthird=?"; pstmt = con.prepareStatement(sql); pstmt.setInt(1, gs.getCategoryIdfirst()); pstmt.setInt(2, gs.getCategoryIdsecond()); pstmt.setInt(3, gs.getCategoryIdthird()); } rs = pstmt.executeQuery(); while (rs.next()) { gs = new Goods(); gs.setGoodsId(rs.getInt("goodsId")); gs.setGoodsName(rs.getString("goodsName")); gs.setPrice(rs.getFloat("price")); gs.setSalePrice(rs.getFloat("salePrice")); gs.setDescriptions(rs.getString("descriptions")); gs.setSaleCount(rs.getInt("saleCount")); gs.setIsExist(rs.getInt("isExist")); gs.setSaleTotal(rs.getInt("saleTotal")); list.add(gs); } } catch (SQLException se) { se.printStackTrace(); } finally { try { rs.close(); pstmt.close(); con.close(); } catch (SQLException se) { se.printStackTrace(); } } return list; } /* * 商品模糊查询 */ public List goodsLike(Goods gs) { List list = new ArrayList(); String sql = "select * from goods where goodsName like ?"; try { con = DBConn.getConn(); pstmt = con.prepareStatement(sql); String name = "%"+gs.getGoodsName()+"%"; pstmt.setString(1, name); rs = pstmt.executeQuery(); while (rs.next()) { gs = new Goods(); gs.