How do I get JDBC driver property information?

By Wayan in JDBC 0 Comment

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

Maven Central

How do I check if the OUT parameter value is null?

By Wayan in JDBC 0 Comment

This example show you how to use the CallableStatement.wasNull() method call to see if the last OUT parameter has a value of SQL NULL.

package org.kodejava.jdbc;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.DriverManager;
import java.sql.Types;
import java.sql.SQLException;

public class WasNullExample {
    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)) {

            // Prepares a call to the sored procedure
            String query = "call Get_Product_By_Price2(?, ?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setBigDecimal(1, new BigDecimal("50"));

            // Registers the OUT parameter
            cb.registerOutParameter(2, Types.VARCHAR);

            // Executes the query
            cb.executeQuery();

            // Gets the OUT parameter value
            cb.getString(2);

            // Checks if the last OUT parameter has the value of SQL NULL.
            // This method should be called only after calling a getter
            // method; otherwise, there is no value to use in determining
            // whether it is null or not.
            if (cb.wasNull()) {
                System.out.println("Product has an SQL NULL value");
            } else {
                System.out.println("Product: " + cb.getString(2));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the stored procedure script we called in the example above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS Get_Product_By_Price2;;
CREATE PROCEDURE Get_Product_By_Price2(
    IN product_price DECIMAL(10, 2),
    OUT product_name VARCHAR(100))
BEGIN
    SELECT name
    INTO product_name
    FROM product
    WHERE price = product_price;
END;;
DELIMITER ;

Maven Dependencies

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

Maven Central

How do I call a stored procedure that return a result set?

By Wayan in JDBC 0 Comment

This example show you how to call a stored procedure that return a result set of the query execution result.

package org.kodejava.jdbc;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;

public class CallableStatementExample {
    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)) {

            // Prepares a call to the stored procedure. This stored procedure takes
            // one IN parameter
            String query = "call Get_Product_By_Price(?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setBigDecimal(1, new BigDecimal("50.39"));

            // Execute the query
            ResultSet rs = cb.executeQuery();

            while (rs.next()) {
                System.out.println("Product: " + rs.getString(1));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the stored procedure script we use in the example above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS Get_Product_By_Price;;
CREATE PROCEDURE Get_Product_By_Price(IN product_price DECIMAL(10, 2))
BEGIN
    SELECT name FROM product WHERE price = product_price;
END;;
DELIMITER ;

Maven dependencies

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

Maven Central

How do I register out parameter in CallableStatement?

By Wayan in JDBC 0 Comment

This example show you how to register out parameter for executing a stored procedure using the CallableStatement.registerOutParameter() method call. We must register the out parameters before the query execution. The registerOutParameter() method takes two parameters, the index of the parameter and the sql data type of the out parameter.

package org.kodejava.jdbc;

import java.sql.*;

public class RegisterOutParameter {
    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)) {

            // Creates a CallableStatement for executing the stored
            // procedure
            String query = "call Get_Product_Detail_By_Name(?, ?, ?)";
            CallableStatement cb = connection.prepareCall(query);

            // Sets the input parameter
            cb.setString(1, "Data Structures, Algorithms");

            // Registers the out parameters
            cb.registerOutParameter(2, Types.VARCHAR);
            cb.registerOutParameter(3, Types.DECIMAL);

            // Executes the query
            cb.executeQuery();

            // Gets the query result output
            System.out.println("Name  : " + cb.getString(1));
            System.out.println("Code  : " + cb.getString(2));
            System.out.println("Price : " + cb.getBigDecimal(3));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here is the MySQL stored procedure that we call in the code above.

DELIMITER ;;
DROP PROCEDURE IF EXISTS `Get_Product_Detail_By_Name`;;
CREATE PROCEDURE `Get_Product_Detail_By_Name`(
    INOUT product_name VARCHAR(100),
    OUT product_code VARCHAR(20),
    OUT product_price DECIMAL(10, 2))
BEGIN
    SELECT code INTO product_code FROM product WHERE name = product_name;

    SELECT price INTO product_price FROM product WHERE name = product_name;
END;;
DELIMITER ;

Maven Dependencies

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

Maven Central

How do I set the query timeout limit?

By Wayan in JDBC 1 Comment

The Statement.setQueryTimeout() method set the limit in seconds for query execution time. The execution has no timeout limit when the value is set to zero.

package org.kodejava.jdbc;

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

public class QueryTimeout {
    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)) {
            Statement stmt = connection.createStatement();

            // Sets the number of seconds the driver will wait for
            // a statement object to execute to the given number of
            // seconds. If the limit is exceeded, an SQLException
            // is thrown.
            stmt.setQueryTimeout(60);

            // Execute sql query
            ResultSet rs = stmt.executeQuery("select * from product");

            while (rs.next()) {
                System.out.println("code: " + rs.getString("code") + 
                                   ", product: " + rs.getString("name") + 
                                   ", price: " + rs.getDouble("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Maven Dependencies

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

Maven Central

How do I limit MySQL query result?

By Wayan in JDBC 0 Comment 
package org.kodejava.jdbc;

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

public class SqlLimitExample {
    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)) {

            // Create PreparedStatement to get all data from a database.
            String query = "select count(*) from product";
            PreparedStatement ps = connection.prepareStatement(query);
            ResultSet result = ps.executeQuery();

            int total = 0;
            while (result.next()) {
                total = result.getInt(1);
            }

            System.out.println("Total number of data in database: " +
                               total + "\n");

            // Create PreparedStatement to the first 5 records only.
            query = "select * from product limit 5";
            ps = connection.prepareStatement(query);
            result = ps.executeQuery();

            System.out.println("Result fetched with specified limit 5");
            System.out.println("====================================");
            while (result.next()) {
                System.out.println("id:" + result.getInt("id") +
                                   ", code:" + result.getString("code") +
                                   ", name:" + result.getString("name") +
                                   ", price:" + result.getString("price"));
            }

            // Create PreparedStatement to get data from the 4th
            // record (remember the first record is 0) and limited
            // to 3 records only.
            query = "select * from product limit 3, 3";
            ps = connection.prepareStatement(query);
            result = ps.executeQuery();

            System.out.println("\nResult fetched with specified limit 3, 3");
            System.out.println("====================================");
            while (result.next()) {
                System.out.println("id:" + result.getInt("id") +
                                   ", code:" + result.getString("code") +
                                   ", name:" + result.getString("name") +
                                   ", price:" + result.getString("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

An example result of our program is:

Total number of data in database: 9

Result fetched with specified limit 5
====================================
id:1, code:P0000001, name:UML Distilled 3rd Edition, price:25.00
id:3, code:P0000003, name:PHP Programming, price:20.00
id:4, code:P0000004, name:Longman Active Study Dictionary, price:40.00
id:5, code:P0000005, name:Ruby on Rails, price:24.00
id:6, code:P0000006, name:Championship Manager, price:0.00

Result fetched with specified limit 3, 3
====================================
id:5, code:P0000005, name:Ruby on Rails, price:24.00
id:6, code:P0000006, name:Championship Manager, price:0.00
id:7, code:P0000007, name:Transport Tycoon Deluxe, price:0.00

Maven Dependencies

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

Maven Central

How do I set the fetch size of a statement?

By Wayan in JDBC 0 Comment

Fetch size is the number of rows that should be fetched from the database on a single database network trip. When more rows are needed, another request is sent by the application to the database server.

Setting the correct fetch size will help our program to perform better by reducing the network communication generated between the program and the database server.

package org.kodejava.jdbc;

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

public class SetFetchSizeExample {
    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)) {
            Statement statement = connection.createStatement();

            // Set the fetch size to 100.
            statement.setFetchSize(100);

            // Execute the given sql query
            String q = "select id, code, name, price from product";
            ResultSet rs = statement.executeQuery(q);

            while (rs.next()) {
                System.out.println("id:" + rs.getLong("id") +
                                   ", code:" + rs.getString("code") +
                                   ", name:" + rs.getString("name") +
                                   ", price:" + rs.getString("price"));
            }

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

Example result of the code snippet above is:

id:1, code:P0000001, name:UML Distilled 3rd Edition, price:25.00
id:3, code:P0000003, name:PHP Programming, price:20.00
id:4, code:P0000004, name:Longman Active Study Dictionary, price:40.00
id:5, code:P0000005, name:Ruby on Rails, price:24.00
id:6, code:P0000006, name:Championship Manager, price:0.00
id:7, code:P0000007, name:Transport Tycoon Deluxe, price:0.00
id:8, code:P0000008, name:Roller Coaster Tycoon 3, price:0.00
id:9, code:P0000009, name:Pro Evolution Soccer, price:0.00
id:10, code:P0000010, name:Data Structures, Algorithms, price:50.99

Maven Dependencies

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

Maven Central

How do I set the maximum rows to read in a query?

By Wayan in JDBC 0 Comment

If you want to limit the result of your query, you can call the Statement.setMaxRows(int max) method. This call will allow the ResultSet object contains a maximum number of records specified in the parameter of the setMaxRows method.

Another way to limit the number of data returned in a query is to use the database-specific command such as the MySQL limit command.

package org.kodejava.jdbc;

import java.sql.*;

public class SetMaxRowExample {
    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)) {
            Statement statement = connection.createStatement();

            // Executes an SQL query to get the total number of data
            // in product table.
            String query = "select count(*) from product";
            ResultSet rs = statement.executeQuery(query);

            while (rs.next()) {
                System.out.println("Total Products: " + rs.getInt(1));
            }

            // Set the maximum row of data that can be stored in the
            // ResultSet.
            statement.setMaxRows(5);

            // Executes an SQL query to retrieve data from product
            // table.
            query = "select id, code, name, price from product";
            rs = statement.executeQuery(query);

            System.out.println("Data read after the MaxRows is set.");
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id")
                                   + ", CODE: " + rs.getString("code")
                                   + ", NAME: " + rs.getString("name")
                                   + ", PRICE: " + rs.getBigDecimal("price"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

When running the code, we’ll see that only 5 records were read from the product table instead of 10 records. This is the result of setting the maximum rows in the Statement object.

Below is the output of our code.

Total Products: 9
Data read after the MaxRows is set.
ID: 1, CODE: P0000001, NAME: UML Distilled 3rd Edition, PRICE: 25.00
ID: 3, CODE: P0000003, NAME: PHP Programming, PRICE: 20.00
ID: 4, CODE: P0000004, NAME: Longman Active Study Dictionary, PRICE: 40.00
ID: 5, CODE: P0000005, NAME: Ruby on Rails, PRICE: 24.00
ID: 6, CODE: P0000006, NAME: Championship Manager, PRICE: 0.00

Maven Dependencies

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

Maven Central

How do I convert java.util.Date to java.sql.Date?

By Wayan in JDBC 0 Comment 
package org.kodejava.jdbc;

import java.util.Date;

public class UtilDateToSqlDate {
    public static void main(String[] args) {
        // Create a new instance of java.util.Date
        Date date = new Date();

        // To covert java.util.Date to java.sql.Date we need to
        // create an instance of java.sql.Date and pass the long
        // value of java.util.Date as the parameter.
        java.sql.Date sqlDate = new java.sql.Date(date.getTime());

        System.out.println("Date    = " + date);
        System.out.println("SqlDate = " + sqlDate);
    }
}

The result of the code snippet above:

Date    = Sat Oct 09 20:23:27 CST 2021
SqlDate = 2021-10-09

How do I get system functions supported by database?

By Wayan in JDBC 0 Comment 
package org.kodejava.jdbc;

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

public class SystemFunction {
    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)) {
            DatabaseMetaData meta = connection.getMetaData();

            // Get system functions supported by database
            String[] functions = meta.getSystemFunctions().split(",\\s*");

            for (String function : functions) {
                System.out.println("Function = " + function);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Here are MySQL database supported system functions.

Function = DATABASE
Function = USER
Function = SYSTEM_USER
Function = SESSION_USER
Function = PASSWORD
Function = ENCRYPT
Function = LAST_INSERT_ID
Function = VERSION

Maven Dependencies

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

Maven Central