How to automatically close resources in JDBC?

One thing that we need to do manually when programming using JDBC is to make sure to close all the resources that we use. All resources including the ResultSet, Statement and Connection must be closed. This usually will produce a lot of boiler plate code in our program.

Starting from JDBC 4.1 which is a part of Java 7 we can use the try-with-resources statement to automatically manage the resources that we use. This try statement closes the resources used when the block finishes its execution either normally or abruptly.

Here is an example that show us how to use the try-with-resources statement.

package org.kodejava.example.sql;

import java.sql.*;

public class TryWithResourceJdbc {
    public static final String URL = "jdbc:mysql://localhost/sampledb";

    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");

            TryWithResourceJdbc demo = new TryWithResourceJdbc();
            demo.selectData();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * Select data from m_users table.
     * @throws SQLException when an exception happens.
     */
    private void selectData() throws SQLException {
        try (Connection conn = DriverManager.getConnection(URL, "root", "");
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM m_users")) {

            while (rs.next()) {
                String username = rs.getString("username");
                String password = rs.getString("password");

                System.out.println("username: " + username + "; password: " + password);
            }
        }
    }
}

How do I retrieve values from ResultSet?

Here is another example on how to read data from a ResultSet returned by executing an SQL query in database.

package org.kodejava.example.sql;

import java.sql.*;

public class ResultSetExample {
    public static void main(String[] args) throws Exception {
        Connection connection = getConnection();
        try {
            String query = "SELECT id, title, publisher, year, price " +
                    "FROM books";
            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                // Read values using column name
                String id = rs.getString("id");
                String title = rs.getString("title");
                String publisher = rs.getString("publisher");

                // Read values using column index
                int year = rs.getInt(4);
                float price = rs.getFloat(5);

                System.out.printf("%s. %s, %s, %d, %f\n", id, title,
                        publisher, year, price);
            }
        } finally {
            closeConnection(connection);
        }
    }

    private static Connection getConnection() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getConnection("jdbc:mysql://localhost/kodejava",
                "kodejava", "kodejava123");
    }

    private static void closeConnection(Connection connection) {
        try {
            if (connection != null && !connection.isClosed()) {
                connection.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

We start by creating a connection to the database, see the getConnection() method. Creates a PreparedStatement to execute a query to get some data from the books table.

After executing the PreparedStatement we will have a ResultSet object. To iterate all the data in the ResultSet we call the next() method in a while-loop. When no more record to read the method return false. The ResultSet object also provides some methods to read value of the fields, the name of the method is corresponded to the type of data stored on each field of the table.

To read data using the ResultSet‘s methods (eg. getString(), getInt(), getFloat(), etc) we can either use the column name or the column index of the field read in the SQL statement.