• 使用 jdbc 技术升级水果库存系统（后端最终版本，不包含前端）

     1、配置依赖

    1.     <dependencies>
    2.         <dependency>
    3.             <groupId>org.projectlombokgroupId>
    4.             <artifactId>lombokartifactId>
    5.             <version>1.18.10version>
    6.         dependency>
    7.         <dependency>
    8.             <groupId>junitgroupId>
    9.             <artifactId>junitartifactId>
    10.             <version>4.12version>
    11.             <scope>testscope>
    12.         dependency>
    13.         <dependency>
    14.             <groupId>mysqlgroupId>
    15.             <artifactId>mysql-connector-javaartifactId>
    16.             <version>8.0.28version>
    17.         dependency>
    18.         <dependency>
    19.             <groupId>com.alibabagroupId>
    20.             <artifactId>druidartifactId>
    21.             <version>1.2.16version>
    22.         dependency>
    23.     dependencies>

    2、Fruit 实体类

    1. package com.csdn.fruit.pojo;
    2. import lombok.AllArgsConstructor;
    3. import lombok.Data;
    4. import lombok.NoArgsConstructor;
    5. import java.io.Serializable;
    6. @Data
    7. @NoArgsConstructor
    8. @AllArgsConstructor
    9. public class Fruit implements Serializable {
    10.     private Integer fid;
    11.     private String fname;
    12.     private Integer price;
    13.     private Integer fcount;
    14.     private String remark;
    15.  
    16.     public Fruit(String fname, Integer price, Integer fcount, String remark) {
    17.         this.fname = fname;
    18.         this.price = price;
    19.         this.fcount = fcount;
    20.         this.remark = remark;
    21.     }
    22.     @Override
    23.     public String toString() {
    24.         return fname + "\t\t" + price + "\t\t" + fcount + "\t\t" + remark;
    25.     }
    26. }

     3、设计数据访问对象层DAO接口

    1. package com.csdn.fruit.dao;
    2. import com.csdn.fruit.pojo.Fruit;
    3. import java.util.List;
    4. //dao ：Data Access Object 数据访问对象
    5. //接口设计
    6. public interface FruitDao {
    7.  
    8.     void addFruit(Fruit fruit);
    9.  
    10.     void delFruit(String fname);
    11.  
    12.     void updateFruit(Fruit fruit);
    13.  
    14.     List getFruitList();
    15.  
    16.     Fruit getFruitByFname(String fname);
    17. }

     4、设计DAO层的实现类

    1. package com.csdn.fruit.dao.impl;
    2. import com.csdn.fruit.dao.FruitDao;
    3. import com.csdn.fruit.pojo.Fruit;
    4. import com.csdn.mymvc.dao.BaseDao;
    5. import java.util.List;
    6. public class FruitDaoImpl extends BaseDao implements FruitDao {
    7.     @Override
    8.     public void addFruit(Fruit fruit) {
    9.         String sql = "insert into t_fruit values (0,?,?,?,?)";
    10.         super.executeUpdate(sql, fruit.getFname(), fruit.getPrice(), fruit.getFcount(), fruit.getRemark());
    11.     }
    12.  
    13.     @Override
    14.     public void delFruit(String fname) {
    15.         String sql = "delete from t_fruit where fname=?";
    16.         super.executeUpdate(sql, fname);
    17.     }
    18.  
    19.     @Override
    20.     public void updateFruit(Fruit fruit) {
    21.         String sql = "update  t_fruit set fcount=? where fname = ?";
    22.         super.executeUpdate(sql, fruit.getFcount(), fruit.getFname());
    23.     }
    24.  
    25.     @Override
    26.     public List getFruitList() {
    27.         return super.executeQuery("select * from t_fruit");
    28.     }
    29.  
    30.     @Override
    31.     public Fruit getFruitByFname(String fname) {
    32.         return load("select * from t_fruit where fname = ?", fname);
    33.     }
    34. }

     5、编写 jdbc 配置文件

    1. jdbc.driver=com.mysql.cj.jdbc.Driver
    2. jdbc.url=jdbc:mysql:///fruitdb
    3. jdbc.user=root
    4. jdbc.pwd=123456
    5. jdbc.init_size=5
    6. jdbc.max_active=20
    7. jdbc.max_wait=3000

    6、 设计数据库操作层（抽象类）

    1. package com.csdn.mymvc.dao;
    2. import com.alibaba.druid.pool.DruidDataSource;
    3. import com.csdn.mymvc.util.ClassUtil;
    4. import javax.sql.DataSource;
    5. import java.io.IOException;
    6. import java.io.InputStream;
    7. import java.lang.reflect.ParameterizedType;
    8. import java.lang.reflect.Type;
    9. import java.sql.*;
    10. import java.util.ArrayList;
    11. import java.util.List;
    12. import java.util.Properties;
    13. public abstract class BaseDao {
    14.  
    15.     private String entityClassName;
    16.  
    17.     public BaseDao() {
    18.         // this 是谁？ this代表的是 FruitDaoImpl 的实例对象，因为 BaseDao是抽象类，不能直接创建对象，所以 new 的是它的子类对象 FruitDaoImpl
    19.         // this.getClass() 获取的是 FruitDaoImpl 的Class对象
    20.         // getGenericSuperclass() 获取到的是：BaseDao
    21.         // Type 是顶层接口，表示所有的类型。它有一个子接口：ParameterizedType
    22.         ParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass();
    23.         // Actual:实际的
    24.         // getActualTypeArguments() 获取实际的类型参数
    25.         Type[] actualTypeArguments = genericSuperclass.getActualTypeArguments();
    26.         Type actualTypeArgument = actualTypeArguments[0];
    27.  
    28.         // System.out.println(actualTypeArgument.getTypeName());//com.csdn.fruit.pojo.Fruit
    29.  
    30.         entityClassName = actualTypeArgument.getTypeName();
    31.  
    32.         initDataSource();
    33.     }
    34.  
    35.     private DataSource dataSource;
    36.  
    37.     //加载jdbc.properties文件
    38.     private void initDataSource() {
    39.         try {
    40.             InputStream inputStream = getClass().getClassLoader().getResourceAsStream("jdbc.properties");
    41.             Properties properties = new Properties();
    42.             properties.load(inputStream);
    43.  
    44.             String driver = properties.getProperty("jdbc.driver", "com.mysql.cj.jdbc.Driver");
    45.             String url = properties.getProperty("jdbc.url", "jdbc:mysql:///fruitdb");
    46.             String user = properties.getProperty("jdbc.user", "root");
    47.             String pwd = properties.getProperty("jdbc.pwd", "123456");
    48.             Integer initSize = Integer.parseInt(properties.getProperty("jdbc.init_size", "5"));
    49.             Integer maxActive = Integer.parseInt(properties.getProperty("jdbc.max_active", "10"));
    50.             Integer maxWait = Integer.parseInt(properties.getProperty("jdbc.max_wait", "5000"));
    51.  
    52.             DruidDataSource druidDataSource = new DruidDataSource();
    53.  
    54.             druidDataSource.setDriverClassName(driver);
    55.             druidDataSource.setUrl(url);
    56.             druidDataSource.setUsername(user);
    57.             druidDataSource.setPassword(pwd);
    58.             druidDataSource.setInitialSize(initSize);
    59.             druidDataSource.setMaxActive(maxActive);
    60.             druidDataSource.setMaxWait(maxWait);
    61.  
    62.             dataSource = druidDataSource;
    63.  
    64.         } catch (IOException e) {
    65.             throw new RuntimeException(e);
    66.         }
    67.  
    68.     }
    69.  
    70.     private Connection getConn() throws SQLException {
    71.             return dataSource.getConnection();
    72.     }
    73.  
    74.     private void close(Connection conn, PreparedStatement psmt, ResultSet rs) {
    75.         try {
    76.             if (rs != null) {
    77.                 rs.close();
    78.             }
    79.             if (psmt != null) {
    80.                 psmt.close();
    81.             }
    82.             if (conn != null && !conn.isClosed()) {
    83.                 conn.close();
    84.             }
    85.         } catch (SQLException e) {
    86.             throw new RuntimeException(e);
    87.         }
    88.     }
    89.  
    90.     //抽取执行更新方法
    91.     //执行更新，返回影响行数
    92.     //如果是执行 insert，那么可以尝试返回自增列的值
    93.     protected int executeUpdate(String sql, Object... params) {
    94.  
    95.         boolean insertFlag = sql.trim().toUpperCase().startsWith("INSERT");
    96.  
    97.         Connection conn = null;
    98.         PreparedStatement psmt = null;
    99.         try {
    100.             conn = getConn();
    101.             psmt = insertFlag ? conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS) : conn.prepareStatement(sql);
    102.  
    103.             setParams(psmt, params);
    104.  
    105.             int count = psmt.executeUpdate();
    106.  
    107.             if (insertFlag) {
    108.                 ResultSet rs = psmt.getGeneratedKeys();
    109.                 if (rs.next()) {
    110.                     Long id = rs.getLong(1);
    111.                     count = id.intValue();
    112.                 }
    113.             }
    114.  
    115.             return count;
    116.         } catch (SQLException e) {
    117.             throw new RuntimeException(e);
    118.         } finally {
    119.             close(conn, psmt, null);
    120.         }
    121.     }
    122.  
    123.     //设置参数
    124.     private void setParams(PreparedStatement psmt, Object... params) throws SQLException {
    125.         if (params != null && params.length > 0) {
    126.             for (int i = 0; i < params.length; i++) {
    127.                 psmt.setObject(i + 1, params[i]);
    128.             }
    129.         }
    130.     }
    131.  
    132.     //执行查询，返回集合
    133.     protected List executeQuery(String sql, Object... params) {
    134.         List list = new ArrayList<>();
    135.         Connection conn = null;
    136.         PreparedStatement psmt = null;
    137.         ResultSet rs = null;
    138.  
    139.         try {
    140.             conn = getConn();
    141.             psmt = conn.prepareStatement(sql);
    142.             setParams(psmt, params);
    143.             rs = psmt.executeQuery();
    144.             ResultSetMetaData rsmd = rs.getMetaData();//元数据,结果集的结构数据
    145.             while (rs.next()) {
    146.                 //T t = new T();  T仅仅是一个符号，所以不能 new
    147.                 T t = (T) ClassUtil.createInstance(entityClassName);
    148.                 int columnCount = rsmd.getColumnCount();//获取结果集的列的数据
    149.                 //jdbc中都是从 1 开始，所以要把 i 改成 从 1 开始
    150.                 for (int i = 1; i <= columnCount; i++) {
    151.                     //假设循环 5 次，得到 5 个值，应该对应的是一个对象的 5 个属性的值
    152.                     String columnName = rsmd.getColumnLabel(i);
    153.                     Object columnValue = rs.getObject(i);
    154.                     //给 t 这个对象的 columnName 属性赋 columnValue 值
    155.                     ClassUtil.setProperty(t, columnName, columnValue);
    156.                 }
    157.                 list.add(t);
    158.             }
    159.             return list;
    160.         } catch (SQLException e) {
    161.             throw new RuntimeException(e);
    162.         } finally {
    163.             close(conn, psmt, rs);
    164.         }
    165.     }
    166.  
    167.     protected T load(String sql, Object... params) {
    168.  
    169.         Connection conn = null;
    170.         PreparedStatement psmt = null;
    171.         ResultSet rs = null;
    172.  
    173.         try {
    174.             conn = getConn();
    175.             psmt = conn.prepareStatement(sql);
    176.             setParams(psmt, params);
    177.             rs = psmt.executeQuery();
    178.             ResultSetMetaData rsmd = rs.getMetaData();//元数据,结果集的结构数据
    179.             if (rs.next()) {
    180.                 //T t = new T();  T仅仅是一个符号，所以不能 new
    181.                 T t = (T) ClassUtil.createInstance(entityClassName);
    182.                 int columnCount = rsmd.getColumnCount();//获取结果集的列的数据
    183.                 //jdbc中都是从 1 开始，所以要把 i 改成 从 1 开始
    184.                 for (int i = 1; i <= columnCount; i++) {
    185.                     //假设循环 5 次，得到 5 个值，应该对应的是一个对象的 5 个属性的值
    186.                     String columnName = rsmd.getColumnLabel(i);
    187.                     Object columnValue = rs.getObject(i);
    188.                     //给 t 这个对象的 columnName 属性赋 columnValue 值
    189.                     ClassUtil.setProperty(t, columnName, columnValue);
    190.                 }
    191.                 return t;
    192.             }
    193.         } catch (SQLException e) {
    194.             throw new RuntimeException(e);
    195.         } finally {
    196.             close(conn, psmt, rs);
    197.         }
    198.         return null;
    199.     }
    200.  
    201.     //select max(age) as max_age ,  avg(age)  as avg_age from t_user
    202.     // 28               24.5
    203.  
    204.     //select deptNo,avg(sal)  as avg_sal  from emp  group by deptNo
    205.  
    206.     /**
    207.      * d001       3500
    208.      * d002       3650
    209.      * d003       2998
    210.      */
    211.     protected List executeComplexQuery(String sql, Object... params) {
    212.         List list = new ArrayList<>();
    213.         Connection conn = null;
    214.         PreparedStatement psmt = null;
    215.         ResultSet rs = null;
    216.  
    217.         try {
    218.             conn = getConn();
    219.             psmt = conn.prepareStatement(sql);
    220.             setParams(psmt, params);
    221.             rs = psmt.executeQuery();
    222.             ResultSetMetaData rsmd = rs.getMetaData();//元数据,结果集的结构数据
    223.             while (rs.next()) {
    224.                 int columnCount = rsmd.getColumnCount();//获取结果集的列的数据
    225.                 Object[] arr = new Object[columnCount];
    226.                 //jdbc中都是从 1 开始，所以要把 i 改成 从 1 开始
    227.                 for (int i = 1; i <= columnCount; i++) {
    228.                     Object columnValue = rs.getObject(i);
    229.                     //数组从 0 开始，所以要减 1
    230.                     arr[i - 1] = columnValue;
    231.                 }
    232.                 list.add(arr);
    233.             }
    234.             return list;
    235.         } catch (SQLException e) {
    236.             throw new RuntimeException(e);
    237.         } finally {
    238.             close(conn, psmt, rs);
    239.         }
    240.     }
    241. }

    7、 设计Class工具类

    1. package com.csdn.mymvc.util;
    2. import java.lang.reflect.Field;
    3. import java.lang.reflect.InvocationTargetException;
    4. public class ClassUtil {
    5.  
    6.     public static Object createInstance(String entityClassName) {
    7.         try {
    8.             return Class.forName(entityClassName).getDeclaredConstructor().newInstance();
    9.         } catch (InstantiationException | IllegalAccessException | InvocationTargetException | NoSuchMethodException |
    10.                  ClassNotFoundException e) {
    11.             throw new RuntimeException(e);
    12.         }
    13.     }
    14.  
    15.     public static void setProperty(Object instance, String propertyName, Object propertyValue) {
    16.         Class aClass = instance.getClass();
    17.         try {
    18.             Field field = aClass.getDeclaredField(propertyName);
    19.             field.setAccessible(true);
    20.             field.set(instance, propertyValue);
    21.         } catch (NoSuchFieldException | IllegalAccessException e) {
    22.             throw new RuntimeException(e);
    23.         }
    24.     }
    25. }

     8、测试DAO层实现类

    1. package com.csdn.dao.impl;
    2. import com.csdn.fruit.dao.FruitDao;
    3. import com.csdn.fruit.dao.impl.FruitDaoImpl;
    4. import com.csdn.fruit.pojo.Fruit;
    5. import org.junit.Test;
    6. import java.util.List;
    7. public class FruitDaoImplTest {
    8.  
    9.     private FruitDao fruitDao = new FruitDaoImpl();
    10.  
    11.     @Test
    12.     public void testAddFruit() {
    13.         Fruit fruit = new Fruit("香蕉", 7, 77, "波罗蜜是一种神奇的水果！");
    14.         fruitDao.addFruit(fruit);
    15.     }
    16.  
    17.     @Test
    18.     public void testDelFruit() {
    19.         fruitDao.delFruit("哈密瓜");
    20.     }
    21.  
    22.     @Test
    23.     public void testUpdateFruit() {
    24.         Fruit fruit = new Fruit("波罗蜜", 5, 1000, "好吃");
    25.         fruitDao.updateFruit(fruit);
    26.     }
    27.  
    28.     @Test
    29.     public void testGetFruitList() {
    30.         List fruitList = fruitDao.getFruitList();
    31.         fruitList.stream().forEach(System.out::println);
    32.     }
    33.  
    34.     @Test
    35.     public void testGetFruitByFname() {
    36.  
    37.         Fruit fruit = fruitDao.getFruitByFname("波罗蜜");
    38.         System.out.println(fruit);
    39.     }
    40.  
    41. /*
    42.     // this 是谁？ this代表的是 FruitDaoImpl 的实例对象，因为 BaseDao是抽象类，不能直接创建对象，所以 new 的是它的子类对象 FruitDaoImpl
    43.     // this.getClass() 获取的是 FruitDaoImpl 的Class对象
    44.     // getGenericSuperclass() 获取到的是：BaseDao
    45.     // Type 是顶层接口，表示所有的类型。它有一个子接口：ParameterizedType
    46.     ParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass();
    47.     // Actual:实际的
    48.     // getActualTypeArguments() 获取实际的类型参数
    49.     Type[] actualTypeArguments = genericSuperclass.getActualTypeArguments();
    50.     Type actualTypeArgument = actualTypeArguments[0];
    52.     // System.out.println(actualTypeArgument.getTypeName());//com.csdn.fruit.pojo.Fruit
    54.     entityClassName = actualTypeArgument.getTypeName();
    56.     loadJdbcProperties();
    57. */
    58.     @Test
    59.     public void testActualTypeArgument() {
    60.             //这个方法是用来测试  actualTypeArgument 实际返回的参数
    61.     }
    62. }

     9、设计控制台操作菜单

    1. package com.csdn.fruit.view;
    2. import com.csdn.fruit.dao.FruitDao;
    3. import com.csdn.fruit.dao.impl.FruitDaoImpl;
    4. import com.csdn.fruit.pojo.Fruit;
    5. import java.util.List;
    6. import java.util.Scanner;
    7. public class Menu {
    8.     Scanner input = new Scanner(System.in);
    9.     private FruitDao fruitDao = new FruitDaoImpl();
    10.     //显示主菜单
    11.     public int showMainMenu() {
    12.         System.out.println("================欢迎使用水果库存系统===================");
    13.         System.out.println("1.显示库存列表");
    14.         System.out.println("2.添加库存记录");
    15.         System.out.println("3.查看特定库存");
    16.         System.out.println("4.水果下架");
    17.         System.out.println("5.退出");
    18.         System.out.println("====================================================");
    19.         System.out.print("请选择：");
    20.  
    21.         return input.nextInt();
    22.     }
    23.     //显示库存列表
    24.     public void showFruitList() {
    25.         List fruitList = fruitDao.getFruitList();
    26.         System.out.println("----------------------------------------------------");
    27.         System.out.println("名称\t\t单价\t\t库存\t\t备注");
    28.         if (fruitList == null || fruitList.size() <= 0) {
    29.             System.out.println("对不起，库存为空！");
    30.         } else {
    31.                /* fruitList.forEach(new Consumer() {
    32.                 @Override
    33.                 public void accept(Fruit fruit) {
    34.                     System.out.println(fruit);
    35.                 }
    36.             });*/
    37.  
    38.             //fruitList.forEach(fruit -> System.out.println(fruit));
    39.             fruitList.forEach(System.out::println);
    40.         }
    41.         System.out.println("----------------------------------------------------");
    42.     }
    43.     //添加库存记录
    44.     public void addFruit() {
    45.         System.out.print("请输入水果名称：");
    46.         String fname = input.next();
    47.  
    48.         Fruit fruit = fruitDao.getFruitByFname(fname);
    49.  
    50.         if (fruit == null) {
    51.             System.out.print("请输入水果单价：");
    52.             Integer price = input.nextInt();
    53.             System.out.print("请输入水果库存：");
    54.             Integer fcount = input.nextInt();
    55.             System.out.print("请输入水果备注：");
    56.             String remark = input.next();
    57.  
    58.             fruit = new Fruit(fname, price, fcount, remark);
    59.             fruitDao.addFruit(fruit);
    60.         } else {
    61.             System.out.print("请输入追加的库存量：");
    62.             Integer fcount = input.nextInt();
    63.             fruit.setFcount(fruit.getFcount() + fcount);
    64.             fruitDao.updateFruit(fruit);
    65.         }
    66.         System.out.println("添加成功！");
    67.     }
    68.     //查看特定库存记录
    69.     public void showFruitInfo() {
    70.         System.out.print("请输入水果名称：");
    71.         String fname = input.next();
    72.         Fruit fruit = fruitDao.getFruitByFname(fname);
    73.  
    74.         if (fruit == null) {
    75.             System.out.println("对不起，没有找到对应的库存记录！");
    76.         } else {
    77.             System.out.println("----------------------------------------------------");
    78.             System.out.println("名称\t\t单价\t\t库存\t\t备注");
    79.             System.out.println(fruit);
    80.             System.out.println("----------------------------------------------------");
    81.         }
    82.     }
    83.     //水果下架
    84.     public void delFruit() {
    85.         System.out.print("请输入水果名称：");
    86.         String fname = input.next();
    87.  
    88.         Fruit fruit = fruitDao.getFruitByFname(fname);
    89.         if (fruit == null) {
    90.             System.out.println("对不起，没有找到需要下架的库存记录！");
    91.         } else {
    92.             System.out.print("是否确认下架?（Y/N）");
    93.             String confirm = input.next();
    94.             if ("y".equalsIgnoreCase(confirm)) {
    95.                 fruitDao.delFruit(fname);
    96.             }
    97.  
    98.         }
    99.     }
    100.     //退出
    101.     public boolean exit() {
    102.         System.out.print("是否确认退出？（Y/N）");
    103.         String confirm = input.next();
    104.         boolean flag= !"y".equalsIgnoreCase(confirm);
    105.         return flag;
    106.     }
    107. }

     10、设计客户端

    1. package com.csdn.fruit.view;
    2. public class Client {
    3.     public static void main(String[] args) {
    4.         Menu m = new Menu();
    5.         boolean flag = true;
    6.         while (flag) {
    7.             int slt = m.showMainMenu();
    8.             switch (slt) {
    9.                 case 1:
    10.                     m.showFruitList();
    11.                     break;
    12.                 case 2:
    13.                     m.addFruit();
    14.                     break;
    15.                 case 3:
    16.                     m.showFruitInfo();
    17.                     break;
    18.                 case 4:
    19.                     m.delFruit();
    20.                     break;
    21.                 case 5:
    22.                     //方法设计时是否需要返回值，依据是：是否在调用的地方需要留下一些值用于再运算
    23.                     flag = m.exit();
    24.                     break;
    25.                 default:
    26.                     System.out.println("你不按套路出牌！");
    27.                     break;
    28.             }
    29.         }
    30.         System.out.println("谢谢使用！再见！");
    31.     }
    32. }

     

  • 相关阅读:
    个人博客测试报告
    3.深入理解Java并发编程
    【2024最新华为OD-C/D卷试题汇总】[支持在线评测] 机器人搬砖(100分) - 三语言AC题解(Python/Java/Cpp)
    Unity学习03:Scene 视图
    1119 Pre- and Post-order Traversals
    云原生中间件RocketMQ(三)RocketMQ集群（多Master和多Master-Slave方式）部署实操
    基于串行并行ADMM算法的主从配电网分布式优化控制研究（Matlab代码实现）
    揭秘GES超大规模图计算引擎HyG：图切分
    免费word转换pdf的软件
    科技云报道：防患于未然，云安全要像空气和水一样无处不在
  • 原文地址：https://blog.csdn.net/m0_65152767/article/details/134082847