Implementing the Data Access Interface with JDBC 

Use

Once you have implemented the temp.persistence.gettingstarted.dao.DAO interface using SQLJ, you also need to provide a JDBC-based implementation to enable your application to access the database using JDBC.

The JDBC-based implementation 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.

You must have developed the DAO interface.

Procedure

...

       1.      Select GettingStartedOpenSQLWeb project and open its context menu. Choose Properties.

       2.      Choose Java Build Path. In the Source tab choose Add Folder….

       3.      Expand GettingStartedOpenSQLWeb and select gen_sqlj. Confirm your choice using OK.

       4.      In the Java perspective, select GettingStartedOpenSQLWeb and open its context menu.

       5.      Choose New → Class. Enter JdbcDAO 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.

       6.      To create the file, choose Finish.

       7.      The Java file opens automatically after being created. Modify the code of the class as follows.

 

                            a.      Add a constructor of the class in which you must obtain a connection to the database using a DataSource. The connection is an instance of the java.sql.Connection interface. Include a declaration for the connection object as well.

public JdbcDAO() throws SQLException {             // obtain a database connection using a DataSource       try {          InitialContext ctx = new InitialContext();          DataSource dataSource =             (DataSource) ctx.lookup("java:comp/env/TMP_PERSISTENCE_EXAMPLE");          conn = dataSource.getConnection();          if (conn == null) {             throw new SQLException("conn == null");          }          conn.setAutoCommit(false);       } catch (NamingException ex) {          throw new SQLException("NamingException: " + ex.getMessage());       }    }   Connection conn;

 

                            b.      Implement the createDepartment() method of the DAO interface. Using a PreparedStatement, it should insert the department data records into the relevant columns of the TMP_DEPARTMENT table.

public void createDepartment(int depId, String depName)    throws SQLException {    PreparedStatement stmt =       conn.prepareStatement(          "insert into TMP_DEPARTMENT (DEPID, NAME) values (?, ?)");    try {       stmt.setInt(1, depId);       stmt.setString(2, depName);       stmt.executeUpdate();    } finally {       stmt.close();    } }

 

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

public void createEmployee(EmployeeData employee) throws SQLException {    PreparedStatement stmt =       conn.prepareStatement(          "insert into TMP_EMPLOYEE (EMPID, FIRST_NAME, LAST_NAME, SALARY, DEPID) "             + "values (?, ?, ?, ?, ?)");    try {       stmt.setInt(1, employee.getEmpId());       stmt.setString(2, employee.getFirstName());       stmt.setString(3, employee.getLastName());       stmt.setBigDecimal(4, employee.getSalary());       stmt.setInt(5, employee.getDepId());       stmt.executeUpdate();    } finally {       stmt.close();    } }

 

                            d.      Implement the getEmployeesFromDepartment() method of the DAO interface. Using a ResultSet, it should select the employees in a particular department. The records are added in an ArrayList, which is then cast to an array of EmployeeData objects.

public EmployeeData[] getEmployeesFromDepartment(int depId)    throws SQLException {       ArrayList list = new ArrayList();       PreparedStatement stmt = conn.prepareStatement(          "select EMPID, FIRST_NAME, LAST_NAME, SALARY "             + "from TMP_EMPLOYEE "             + "where DEPID = ?");       try {          stmt.setInt(1, depId);          ResultSet rs = stmt.executeQuery();          try {             while (rs.next()) {                EmployeeData data =                   new EmployeeData(                      rs.getInt(1),                      rs.getString(2),                      rs.getString(3),                      rs.getBigDecimal(4),                      depId);                list.add(data);             }          } finally {             rs.close();          }          return (EmployeeData[]) list.toArray(new EmployeeData[]  {          });       } finally {       stmt.close();    } }

 

                            e.      Implement the methods for finishing the work and closing the connection.

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

 

                              f.      To add the required imports, position the cursor anywhere in the Java editor and open the context menu. Choose Source → Organize Imports. Select javax.sql.DataSource and then choose Next.

                            g.      Select java.sql.Connection and confirm by choosing Finish. The following import declarations are added to the existing import of java.sql.SQLException:

import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList;   import javax.naming.InitialContext; import javax.naming.NamingException; import javax.sql.DataSource;

 

       8.      Save and close the file.

 

Result

Using the implementations of the DAO interface, you can now access the database via both SQLJ and JDBC. The next step is to create the presentation layer for the application.