满汉楼（德鲁伊连接池+DBUtils+DAO+Mysql）保姆级别分析+代码实现

一：需求

1.完成一个酒店后台管理系统，当然只是模拟，在控制台就行模拟
2.实现管理人员的登录和酒店成本账单的查看，以及正常的点餐，结账和查看账单等等功能

二：实现的功能展示

1.用户登录（这里输入的员工号和密码要与数据库中存的进行校验）

2.显示前台和后台成本信息

3：前台

4：后台

其他细节功能展示不展示了

三：分层实现

1.图示

2：代码分析

1>:总体上先将框架写好，然后在补充功能
2>.具体的功能我们是从下往上开始写，因为上方的逻辑结构可能用到下方的
3>:我们补充的功能先创建一个表，然后创建一个Javabean（将你想要获取的表当中的字段，写成类的属性，注意空构造器和set,get方法）,然后就是创建一个相关类的DAO,方便我们sql语句的操作，再然后就是Service层这是我们的业务层，我们把我们想要实现的功能在这里封装成方法，提供给最后的View层调用,从这里实现的功能的步骤我们可以看出，这是一个自下向上的过程，

3：代码层次分析

1>:德鲁伊连接池+DBUtils+DAO+Mysql
2>:我们首先要引入相关的jar包（druid.jar;dbu.jar,mysql.jar）
3>:德鲁伊连接池我们需要相关的配置文件，url=(数据库的地址)，password,username,
初始化可以连接多少连接，最多可以有多少连接，指的是java程序连接数据库,使用连接池，我们可以提高我们的连接效率，还有一个连接池是c3p0这个连接池比德鲁伊要慢一些，所以我们采用的是德鲁伊连接池.
4>:
关于DBUtils,这是通过select得到的那个结果只能在不关闭连接的时候可以看见，一旦关闭了连接，我们就无法获得查询的结果，然后就用到了Javbean(创建一个类包含其表的属性)，通过反射获取到相关的属性，然后放到一个容器当中，这样你关闭了连接，但查询的结果还是可以看到的

5>:
DAO的引出是因为我们频繁的操作sql语句，每次都要写来连接，写sql,发送sql,返回sql语句，这其实是很麻烦的，所以BasicDAO做了一件事，将select和dml操作封装起来，用的时候传入相关的类和sql语句以及想要的操作赋的值。

四：代码+创建的表

1.注意引进的jar包，

其分的包也就是上诉的层次结构

2：view包

package com.wyj.mhl.view;

import com.wyj.mhl.domain.Employee;
import com.wyj.mhl.domain.Menu;
import com.wyj.mhl.domain.diningTable;
import com.wyj.mhl.service.*;
import com.wyj.mhl.utils.Utility;

public class MHL_View {

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

    }

    //预定餐桌
    public void reser_food(){
        System.out.print("请选择要预定的餐桌编号(1到6号餐桌)(-1退出):");
        int idd = Utility.readInt();

        if (idd != -1) {

            System.out.print("请确认是否要预定(Y/N):");
            char ch = Utility.readChar();
            if (ch == 'Y') {
                System.out.print("请输入预定人的名字:");
                String name = Utility.readString(10);
                System.out.print("请输入预定人的电话:");
                String phone = Utility.readString(10);

                int i = diningTableService.book_table(idd, name, phone);

                if(i!= 0){
                    System.out.println("======预定成功======");
                }else{
                   return;
                }
            } else {
                return;
            }
        } else {
            return ;
        }

    }

    //点餐
    public void OrderMenu() {
        System.out.print("请输入点餐的桌号(-1退出):");
        int idd2 = Utility.readInt();

        if (idd2 == -1)
            return;
        diningTable empty = diningTableService.isEmpty(idd2);

        if (!empty.getState().equals("空")) {
            System.out.println("===您选的座位已占==");
            return;
        }


        System.out.print("选择菜品的编号(-1退出):");
        int menu_id1 = Utility.readInt();

        if (menu_id1 == -1)
            return;

        Menu singleBYid = menuService.getBillSingleBYid(menu_id1);

        if (singleBYid == null) {
            System.out.println("====您输入的菜品单号本店没有===");
            return;
        }

        System.out.print("选择菜品的数量(-1退出):");
        int menu_nums = Utility.readInt();

        if (menu_nums == -1)
            return;

        System.out.print("确认是否点这个菜(Y?N):");
        char ch = Utility.readChar();

        if (ch == 'Y') {

            if (billService.orderMenu(menu_id1, menu_nums, idd2)) {
                System.out.println("=====点餐成功=====");
            } else {
                System.out.println("====点餐失败");
            }

        } else {
            System.out.println("==请重新输入Y/N====");
            return;
        }

    }
    //结账
    public void leave(){
        System.out.print("请选择要结账的餐桌编号(-1退出):");
        int leave_id = Utility.readInt();

        if(leave_id == -1){
            return ;
        }

        //检验餐桌号是否存在
        diningTable empty = diningTableService.isEmpty(leave_id);
        if(empty == null || empty.getState().equals("空")){

            if(empty == null)
                System.out.println("===您结账的餐桌本店不存在===");
            else
                System.out.println("===您并未在该餐桌就餐===");
            return;
        }
        //检验结账的餐桌是否为空


        System.out.print("结账方式(现金/支付宝/微信):");
        String leave_way = Utility.readString(10,"");
        if("".equals(leave_way)){
            System.out.println("===取消结账===");
            return;
        }

        System.out.print("确认是否结账(Y/N):");

        char ch = Utility.readChar();

        if( ch == 'Y'){

           if (billService.invoicing(leave_id,leave_way)){
               System.out.println("====结账成功====");
           }else{
               System.out.println("====结账失败====");
           }

        }else{
            System.out.println("===请您结账离开本店===");
            return;
        }

    }



    private boolean loop = true;
    private String key = "";//一级菜单
    private String option = "";//二级菜单
    private EmployeeService service = new EmployeeService();//员工登录的业务
    private DiningTableService diningTableService = new DiningTableService();//点餐的业务层
    private MenuService menuService = new MenuService();//显示菜单
    private BillService billService = new BillService();//账单
    private MulityTableService mulityTableService = new MulityTableService();//这个可以显示账单中带菜名
    private CostSerivice costSerivice = new CostSerivice();//显示成本信息
    public void mainMenu() {

        while (loop) {
            System.out.println("==========满汉楼=========");
            System.out.println("\t\t1.登录满汉楼");
            System.out.println("\t\t2.退出满汉楼");
            System.out.print("亲输入您的选项：");
            key = Utility.readString(1);

            switch (key) {

                case "1":
                    System.out.print("请输入员工号:");
                    String empId = Utility.readString(20);
                    System.out.print("请输入  密码:");
                    String pwd = Utility.readString(20);
                    Employee employee = service.getEmployeeEmpidandpwd(empId, pwd);


                    if (employee != null) {
                        System.out.println("===========登录成功==========");

                        System.out.println(employee.getEmpId() + " " + employee.getName());


                        while(loop) {
                            System.out.println("===满汉楼的功能表===");
                            System.out.println("1.前台状态");
                            System.out.println("2.后台成本");
                            System.out.println("3.退出系统");
                            System.out.print("请选择您的选项：");
                            int option2 = Utility.readInt();

                            switch (option2) {

                                case 1:
                                    //显示二级菜单
                                    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\t7.退出满汉楼");

                                        System.out.print("请输入您的选项:");
                                        option = Utility.readString(1);

                                        switch (option) {
                                            case "1":
                                                diningTableService.showDiningTable();//显示餐桌的状态
                                                break;
                                            case "2":
                                                System.out.println("========预定餐桌========");
                                                new MHL_View().reser_food();

                                                break;
                                            case "3":
                                                System.out.println("======显示菜单======");
                                                menuService.showMenu();
                                                break;
                                            case "4":
                                                System.out.println("======点餐服务======");
                                                new MHL_View().OrderMenu();
                                                break;
                                            case "5":
                                                System.out.println("===查看账单====");
                                                //billService.showBill();
                                                mulityTableService.ShowBillandMenu();
                                                break;
                                            case "6":
                                                System.out.println("====结账====");
                                                new MHL_View().leave();
                                                break;
                                            case "7":
                                                loop = false;
                                                break;
                                            default:
                                                System.out.println("输入没有在选项范围内");
                                        }
                                    }
                                    break;
                                case 2:
                                    System.out.println("====成本控制====");
                                    costSerivice.showCost();
                                    break;
                                case 3:
                                    loop = false;
                                    break;
                                default:
                                    System.out.println("您的输入有误，请重新输入");

                            }


                        }
                    } else {
                        System.out.println("===========登录失败==========");
                    }
                    break;
                case "2":
                    loop = false;
                    break;
                default:
                    System.out.println("输入的有问题，请重新输入");
            }

        }
        System.out.println("退出满汉楼!");

    }

}

3：utiles包中有一个输入的的类和一个将德鲁伊连接池变成工具类

(1):Utility

package com.wyj.mhl.utils;


import java.util.Scanner;

public class Utility {
    private static Scanner scanner;

    static {
        scanner = new Scanner(System.in);
    }

    public Utility() {

    }

    public static char readMenuSelection() {
        while (true) {
            String str = readKeyBoard(1, false);
            char c = str.charAt(0);
            if (c == 1 || c == 2 || c == 3 || c == 4 || c == 5) {
                return c;
            }

            System.out.print("选择错误，请重新输入：");
        }
    }

    public static char readChar() {
        String str = readKeyBoard(1, false);
        return str.charAt(0);
    }

    public static char readChar(char defaultValue){
        String str=readKeyBoard(1, true);
        return str.length()==0?defaultValue:str.charAt(0);
    }

    public static int readInt(){
        while (true){
            String str=readKeyBoard(2, false);
            try {
                int n=Integer.parseInt(str);
                return n;
            }catch (NumberFormatException var3){
                System.out.println("数字输入错误，请重新输入：");
            }
        }
    }

    public static int readInt(int defaultValue) {
        while(true) {
            String str = readKeyBoard(2, true);
            if (str.equals("")) {
                return defaultValue;
            }

            try {
                int n = Integer.parseInt(str);
                return n;
            } catch (NumberFormatException var4) {
                System.out.print("数字输入错误，请重新输入：");
            }
        }
    }

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

    public static String readString(int limit, String defaultValue) {
        String str = readKeyBoard(limit, true);
        return str.equals("") ? defaultValue : str;
    }

    public static char readConfirmSelection(){
        while (true){
            String str=readKeyBoard(1,false).toUpperCase();
            char c=str.charAt(0);
            if(c=='Y'||c=='N'){
                return c;
            }
            System.out.print("选择错误，请重新输入：");
        }
    }

    private static String readKeyBoard(int limit, boolean blankReturn){
        String line="";

        while (scanner.hasNextLine()){
            line=scanner.nextLine();
            if(line.length()==0){
                if(blankReturn){
                    return line;
                }
            }else {
                if(line.length()>=1 && line.length()<=limit){
                    break;
                }
                System.out.println("输入长度（不大于" + limit + "）错误，请重新输入：");
            }
        }
        return line;
    }
}

(2):JDBCUtilsBYDruid

package com.wyj.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 {

    //(1):定义一个数据源,作为连接池
    private static DataSource ds;

    //(2):静态代码块  类加载的时候自动加载 而且只加载一次

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

    //关闭连接，是关闭java程序与连接池的连接 而并不是关闭与数据库的连接

    public static void close(ResultSet set, Statement statement,Connection connection){

        try {
            if(set != null){
                set.close();
            }

            if(statement != null){
                statement.close();
            }

            if(connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}

4.Service包

(1):BillService

package com.wyj.mhl.service;

import com.wyj.mhl.dao.BillDAO;
import com.wyj.mhl.domain.Bill;

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

public class BillService {

    private BillDAO billDAO = new BillDAO();
    private MenuService menuService = new MenuService();
    private DiningTableService diningTableService = new DiningTableService();

    /**
     * 实现点餐的功能：
     * 1.点菜品单号是否存在，点的餐桌号是否存在， 在view层中实现
     * 2.id:自增 不用管他
     * ,biilid：随机字符串 不用管他
     * ,menuid：输入的菜品号 形参
     * ,nums：点单数量 形参
     * ,money：计算出来的，需要MenuService 提供方法获取菜品的价格
     * ,state：自己输入的
     * billdate：当前直接获取
     * diningTable：输入的餐桌号，形参
     */

    public boolean orderMenu(int menuid,int nums,int dingtableid){

        String billid = (UUID.randomUUID().toString());//生成一个随机账单号

        billid = billid.substring(30);

        int i = billDAO.update("insert into bill values(null,?,?,?,?,?,now(),?)",
                billid, menuid, nums, menuService.getBillSingleBYid(menuid).getPrice() * nums
                , "未结账",  dingtableid);

        if(i < 0){
            return false;
        }

        //更新餐桌的状态
        return diningTableService.updatestate(dingtableid,"就餐中");

    }

    //显示账单
    public void showBill(){

        List<Bill> list = billDAO.queryMulti("select * from bill", Bill.class);

        for (Bill bill : list) {
            System.out.println(bill);
        }

    }

    /**
     * 结账：
     * 1.输入餐桌号（检验餐桌是否存在，检验餐桌是否为空（为空的话 不用结账））
     * 2.输入结账方式 支付宝，现金，微信
     * 3.更新餐桌的状态
     *
     * 就餐号	账单单号		菜单号	点餐的份数	金额		餐桌状态		餐桌号
     */

    public boolean invoicing(int id,String check_way){

        int i = billDAO.update("update bill set state = ? where diningTable = ?", check_way, id);

        if(i < 0){
            return false;
        }

        //更新餐桌的状态
        return diningTableService.updatestate(id,"空");
    }



}

(2):CostService

package com.wyj.mhl.service;

import com.wyj.mhl.dao.CostDAO;
import com.wyj.mhl.domain.Cost;

import java.util.List;

public class CostSerivice {

    private CostDAO costDAO = new CostDAO();

    //显示成本：
    public void showCost(){

        List<Cost> list = costDAO.queryMulti("select * from cost", Cost.class);

        System.out.println("日期"+"\t\t"+"电费"+"\t\t"+"水费"+"\t\t"+"人工费"+"\t\t"+"食品费");

        for (Cost cost : list) {
            System.out.println(cost);
        }


    }


}

(3)DingtableService

package com.wyj.mhl.service;

import com.wyj.mhl.dao.DingTableDAO;
import com.wyj.mhl.domain.diningTable;

import java.util.List;

/**
 * 业务层：提供方法显示餐桌的状态
 *
 */
public class DiningTableService {

   private  DingTableDAO dingTableDAO = new DingTableDAO();

   //1.显示餐桌的状态
   public void showDiningTable(){
       List<diningTable> list = dingTableDAO.queryMulti("select * from diningTable",diningTable.class );

       System.out.println("餐桌编号"+"\t\t"+"餐桌状态");
       for (diningTable diningTable : list) {
           System.out.println(diningTable);
       }
   }

   //2.预定餐桌
    /**
     * 功能：
     * 请选择要预定餐桌编号(-1退出):
     * 预定人名字:
     * 预定人电话:
     * 确认是否预定(Y/N):
     *
     * 注意的问题:
     * 1.输入的餐桌编号是否存在的编号
     * 2.餐桌是否被预定
     */

    public int  book_table(int idd,String name,String phone){

        int update;

        //查询要预定餐桌的状态（看是否被预定）
        Object scalar = dingTableDAO.queryScalar("select state from diningTable where id = ?", idd);

        //查看是否存在
        diningTable table = dingTableDAO.querySingle("select * from diningTable where id = ?", diningTable.class, idd);

        if(table == null ){
            System.out.println("====输入的idd号不存在,请重新输入====");
            update = 0;
            return update;
        }else if(scalar.equals("已预定")){
            update = 0;
            System.out.println("====进预定的餐桌号已经被预定了，请重新选择！====");
            return update;
        }
        else {
            update = dingTableDAO.update("update diningTable " +
                    "set state = ? ,orderName = ?,orderTel = ? " +
                    "where id = ?", "已预定", name, phone, idd);
        }

        return update;
    }

    //为billservice提供更新状态的方法
    public boolean updatestate(int id,String state){

        int i = dingTableDAO.update("update diningTable set state = ? where id = ?", state, id);

        if(i < 0)
            return false;

        return true;
    }

    //显示判断餐桌是否为存在
    public diningTable isEmpty(int id){

        diningTable table = dingTableDAO.querySingle("select * from diningTable where id = ?", diningTable.class, id);

        return  table;
    }




}

(4):EmployeeService

package com.wyj.mhl.service;

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

import java.util.List;

/**
 * 业务层:这里通过调用EmployeeDAO来完成相关的操作
 */

public class EmployeeService {

    public static void main(String[] args) {
        EmployeeDAO employeeDAO = new EmployeeDAO();
        List<Employee> list = employeeDAO.queryMulti("select * from employee", Employee.class);

        for (Employee employee : list) {
            System.out.println(employee);
        }


    }

    private  EmployeeDAO employeeDAO = new EmployeeDAO();

    /**
     * 我们在确定用户输入的账号密码是否正确的时候，并不是拿输入的数据去和数据库中的
     * 数据进行比较,而是将输入的数据在数据库寻找，看是否可以找到
     */
    //如果返回结果为空 就是没查到该数据
    public Employee getEmployeeEmpidandpwd(String empId,String pwd){

        Employee employee = employeeDAO.querySingle("select * from employee where empId = ? and pwd = md5(?)", Employee.class,empId,pwd);
        return employee;




    }

}

(5):MenuService

package com.wyj.mhl.service;

import com.wyj.mhl.dao.MenuDAO;
import com.wyj.mhl.domain.Bill;
import com.wyj.mhl.domain.Menu;

import java.util.List;

public class MenuService {
    public static void main(String[] args) {
        new MenuService().showMenu();
    }

    private MenuDAO menuDAO = new MenuDAO();

    //显示菜品

    public void showMenu(){

        List<Menu> list = menuDAO.queryMulti("select * from menu", Menu.class);

        for (Menu menu : list) {
            System.out.println(menu);
        }

    }

    //通过返回一个menu对象  菜单号来获取到一行的数据 来求取价格

    public Menu getBillSingleBYid(int menuid){

        Menu single = menuDAO.querySingle("select * from menu where id = ?", Menu.class, menuid);
        return single;

    }

    //提供方法 来判断这个菜品是否存在


}

(6)MulitytableService(这是一个多表)

package com.wyj.mhl.service;

import com.wyj.mhl.dao.MulityTableDAO;
import com.wyj.mhl.domain.Mulitytable;

import java.util.List;

public class MulityTableService {


    private MulityTableDAO mulityTableDAO = new MulityTableDAO();

    //显示账单和菜名
    public void ShowBillandMenu(){

        List<Mulitytable> list = mulityTableDAO.queryMulti("select bill.*,food_name,price from bill,menu " +
                " where bill.menuId = menu.id", Mulitytable.class);

        for (Mulitytable mulitytable : list) {
            System.out.println(mulitytable);
        }

    }

}

5.domain(javabain)

(1):BIll

package com.wyj.mhl.domain;

import com.sun.org.apache.bcel.internal.generic.IINC;

/**
 * 生成账单：
 * id INT PRIMARY KEY AUTO_INCREMENT,-- 自增主键
 * billId VARCHAR(50) NOT NULL DEFAULT'', -- 账单号
 * menuId INT NOT NULL DEFAULT 0,-- 菜单编号
 * nums INT NOT NULL DEFAULT 0,-- 菜的份数
 * money DOUBLE NOT NULL DEFAULT 0,-- 花的钱
 * state VARCHAR(32) NOT NULL DEFAULT '', -- 餐桌的状态 结账，未结账，挂单，现金，支付宝
 * billdate DATETIME
 * diningTable INT NOT NULL DEFAULT 0;-- 餐桌号
 */
public class Bill {

    private Integer id;//自增的主键
    private String billId;//账单号
    private Integer menuId;//菜单号
    private Integer nums;//点菜的份数
    private Double money;//点菜花的钱
    private String state;//餐桌的状态
    private Integer diningTable;//餐桌号

    public Bill() {
    } //空构造器，为后面的（BasicDAO）反射底层的调用做准备，

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

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

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

    public Integer getDiningTable() {
        return diningTable;
    }

    public void setDiningTable(Integer diningTable) {
        this.diningTable = diningTable;
    }

    @Override
    public String toString() {
        System.out.println("就餐号"+"\t"+"账单单号"+"\t\t"+"菜单号"+"\t"+"点餐的份数"+"\t"+"金额"+"\t\t"+"状态"+"\t\t"+"餐桌号");

        return id + "\t\t" + billId + "\t\t" + menuId + "\t\t" + nums + "\t\t\t" + money + "\t\t"+state + "\t\t"+diningTable+"\n";

    }
}

(2):Cost

package com.wyj.mhl.domain;

/**
 * 成本表：
 * `date` VARCHAR(32) NOT NULL DEFAULT '',
 * 	electricity DOUBLE NOT NULL DEFAULT 0, -- 电费
 * 	water DOUBLE NOT NULL DEFAULT 0, -- 水费
 * 	people DOUBLE NOT NULL DEFAULT 0, -- 人工费
 * 	food DOUBLE NOT NULL DEFAULT 0 -- 食品费
 *
 */
public class Cost {

    private String date;
    private Double electricity;
    private Double water;
    private Double people;
    private Double food;

    public Cost(){}

    public Cost(String date, Double electricity, Double water, Double people, Double food) {
        this.date = date;
        this.electricity = electricity;
        this.water = water;
        this.people = people;
        this.food = food;
    }

    public String getDate() {
        return date;
    }

    public void setDate(String date) {
        this.date = date;
    }

    public Double getElectricity() {
        return electricity;
    }

    public void setElectricity(Double electricity) {
        this.electricity = electricity;
    }

    public Double getWater() {
        return water;
    }

    public void setWater(Double water) {
        this.water = water;
    }

    public Double getPeople() {
        return people;
    }

    public void setPeople(Double people) {
        this.people = people;
    }

    public Double getFood() {
        return food;
    }

    public void setFood(Double food) {
        this.food = food;
    }

    @Override
    public String toString() {

       return date+"\t\t"+electricity+"\t\t"+water+"\t\t"+people+"\t\t"+food;
    }
}

(3):dingtable

package com.wyj.mhl.domain;

/**
 * 创建餐桌表：显示餐桌状态
 */

public class diningTable {
    private Integer id;
    private String state;//餐桌状态
    private String orderName;//点餐人姓名
    private String orderTel;//点餐人电话

    public diningTable() {//反射底层要用到，在DAO层中需要用到反射来获取类的属性
    }

    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+"\n";
    }
}

(4)；Employee

package com.wyj.mhl.domain;

/**
 * id INT  PRIMARY KEY AUTO_INCREMENT,
 * 	empId  VARCHAR(32) NOT NULL DEFAULT '', -- 员工号
 * 	pwd CHAR(32) NOT NULL DEFAULT '',-- 加密密码
 * 	`name` VARCHAR(32) NOT NULL DEFAULT '',-- 名字
 * 	job VARCHAR(20) 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;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", empId='" + empId + '\'' +
                ", pwd='" + pwd + '\'' +
                ", name='" + name + '\'' +
                ", job='" + job + '\'' +
                '}'+ "\n";
    }
}

(5):Menu

package com.wyj.mhl.domain;

/**
 * javabean：显示菜单
 */
public class Menu {

    private Integer id;
    private String food_name;
    private String food_class;
    private Double price;

    public Menu() {
    }//空构造器，反射底层要调用，在BasicDAO中需要用到

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

    public Integer getId() {
        return id;
    }

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

    public String getFood_name() {
        return food_name;
    }

    public void setFood_name(String food_name) {
        this.food_name = food_name;
    }

    public String getFood_class() {
        return food_class;
    }

    public void setFood_class(String food_class) {
        this.food_class = food_class;
    }

    public Double getPrice() {
        return price;
    }

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

    @Override
    public String toString() {
        return id + "\t" + food_name + "\t\t" + food_class + "\t\t" + price+"\n";
    }
}

(6):Mulitytable

package com.wyj.mhl.domain;

public class Mulitytable {
    private Integer id;//自增的主键
    private String billId;//账单号
    private Integer menuId;//菜单号
    private Integer nums;//点菜的份数
    private Double money;//点菜花的钱
    private String state;//餐桌的状态
    private Integer diningTable;//餐桌号

    private String food_name;//菜名
    //增加每种菜的价格
    private Double price;

    public Mulitytable() {
    }



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

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

    public Integer getDiningTable() {
        return diningTable;
    }

    public void setDiningTable(Integer diningTable) {
        this.diningTable = diningTable;
    }

    public String getFood_name() {
        return food_name;
    }

    public void setFood_name(String food_name) {
        this.food_name = food_name;
    }

    public Double getPrice() {
        return price;
    }

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

    @Override
    public String toString() {
        System.out.println("就餐号"+"\t"+"账单单号"+"\t\t"+"菜单号"+"\t"+"点餐的份数"+"\t"+"金额"+"\t\t"+"状态"+"\t\t"+"餐桌号"+"\t\t"+"菜名"+"\t\t"+"价格");

        return id + "\t\t" + billId + "\t\t" + menuId + "\t\t" + nums + "\t\t\t" + money + "\t\t"+state + "\t\t"+diningTable+"\t\t"+food_name+"\t\t"+price+"\n";

    }

}

6：DAO包

(1):BasicDAO

package com.wyj.mhl.dao;

import com.wyj.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;

/**
 * dao的父类Basicdao
 *
 * @param <T>
 */
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:给sql语句当中的 ？？ 进行赋值
     * @return
     */
    public List<T> queryMulti(String sql,Class<T> clazz,Object... parameters) {

        Connection connection = null;

        try {
            connection = JDBCUtilsBYDruid.getConnection();
            List<T> list = qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsBYDruid.close(null, null, connection);
        }

        return null;
    }

    //查询单行的结果

    public T querySingle(String sql,Class<T> clazz,Object... parameters){

        Connection connection = null;

        try {
             connection = JDBCUtilsBYDruid.getConnection();
            T query = qr.query(connection, sql, new BeanHandler<T>(clazz), parameters);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {

        }
     return null;
    }

    //查询单个数据
    public Object queryScalar(String sql,Object... paramenters){
        Connection connection = null;

        try {
            connection = JDBCUtilsBYDruid.getConnection();
            Object query = qr.query(connection, sql, new ScalarHandler<>(), paramenters);
            return query;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsBYDruid.close(null,null,connection);
        }

        return null;
    }


}

(2):BillDAO

package com.wyj.mhl.dao;

import com.wyj.mhl.domain.Bill;

public class BillDAO extends BasicDAO<Bill>{

    //继承BasicDAO的一些方法：select,整个表的数据，单行数据（返回一个对象，可以通过get...
    // 方法获取到一些属性），单个数据，dml操作
}

(3):CostDAO

package com.wyj.mhl.dao;

import com.wyj.mhl.domain.Cost;

public class CostDAO extends BasicDAO<Cost>{

    /**
     * 继承：BasicDAO当中的方法
     * dml 和 select,可以直接输入sql语句和操作就可以了
     *
     *
     */
}

(4):DingtableDAO

package com.wyj.mhl.dao;

import com.wyj.mhl.domain.diningTable;

public class DingTableDAO extends BasicDAO<diningTable>{

    /**
     * 直接继承父类当中的方法，供Service层使用
     */
}

（5）：Employee

package com.wyj.mhl.dao;

import com.wyj.mhl.domain.Employee;

public class EmployeeDAO extends BasicDAO<Employee>{

    /**
     * 可以调用父类当中的方法,
     * 1.查询的整个表，查询单行数据，查询单个数据
     * 2.dml
    */

}

(6)；Menu

package com.wyj.mhl.dao;


import com.wyj.mhl.domain.Menu;

public class MenuDAO extends BasicDAO<Menu>{
    /**
     * 继承BasicDAO，这样可以调用其的方法 select(整个表和单个数据和单行数据) 和 dml
     *
     */


}

(7):MulityDAO

package com.wyj.mhl.dao;

import com.wyj.mhl.domain.Mulitytable;

public class MulityTableDAO extends BasicDAO<Mulitytable>{

}

补充：德鲁伊的配置文件（要根据自己的电脑信息进行相应的修改）

#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mhl?rewriteBatchedStatements=true
#url=jdbc:mysql://localhost:3306/mhl
username=root
password=wyj
#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

五：相关在数据库中的表

`wyj_dp02`-- 1.创建一个测试表

CREATE TABLE text01(
	id INT KEY AUTO_INCREMENT,
	`name` VARCHAR(32)
);

DROP TABLE text01;

INSERT INTO text01
	VALUES(1,'王永杰'),
	(NULL,'王大爷'),
	(NULL,'王二爷');


SELECT * FROM text01;


-- 员工表

-- 字段(创建表 employee表(主键id, empId,name,pwd,jb等)
CREATE TABLE employee(
	id INT  PRIMARY KEY AUTO_INCREMENT,
	empId  VARCHAR(32) UNIQUE NOT NULL DEFAULT '', -- 员工号
	pwd CHAR(32) NOT NULL DEFAULT '',-- 加密密码
	`name` VARCHAR(32) NOT NULL DEFAULT '',-- 名字
	job VARCHAR(20) NOT NULL DEFAULT '' -- 工作 
);

INSERT INTO employee
	VALUES(NULL,"9912",MD5("123456"),'王大美','经理'),
	(NULL,"9913",MD5("123456"),'王小美','服务员'),
	(NULL,"9914",MD5("123456"),'王大力','收银员'),
	(NULL,"9915",MD5("123456"),'王永杰','经理');
	
	
DROP TABLE employee	

DELETE FROM employee
	WHERE id  = 6;
	
SELECT * FROM employee;	


-- 创建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

DROP TABLE diningtable

UPDATE diningTable 
              SET state = '已预定',orderName = "jack",orderTel = "00999"
                    WHERE id = 6;
UPDATE diningTable 
              SET state = '空'
                    WHERE id = 1;

-- 菜单表 menu

CREATE TABLE menu(
	id INT PRIMARY KEY AUTO_INCREMENT,
	food_name VARCHAR(32) NOT NULL DEFAULT '', -- 菜品名
	food_class VARCHAR(32) NOT NULL DEFAULT '',-- 类别
	price DOUBLE NOT NULL DEFAULT 0.0	
);

INSERT INTO menu
	VALUES(1,"八宝饭","主食",10.0),
	(NULL,"叉烧包","主食",20.0),
	(NULL,"宫保鸡丁","热菜",20.0),
	(NULL,"糖醋鲤鱼","热菜",120.0);
	
	
SELECT * FROM menu;


-- 创建账单表
-- 字段:(id,biilid,menuid,nums,billdate,money,state,diningTable)

CREATE TABLE bill(
	id INT PRIMARY KEY AUTO_INCREMENT,-- 自增主键
	billId VARCHAR(50) NOT NULL DEFAULT'', -- 账单号
	menuId INT NOT NULL DEFAULT 0,-- 菜单编号
	nums INT NOT NULL DEFAULT 0,-- 菜的份数
	money DOUBLE NOT NULL DEFAULT 0,-- 花的钱
	state VARCHAR(32) NOT NULL DEFAULT '', -- 餐桌的状态 结账，未结账，挂单，现金，支付宝
	billdate DATETIME
);

ALTER TABLE bill
	ADD diningTable INT NOT NULL DEFAULT 0;-- 餐桌号


SELECT * FROM bill;

DROP TABLE bill;

DELETE FROM bill WHERE id = 3;

DELETE FROM diningTable WHERE id = 2;
SELECT * FROM diningTable;

SELECT * FROM menu WHERE id = 1;

SELECT * FROM diningTable WHERE id = 1

UPDATE bill SET state = "就餐中" WHERE diningTable = 1;



SELECT * FROM menu;
SELECT * FROM bill;


SELECT bill.*,food_name
	FROM menu,bill
	WHERE menu.id = bill.menuId;
	
	
	SELECT bill.*,food_name 
                 FROM menu,bill 
                 WHERE menu.id = bill.menuId


-- 成本控制表（date,electricity,water,people,food）

CREATE TABLE cost(
	`date` VARCHAR(32) NOT NULL DEFAULT '',
	electricity DOUBLE NOT NULL DEFAULT 0, -- 电费
	water DOUBLE NOT NULL DEFAULT 0, -- 水费
	people DOUBLE NOT NULL DEFAULT 0, -- 人工费
	food DOUBLE NOT NULL DEFAULT 0 -- 食品费
);

DROP TABLE cost;



INSERT INTO cost
	VALUES('2019—12-11',1200,200,3000,4000),
		('2019—12-12',1300,200,2000,6000),
		('2019—12-13',1400,400,1000,8000),
		('2019—12-14',1300,500,4000,2000),
		('2019—12-15',1400,700,2000,1000);
		
SELECT * FROM cost;

SELECT * FROM cost

六:总结

兄弟们满汉楼是学完jdbc和mysql后可以用来巩固你知识的，而且仅仅是在控制台展示，是比较low的，但学习是循序渐进的，加油BOY！！！！！，我们共勉，还有的是这是最后一次上传这么长的码，太傻了，我以为不多呢，太费时间了，下次直接上传文件源码！！！加油加油加油！！！！！!!！！！!!！!!！!!！!！!!!！! 多吃核桃哈哈哈哈哈哈