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;