尝试使用资源的JDBC [英] JDBC with try with resources

问题描述

我正在尝试创建一个集中的类来连接并返回SQL查询的ResultSet，这样我就不必总是在每次尝试获取查询时都创建一个新的连接.

I am trying to create a centralized class that connects and returns the ResultSet of a SQL query so that I don't always have to create a new connection every time I am trying to get a query.

我正在使用try-with-resources，但是，每当使用try-with-resources时都会出现编译时错误，我不知道为什么?

I am using the try-with-resources, however, I am getting a compile-time error whenever I use the try-with-resources and I don't know why?

public class JDBC {

    // logger declaration is omitted

    private static final String dbURL = "jdbc:oracle:";
    private static final String userName = "blah";
    private static final String password = "12345";

    public ResultSet retrieveSQLQuery(String sqlQuery) {            
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;

        try (conn = DriverManager.getConnection(dbUrl, user, password);
             statement = conn.createStatement();
             rs = statement.executeQuery(sqlQuery)) {               

        } catch (SQLException e) {
            logger.info(e.getMessage());
        }                    
        return rs;        
    }
}

推荐答案

Java 7

当您使用尝试资源时，指向Closeable资源的变量必须在try-with-resources块中声明.

When you use try-with-resources, variables pointing to Closeable resources must be declared inside try-with-resources block.

此外，返回rs是一个坏主意，方法完成后将关闭它.因此，您可能会在方法之外得到一个SQLException(类似"ResultSet已关闭"之类的东西).您应该在try-with-resources块中解析rs并从您的方法中返回SQL不可知对象:

Moreover, returning rs is a bad idea, it would be closed after method is done. So you might get an SQLException outside your method (something like "ResultSet is closed"). You should parse rs inside try-with-resources block and return SQL agnostic object from your method:

public ResultSet retrieveSQLQuery(String sqlQuery) {            
    try (Connection conn = DriverManager.getConnection(dbUrl, user, password);
         Statement statement = conn.createStatement();
         ResultSet rs = statement.executeQuery(sqlQuery)) {
        MyResult result = ...; // parse rs here
        return myResult;               
    } catch (SQLException e) {
        logger.info(e.getMessage());
        // return something (empty MyResult or null) from here or rethrow the exception
        // I'd recommend to get rid of this catch block and declare the SQLException on method signature
    }                    
}

使用错误的try-with-resources语法会遇到编译时错误，就是这样.

You're getting compile-time error on incorrect try-with-resources syntax, that's it.

更新

Java 9 Java 9为try-with-resources提供了更灵活的语法.您可以在try (...)块之外声明Closeable资源:

Java 9 Java 9 provides more flexible syntax for try-with-resources. You can declare Closeable resource outside the try (...) block:

public ResultSet retrieveSQLQuery(String sqlQuery) {
    Connection conn = DriverManager.getConnection(dbUrl, user, password);
    try (conn; ResultSet rs = conn.createStatement().executeQuery(sqlQuery)) {
        MyResult result = ...; // parse rs here
        return myResult;               
    } catch (SQLException e) {
        // handle error
    }                    
}

这篇关于尝试使用资源的JDBC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！