Jonathan Bruce's Blog

August 2004 Archives

Sun Java System Application Server 8.0 and MySQL

Java^TM Adventure Builder Reference Application - Version 1.0.1

Using MySQL

This document assumes that you have downloaded and installed the J2EE 1.4 Platform Edition. Download the J2EE 1.4 Platform Edition from http://java.sun.com/j2ee/1.4/download.html.

This document only explains how to setup the the Customer Web Site application to use MySql as the database. The back end Order Processing Center still uses Pointbase as the databases. There are known issues with MySql support CMP (due to XA driver support). The Adventure Builder Customer Web site does not require XA drivers and can therefore with be configured to use the MySql database.

1. Download MySQL

Download MySql from http://www.mysql.com. version 4.0 is used in this example.

2. Start MySql

This will differ by the platform you use. Please follow the instructions for the target platform.

3. Log into mysql command line mysql tool>

Unix:

/usr/local/mysql/bin/

On Windows:

C:\mysql\bin

From the command prompt type:

>mysql --user=root --password=yourrootpassword

a. Create the adventurebuilder database:

mysql> create database adenturebuilder;

You should see:

Query OK, 1 row affected (0.00 sec)

b. Create the adventurebuilder user:

mysql> grant all privileges on adventurebuilder.* to 'adventurebuilder'@'%' identified by 'adventurebuilder';

You should see:

Query OK, 0 rows affected (0.10 sec)

c. Commit the changes

mysql> flush privileges;

4. Test the adventurebuilder user:

mysql --user=adventurebuilder --password=adventurebuilder --database=adventurebuilder

If you see the following error:

ERROR 1045: Access denied for user: 'adventurebuilder@localhost' (Using password : YES)

Follow the following steps to fix this access error:

a. Log into mysql command line tool:

mysql --user=root --password=yourrootpassword

b. Connect to the users database:

mysql> connect mysql

You should see:

Connection id:    8
Current database: mysql

c. List the users:

mysql> select host, password from user;

Below is what may appear:

+-----------+------------------+
| host      | password         |
+-----------+------------------+
| localhost | 33e415d35e3cc0ff |
| build     |                  |
| localhost |                  |
| build     |                  |
| %         | 585a331f146c3b21 |
+-----------+------------------+
5 rows in set (0.00 sec)

d. Look for a user with a host of 'localhost' and blank password

This user is taking precedence on your localhost over the one you created. You can avoid the 10545 error if you delete this user. Make sure you do this only after you set your root password as described in step 1. Deleting this user from the table will top the access problems.

From the mysql prompt type:

mysql> delete from user where host = 'localhost' and password = '';

You should see:

Query OK, 1 row affected (0.05 sec)

From the mysql prompt type:

mysql>flush privileges;

You should now be able to log in as the user adventurebuilder.

5. Check that the tables got created

From the mysql prompt type:

mysql> connect adventurebuilder

You should see:

Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Connection id:    35
Current database: adventurebuilder

From the mysql prompt type:

mysql> show tables;

You should see:

+----------------------------+
| Tables_in_adventurebuilder |
+----------------------------+
| account                    |
| activity                   |
| activitylist               |
| category                   |
| lodging                    |
| package                    |
| signon                     |
| transportation             |
+----------------------------+
8 rows in set (0.00 sec)

6. Configure the J2EE server and populate the database

Note: If you have a custom setup of MySql or if you are deploying with different usernames or databases than those specified you will need to modify the setup-mysql.properties file located in the WORKSPACE_HOME/setup/mysql directory.

Run the ant script in this directory to create the necessary JDBC database pool as well as JDBC connection.

To do from the WORKSPACE_HOME/setup/mysql directory type:

>ant

Your J2EE environment should be ready for deployment.

7. Modify the Adventure Builder Demo Datasource

You will need to modify the body content <jndi-name> element in the sun-web.xml file which is located in the WORKSPACE_HOME/ws/apps/consumerwebsite/web/WEB-INF directory. The modified file should appear as follows:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE sun-web-app PUBLIC "-//Sun Microsystems, Inc.//DTD Application Server
8.0 Servlet 2.4//EN" "http://www.sun.com/software/appserver/dtds/sun-web-app_2_4-0.dtd">
<sun-web-app>
  <session-config>
     <session-manager/>
  </session-config>

  <resource-ref>

     <res-ref-name>jdbc/CatalogDB</res-ref-name>
     <jndi-name>jdbc/adventure/AdventureCatalogDB</jndi-name>
     <default-resource-principal>
       <name>adventurebuilder</name>

       <password>adventurebuilder</password>
     </default-resource-principal>
  </resource-ref>

  <service-ref>
    <service-ref-name>service/OpcPurchaseOrderService</service-ref-name>

    <port-info>
      <service-endpoint-interface>com.sun.j2ee.blueprints.consumerwebsite.actions.PurchaseOrderIntf</service-endpoint-interface>
    </port-info>
  </service-ref>

  <service-ref>

    <service-ref-name>service/OpcOrderTrackingService</service-ref-name>
    <port-info>
      <service-endpoint-interface>com.sun.j2ee.blueprints.consumerwebsite.actions.OrderTrackingIntf</service-endpoint-interface>
    </port-info>
  </service-ref>

</sun-web-app>

Note that the only thing that has changed is:

<jndi-name>jdbc/adventure/AdventureCatalogDB</jndi-name>

We will use the new data source created to provide the Adventure Builder Reference Demo catalog.

7. Download and Install the Connector/J JDBC Driver

Download the driver from:http://dev.mysql.com/downloads/connector/j/3.1/html

For more information see Integrating a JDBC Driver documentation for more details.

8.Build and Deploy Adventure Builder Reference demo applications

Build or Deploy the Adventure Builder application. If you have not deployed the Adventure Builder Reference demo applications you will need to deploy them if you do not want to see an error on the last screen. Adventure Builder requires support applications to do the order processing.

a.Build the applications

From the WORKSPACE_HOME/ws directory type:

>ant (or asant)

For more information about building the application see Building the Demo.

b. Deploy the applications

From the WORKSPACE_HOME/ws directory type:

ant deploy-apps (or asant deploy-apps)

9. View the Adventure Builder Reference demo

View http://localhost:8080/ab.

This work is licensed under a Creative Commons License.