Deploy an existing JDBC application to the Liberty profile

We can take an existing application that uses Java Database Connectivity (JDBC) and a data source, and deploy the application to a server.

We can take an existing JDBC application and deploy it to the Liberty profile. To complete this deployment, we add the jdbc-4.0 Liberty feature to server.xml. Also add code that tells the server the JDBC driver location and specifies properties that the JDBC driver uses to connect to the database.

In this example, we can extend the servlet application, or use the one provided here to test the interactivity used through the JDBC driver is working as expected.

1. Create a server.

2. Start the server.

3. Add the jdbc-4.0 and the servlet-3.0 Liberty features to server.xml.

   <server>
      <featureManager>
          <feature>jdbc-4.0</feature>
          <feature>servlet-3.0</feature>
      </featureManager>
   </server>

   To check that the server is working and that the features are enabled successfully, see the console.log file, which is stored in the logs directory of the server. We can view it using any text editor. We should see something like this example:

   [AUDIT   ] CWWKF0012I: The server installed the following features: [jdbc-4.0, jndi-1.0].
   [AUDIT   ] CWWKF0008I: Feature update completed in 0.326 seconds.

4. Specify the database type and the data source location in server.xml.

   Where path_to_derby is the location where derby is installed on the operating system, lib is the folder where derby.jar is located, and data/exampleDB is the directory that is created if it does not exist.

   For example:

   <jdbcDriver id="DerbyEmbedded" libraryRef="DerbyLib"/>
   
   <library id="DerbyLib">
     <fileset dir="C:/path_to_derby/lib" includes="derby.jar"/>
   </library>
   
   <dataSource id="ds1" jndiName="jdbc/exampleDS" jdbcDriverRef="DerbyEmbedded">
     <properties.derby.embedded
      databaseName="C:/path_to_derby/data/exampleDB"
      createDatabase="create"
     />
   </dataSource>

   For information about other options for coding data source definitions, see Use Ref tags in configuration files.

5. Add some SQL create, read, update, and delete statements to the JDBC application to test the interactivity with the database.

   package wasdev;
   
   import java.io.*;
   import java.sql.Connection;
   import java.sql.ResultSet;
   import java.sql.SQLException;
   import java.sql.Statement;
   import javax.annotation.Resource;
   import javax.servlet.*;
   import javax.servlet.http.*;
   import javax.servlet.annotation.WebServlet;
   import javax.sql.DataSource;
   
   @WebServlet("/HelloWorld")
   public class HelloWorld extends HttpServlet {
     
     @Resource(name = "jdbc/exampleDS")
     private DataSource ds1;
     private Connection con = null;
     private static final long serialVersionUID = 1L;
     
     public HelloWorld() {
       super();
     }
     public void doGet(HttpServletRequest request, HttpServletResponse response) 
                                          throws ServletException, IOException {
       response.setContentType("text/html");
       PrintWriter out = response.getWriter();
       out.println("<H1>Hello World Liberty Profile</H1>\n");
       try {
          con = ds1.getConnection();
          Statement stmt = null;
          stmt = con.createStatement();
          // create a table
          stmt.executeUpdate("create table cities (name varchar(50) not null primary key, population int, county varchar(30))");
          // insert a test record        stmt.executeUpdate("insert into cities values ('myHomeCity', 106769, 'myHomeCounty')");
          // select a record        ResultSet result = stmt.executeQuery("select county from cities where name='myHomeCity'"); would result.next();
          // display the county information for the city.
          out.println("The county for myHomeCity is " + result.getString(1));
          // drop the table to clean up and to be able to rerun the test.
          stmt.executeUpdate("drop table cities");
          } 
       catch (SQLException e) {
          e.printStackTrace();
          } 
       finally {
          if (con != null){
              try{
                 con.close();
                 }
              catch (SQLException e) {
                e.printStackTrace();
                } 
              }
          }
       }
   }

6. Compile the application.

   Where path_to_liberty is the location you installed Liberty on the operating system, and path_to_app is the location of the Java file of the application to compile.

   Example on Windows:

   C:\> javac -cp 
   path_to_liberty\wlp\dev\api\spec\com.ibm.ws.javaee.servlet.3.0_1.0.1.jar
          path_to_App\HelloWorld.java

   Example on Linux:

   mo@machine01:~> javac -cp  
            path_to_liberty/wlp/dev/api/spec/com.ibm.ws.javaee.servlet.3.0_1.0.1.jar
            path_to_App/HelloWorld.java

   If the javac command is not recognized, ensure that we have the Java bin directory in the PATH environment variable of the operating system.

7. Add the application to the server.

   In this example, the JDBC application is put in the dropins directory of the server:

   ...\dropins\HelloWorldApp.war\WEB-INF\classes\wasdev\HelloWorld

   The wasdev directory uses the same package name used in HelloWorld.java.

8. Check that the JDBC application is working.

   For this example, go to this URL:

   http://localhost:9080/HelloWorldApp/HelloWorld

   Port 9080 is the default HTTP port used by the Liberty server. We can check which HTTP port the server is set on by looking in server.xml.

   The output on the browser for this example looks like:

   Hello World Liberty Profile The county for myHomeCity is myHomeCounty

Parent topic: Deploy data access applications to the Liberty profile

Tasks:

Configure connection pooling for database connections