java - 这是数据库连接和自动关闭资源的正确实现吗?
问题描述
我有一个程序从 MySQL 数据库表中读取,遍历结果集并返回列表中特定 userId 的行数据。在所有可用的互联网资源的帮助下,我设法创建了一些可行的东西,但我不确定我的实现是否正确。下面是我的 DbConnection 和 DaoImpl 类。
数据库连接.java
/**
* This class is responsible for creating a new database connection using the declared URL, USER and PASSWORD.
*/
package dbconnection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DbConnection implements AutoCloseable {
public static final String DB_URL = "jdbc:mysql://localhost:3306/test?autoReconnect=true&useSSL=false";
public static final String USER = "root";
public static final String PWD = "root";
private Connection conn;
/**
* An empty constructor that takes no arguments and creates a new Connection using DriverManager.getConnection.
* The connection is initiated inside a try-with resources with SQLException catch.
*/
public DbConnection() {
try {
conn = DriverManager.getConnection(DB_URL, USER, PWD);
System.out.println("Connection Established.");
} catch (SQLException e) {
System.out.println("Couldn't Connect!! " + e.getMessage());
}
}
//Return the current connection
public Connection getConn() {
return conn;
}
//close
public void close() {
try {
if (conn != null) {
conn.close();
System.out.println("Connection Closed");
}
} catch (SQLException e) {
System.out.println("Couldn't close Connection " + e.getMessage());
}
}
}
TiedostoDaoImpl.java
import dbconnection.DbConnection;
import pojos.Tiedosto;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class TiedostoDaoImpl implements TiedostoDAO {
final String TABLE_TIEDOSTO = "tiedosto";
final String COLUMN_TIEDOSTO_NAME = "nimi";
final String COLUMN_TIEDOSTO_SIZE = "koko_tavua";
final String COLUMN_TIEDOSTO_CONTENT = "sisalto";
final String COLUMN_TIEDOSTO_CUSTOMER_ID = "hlo_id";
final String COLUMN_TIEDOSTO_MINIOUPLOAD = "minioupload";
final String QUERY_TIEDOSTO = "SELECT " + COLUMN_TIEDOSTO_NAME + ", " + COLUMN_TIEDOSTO_CUSTOMER_ID + ", " + COLUMN_TIEDOSTO_SIZE + ", " + COLUMN_TIEDOSTO_CONTENT +
" FROM " + TABLE_TIEDOSTO + " WHERE " + COLUMN_TIEDOSTO_CUSTOMER_ID + " = ?";
final String UPDATE_TIEDOSTO = "UPDATE " + TABLE_TIEDOSTO + " SET " + COLUMN_TIEDOSTO_MINIOUPLOAD + " = 1 WHERE " + COLUMN_TIEDOSTO_CUSTOMER_ID + " = ?";
private DbConnection dbConnection = new DbConnection();
private Connection databaseConnection;
public TiedostoDaoImpl() {
try {
databaseConnection = dbConnection.getConn();
} catch (Exception e) {
System.out.println("Problem Connecting to Database! " + e.getMessage());
}
}
//This method queries tiedosto table and returns the data as a List.
@Override
public List<Tiedosto> getDetails(int userId) throws SQLException {
List<Tiedosto> files;
try (PreparedStatement selectFromTiedosto = databaseConnection.prepareStatement(QUERY_TIEDOSTO)) {
selectFromTiedosto.setInt(1, userId);
try (ResultSet results = selectFromTiedosto.executeQuery()) {
files = new ArrayList<>();
while (results.next()) {
Tiedosto myFile = new Tiedosto();
myFile.setCustomerId(results.getInt(COLUMN_TIEDOSTO_CUSTOMER_ID));
myFile.setFileName(results.getString(COLUMN_TIEDOSTO_NAME));
myFile.setFileSize(results.getInt(COLUMN_TIEDOSTO_SIZE));
myFile.setContent(results.getBlob(COLUMN_TIEDOSTO_CONTENT));
files.add(myFile);
}
}
}
return files;
}
当我运行程序时,它显示“连接已建立”并返回数据,但它没有说明关闭连接的任何内容。我需要知道我是否正确地实施了这一切。有人可以帮忙吗?
解决方案
你需要DbConnection
在try-with-resource
声明中包含,也许像......
try (DbConnection db = new DbConnection(); PreparedStatement selectFromTiedosto = db.getConn().prepareStatement(QUERY_TIEDOSTO)) {
// ...
} catch (SQLException ex) {
e.printStackTrace();
}
这将自动关闭 theDbConnection
和 the PreparedStatement
(尽管关闭连接会自动关闭PreparedStatement
)。
缺点,每次你以这种方式使用它时,你都在Connection
为数据库创建一个新的,这可能需要一些时间。使用关闭挂钩或连接池可能更可取,具体取决于您实际在做什么
推荐阅读
- json - 为组合字段值添加最小长度条件
- json - org.json.JSONObject 将数字变为指数形式
- oracle - Oracle 12.2 二进制 XML 和 SAX 解析器
- ios - 来自调试器的消息:由于信号 9 而终止
- reactjs - 循环遍历 React.js 中的状态对象
- c - 如何降低两个 for 循环的大 O 复杂度
- c++ - 在tensorflow中,是否有任何用于调整GPU内存大小的c ++函数或方法?
- javascript - vue.js 中需要“new ...”的库 - eslint 吹嘘拼写
- json - Api 控制器:Json 将属性名称取消大写
- jquery - 如何在 jquery 中使用 @url 将 ViewData 值传递给部分视图