Dismiss
InnovationQ will be updated on Sunday, Oct. 22, from 10am ET - noon. You may experience brief service interruptions during that time.
Browse Prior Art Database

The way to generate application components automatically from SQL

IP.com Disclosure Number: IPCOM000016382D
Original Publication Date: 2003-Feb-15
Included in the Prior Art Database: 2003-Jun-21
Document File: 8 page(s) / 106K

Publishing Venue

IBM

Abstract

This is a method to generate application components not only by a DDL but also by select/update/delete/insert SQL statements. By using this method, user can generate data access components including specific SQL statements, not generic SQL statements.

This text was extracted from a PDF file.
At least one non-text object (such as an image or picture) has been suppressed.
This is the abbreviated version, containing approximately 30% of the total text.

Page 1 of 8

The way to generate application components automatically from SQL

   Currently, most J2EE tools can generate EJB CMP components from DDL (CREATE
TABLE statement). However SQL statements which are included in EJB CMP are not
optimized for performance. Because these SQLs are generic SQL statements to
create/retrieve/update/delete DB records.

For example, if an EJB CMP have 20 fields, its retrieve method always
retrieves data of 20 columns even if application uses only 1 column. If
application queries 100 records from DB, 100*20=2000 columns of data are
retrieved, but only 100*1=100 columns are referred by application. It is not
optimized and not good for performance.

Following is a sample DB definition of EMPLOYEE table.

DDL-1)

  CREATE TABLE EMPLOYEE
(EMPNO CHARACTER(6) NOT NULL,

FIRSTNME VARCHAR(12) NOT NULL,
MIDINIT CHARACTER(1) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
WORKDEPT CHARACTER(3),
PHONENO CHARACTER(4),
HIREDATE DATE,
JOB CHARACTER(8),
EDLEVEL SMALLINT NOT NULL,
SEX CHARACTER(1),
BIRTHDATE DATE,
SALARY DECIMAL(9, 2),
BONUS DECIMAL(9, 2),
COMM DECIMAL(9, 2));

If user intends to retrieve all columns of the record, user describes SQL
statement as follows.

SQL-1)

SQL_retrieve_Employee =
SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE, SALARY, BONUS, COMM FROM DB2ADMIN.EMPLOYEE WHERE EMPNO = :EMPNO

If user intends to retrieve 1 column of the record, user describes SQL
statement as follows.

SQL-2)

SQL_list_EmployeeName = SELECT LASTNAME FROM DB2ADMIN.EMPLOYEE WHERE EMPNO = :EMPNO

When we use EJB CMP, EJB always issues SQL-1), even if application refers only
1 column. But using this method, user can generate separate method which
includes optimized SQLs.

Method:

User makes SQL list which is required for a set of application.

User specifies component name (Employee / EmployeeName) for each SQL.

Generation tool analyzes all SQLs in the list. And categorizes by DB's


1.


2.


3.

table name.

1

Page 2 of 8


4.


5.


6.

Result Bean.

Prototype:

I made a tool to proof of this concept. This tool generates 1 DAO(Data Access
Object) and 2 Beans(Value Objects) from these 2 SQL statements. 2 SQLs are
included in separated 2 methods (getEmployee/getEmployeeName) of DAO
component. Each of method returns Employee bean or EmployeeName bean.
Employee bean has 14 fields and EmployeeName bean has 1 field. Tool also
generate Facade bean. Facade bean includes all methods of a set of DAOs.

User can access all DAO methods via 1 Facade bean.

Generate DAO class for each table name. If 4 SQLs are included in the SQL

list, this DAO has 4 methods to execute each SQL statement.
Generate Entry Beans and Result Beans for each SQL statement. Entry bean

is generated by conditions in SQL. If the condition is "WHERE EMPNO = :EMPNO", Entry bean has 1 field "empno" and its setter/getter method.
Result Beans is generated by retrieved columns. If the retrieved column is
"LASTNAME", Result Bean has only 1 field "lastname". Tool also generate
Result List bean li...