java - JDBC 与 Apache Derby 抛出 java.sql.SQLNonTransientConnectionException : No current connection

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 语句来创建连接。这意味着此连接将在该 block 的导出处自动关闭，无论它是如何退出的(通过异常、通过返回或只是 block 结束)。

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

简而言之，您应该将 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 中你没有使用 try-with-resource，而你应该使用，因为一旦你用 createConnection 解决了问题，你将因未在 getCareerList 中正确关闭资源而泄漏资源。将其更改为:

try (Connection connection = createConnection(PROPERTIES_FILE_PATH);
     Statement stmt = connection.createStatement();
     ResultSet rs = stmt.executeQuery(queryCareers)) {

    //... rest 
} catch (SQLException e) {
    //... rest
}

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