JAVA-MANI.BLOGSPOT.COM

Stored procedure are user-generated functions or procedures that, once created in the database, can be called by the client applications, such as Java application. In this example we'll demonstrate how to use the JDBC java.sql.CallableStatement to call a stored procedure.

The store procedure in this example is just for inserting a record into table. Just like the PreparedStatement interface, in the CallableStatement we can pass the parameter to the procedure by calling the appropriate setXXX(index, value) method.


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

public class CallableStatementDemo {
private static String url = "jdbc:oracle:thin:@localhost:1521:xe";
private static String username = "kodejava";
private static String password = "welcome";

public static void main(String[] args) throws Exception {
Connection conn = null;
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
conn = DriverManager.getConnection(url, username, password);

//
// Create a CallableStatement to execute the CREATE_USERS procedure
//
CallableStatement stmt = conn.prepareCall("{call CREATE_USERS (?, ?, ?, ?, ?, ?)}");

//
// Defines all the required parameter values.
//
stmt.setString(1, "kodejava");
stmt.setString(2, "welcome");
stmt.setString(3, "Kode");
stmt.setString(4, "Java");
stmt.setString(5, "Denpasar - Bali");
stmt.setString(6, "webmaster[at]kodejava[.]org");
stmt.execute();
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (conn != null && !conn.isClosed()) {
conn.close();
}
}
}
}



Below is the stored procedure that was executed in the code above.

CREATE OR REPLACE PROCEDURE CREATE_USERS (username IN VARCHAR2, password IN VARCHAR2, firstName IN VARCHAR2, lastName IN VARCHAR2, address IN VARCHAR2, email IN VARCHAR2) AS
BEGIN
INSERT INTO users (username, password, first_name, last_name, address, email) VALUES (username, password, firstName, lastName, address, email);
END CREATE_USERS;

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

public class DriverInfo {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

DatabaseMetaData metadata = connection.getMetaData();
String driverName = metadata.getDriverName();
String driverVersion = metadata.getDriverVersion();
int majorVersion = metadata.getDriverMajorVersion();
int minorVersion = metadata.getDriverMinorVersion();

System.out.println("driverName = " + driverName);
System.out.println("driverVersion = " + driverVersion);
System.out.println("majorVersion = " + majorVersion);
System.out.println("minorVersion = " + minorVersion);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}



The result of our program:

driverName = MySQL-AB JDBC Driver
driverVersion = mysql-connector-java-5.0.3 ( $Date: 2006-07-26 17:26:47 +0200 (Wed, 26 Jul 2006) $, $Revision: 5553 $ )
majorVersion = 5
minorVersion = 0

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

public class DriverInfo {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

DatabaseMetaData metadata = connection.getMetaData();

int majorVersion = metadata.getDatabaseMajorVersion();
System.out.println("majorVersion = " + majorVersion);

int minorVersion = metadata.getDatabaseMinorVersion();
System.out.println("minorVersion = " + minorVersion);

String productName = metadata.getDatabaseProductName();
System.out.println("productName = " + productName);

String productVersion = metadata.getDatabaseProductVersion();
System.out.println("productVersion = " + productVersion);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}



And here are the result:

majorVersion = 5
minorVersion = 0
productName = MySQL
productVersion = 5.0.51a

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

public class SupportTransaction {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

boolean isSupportTransaction = metadata.supportsTransactions();
System.out.println("Support Transaction = " + isSupportTransaction);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

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


public class DatabaseTypeInfo {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
ResultSet resultSet = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);

DatabaseMetaData metadata = connection.getMetaData();
resultSet = metadata.getTypeInfo();
while (resultSet.next()) {
String typeName = resultSet.getString("TYPE_NAME");
System.out.println("Type Name = " + typeName);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (resultSet != null) {
resultSet.close();
}
if (connection != null) {
connection.close();
}
}
}
}

Here are the data type support MySQL database:

Type Name = BIT
Type Name = BOOL
Type Name = TINYINT
Type Name = BIGINT
Type Name = LONG VARBINARY
Type Name = MEDIUMBLOB
Type Name = LONGBLOB
Type Name = BLOB
Type Name = TINYBLOB
Type Name = VARBINARY
Type Name = BINARY
Type Name = LONG VARCHAR
Type Name = MEDIUMTEXT
Type Name = LONGTEXT
Type Name = TEXT
Type Name = TINYTEXT
Type Name = CHAR
Type Name = NUMERIC
Type Name = DECIMAL
Type Name = INTEGER
Type Name = INT
Type Name = MEDIUMINT
Type Name = SMALLINT
Type Name = FLOAT
Type Name = DOUBLE
Type Name = DOUBLE PRECISION
Type Name = REAL
Type Name = VARCHAR
Type Name = ENUM
Type Name = SET
Type Name = DATE
Type Name = TIME
Type Name = DATETIME
Type Name = TIMESTAMP

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

public class SupportBatch {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Check to see if the database support batch updates
//
boolean isBatchingSupported = metadata.supportsBatchUpdates();
System.out.println("Batching Supported = " + isBatchingSupported);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

import java.sql.*;

public class SupportScrollableResultSet {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

boolean supportForwardOnly =
metadata.supportsResultSetType(ResultSet.TYPE_FORWARD_ONLY);
System.out.println("supportForwardOnly = " + supportForwardOnly);

boolean supportScrollInsensitive =
metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_INSENSITIVE);
System.out.println("supportScrollInsensitive = " + supportScrollInsensitive);

boolean supportScrollSensitive =
metadata.supportsResultSetType(ResultSet.TYPE_SCROLL_SENSITIVE);
System.out.println("supportScrollSensitive = " + supportScrollSensitive);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

import java.sql.*;

public class SupportUpdatetableResultSet {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

boolean updatable = metadata.supportsResultSetConcurrency(
ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

System.out.println("Updatable ResultSet supported = " + updatable);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

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

public class MaximumTableNameLength {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get the information of maximum length of database table name
//
int maxLength = metadata.getMaxTableNameLength();
System.out.println("Max Table Name Length = " + maxLength);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

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

public class MaxConcurrentConnection {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get the information of maximum concurrent connection to the
// database. The maximum number of active connections possible at
// one time; a result of zero means that there is no limit or the
// limit is not known
//
int maxConnection = metadata.getMaxConnections();
System.out.println("Maximum Connection = " + maxConnection);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

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

public class StringFunction {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get string functions supported by database
//
String[] functions = metadata.getStringFunctions().split(",\\s*");

for (int i = 0; i < functions.length; i++) {
String function = functions[i];
System.out.println("Function = " + function);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}

Here is the result of string functions supported by MySQL database:

Function = ASCII
Function = BIN
Function = BIT_LENGTH
Function = CHAR
Function = CHARACTER_LENGTH
Function = CHAR_LENGTH
Function = CONCAT
Function = CONCAT_WS
Function = CONV
Function = ELT
Function = EXPORT_SET
Function = FIELD
Function = FIND_IN_SET
Function = HEX
Function = INSERT
Function = INSTR
Function = LCASE
Function = LEFT
Function = LENGTH
Function = LOAD_FILE
Function = LOCATE
Function = LOCATE
Function = LOWER
Function = LPAD
Function = LTRIM
Function = MAKE_SET
Function = MATCH
Function = MID
Function = OCT
Function = OCTET_LENGTH
Function = ORD
Function = POSITION
Function = QUOTE
Function = REPEAT
Function = REPLACE
Function = REVERSE
Function = RIGHT
Function = RPAD
Function = RTRIM
Function = SOUNDEX
Function = SPACE
Function = STRCMP
Function = SUBSTRING
Function = SUBSTRING
Function = SUBSTRING
Function = SUBSTRING
Function = SUBSTRING_INDEX
Function = TRIM
Function = UCASE
Function = UPPER

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

public class NumericFunction {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get numeric functions supported by database
//
String[] functions = metadata.getNumericFunctions().split(",\\s*");

for (int i = 0; i < functions.length; i++) {
String function = functions[i];
System.out.println("Function = " + function);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}


Here is the numeric functions supported by MySQL database.

Function = ABS
Function = ACOS
Function = ASIN
Function = ATAN
Function = ATAN2
Function = BIT_COUNT
Function = CEILING
Function = COS
Function = COT
Function = DEGREES
Function = EXP
Function = FLOOR
Function = LOG
Function = LOG10
Function = MAX
Function = MIN
Function = MOD
Function = PI
Function = POW
Function = POWER
Function = RADIANS
Function = RAND
Function = ROUND
Function = SIN
Function = SQRT
Function = TAN
Function = TRUNCATE

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

public class DateTimeFunction {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

//
// Get date and time functions supported by database
//
String[] functions = metadata.getTimeDateFunctions().split(",\\s*");

for (int i = 0; i < functions.length; i++) {
String function = functions[i];
System.out.println("Function = " + function);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}



Date and time functions supported by MySQL database.

Function = DAYOFWEEK
Function = WEEKDAY
Function = DAYOFMONTH
Function = DAYOFYEAR
Function = MONTH
Function = DAYNAME
Function = MONTHNAME
Function = QUARTER
Function = WEEK
Function = YEAR
Function = HOUR
Function = MINUTE
Function = SECOND
Function = PERIOD_ADD
Function = PERIOD_DIFF
Function = TO_DAYS
Function = FROM_DAYS
Function = DATE_FORMAT
Function = TIME_FORMAT
Function = CURDATE
Function = CURRENT_DATE
Function = CURTIME
Function = CURRENT_TIME
Function = NOW
Function = SYSDATE
Function = CURRENT_TIMESTAMP
Function = UNIX_TIMESTAMP
Function = FROM_UNIXTIME
Function = SEC_TO_TIME
Function = TIME_TO_SEC

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

public class SystemFunction {
private static final String DRIVER = "com.mysql.jdbc.Driver";
private static final String URL = "jdbc:mysql://localhost/kodejava";
private static final String USERNAME = "root";
private static final String PASSWORD = "";

public static void main(String[] args) throws Exception {
Connection connection = null;
try {
Class.forName(DRIVER);
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
DatabaseMetaData metadata = connection.getMetaData();

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

for (int i = 0; i < functions.length; i++) {
String function = functions[i];
System.out.println("Function = " + function);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (connection != null) {
connection.close();
}
}
}
}


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

import java.math.BigInteger;

public class BigIntegerOperation {
public static void main(String[] args) {
BigInteger numberA = new BigInteger("98765432123456789");
BigInteger numberB = BigInteger.TEN;

numberA = numberA.add(numberB);
System.out.println("numberA = " + numberA);

numberA = numberA.multiply(numberB);
System.out.println("numberA = " + numberA);

numberA = numberA.subtract(numberB);
System.out.println("numberA = " + numberA);

numberA = numberA.divide(numberB);
System.out.println("numberA = " + numberA);

numberA = numberA.mod(numberB);
System.out.println("numberA = " + numberA);

numberA = numberA.pow(2);
System.out.println("numberA = " + numberA);

numberA = numberA.negate();
System.out.println("numberA = " + numberA);
}
}


Our class result are:

numberA = 98765432123456799
numberA = 987654321234567990
numberA = 987654321234567980
numberA = 98765432123456798
numberA = 8
numberA = 64
numberA = -64