超市信息管理系统，SQL Server + IDEA + JAVA 图形用户界面.zip

package com.weidong.datebase.impl; import com.weidong.datebase.SaleSQL; import com.weidong.datebase.impl.superclass.BaseSQL; import com.weidong.entity.*; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashSet; import java.util.List; import java.util.Set; public class SaleSQL_Impl extends BaseSQL implements SaleSQL { public static void main(String[] args) { SaleSQL_Impl impl = new SaleSQL_Impl(); } @Override public List<Sale> queryAllSale() { Connection conn = null; PreparedStatement pStmt = null; ResultSet rs = null; List<Sale> list = new ArrayList<>(); String sql1 = "select * from sale where "+SaleSQL.IS_NORMAL_SALE; try { conn = getConn(); pStmt = conn.prepareStatement(sql1); rs = pStmt.executeQuery(); while (rs.next()) { Sale sale = new Sale(); sale.setId(rs.getInt("sale_id")); sale.setName(rs.getString("sale_name")); sale.setPrice(rs.getDouble("sale_price")); sale.setCustomerPurchase(null); sale.setSaleMakeup(new Makeup()); sale.getSaleMakeup().setSale(sale); sale.getSaleMakeup().setMakeup(new HashSet<>()); //makeup准备初始。 list.add(sale); //第一层，sale初始载入完成。 this.loadMakeupToSale(sale,sale.getId(),conn); } } catch (SQLException e) { e.printStackTrace(); } finally { closeAll(conn,pStmt,rs); } return list; } /*本类的方法提炼-1。 * 使用：适合数据库访问正进行到一半时。 * 前提：sale的各个属性已载入或初始完成， * 否则：可能会有空指针。 * 提示：本方法中不会关闭conn。方法创建并关闭statement和resultSet，不影响主调函数的操作。 * */ private void loadMakeupToSale(Sale sale, int id, Connection conn){ PreparedStatement pStmt2 = null; PreparedStatement pStmt3 = null; PreparedStatement pStmt3_1 = null; ResultSet rs2 = null; ResultSet rs3 = null; ResultSet rs3_1 = null; String sql2 = "select goods.goods_id, goods_name, goods_type, number_c, number_n " + "from makeup, goods " + "where makeup.goods_id = goods.goods_id " + "and makeup.sale_id = ?"; String sql3 = "select * from import where import.goods_id = ?"; String sql3_1 = "select * from remove where remove.goods_id = ?"; try { pStmt2 = conn.prepareStatement(sql2); pStmt2.setObject(1,id); rs2 = pStmt2.executeQuery(); while (rs2.next()) { Makeup.Node node = new Makeup.Node(); node.setGoods(new Goods()); node.getGoods().setId(rs2.getInt("goods_id")); node.getGoods().setName(rs2.getString("goods_name")); node.getGoods().setType(rs2.getString("goods_type")); node.getGoods().setC(rs2.getInt("number_c")); node.getGoods().setComposeSale(null); //不向上层载入 node.getGoods().setImports(new ArrayList<>()); node.getGoods().setRemoves(new ArrayList<>()); node.setN(rs2.getInt("number_n")); sale.getSaleMakeup().getMakeup().add(node); //第二层，node对应goods全载入完成. pStmt3 = conn.prepareStatement(sql3); pStmt3.setObject(1,node.getGoods().getId()); rs3 = pStmt3.executeQuery(); while (rs3.next()) { Import imp = new Import(); imp.setId(rs3.getInt("import_id")); imp.setGoods(node.getGoods()); imp.setA(rs3.getInt("number_a")); imp.setDate(rs3.getDate("import_date")); node.getGoods().getImports().add(imp); //第三层，imp全载入完成。 } pStmt3_1 = conn.prepareStatement(sql3_1); pStmt3_1.setObject(1,node.getGoods().getId()); rs3_1 = pStmt3_1.executeQuery(); while (rs3_1.next()) { Remove rem = new Remove(); rem.setId(rs3_1.getInt("remove_id")); rem.setGoods(node.getGoods()); rem.setDate(rs3_1.getDate("remove_date")); rem.setO(rs3_1.getInt("number_o")); node.getGoods().getRemoves().add(rem); //第三层，rem全载入完成。 } } } catch (SQLException e) { e.printStackTrace(); } finally { closeAll(null,pStmt2,rs2); closeAll(null,pStmt3,rs3); closeAll(null,pStmt3_1,rs3_1); } } @Override public Sale querySaleById(int id) { Connection conn = null; PreparedStatement pStmt = null; ResultSet rs = null; Sale sale = null; String sql1 = "select * from sale where sale_id = ? and "+SaleSQL.IS_NORMAL_SALE; try { conn = getConn(); pStmt = conn.prepareStatement(sql1); pStmt.setObject(1,id); rs = pStmt.executeQuery(); if (rs.next()) { sale = new Sale(); sale.setId(rs.getInt("sale_id")); sale.setName(rs.getString("sale_name")); sale.setPrice(rs.getDouble("sale_price")); sale.setCustomerPurchase(null); sale.setSaleMakeup(new Makeup()); sale.getSaleMakeup().setSale(sale); sale.getSaleMakeup().setMakeup(new HashSet<>()); //sale初步全载入完成。 this.loadMakeupToSale(sale,sale.getId(),conn); } } catch (SQLException e) { e.printStackTrace(); } finally { closeAll(conn,pStmt,rs); } return sale; } @Override public List<Sale> querySaleLikeName(String info) { Connection conn = null; PreparedStatement pStmt = null; ResultSet rs = null; List<Sale> list = new ArrayList<>(); String sql1 = "select * from sale where sale_name like ? and "+SaleSQL.IS_NORMAL_SALE; try { conn = getConn(); pStmt = conn.prepareStatement(sql1); pStmt.setObject(1,"%"+info+"%"); //模糊查询，%在此处加。 rs = pStmt.executeQuery(); while (rs.next()) { Sale sale = new Sale(); sale.setId(rs.getInt("sale_id")); sale.setName(rs.getString("sale_name")); sale.setPrice(rs.getDouble("sale_price")); sale.setCustomerPurchase(null); sale.setSaleMakeup(new Makeup()); sale.getSaleMakeup().setSale(sale); sale.getSaleMakeup().setMakeup(new HashSet<>()); list.add(sale); //第一层，sale初始载入完成。 this.loadMakeupToSale(sale,sale.getId(),conn); } } catch (SQLException e) { e.printStackTrace(); } finally { closeAll(conn,pStmt,rs); } return list; } @Override public List<Sale> querySaleAndPurchaseLikeName(String info) { Connection conn = null; PreparedStatement pStmt = null; ResultSet rs = null; List<Sale> list = new ArrayList<>(); String sql1 = "select * from sale where sale_name like ? and "+SaleSQL.IS_NORMAL_SALE; try { conn = getConn(); pStmt =