我们来写一个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);
}
}
}
上代码:
/**
* 使用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);
}
}
}
上代码:
/**
* 测试查询语句
*/
@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);
}
}