Use Structured Query Language in Java for bean-managed persistence entity beans, session beans, and servlets

 

Overview

This article describes how JDBC applications, comprised of bean-managed persistence (BMP) entity beans, session beans, or servlets, are converted to SQLJ applications. It also describes how the SQLJ applications are then deployed in WebSphere Application Server.

Follow these steps precisely and in the right order to ensure a correct conversion:

 

Procedure

1. Create a backup copy of your .java file. For example if your file is called MyServlet.java, copy MyServlet.java to MyServlet.java.bkup.

2. Rename your .java file to to a file name with a .sqlj extension. For example, if your application is a servlet named MyServlet.java, rename MyServlet.java to MyServlet.sqlj.

   Now when you run the sqlj tool in the next step, the .java file that it creates will have the same name as your old .java file, providing you with a seamless transition to the SQLJ technology.

3. Edit the .sqlj file to convert the JDBC syntax to SQLJ syntax. When using SQLJ in WebSphere Application Server, if you want WAS connection management to function properly, specify correct connection contexts.

   For example, convert the following JDBC operation:

               Connection con = dataSource.getConnection();
               Statement stmt = con.createStatement();
               stmt.execute("INSERT INTO users VALUES (1, 'user1')");
               con.commit();
   
   

   to the following SQLJ

                // At the top of the file and just below the import statements, define Connection_Context
                #sql context Connection_context;
                 .
                 .
                Connection con = dataSource.getConnection();
                 .
                 .
                Connection_context ctx1 = new Connection_context(con);
                 .
                 .
                #sql [ctx1] {INSERT INTO users VALUES (1, 'user1')};
                 .
                 . 
                con.commit();
                ctx1.close();
   
   

4. Run the DB2 SQLJ translator. This tool creates a .java version of your .sqlj file, as well as a .ser profile that is used later in the processing. Refer to the DB2 documentation for more information on the SQLJ translator tool.

5. Package your EJB jar and deploy it in the usual manner. After deployment, one serialized profile exists for each EJB .jar file. (We can find the profile in the EJB .jar file; one example of a serialized profile file name is MyBMPBeanProfile.ser.)

6. Run the db2sqljcustomize tool to customize the .ser files that correspond to each EJB .jar file. When you run the DB2 SQLJ customizer against the serialized profiles, you create static SQL in the database, which is used at runtime. The customization phase creates four database packages that contain static SQL, one for each isolation level.

   Optionally, use the SQLJ customizer tool to enable context caching for your application's data source connections, thereby improving application performance.

   • DB2 V8.1 fix pack 6 provides the new caching option, called db2optimize , with the db2sqljcustomize tool. We can run this option if your application uses the explicit connection context instead of the default context.

   • The following example code demonstrates proper syntax for running the option

     sqlj -db2optimize SQLJTransactionTest.sqlj
     db2sqljcustomize -url jdbc:db2://localhost:50000/jtest -user dbuser1 -password dbpwd1
     SQLJTransactionTest_SJProfile0.ser
     

   • If you want to enable context caching for an application or BMP bean that caches connections across transaction boundaries, one cannot use shareable connections. You must use the get/use/close pattern of connection usage when you invoke the db2optimize option. Otherwise, an object closed exception occurs. The following code gives an example of incorrect connection usage for context caching

      utx.begin(); 
          cons =ds.getConnection( 
          request.getParameter("db.user"), 
          request.getParameter("db.password"));         
              cmctx1 = new CM_context(cons);                       
              #sql [cmctx1] {DELETE FROM cmtest WHERE id=1};     
      utx.commit(); 
            //The next statement verifies the result: 
              #sql [cmctx1] cursor1 = {SELECT id, name FROM cmtest WHERE id=1}; 
     

     In this case, the Select statement elicits an object closed exception. To prevent the exception from occurring, close the connection before committing the transaction. Then get a new connection and a new context before running the Select statement.

7. Configure your database.

8. Update your EJB jar with the .ser file.

9. Package your EJB jar and servlets, along with .ser files, into an .ear file.

10. Install the application in the usual manner.

---

 

See Also

Embedded Structured Query language in Java (SQLJ) support
Tips for developing entity beans
Exceptions pertaining to data access

 

Related Tasks

Developing data access applications

 

See Also

Data access bean types

 

---