关于java:Java数据库项目之满汉楼

文章和代码曾经归档至【Github 仓库：https://github.com/timerring/java-tutorial】或者公众号【AIShareLab】回复 java 也可获取。

程序框架图

代码实现

数据库

-- 创立满汉楼的数据库
CREATE DATABASE mhl
-- 创立表 employee 表 (主键 id, empId, name, pwd, job, 如果须要能够本人加字段等)
#用户表
CREATE TABLE employee (
    id INT PRIMARY KEY AUTO_INCREMENT, #自增
    empId VARCHAR(50) UNIQUE NOT NULL DEFAULT '',# 员工号
    pwd CHAR(32) NOT NULL DEFAULT '',# 明码 md5
    NAME VARCHAR(50) NOT NULL DEFAULT '',# 姓名
    job VARCHAR(50) NOT NULL DEFAULT '' #岗位
)CHARSET=utf8; 

DROP TABLE employee
#增加测试数据
INSERT INTO employee VALUES(NULL, '6668612', MD5('123456'), '张三丰', '经理');
INSERT INTO employee VALUES(NULL, '6668622', MD5('123456'),'小龙女', '服务员');
INSERT INTO employee VALUES(NULL, '6668633', MD5('123456'), '张无忌', '收银员');
INSERT INTO employee VALUES(NULL, '666666', MD5('123456'), '老韩', '经理');

SELECT * FROM employee;

SELECT * FROM employee WHERE empId='666666' AND pwd=MD5('123456')

-- 创立 diningTable 表(id, state , orderName, orderTel ....)

CREATE TABLE diningTable (
    id INT PRIMARY KEY AUTO_INCREMENT, #自增, 示意餐桌编号
    state VARCHAR(20) NOT NULL DEFAULT '',# 餐桌的状态
    orderName VARCHAR(50) NOT NULL DEFAULT '',# 预订人的名字
    orderTel VARCHAR(20) NOT NULL DEFAULT ''
)CHARSET=utf8; 
#测试数据
INSERT INTO diningTable VALUES(NULL, '空','','');
INSERT INTO diningTable VALUES(NULL, '空','','');
INSERT INTO diningTable VALUES(NULL, '空','','');
SELECT * FROM diningTable

SELECT * FROM diningTable

SELECT * FROM diningTable WHERE id = 1

UPDATE diningTable SET state='空', orderName='', orderTel='' WHERE id=1

-- 创立 menu 表(id, name, type, price)
#菜谱
CREATE TABLE menu (id INT PRIMARY KEY AUTO_INCREMENT, #自增主键，作为菜谱编号(惟一)
    NAME VARCHAR(50) NOT NULL DEFAULT '',# 菜品名称
    TYPE VARCHAR(50) NOT NULL DEFAULT '', #菜品品种
    price DOUBLE NOT NULL DEFAULT 0# 价格
)CHARSET=utf8; 
#测试数据
INSERT INTO menu VALUES(NULL, '八宝饭', '主食', 10);
INSERT INTO menu VALUES(NULL, '叉烧包', '主食', 20);
INSERT INTO menu VALUES(NULL, '宫保鸡丁', '热菜', 30);
INSERT INTO menu VALUES(NULL, '山药拨鱼', '凉菜', 14);
INSERT INTO menu VALUES(NULL, '银丝卷', '甜食', 9);
INSERT INTO menu VALUES(NULL, '水煮鱼', '热菜', 26);
INSERT INTO menu VALUES(NULL, '甲鱼汤', '汤类', 100);
INSERT INTO menu VALUES(NULL, '鸡蛋汤', '汤类', 16);

SELECT * FROM menu;
#同学们能够独自创立一张表，示意菜品的类别

#减少表 bill 账单表(id, billId, menuId, nums, billDate, money, state, diningTableId)
#账单流水, 思考能够离开结账, 并思考未来别离统计各个不同菜品的销售状况
CREATE TABLE bill (
    id INT PRIMARY KEY AUTO_INCREMENT, #自增主键
    billId VARCHAR(50) NOT NULL DEFAULT '',# 账单号能够依照本人规定生成 UUID
    menuId INT NOT NULL DEFAULT 0,# 菜品的编号, 也能够应用外键
    nums INT NOT NULL DEFAULT 0,# 份数
    money DOUBLE NOT NULL DEFAULT 0, #金额
    diningTableId INT NOT NULL DEFAULT 0, #餐桌
    billDate DATETIME NOT NULL ,# 订单日期
    state VARCHAR(50) NOT NULL DEFAULT ''# 状态' 未结账 ',' 曾经结账 ',' 挂单 ',' 现金 ',' 支付宝 ',' 坏账 '
)CHARSET=utf8;

SELECT * FROM bill;
select * from bill


SELECT * FROM menu WHERE id = 1

UPDATE diningTable SET state=? WHERE id=?

SELECT * FROM diningTable


-- 查出某个餐桌是否有未结账的账单
select * from bill where diningTableId=1 and state = '未结账' limit 0, 1

Java

`-- src
    |-- com
    |   `-- hspedu
    |       `-- mhl
    |           |-- dao
    |           |   |-- BasicDAO.java
    |           |   |-- BillDAO.java
    |           |   |-- DiningTableDAO.java
    |           |   |-- EmployeeDAO.java
    |           |   |-- MenuDAO.java
    |           |   `-- MultiTableDAO.java
    |           |-- domain
    |           |   |-- Bill.java
    |           |   |-- DiningTable.java
    |           |   |-- Employee.java
    |           |   |-- Menu.java
    |           |   `-- MultiTableBean.java
    |           |-- service
    |           |   |-- BillService.java
    |           |   |-- DiningTableService.java
    |           |   |-- EmployeeService.java
    |           |   `-- MenuService.java
    |           |-- utils
    |           |   |-- JDBCUtilsByDruid.java
    |           |   `-- Utility.java
    |           `-- view
    |               `-- MHLView.java
    `-- druid.properties

src/com/hspedu/mhl/dao/BasicDAO.java

package com.hspedu.mhl.dao;


import com.hspedu.mhl.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

/**
 * 开发 BasicDAO , 是其余 DAO 的父类
 */
public class BasicDAO<T> { // 泛型指定具体类型

    private QueryRunner qr =  new QueryRunner();

    // 开发通用的 dml 办法, 针对任意的表
    public int update(String sql, Object... parameters) {

        Connection connection = null;

        try {connection = JDBCUtilsByDruid.getConnection();
            int update = qr.update(connection, sql, parameters);
            return  update;
        } catch (SQLException e) {throw  new RuntimeException(e); // 将编译异样 -> 运行异样 , 抛出
        } finally {JDBCUtilsByDruid.close(null, null, connection);
        }

    }

    // 返回多个对象(即查问的后果是多行), 针对任意表

    /**
     *
     * @param sql sql 语句，能够有 ?
     * @param clazz 传入一个类的 Class 对象 比方 Actor.class
     * @param parameters 传入 ? 的具体的值，能够是多个
     * @return 依据 Actor.class 返回对应的 ArrayList 汇合
     */
    public List<T> queryMulti(String sql, Class<T> clazz, Object... parameters) {

        Connection connection = null;
        try {connection = JDBCUtilsByDruid.getConnection();
            return qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);

        } catch (SQLException e) {throw  new RuntimeException(e); // 将编译异样 -> 运行异样 , 抛出
        } finally {JDBCUtilsByDruid.close(null, null, connection);
        }

    }

    // 查问单行后果 的通用办法
    public T querySingle(String sql, Class<T> clazz, Object... parameters) {

        Connection connection = null;
        try {connection = JDBCUtilsByDruid.getConnection();
            return  qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);

        } catch (SQLException e) {throw  new RuntimeException(e); // 将编译异样 -> 运行异样 , 抛出
        } finally {JDBCUtilsByDruid.close(null, null, connection);
        }
    }

    // 查问单行单列的办法, 即返回单值的办法

    public Object queryScalar(String sql, Object... parameters) {

        Connection connection = null;
        try {connection = JDBCUtilsByDruid.getConnection();
            return  qr.query(connection, sql, new ScalarHandler(), parameters);

        } catch (SQLException e) {throw  new RuntimeException(e); // 将编译异样 -> 运行异样 , 抛出
        } finally {JDBCUtilsByDruid.close(null, null, connection);
        }
    }

}

src/com/hspedu/mhl/dao/BillDAO.java

package com.hspedu.mhl.dao;

import com.hspedu.mhl.domain.Bill;
public class BillDAO extends BasicDAO<Bill> {}

src/com/hspedu/mhl/dao/DiningTableDAO.java

package com.hspedu.mhl.dao;

import com.hspedu.mhl.domain.DiningTable;

public class DiningTableDAO extends BasicDAO<DiningTable> {// 如果有特地的操作，能够写在 DiningTableDAO}

src/com/hspedu/mhl/dao/EmployeeDAO.java

package com.hspedu.mhl.dao;

import com.hspedu.mhl.domain.Employee;

public class EmployeeDAO extends BasicDAO<Employee> {// 这里还能够写特有的操作.}

src/com/hspedu/mhl/dao/MenuDAO.java

package com.hspedu.mhl.dao;

import com.hspedu.mhl.domain.Menu;

public class MenuDAO extends BasicDAO<Menu> {}

src/com/hspedu/mhl/dao/MultiTableDAO.java

package com.hspedu.mhl.dao;

import com.hspedu.mhl.domain.MultiTableBean;

public class MultiTableDAO extends BasicDAO<MultiTableBean> {}

src/com/hspedu/mhl/domain/Bill.java

package com.hspedu.mhl.domain;

import java.util.Date;

/**
 * Bill 是 javabean 和 bill 对应
 * id int primary key auto_increment, #自增主键
 *     billId varchar(50) not null default '',# 账单号能够依照本人规定生成 UUID
 *     menuId int not null default 0,# 菜品的编号, 也能够应用外键
 *     nums int not null default 0,# 份数
 *     money double not null default 0, #金额
 *     diningTableId int not null default 0, #餐桌
 *     billDate datetime not null ,# 订单日期
 *     state varchar(50) not null default ''# 状态' 未结账 ',' 曾经结账 ',' 挂单 '
 */
public class Bill {
    private Integer id;
    private String billId;
    private Integer menuId;
    private Integer nums;
    private Double money;
    private Integer diningTableId;
    private Date billDate;
    private String state;

    public Bill() {}

    public Bill(Integer id, String billId, Integer menuId, Integer nums, Double money, Integer diningTableId, Date billDate, String state) {
        this.id = id;
        this.billId = billId;
        this.menuId = menuId;
        this.nums = nums;
        this.money = money;
        this.diningTableId = diningTableId;
        this.billDate = billDate;
        this.state = state;
    }

    public Integer getId() {return id;}

    public void setId(Integer id) {this.id = id;}

    public String getBillId() {return billId;}

    public void setBillId(String billId) {this.billId = billId;}

    public Integer getMenuId() {return menuId;}

    public void setMenuId(Integer menuId) {this.menuId = menuId;}

    public Integer getNums() {return nums;}

    public void setNums(Integer nums) {this.nums = nums;}

    public Double getMoney() {return money;}

    public void setMoney(Double money) {this.money = money;}

    public Integer getDiningTableId() {return diningTableId;}

    public void setDiningTableId(Integer diningTableId) {this.diningTableId = diningTableId;}

    public Date getBillDate() {return billDate;}

    public void setBillDate(Date billDate) {this.billDate = billDate;}

    public String getState() {return state;}

    public void setState(String state) {this.state = state;}

    @Override
    public String toString() {
        return  id +
                "\t\t" + menuId +
                "\t\t\t" + nums +
                "\t\t\t" + money +
                "\t" + diningTableId +
                "\t\t" + billDate +
                "\t\t" + state ;
    }
}

src/com/hspedu/mhl/domain/DiningTable.java

package com.hspedu.mhl.domain;

/**
 * 这是一个 javabean 和 diningTable 表对应
 *     id int primary key auto_increment, #自增, 示意餐桌编号
 *     state varchar(20) not null default '',# 餐桌的状态
 *     orderName varchar(50) not null default '',# 预订人的名字
 *     orderTel varchar(20) not null default ''
 */
public class DiningTable {
    private Integer id;
    private String state;
    private String orderName;
    private String orderTel;

    public DiningTable() {}

    public DiningTable(Integer id, String state, String orderName, String orderTel) {
        this.id = id;
        this.state = state;
        this.orderName = orderName;
        this.orderTel = orderTel;
    }

    public Integer getId() {return id;}

    public void setId(Integer id) {this.id = id;}

    public String getState() {return state;}

    public void setState(String state) {this.state = state;}

    public String getOrderName() {return orderName;}

    public void setOrderName(String orderName) {this.orderName = orderName;}

    public String getOrderTel() {return orderTel;}

    public void setOrderTel(String orderTel) {this.orderTel = orderTel;}

    @Override
    public String toString() {return id + "\t\t\t" + state;}
}

src/com/hspedu/mhl/domain/Employee.java

package com.hspedu.mhl.domain;

/**
 * 这是一个 javabean 和 employee 对应
 * id int primary key auto_increment, #自增
 *     empId varchar(50) not null default '',# 员工号
 *     pwd char(32) not null default '',# 明码 md5
 *     name varchar(50) not null default '',# 姓名
 *     job varchar(50) not null default '' #岗位
 */
public class Employee {
    private Integer id;
    private String empId;
    private String pwd;
    private String name;
    private String job;

    public Employee() { // 无参结构器，底层 apache-dbutils 反射须要}

    public Employee(Integer id, String empId, String pwd, String name, String job) {
        this.id = id;
        this.empId = empId;
        this.pwd = pwd;
        this.name = name;
        this.job = job;
    }

    public Integer getId() {return id;}

    public void setId(Integer id) {this.id = id;}

    public String getEmpId() {return empId;}

    public void setEmpId(String empId) {this.empId = empId;}

    public String getPwd() {return pwd;}

    public void setPwd(String pwd) {this.pwd = pwd;}

    public String getName() {return name;}

    public void setName(String name) {this.name = name;}

    public String getJob() {return job;}

    public void setJob(String job) {this.job = job;}
}

src/com/hspedu/mhl/domain/Menu.java

package com.hspedu.mhl.domain;

/**
 * 该类 (javabean) 和 menu 表对应
 * id int primary key auto_increment, #自增主键，作为菜谱编号(惟一)
 * name varchar(50) not null default '',# 菜品名称
 * type varchar(50) not null default '', #菜品品种
 * price double not null default 0# 价格
 */
public class Menu {
    private Integer id;
    private String name;
    private String type;
    private Double price;

    public Menu() {// 无参结构器}

    public Menu(Integer id, String name, String type, Double price) {
        this.id = id;
        this.name = name;
        this.type = type;
        this.price = price;
    }

    public Integer getId() {return id;}

    public void setId(Integer id) {this.id = id;}

    public String getName() {return name;}

    public void setName(String name) {this.name = name;}

    public String getType() {return type;}

    public void setType(String type) {this.type = type;}

    public Double getPrice() {return price;}

    public void setPrice(Double price) {this.price = price;}

    @Override
    public String toString() {return id + "\t\t\t" + name + "\t\t" + type + "\t\t" + price;}
}

src/com/hspedu/mhl/domain/MultiTableBean.java

package com.hspedu.mhl.domain;

import java.util.Date;

/**
 * 这是一个 javabean 能够和多张表进行对应
 */
public class MultiTableBean {
    private Integer id;
    private String billId;
    private Integer menuId;
    private Integer nums;
    private Double money;
    private Integer diningTableId;
    private Date billDate;
    private String state;
    // 减少一个来自 menu 表的列 name
    // 思考 这里的属性名是否肯定要和表的列名保持一致.
    // 答: 能够不统一，然而须要 sql 做相应的批改, 标准须要保持一致（例如 SELECT Name AS name2  他会调用对应的 setName2 构造方法）.
    private String name;
    // 减少来自 menu 表的列 price
    private Double price;// 默认值 nulll

    // 实际上是调用无参结构器，进行反射调用，而后通过 setter 和 getter 办法进行调用的：public MultiTableBean() {System.out.println("反射调用....");
    }

//    public MultiTableBean(Integer id, String billId, Integer menuId, Integer nums, Double money, Integer diningTableId, Date billDate, String state, String name, Double price) {
//        this.id = id;
//        this.billId = billId;
//        this.menuId = menuId;
//        this.nums = nums;
//        this.money = money;
//        this.diningTableId = diningTableId;
//        this.billDate = billDate;
//        this.state = state;
//        this.name = name;
//        this.price = price;
//    }

    // 给 price 生成 setter 和 getter

    public Double getPrice() {return price;}

    public void setPrice(Double price) {this.price = price;}


    // 给 name 生成 setter 和 getter


    public String getName() {return name;}

    public void setName(String name) {this.name = name;}

    public Integer getId() {return id;}

    public void setId(Integer id) {this.id = id;}

    public String getBillId() {return billId;}

    public void setBillId(String billId) {this.billId = billId;}

    public Integer getMenuId() {return menuId;}

    public void setMenuId(Integer menuId) {this.menuId = menuId;}

    public Integer getNums() {return nums;}

    public void setNums(Integer nums) {this.nums = nums;}

    public Double getMoney() {return money;}

    public void setMoney(Double money) {this.money = money;}

    public Integer getDiningTableId() {return diningTableId;}

    public void setDiningTableId(Integer diningTableId) {this.diningTableId = diningTableId;}

    public Date getBillDate() {return billDate;}

    public void setBillDate(Date billDate) {this.billDate = billDate;}

    public String getState() {return state;}

    public void setState(String state) {this.state = state;}

    @Override
    public String toString() {
        return  id +
                "\t\t" + menuId +
                "\t\t\t" + nums +
                "\t\t\t" + money +
                "\t" + diningTableId +
                "\t\t" + billDate +
                "\t\t" + state +
                "\t\t" + name +
                "\t\t" + price;
    }
}

src/com/hspedu/mhl/service/BillService.java

package com.hspedu.mhl.service;

import com.hspedu.mhl.dao.BillDAO;
import com.hspedu.mhl.dao.MultiTableDAO;
import com.hspedu.mhl.domain.Bill;
import com.hspedu.mhl.domain.MultiTableBean;

import java.util.List;
import java.util.UUID;

/**
 * 解决和账单相干的业务逻辑
 */
public class BillService {
    // 定义 BillDAO 属性
    private BillDAO billDAO = new BillDAO();
    // 定义 MenuService 属性
    private MenuService menuService = new MenuService();
    // 定义 DiningTableService 属性
    private DiningTableService diningTableService = new DiningTableService();

    private MultiTableDAO multiTableDAO = new MultiTableDAO();

    // 思考
    // 编写点餐的办法
    //1. 生成账单
    //2. 须要更新对应餐桌的状态
    //3. 如果胜利返回 true, 否则返回 false
    public boolean orderMenu(int menuId, int nums, int diningTableId) {
        // 生成一个账单号,UUID
        String billID = UUID.randomUUID().toString();

        // 将账单生成到 bill 表, 要求间接计算账单金额
        int update = billDAO.update("insert into bill values(null,?,?,?,?,?,now(),' 未结账 ')",
                billID, menuId, nums, menuService.getMenuById(menuId).getPrice() * nums, diningTableId);

        if (update <= 0) {return false;}

        // 须要更新对应餐桌的状态
        return diningTableService.updateDiningTableState(diningTableId, "就餐中");

    }

    // 返回所有的账单，提供给 View 调用
    public List<Bill> list() {return billDAO.queryMulti("select * from bill", Bill.class);
    }

    // 返回所有的账单并带有菜品名, 价格，提供给 View 调用
    public List<MultiTableBean> list2() {
        return multiTableDAO.queryMulti("SELECT bill.*, NAME" +
                "FROM bill, menu" +
                "WHERE bill.menuId = menu.id", MultiTableBean.class);
    }


    // 查看某个餐桌是否有未结账的账单
    public boolean hasPayBillByDiningTableId(int diningTableId) {

        Bill bill =
                billDAO.querySingle("SELECT * FROM bill WHERE diningTableId=? AND state =' 未结账 'LIMIT 0, 1", Bill.class, diningTableId);
        return bill != null;
    }

    // 实现结账[如果餐桌存在，并且该餐桌有未结账的账单]
    // 如果胜利，返回 true, 失败返回 false
    public boolean payBill(int diningTableId, String payMode) {

        // 如果这里应用事务的话，须要用 ThreadLocal 来解决 , 框架中比方 mybatis 提供了事务反对
        //1. 批改 bill 表
        int update = billDAO.update("update bill set state=? where diningTableId=? and state=' 未结账 '", payMode, diningTableId);

        if(update <= 0) { // 如果更新没有胜利，则示意失败...
            return false;
        }
        //2. 批改 diningTable 表
        // 留神：不要间接在这里操作，而应该调用 DiningTableService 办法, 实现更新，体现各司其职

        if(!diningTableService.updateDiningTableToFree(diningTableId, "空")) {return false;}
        return true;
    }
}

src/com/hspedu/mhl/service/DiningTableService.java

package com.hspedu.mhl.service;

import com.hspedu.mhl.dao.DiningTableDAO;
import com.hspedu.mhl.domain.DiningTable;

import java.util.List;

public class DiningTableService { // 业务层
    // 定义一个 DiningTableDAO 对象
    private DiningTableDAO diningTableDAO = new DiningTableDAO();

    // 返回所有餐桌的信息
    public List<DiningTable> list() {return diningTableDAO.queryMulti("select id, state from diningTable", DiningTable.class);
    }

    // 依据 id , 查问对应的餐桌 DiningTable 对象
    //, 如果返回 null , 示意 id 编号对应的餐桌不存在
    public DiningTable getDiningTableById(int id) {

        // 小技巧：把 sql 语句放在查问分析器去测试一下.
        return diningTableDAO.querySingle("select * from diningTable where id = ?", DiningTable.class, id);
    }

    // 如果餐桌能够预约，调用办法，对其状态进行更新(包含预约人的名字和电话)
    public boolean orderDiningTable(int id, String orderName, String orderTel) {

        int update =
                diningTableDAO.update("update diningTable set state=' 曾经预约 ', orderName=?, orderTel=? where id=?", orderName, orderTel, id);

        return  update > 0;
    }

    // 须要提供一个更新 餐桌状态的办法
    public boolean updateDiningTableState(int id, String state) {int update = diningTableDAO.update("update diningTable set state=? where id=?", state, id);
        return update > 0;
    }

    // 提供办法，将指定的餐桌设置为闲暇状态
    public boolean updateDiningTableToFree(int id, String state) {int update = diningTableDAO.update("update diningTable set state=?,orderName='',orderTel=''where id=?", state, id);
        return update > 0;
    }

}

src/com/hspedu/mhl/service/EmployeeService.java

package com.hspedu.mhl.service;

import com.hspedu.mhl.dao.EmployeeDAO;
import com.hspedu.mhl.domain.Employee;

/**
 * 该类实现对 employee 表的各种操作(通过调用 EmployeeDAO 对象实现)
 */
public class EmployeeService {

    // 定义一个 EmployeeDAO 属性
    private EmployeeDAO employeeDAO = new EmployeeDAO();

    // 办法，依据 empId 和 pwd 返回一个 Employee 对象
    // 如果查问不到，就返回 null
    public Employee getEmployeeByIdAndPwd(String empId, String pwd) {return employeeDAO.querySingle("select * from employee where empId=? and pwd=md5(?)", Employee.class, empId, pwd);

    }
}

src/com/hspedu/mhl/service/MenuService.java

package com.hspedu.mhl.service;

import com.hspedu.mhl.dao.MenuDAO;
import com.hspedu.mhl.domain.Menu;

import java.util.List;

/**
 * 实现对 menu 表的各种操作(通过调用 MenuDAO)
 */
public class MenuService {

    // 定义 MenuDAO 属性
    private MenuDAO menuDAO = new MenuDAO();

    // 返回所有的菜品, 返回给界面应用
    public List<Menu> list() {return menuDAO.queryMulti("select * from menu", Menu.class);
    }

    // 须要办法，依据 id, 返回 Menu 对象
    public Menu getMenuById(int id) {return menuDAO.querySingle("select * from menu where id = ?", Menu.class, id);
    }
}

src/com/hspedu/mhl/utils/JDBCUtilsByDruid.java

package com.hspedu.mhl.utils;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * 基于 druid 数据库连接池的工具类
 */
public class JDBCUtilsByDruid {

    private static DataSource ds;

    // 在动态代码块实现 ds 初始化
    static {Properties properties = new Properties();
        try {properties.load(new FileInputStream("src\\druid.properties"));
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {e.printStackTrace();
        }

    }

    // 编写 getConnection 办法
    public static Connection getConnection() throws SQLException {return ds.getConnection();
    }

    // 敞开连贯, 老师再次强调：在数据库连接池技术中，close 不是真的断掉连贯
    // 而是把应用的 Connection 对象放回连接池
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {

        try {if (resultSet != null) {resultSet.close();
            }
            if (statement != null) {statement.close();
            }
            if (connection != null) {connection.close();
            }
        } catch (SQLException e) {throw new RuntimeException(e);
        }
    }
}

src/com/hspedu/mhl/utils/Utility.java

package com.hspedu.mhl.utils;


/**
   工具类的作用:
   解决各种状况的用户输出，并且可能依照程序员的需要，失去用户的控制台输出。*/

import java.util.*;
/**

   
*/
public class Utility {
   // 动态属性。。。private static Scanner scanner = new Scanner(System.in);

    
    /**
     * 性能：读取键盘输入的一个菜单选项，值：1——5 的范畴
     * @return 1——5
     */
   public static char readMenuSelection() {
        char c;
        for (; ;) {String str = readKeyBoard(1, false);// 蕴含一个字符的字符串
            c = str.charAt(0);// 将字符串转换成字符 char 类型
            if (c != '1' && c != '2' && 
                c != '3' && c != '4' && c != '5') {System.out.print("抉择谬误，请从新输出：");
            } else break;
        }
        return c;
    }

   /**
    * 性能：读取键盘输入的一个字符
    * @return 一个字符
    */
    public static char readChar() {String str = readKeyBoard(1, false);// 就是一个字符
        return str.charAt(0);
    }
    /**
     * 性能：读取键盘输入的一个字符，如果间接按回车，则返回指定的默认值；否则返回输出的那个字符
     * @param defaultValue 指定的默认值
     * @return 默认值或输出的字符
     */
    
    public static char readChar(char defaultValue) {String str = readKeyBoard(1, true);// 要么是空字符串，要么是一个字符
        return (str.length() == 0) ? defaultValue : str.charAt(0);
    }
   
    /**
     * 性能：读取键盘输入的整型，长度小于 2 位
     * @return 整数
     */
    public static int readInt() {
        int n;
        for (; ;) {String str = readKeyBoard(2, false);// 一个整数，长度 <= 2 位
            try {n = Integer.parseInt(str);// 将字符串转换成整数
                break;
            } catch (NumberFormatException e) {System.out.print("数字输出谬误，请从新输出：");
            }
        }
        return n;
    }
    /**
     * 性能：读取键盘输入的 整数或默认值，如果间接回车，则返回默认值，否则返回输出的整数
     * @param defaultValue 指定的默认值
     * @return 整数或默认值
     */
    public static int readInt(int defaultValue) {
        int n;
        for (; ;) {String str = readKeyBoard(10, true);
            if (str.equals("")) {return defaultValue;}
         
         // 异样解决...
            try {n = Integer.parseInt(str);
                break;
            } catch (NumberFormatException e) {System.out.print("数字输出谬误，请从新输出：");
            }
        }
        return n;
    }

    /**
     * 性能：读取键盘输入的指定长度的字符串
     * @param limit 限度的长度
     * @return 指定长度的字符串
     */

    public static String readString(int limit) {return readKeyBoard(limit, false);
    }

    /**
     * 性能：读取键盘输入的指定长度的字符串或默认值，如果间接回车，返回默认值，否则返回字符串
     * @param limit 限度的长度
     * @param defaultValue 指定的默认值
     * @return 指定长度的字符串
     */
   
    public static String readString(int limit, String defaultValue) {String str = readKeyBoard(limit, true);
        return str.equals("")? defaultValue : str;
    }


   /**
    * 性能：读取键盘输入的确认选项，Y 或 N
    * 将小的性能，封装到一个办法中.
    * @return Y 或 N
    */
    public static char readConfirmSelection() {System.out.print("确认是否预订(Y/N):");
        char c;
        for (; ;) {// 有限循环
           // 在这里，将承受到字符，转成了大写字母
           //y => Y n=>N
            String str = readKeyBoard(1, false).toUpperCase();
            c = str.charAt(0);
            if (c == 'Y' || c == 'N') {break;} else {System.out.print("抉择谬误，请从新输出：");
            }
        }
        return c;
    }

    /**
     * 性能：读取一个字符串
     * @param limit 读取的长度
     * @param blankReturn 如果为 true , 示意 能够读空字符串。*                   如果为 false 示意 不能读空字符串。*           
    * 如果输出为空，或者输出大于 limit 的长度，就会提醒从新输出。* @return
     */
    private static String readKeyBoard(int limit, boolean blankReturn) {
        
      // 定义了字符串
      String line = "";

      //scanner.hasNextLine() 判断有没有下一行
        while (scanner.hasNextLine()) {line = scanner.nextLine();// 读取这一行
           
         // 如果 line.length=0, 即用户没有输出任何内容，间接回车
         if (line.length() == 0) {if (blankReturn) return line;// 如果 blankReturn=true, 能够返回空串
                else continue; // 如果 blankReturn=false, 不承受空串，必须输出内容
            }

         // 如果用户输出的内容大于了 limit，就提醒重写输出  
         // 如果用户如的内容 >0 <= limit , 我就承受
            if (line.length() < 1 || line.length() > limit) {System.out.print("输出长度（不能大于" + limit + "）谬误，请从新输出：");
                continue;
            }
            break;
        }

        return line;
    }
}

src/com/hspedu/mhl/view/MHLView.java

package com.hspedu.mhl.view;

import com.hspedu.mhl.domain.*;
import com.hspedu.mhl.service.BillService;
import com.hspedu.mhl.service.DiningTableService;
import com.hspedu.mhl.service.EmployeeService;
import com.hspedu.mhl.service.MenuService;
import com.hspedu.mhl.utils.Utility;

import java.util.List;
import java.util.UUID;

/**
 * 这是主界面
 */
public class MHLView {

    // 管制是否退出菜单
    private boolean loop = true;
    private String key = ""; // 接管用户的抉择
    // 定义 EmployeeService 属性
    private EmployeeService employeeService = new EmployeeService();
    // 定义 DiningTableService 的属性
    private DiningTableService diningTableService = new DiningTableService();
    // 定义 MenuService 属性
    private MenuService menuService = new MenuService();
    // 定义 BillService 属性
    private BillService billService = new BillService();

    public static void main(String[] args) {new MHLView().mainMenu();}

    // 实现结账
    public void payBill() {System.out.println("============== 结账服务 ============");
        System.out.print("请抉择要结账的餐桌编号(- 1 退出):");
        int diningTableId = Utility.readInt();
        if (diningTableId == -1) {System.out.println("============= 勾销结账 ============");
            return;
        }
        // 验证餐桌是否存在
        DiningTable diningTable = diningTableService.getDiningTableById(diningTableId);
        if (diningTable == null) {System.out.println("============= 结账的餐桌不存在 ============");
            return;
        }
        // 验证餐桌是否有须要结账的账单
        if (!billService.hasPayBillByDiningTableId(diningTableId)) {System.out.println("============= 该餐位没有未结账账单 ============");
            return;
        }
        System.out.print("结账形式 (现金 / 支付宝 / 微信) 回车示意退出:");
        String payMode = Utility.readString(20, "");// 阐明如果回车，就是返回""
        if ("".equals(payMode)) {System.out.println("============= 勾销结账 ============");
            return;
        }
        char key = Utility.readConfirmSelection();
        if (key == 'Y') { // 结账

            // 调用咱们写的办法
            if (billService.payBill(diningTableId, payMode)) {System.out.println("============= 实现结账 ============");
            } else {System.out.println("============= 结账失败 ============");
            }

        } else {System.out.println("============= 勾销结账 ============");
        }
    }

    // 显示账单信息
    public void listBill() {//        List<Bill> bills = billService.list();
//        System.out.println("\n 编号 \t\t 菜品号 \t\t 菜品量 \t\t 金额 \t\t 桌号 \t\t 日期 \t\t\t\t\t\t\t 状态");
//        for (Bill bill : bills) {//            System.out.println(bill);
//        }
//        System.out.println("============== 显示结束 ============");

        List<MultiTableBean> multiTableBeans = billService.list2();
        System.out.println("\n 编号 \t\t 菜品号 \t\t 菜品量 \t\t 金额 \t\t 桌号 \t\t 日期 \t\t\t\t\t\t\t 状态 \t\t 菜品名 \t\t 价格");
        for (MultiTableBean bill : multiTableBeans) {System.out.println(bill);
        }
        System.out.println("============== 显示结束 ============");
    }

    // 实现点餐
    public void orderMenu() {System.out.println("============== 点餐服务 ============");
        System.out.print("请输出点餐的桌号(- 1 退出):");
        int orderDiningTableId = Utility.readInt();
        if (orderDiningTableId == -1) {System.out.println("============== 勾销点餐 ============");
            return;
        }
        System.out.print("请输出点餐的菜品号(- 1 退出):");
        int orderMenuId = Utility.readInt();
        if (orderMenuId == -1) {System.out.println("============== 勾销点餐 ============");
            return;
        }
        System.out.print("请输出点餐的菜品量(- 1 退出):");
        int orderNums = Utility.readInt();
        if (orderNums == -1) {System.out.println("============== 勾销点餐 ============");
            return;
        }

        // 验证餐桌号是否存在.
        DiningTable diningTable = diningTableService.getDiningTableById(orderDiningTableId);
        if (diningTable == null) {System.out.println("============== 餐桌号不存在 ============");
            return;
        }
        // 验证菜品编号
        Menu menu = menuService.getMenuById(orderMenuId);
        if (menu == null) {System.out.println("============== 菜品号不存在 ============");
            return;
        }

        // 点餐
        if (billService.orderMenu(orderMenuId, orderNums, orderDiningTableId)) {System.out.println("============== 点餐胜利 ============");
        } else {System.out.println("============== 点餐失败 ============");
        }
    }

    // 显示所有菜品
    public void listMenu() {List<Menu> list = menuService.list();
        System.out.println("\n 菜品编号 \t\t 菜品名 \t\t 类别 \t\t 价格");
        for (Menu menu : list) {System.out.println(menu);
        }
        System.out.println("============== 显示结束 ============");
    }

    // 实现订座
    public void orderDiningTable() {System.out.println("============== 预约餐桌 ============");
        System.out.print("请抉择要预约的餐桌编号(- 1 退出):");
        int orderId = Utility.readInt();
        if (orderId == -1) {System.out.println("============== 勾销预订餐桌 ============");
            return;
        }
        // 该办法失去的是 Y 或者 N
        char key = Utility.readConfirmSelection();
        if (key == 'Y') {// 要预约

            // 依据 orderId 返回 对应 DiningTable 对象, 如果为 null, 阐明该对象不存在
            DiningTable diningTable = diningTableService.getDiningTableById(orderId);
            if (diningTable == null) {//
                System.out.println("============== 预订餐桌不存在 ============");
                return;
            }
            // 判断该餐桌的状态是否 "空"
            if (!("空".equals(diningTable.getState()))) {// 阐明以后这个餐桌不是 "空" 状态
                System.out.println("============== 该餐桌曾经预约或者就餐中 ============");
                return;
            }

            // 接管预约信息
            System.out.print("预约人的名字:");
            String orderName = Utility.readString(50);
            System.out.print("预约人的电话:");
            String orderTel = Utility.readString(50);

            // 更新餐桌状态
            if (diningTableService.orderDiningTable(orderId, orderName, orderTel)) {System.out.println("============== 预订餐桌胜利 ============");
            } else {System.out.println("============== 预订餐桌失败 ============");
            }

        } else {System.out.println("============== 勾销预订餐桌 ============");
        }

    }

    // 显示所有餐桌状态
    public void listDiningTable() {List<DiningTable> list = diningTableService.list();
        System.out.println("\n 餐桌编号 \t\t 餐桌状态");
        for (DiningTable diningTable : list) {System.out.println(diningTable);
        }
        System.out.println("============== 显示结束 ============");
    }

    // 显示主菜单
    public void mainMenu() {while (loop) {System.out.println("\n=============== 满汉楼 ================");
            System.out.println("\t\t 1 登录满汉楼");
            System.out.println("\t\t 2 退出满汉楼");
            System.out.print("请输出你的抉择:");
            key = Utility.readString(1);
            switch (key) {
                case "1":
                    System.out.print("输出员工号:");
                    String empId = Utility.readString(50);
                    System.out.print("输出密  码:");
                    String pwd = Utility.readString(50);
                    Employee employee = employeeService.getEmployeeByIdAndPwd(empId, pwd);
                    if (employee != null) { // 阐明存在该用户
                        System.out.println("=============== 登录胜利[" + employee.getName() + "]================\n");
                        // 显示二级菜单, 这里二级菜单是循环操作，所以做成 while
                        while (loop) {System.out.println("\n=============== 满汉楼(二级菜单)================");
                            System.out.println("\t\t 1 显示餐桌状态");
                            System.out.println("\t\t 2 预约餐桌");
                            System.out.println("\t\t 3 显示所有菜品");
                            System.out.println("\t\t 4 点餐服务");
                            System.out.println("\t\t 5 查看账单");
                            System.out.println("\t\t 6 结账");
                            System.out.println("\t\t 9 退出满汉楼");
                            System.out.print("请输出你的抉择:");
                            key = Utility.readString(1);
                            switch (key) {
                                case "1":
                                    listDiningTable();// 显示餐桌状态
                                    break;
                                case "2":
                                    orderDiningTable();
                                    break;
                                case "3":
                                    listMenu();
                                    break;
                                case "4":
                                    orderMenu();
                                    break;
                                case "5":
                                    listBill();// 显示所有账单
                                    break;
                                case "6":
                                    payBill();
                                    break;
                                case "9":
                                    loop = false;
                                    break;
                                default:
                                    System.out.println("你的输出有误，请从新输出");
                                    break;
                            }
                        }
                    } else {System.out.println("=============== 登录失败 ================");
                    }
                    break;
                case "2":
                    loop = false;//
                    break;
                default:
                    System.out.println("你输出有误，请从新输出.");
            }
        }
        System.out.println("你退出了满汉楼零碎~");
    }

}

src/druid.properties

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mhl?rewriteBatchedStatements=true
username=root
password=hsp
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=50
#max wait time (5000 mil seconds)
maxWait=5000

mhl.iml

<?xml version="1.0" encoding="UTF-8"?>
<module type="JAVA_MODULE" version="4">
  <component name="NewModuleRootManager" inherit-compiler-output="true">
    <exclude-output />
    <content url="file://$MODULE_DIR$">
      <sourceFolder url="file://$MODULE_DIR$/src" isTestSource="false" />
    </content>
    <orderEntry type="inheritedJdk" />
    <orderEntry type="sourceFolder" forTests="false" />
    <orderEntry type="library" name="commons-dbutils-1.3" level="project" />
  </component>
</module>

多表查问思路

能够用 MapListHandler 实现：

https://www.pianshen.com/article/48341042440/

也能够另写 MultiTabBean 类，映射多个表，如上所示。