JDBC - Learn Java by Examples

How to establish connection to a database using Properties object?

By I Wayan Saryada in JDBC Last modified: May 31, 2024 0 Comment

In the following code snippet, you will see how to pass some connection arguments when connecting to a database. To do this, we can use the java.util.Properties class. We can put some key value pairs as a connection arguments to the Properties object
before we pass this information into the DriverManager class.

Let’s see the example below:

package org.kodejava.jdbc;

import java.sql.*;
import java.util.Properties;

public class GetConnectionWithProperties {
    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) {
        GetConnectionWithProperties demo = new GetConnectionWithProperties();
        try (Connection connection = demo.getConnection()) {
            // do something with the connection.
            Statement stmt = connection.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM product");
            while (rs.next()) {
                System.out.println("Code = " + rs.getString("code"));
                System.out.println("Name = " + rs.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private Connection getConnection() throws SQLException {
        Properties connectionProps = new Properties();
        connectionProps.put("user", USERNAME);
        connectionProps.put("password", PASSWORD);

        Connection connection = DriverManager.getConnection(URL, connectionProps);
        System.out.println("Connected to database.");
        return connection;
    }
}

Maven Dependencies

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

How to automatically close resources in JDBC?

By I Wayan Saryada in JDBC Last modified: May 31, 2024 0 Comment

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.4.0</version>
</dependency>

How do I retrieve available catalogs in database?

By I Wayan Saryada in JDBC Last modified: August 3, 2023 0 Comment

The examples below show you how to get the available catalog names in MySQL database. The available catalog names can be obtained from the DatabaseMetaData object by calling the getCatalogs() method. This method returns a ResultSet object. We iterate it and get the schema name by calling the getString() method and pass TABLE_CAT as the column name.

package org.kodejava.jdbc;

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

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

    public static void main(String[] args) {
        try (Connection connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

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

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

            while (rs.next()) {
                String catalog = rs.getString("TABLE_CAT");
                System.out.println("Catalog: " + catalog);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the list of catalog names available in the MySQL database:

Catalog: kodejava
Catalog: musicdb
Catalog: mysql
Catalog: performance_schema
Catalog: sys

Maven Dependencies

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

How do I get the maximum number of concurrent connections?

By I Wayan Saryada in JDBC Last modified: May 31, 2024 0 Comment

This example shows you how to get the maximum number of concurrent connections to a database that are possible.
To get this information, we use the DatabaseMetaData.getMaxConnections() method call. If the return value is zero, it means that there is no limit or the limit is unknown.

package org.kodejava.jdbc;

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

public class MaxConnections {
    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 connection =
                     DriverManager.getConnection(URL, USERNAME, PASSWORD)) {

            // Get database meta data.
            DatabaseMetaData metaData = connection.getMetaData();

            // Retrieves the maximum number of concurrent
            // connections to this database that are possible.
            // A result of zero means that there is no limit or
            // the limit is not known.
            int max = metaData.getMaxConnections();
            System.out.println("Max concurrent connections: " + max);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

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

How do I check whether a driver is JDBC compliant?

By I Wayan Saryada in JDBC Last modified: May 31, 2024 0 Comment

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.4.0</version>
</dependency>