如何正确关闭数据源连接? [英] How to properly close datasource connection?
问题描述
我有此类，但是我不确定如何正确关闭连接，因为即使我只有3个用户登录但有多个SQL查询，我仍然会遇到此错误.
I have this class but Im not sure on how to properly close connection because Im still having this error even though I only have 3 users logged in but with multiple sql queries.
> com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:
> Data source rejected establishment of connection, message from
> server: "Too many connections"
import java.io.File;
import java.io.IOException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
public class UserDaoImpl implements UserDao
{
DataSource dataSource;
public DataSource getDataSource()
{
return this.dataSource;
}
public void setDataSource(DataSource dataSource)
{
this.dataSource = dataSource;
}
public boolean isValidUser(String username, String password) throws SQLException
{
PreparedStatement pstmt = null;
ResultSet resultSet = null;
boolean rt = false;
try{
PasswordEncryptor pws = new PasswordEncryptor();
String encryptedPass = pws.encrypt(password);
String query = "Select count(1) from userdetails where username = ? and password = ?";
pstmt = dataSource.getConnection().prepareStatement(query);
pstmt.setString(1, username);
pstmt.setString(2, encryptedPass);
resultSet = pstmt.executeQuery();
if (resultSet.next()){
rt = (resultSet.getInt(1) > 0);
}
else{
rt = false;
}
}
catch(Exception e){
e.printStackTrace();
}
finally{
resultSet.close();
pstmt.close();
dataSource.getConnection().close();
}
return rt;
}
}
SpringConfiguration.xml
SpringConfiguration.xml
<bean name="userDao" class="com.spring.acadconnect.services.UserDaoImpl">
<property name="dataSource" ref="dataSource"></property>
</bean>
<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost:3306/acadconnect" />
<property name="username" value="root" />
<property name="password" value="" />
</bean>
推荐答案
请注意，您多次呼叫.getConnection().尽管在这方面文档可能更清晰，但 DataSource.getConnection() 实际上打开一个新的连接(而不是返回一个现有的连接)，因此您需要关闭从该方法返回的每个实例.
Notice that you're calling .getConnection() multiple times. Although the documentation could be clearer on this front DataSource.getConnection() actually opens a new connection (as opposed to returning an existing one) thus you need to close each instance returned from that method.
由于.getConnection()每次被称为此行都会创建一个新实例，这是连接泄漏，因为它没有关闭返回的连接:
As .getConnection() creates a new instance every time it's called this line is a connection leak, since it's not closing the connection being returned:
pstmt = dataSource.getConnection().prepareStatement(query);
此行无用地打开一个新连接，只是立即将其关闭:
And this line wastefully opens a new connection only to immediately close it:
dataSource.getConnection().close();
似乎您正在尝试为每次isValidUser()的调用打开和关闭单独的连接(因为您要在该方法调用结束时关闭连接).即使您修复了上述泄漏，也不是要使用连接的方式.相反，您应该在整个应用程序中共享一个连接(或少数几个连接).因此，当您的程序启动时，您将打开这样的连接，并且一旦整个程序不再需要该连接(通常在终止前不久)，您就将其关闭.
It looks like you're trying to open and close a separate connection for each invocation of isValidUser() (since you're closing the connection at the end of that method call). Even if you fix the leak described above, that's not how connections are intended to be used. Instead you should share one connection (or a small number of them) across your application. So when your program starts up you open such a connection, and once the whole program no longer needs the connection (often shortly before terminating) you close it.
这种行为通常是由依赖注入实现的，您可以在其中构建连接并其他资源，然后将它们传递到需要它们的任何对象中-这使资源管理与使用这些资源的代码分离.举一个简单的例子:
This sort of behavior is commonly implemented by dependency injection, where you construct your connections and other resources and then pass them into whatever objects need them - this decouples resource management from the code that uses those resources. As a simplistic example:
public static void main(String[] args) {
DataSource dataSource = createDataSource();
try (Connection connection = dataSource.getConnection()) {
runProgram(connection);
}
}
/**
* this method doesn't need to worry about closing the Connection,
* it trusts that its caller will be responsible for that.
*/
private static void runProgram(Connection connection) {
// ...
}
根据经验，对象仅应负责关闭其构造的对象，并应避免关闭其传递的对象.在您当前的代码中，UserDaoImpl正在打开连接，因此应负责关闭连接，但我建议改为传入Connection.
As a rule of thumb, objects should only be responsible for closing objects they construct, and should avoid closing objects they are passed. In your current code UserDaoImpl is opening the connection, so it should be responsible for closing it, but I'm suggesting passing in the Connection instead.
这篇关于如何正确关闭数据源连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
