druid,mysql实现的增删改查（控制台）

实体类：

package com.example;

import org.omg.CORBA.PRIVATE_MEMBER;

public class Brand {
    private int id;
    private String brandName;
    private String companyName;
    private int ordered;
    private String description;
    private int status;

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public int getOrdered() {
        return ordered;
    }

    public void setOrdered(int ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public int getStatus() {
        return status;
    }

    public void setStatus(int status) {
        this.status = status;
    }

    public Brand(int id, String brandName, String companyName, int ordered, String description, int status) {
        this.brandName = brandName;
        this.companyName = companyName;
        this.ordered = ordered;
        this.description = description;
        this.status = status;
    }
}

主函数：

package com.example;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.Scanner;

public class CrudExample {
    //    集合储存所有商品信息
    private static List<Brand> brandList = new ArrayList<>();
    //    创建扫描器对象
    private static Scanner sc = new Scanner(System.in);

    public static void main(String[] args) throws Exception {
        while (true) {
            System.out.println("<------------------商品信息的增删改查----------------------->");
            System.out.println("1，查询所有商品操作");
            System.out.println("2，增加商品信息操作");
            System.out.println("3，更改商品信息操作");
            System.out.println("4，删除商品信息操作");
            System.out.println("请选择您的操作:");
            String s = sc.nextLine();
            switch (s) {
                case "1":
                    selectAllBrands();
                    break;
                case "2":
                    addBrand();
                    break;
                case "3":
                    editBrand();
                    break;
                case "4":
                    deleteBrand();
                    break;
                default:
                    System.out.println("请重新选择你的操作");
            }
        }
    }

    private static void deleteBrand() throws Exception{
        System.out.println("<---删除产品信息界面--->");
        System.out.println("请输入删除的产品id");
        final int s = sc.nextInt();
        final ResultSet set = selectById(s);
        if(set.next()){
            String sql = "delete from tb_brand where id=?";
            final PreparedStatement pst = pst(sql);
            pst.setInt(1,s);
            final int i = pst.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
                return;
            }else{
                System.out.println("删除失败");
            }
        }else{
            System.out.println("删除失败");
        }
    }

    private static void addBrand() throws SQLException {
        System.out.println("<---添加产品信息界面--->");
        System.out.println("请输入商品名称");
        final String brandName = sc.nextLine();
        System.out.println("请输入公司名称");
        final String companyName = sc.nextLine();
        System.out.println("请输入预定数量（数字）");
        final String number = sc.nextLine();
        System.out.println("请输入商品描述");
        final String description = sc.nextLine();
        System.out.println("请输入商品状态（0/1）");
        final String status = sc.nextLine();
        String sql = "insert into tb_brand(brand_name,company_name,ordered,description,status) values (?,?,?,?,?)";
        final PreparedStatement pst = pst(sql);
        pst.setString(1,brandName);
        pst.setString(2,companyName);
        pst.setInt(3,Integer.parseInt(number));
        pst.setString(4,description);
        pst.setInt(5,Integer.parseInt(status));
        final int i = pst.executeUpdate();
        if(i>0){
            System.out.println("新增成功");
        }else
        {
            System.out.println("新增失败");
        }
    }

    private static void editBrand() throws SQLException {
        System.out.println("<---根据id修改界面所有产品界面--->");
        System.out.println("请输入要修改的id");
        final String s = sc.nextLine();
        final int i = Integer.parseInt(s);
        while (true) {
            final ResultSet set = selectById(i);
            if (set.next()) {
//                查询该id的商品信息
                System.out.println("商品信息如下");
                System.out.print("商品名称：" + set.getString(2) + "   ");
                System.out.print("公司名称：" + set.getString(3) + "   ");
                System.out.print("预定数量：" + set.getInt(4) + "   ");
                System.out.print("商品描述：" + set.getString(5) + "   ");
                System.out.println("状态：" + set.getInt(6) + "   ");
                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("请选择修改类型 输入q退出操作");
                final String s1 = sc.nextLine();
                if(!"q".equals(s1)){
                    switch (s1) {
                        case "1":
                            editBrandItem("brand_name", i);
                            break;
                        case "2":
                            editBrandItem("company_name", i);
                            break;
                        case "3":
                            editBrandItem("ordered", i);
                            break;
                        case "4":
                            editBrandItem("description", i);
                            break;
                        case "5":
                            editBrandItem("status", i);
                            break;
                        default:
                            System.out.println("输入错误,请重新输入");
                    }
                }else {
                    break;
                }

            } else {
                System.out.println("该用户不存在,请重新输入商品id");
            }
        }

    }

    private static ResultSet selectById(int i) throws SQLException {
        String sql = "select * from tb_brand where id = ?";
        final PreparedStatement pst = pst(sql);
        //为问号赋值
        pst.setInt(1, i);
        final ResultSet set = pst.executeQuery();
        return set;
    }

    private static void editBrandItem(String name, int id) throws SQLException {
        System.out.println("请输入修改后的内容");
        String sql = "update tb_brand set " + name + "= ? where id =?";
        final String s = sc.nextLine();
        final PreparedStatement pst = pst(sql);
        pst.setString(1, s);
        pst.setInt(2, id);
        final int i = pst.executeUpdate();
        if (i > 0) {
            System.out.println("修改成功");
        } else {
            System.out.println("修改失败");
        }
        return;
    }


    private static void selectAllBrands() throws SQLException {
        System.out.println("查询所有产品界面--->");
        String sql = "select * from tb_brand";
        //获取预编译SQL的执行sql对象进行查询操作
        final PreparedStatement pst = pst(sql);
        final ResultSet brand = pst.executeQuery();
        while (brand.next()) {
            final int id = brand.getInt(1);
            final String brandName = brand.getString(2);
            final String companyName = brand.getString(3);
            final int ordered = brand.getInt(4);
            final String description = brand.getString(5);
            final int status = brand.getInt(6);
            brandList.add(new Brand(id, brandName, companyName, ordered, description, status));
        }
//        遍历集合信息
        System.out.println("商品id            商品名称            公司名称            预定数量            描述信息            状态");
        for (Brand brand1 : brandList) {
            System.out.print(brand1.getId() + "\t\t\t");
            System.out.print(brand1.getBrandName() + "\t\t\t");
            System.out.print(brand1.getCompanyName() + "\t\t\t");
            System.out.print(brand1.getOrdered() + "\t\t\t");
            System.out.print(brand1.getDescription() + "\t\t\t");
            System.out.println(brand1.getStatus());
        }
        System.out.println("<--输入q退出-->");
        final String s = sc.nextLine();
        if ("q".equals(s)) {
//            返回上级菜单 结束当前方法
            return;
        }

    }

    /**
     * 获取德鲁伊数据库链接并且对sql进行预编译 返回预编译对象
     */
    public static PreparedStatement pst(String sql) {
        PreparedStatement preparedStatement = null;
        //加载配置文件
        try {
            Properties prep = new Properties();
            prep.load(new FileInputStream("src/druid.properties"));
//        获取druid的数据库连接池对象
            DataSource dataSource = DruidDataSourceFactory.createDataSource(prep);
            Connection connection = dataSource.getConnection();
            preparedStatement = connection.prepareStatement(sql);

        } catch (Exception e) {
            e.printStackTrace();
        }
        return preparedStatement;
    }
}

运行截图：