OracleAS TopLink - HowTo use batch reading/writing using OracleAS TopLink

Date: 23/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. OracleAS TopLink supports batch reading, which significantly reduces the number of required SQL select statements and improves database access efficiency. Also OracleAS TopLink supports batch writing, which queues the queries to be executed and send them as batch to the database and improves database access efficiency.

Prerequisites 

• You must have little knowledge about JDBC.

Software Requirements 

• Oracle Database 10g installed with HR Schema
• OracleAS TopLink 10g

HowTo enable batch reading/writing using OracleAS TopLink 

The way the application reads data from the database affects performance. For example, reading a collection of rows from the database is significantly faster than reading each row individually. A common performance challenge is to read a collection of objects that have a one-to-one reference to another object. This normally requires one read operation to read in the source rows, and one call for each target row in the one-to-one relationship.

To reduce the number of reads required we can use join and batch reading. Batch reading propagates query selection criteria through an object's relationship attribute mappings. Batch reading can be nested down through complex object graphs. This significantly reduces the number of required SQL select statements and improves database access efficiency.

Batch writing can improve database performance by sending groups of INSERT, UPDATE, and DELETE statements to the database in a single transaction, rather than individually. TopLink supports batch writing for selected databases and for JDBC 2.0 batch-compliant drivers.


i) To enable batch reading, set the uses-batch-reading tag to true in project.xml.

<project>
  ...
  <descriptors> 
    ...
    <descriptor>
      ...
      <mappings>
        <database-mapping>
          <attribute-name>department</attribute-name>
          ...
          <uses-batch-reading>true</uses-batch-reading>
          ...
        </database-mapping>
      </mappings>
    <descriptor>  
 </descriptors>
</project>

Alternatively, batch reading can be enabled selectively for a particular object using the API shown like below :

 

public Vector getEmployees(){ Vector employees = new Vector(); ReadAllQuery readAllQuery = new ReadAllQuery(); readAllQuery.setReferenceClass(Employees.class); readAllQuery.setSelectionCriteria(new ExpressionBuilder().get("employeeId").between(199,206)); readAllQuery.addBatchReadAttribute("department"); employees = (Vector)getSession().executeQuery(readAllQuery); return employees; }

ii) To enable batch writing, set the uses-batch-writing in sessions.xml

<toplink-configuration> <session> ... <login> ... <uses-batch-writing>true</uses-batch-writing> </login> </session> </toplink-configuration>

Steps to run the sample 

• Download the jar from here
  
• Extract the BatchSample.jar file
• Edit <SAMPLE_HOME>/src/session.xml, set database parameters in <connection-url> tag
  
• Add toplink.jar, xmlparserv2.jar,connector.jar, and classes12.jar to the classpath
• Also add <SAMPLE_HOME>/src folder to the classpath
• From <SAMPLE_HOME>/src/oracle/otnsamples/toplink/batch directory, Compile the java sources as shown below
  javac -d . *.java
• For executing this sample with batch reading feature, from <SAMPLE_HOME>/src/oracle/otnsamples/toplink/batch directory Run
  java oracle.otnsamples.toplink.batch.EmpMgmtBean read
  
  For executing this sample with batch write feature, from <SAMPLE_HOME>/src/oracle/otnsamples/toplink/batch directory Run
  java oracle.otnsamples.toplink.batch.EmpMgmtBean write
  
  
• Since logging is enabled you can compare the number of sql statements executed by enabling and disabling the batch reading/writing.
  

Useful References 

• OracleAS TopLink 10g
• OracleAS TopLink Discussion Forum