JDBC——Mysql增删改查连接操作优化版

步骤一：导入jar包

 MySQL的Jar包https://download.csdn.net/download/m0_62642719/85360784步骤二：编写propreties配置文件

具体内容:

url=jdbc:mysql://localhost:3306/db_wwq
username=root
password=root
driverClassName=com.mysql.cj.jdbc.Driver
initialSize=10
maxActive=20
maxWait=1000

步骤三：编写JdbcUtils工具类

public class JdbcUtils {
    private static DataSource dataSource;
    private static ThreadLocal<Connection> threadLocal;

    static {

        try {
            //加载连接池配置文件，创建DataSource
            Properties properties = new Properties();
            properties.load(JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
            //初始化ThreadLocal对象
            threadLocal = new ThreadLocal<>();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取数据库连接池
    public static Connection getConnection(){
        //从threadlocal获取连接
        Connection connection = threadLocal.get();
        if (connection==null){
            try {
                connection = dataSource.getConnection();
                threadLocal.set(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return connection;
    }

    //释放资源
    public static void closeResource(){
        Connection connection = threadLocal.get();
        if (connection != null){
            try {
                connection.close();
                threadLocal.remove();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

步骤四：编写要查询的数据库表进行封装如stu信息表：

public class Stu {
    private String sid;
    private String sname;
    private int age;
    private String gender;

    
    public String getSid() {
        return sid;
    }

    public void setSid(String sid) {
        this.sid = sid;
    }

    public String getSname() {
        return sname;
    }

    public void setSname(String sname) {
        this.sname = sname;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }
    @Override
        public String toString() {
            return "Stu{" +
                    "sid='" + sid + '\'' +
                    ", sname='" + sname + '\'' +
                    ", age=" + age +
                    ", gender='" + gender + '\'' +
                    '}';
    }

}

步骤五：编写增删改查代码：

public class DbUtils {
    //添加操作
    @Test
    public void test01() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "INSERT INTO stu VALUES(?,?,?,?)";
        queryRunner.update(JdbcUtils.getConnection(),sql,"S_1012","baba",18,"male");
        JdbcUtils.closeResource();
    }
    //修改操作
    @Test
    public void test02() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "UPDATE stu SET sname=? WHERE sname=?";
        queryRunner.update(JdbcUtils.getConnection(),sql,"diedie","baba");
        JdbcUtils.closeResource();
    }

    //删除操作
    @Test
    public void test03() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "DELETE FROM stu WHERE sid=?";
        queryRunner.update(JdbcUtils.getConnection(), sql, "S_1012");
        JdbcUtils.closeResource();
    }


    //查询操作-查询一条记录
    @Test
    public void test04() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT * FROM stu WHERE sid=?";
        Stu s_1012 = queryRunner.query(JdbcUtils.getConnection(), sql, new BeanHandler<Stu>(Stu.class), "S_1012");
        System.out.println("s_1012 = " + s_1012);
        JdbcUtils.closeResource();
    }

    //查询操作-查询多条记录
    @Test
    public void test05() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        String sql = "SELECT * FROM stu";
        List<Stu> list = queryRunner.query(JdbcUtils.getConnection(), sql, new BeanListHandler<Stu>(Stu.class));
        System.out.println("list = " + list);
        JdbcUtils.closeResource();
    }

}

步骤六：运行Test，测试结果

五月 13, 2022 7:48:10 下午 com.alibaba.druid.pool.DruidDataSource info
信息: {dataSource-1} inited
list = [
Stu{sid='S_1001', sname='liuYi', age=35, gender='male'},
Stu{sid='S_1002', sname='chenEr', age=15, gender='female'}, 
Stu{sid='S_1003', sname='zhangSan', age=95, gender='male'}, 
Stu{sid='S_1004', sname='liSi', age=65, gender='female'}, 
Stu{sid='S_1005', sname='wangWu', age=55, gender='male'}, 
Stu{sid='S_1006', sname='zhaoLiu', age=75, gender='female'}, 
Stu{sid='S_1007', sname='sunQi', age=25, gender='male'}, 
Stu{sid='S_1008', sname='zhouBa', age=45, gender='female'}, 
Stu{sid='S_1009', sname='wuJiu', age=85, gender='male'}, 
Stu{sid='S_1010', sname='zhengShi', age=5, gender='female'}, 
Stu{sid='S_1011', sname='xxx', age=0, gender='null'}, 
Stu{sid='S_1012', sname='baba', age=18, gender='male'}
]



Process finished with exit code 0