How to automatically generate JDBC code for calling Oracle's stored procedure or function

Imagine you have oracle PL/SQL (stored) procedure (or function) and want to call it from within java program, you have to write some JDBC code to achieve it.  To do this, following  information are necessary:

1. Procedure (function) name
2. Number of parameters (input and output)
3. Parameters names
4. Parameters types


Your code should be something like this:

Connection con = DriverManager.getConnection (" some connectionURL ...");
CallableStatement cs= conn.prepareCall("{call ProcName(?,?,?)}");

cs.setXXX(name1, value); // *** This code can be automatically generated
cs.setXXX(name2, value);
cs.setXXX(name3, value);

cs.execute();

Let's say you want to call procedure containing input parameters eg. send data from within  your java application to oracle procedure. Handling procedure with output parameters can be achieved on similar way with some minor changes in the code.

Send data from java application to oracle pl/sql procedure

The  procedure's input parameters are bound to CallableStatement object using  setXXX() methods on the object.
For each input bind parameter, a setXXX() method (e.g., setInt(), setString(),) is called.

cs.setXXX(name, value);

name - procedure's parameter name
value - strong type value

Now, you can write this method for every parameter by hand, step by step. Of course, it is possible, but just imagine you have 20 or 30 + parameters in your oracle procedure or dozen of  procedures you must call within your application. Or both of above.
In one of my projects I had 40 + procedures to be called and some of them had more than 20 parameters.
Hand coding this is very error prone and time consuming task.

The idea is to make script which automatically generate appropriate JDBC code knowing stored procedure's signature.

Let's say you have following oracle procedure signature:

PROCEDURE  input_proc
       (p_bank              IN NUMBER
        p_name             IN VARCHAR2
        p_date               IN DATE)

First step is to create file that contains parameters names and their types.

p_bank IN NUMBER,
p_name IN VARCHAR2,
p_date IN DATE

File is named: input.txt and saved somewhere on file system.

Second, write java program which reads file and creates desired output - calling JDBC code.
The program is listed below:

import java.io.File;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.Scanner;


public class InputJDBC {
 public static void main(String[] args) {
  // Location of file to read
  File file = new File("C:\\input.txt");
  ArrayList<String> var2 = new ArrayList<String>();
  try {
   Scanner scanner = new Scanner(file);
   while (scanner.hasNextLine()) {
    String line = scanner.nextLine();
    System.out.println(line);
    var2.add(line);
   }
   scanner.close();
   System.out.println("*************************************");
   generateOutput(var2);
  } catch (FileNotFoundException e) {
   e.printStackTrace();
  }
 }
 public static void generateOutput(ArrayList<String> variables) {
  String str[] = null;
  for (String s : variables) {
   if (s.length() == 0)
    continue;
   str = s.split("IN");
   if (str[1].replace(',', ' ').trim().startsWith("VARCHAR2")) {
    System.out.println("cs.setString(\"" + str[0].trim()
    + "\", null);");
   }
   if (str[1].replace(',', ' ').trim().startsWith("NUMBER")) {
    System.out.println("cs.setInt(\"" + str[0].trim()
      + "\", null);");
   }
   if (str[1].replace(',', ' ').trim().startsWith("DATE")) {
    System.out.println("cs.setDate(\"" + str[0].trim()
    + "\", null);");
   }
  }
 }
}


Third, run program above and copy and paste generated output to your aplication (on the place with ***), it looks like:

cs.setInt("p_bank", null);
cs.setString("p_name", null);
cs.setDate("p_date", null);

Fourth, put your desired data values instead of null inside the setXXX() methods.

That is all. We have generated jdbc code for calling PL/SQL procedure.