一、封装JdbcUtils工具类

1. 配置数据库连接信息jdbc.properties

driver=com.mysql.cj.jdbc.Driver # mysql8版本
url=jdbc:mysql://localhost:3306/your_db_name
username=root
password=your_PWD

2. 工具类代码

public class JdbcUtils {

    private static final String URL;
    private static final String USERNAME;
    private static final String PASSWORD;

    static {
        InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
        Properties properties = new Properties();
        try {
            properties.load(is);
            String driver = properties.getProperty("driver");
            URL = properties.getProperty("url");
            USERNAME = properties.getProperty("username");
            PASSWORD = properties.getProperty("password");
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USERNAME, PASSWORD);
    }

    public static void close(Statement statement, Connection connection) {
        if (statement != null) {
            try {
                if (statement.isClosed()) {
                    return;
                }
                statement.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

        if (connection != null) {
            try {
                if (connection.isClosed()) {
                    return;
                }
                connection.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
    }

    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        if (resultSet != null) {
            try {
                if (resultSet.isClosed()) {
                    return;
                }
                resultSet.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }
        close(statement, connection);
    }

}

二、使用工具类操作数据库

1. 增删改

public static void main(String[] args) throws SQLException {
    Connection connection = JdbcUtils.getConnection();
    String sql = "delete from book where id = ?";
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setInt(1, 100);
    int i = statement.executeUpdate();
    if (i > 0){
        System.out.println("操作成功!");
    } else {
        System.out.println("操作失败!");
    }
}

2. 查询

public static void main(String[] args) throws SQLException {
    Connection connection = JdbcUtils.getConnection();
    String sql = "select * from book where author like ?";
    PreparedStatement statement = connection.prepareStatement(sql);
    statement.setString(1, "%刘%");
    ResultSet resultSet = statement.executeQuery();
    while (resultSet.next()) {
        System.out.print(resultSet.getInt("id"));
        System.out.print(resultSet.getString("title"));
        System.out.print(resultSet.getString("author"));
        System.out.println();
    }
    JdbcUtils.close(resultSet, statement, connection);
}