How do I check whether a driver is JDBC compliant?

package org.kodejava.example.sql;

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

public class JdbcCompliant {
    public static void main(String[] args) {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            String url = "jdbc:mysql://localhost/kodejavadb";
            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 (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

How do I get JDBC driver property information?

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

package org.kodejava.example.sql;

import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.DriverPropertyInfo;

public class DriverPropertyInfoDemo {

    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost/kodejavadb";

        try {
            //
            // Gets information about the possible properties for this
            // driver.
            //
            Driver driver = getDriver(url);
            DriverPropertyInfo[] properties = driver.getPropertyInfo(url, null);

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

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

                    //
                    // Deletes the last ","
                    //
                    sb = sb.deleteCharAt(sb.length() - 1);
                    System.out.println(sb.toString());
                }
            }

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

    // 
    // Get the Driver used to connect to database
    //
    private static Driver getDriver(String url) throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        return DriverManager.getDriver(url);
    }
}

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

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.example.sql;

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

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

        try {
            connection = getConnection();

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

            //
            // Sets the input parameter
            //
            cb.setDouble(1, 150000d);

            //
            // 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 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 (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("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * 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 is the stored procedure script we called in the example above.

CREATE PROCEDURE GET_PRODUCT_BY_PRICE2 (IN vprice decimal, OUT vproduct varchar(30))
BEGIN
  SELECT name INTO vproduct
  FROM products
  WHERE price = vprice;
END;

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

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

package org.kodejava.example.sql;

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

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

        try {
            connection = getConnection();
            //
            // Prepares a call to the sored procedure. This SP takes
            // one IN parameter
            //
            String query = "call GET_PRODUCT_BY_PRICE(?)";
            CallableStatement cb = connection.prepareCall(query);

            //
            // Sets the input parameter
            //
            cb.setDouble(1, 50d);

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

            while (rs.next()) {
                System.out.println("Product: " + rs.getString(1));
            }
        } 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("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * 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 is the stored procedure script we use in the example above.

CREATE PROCEDURE GET_PRODUCT_BY_PRICE (IN vprice decimal)
BEGIN
 SELECT name
 FROM product
 WHERE price = vprice;
END;

How do I register out parameter in CallableStatement?

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.example.sql;

import java.sql.*;

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

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

            // 
            // Sets the input parameter
            //
            cb.setString(1, "bag");

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

            //
            // Executes the query
            //
            cb.executeQuery();

            //
            // Gets the query result output
            //
            System.out.println("Code    : " + cb.getString(2));
            System.out.println("Price   : " + cb.getBigDecimal(3));
            System.out.println("Quantity: " + cb.getInt(4));

        } 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("com.mysql.jdbc.Driver");
        String url = "jdbc:mysql://localhost/kodejavadb";
        return DriverManager.getConnection(url, "root", "");
    }

    /**
     * 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 is the MySQL stored procedure that we call in the code above.

DROP PROCEDURE IF EXISTS kodejavadb.GET_DETAIL_BY_PRODUCT;
CREATE PROCEDURE kodejavadb.`GET_DETAIL_BY_PRODUCT`(IN vproduct varchar(30),
OUT vcode varchar(5),
OUT vprice decimal,
OUT vqty int)
BEGIN
  SELECT
  code INTO vcode
  FROM kodejavadb.products
  WHERE name = vproduct;

  SELECT
  price INTO vprice
  FROM kodejavadb.products
  WHERE name = vproduct;

  SELECT
  qty INTO vqty
  FROM kodejavadb.products
  WHERE name = vproduct;
END;