Tech-Today

How to create and call a database routine (stored procedure/function) using a jdbc driver in 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;

- Connect To Oracle Database In Java Language
This code connects to an oracle database using java programming language. This to remember: 1.) You must add ojdbc14.jar to the project's build path. 2.) Oracle's driver name: oracle.jdbc.driver.OracleDriver 3.) Connection string: jdbc:oracle:client:@server:port:database...

- C# Converting A Date To Timestamp And How To Create An Md5 Function
A always use these 2 methods so I'm gonna paste them here. I don't remember if I change some lines in these code but I'm sure I got them from Microsoft hmmm already forget about it. DateTime -> TimeStamp /// /// method for converting a System.DateTime...

- How To Get Started With Hibernate In Java. Create A Simple Class And Database Schema For Demostration.
Since, I've always been using different database sometimes I get confused how to implement the others. Like hibernate, where the configuration must be properly set. *This help assumes that you have already created a java project in eclipse that has...

- Moving Data From A Table In One Database Into Another With The Same Table Definition
Objective: Given the same schema of database for example an active and archive, how should we move rows of data from one table in one database into the same table name in another database. Below is a java code snippet/pseudocode that I've created:...

- How To Connect Mysql Or Postgresql Server From Java Using Jdbc Driver
How to connect MySql or PostgreSql Server from Java using JDBC Driver I think the best answer to this is through coding: private static Connection getConnection() { Connection conn = null; try { Class.forName("com.mysql.jdbc.Driver"); //conn = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5432/anime",...

Tech-Today