Retrieving all values from a column

Question

How can I efficiently get all the values from a column via java through a SQL query without having to connect to the database with each subsequent call?

What I have now:

private List<String> retrieveSQLQuery(String sqlQuery) throws SQLException {
    JDBC jdbc = new JDBC();
    ResultSet rs = jdbc.getResultsSet(sqlQuery);
    List<String> values = new ArrayList<>();

    while (rs.next()) {
        values.add(rs.getString("model"));
    }
    return values;        
}

However, for each subsequent rs.next(), it connects to the database, creates a statement and then retrieves the value.

It looks like this:

Connecting to database... Creating statement... Statement Created. Connecting to database... Creating statement... Statement Created. value1 Connecting to database... Creating statement... Statement Created. Connecting to database... Creating statement... Statement Created. value2 Connecting to database... Creating statement... Statement Created. Connecting to database... Creating statement... Statement Created. value3

Is there a more efficient way to do this?

Edit:

Here is my resultset method:

public ResultSet getResultsSet(String QueryString) {
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        CachedRowSetImpl crs = null;
        try {
            // STEP 1: Register JDBC driver
            Class.forName(jdbcDriver);

            // STEP 2: Open a connection
            logger.info("Connecting to database...");
            conn = DriverManager.getConnection(dbUrl, user, password);

            // STEP 3: Execute a query.
            logger.info("Creating statement...");
            stmt = conn.createStatement();
            String sql;
            sql = QueryString;
            rs = stmt.executeQuery(sql);
            crs = new CachedRowSetImpl();
            crs.populate(rs);

            logger.info("Statement Created.");
            // STEP 5: Clean-up environment
        } catch (ClassNotFoundException e) {
            logger.error(e.getMessage());
        } catch (SQLException e) {
            logger.error(e.getMessage());
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
            try {
                if (stmt != null) {
                    stmt.close();
                }
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                logger.error(e.getMessage());
            }
        } // end try

        return crs;
    }

You should create a Simple, Self-Contained, Compilable Example http://sscce.org/. We don't know the types `JDBC` and its method `getResultsSet()`, for example, so it's hard to know what is wrong. The usual way to get a specific column from a table is for the query to select that column. — Lew Bloch, Jul 14 '16 at 22:19
@LewBloch Please let me know if you need anymore information — Robben, Jul 14 '16 at 22:25
You're closing the `ResultSet` before using it. Also the statement. Also the connection. It's a miracle you get any results back at all. You also don't need to load the driver every time you call the method. `Class>.forName()` loads then initializes the driver class. Once that's done, it's loaded and initialized; you can't do it twice so the call is wasted code. — Lew Bloch, Jul 14 '16 at 22:32
What does `CachedRowSetImpl` accomplish? Can you post that too? — sstan, Jul 15 '16 at 00:17

score 3 · Accepted Answer · edited May 23 '17 at 12:08

The idiomatic way to retrieve data from the DB over JDBC is to

Get the Connection
Create a Statement on the Connection
Execute the Statement
Iterate through the returned Resultset

This means that for each query, you should only retrieve the Connection once and create a single Statement. Note that you shouldn't be connecting to the database each time you retrieve a Connection; you should be retrieving it from one of the many connection pools available for JDBC. Also note that using Class.forName(...) for initializing the driver is redundant for post-JDBC 4.0 drivers.

In short, it's better to operate on the JDBC API classes directly and not to roll your own wrappers/caching for these classes. Additionally, Statement and ResultSet objects should have a very limited scope (meaning that they shouldn't preferrably be passed between methods), in order to prevent resource leakage. JDBC resource management is also best and safest handled by using the try-with-resources statement instead of the legacy try-catch-finally structure.

Keeping the above in mind, a rewrite of retrieveSQLQuery() (using only JDBC API classes, instead of your custom JDBC class) could look like the following:

 public List<String> retrieveSQLQuery(String sqlQuery) throws SQLException {

    List<String> values = new ArrayList<>();

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

        while (rs.next()) {
            // this assumes that the passed-in query string has 
            // a "model" column in the SELECT clause
            values.add(rs.getString("model"));
        }
    }

    return values;        
}

Hey Mich, sorry to bother you again with this question but I am a bit confused. Is it not okay for me to create a custom JDBC class that can be used by other classes that needs to connect to the database to retrieve a query? Or should I just reuse the existing JDBC Api for every class that needs to connect to the database? — Robben, Jul 25 '16 at 00:33
It is absolutely okay to create custom classes for data access -- however, this code should follow best practices (minimize scope of variables, ensure closing of resources etc). — Mick Mnemonic, Jul 25 '16 at 00:56
Usually you shouldn't do that (and when using try-with-resources you can't, because the result set is locally scoped and autocloseable). Of course, it always depends.. You could also post a question on codereview.stackexchange.com if you want some more comments on custom code. — Mick Mnemonic, Jul 25 '16 at 01:07

Retrieving all values from a column

1 Answers1