OracleAS TopLink - HowTo create CMP Entity Beans which use EJB-QL
using OracleAS TopLink Workbench

Date: 19/04/2004

Table of Contents

Introduction 

Oracle Application Server TopLink is an advanced object-to-relational persistence. It helps to build high performance applications that store persistent data in a relational database. You can use the OracleAS TopLink Workbench to create CMP entity beans with finders.You can also provide custom SQL queries for complex finder methods in the CMP entity beans.

Prerequisites 

• Minimum knowledge of EJB.
  
• Minimum knowledge on how to use OracleAS TopLink Workbench

Software Requirements 

• Oracle Database 10g installed with HR Schema
• OracleAS TopLink 10g
• Apache ANT 1.4.1 or above
• JDK 1.4.x
  
• Oracle Application Server 10g

How to use EJB-QL and custom SQL with TopLink CMP for OC4J

OracleAS TopLink Workbench provides a graphical way of creating EJB 2.0 CMP entity beans,with toplink bindings for OC4J. The workbench also provides a way to override EJB-QL queries with custom SQL queries,for complex finder methods.
   We will use two entities from the HR schema- Employees and Departments. There is a 1-many relationship between Departments and Employees.
    To create a TopLink project for CMP entity beans,follow the steps below
1.Select File->New Project. Save the Mapping Workbench Project (mwp) file. (Say,ejbql.mwp)
2. Select the ejbql node,and select 'Entity Beans using CMP 2.0' under the 'General' tab.
3.Expand ejbql, click on the Database Node.
4.Add a new Login by clicking on the Add Button.Provide a name for the login.
5.Select the login and fill in Driver Class,URL,User Name and Password fields.

6.Right click on the Database node and select Login.Now you are logged in to the database.
7.To create CMP entity beans from existing tables, right click on Database and select 'Add or Update Existing Tables'
8. Select the schema and press 'Get Table Names'
9. Select the tables you need (Say,EMPLOYEES and DEPARTMENTS).
10. Select Database->EMPLOYEES->Generate EJB entities and Descriptors from all tables

This will generate ejb-jar.xml and CMP entity beans under the project node.

To add a finder to the entity bean -
1. Select EmployeesEJB under 'ejbql' node
2. Go to the EJB Info tab and add the primary key class as oracle.otnsamples.tl.ejbql.EmployeePK.(Specify oracle.otnsamples.tl.ejbql.DepartmentPK as the primary key class for DepartmentEJB)
3. Go to the Queries tab and press 'Add' under Named Queries tab.Enter the name of the finder.

4. You can add parameters to this finder in the 'General' tab.

5. Now enter the EJB-QL in th textarea provided under the 'Format' tab.In ejb-jar.xml, the query will appear as-

<ejb-jar> ... <query> <query-method> <method-name>findByDeptName</method-name> <method-params> <method-param>java.lang.String</method-param> </method-params> </query-method> <ejb-ql> <![CDATA[select object(e) from EmployeesEJB e where  e.department.departmentName =?1]]> </ejb-ql> </query> ... </ejb-jar>

6. If you want to use a complex custom SQL instead of EJB-QL, select 'SQL' in the 'Format' tab
and enter the SQL in the text area.

Note that we are using ROWNUM to restrict the number of rows returned, which is not possible in EJB-QL
In ejb-jar.xml, such queries will appear as-

<ejb-jar> ... <query> <query-method> <method-name>findTop3SalariedEmployees</method-name> <method-params/> </query-method> <ejb-ql> <![CDATA[TopLink_Implemented_Query]]> </ejb-ql> </query> ... </ejb-jar>

7.After creating the finders you can export Model Java Source which will create Java sources for the beans.
8. To create the toplink deployment descriptor,select File->Export->Project Deployment XML. Make sure that the file name is toplink-ejb-jar.xml, since this name is understood by OC4J

Part of the toplink-ejb-jar.xml looks like this-

<database-query> <query-name>findTop3SalariedEmployees</query-name> ... <sql-string>SELECT * FROM EMPLOYEES WHERE ROWNUM &lt; 4 ORDER BY SALARY DESC</sql-string> <reference-class>oracle.otnsamples.tl.ejbql.EmployeesEJB</reference-class> ... </database-query> ... <database-query> <query-name>findByDeptName</query-name> ... <ejbql-string>select object(e) from EmployeesEJB e where e.department.departmentName =?1</ejbql-string> <query-arguments> <string>1</string> </query-arguments> <query-argument-types> <values>java.lang.String</values> </query-argument-types> ... </database-query>

Steps to run the sample 

• Download the jar from here
  
• Extract the jar file
• Edit <SAMPLE_HOME>/META-INF/data-sources.xml to point to your database installation.
• Set the following environment variables
• OC4J_HOME to the OC4J installation directory 
• OC4J_HOST to the machine where OC4J is running.
• JAVA_HOME to the  J2SDK 1.4.x installation directory
• ANT_HOME to the ANT 1.4.1 installation directory
• Make sure that <JAVA_HOME>/bin and <ANT_HOME>/bin are in PATH
  
• From <SAMPLE_HOME> directory run,
• ant -this will deploy the application to OC4J 10g
• ant run - this will run the client class oracle.otnsamples.tl.ejbql.EmployeeManagerClient
  (Note : change the password for 'admin' in method getInitialContext(), if it is different from 'welcome')
  

• The client class will output the results of invoking 'findTop3SalariedEmployees' and 'findByDeptName' as a table of Names v/s salaries
  

Useful References 

• OracleAS TopLink 10g
• OracleAS TopLink Discussion Forum