说明:在JDBC操作中,获取连接和释放资源是经常使用到的,可以将其封装JDBC连接工具类JDBCUtils
package godairo.jdbc.utils;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;
/**
* @author 吴志超
* @since JavaWeb 4.0.0
* Creation Time: 2022/7/15 10:01.
* 这是一个工具类,完成mysql的连接和关闭资源。
*/
public class JDBCUtils {
//定义相关属性(4个),因为只需要一份,因此我们做成static
private static String user;
private static String password;
private static String url;
private static String driver;
//在static代码块中初始化
static{
try {
Properties properties = new Properties();
properties.load(new FileInputStream("F:\\JavaWeb-Study\\JavaWeb\\JdbcStudy\\src\\main\\resources\\jdbc.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) {
//将编译异常转成一个 运行异常
throw new RuntimeException(e);
}
}
/**
* 关闭相关资源
* 1.ResultSet 结果集
* 2.Statement 或者 PreparedStatement
* 3.Connection
* 4.如果需要关闭资源,就传入对象,否则传入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);
}
}
}
JDBCUtilsDML
package godairo.jdbc.utils;
import org.junit.jupiter.api.Test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
/**
* @author 吴志超
* Creation Time: 2022/7/15 10:18.
* 该类演示如何使用JDBCUtils工具类,完成DML和select语句
*/
public class JDBCUtils_Use {
@Test
public void testDML(){
//1.得到连接
// Connection connection = JDBCUtils.getConnection();
Connection connection=null;
//2.组织一个sql
String sql = "update actor set name=? where id=?";
PreparedStatement preparedStatement = null ;
//3.创建一个PreparedStatement对象
try {
connection=JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
//给占位符赋值
preparedStatement.setString(1,"周星驰");
preparedStatement.setInt(2,2);
//执行
preparedStatement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.close(null,preparedStatement,connection);
}
}
}
这里的Connection和PreparedStatement直接在外面声明,扩大作用域,不然在finally里面用不了
JDBCUtils查询
@Test
public void testSelect() {
Connection connection = null;
PreparedStatement preparedStatement = null;
ResultSet resultSet=null;
String sql = "select id,name,sex,borndate,phone from actor";
try {
connection = JDBCUtils.getConnection();
preparedStatement = connection.prepareStatement(sql);
resultSet = preparedStatement.executeQuery();
while (resultSet.next()) {
int id = resultSet.getInt("id");
String name = resultSet.getString("name");
String sex = resultSet.getString("sex");
Date borndate = resultSet.getDate("borndate");
String phone = resultSet.getString("phone");
System.out.println(id+"\t"+name+"\t"+sex+"\t"+borndate+"\t"+phone);
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JDBCUtils.close(resultSet,preparedStatement,connection);
}
}