Implementing the Data Access Interface with SQLJ 

Use

To access the database using SQLJ, you need an appropriate implementation of the data access interface temp.persistence.gettingstarted.dao.DAO. This procedure demonstrates how you implement the interface with SQLJ.

The example uses local transactions in the communication with the database. This model can be used in simple applications only. Therefore, when writing complex J2EE applications, you must use JTA transactions.

Prerequisites

You must have created the:

• GettingStartedOpenSQLWeb project
• DAO interface
• and the connection context Ctx and the result set iterator EmployeeIter.

Procedure

...

       1.      In the Java Perspective, select GettingStartedOpenSQLWeb and open its context menu.

       2.      Choose New → Other… Select Persistence in the left-hand pane, and SQLJ Source in the right-hand pane.

       3.      Choose Next.

       4.      Enter SqljDAO as the class name. To choose a package, use Browse… next to the Package field, and then select temp.persistence.gettingstarted.dao from the list. To choose the interface that the class implements, use Add… next to the Interfaces field. Type DAO in the Choose Interfaces field. Select the DAO interface from the temp.persistence.gettingstarted.dao package and confirm the selection by choosing OK.

       5.      To create the file, choose Finish.

       6.      The SQLJ file opens automatically. Modify the generated code as follows:

                            a.      After the package declaration, add an import declaration for java.util.ArrayList, which you will use to order the employee data obtained from the database by a select query:

import java.util.ArrayList;

                            b.      Add a constructor for the class and declare the connection context ctx variable:

public SqljDAO() throws SQLException {       ctx = new Ctx();       if (ctx == null) {          throw new SQLException("ctx == null");       }       ctx.getConnection().setAutoCommit(false);    }       Ctx ctx;

 

                            c.      Implement the createDepartment() method of the DAO interface. It should insert data records about the created departments in the relevant columns of the TMP_DEPARTMENT table:

public void createDepartment(int depId, String depName)       throws SQLException {         #sql [ctx] { insert into TMP_DEPARTMENT (DEPID, NAME)                  values (:depId, :depName) }; }

 

                            d.      Implement the createEmployee() method of the DAO interface. It should insert data records in the relevant columns of the TMP_EMPLOYEE table:

public void createEmployee(EmployeeData employee) throws SQLException {       #sql [ctx] { insert into TMP_EMPLOYEE (EMPID, FIRST_NAME, LAST_NAME, SALARY, DEPID)                   values (:(employee.getEmpId()),                           :(employee.getFirstName()),                           :(employee.getLastName()),                           :(employee.getSalary()),                           :(employee.getDepId())) }; }

 

                            e.      Implement the getEmployeesFromDepartment() method. As its name implies, the method should select from the database tables the records for the employees in a particular department. The records are ordered in an ArrayList, which is then cast to an array of EmployeeData objects.

public EmployeeData[] getEmployeesFromDepartment(int depId) throws SQLException {       EmployeeIter iter = null;       ArrayList list = new ArrayList();             #sql [ctx] iter = { select EMPID, FIRST_NAME, LAST_NAME, SALARY                          from TMP_EMPLOYEE                          where DEPID = :depId };       while (iter.next()) {          EmployeeData data = new EmployeeData(iter.EMPID(),                                               iter.FIRST_NAME(),                                               iter.LAST_NAME(),                                               iter.SALARY(),                                               depId);          list.add(data);       }       return (EmployeeData[])list.toArray(new EmployeeData[] {}); }

 

                              f.      Implement the methods for finishing the work and closing the connection:

// commits the work public void commit() throws SQLException {       #sql [ctx] { commit work }; }   // rolls back the work public void rollback() throws SQLException {       #sql [ctx] { rollback work }; }   // closes the connection public void close() throws SQLException {       if (ctx != null)           ctx.close(); }

 

       7.      Save and close the file.

Result

Now you have an SQLJ-based implementation of the data access interface. Go on with implementing the interface using JDBC.