【JDBC】JDBCUtils工具类开发

1.JDBCUtils工具类开发

我们来写一个Jdbc工具类 - jdbcUtils，这个工具类可以用来获取Connection对象以及关闭数据库连接

上代码：

/**
 * JdbcUtils开发
 */
public class JdbcUtils {
    private static String user;
    private static String password;
    private static String url;
    private static String driver;

    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);
        }
    }

    /**
     * 连接数据库
     *
     * @return 一个数据库的连接
     */
    public static Connection getConnection() {
        try {
            Class.forName(driver);
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 关闭数据库连接
     * @param resultSet 结果集
     * @param statement 用于执行sql语句的对象
     * @param connection 数据库连接
     * 需要关闭资源就传入对象，否则传入null即可
     */
    public static void close(ResultSet resultSet, Statement statement, Connection connection) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }

            if (statement != null) {
                statement.close();
            }

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

---

2.使用JdbcUtils工具类测试DML语句

上代码：

/**
 * 使用JdbcUtils工具类
 */
public class UseJdbcUtils {
    /**
     * 测试更新语句
     */
    @Test
    public void testDML() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "update user set school = ? where id = ?";
        try {
            connection = JdbcUtils.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, "大和大学");
            preparedStatement.setInt(2, 4);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 关闭连接
            JdbcUtils.close(null, preparedStatement, connection);
        }
    }
}

---

3.使用JdbcUtils工具类测试查询语句

上代码：

/**
 * 测试查询语句
 */
@Test
public void testSelect() {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;
    String sql = "select * from user";
    try {
        connection = JdbcUtils.getConnection();
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String username = resultSet.getString("username");
            String password = resultSet.getString("password");
            String school = resultSet.getString("school");
            System.out.println(id + username + password + school);
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    } finally {
        // 关闭连接
        JdbcUtils.close(resultSet, preparedStatement, connection);
    }
}