开头
项目参考自 韩顺平讲JDBC
文末提供源码,可运行
项目
以下以 Employee类 为例解释
结构
utils:
界面层:
Service(业务层):
DAO层:
Domain类;
MySQL:
View -> Service -> DAO -> Domain ( Bean ) -> MySQL ->DataBase -> Table
MySQL内容
图形化软件为 SQLyog社区版
# 用户表
CREATE TABLE `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 ''
)CHARSET=utf8;
# 加入
INSERT INTO employee VALUES(NULL,0001,MD5(123456),'Mary','Manager');
Domain 类
package com.dxh.mhl.domain;
public class Employee {
private Integer id;
private String empId;
private String name;
private String job;
private String pwd;
public Employee() {
}
public Employee(Integer id, String empId, String name, String job, String pwd) {
this.id = id;
this.empId = empId;
this.name = name;
this.job = job;
this.pwd = pwd;
}
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 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 String getPwd() {
return pwd;
}
public void setPwd(String pwd) {
this.pwd = pwd;
}
}
table -> domain 类型映射
空参,全参构造;getter,setter.
DAO 层
组织CRUD操作
BasicDAO
package com.dxh.mhl.dao;
import com.dxh.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的父类
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);
}
}
}
EmployeeDAO
package com.dxh.mhl.dao;
import com.dxh.mhl.domain.Employee;
public class EmployeeDAO extends BasicDAO<Employee>{
// additional operations
}
Service 业务层
package com.dxh.mhl.service;
import com.dxh.mhl.dao.EmployeeDAO;
import com.dxh.mhl.domain.Employee;
public class EmployeeService {
private EmployeeDAO employeeDAO = new EmployeeDAO(); // sql
public Employee getEmployeeByIdAndPwd(String empId, String pwd) {
return employeeDAO.querySingle("select * from employee where empId = ? and pwd = md5(?)",
Employee.class,empId,pwd);
}
}
Service 随类在项目中的作用而不同
View 层
package com.dxh.mhl.view;
import com.dxh.mhl.domain.*;
import com.dxh.mhl.service.BillService;
import com.dxh.mhl.service.DiningTableService;
import com.dxh.mhl.service.EmployeeService;
import com.dxh.mhl.service.MenuService;
import com.dxh.mhl.utils.Utility;
import java.util.List;
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("你退出了满汉楼系统~");
}
}
Utils 工具包
JDBCUtilsByDruid
package com.dxh.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;
//在静态代码块完成 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);
}
}
}
Utility
package com.dxh.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;
while (true) {
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;
while (true) {
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;
while (true) {
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;
while (true) {//无限循环
//在这里,将接受到字符,转成了大写字母
//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目录下的druid.properties
#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/mhl?rewriteBatchedStatements=true
username=root
password= # your password
#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
最后
下一阶段进入 JavaWeb , 将会频繁更新