Access data from application clients

To access a database directly from a Java EE application client, you retrieve a javax.sql.DataSource object from a resource reference configured in the client deployment descriptor. This resource reference is configured as part of the deployment descriptor for the client application, and provides a reference to a pre-configured data source object.

that data access from an application client uses the JDBC driver connection functionality directly from the client side. It does not take advantage of the additional pooling support available in the application server run time. For this reason, the client application should utilize an enterprise bean running on the server side to perform data access. This enterprise bean can then take advantage of the connection reuse and additional added functionality provided by the product run time.

Tasks

1. Import the appropriate JDBC API and naming packages:

   import java.sql.*;
   import javax.sql.*;
   import javax.naming.*;
   

2. Create the initial naming context:

   InitialContext ctx = new InitialContext();
   

3. Use the InitialContext object to look up a data source object from a resource reference.

   javax.sql.DataSource ds = (DataSource)ctx.lookup("java:comp/env/jdbc/myDS");  
   //where jdbc/myDS is the name of the resource reference
   

4. Get a java.sql.Connection from the data source.

   • If no user ID and password are required for the connection, or if we are going to use the defaultUser and defaultPassword specified when the data source is created in the Application Client Resource Configuration tool (ACRCT) in a future step, use this approach:

     java.sql.Connection conn = ds.getConnection();
     

   • Otherwise, we should make the connection with a specific user ID and password:

     java.sql.Connection conn = ds.getConnection("user", "password"); 
     //where user and password are the user id and password for the connection
     

5. Run a database query using the java.sql.Statement, java.sql.PreparedStatement, or java.sql.CallableStatement interfaces as appropriate.

   Statement stmt = conn.createStatement();
   String query = "Select FirstNme from " + owner.toUpperCase() + ".Employee where LASTNAME = '" + searchName + "'";
   ResultSet rs = stmt.executeQuery(query);
   while (rs.next()) { firstNameList.addElement(rs.getString(1));
   }
   

6. Close the database objects used in the previous step, including any ResultSet, Statement, PreparedStatement, or CallableStatement objects.
7. Close the connection. Ideally, we should close the connection in a finally block of the try...catch statement wrapped around the database operation. This action ensures that the connection gets closed, even in the case of an exception.

   conn.close(); 
   

Related:

Data sources

Configure data access for the Application Client

Create or change a resource reference