将连接和关闭资源封装在JDBCUtils类中:
package com.hspedu.jdbc.utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* 这是一个工具类,完成mysql的连接和关闭资源
*/
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) {
//在实际开发中,可以这样处理
//1.将编译异常转成运行异常
//2.这时调用者可以选择捕获该异常,也可以选择默认处理该异常,比较方便。
throw new RuntimeException(e);
}
}
//连接数据库,返回Connection
public static Connection getConnection() {
try {
return DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
//1.将编译异常转成运行异常
//2.这时调用者可以选择捕获该异常,也可以选择默认处理该异常,比较方便。
throw new RuntimeException(e);
}
}
//关闭相关资源
/**
* 1.ResultSet 结果集
* 2.Statement 或者 PreparedStatement
* 3.Connection
* 4.如果需要关闭资源,就传入对象,否则传入null
*/
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) {
//1.将编译异常转成运行异常
//2.这时调用者可以选择捕获该异常,也可以选择默认处理该异常,比较方便。
throw new RuntimeException(e);
}
}
}
使用开发的JUtils工具类:
package com.hspedu.jdbc.utils;
/**
* 该类演示如何使用JDBCUtils工具类,完成dml操作和select操作
*/
import org.junit.Test;
import java.sql.*;
public class JDBCUtils_Use {
@Test
public void testSelect() {
//1.得到连接
Connection connection = null;
//2.组织一个sql
String sql = "select name, sex, borndate, phone from actor where id = ?";
//3.创建PreparedStatement对象
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
//给占位符赋值
preparedStatement.setInt(1, 1);
//执行
resultSet = preparedStatement.executeQuery();
//遍历该结果集
while (resultSet.next()) {
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
Date borndate = resultSet.getDate("borndate");
String phone = resultSet.getString("phone");
System.out.println(name + "\t" + sex + "\t" + borndate + "\t" + phone);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.close(resultSet, preparedStatement, connection);
}
}
@Test
public void testDML() { //DML:insert, update, delete
//1.得到连接
Connection connection = null;
//2.组织一个sql
String sql = "update actor set name = ? where id = ?";
//3.创建PreparedStatement对象
PreparedStatement preparedStatement = null;
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
//给占位符赋值
preparedStatement.setString(1, "周星驰");
preparedStatement.setInt(2, 1);
//执行
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.close(null, preparedStatement, connection);
}
}
}