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

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

Run the DB2 SQLJ translator to translate JDBC applications to SQJL applications that can be deployed in an appserver.

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 WAS.
Follow these steps precisely, and in the right order, to ensure a correct conversion.

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

Rename your .java file 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.
Edit the .sqlj file to convert the JDBC syntax to SQLJ syntax. When using SQLJ in WAS, 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();
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.

Package your EJB jar and deploy it in the usual manner. After deployment, one serialized profile exists for each EJB .jar file. (You can find the profile in the EJB .jar file; one example of a serialized profile file name is MyBMPBeanProfile.ser.)
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, you can 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. You 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
To enable context caching for an application or BMP bean that caches connections across transaction boundaries, you 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.
Configure your database.

Update your EJB jar with the .ser file.

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

Install the application in the usual manner.
Embedded Structured Query language in Java (SQLJ) support
Exceptions pertaining to data access

Related tasks

Applying access intent policies to beans
Developing data access applications

Related Reference

Data access bean types