JDBC with Apache Derby 抛出 java.sql.SQLNonTransientConnection

Question

我正在一个桌面应用程序中工作，我需要连接到数据库以检索一些数据并生成报告。出于测试目的，我在 Derby 中创建了一个测试数据库，其中包含我需要的表和一些虚拟数据。

由于它是一个小应用程序，我决定使用普通的JDBC来使其尽可能轻量级。

我正在使用TDD进行开发。我已经测试了从文件加载连接属性的方法以及创建与数据库连接的方法。我在尝试查询数据并填充映射到该数据的对象时遇到了困难。这是我的代码：

public class JdbcManager {
static final String JDBC_DRIVER_PROPERTY = "jdbc.driver";
static final String JDBC_URL_PROPERTY = "jdbc.url";
static final String JDBC_USER_PROPERTY = "jdbc.username";
static final String JDBC_PASSWORD_PROPERTY = "jdbc.password";
static String PROPERTIES_FILE_PATH = "src/main/resources/connection.properties";
String queryCareers = "SELECT career, name FROM db_career”;
private static final Logger LOG = Logger.getLogger(JdbcManager.class.getName());
public List<Career> getCareerList() {
List<Career> result = new ArrayList<>();
Career item;
Connection connection = createConnection(PROPERTIES_FILE_PATH);
try {
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(queryCareers);
while (rs.next()) {
item = new Career(rs.getString("Career"), rs.getString("name"));
result.add(item);
}
} catch (SQLException ex) {
LOG.log(Level.SEVERE, “Error retrieving Career data”, ex);
}
return result;
}
// This method is working correctly according to my tests
Connection createConnection(String configFilePath) {
Properties connectionProperties = loadPropertiesFromFile(configFilePath);
try {
Class driverClass = Class.forName(connectionProperties.getProperty(JDBC_DRIVER_PROPERTY));
LOG.log(Level.INFO, "Driver loaded: {0}", driverClass);
} catch (ClassNotFoundException ex) {
LOG.log(Level.SEVERE, "Driver not found”, ex);
}
try (Connection connection = DriverManager.getConnection(
connectionProperties.getProperty(JDBC_URL_PROPERTY),
connectionProperties.getProperty(JDBC_USER_PROPERTY),
connectionProperties.getProperty(JDBC_PASSWORD_PROPERTY))) {
LOG.log(Level.INFO, “Connection created: {0}", connection);
return connection;
} catch (SQLException ex) {
LOG.log(Level.SEVERE, “Unable to connect to DB”, ex);
return null;
}
}
// This method is working correctly according to my tests
Properties loadPropertiesFromFile(String configFilePath) {
File configFile = new File(configFilePath);
Properties properties = new Properties();
try {
properties.load(new FileInputStream(configFile));
LOG.log(Level.INFO, “Properties loaded: {0}", properties);
} catch (FileNotFoundException ex) {
LOG.log(Level.SEVERE, "File not found", ex);
} catch (IOException ex) {
LOG.log(Level.SEVERE, "Error reading the file", ex);
}
return properties;
}
}

然后我有失败的测试：

@Test
public void testGetCareerList() {
logger.log(Level.INFO, "testGetCareerList():");
List<Career> expectedResult = Arrays.asList(
new Career("LGU", “Some dummy name 1”),
new Career("TSGU", "Some dummy name 2”));
// Change the location of the properties file to the testing one
GestorJdbc.PROPERTIES_FILE_PATH = "src/test/resources/connectionTest.properties";
List<Career> actualResult = jdbcManager.getCareerList();
assertEquals(expectedResult, actualResult);
}

最后是我执行测试时的堆栈跟踪：

-------------------------------------------------------
T E S T S
-------------------------------------------------------
Running ar.edu.unt.jdbc.JdbcManagerTest
jun 06, 2017 7:57:24 PM ar.edu.unt.jdbc.JdbcManagerTest testGetCareerList
INFO: testGetCareerList():
jun 06, 2017 7:57:24 PM ar.edu.unt.jdbc.JdbcManager loadPropertiesFromFile
INFO: Properties loaded: {jdbc.url=jdbc:derby://localhost:1527/siu_test, jdbc.username=dba, jdbc.driver=org.apache.derby.jdbc.ClientDriver, jdbc.password=dba}
jun 06, 2017 7:57:24 PM ar.edu.unt.jdbc.JdbcManager createConnection
INFO: Driver loaded: class org.apache.derby.jdbc.ClientDriver
jun 06, 2017 7:57:26 PM ar.edu.unt.jdbc.JdbcManager createConnection
INFO: Connection created: org.apache.derby.client.net.NetConnection40@dc24521
jun 06, 2017 7:57:26 PM ar.edu.unt.jdbc.JdbcManager getCareerList
SEVERE: Error retrieving Career data
java.sql.SQLNonTransientConnectionException: No current connection.
at org.apache.derby.client.am.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.client.am.SqlException.getSQLException(Unknown Source)
at org.apache.derby.client.am.Connection.createStatement(Unknown Source)
at ar.edu.unt.jdbc.JdbcManager.getCareerList(JdbcManager.java:49)
at ar.edu.unt.jdbc.JdbcManagerTest.testGetCareerList(JdbcManagerTest.java:71)
...

我正在使用 Netbeans，在那里我有一个正在运行的 Derby DB，我可以连接到其中(正如我所说，连接工作正常)。正如您在堆栈跟踪中看到的那样，异常被抛出createStatement()但我找不到原因。

我一直在博客和帖子中潜水，但没有任何效果。在其他论坛上有一篇文章，用户通过调用Thread.isInterrupted()解决了他的问题，因为createStatement()是同步的，但对我不起作用，因为我在整个应用程序中使用单个线程(事实上，它一定是一个旧帖子，因为该方法似乎在 Java 8 中不可用， 只是一种类似的方法，称为Thread.interrupted()或类似的东西)。

如果您需要有关我的应用程序的更多信息，请在您的评论中告诉我。任何帮助和/或指导不胜感激。

Answer 1

您的方法createConnection返回一个关闭的连接。您正在使用 try-with-resources 语句来创建连接。这意味着此连接将在该块退出时自动关闭，无论它如何退出(通过例外，返回或只是块结束)。

当您随后尝试创建语句时，驱动程序会报告您没有连接(尽管错误消息本身可能更清晰一些)。

简而言之，您应该将createConnection更改为：

Connection createConnection(String configFilePath) {
Properties connectionProperties = loadPropertiesFromFile(configFilePath);
try {
Class driverClass = Class.forName(connectionProperties.getProperty(JDBC_DRIVER_PROPERTY));
LOG.log(Level.INFO, "Driver loaded: {0}", driverClass);
} catch (ClassNotFoundException ex) {
LOG.log(Level.SEVERE, "Driver not found”, ex);
}
try {
Connection connection = DriverManager.getConnection(
connectionProperties.getProperty(JDBC_URL_PROPERTY),
connectionProperties.getProperty(JDBC_USER_PROPERTY),
connectionProperties.getProperty(JDBC_PASSWORD_PROPERTY))
LOG.log(Level.INFO, “Connection created: {0}", connection);
return connection;
} catch (SQLException ex) {
LOG.log(Level.SEVERE, “Unable to connect to DB”, ex);
return null;
}
}

顺便说一句：返回null例外实际上是在其他地方要求NullPointerException。考虑仅抛出SQLException或创建自己的异常。

另一方面，getCareerList您没有使用资源尝试，而您应该这样做，因为一旦您解决了createConnection的问题，您将因未正确关闭资源而泄漏资源getCareerList.将其更改为：

try (Connection connection = createConnection(PROPERTIES_FILE_PATH);
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery(queryCareers)) {
//... rest 
} catch (SQLException e) {
//... rest
}

这样，结果集、语句和连接将始终正确关闭。