How to Call a Database Routing Using Java

How to create and call a database routine (stored procedure/function) using a jdbc driver in java

Stored procedure/function is a routine that is defined and executed in the database itself. Minimizing the time of execution, since the code is already residing/compiled in the database. Java is an OOP language that is widely used today.

This article will try to discuss how to call a stored procedure in mysql using java.

Requirements:
1.) mysql connector j: http://dev.mysql.com/downloads/connector/j/5.1.html
2.) eclipse ide (classic will do): http://eclipse.org

There are several things that need to be setup before you can call a database routine, we will enumerate each:

1.) Create a new java project in eclipse
2.) Extract the mysql connector and link the jar file to the project's build path
3.) You need to tell java about the new driver: Class.forName("com.mysql.jdbc.Driver");
4.) Get the connection object: conn = DriverManager.getConnection("jdbc:"+database+"://"+host+":"+port+"/"+db, uname, pass);

So in summary we will define a function that will return a new java Connection object:

private Connection getConnection() {
  if(conn == null) {
   init();
   try {
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection(
    "jdbc:"+database+"://"+host+":"+port+"/"+db, uname, pass);
   } catch(ClassNotFoundException ce) {
    ce.printStackTrace();
   } catch(SQLException se) {
    se.printStackTrace();
   }
  }
  return conn;
 }

To call a database routine we have to create a CallableStatement object from the open Connection, while doing this we also need to define our routine statement:

//we will call our function addx, with 3 parameters; 1 out; 2 in
String stmt = "{? = CALL addx(?, ?)}"; 
 CallableStatement cs = getConnection().prepareCall(stmt);
 //register the output parameter, index 1
 cs.registerOutParameter(1, java.sql.Types.INTEGER);
 //define the 2 in parameters
 cs.setInt(2, 2); //index 2
 cs.setInt(3, 4); //index 3 
 cs.execute();//call the routine 
 int sum = cs.getInt(1); //get the result

To automate the calling of a routine, I've created a simple utility class:

//Utility class

package com.ipiel.jdbc.mysql;
import java.awt.image.ConvolveOp;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Enumeration;
import java.util.Vector;

public class JDBCUtility {
 private Connection conn;
 private String uname;
 private String pass;
 private String db;
 private String host;
 private String port;
 private String database;
 
 /**
  * Returns an instance of a JDBC Connection object. 
  * If an instance is null it is initialize.
  * @return
  */
 private Connection getConnection() {
  if(conn == null) {
   init();
   try {
    Class.forName("com.mysql.jdbc.Driver");
    conn = DriverManager.getConnection(
    "jdbc:"+database+"://"+host+":"+port+"/"+db, uname, pass);
   } catch(ClassNotFoundException ce) {
    ce.printStackTrace();
   } catch(SQLException se) {
    se.printStackTrace();
   }
  }
  return conn;
 }
 
 /**
  * Initialize the database connection configuration.
  */
 private void init() {
  database = "mysql";
  db = "employee";
  port = "3306";
  host = "127.0.0.1";
  uname = "user";
  pass = "password";
 }
 
 /**
  * Calls a simple function routine
  * @param fxnName name of function
  * @param v Vector list of Parameter objects
  * @param returnType
  * @return
  */
 public Object callSimpleFunction(String funcName, Vector v, String returnType) {
  Object obj = "";
  try {
   StringBuilder sb = new StringBuilder();
   sb.append("{? = call ");
   
   sb.append(funcName + "(");
   for(int i = 0; i < v.size(); i++) {
    sb.append("?,");
   }   
   sb.deleteCharAt(sb.length() - 1);
   sb.append(")}");
   
   //initialize the statement
   CallableStatement cs = getConnection().prepareCall(sb.toString());
   
   //first parameter return type (instance of ? character)
   cs = setFunctionReturnType(cs, returnType);
   
   Enumeration e = v.elements();
   //index 2 is the start of parameter input
   //format of fxn call {? = call fxnName(?,?)}
   int index = 2;
   while(e.hasMoreElements()) {
    Parameter param = e.nextElement();
    cs = setParameter(cs, param.getType(), param.getValue(), index++);
   }
   
   cs.execute();
   
   //index 1 is always the return binder
   //fix this
   obj = getFunctionReturn(cs, returnType);
  } catch(SQLException e) {
   e.printStackTrace();
  }
  
  return obj;
 }
 
 /**
  * Calls a simple procedure that returns resultset.
  * @param procName
  * @param v
  * @return
  */
 public ResultSet callSimpleProcedure(String procName, Vector v) {
  ResultSet rs = null;
  try {
   StringBuilder sb = new StringBuilder();
   sb.append("{call ");
   
   sb.append(procName + "(");
   for(int i = 0; i < v.size(); i++) {
    sb.append("?,");
   }   
   sb.deleteCharAt(sb.length() - 1);
   sb.append(")}");
   
   System.out.println(sb.toString());
   
   //initialize the statement
   CallableStatement cs = getConnection().prepareCall(sb.toString());
   
   Enumeration e = v.elements();
   //no more return bind
   int index = 1;
   while(e.hasMoreElements()) {
    Parameter param = e.nextElement();
    cs = setParameter(cs, param.getType(), param.getValue(), index++);
   }
      
   cs.execute();
   
   rs = cs.getResultSet();
  } catch(SQLException e) {
   e.printStackTrace();
  }
  return rs;
 }
 
 /**
  * Sets the return type of a function. Acceptable types are integer,
  * Take note that the return index binded is always 1.
  * @param cs
  * @param returnType
  * @return
  */
 private CallableStatement setFunctionReturnType(CallableStatement cs, 
                String returnType) {
  int sqlType = -1;
  
  try {
   if(returnType.equals("integer")) {
    sqlType = java.sql.Types.INTEGER;
   } else if(returnType.equals("varchar")) {
    sqlType = java.sql.Types.VARCHAR;
   } else if(returnType.equals("boolean")) {
    sqlType = java.sql.Types.BOOLEAN;
   } else if(returnType.equals("datetime")) {
    sqlType = java.sql.Types.DATE;
   }
   if(sqlType != -1)
    cs.registerOutParameter(1, sqlType);
  } catch(SQLException e) {
   e.printStackTrace();
  }
  return cs;
 }
 
 /**
  * Gets the return value from a function based on returnType variable.
  * @param cs
  * @param returnType
  * @return
  * @throws SQLException
  */
 private Object getFunctionReturn( CallableStatement cs, String returnType) 
          throws SQLException {
  Object obj = "";
  if(returnType.equals("integer")) {
   obj = cs.getInt(1);
  } else if(returnType.equals("varchar")) {
   obj = cs.getString(1);
  } else if(returnType.equals("boolean")) {
   obj = cs.getBoolean(1);
  } else if(returnType.equals("datetime")) {
   obj = cs.getDate(1);
  } else if(returnType.equals("resultset")) {
   obj = cs.getResultSet();
  }
  return obj;
 }
 
 /**
  * Sets the functions parameter.
  * @param cs
  * @param paramType
  * @param value
  * @param index
  * @return
  * @throws SQLException
  */
 private CallableStatement setParameter( CallableStatement cs, String paramType, 
         Object value, int index) throws SQLException {
  if(paramType.equals("integer")) {
   cs.setInt(index, Integer.valueOf(value.toString()));
  } else if(paramType.equals("varchar")) {
   cs.setString(index, value.toString());
  } else if(paramType.equals("varchar")) {
   cs.setBoolean(index, Boolean.valueOf(value.toString()));
  } else if(paramType.equals("datetime")) {
   cs.setDate(index, Date.valueOf(value.toString()));
  }
  return cs;
 }
 
 /**
  * Dispose the current open Connection instance.
  */
 public void dispose() {
  try {
  if(conn.isClosed())
   conn.close();
  } catch(SQLException e) {
   e.printStackTrace();
  }
 }
}

//Parameter Object

package com.ipiel.jdbc.mysql;

public class Parameter {
 /**
  * Acceptable types integer, string(max 50 chars), boolean, date
  */
 private String type;
 private Object value;
 
 public Parameter() {
  type = "integer";
  value = "";
 }
 
 public Parameter(String type, Object value) {
  this.type = type;
  this.value = value;
 }
 
 public String getType() {
  return type;
 }

 public void setType(String type) {
  this.type = type;
 }

 public Object getValue() {
  return value;
 }

 public void setValue(Object value) {
  this.value = value;
 }
}

//Sample calls

package com.ipiel.jdbc.mysql;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.Vector;

public class Test {
 
 public static void main(String args[]) {
  try {
   new Test().test();
  } catch(Exception e) {
   e.printStackTrace();
  }
 }
 
 private void test() throws SQLException {
  //initialize
  JDBCUtility jconn = new JDBCUtility();
  Vector v = new Vector();
  
  //add 2 numbers
  Parameter x = new Parameter("integer", 2);
  v.add(x);
  x = new Parameter("integer", 4);
  v.add(x);
  Object r = jconn.callSimpleFunction("addx", v, "integer");
  System.out.println("add(2, 4): " + r);  
  
  //get resultset from procedure call
  v.removeAllElements();
  x = new Parameter("integer", 1);
  v.add(x);
  r = jconn.callSimpleProcedure("get_employee_resultset", v);
  printResultSet((ResultSet)r);  
 }
 
 private static void printResultSet(ResultSet rs) throws SQLException {
  if(rs == null) {
   System.out.println("No resultset");
  } else {
   ResultSetMetaData md = rs.getMetaData();
   System.out.println("Record Count: " + md.getColumnCount());
   while(rs.next()) {
    System.out.println(rs.getInt(1) + " " + rs.getString(2));
   }
  }
 }
}

Mysql database scripts:
//create employee database

DROP TABLE IF EXISTS `employee`;
CREATE TABLE IF NOT EXISTS `employee` (
  `employee_id` int(10) unsigned NOT NULL auto_increment,
  `employee_name` varchar(45) NOT NULL,
  `employee_male` int(10) unsigned NOT NULL default '1',
  `employee_createdat` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  USING BTREE (`employee_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `employee` (
`employee_id`, `employee_name`, `employee_male`, `employee_createdat`
) VALUES
(1, 'employee1', 1, '2009-11-11 00:00:00'),
(2, 'employee2', 0, '2009-12-31 00:00:00');

//function addx

DELIMITER $$
DROP FUNCTION IF EXISTS `employee`.`addx` $$
CREATE FUNCTION `employee`.`addx` (a INT, b INT) RETURNS INT
BEGIN
  DECLARE c INT;
  SET c = a + b;
  RETURN c;
END $$
DELIMITER ;

//function get_employee_resultset

DELIMITER $$
DROP PROCEDURE IF EXISTS `employee`.`get_employee_resultset` $$
CREATE PROCEDURE `get_employee_resultset`(id INT)
BEGIN
  SELECT employee_id, employee_name FROM employee WHERE employee_id=id;
END $$
DELIMITER;