Skip to main content

Sun Java System Application Server 8.0 and MySQL

Posted by jonbruce on August 6, 2004 at 12:14 AM PDT

The following walks you through how to get the Blue Prints application, Adventure Builder to
work with the MySQL database. For more information, check out the Adventure Builder Java.net Community

JavaTM 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)

e. Commit the changes you made to the user table

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 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

This example uses the Connector/J 3.1 Developer Release

Put the driver jar file in the application server classpath.

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.

Related Topics >>