Raghu Kodali's Radio Weblog

Raghu Kodali's Radio Weblog

Home

Friday, September 19, 2003

How-to execute Toplink Queries using JavaBean DataControl ?

This posting shows how-to use JavaBean DataControl to execute methods that work on Queries basesd on POJO (Java class with TopLink persistence)

JDeveloper 10g preview release
Oracle DB 9.2

Create an Application Workspace, choose Custom Application [All Technologies]
Reverse engineer Employees table from hr schema as Java Object.

Select project node
File | New
Select Toplink under Business Tier node
Select JavaObjects From Table in Items

Add a Named Query AllEmps which would select * from employees

In the project select TopLink Mappings node under TOPLINK package
Double-click on Employees node in Structure pane. (under TopLink Mappings -> mypackage)
In the TopLink Mapping Editor, Select Queries tab
Click on Add
Enter AllEmps as the name of the query
Click OK
Select Format tab
Select SQL radion button
Enter Select * from Employees in the Query String text box

File | Save All
Generate TopLink Deployment Descriptor

In the project select TopLink Mappings node under TOPLINK package
Right mouse and choose Generate toplink-deployment-descriptor.xml

Create a Java Class which would be a facade for Employees .java (POJO)

Select Project node
File | New
Select Simple Files under General Category
Select Java Class from Items
Name the java class as employeeFacade

Add business methods which would execute TopLink queries. One method would execute AllEmps Named Query and the other one would execute a dyanmic query built with Expression builder

Add an attribute allEmps which is of vector type
In the getter method for this attribute execute AllEmps query

public Vector getAllEmps()
{
    oracle.toplink.sessions.Project project = XMLProjectReader.read("/E:/905build-1375/jdev/mywork/Application11/Model/src/META-INF/toplink-deployment-descriptor.xml");
    DatabaseSession tlsession = project.createDatabaseSession();
    tlsession.login();
    Vector allEmps = (Vector)tlsession.executeQuery("AllEmps",Employees.class);
    return allEmps;
}

Add a method empsInDept which would get all employees in a given department

public Vector empsInDept(Double deptid)
{
    oracle.toplink.sessions.Project project = XMLProjectReader.read("/E:/905build-1375/jdev/mywork/Application11/Model/src/META-INF/toplink-deployment-descriptor.xml");
    DatabaseSession tlsession = project.createDatabaseSession();
    tlsession.login();
    Expression exp = new ExpressionBuilder().get("departmentId").equal(new Double(50));
    Vector myemps =   tlsession.readAllObjects(Employees.class, exp);
    return myemps;
}
Note:- code for toplink session could be abstracted out into a separate method. For simplicity reasons I have that in each method. Path to toplink-deployment-descriptor.xml is hardcoded
Above code would require following import statements
import java.util.Vector;
import oracle.toplink.expressions.Expression;
import oracle.toplink.expressions.ExpressionBuilder;
import oracle.toplink.sessions.DatabaseSession;
import oracle.toplink.tools.workbench.XMLProjectReader;

Create DataControl for employeeFacade class

Select employeeFacade.java in Navigator
Right mouse and choose Create Data Control

Set the bean class to generate metadata

Select employeeFacade.xml (which gets generated) in the Navigator
Select allEmps in the structure pane
Set the Bean Class property in Property Inspector (View | Property Inspector), to Employees.class
Select empsInDept in the structure pane
Set the bean class property to Employees.class

File | Save All
Project | Rebuild

Databinding to JSP pages

Create StrutsPageFlow

Select project node
File | New
Select Struts under Web Tier node
Select StrutsControllerPageFlow in Items

Databind AllEmps NamedQuery

Open StrutsPageFlow diagram
Drop a DataAction and call it /allEmpsDA
Drop a Page and call it /allEmps.jsp
Draw a Forward link between allEmpsDA and /allEmps.jsp
Double click on the /allEmps.jsp
Select allEmps node in Data Control Palette (View | Data Control Palette) and choose Table in "Drop As"
Drag-n-drop allEmps onto allEmps.jsp (JSP Page)

Pageflow would like /allEmpsDA------->/allEmps.jsp
In StrutsPageFlow diagram, select allEmpsDA Data Action, right mouse and chooe Run

Databind a dynamic query built using expression builder (Selects all employess from a particular department)

Open StrutsPageFlow diagram
Drop a page and call it /query.jsp
Drop a Data Action and call it /empsinDeptDA
Double click on the /query.jsp (opens JSP page in visual editor)
In the component palette (View | Component Palette), select Struts HTML
Click on form, In the JavaServer Page Tag Editor, select /empsinDeptDA.do as the action
Click ok
Place the cursor within the form, In the Component palette, click on text, In the JavaServer Page Tag Editor, set the property value as deptid and (scroll down) and value as 50
Click ok
Place the cursor next to text field, In the Component palette, click on submit

Open StrutsPageFlow diagram
Draw a forward link between /query.jsp and /empsinDeptDA
Select empsinDept(Double) (under Operations in Data Control Palette), drop it onto /empsinDeptDA Data Action
In the structure pane, open the node /empsinDeptDA (under Struts-config -> Action Mappings), select paramNames[0]
In the property inspector (View | Property Inspector) , set the value as ${param.deptid}

In the StrutsPageFlow diagram, drop a page from Component palette, rename it as /employees.jsp
Draw a forward link between /empsinDeptDA Data Action and /employees.jsp
Double-click on /employees.jsp
In the Data Control Palette, select return (under Operatins -> empsinDept(Double)), choose Table in Drop AS
Drag-n-drop return onto employees.jsp page

Page Flow would like /query.jsp------>/empsinDeptDA-------->/employees.jsp
In the Strutspageflow diagram, select /query.jsp JSP page , right mouse and choose Run

11:45:15 AM