java实现多表联查（从数据库查询数据）

实现购物管理系统的购物结算功能，选择商品的id，生成消费清单。

其中要调用数据库判断库存是否满足所需数量，生成消费清单后要在数据库orders表中添加一条购买记录。

难点在于，数据库的多表查询在dao层如何调用，这里采用创建一个多表联查的实体类，把要查询的字段放在一个entity中new

dao包实现与数据库的连接查询

service包实现业务逻辑

view包实现界面

entity包实现实体类

主界面

 public static void shopping() throws SQLException, IllegalAccessException {
        System.out.println("购物管理系统 > 购物结算\n");
        System.out.println("");
        System.out.println("********************************************************");
        System.out.println("请选择购买的商品编号：");
        //显示商品
        List<Goods> list = ordersService.getGoods();
        while(true){
        for (Goods goods : list) {
            System.out.print(goods.getId() + ":\t" + goods.getBrand() + goods.getGoodsName());
            System.out.println();
        }
        //输入商品编号选择购买的商品 输入商品数量
        System.out.print("请输入商品编号：");
        int goodsId = ShopUI.scanner.nextInt();
        System.out.print("请输入购买的商品数量：");
        int goodsNum = ShopUI.scanner.nextInt();
        //判断库存是否满足购买数量
        if(ordersService.isEnough(goodsId,goodsNum)) {
            System.out.println("********************************************************");
            System.out.print("请输入会员号：");
            int memberNumber = ShopUI.scanner.nextInt();
            if(memberService.isMemberExists(memberNumber)) {
                //添加一条订单记录
                orders.setMemberNum(memberNumber);
                orders.setGoodsId(goodsId);
                orders.setNum(goodsNum);
                ordersService.addOrders(orders);

                //显示消费清单
                showBill(memberNumber);

                System.out.println("请按'n'返回上一级菜单");
                String str = ShopUI.scanner.next();
                if (str.equals("n")) {
                    ShopUI.vipManage();
                    break;
                }

            }else {
                System.out.println("该会员号不存在！请重新操作");
            }
        }else {
            System.out.println("该商品库存不足！");
        }
        }
    }

这里的while（true）用于循环调用，如果符合，break跳出循环，不符合从头开始循环

显示商品：从数据库查询，JDBCUtils是自己写的一个连接查询数据库的通用工具类。

dao包

定义一个全局变量，减少重复代码

OrdersDao ordersDao;
MemberDao memberDao;

{
    ordersDao = new OrdersDao();
    memberDao = new MemberDao();
}

 //显示所有商品信息
    public List<Goods> selectAll() throws SQLException {
        String sql = "select id,brand,goods_name,price,stock,discount from goods";
        List<Goods> list = JDBCUtils.select(Goods.class, sql, null);
        return list;
    }

 //通过id查询
    public Goods findById(Integer id) throws SQLException {
        String sql = "select id,brand,goods_name,price,stock,discount from goods where id=?";
        return JDBCUtils.findOne(Goods.class, sql, id);
    }

 

 //查询订单记录 根据会员号
    //多表联查，创建一个要查询字段的实体类
    public List<OrdersMember> selectBill(Integer memberNum) throws SQLException {
        String sql = "select o.member_num, brand, goods_name, price, num from orders o,goods g,member m where o.goods_id=g.id and o.member_num=m.member_num and m.member_num=?";

        List<OrdersMember> list =JDBCUtils.select(OrdersMember.class,sql,memberNum);
      /*  String sql = "select * from orders where member_num=?";
        List<Orders> list = JDBCUtils.select(Orders.class,sql,memberNum);*/
        return list;
    }

 

 //更新库存
    public void update(Goods goods) throws SQLException {
        String sql = "update goods set stock=? where id=?";
        JDBCUtils.update(sql,goods.getStock(),goods.getId());
    }

 service包

 public List<Goods> getGoods() {
        try {
            return ordersDao.selectAll();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

  //判断库存是否足够
    public boolean isEnough(int id, int number) throws SQLException {
        Goods goods = ordersDao.findById(id);
        if (goods != null && goods.getStock() >= number) {
            return true;
        }
        return false;
    }

public List<OrdersMember> getBill(Integer memberNum) throws SQLException {
        return ordersDao.selectBill(memberNum);
    }

消费清单界面

 public static void showBill(Integer memberNum) throws SQLException {
        System.out.println("*************************消费清单***************************");
        System.out.println("物品\t\t\t\t单价\t\t\t\t个数\t\t\t\t金额");

        List<OrdersMember> list = ordersService.getBill(memberNum);
        BigDecimal total = new BigDecimal(0);
        for (OrdersMember om : list) {
            System.out.print(om.getBrand()+om.getGoodsName()+"\t\t");
            System.out.print("¥" + om.getPrice()+"\t\t\t\t");
            System.out.print(om.getNum()+"\t\t\t\t");
            BigDecimal price = om.getPrice();
            BigDecimal prices = price.multiply(new BigDecimal(om.getNum()));
            System.out.print("¥" +prices);
            total = total.add(prices);
            System.out.println();
        }
        System.out.println("折扣：" + DISCOUNT);
        System.out.println("金额总计： "+ total.multiply(new BigDecimal(DISCOUNT)).setScale(2,BigDecimal.ROUND_UP));
        System.out.println("实际消费： ");
        System.out.println("********************************************************");
    }