PL/SQL Table-to-Java Bean (and Data to Java Bean Manager) generator – useful for data driven demos without database

The other day, I sat preparing for a demonstration of a data driven Java Web application. I preferred to be able to present the demo without actually having to use the database – as it takes a lot of time to start and too many resources from my hard press laptop. I only needed several dozens of records to show in the web pages, nothing too serious.

I decided it would be convenient to have a quick way of creating Java Beans from my table definitions (very much like many JPA tools & frameworks can do) and generate code that will instantiate a series of objects based on those bean definitions with the same data that also live in my database. I can then shutdown the database, yet continue working with data in my Java application. This article introduces a PL/SQL package that will do this for me.

The result of running this package for tables EMP and DEPT looks like this – and allows me EMP & DEPT based demonstrations without the need for a database up and running!

 

As an example of how to use this, we will see how an ADF Data Control can be created for the EmpManager class that allows quick development (& prototyping) of data driven web applications.

The PL/SQL Package Specification:

create or replace
package table_to_bean_generator
is

procedure generate_bean
( p_tbl_name in varchar2)
;
procedure generate_bean_mgr
( p_tbl_name in varchar2
, p_where    in varchar2 default null
);

end table_to_bean_generator;
 

We can call the package for one table or view at a time (note: the package does not distinguish between tables and views). To generate the bean class from a table definition, we call:

begin
  table_to_bean_generator.generate_bean( p_tbl_name=> 'EMP' );
end;

The implementation of the generate_bean procedure is fairly straightforward:

create or replace package body table_to_bean_generator
is
  cursor c_col(b_tbl_name in varchar2)
  is
    select column_name
    ,      data_type
    ,      data_scale
    ,      data_length
    ,      case data_type
           when 'VARCHAR2'
           then 'String'
           when 'DATE'
           then 'Timestamp'
           when 'NUMBER'
           then case data_scale
                when 0
                then 'Long'
                else 'Double'
                end
           end java_type
    from   user_tab_columns
    where  table_name = b_tbl_name
    ;
...
procedure w(p_txt in varchar2)
is
begin
  dbms_output.put_line(p_txt); --||chr(13)||chr(10));
end w;
procedure generate_bean
( p_tbl_name in varchar2)
is
  l_prefix varchar2(1):='';
begin
  -- mapping: Date => java.sql.Timestamp
  --          Number - scale==0 : Long
  --          Number - scale<>0 : Double
  --          varchar2          : String
  --          at this point, ignore all others
  -- create class with imports, constructor
  w('package nl.amis.hrm;');
  w('import java.sql.Timestamp;');
  w('public class '||initcap(p_tbl_name)||' {');
  -- iterate over columns
  for col in c_col(b_tbl_name => p_tbl_name) loop
    w('private '||col.java_type||' '||lower(col.column_name)||';');
  end loop;

  w('// Constructor');
  w('public '||initcap(p_tbl_name)||' () {');
  w('}');
  w('public '||initcap(p_tbl_name)||' ( ');
  for col in c_col(b_tbl_name => p_tbl_name) loop
    w(l_prefix||col.java_type||' '||lower(col.column_name));
    l_prefix:=',';
  end loop;
  w(') { ');
  for col in c_col(b_tbl_name => p_tbl_name) loop
    w('this.'||lower(col.column_name)||'='||lower(col.column_name)||';');
  end loop;
  w('}');

  -- now generate accessor methods
  for col in c_col(b_tbl_name => p_tbl_name) loop
    w('public void set'||initcap(col.column_name)||' ('||col.java_type||' '||lower(col.column_name)||') {');
    w('this.'||lower(col.column_name)||'='||lower(col.column_name)||';');
    w('}');
    w('public '||col.java_type||' get'||initcap(col.column_name)||' () {');
    w('  return this.'||lower(col.column_name)||';');
    w('}');
  end loop;
    w('}');
end generate_bean;

A loop over the columns in the table, a private member for each column as well as a getter and a setter method. Also an additional bean constructor method that accepts all properties as input. The result of generating a bean class looks like this:

package nl.amis.hrm;

import java.sql.Timestamp;

public class Dept {
    private Long deptno;
    private String dname;
    private String loc;
    // Constructor

    public Dept() {
    }

    public Dept(Long deptno, String dname, String loc) {
        this.deptno = deptno;
        this.dname = dname;
        this.loc = loc;
    }

    public void setDeptno(Long deptno) {
        this.deptno = deptno;
    }

    public Long getDeptno() {
        return this.deptno;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getDname() {
        return this.dname;
    }

    public void setLoc(String loc) {
        this.loc = loc;
    }

    public String getLoc() {
        return this.loc;
    }
}
 

This code can be pasted in any Java editor, for example JDeveloper, to create and compile the class.

The class generated here can be created using most Java IDEs and JPA & Persistence frameworks, so nothing special here. However, the next step makes it more interesting. 

For each table or view, I want to have a manager class that returns a list of instances and supports a primary key based query. The DeptManager class generated for table DEPT looks like this:

package nl.amis.hrm;

import java.util.ArrayList;
import java.util.List;

import java.sql.Timestamp;

public class DeptManager {
    private List<Dept> depts = new ArrayList();
    // Constructor

    public DeptManager() {
        resetData();
    }

    public void resetData() {
        depts = new ArrayList();
        createDept((long)10, "ACCOUNTING", "NEW YORK");
        createDept((long)20, "RESEARCH", "DALLAS");
        createDept((long)30, "SALES", "CHICAGO");
        createDept((long)40, "OPERATIONS", "BOSTON");
    }

    public void createDept(Long deptno, String dname, String loc) {
        Dept newDept = null;
        newDept = new Dept(deptno, dname, loc);
        depts.add(newDept);
    }

    public Dept findDept(Long id) {
        // iterate through depts to locate entry where get<PrimaryKeyColumn> equal to id
        // if none are found, return null
        Dept deptFound = null;
        for (Dept dept: depts) {
            if (dept.getDeptno().compareTo(id) == 0) {
                deptFound = dept;
                break;
            }
        }
        return deptFound;
    }

    public void setDepts(List<Dept> depts) {
        this.depts = depts;
    }

    public List<Dept> getDepts() {
        return depts;
    }

    public static void main(String[] args) {
        DeptManager mgr = new DeptManager();
        System.out.println("Found " + mgr.getDepts().size() + "Depts");
    }
}
 

You see how this class provides the getDepts() method that returns a List of Dept instances. In the method resetData(), called from the constructor, the internal list of Dept instances is instantiated, based on the data found in the table in the database. The DeptManager class also provides the createDept() method, to add a new Dept object to the internal collection and the findDept() method that returns the instance identified by the parameter passed in. The class is generated with a main method that allows rapid inspection of the static data available through the class.

This main method:

    public static void main(String[] args) {
        DeptManager mgr = new DeptManager();
        for (Dept dept:mgr.getDepts()) {
            System.out.println("Department: "+dept.getDeptno()+" - "+dept.getDname());
        }
    }

returns this output:

Department: 10 - ACCOUNTING
Department: 20 - RESEARCH
Department: 30 - SALES
Department: 40 - OPERATIONS
Process exited with exit code 0.

Some limitations of the current implementation of the bean generator:

• only support for Date, Number and Varchar2 data types
• only support for Number based, single column primary keys
• no Java-fication in Table-to-Classname and Column-to-Property conversion (camelcase, no underscores)
• no true object reference but instead plain foreign key value (Emp.getDeptno() instead of Emp.getDept())
• we use the deprecated constructor for Timestamp
• no support for master-detail relationships

The procedures that implement the BeanManagerClass-generator are defined as follows:

procedure  generate_create_statements
( p_class_name in varchar2
, p_tbl_name in varchar2
, p_where in varchar2 default null
) is
  l_prefix varchar2(1):='';
  l_date date;
  l_varchar2 varchar2(2000);
  l_number number;
  l_index integer:=0;
  src_cur  INTEGER;
  ignore   INTEGER;
begin
  src_cur := dbms_sql.open_cursor;
  -- parse the SELECT statement
  dbms_sql.parse(src_cur, 'SELECT * from '||p_tbl_name||nvl(p_where,''),  dbms_sql.NATIVE);
  for col in c_col(b_tbl_name=>p_tbl_name) loop
  -- define the column type
  -- loop over the columns in the order of user_tab_columns (same as select * from )
  -- for each column, do
    l_index:= l_index+1;
    if col.data_type = 'DATE'
    then
      dbms_sql.define_column(src_cur, l_index, l_date);
    elsif col.data_type = 'NUMBER'
    then
      dbms_sql.define_column(src_cur, l_index, l_number);
    elsif col.data_type = 'VARCHAR2'
    then
      dbms_sql.define_column(src_cur, l_index, l_varchar2, col.data_length);
    end if;
  end loop;
  -- loop over all records
  ignore := dbms_sql.execute(src_cur);
  LOOP
    -- Fetch a row from the source table
    IF dbms_sql.fetch_rows(src_cur) > 0
    THEN
      -- get column values of the row
      l_index :=0;
      l_prefix:= '';
      w(' create'||p_class_name||'(');
      -- loop over columns
      -- within record, loop over columns
      for col in c_col(b_tbl_name=>p_tbl_name) loop
        l_index:= l_index+1;
        if col.data_type = 'DATE'
        then
          begin
            dbms_sql.column_value(src_cur, l_index, l_date);
            if l_date is null
            then
              w(l_prefix||'null');
            else
              w(l_prefix||'new Timestamp('||extract(year from l_date)
               ||', '||extract(month from l_date)
               ||', '||extract(day from l_date)
               ||', '||to_char(l_date,'hh24')
               ||', '||to_char(l_date,'mi')
               ||', '||to_char(l_date,'ss')
               ||', 0)'); --y-m-d-h-m-s-n
            end if;
          end;
        elsif col.data_type = 'NUMBER'
        then
          dbms_sql.column_value(src_cur, l_index, l_number);
          if l_number is null
          then
            w(l_prefix||'null');
          else
             if col.data_scale = 0
            then
              w(l_prefix||'(long)'||l_number);
             else
              w(l_prefix||'(double)'||l_number);
            end if;
          end if;
        elsif col.data_type = 'VARCHAR2'
        then
          dbms_sql.column_value(src_cur, l_index, l_varchar2);
          if l_varchar2 is null
          then
            w(l_prefix||'null');
          else
            w(l_prefix||'"'||l_varchar2||'"');
          end if;
        end if;
        l_prefix:= ',';
      end loop;
      w(');');
    ELSE
      -- No more rows to process
      EXIT;
    END IF;
  END LOOP;
end generate_create_statements;
procedure generate_bean_mgr
( p_tbl_name in varchar2
, p_where    in varchar2 default null)
is
  l_prefix varchar2(1):='';
  l_class_name varchar2(40):= initcap(p_tbl_name);
begin
  w('package nl.amis.hrm;');
  w('import java.util.ArrayList;');
  w('import java.util.List;');
  w('import java.sql.Timestamp;');
  w('public class '||initcap(p_tbl_name)||'Manager {');
  w('private List<'||l_class_name||'> '||lower(p_tbl_name)||'s= new ArrayList();');
  w('// Constructor');
  w('public '||initcap(p_tbl_name)||'Manager() {');
  w(' resetData();');
  w('}');
  -- resetData
  w('public void resetData() {');
  w('  '||lower(p_tbl_name)||'s= new ArrayList();');
  generate_create_statements(p_class_name => l_class_name, p_tbl_name => p_tbl_name, p_where => p_where);
  w('}');
  -- create
  w('public void create'||l_class_name||'(');
    l_prefix:='';
    for col in c_col(b_tbl_name => p_tbl_name) loop
      w(l_prefix||col.java_type||' '||lower(col.column_name));
      l_prefix:=',';
    end loop;
  w('){');
  w(' '||l_class_name||' new'||l_class_name||'= null;');
  w('  '||' new'||l_class_name||'= new '||l_class_name||'( ' );
    l_prefix:='';
    for col in c_col(b_tbl_name => p_tbl_name) loop
      w(l_prefix||' '||lower(col.column_name));
      l_prefix:=',';
    end loop;
  w(');');
  w(lower(p_tbl_name)||'s.add(new'||l_class_name||');');
  w('}');
-- generate the find method
  for pk_col in c_pk_col( b_tbl_name => p_tbl_name) loop
  w('public  '||l_class_name||' find'||l_class_name||'( Long id) {');
  w('      // iterate through '||lower(p_tbl_name)||'s to locate entry where get<PrimaryKeyColumn> equal to id ');
  w('      // if none are found, return null ');
  w('  '||l_class_name||' '||lower(p_tbl_name)||'Found = null;');
  w('      for ('||l_class_name||' '|| lower(p_tbl_name)||':'||lower(p_tbl_name)||'s ) {');
  w('        if ('||lower(p_tbl_name)||'.get'||initcap(pk_col.column_name)||'().compareTo(id)==0) {');
  w(            lower(p_tbl_name)||'Found = '||lower(p_tbl_name)||';');
  w('            break;');
  w('        }');
       w('}');
   w('    return '||lower(p_tbl_name)||'Found; ');
   w(' }');
  end loop;
-- generate accessors for emps
   w(' public void set'||l_class_name||'s(List<'||l_class_name||'> '||lower(p_tbl_name)||'s) {');
   w('     this.'||lower(p_tbl_name)||'s = '||lower(p_tbl_name)||'s;');
   w(' }');
   w(' public List<'||l_class_name||'> get'||l_class_name||'s() {');
   w('     return '||lower(p_tbl_name)||'s;');
   w(' }');
-- generate a main method
    w('public static void main(String[] args) {');
        w(l_class_name||'Manager mgr = new '||l_class_name||'Manager();');
        w('System.out.println("Found "+mgr.get'||l_class_name||'s().size()+ "'||l_class_name||'s");');
    w('}');
    w('}');
end generate_bean_mgr;
 

The most complex part of the PL/SQL code is the generate_create_statements procedure that turns the rows from the table or view into create<ClassName> method calls. This procedure uses dbms_sql to retrieve the values of the columns in the rows fetched from the database. Why dbms_sql? We do not know at the time of writing the code what the names are going to be for each of the columns. Even if we were to use a sys_refcursor as source for the data, we do not have the means to get individual column values from it.

The dbms_sql.define_column statements specify the data type for each of the columns fetched from the cursor. When all columns have been registered thus with dbms_sql, we can start fetching rows from the cursor. Using dbms_sql.column_value, we can extract values from the current row by using the column index. Using again the cursor over all table columns – that tells us the data type of the column and in which we keep track of the position or index – we can ask for the value of the column at a certain position and retrieve it into a variable (l_date, l_number or l_varchar2) of the proper data type.

Using the Static Data Collections in Java Web Applications

Now that we have used the Database PL/SQL package to generate four Java Classes – Emp, Dept, EmpMgr and DeptManager – we can shut down the database and start creating the data driven Web Application, based on ADF in this case.

From the right mouse button menu on the class EmpManager, choose the option Create Data Control:

 

The Data Control palette is refreshed, and now presents the EmpManager control:

Create a new JSF page. Drag and drop the emps collection from the Data Control palette to the new page. Drop it as an ADF Table.

 

Add the Commit operation, drop as Command Button. Drag the resetData() operation, also drop as Command button:

 

Run the JSF page.

 

It looks like any ADF page based on a Database Table. However, it is based on just two classes that instantiate a number of beans. Note: the data can be changed in the page, submitted and sorted. Through clicking the resetData  button all changes can be undone and the original data is restored.

For all intents and (demo) purposes, this application seems based on a database. And is developed in a similar fashion. But much less greedy on the laptop resources!

Add Master Detail support

The easiest way to add Master Detail, for example the Dept-Emp master detail relationship:

1. turn the DeptManager class into a singleton

    private static DeptManager ref;
    // Constructor

    private DeptManager() {
        resetData();
    }

    public static DeptManager getDeptManager()
     {
       if (ref == null)
           // can call this constructor
           ref = new DeptManager();
       return ref;
     }
 

2. add the List<Emp> emps private member with accessor methods to the Dept class

    private List<Emp> emps= new ArrayList();
    public void setEmps(List<Emp> emps) {
        this.emps = emps;
    }

    public List<Emp> getEmps() {
        return emps;
    }
 

3. extend the createEmp() method: have it inform the DeptManager of a new Employee detail; add these lines:

   if (deptno!=null) {
     DeptManager.getDeptManager().findDept(deptno).getEmps().add(newEmp);
   }

4. create the HrmManager class that makes the DeptManager available ; we cannot create a DataControl for the DeptManager as it does not have a public constructor.

package nl.amis.hrm;

public class HrmManager {
    public HrmManager() {
    }

    public DeptManager getDeptManager() {
        return DeptManager.getDeptManager();
    }
}

5. Create a Data Control for HrmManager.

 

With these changes, we can create the master-detail page you see here:

Resources

Download package table_to_bean_generator.