Upgrade the fruit inventory system using jdbc technology (final version of the backend, not including the frontend)

1. Configuration dependencies

 <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 entity class

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. Design the data access object layer DAO interface

package com.csdn.fruit.dao;
import com.csdn.fruit.pojo.Fruit;
import java.util.List;
//dao: Data Access Object data access object
//Interface design
public interface FruitDao {

    void addFruit(Fruit fruit);

    void delFruit(String fname);

    void updateFruit(Fruit fruit);

    List<Fruit> getFruitList();

    Fruit getFruitByFname(String fname);
}

4. Design the implementation class of the DAO layer

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. Write jdbc configuration file

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. Design database operation layer (abstract class)

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() {
        //Who is this? This represents the instance object of FruitDaoImpl. Because BaseDao is an abstract class and cannot directly create objects, new is its subclass object FruitDaoImpl.
        // this.getClass() obtains the Class object of FruitDaoImpl
        // getGenericSuperclass() gets: BaseDao<Fruit>
        // Type is the top-level interface, representing all types. It has a sub-interface: ParameterizedType
        ParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass();
        // Actual: actual
        // getActualTypeArguments() gets the actual type parameters
        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;

    //Load jdbc.properties file
    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 & amp; & amp; !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //Extract execution update method
    //Perform the update and return the number of affected rows
    //If you are executing insert, you can try to return the value of the auto-increment column
    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);
        }
    }

    //Setting parameters
    private void setParams(PreparedStatement psmt, Object... params) throws SQLException {
        if (params != null & amp; & amp; params.length > 0) {
            for (int i = 0; i < params.length; i + + ) {
                psmt.setObject(i + 1, params[i]);
            }
        }
    }

    //Execute the query and return the collection
    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();//Metadata, structural data of the result set
            while (rs.next()) {
                //T t = new T(); T is just a symbol, so it cannot be new
                T t = (T) ClassUtil.createInstance(entityClassName);
                int columnCount = rsmd.getColumnCount();//Get the column data of the result set
                //JDBC all starts from 1, so change i to start from 1
                for (int i = 1; i <= columnCount; i + + ) {
                    //Suppose you loop 5 times and get 5 values, which should correspond to the values of 5 attributes of an object.
                    String columnName = rsmd.getColumnLabel(i);
                    Object columnValue = rs.getObject(i);
                    //Assign the columnValue value to the columnName property of the object t
                    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();//Metadata, structural data of the result set
            if (rs.next()) {
                //T t = new T(); T is just a symbol, so it cannot be new
                T t = (T) ClassUtil.createInstance(entityClassName);
                int columnCount = rsmd.getColumnCount();//Get the column data of the result set
                //JDBC all starts from 1, so change i to start from 1
                for (int i = 1; i <= columnCount; i + + ) {
                    //Suppose you loop 5 times and get 5 values, which should correspond to the values of 5 attributes of an object.
                    String columnName = rsmd.getColumnLabel(i);
                    Object columnValue = rs.getObject(i);
                    //Assign the columnValue value to the columnName property of the object t
                    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();//Metadata, structural data of the result set
            while (rs.next()) {
                int columnCount = rsmd.getColumnCount();//Get the column data of the result set
                Object[] arr = new Object[columnCount];
                //JDBC all starts from 1, so change i to start from 1
                for (int i = 1; i <= columnCount; i + + ) {
                    Object columnValue = rs.getObject(i);
                    //The array starts at 0, so subtract 1
                    arr[i - 1] = columnValue;
                }
                list.add(arr);
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            close(conn, psmt, rs);
        }
    }
}

7. Design Class tool 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. Test DAO layer implementation class

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("Banana", 7, 77, "Jackfruit is a magical fruit!");
        fruitDao.addFruit(fruit);
    }

    @Test
    public void testDelFruit() {
        fruitDao.delFruit("Halmimelon");
    }

    @Test
    public void testUpdateFruit() {
        Fruit fruit = new Fruit("Jackfruit", 5, 1000, "Delicious");
        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("Jackfruit");
        System.out.println(fruit);
    }

/*
    //Who is this? This represents the instance object of FruitDaoImpl. Because BaseDao is an abstract class and cannot directly create objects, new is its subclass object FruitDaoImpl.
    // this.getClass() obtains the Class object of FruitDaoImpl
    // getGenericSuperclass() gets: BaseDao<Fruit>
    // Type is the top-level interface, representing all types. It has a sub-interface: ParameterizedType
    ParameterizedType genericSuperclass = (ParameterizedType) this.getClass().getGenericSuperclass();
    // Actual: actual
    // getActualTypeArguments() gets the actual type parameters
    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() {
            //This method is used to test the parameters actually returned by actualTypeArgument
    }
}

9. Design console operation menu

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();
    //Display main menu
    public int showMainMenu() {
        System.out.println("================Welcome to the fruit inventory system===================" );
        System.out.println("1.Display inventory list");
        System.out.println("2.Add inventory record");
        System.out.println("3. View specific inventory");
        System.out.println("4. Fruits removed from shelves");
        System.out.println("5.Exit");
        System.out.println("========================================== ==========");
        System.out.print("Please select:");

        return input.nextInt();
    }
    //Display inventory list
    public void showFruitList() {
        List<Fruit> fruitList = fruitDao.getFruitList();
        System.out.println("--------------------------------------------- ----------");
        System.out.println("Name\t\tUnit price\t\tInventory\t\tRemarks");
        if (fruitList == null || fruitList.size() <= 0) {
            System.out.println("Sorry, the inventory is empty!");
        } 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("--------------------------------------------- ----------");
    }
    //Add inventory record
    public void addFruit() {
        System.out.print("Please enter the name of the fruit:");
        String fname = input.next();

        Fruit fruit = fruitDao.getFruitByFname(fname);

        if (fruit == null) {
            System.out.print("Please enter the fruit unit price:");
            Integer price = input.nextInt();
            System.out.print("Please enter fruit inventory:");
            Integer fcount = input.nextInt();
            System.out.print("Please enter fruit remarks:");
            String remark = input.next();

            fruit = new Fruit(fname, price, fcount, remark);
            fruitDao.addFruit(fruit);
        } else {
            System.out.print("Please enter the additional inventory:");
            Integer fcount = input.nextInt();
            fruit.setFcount(fruit.getFcount() + fcount);
            fruitDao.updateFruit(fruit);
        }
        System.out.println("Added successfully!");
    }
    //View specific inventory records
    public void showFruitInfo() {
        System.out.print("Please enter the name of the fruit:");
        String fname = input.next();
        Fruit fruit = fruitDao.getFruitByFname(fname);

        if (fruit == null) {
            System.out.println("Sorry, no corresponding inventory record found!");
        } else {
            System.out.println("--------------------------------------------- ----------");
            System.out.println("Name\t\tUnit price\t\tInventory\t\tRemarks");
            System.out.println(fruit);
            System.out.println("--------------------------------------------- ----------");
        }
    }
    //Fruits removed from shelves
    public void delFruit() {
        System.out.print("Please enter the name of the fruit:");
        String fname = input.next();

        Fruit fruit = fruitDao.getFruitByFname(fname);
        if (fruit == null) {
            System.out.println("Sorry, no inventory records that need to be removed from the shelves were found!");
        } else {
            System.out.print("Are you sure you want to take it off the shelves? (Y/N)");
            String confirm = input.next();
            if ("y".equalsIgnoreCase(confirm)) {
                fruitDao.delFruit(fname);
            }

        }
    }
    //quit
    public boolean exit() {
        System.out.print("Confirm to exit? (Y/N)");
        String confirm = input.next();
        boolean flag= !"y".equalsIgnoreCase(confirm);
        return flag;
    }
}

10. Design client

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:
                    //Whether a return value is required when designing a method is based on: whether some values need to be left at the place of call for further calculations
                    flag = m.exit();
                    break;
                default:
                    System.out.println("You don't play according to the routine!");
                    break;
            }
        }
        System.out.println("Thank you for using! Goodbye!");
    }
}

The knowledge points of the article match the official knowledge files, and you can further learn relevant knowledge. Java skill treeUsing JDBC to operate databasesJDBC Overview 138353 people are learning the system