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 available schemas in database?

The examples below show you how to get the available schema names in Oracle database. The available schema names can be obtained from the DatabaseMetaData object by calling the getSchemas() method.

This method returns a ResultSet object. After obtaining the ResultSet object we need to iterate it and get the schema name by calling the result set getString() method and pass TABLE_SCHEM as the column name.

package org.kodejava.example.sql;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class MetadataGetSchema {

    public static void main(String[] args) {
        Connection connection = null;
        try {
            connection = getConnection();

            //
            // Gets DatabaseMetaData
            //
            DatabaseMetaData metadata = connection.getMetaData();

            //
            // Retrieves the schema names available in this database
            //
            ResultSet rs = metadata.getSchemas();

            while (rs.next()) {
                String schema = rs.getString("TABLE_SCHEM");
                System.out.println("Schema: " + schema);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * Get a connection to database.
     * @return a connection to database.
     * @throws Exception when an exception occurs.
     */
    private static Connection getConnection() throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        String url = "jdbc:oracle:thin:@localhost:1521:xe";        
        return DriverManager.getConnection(url, "kodejava", "welcome");
    }

    /**
     * Close a connection to database.
     * @param connection a connection to be closed.
     * @throws SQLException when an exception occurs.
     */
    private static void closeConnection(Connection connection)
            throws SQLException {
        if (connection != null && !connection.isClosed()) {
            connection.close();
        }
    }
}

Here an a list of schema names available in the Oracle XE database:

Schema: ANONYMOUS
Schema: CTXSYS
Schema: DBSNMP
Schema: DIP
Schema: FLOWS_020100
Schema: FLOWS_FILES
Schema: HR
Schema: KODEJAVA
Schema: MDSYS
Schema: OUTLN
Schema: SYS
Schema: SYSTEM
Schema: TSMSYS
Schema: XDB