1、配置依赖
<dependencies>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.10</version>
</dependency>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.12</version>
<scope>test</scope>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.2.16</version>
</dependency>
</dependencies>
2、Fruit 实体类
package com.csdn.fruit.pojo;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Fruit implements Serializable {
private Integer fid;
private String fname;
private Integer price;
private Integer fcount;
private String remark;
public Fruit(String fname, Integer price, Integer fcount, String remark) {
this.fname = fname;
this.price = price;
this.fcount = fcount;
this.remark = remark;
}
@Override
public String toString() {
return fname + "\t\t" + price + "\t\t" + fcount + "\t\t" + remark;
}
}
3、设计数据访问对象层DAO接口
package com.csdn.fruit.dao;
import com.csdn.fruit.pojo.Fruit;
import java.util.List;
//dao ：Data Access Object 数据访问对象
//接口设计
public interface FruitDao {
void addFruit(Fruit fruit);
void delFruit(String fname);
void updateFruit(Fruit fruit);
List<Fruit> getFruitList();
Fruit getFruitByFname(String fname);
}
4、设计DAO层的实现类
package com.csdn.fruit.dao.impl;
import com.csdn.fruit.dao.FruitDao;
import com.csdn.fruit.pojo.Fruit;
import com.csdn.mymvc.dao.BaseDao;
import java.util.List;
public class FruitDaoImpl extends BaseDao<Fruit> implements FruitDao {
@Override
public void addFruit(Fruit fruit) {
String sql = "insert into t_fruit values (0,?,?,?,?)";
super.executeUpdate(sql, fruit.getFname(), fruit.getPrice(), fruit.getFcount(), fruit.getRemark());
}
@Override
public void delFruit(String fname) {
String sql = "delete from t_fruit where fname=?";
super.executeUpdate(sql, fname);
}
@Override
public void updateFruit(Fruit fruit) {
String sql = "update t_fruit set fcount=? where fname = ?";
super.executeUpdate(sql, fruit.getFcount(), fruit.getFname());
}
@Override
public List<Fruit> getFruitList() {
return super.executeQuery("select * from t_fruit");
}
@Override
public Fruit getFruitByFname(String fname) {
return load("select * from t_fruit where fname = ?", fname);
}
}
5、编写 jdbc 配置文件
jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql:///fruitdb
jdbc.user=root
jdbc.pwd=123456
jdbc.init_size=5
jdbc.max_active=20
jdbc.max_wait=3000
6、 设计数据库操作层（抽象类）
package com.csdn.mymvc.dao;
import com.alibaba.druid.pool.DruidDataSource;
import com.csdn.mymvc.util.ClassUtil;
import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
public abstract class BaseDao<T> {
private String entityClassName;
public BaseDao() {
// this 是谁？ this代表的是 FruitDaoImpl 的实例对象，因为 BaseDao是抽象类，不能直接创建对象，所以 new 的是它的子类对象 FruitDaoImpl
// this.getClass() 获取的是 FruitDaoImpl 的Class对象
// getGenericSuperclass() 获取到的是：BaseDao<Fruit>
// Type 是顶层接口，表示所有的类型。它有一个子接口：ParameterizedType
ParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass();
// Actual:实际的
// getActualTypeArguments() 获取实际的类型参数
Type[] actualTypeArguments = genericSuperclass.getActualTypeArguments();
Type actualTypeArgument = actualTypeArguments[0];
// System.out.println(actualTypeArgument.getTypeName());//com.csdn.fruit.pojo.Fruit
entityClassName = actualTypeArgument.getTypeName();
initDataSource();
}
private DataSource dataSource;
//加载jdbc.properties文件
private void initDataSource() {
try {
InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(inputStream);
String driver = properties.getProperty("jdbc.driver", "com.mysql.cj.jdbc.Driver");
String url = properties.getProperty("jdbc.url", "jdbc:mysql:///fruitdb");
String user = properties.getProperty("jdbc.user", "root");
String pwd = properties.getProperty("jdbc.pwd", "123456");
Integer initSize = Integer.parseInt(properties.getProperty("jdbc.init_size", "5"));
Integer maxActive = Integer.parseInt(properties.getProperty("jdbc.max_active", "10"));
Integer maxWait = Integer.parseInt(properties.getProperty("jdbc.max_wait", "5000"));
DruidDataSource druidDataSource = new DruidDataSource();
druidDataSource.setDriverClassName(driver);
druidDataSource.setUrl(url);
druidDataSource.setUsername(user);
druidDataSource.setPassword(pwd);
druidDataSource.setInitialSize(initSize);
druidDataSource.setMaxActive(maxActive);
druidDataSource.setMaxWait(maxWait);
dataSource = druidDataSource;
} catch (IOException e) {
throw new RuntimeException(e);
}
}
private Connection getConn() throws SQLException {
return dataSource.getConnection();
}
private void close(Connection conn, PreparedStatement psmt, ResultSet rs) {
try {
if (rs != null) {
rs.close();
}
if (psmt != null) {
psmt.close();
}
if (conn != null && !conn.isClosed()) {
conn.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
//抽取执行更新方法
//执行更新，返回影响行数
//如果是执行 insert，那么可以尝试返回自增列的值
protected int executeUpdate(String sql, Object... params) {
boolean insertFlag = sql.trim().toUpperCase().startsWith("INSERT");
Connection conn = null;
PreparedStatement psmt = null;
try {
conn = getConn();
psmt = insertFlag ? conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) : conn.prepareStatement(sql);
setParams(psmt, params);
int count = psmt.executeUpdate();
if (insertFlag) {
ResultSet rs = psmt.getGeneratedKeys();
if (rs.next()) {
Long id = rs.getLong(1);
count = id.intValue();
}
}
return count;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(conn, psmt, null);
}
}
//设置参数
private void setParams(PreparedStatement psmt, Object... params) throws SQLException {
if (params != null && params.length > 0) {
for (int i = 0; i < params.length; i++) {
psmt.setObject(i + 1, params[i]);
}
}
}
//执行查询，返回集合
protected List<T> executeQuery(String sql, Object... params) {
List<T> list = new ArrayList<>();
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = getConn();
psmt = conn.prepareStatement(sql);
setParams(psmt, params);
rs = psmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();//元数据,结果集的结构数据
while (rs.next()) {
//T t = new T(); T仅仅是一个符号，所以不能 new
T t = (T) ClassUtil.createInstance(entityClassName);
int columnCount = rsmd.getColumnCount();//获取结果集的列的数据
//jdbc中都是从 1 开始，所以要把 i 改成 从 1 开始
for (int i = 1; i <= columnCount; i++) {
//假设循环 5 次，得到 5 个值，应该对应的是一个对象的 5 个属性的值
String columnName = rsmd.getColumnLabel(i);
Object columnValue = rs.getObject(i);
//给 t 这个对象的 columnName 属性赋 columnValue 值
ClassUtil.setProperty(t, columnName, columnValue);
}
list.add(t);
}
return list;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(conn, psmt, rs);
}
}
protected T load(String sql, Object... params) {
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = getConn();
psmt = conn.prepareStatement(sql);
setParams(psmt, params);
rs = psmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();//元数据,结果集的结构数据
if (rs.next()) {
//T t = new T(); T仅仅是一个符号，所以不能 new
T t = (T) ClassUtil.createInstance(entityClassName);
int columnCount = rsmd.getColumnCount();//获取结果集的列的数据
//jdbc中都是从 1 开始，所以要把 i 改成 从 1 开始
for (int i = 1; i <= columnCount; i++) {
//假设循环 5 次，得到 5 个值，应该对应的是一个对象的 5 个属性的值
String columnName = rsmd.getColumnLabel(i);
Object columnValue = rs.getObject(i);
//给 t 这个对象的 columnName 属性赋 columnValue 值
ClassUtil.setProperty(t, columnName, columnValue);
}
return t;
}
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(conn, psmt, rs);
}
return null;
}
//select max(age) as max_age , avg(age) as avg_age from t_user
// 28 24.5
//select deptNo,avg(sal) as avg_sal from emp group by deptNo
/**
* d001 3500
* d002 3650
* d003 2998
*/
protected List<Object[]> executeComplexQuery(String sql, Object... params) {
List<Object[]> list = new ArrayList<>();
Connection conn = null;
PreparedStatement psmt = null;
ResultSet rs = null;
try {
conn = getConn();
psmt = conn.prepareStatement(sql);
setParams(psmt, params);
rs = psmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();//元数据,结果集的结构数据
while (rs.next()) {
int columnCount = rsmd.getColumnCount();//获取结果集的列的数据
Object[] arr = new Object[columnCount];
//jdbc中都是从 1 开始，所以要把 i 改成 从 1 开始
for (int i = 1; i <= columnCount; i++) {
Object columnValue = rs.getObject(i);
//数组从 0 开始，所以要减 1
arr[i - 1] = columnValue;
}
list.add(arr);
}
return list;
} catch (SQLException e) {
throw new RuntimeException(e);
} finally {
close(conn, psmt, rs);
}
}
}
7、 设计Class工具类
package com.csdn.mymvc.util;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
public class ClassUtil {
public static Object createInstance(String entityClassName) {
try {
return Class.forName(entityClassName).getDeclaredConstructor().newInstance();
} catch (InstantiationException | IllegalAccessException | InvocationTargetException | NoSuchMethodException |
ClassNotFoundException e) {
throw new RuntimeException(e);
}
}
public static void setProperty(Object instance, String propertyName, Object propertyValue) {
Class<?> aClass = instance.getClass();
try {
Field field = aClass.getDeclaredField(propertyName);
field.setAccessible(true);
field.set(instance, propertyValue);
} catch (NoSuchFieldException | IllegalAccessException e) {
throw new RuntimeException(e);
}
}
}
8、测试DAO层实现类
package com.csdn.dao.impl;
import com.csdn.fruit.dao.FruitDao;
import com.csdn.fruit.dao.impl.FruitDaoImpl;
import com.csdn.fruit.pojo.Fruit;
import org.junit.Test;
import java.util.List;
public class FruitDaoImplTest {
private FruitDao fruitDao = new FruitDaoImpl();
@Test
public void testAddFruit() {
Fruit fruit = new Fruit("香蕉", 7, 77, "波罗蜜是一种神奇的水果！");
fruitDao.addFruit(fruit);
}
@Test
public void testDelFruit() {
fruitDao.delFruit("哈密瓜");
}
@Test
public void testUpdateFruit() {
Fruit fruit = new Fruit("波罗蜜", 5, 1000, "好吃");
fruitDao.updateFruit(fruit);
}
@Test
public void testGetFruitList() {
List<Fruit> fruitList = fruitDao.getFruitList();
fruitList.stream().forEach(System.out::println);
}
@Test
public void testGetFruitByFname() {
Fruit fruit = fruitDao.getFruitByFname("波罗蜜");
System.out.println(fruit);
}
/*
// this 是谁？ this代表的是 FruitDaoImpl 的实例对象，因为 BaseDao是抽象类，不能直接创建对象，所以 new 的是它的子类对象 FruitDaoImpl
// this.getClass() 获取的是 FruitDaoImpl 的Class对象
// getGenericSuperclass() 获取到的是：BaseDao<Fruit>
// Type 是顶层接口，表示所有的类型。它有一个子接口：ParameterizedType
ParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass();
// Actual:实际的
// getActualTypeArguments() 获取实际的类型参数
Type[] actualTypeArguments = genericSuperclass.getActualTypeArguments();
Type actualTypeArgument = actualTypeArguments[0];
// System.out.println(actualTypeArgument.getTypeName());//com.csdn.fruit.pojo.Fruit
entityClassName = actualTypeArgument.getTypeName();
loadJdbcProperties();
*/
@Test
public void testActualTypeArgument() {
//这个方法是用来测试 actualTypeArgument 实际返回的参数
}
}
9、设计控制台操作菜单
package com.csdn.fruit.view;
import com.csdn.fruit.dao.FruitDao;
import com.csdn.fruit.dao.impl.FruitDaoImpl;
import com.csdn.fruit.pojo.Fruit;
import java.util.List;
import java.util.Scanner;
public class Menu {
Scanner input = new Scanner(System.in);
private FruitDao fruitDao = new FruitDaoImpl();
//显示主菜单
public int showMainMenu() {
System.out.println("================欢迎使用水果库存系统===================");
System.out.println("1.显示库存列表");
System.out.println("2.添加库存记录");
System.out.println("3.查看特定库存");
System.out.println("4.水果下架");
System.out.println("5.退出");
System.out.println("====================================================");
System.out.print("请选择：");
return input.nextInt();
}
//显示库存列表
public void showFruitList() {
List<Fruit> fruitList = fruitDao.getFruitList();
System.out.println("----------------------------------------------------");
System.out.println("名称\t\t单价\t\t库存\t\t备注");
if (fruitList == null || fruitList.size() <= 0) {
System.out.println("对不起，库存为空！");
} else {
/* fruitList.forEach(new Consumer<Fruit>() {
@Override
public void accept(Fruit fruit) {
System.out.println(fruit);
}
});*/
//fruitList.forEach(fruit -> System.out.println(fruit));
fruitList.forEach(System.out::println);
}
System.out.println("----------------------------------------------------");
}
//添加库存记录
public void addFruit() {
System.out.print("请输入水果名称：");
String fname = input.next();
Fruit fruit = fruitDao.getFruitByFname(fname);
if (fruit == null) {
System.out.print("请输入水果单价：");
Integer price = input.nextInt();
System.out.print("请输入水果库存：");
Integer fcount = input.nextInt();
System.out.print("请输入水果备注：");
String remark = input.next();
fruit = new Fruit(fname, price, fcount, remark);
fruitDao.addFruit(fruit);
} else {
System.out.print("请输入追加的库存量：");
Integer fcount = input.nextInt();
fruit.setFcount(fruit.getFcount() + fcount);
fruitDao.updateFruit(fruit);
}
System.out.println("添加成功！");
}
//查看特定库存记录
public void showFruitInfo() {
System.out.print("请输入水果名称：");
String fname = input.next();
Fruit fruit = fruitDao.getFruitByFname(fname);
if (fruit == null) {
System.out.println("对不起，没有找到对应的库存记录！");
} else {
System.out.println("----------------------------------------------------");
System.out.println("名称\t\t单价\t\t库存\t\t备注");
System.out.println(fruit);
System.out.println("----------------------------------------------------");
}
}
//水果下架
public void delFruit() {
System.out.print("请输入水果名称：");
String fname = input.next();
Fruit fruit = fruitDao.getFruitByFname(fname);
if (fruit == null) {
System.out.println("对不起，没有找到需要下架的库存记录！");
} else {
System.out.print("是否确认下架?（Y/N）");
String confirm = input.next();
if ("y".equalsIgnoreCase(confirm)) {
fruitDao.delFruit(fname);
}
}
}
//退出
public boolean exit() {
System.out.print("是否确认退出？（Y/N）");
String confirm = input.next();
boolean flag= !"y".equalsIgnoreCase(confirm);
return flag;
}
}
10、设计客户端
package com.csdn.fruit.view;
public class Client {
public static void main(String[] args) {
Menu m = new Menu();
boolean flag = true;
while (flag) {
int slt = m.showMainMenu();
switch (slt) {
case 1:
m.showFruitList();
break;
case 2:
m.addFruit();
break;
case 3:
m.showFruitInfo();
break;
case 4:
m.delFruit();
break;
case 5:
//方法设计时是否需要返回值，依据是：是否在调用的地方需要留下一些值用于再运算
flag = m.exit();
break;
default:
System.out.println("你不按套路出牌！");
break;
}
}
System.out.println("谢谢使用！再见！");
}
}
