jdbc满汉楼代码

参考韩顺平jdbc课程
代码还是要多敲，就算是跟着敲一遍也有一遍的收获。

一.dao包

1.BasicDao

public class BasicDao<T> {
    private QueryRunner qr=new QueryRunner();
    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);

        }
    }
    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);

        }
    }
}

2.一系列继承于BasicDao的Dao

public class EmployeeDao extends BasicDao<Employee>{

}
public class BillDao extends BasicDao<Bill>{
}
public class DiningTableDao extends BasicDao<DiningTable>{


}
public class MenuDao extends BasicDao<Menu>{
}

public class MutiTableDao extends BasicDao<MutiTable>{

}

二.domain

1.Bill

package com.aylxx.mhl.domain;

import java.util.Date;

public class Bill {
    private Integer id;
    private String billId;
    private Integer menuId;
    private  Integer nums;
    private  Double money;
    private Integer diningTableId;
    private String billDate;
    private String state;

    @Override
    public String toString() {
        return  id +

                "\t\t" + menuId +
                "\t\t\t" + nums +
                "\t\t\t\t" + money +
                "\t\t" + diningTableId +
                "\t\t" + state +
                "\t\t" + billDate ;
    }

    public Integer getId() {
        return id;
    }

    public Integer getMenuId() {
        return menuId;
    }

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

    public String getBillDate() {
        return billDate;
    }

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

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

    public String getBillId() {
        return billId;
    }

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



    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 String getState() {
        return state;
    }

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

    public Bill(Integer id, String billId, Integer menuId, Integer nums, Double money, Integer diningTableId, String 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 Bill() {
    }
}

2.DiningTable

package com.aylxx.mhl.domain;

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;
    }
}

3.employee

package com.aylxx.mhl.domain;

public class Employee {
    private  Integer id;
    private String empId;
    private String pwd;
    private String name;
    private String job;

    public Employee() {
    }

    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;
    }

    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;
    }
}

4.Menu

package com.aylxx.mhl.domain;

public class Menu {
    private Integer id;
    private String name;
    private String type;
    private Double 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;
    }

    public Menu(Integer id, String name, String type, Double price) {

        this.id = id;
        this.name = name;
        this.type = type;
        this.price = price;
    }

    public Menu() {
    }

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

三.service

1.Billservice

package com.aylxx.mhl.service;

import com.aylxx.mhl.dao.BillDao;
import com.aylxx.mhl.dao.MutiTableDao;
import com.aylxx.mhl.domain.Bill;
import com.aylxx.mhl.domain.MutiTable;

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

public class BillService {
    BillDao billDao =new BillDao();
    MenuService menuService =new MenuService();
    MutiTableDao mutiTableDao=new MutiTableDao();
    private DiningTableService diningTableService=new DiningTableService();
    public boolean orderMenu(int menuId,int nums,int diningTableId){
        String billId= UUID.randomUUID().toString();
        int update=billDao.update("insert into bill values(null,?,?,?,?,?,'未结账',now())",
        billId,menuId,nums,menuService.getMenuId(menuId).getPrice()*nums,diningTableId);
        if (update<=0)
            return false;
        return diningTableService.updateDiningTableState(diningTableId,"就餐中");
    }
    public List<Bill> list(){
        return billDao.queryMulti("select * from bill",Bill.class);
    }
    public List<MutiTable> list2(){
        return mutiTableDao.queryMulti("select bill.*,NAME " +
                "from bill,menu " +
                "where bill.menuId=menu.id ",MutiTable.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;

    }
    public boolean payBill(int dingingTableId ,String state){
        int update = billDao.update("update bill set state=? where diningTableId=?", state, dingingTableId);
        if(update<=0){
            return false;
        }
        if(!diningTableService.updateDiningTableState(dingingTableId,"空")){
            return false;
        }
        return true;

    }
}

2.diningTableService

package com.aylxx.mhl.service;

import com.aylxx.mhl.dao.DiningTableDao;
import com.aylxx.mhl.domain.DiningTable;

import java.util.List;

public class DiningTableService {
    private DiningTableDao diningTableDao = new DiningTableDao();

    public List<DiningTable> list() {

        return diningTableDao.queryMulti("select id,state from diningTable", DiningTable.class);

    }

    public DiningTable getDiningTableId(int id) {
        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;

    }

}

3.employeeService

package com.aylxx.mhl.service;

import com.aylxx.mhl.dao.EmployeeDao;
import com.aylxx.mhl.domain.Employee;

public class EmployeeService {
    private EmployeeDao employeeDao=new EmployeeDao();
    public Employee getEmployeeByIdAndPwd(String empId, String pwd){
        Employee employee = employeeDao.querySingle("select * from employee where empId=?and pwd=md5(?)", Employee.class, empId, pwd);
        return employee;


    }
}

4.MenuService

import java.util.List;

public class MenuService {
    private MenuDao menuDao=new MenuDao();
    public List<Menu> list(){
        return menuDao.queryMulti("select * from Menu",Menu.class);
    }
    public Menu getMenuId(int id){
        return menuDao.querySingle("select * from Menu where id=? ",Menu.class,id);

    }

}

四.工具类

1.JDBCUtilsByDruid

package com.aylxx.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;

public class JDBCUtilsByDruid {
    private  static DataSource ds;
    static {
        Properties properties=new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            ds= DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    public  static Connection getConnection() throws SQLException {
        return ds.getConnection();
    }
    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);
        }
    }
}

2.Utility

package com.aylxx.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.println("请输入你的选择(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;
    }
}

五.view

MHLView

package com.aylxx.mhl.view;

import com.aylxx.mhl.domain.*;
import com.aylxx.mhl.service.*;
import com.aylxx.mhl.utils.Utility;

import java.util.List;

public class MHLview {
    private boolean loop=true;
    private String key="";
    private MutiTableService mutiTableService=new MutiTableService();
    private EmployeeService employeeService=new EmployeeService();
    private DiningTableService diningTableService=new DiningTableService();
    private MenuService menuService=new MenuService();
    private BillService billService=new BillService();
    public static void main(String[] args) {
           new MHLview().mainmenue();
    }
    public void payBill(){
        System.out.println("===========结账===========");
        System.out.println("请选择要结账的桌号(-1退出)：");
        int diningTableId=Utility.readInt();
        if (diningTableId == -1 ){
            System.out.println("=========取消结账===========");
            return;
        }
        DiningTable diningTableId1 = diningTableService.getDiningTableId(diningTableId);
        if (diningTableId1 == null) {
            System.out.println("===========该桌不存在===========");
            return;
        }
         if(!billService.hasPayBillByDiningTableId(diningTableId)){
             System.out.println("===========该桌不存在未结账账单===========");
             return;
         }
        System.out.println("结账的方式：微信/支付宝/现金");
         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(){
        System.out.println("\n编号\t\t菜品号\t\t菜品量\t\t金额\t\t桌号\t\t状态\t\t日期");
        List<Bill> list = billService.list();
        for(Bill b:list){
            System.out.println(b);
        }
        System.out.println("============显示完毕=============");
    }
    public void listBill2(){
        System.out.println("\n编号\t\t菜品号\t\t菜品量\t\t金额\t\t桌号\t\t状态\t\t\t日期\t\t\t\t\t\t\t菜名");
        List<MutiTable> list = billService.list2();
        for(MutiTable b:list){
            System.out.println(b);
        }
        System.out.println("============显示完毕=============");
    }
    public void orderMenu(){
        System.out.println("========点餐服务=========");
        System.out.println("请输入点餐的桌号(-1退出):");
        int orderDiningTableId=Utility.readInt();
        DiningTable diningTableId = diningTableService.getDiningTableId(orderDiningTableId);
        if(diningTableId==null){
            System.out.println("=======桌号不存在=======");
            return;
        }
        if(orderDiningTableId==-1){
            System.out.println("============取消点餐========");
            return;
        }
        System.out.println("请输入点餐的菜品号：");
        int orderMenuId=Utility.readInt();
        Menu menuId = menuService.getMenuId(orderMenuId);
        if(menuId==null){
            System.out.println("=======菜品号不存在=======");
            return;
        }
        if(orderMenuId==-1){
            System.out.println("============取消点餐========");
            return;
        }
        System.out.println("请输入点餐的菜品量：");
        int orderNums=Utility.readInt();
        if(orderNums==-1){
            System.out.println("============取消点餐========");
            return;
        }


        if(billService.orderMenu(orderMenuId,orderNums,orderDiningTableId)){
            System.out.println("==========点餐成功===========");

        }else System.out.println("===========点餐失败===========");

    }
    public void listMenu()
    {
        System.out.println("\n菜名编号\t\t菜品名\t\t类别\t\t价格");
        List<Menu> list = menuService.list();
        for(Menu menu:list){
            System.out.println(menu);
        }
    }
    public void listDiningTable()
    {
        List<DiningTable> list = diningTableService.list();
        System.out.println("餐桌编号\t\t餐桌状态");
        for(DiningTable l :list){
            System.out.println(l);
        }
        System.out.println("=========显示完毕=========");
    }
    public void orderDiningTable(){
        System.out.println("请输入要预定的桌号");
        int number=Utility.readInt();
        if(number==-1){
            System.out.println("========取消预定餐桌=========");
            return;
        }
        char key=Utility.readConfirmSelection();
        if(key=='Y')
        {
        DiningTable diningTable = diningTableService.getDiningTableId(number);
        if(diningTable == null){
            System.out.println("没有此餐桌");
            return;
        }
        if(!"空".equals(diningTable.getState()))
        {
            System.out.println("此餐桌已经被预定或者用餐中");
            return;
        }
        System.out.println("请输入预定者姓名：");
        String name=Utility.readString(10);
        System.out.println("请输入预定者电话：");
        String tel=Utility.readString(15);
        if(diningTableService.orderDiningTable(number,name,tel)){
            System.out.println("===========预定成功==========");
        }else System.out.println("==========预定失败===========");

        }else {
            System.out.println("=========取消预定餐桌=========");
        }


    }
    public void mainmenue()
    {


        while(loop)
        {
            System.out.println("=========满汉楼界面===========");
            System.out.println("=========1 登入满汉楼系统======");
            System.out.println("=========2 退出满汉楼系统======");
            System.out.println("请选择：");

             key= Utility.readString(1);
            switch (key)
            {
                case "1":
                    System.out.println("请输入员工号：");
                    String empId=Utility.readString(50);
                    System.out.println("请输入密码:");
                    String pwd=Utility.readString(50);
                    Employee employee = employeeService.getEmployeeByIdAndPwd(empId, pwd);

                    if(employee!=null)
                    {
                        System.out.println("===============登入成功["+employee.getName()+"]=============");
                        while (loop){
                            System.out.println("============满汉楼二级菜单=============");
                            System.out.println("\t\t1 显示餐桌状态");
                            System.out.println("\t\t2 预定餐桌");
                            System.out.println("\t\t3 显示所有菜品");
                            System.out.println("\t\t4 点餐服务");
                            System.out.println("\t\t5 查看账单");
                            System.out.println("\t\t6 结账");
                            System.out.println("\t\t9 退出满汉楼");
                            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":
                                    listBill2();
                                    break;
                                case "6":
                                    payBill();
                                    break;
                                case "9":
                                    loop=false;
                                    break;
                                default:
                                    System.out.println("您的输入有误请重新输入");
                            }
                        }
                    }else System.out.println("==========登入失败===========");

                    break;
                case "2":
                    loop=false;
                    System.out.println("您已退出");
                    break;
            }
        }
    }
}

六. 配置文件

#key=value
driverClassName=com.mysql.cj.jdbc.Driver
url=jdbc:mysql://localhost:3306/mhl?rewriteBatchedStatements=true

username=root
password=密码
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=20
#max wait time (5000 mil seconds)
maxWait=5000