使用 jdbc 技术升级水果库存系统（优化版本）

• 抽取执行更新方法
• 抽取查询方法 —— ResultSetMetaData  

  ResultSetMetaData rsmd = rs.getMetaData();//元数据,结果集的结构数据

• 抽取查询方法 —— 解析结果集封装成实体对象
• 提取 获取连接 和 释放资源 的方法
• 将数据库配置信息转移到配置文件

    <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></dependencies>

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;}@Overridepublic String toString() {return fname + "\t\t" + price + "\t\t" + fcount + "\t\t" + remark;}
}

•  

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql:///fruitdb
jdbc.user=root
jdbc.pwd=123456

package com.csdn.mymvc.dao;
import com.csdn.mymvc.util.ClassUtil;
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 DRIVER;private String URL;private String USER;private String PWD;private String entityClassName;public BaseDao() {// this 是谁？ this代表的是 FruitDaoImpl 的实例对象，因为 BaseDao是抽象类，不能直接创建对象，所以 new 的是它的子类对象 FruitDaoImpl// this.getClass() 获取的是 FruitDaoImpl 的Class对象// getGenericSuperclass() 获取到的是：BaseDao<Fruit>// Type 是顶层接口，表示所有的类型。它有一个子接口：ParameterizedTypeParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass();// Actual:实际的// getActualTypeArguments() 获取实际的类型参数Type[] actualTypeArguments = genericSuperclass.getActualTypeArguments();Type actualTypeArgument = actualTypeArguments[0];// System.out.println(actualTypeArgument.getTypeName());//com.csdn.fruit.pojo.FruitentityClassName = actualTypeArgument.getTypeName();loadJdbcProperties();}//加载jdbc.properties文件private void loadJdbcProperties() {try {InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");Properties properties = new Properties();properties.load(inputStream);DRIVER = properties.getProperty("jdbc.driver", "com.mysql.cj.jdbc.Driver");URL = properties.getProperty("jdbc.url", "jdbc:mysql:///fruitdb");USER = properties.getProperty("jdbc.user", "root");PWD = properties.getProperty("jdbc.pwd", "123456");} catch (IOException e) {throw new RuntimeException(e);}}private Connection getConn() {try {Class.forName(DRIVER);return DriverManager.getConnection(URL, USER, PWD);} catch (ClassNotFoundException | SQLException e) {throw new RuntimeException(e);}}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);}}//抽取执行更新方法//执行更新，返回影响行数protected int executeUpdate(String sql, Object... params) {PreparedStatement psmt = null;Connection conn = null;try {conn = getConn();psmt = conn.prepareStatement(sql);setParams(psmt, params);return psmt.executeUpdate();} 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仅仅是一个符号，所以不能 newT 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仅仅是一个符号，所以不能 newT 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;}
}

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

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

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 {@Overridepublic void addFruit(Fruit fruit) {String sql = "insert into t_fruit values (0,?,?,?,?)";super.executeUpdate(sql, fruit.getFname(), fruit.getPrice(), fruit.getFcount(), fruit.getRemark());}@Overridepublic void delFruit(String fname) {String sql = "delete from t_fruit where fname=?";super.executeUpdate(sql, fname);}@Overridepublic void updateFruit(Fruit fruit) {String sql = "update  t_fruit set fcount=? where fname = ?";super.executeUpdate(sql, fruit.getFcount(), fruit.getFname());}@Overridepublic List<Fruit> getFruitList() {return super.executeQuery("select * from t_fruit");}@Overridepublic Fruit getFruitByFname(String fname) {return load("select * from t_fruit where fname = ?", fname);}
}

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

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("谢谢使用！再见！");}
}

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();@Testpublic void testAddFruit() {Fruit fruit = new Fruit("香蕉", 7, 77, "波罗蜜是一种神奇的水果！");fruitDao.addFruit(fruit);}@Testpublic void testDelFruit() {fruitDao.delFruit("哈密瓜");}@Testpublic void testUpdateFruit() {Fruit fruit = new Fruit("波罗蜜", 5, 1000, "好吃");fruitDao.updateFruit(fruit);}@Testpublic void testGetFruitList() {List<Fruit> fruitList = fruitDao.getFruitList();fruitList.stream().forEach(System.out::println);}@Testpublic void testGetFruitByFname() {Fruit fruit = fruitDao.getFruitByFname("波罗蜜");System.out.println(fruit);}/*// this 是谁？ this代表的是 FruitDaoImpl 的实例对象，因为 BaseDao是抽象类，不能直接创建对象，所以 new 的是它的子类对象 FruitDaoImpl// this.getClass() 获取的是 FruitDaoImpl 的Class对象// getGenericSuperclass() 获取到的是：BaseDao<Fruit>// Type 是顶层接口，表示所有的类型。它有一个子接口：ParameterizedTypeParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass();// Actual:实际的// getActualTypeArguments() 获取实际的类型参数Type[] actualTypeArguments = genericSuperclass.getActualTypeArguments();Type actualTypeArgument = actualTypeArguments[0];// System.out.println(actualTypeArgument.getTypeName());//com.csdn.fruit.pojo.FruitentityClassName = actualTypeArgument.getTypeName();loadJdbcProperties();
*/@Testpublic void testActualTypeArgument() {//这个方法是用来测试  actualTypeArgument 实际返回的参数}
}