韩顺平Java | C25 JDBC和连接池（中）

本文链接：https://blog.csdn.net/weixin_45428118/article/details/137746650

JDBCUtils工具类

JDBC操作中获取连接和释放资源操作可以封装到JDBCUtils工具类中。

工具类代码

完成mysql的连接和关闭资源

package com.hspedu.jdbc.utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JDBCUtils {
    //定义相关属性(4个)，因为只需要一份，因此做成静态的static
    private static String user; //用户名
    private static String password; //密码
    private static String url; //url
    private static String driver; //驱动

    //在static代码块初始化
    static {
        try {
            Properties properties = new Properties();
            properties.load(new FileInputStream("src\\mysql.properties"));
            //读取相关属性值
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            url = properties.getProperty("url");
            driver = properties.getProperty("driver");
        } catch (IOException e) {
            //将编译异常转成运行异常抛出，调用者可以选择捕获或默认处理该异常
            throw new RuntimeException(e);
        }
    }

    //连接数据库，返回Connection
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //关闭相关资源：ResultSet、Statement或PreparedStatement、Connection
    public static void close(ResultSet set, Statement statement, Connection connection) {
        //如果传入对象则关闭，否则传null
        try {
            if(set != null){
                set.close();
            }
            if(statement != null){
                statement.close();
            }
            if(connection != null){
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

使用JDBCUtils工具类完成DML和select

package com.hspedu.jdbc.utils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JDBCUtils_Use {
	@Test
    public void testDML(){
        //1 得到连接
        Connection connection = JDBCUtils.getConnection();
        //2 组织sql
        String sql = "update actor set name = ? where id = ?";
        PreparedStatement preparedStatement = null;
        //3 创建PreparedStatement对象
        try {
            preparedStatement = connection.prepareStatement(sql);
            //给占位符赋值
            preparedStatement.setString(1, "周星驰");
            preparedStatement.setInt(2, 1);
            //执行
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            //关闭资源
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
    @Test
    public void testSelect(){
        Connection connection = JDBCUtils.getConnection();
        String sql = "select * from actor where id = ?";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, 1);
            resultSet = preparedStatement.executeQuery();
            //遍历该结果集
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String sex = resultSet.getString("sex");
                Date borndate = resultSet.getDate("borndate");
                String phone = resultSet.getString("phone");
                System.out.println(id + "\t" + name + "\t" + sex + "\t" + borndate + "\t" + phone);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }

    }
}

※事务

JDBC程序中创建一个Connection对象时，默认自动提交事务：每当成功执行一个SQL语句时，就会像数据库自动提交，而不能回滚。
JDBC程序中为让多个SQL语句整体执行，需要使用事务。connection.setAutoCommit(false)取消自动提交事务，执行完成后connection.commit()提交事务，通过connection.rollback()回滚。

public class Transaction_ {
    @Test
    public void useTransaction() {
        //1 得到连接
        Connection connection = JDBCUtils.getConnection();
        //2 组织sql
        String sql = "update accout set balance = balance - 100 where id = 1";
        String sql2 = "update accout set balance = balance + 100 where id = 2";
        PreparedStatement preparedStatement = null;
        //3 创建PreparedStatement对象
        try {
            //将connection设置为不自动提交
            connection.setAutoCommit(false);//开启事务
            preparedStatement = connection.prepareStatement(sql);
            //执行
            preparedStatement.executeUpdate(); //执行第一条sql
            // int i = 1 / 0; //执行中断
            preparedStatement.executeUpdate(sql2);
            //执行完成，提交事务
            connection.commit();
        } catch (SQLException e) {
            // 这里可以进行回滚，即撤销执行的sql
            System.out.println("执行发生了异常，撤销执行的sql");
            try {
                connection.rollback(); //默认回滚到事务开始的状态
            } catch (SQLException ex) {
                throw new RuntimeException(ex);
            }
            throw new RuntimeException(e);
        } finally {
            //关闭资源
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }
}

批处理

批处理机制：需成批插入或更新数据，允许多条语句一次性提交数据库批量处理。
jdbc连接mysql时，若需要使用批处理，须在url中添加参数?rewriteBatchedStatements=true
批处理一般和PrepareStatement一起使用。

addBatch() //添加需要批处理的sql语句或参数
excuteBatch() //执行批量处理语句
clearBatch() // 清空批处理包的语句

public class Batch_ {
    @Test
    public void noBatch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values(null, ?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("开始执行");
        long start = System.currentTimeMillis();

        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");
            preparedStatement.executeUpdate();
        }
        long end = System.currentTimeMillis();
        System.out.println("传统方式耗时" + (end - start));

        JDBCUtils.close(null, preparedStatement, connection);
    }

    @Test
    public void useBatch() throws SQLException {
        Connection connection = JDBCUtils.getConnection();
        String sql = "insert into admin2 values (null, ?, ?)";//values后须有空格 否则批处理失效
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        System.out.println("开始执行");
        long start = System.currentTimeMillis();

        for (int i = 0; i < 5000; i++) {
            preparedStatement.setString(1, "jack" + i);
            preparedStatement.setString(2, "666");
            //将sql语句加入到批处理包
            preparedStatement.addBatch();
            //每当有1000条记录时再批量执行
            if((i + 1) % 1000 == 0) {
                preparedStatement.executeBatch();
                preparedStatement.clearBatch();//清空
            }
        }
        long end = System.currentTimeMillis();
        System.out.println("批处理方式耗时" + (end - start));

        JDBCUtils.close(null, preparedStatement, connection);
    }
}