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 will usually produce a lot of boilerplate 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 shows us how to use the try-with-resources statement.

package org.kodejava.jdbc;

import java.sql.*;

public class TryWithResourceJdbc {
    private static final String URL = "jdbc:mysql://localhost/kodejava";
    private static final String USERNAME = "kodejava";
    private static final String PASSWORD = "s3cr*t";

    public static void main(String[] args) {
        try (Connection conn =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT * FROM product")) {

            while (rs.next()) {
                String code = rs.getString("code");
                String name = rs.getString("name");

                System.out.println("Code: " + code + "; Name: " + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

How do I check whether a driver is JDBC compliant?

package org.kodejava.jdbc;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.SQLException;

public class JDBCCompliant {
    private static final String URL = "jdbc:mysql://localhost/kodejava";

    public static void main(String[] args) {
        try {
            Driver driver = DriverManager.getDriver(URL);

            // Check if the driver is a genuine JDBC compliant driver.
            if (driver.jdbcCompliant()) {
                System.out.println("A genuine JDBC compliant driver");
            } else {
                System.out.println("Not a genuine JDBC compliant driver");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central

How do I get JDBC driver property information?

The example below shows you how to get the information about the possible properties for the corresponding JDBC driver.

package org.kodejava.jdbc;

import java.sql.*;

public class DriverPropertyInfoDemo {
    private static final String URL = "jdbc:mysql://localhost/kodejava";

    public static void main(String[] args) {
        try {
            // Gets information about the possible properties for this
            // driver.
            Driver driver = DriverManager.getDriver(URL);
            DriverPropertyInfo[] props = driver.getPropertyInfo(URL, null);

            for (DriverPropertyInfo info : props) {
                System.out.println("Name       : " + info.name);
                System.out.println("Description: " + info.description);
                System.out.println("Value      : " + info.value);
                System.out.println("-----------------------------------");

                String[] choices = info.choices;
                if (choices != null) {
                    StringBuilder sb = new StringBuilder("Choices    : ");
                    for (String choice : choices) {
                        sb.append(choice).append(",");
                    }

                    System.out.println(sb);
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

The example result of the code snippet above:

Name       : host
Description: Hostname of MySQL Server
Value      : localhost
-----------------------------------
Name       : port
Description: Port number of MySQL Server
Value      : 3306
-----------------------------------
Name       : dbname
Description: Database name;
Value      : kodejava
-----------------------------------
Name       : user
Description: Username to authenticate as
Value      : null
-----------------------------------
Name       : password
Description: Password to use for authentication
Value      : null
-----------------------------------
Name       : clientCertificateKeyStorePassword
Description: Password for the client certificates key store.
Value      : null
-----------------------------------
Name       : serverRSAPublicKeyFile
Description: File path to the server RSA public key file for sha256_password authentication. If not specified, the public key will be retrieved from the server.
Value      : null
-----------------------------------
Name       : cacheDefaultTimeZone
Description: Caches clients default time zone. This results in better performance when dealing with time zone conversions in Date and Time data types, however it wont be aware of time zone changes if they happen at runtime.
Value      : true
-----------------------------------
Choices    : TRUE,FALSE,YES,NO,
...
...
...

Maven Dependencies

<dependency>
    <groupId>com.mysql</groupId>
    <artifactId>mysql-connector-j</artifactId>
    <version>8.1.0</version>
</dependency>

Maven Central