About this series

PHP is a top Web development language, and the Java™ programming language is popular for business application development. Thus, to utilize the strengths of each language on the AIX® Version 5.3 operating system, the PHP Java Bridge has been developed. The goal of this series is to get AIX 5.3 developers up to speed in integrating both PHP and Java technology in Web application development.

To demonstrate this, you will build a simple survey application that follows a typical development process, including:

• Developing the main Java application
• Exposing the Java application through servlets as a Java-based Web application
• Adding support for storing information within a database
• Exposing the original application as Web service and providing a PHP interface to the application
• Redeveloping the PHP interface using the dedicated PHP Java Bridge

The series is split into six parts:

• Part 1 looks at the application and sets up an environment ready for building Java applications and serving Java-based Web applications using Tomcat.
• Part 2 covers the main application code and the development of a simple Java Servlet to provide a Web interface to the information.
• Part 3 connects the core application to a DB2® database for the storage of the survey questions and responses.
• Part 4 converts the original application into one that can be accessed as a Web service, and it provides the base for the PHP interface.
• Part 5 builds the PHP interface to the Java application by using the PHP Java Bridge.
• Part 6 redevelops the application to make use of the PHP Java Bridge in place of the Web service interface.

Introduction

This article integrates the Java business application created in Part 2 with a database, DB2 Version 9. Data is submitted to the Java business application, and you can use the data that is stored to provide statistical information about the responses.

This article covers:

• Use of the database in the survey application
• Installing DB2
• The database structure
• Creating the database tables
• Inserting data into the tables
• Reporting from the stored data

Database roles

The role of a database in an application like this works on different levels. From the point of data capture and storage, it provides a simple way for your application to store data. There are many possible solutions for storing information, from using basic text files up to a full blown relational solution like the IBM DB2 database server.

Text files are impractical for storing data in a Web-based application like this because of the high-level concurrency potential. With a professional database solution, the problems of locking and corruption are resolved by the database engine.

More importantly, from the perspective of getting the information back out of the database, you can use the database engine to perform queries, collation, and calculations, saving you the need of performing the aggregation and statistical analysis that would require you to exchange lots of data with the database. Instead, you get the database engine to handle the process and then report back the summary rather than the raw data.

You need to use the database for storing the raw data from the survey form and as a method of reporting the information for your application.

Installing DB2 Version 9 on AIX

Even though DB2 Version 8 is available on the AIX distribution media, we decided to use DB2 Version 9 just in case you later decide to take advantage of the DB2 Version 9 PureXML capabilities. A 90-day evaluation of DB2 Version 9 enterprise server is available from IBM. The evaluation and system requirements and links to installation documentation are all available from the Resources.

To install DB2 Version 9 on AIX, you need a 64-bit kernel, Technology Level 5300-04, Service Pack 5300-04-02, and the C++ runtime level xlC.rte 8.0.0.4.

Looking at your server, the AIX 5L™ pSeries® box, use the oslevel -s command to discover that you are at technology level 5300.0.3, which means you need to upgrade it. See Resources for a link to AIX updates.

For the C++ runtime, lslpp is the command to show you which revision is currently installed on your system. So, using lslpp -L xlC.rte shows your server at 6.0.0.0, which means you need to upgrade it as well. See Resources for a link to Fix Central for an update.

If you haven't installed one already, now would be a good time to install a Web browser. A version of Mozilla for AIX can be downloaded from IBM (see Resources).

Installation of DB2 Version 9 is straightforward. You will want an X-windows interface to run the GUI setup.

1. Download the software and get it to your AIX system. Registration is required, but it is free of charge. The file name is db2_v9_ese_aix.tar.gz.
2. As the root user, unpack the gzip tar image and install (see Listing 1).
   
   
   Listing 1. Unpacking and installing DB2
   

   # gunzip -c db2_v9_ese_aix.tar.gz | tar -xvf - # cd ese_t/disk1 # ./db2setup

   
   
   From the Launchpad page, you can click on the links to the left to view the DB2 documentation (requires a Web browser).
3. To proceed with the installation, click on the Install a Product item.
4. Click on Install New to enter the DB2 Setup Wizard. The Wizard guides you through the remainder of the installation.

Here are a couple of things to be aware of during the installation process:

• The default installation directory for DB2 is /opt/IBM/db2/V9.1. The installation requires at least 474MB. I did not have that much space in /opt on my server for this, so I opted to create a new file system, /DB2, for this exercise. I made the installation directory /DB2/V9.1.
• The Wizard creates the database administration logins on the system. By default, the database you create will be under these instance owner user's home directory. I chose to also put these home directories in /DB2, so the home directory path was /DB2/home.

Continue through the Wizard and the DB2 Enterprise Server will be installed on your system.

Test the installation by installing the sample database. To do so, log in as "db2inst1" and do the following (commands are in bold):

1. Start the database manager with the db2start command (see Listing 2).
   
   
   Listing 2. Running the db2stsart command
   

   $ db2start 06/09/2007 12:46:46 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful.

   
   
2. Create the sample database with the db2sampl command (see Listing 3).
   
   
   Listing 3. Creating the sample database
   

   $ db2sampl Creating database "SAMPLE"... Connecting to database "SAMPLE"... Creating tables and data in schema "DB2INST1"... 'db2sampl' processing complete.

   
   
3. Verify that the database was created by connecting to the database and performing a query (see Listing 4).
   
   
   Listing 4. Verifying that the database was created
   

   $ db2 (c) Copyright IBM Corporation 1993,2002 Command Line Processor for DB2 ADCL 9.1.0 db2 => connect to sample Database Connection Information Database server = DB2/AIX64 9.1.0 SQL authorization ID = DB2INST1 Local database alias = SAMPLE db2 => select * from staff where dept = 20 ID NAME DEPT JOB YEARS SALARY COMM ------ --------- ------ ----- ------ --------- --------- 10 Sanders 20 Mgr 7 98357.50 - 20 Pernal 20 Sales 8 78171.25 612.45 80 James 20 Clerk - 43504.60 128.20 190 Sneider 20 Clerk 8 34252.75 126.50 4 record(s) selected. db2 => quit DB20000I The QUIT command completed successfully. $

   
   

With DB2 now installed, you can set about using it in conjunction with the application.

Connecting to a DB2 database with the Java business application

To connect to a database within the Java business application, the best solution is to use the Java Database Connectivity (JDBC) interface. JDBC is a database interface, independent to any database system for a which a suitable JDBC compliant driver exists, and it's a practical solution for developing an application where the eventual target database platform is unknown.

For your purposes, it's the most convenient method of connecting to your DB2 installation. You can see a basic structure for connecting to a DB2 database using JDBC and the Java business application in Listing 5.

                
import java.sql.*;

public class PHPJavaAddData {
  public static void main(String[] args) {
    Connection conn = null;

    try {
      Class.forName("com.ibm.db2.jcc.DB2Driver");
      conn =
   DriverManager.getConnection("jdbc:db2://localhost:50000/SURVEY","db2inst","s
urveypw");
            System.out.println("Yay, connected");
    } catch (Exception ex) {
      System.out.println("SQLException: " + ex.getMessage());
    }
  }
}
      

The connection URL, the string supplied to the getConnection() method, defines the database (DB2), the hostname holding the database server, the database name, and the username and password to use when connecting.

To compile and run these JDBC examples, use the instance owner login "db2inst1" and set the password to "surveypw." You need to modify this user's environment to include a path to the java5 binaries and to set the DB2 JDBC jars as the class path. You also need the main JDBC jar and the license that goes with it, which are accessible within the Java directory. To do this, log in as "db2inst1" and issue the following commands:

$ PATH=/usr/java5_64/bin:$PATH
$ export PATH
$ CLASSPATH=/DB2/V9.1/java/db2jcc.jar:/DB2/V9.1/java/db2jcc_license_cu.jar:.
$ export CLASSPATH

You might wish to make these changes to db2inst1's .profile file so that these changes are not lost when you log out.

Then, you need to create the "SURVEY" database:

$ db2 create database SURVEY

For each of the examples, compile and execute the listed code, as follows (example file name "PHPJavaAddData.java" for the listing above):

$ javac PHPJavaAddData.java
$ java PHPJavaAddData

Before you start to add data, you need to create the database structure.

Creating the database structure

The database structure needs to hold the survey results and then enable you to query and report on the data that has been stored. It also needs to be stored in such a way that you can produce useful statistics on the output.

Depending on the complexity of your survey, the way that you structure the database can vary a lot. For a single, simple, survey, you could probably place all of the information from each question into a field in a single table. This would require the survey and the table to be fixed, or least to be in synchronization, because a minor change to the survey (which is coded in the Java business application through your classes) would require a change to the fields in the table for that survey.

This means you need to create a more flexible structure that can accept more flexible input (according to the format of the survey), which means creating a single table to hold the results, where each row in that table contains the information for just one question.

To identify all the responses (from each question) for a single survey (and for example, a single user), you also need a way of identifying a single group of responses and the individual survey question responses. That should enable you to pull out information from the database both in terms of an entire survey from a single user and to collate the responses from multiple surveys.

You can see a basic database structure here in Figure 1.

The first table, survey_response, is used to identify the responses to a single survey, and a unique ID (using the serial data type) is used to identify each survey and the questions that were answered. In the example structure, there's only one field defined, the unique ID, that will be auto generated by the database. You'll use that unique ID to identify the groups of actual responses in the survey_response_detail table. You cannot use an autoincrement field within survey_response_detail because it needs to be unique for the group of question responses. Since you are storing each response in each row, you get a new ID for each question, not for each survey, which is not what want.

Obviously, in its current format with only one field, the table looks pretty useless, but you could store additional information in this table, such as the email address of the respondent or other identifying information for the individual survey.

The Java class in Listing 6, CreateDB, creates the tables you need.

                
import java.sql.*;

public class CreateDB {
    public static void main(String[] args) {
        Connection conn = null;

        try {
            Class.forName("com.ibm.db2.jcc.DB2Driver");
            conn =
                DriverManager.getConnection("jdbc:db2://localhost:50000/SURV
EY",
                                            "db2inst1","surveypw");

            Statement s = conn.createStatement ();

            s.executeUpdate ("CREATE TABLE survey_response (" +
                             "responseid int GENERATED ALWAYS AS IDENTITY
PRIMARY KEY" +
                             ")");

            s.executeUpdate ("CREATE TABLE survey_response_detail (" +
                             "responseid int, " +
                             "question int, " +
                             "subquestion int, " +
                             "responsenumeric int," +
                             "responsestring varchar(40)" +
                             ")");

            s.close ();

        } catch (Exception ex) {
            System.out.println("SQLException: " + ex.getMessage());
        }
    }
}

Now let's look at how the information is stored and organized within the database, and how you can update the information when the survey response is received.

Adding results to the database

To add the results to the database, you need to get a unique ID for the survey that was completed, which you'll use autoincrement within the survey_results table. The process of adding the information to the table is a case of running a suitable INSERT statement.

Within your Web application, you need to perform these three steps:

1. Open the connection to the database during the init() function in your servlet.
2. Get a number response ID by inserting an 'empty' row into the survey_response table, obtaining the auto-generated value.
3. For each question in the survey, insert a row of data into the database.

The first step is easy, you add a connection parameter to the servlet class you are creating, and then use the connection example, as shown above, to open the connection, as shown in Listing 7 below.

                
public void init(javax.servlet.ServletConfig config) {
    this.survey.add(new SurveyQuestionText("Name",
                                 "Enter your full name"));
    this.survey.add(new SurveyQuestionRadio("Favourite colour",
                                 "Enter your favourite colour",
                         new String[] {"Red", "Blue", "Green"}));

    try {
        Class.forName("com.ibm.db2.jcc.DB2Driver");
        conn =
 DriverManager.getConnection("jdbc:db2://localhost:50000/SURVEY","db2inst","s
urveypw");
    } catch (Exception ex) {
        System.out.println("SQLException: " + ex.getMessage());
    }
}

To get the unique response ID, you need to insert a row into the table to trigger the generation of a new ID that you can use. The ID is created automatically by the database engine and increments automatically, guaranteeing a unique ID. When you submit the statement, you must add the RETURN_GENERATED_KEYS option, which returns the automatically generated value.

You can then use the getGeneratedKeys() method to accept the automatic ID. This returns a resultset object, and you need the first value returned by this process to get the new unique ID. Listing 8 shows this process in code.

                
try {
    s = conn.createStatement();
    s.executeUpdate(
                    "INSERT INTO survey_response (responseid) "
                    + "values (0)",
                    Statement.RETURN_GENERATED_KEYS);

    rs = s.getGeneratedKeys();

    if (rs.next()) {
        responseid = rs.getInt(1);
    } else {
        System.out.println("Can't get auto incremement data");
        out.println("Sorry, we couldn't write your responses into the DB");
    }

    rs.close();
    s.close ();
} catch (Exception ex) {
    System.out.println("SQLException (getting responseid): " + 
                       ex.getMessage());
}

Finally, you have to insert the information by formulating a suitable INSERT statement and writing the data into the database. This must be done for each survey question, so you can do this in the same loop that you have previously used for outputting the survey results as part of the servlet (see Listing 9).

                
for(Iterator<SurveyQuestion> i = this.survey.iterator(); i.hasNext(); ) {
    SurveyQuestion question = (SurveyQuestion) i.next();
    question.showquestion(out,false);
    out.println("<p>" + request.getParameter("field" + fieldid));

    try {
        ps = conn.prepareStatement (
           "INSERT INTO survey_response_detail " + 
           "(responseid, question, responsestring) VALUES(?,?,?)");
        ps.setString (1,responseid.toString());
        ps.setString (2,fieldid.toString());
        ps.setString (3,request.getParameter("field" + fieldid));
        int count = ps.executeUpdate ();
        ps.close ();
    } catch (Exception ex) {
        System.out.println("SQLException (adding question result): " + 
              ex.getMessage());
    }
    fieldid++;
}

We've used a prepared statement in the above to make the process of filling in the data much easier within the query. We could have used a prepared statement and just reset the data each time, but we decided to recreate it for each iteration of the loop here for completeness.

Generating statistics from the database of results

Although not a critical part of the current application, it's worth examining how the information can be recovered to see whether the database structure is correct.

You can use the database to provide statistics about the responses that you have received by writing a suitable SQL query that extracts the information. For example, you can get a count out of the database for the number of respondents who have stated that their favorite color (question 2) is red by using the following query shown in Listing 10.

                
SELECT count(responsestring) FROM survey_response_detail
    WHERE question = 2 AND
          responsestring = 'red'

This provides a single result, the count of the number of occurrences of the 'red' response in question two across all the respondents.

Or you could get a summary of all the different colors by using the GROUP BY clause. This collates the results by the unique elements of the specified field. Thus, you can rewrite a query that returns a count of each item within a specific field, grouped by the unique values in that field, in this case, your response string. The query in Listing 11 returns a count for each unique value of responsestring for question 2.

                
SELECT count(responseid),responsestring FROM survey_response_detail     
    WHERE question = 2                     
    GROUP BY responsestring

Now you get a multi-row result set, each row relating to a unique value (red, blue, and green), and a corresponding count of the occurrences of that value in the table.

Using the database to store questions

Since you are using a database to store the results, there is no reason why you couldn't also use the database to store the survey questions. The full complexity of a survey system is difficult to explain in such a short space, but you can imagine a simple structure with a table for the survey questions, the question number and the question type, and another for the possible question options when working with a radio button or checkbox question type. You can see this basic layout here in Figure 2.

Generating the survey question within the init() block of the servlet can now be achieved by running a SELECT query on the database and creating the objects necessary to execute the rest of the application.

Summary

In this article, you've seen how to extend the functionality of the survey application so that you can store the information in the database. First, you learned how to install the DB2 database engine before examining what it was you wanted to store and the database structure that would be required to store it.

In the second half, you then extended the original WebSurvey class servlet with the information you needed to be able to write the information into the database. You've also taken a quick look at how you can use the information that will be stored in the database to get statistical information—surely the point of a survey application.

Back to top

Download

Information about download methods

Resources

Learn

• Develop with Java and PHP technology on AIX Version 5.3: Check out other parts in this series.
  
  
• IBM developerWorks Java zone: More information on Java development can be located here.
  
  
• "Introduction to Java Servlet technology" (Roy Miller, developerWorks, December 2004): Read this article for an introduction to servlet technology.
  
  
• "Run PHP applications in Apache Geronimo" (Tyler Anderson, developerWorks, February 2006): This tutorial provides basic information on developing using PHP and Java technology.
  
  
• PHP Java Bridge: For more information on PHP Java Bridge, be sure to check out sourceforge.net.
  
  
• DB2 Version 9 system requirements and links: Get all the information you need to install DB2 Version 9.
  
  
• Popular content: See what AIX and UNIX® content your peers find interesting.
  
  
• Check out other articles and tutorials written by Martin Brown:
  • Across developerWorks and IBM
  
  
• Check out other articles and tutorials written by Doug Monroe:
  • Across developerWorks and IBM
  
  
• AIX and UNIX® : The AIX and UNIX developerWorks zone provides a wealth of information relating to all aspects of AIX systems administration and expanding your UNIX skills.
  
  
• New to AIX and UNIX?: Visit the "New to AIX and UNIX" page to learn more about AIX and UNIX.
  
  
• AIX 5L Wiki: A collaborative environment for technical information related to AIX.
  
  
• Search the AIX and UNIX library by topic:
  • System administration
  • Application development
  • Performance
  • Porting
  • Security
  • Tips
  • Tools and utilities
  • Java technology
  • Linux®
  • Open source
  
  
• Safari bookstore: Visit this e-reference library to find specific technical resources.
  
  
• developerWorks technical events and webcasts: Stay current with developerWorks technical events and webcasts.
  
  
• Podcasts: Tune in and catch up with IBM technical experts.
  
  

Get products and technologies

• IBM trial software: Build your next development project with software for download directly from developerWorks.
  
  
• A 90-day evaluation: Get a evaluation copy of DB2 Version 9 enterprise server from IBM.
  
  
• Quick links for AIX fixes: Get your AIX updates here.
  
  
• IBM Fix Central: This site provides fixes and updates for your system's software, hardware, and operating system.
  
  
• Eclipse Web site: You can obtain the Eclipse IDE from here.
  
  
• Java 5 64-bit SDK: You need to register to download this package, but registration is free.
  
  
• Tomcat: Download the latest package.
  
  
• Mozilla: The Mozilla Web browser for AIX can be downloaded from IBM.
  
  

Discuss

• Participate in the developerWorks blogs and get involved in the developerWorks community.
  
  
• Participate in the AIX and UNIX forums:
  • AIX 5L—technical forum
  • AIX for Developers Forum
  • Cluster Systems Management
  • IBM Support Assistant
  • Performance Tools—technical
  • Virtualization—technical
  • More AIX and UNIX forums
  
  

About the authors

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• About this series
• Introduction
• Database roles
• Installing DB2 Version 9 on AIX
• Connecting to a DB2 database with the Java business application
• Creating the database structure
• Adding results to the database
• Generating statistics from the database of results
• Using the database to store questions
• Summary
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into AIX and UNIX on developerWorks

Calling all developers

Download no charge Lotus Domino Designer

Special offers