Upgrade fruit inventory system using jdbc technology

 <dependencies>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>1.18.10</version>
            <scope>compile</scope>
        </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>
            <scope>compile</scope>
        </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;
    }
    @Override
    public String toString() {
        return fname + "\t\t" + price + "\t\t" + fcount + "\t\t" + remark;
    }
}

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 {

    String URL = "jdbc:mysql:///fruitdb";
    String USER = "root";
    String PASSWORD = "123456";

    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 java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class FruitDaoImpl implements FruitDao {
    @Override
    public void addFruit(Fruit fruit) {
        PreparedStatement psmt = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER, PASSWORD);

            String sql = "insert into t_fruit values (0,?,?,?,?)";

            psmt = conn.prepareStatement(sql);

            psmt.setString(1, fruit.getFname());
            psmt.setInt(2, fruit.getPrice());
            psmt.setInt(3, fruit.getFcount());
            psmt.setString(4, fruit.getRemark());

            psmt.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (psmt!=null) {
                    psmt.close();
                }
                if (conn!=null & amp; & amp;!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    @Override
    public void delFruit(String fname) {
        PreparedStatement psmt = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER, PASSWORD);

            String sql = "delete from t_fruit where fname=?";

            psmt = conn.prepareStatement(sql);

            psmt.setString(1, fname);

            psmt.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (psmt!=null) {
                    psmt.close();
                }
                if (conn!=null & amp; & amp;!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    @Override
    public void updateFruit(Fruit fruit) {
        PreparedStatement psmt = null;
        Connection conn = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER, PASSWORD);

            String sql = "update t_fruit set fcount=? where fname=?";

            psmt = conn.prepareStatement(sql);

            psmt.setInt(1,fruit.getFcount());
            psmt.setString(2,fruit.getFname());

            psmt.executeUpdate();

        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (psmt!=null) {
                    psmt.close();
                }
                if (conn!=null & amp; & amp;!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }
    @Override
    public List<Fruit> getFruitList() {
        List<Fruit> fruitList = new ArrayList<>();
        PreparedStatement psmt = null;
        Connection conn = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER, PASSWORD);

            String sql = "select * from t_fruit";

            psmt = conn.prepareStatement(sql);

            rs = psmt.executeQuery();

            while (rs.next()) {
                Integer fid = rs.getInt(1);
                String fname = rs.getString(2);
                Integer price = rs.getInt("price");
                Integer fcount = rs.getInt("fcount");
                String remark = rs.getString("remark");

                Fruit fruit = new Fruit(fid, fname, price, fcount, remark);

                fruitList.add(fruit);
            }
            return fruitList;
        } catch (ClassNotFoundException | SQLException e) {
            throw new RuntimeException(e);
        } finally {
            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);
            }
        }
    }
    @Override
    public Fruit getFruitByFname(String fname) {
        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(URL, USER, PASSWORD);

            String sql = "select * from t_fruit where fname like ?";

            psmt = conn.prepareStatement(sql);

            psmt.setString(1, fname);

            rs = psmt.executeQuery();

            if (rs.next()) {
                int fid = rs.getInt(1);
                int price = rs.getInt(3);
                int fcount = rs.getInt("fcount");
                String remark = rs.getString("remark");

                return new Fruit(fid, fname, price, fcount, remark);
            }
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs!=null) {
                    rs.close();
                }
                if (psmt!=null) {
                    psmt.close();
                }
                if (conn!=null & amp; & amp;!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }
}

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 fruit name:");
        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;
    }
}

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 138,175 people are learning the system