JDBCUtils工具库

mysql.properties配置文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/db02?rewriteBatchedStatements=true
user=root_db02
password=root_db02

JDBCUtils类

package com.utils;

import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

/**
 * Created by Lenovo on 2022-05-10-下午 7:51.
 *
 * @author 小象
 * @version 1.0
 */
public class JDBCUtils {

    /*
    在jdbc操作中，获取连接和释放资源是经常使用到，可以将其封装JDBC连接的工具类JDBCUtils
     */

    // 使用 static 修饰的属性和代码块，原因：只需加载一次，无需多次加载
    private static final String driver;
    private static final String url;
    private static final String user;
    private static final String password;

    static {
        try {
            // 获取配置文件信息
            Properties properties = new Properties();
            properties.load(new FileReader("src/com/connect_database/mysql.properties"));
            // 读取配置文件
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            user = properties.getProperty("user");
            password = properties.getProperty("password");
            // 加载驱动
            Class.forName(driver);
        } catch (ClassNotFoundException | IOException e) {
            // IOException 编译类型必须处理，但是建议将其转换为运行类型，可以选择抛出(默认)/处理(捕获)
            throw new RuntimeException(e);
        }
    }

    /**
     * <h3>获取连接 返回connection</h3>
     */
    public static Connection getConnection() {
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * <h3>关闭资源</h3>
     * 1. 如果需要关闭资源，就传入对象，否则传入 null <br>
     * 2. 使用Statement 接口类型，保证Statement 和 PreparedStatement对象都可以传入
     */
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (statement != null) {
                connection.close();
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

JDBCUtilsUse类

package com.utils;

import org.junit.jupiter.api.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by Lenovo on 2022-05-10-下午 8:21.
 *
 * @author 小象
 * @version 1.0
 */
@SuppressWarnings("NewClassNamingConvention")
public class JDBCUtilsUse {

    /**
     * <h2>封装JDBCUtils</h2>
     *
     * @param args 形参数组
     */
    public static void main(String[] args) {

    }

    /**
     * <h3>使用JDBCUtils工具类完成对db02数据库下admin表的 增/删/改</h3>
     */
    @Test
    @SuppressWarnings({"SqlNoDataSourceInspection", "SqlResolve"})
    public void dml() {
        // 得到连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        // 执行sql
        String deleteSql = "delete from admin where name = ?";

        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(deleteSql);
            preparedStatement.setString(1, "jack");
            int rows = preparedStatement.executeUpdate();
            System.out.println(rows > 0 ? "操作成功" : "操作失败");
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            JDBCUtils.close(null, preparedStatement, connection);
        }
    }

    /**
     * <h3>使用JDBCUtils工具类完成对db02数据库下admin表的 查询</h3>
     */
    @Test
    @SuppressWarnings({"SqlNoDataSourceInspection", "SqlResolve"})
    public void select() {
        // 得到连接
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        // 执行sql
        String selectSql = "select * from admin where name = ?";

        try {
            connection = JDBCUtils.getConnection();
            preparedStatement = connection.prepareStatement(selectSql);
            preparedStatement.setString(1, "mack");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String name = resultSet.getString("name");
                String pwd = resultSet.getString("pwd");
                System.out.println(name + "\t" + pwd);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 关闭资源
            JDBCUtils.close(resultSet, preparedStatement, connection);
        }
    }

}