Create java classes from XML files in Rational Application Developer 8 using JAXB

If you have XML files filed with data and want to map it to java object inside your application you can achieve it easy using IBM Rational Application Developer 8 (RAD).

To do this, necessary things are:

- Data in the form of XML file
- XML to XML schema generator
- IBM RAD tool


JAXB stands for Java Architecture for XML Binding. JAXB provides ability to marshal Java objects into XML and the inverse, i.e. to unmarshal XML back into Java objects. To create appropriate class for XML file  JAXB requests XML schema (XSD) for XML data. An XML Schema describes the structure of an XML document.
Very often, in real life, XML files are created  and populated with data but without XSD  schema. Using XML file to XML schema generator this problem can be solved. There are a lot of on-line generators and one of them is: http://www.freeformatter.com/xsd-generator.html

Copy-paste your XML into generator and click Generate XSD. Copy generated code (XSL schema ) and paste it into new created file inside RAD. Save file with xsd extension. Then right click and choose New. Type jaxb and select Schema to JAXB Java Classes option.

Click Next.

 

                         
                                                                                                                                            

 Choose your .xsd file and click Next.

Click Generate serializable JAXB classes and finish.

RAD will generate  java and class files inside generated folder in target container.
The class name is the same as the XML root element name.

How to automatically generate JDBC code for reading data from REF CURSOR inside Oracle's stored procedure

   Sometimes we have to write JDBC code which reads  block of data (record set) from oracle procedure.  Since Oracle 7.3 the REF CURSOR type has been available to allow recordsets to be returned from stored procedures and functions. Ref cursor can return more or less data fields inside each row.

For example,  the following procedure opens a query using a SYS_REFCURSOR  as output parameter:

CREATE OR REPLACE
PROCEDURE get_data_rs (p_cur OUT SYS_REFCURSOR) AS
BEGIN
    OPEN p_cur FOR
       SELECT bank,
                       name,
                       date,
                       address
       FROM some_table;
END get_data_rs;

To collect data, your  JDBC code should be something like this:

   Connection conn = DriverManager.getConnection (" some connectionURL ...");
   CallableStatement cs= conn.prepareCall("{call get_data_rs (?)}");
   cs.registerOutParameter(1, OracleTypes.CURSOR); //REF CURSOR
   cs.execute();

  ResultSet rs = cs.getCursor(1);

  while (rs.next()) {
            //  Reading ref cursor values
           // *** This code  can be automatically  generated
  }
   rs.close();
   rs = null;
   cs.close();
   cs= null;
   conn.close();
   conn = null;

For each value inside recordset a getXXX() method (e.g., getInt(), getString()) is called.

Hand coding this is very error prone and time consuming task, especially if there are a large number of fields inside ref cursor or there are several PL/SQL procedures  to be called.

The idea is to make script which automatically generate appropriate reading JDBC code knowing parameters name and type in ref cursor.

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

bank  NUMBER
nameVARCHAR2
date  DATE
address VARCHAR2

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

Second, write java program which reads file and creates desired output -  JDBC code which reads  ref cursor data from oracle procedure.

The program is listed below:

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

public class OutputJDBC {
 /**
  * @param args
  */
 public static void main(String[] args) {
  // Location of file to read
  File file = new File(
    "C:\\output.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(" ");
   if (str[str.length - 1].trim().startsWith("VARCHAR2")) {
    System.out.println("rs.getString(\"" + str[0].trim() + "\")");
   }
   if (str[str.length - 1].trim().startsWith("NUMBER")) {
    System.out.println("rs.getInt(\"" + str[0].trim() + "\")");
   }
   if (str[str.length - 1].trim().startsWith("DATE")) {
    System.out.println("rs.getDate(\"" + str[0].trim() + "\")");
   }
  }
 }
}

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

rs.getInt("bank")
rs.getString("name")
rs.getDate("date")
rs.getString("address")

Fourth, fill your application data with results from cursor.

That is all. We have generated jdbc code which reads  ref cursor from oracle pl/sql (stored) procedure.

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.