Introduction

Many people have sent me an e-mail regarding the 'MySQL from Java' article, asking how to apply this knowledge in a J2EE environment.
Many people seem to have difficulties to get MySQL configured in WebLogic.

Because this information could be helpfull to many, I posted a little tutorial here.
The information provided is really basic, so if you are already familiar with this topic, you won't learn much new. If you are new to J2EE and/or MySQL and Weblogic, this information might save you a few hours of investigation and frustration.

The examples given are tested on WebLogic 6.1 and 7.0 on Mandrake Linux, using the JDBC Driver Connector/J 2.0.14 from MySQL.

A little bit of knowledge on WebLogic administration (starting, stopping, access to the admin password) is assumed.

We will be using standard J2EE ways to access the database, this means: creating a DataSource for the Database, so we are able to access it from JSP, Servlets and Enterprise Java Beans (EJBs) doing a simple JNDI lookup -- so, no DriverManager stuff, just plain Application Server configuration and JNDI!

STEP 1: Put the MySQL JDBC Driver on the classpath

Where you can download the MySQL JDBC Driver is explained in our MySQL from Java Article.

Before you can make a connection to MySQL from within the virtual machine in which your WebLogic runs, the JDBC Driver must be on the classpath.
You can do this by adding a line before the actual server startup in the startWebLogic.sh (unix) or startWebLogic.cmd (script) script.

Unix:
export CLASSPATH=/path/to/mysql-connector-java-2.0.14-bin.jar:$CLASSPATH

Windows:
set CLASSPATH=c:\path\to\mysql-connector-java-2.0.14-bin.jar;%CLASSPATH%

For WebLogic 6, add this right BEFORE the line that starts with:
$JAVA_CMD $JAVA_OPTIONS ...

In WebLogic 7, add this right BEFORE the line containing:
/path/of/your/bea/installation/server/bin/startWLS.sh

When you will start the Weblogic server again, the com.mysql.jdbc.Driver class and all other classes needed will be available to weblogic.

STEP 2: Start the BEA WebLogic server

You start the server with the startWeblogic.sh (unix) or startWebLogic.cmd (windows) script.
After the server has started, move on to step 3.
It could be that you are asked for your system password (weblogic 6) or administrator username and password (weblogic 7), if this information is not present in the startup script.
In this case, provide this information and the startup will proceed.

STEP3: Open the Configuration Console

Go to your Weblogic configuration console. Direct your web browser to the weblogic host, on the port you installed it. (by default this is 7001). eg: http://localhost:7001/console.
Use the parameters that you need (other host, other port, ...)

You will be prompted to give your administration password to enter the console.

STEP 4: Configure a ConnectionPool

When you're in the configuration panel, go to 'Services > JDBC > Connection Pools.'
Choose 'Configure a new JDBC Connection Pool'
Now you are presented a blank form where you have to put in all needed data. Use these values:

Name: MyConnectionPool
(unique name of the pool - you can use what you want)
URL: jdbc:mysql://mysqlhost/database
(where mysqlhost is the host on which MySQL is running, database is the name of the database) eg: jdbc:mysql://localhost/testdb
Driver ClassName: com.mysql.jdbc.Driver
Properties: user=youraccount
(use the name of your account on MySQL)
Password: ...
(type in your MySQL account password)

Leave all other fields as they are.
Now, click 'Apply' and your Connection Pool is created.

Click on the 'Targets' tab, select your weblogic server, click the right arrow to add it and click apply to deploy the pool to your server.

If there are any errors reported, consult the console window from which you started the weblogic server for details in the stacktrace. If something goes wrong at this point, the most likely reason is a typo in the classpath, resulting in a ClassNotFoundException.

STEP 5: Configure a DataSource

Now we've configured a connection pool with JDBC connections to the database, we're going to create a DataSource that we'll use from our J2EE components.

In the console panel, go to 'Services > JDBC > DataSources'.
Choose 'Configure a new JDBC DataSource'
Use these properties in the form:

Name: MyDataSource
(Unique name to identify your datasource, you can use what you want)
JNDI Name: MySQLDataSource
(You'll use this name to look up the datasource in JNDI from code)
Pool Name: MyConnectionPool
(Use the unique name you gave to your connection pool in step 4)

Click apply, choose the 'Targets' tab and deploy the DataSource to your server just as you did with the connection pool.

Restart your server and now you're ready to test the datasource.

STEP 6: Testing the DataSource from a simple JSP P

Next, we'll create a testmysql.jsp file in the applications/DefaultWebApp folder under the folder in which your startup script is located.
Put in this content: (Change the table and column names to fit your database schema):

< %@page import="javax.naming.*,javax.sql.*,java.sql.*"%>
< %
  // sample source for article http://www.javacoding.net/articles/technical/mysql-bea-wl.html

  Context ctx = new InitialContext ( );

  // In the lookup, be sure to use the 'JDNI NAME' you configured!
  DataSource dataSource = (DataSource) ctx.lookup ( "MySQLDataSource" );
  Connection connection = dataSource.getConnection ( );
  Statement statement = connection.createStatement ( );

  ResultSet result = statement.executeQuery ( "select * from myTable" );

  while ( result.next ( ) ) {
  out.print ( result.getString ( "myColumn" ) );
  out.print ( "
" );
 }

 connection.close ( );
%> Download this source file...

If you now direct your browser to http://localhost:7001/testmysql.jsp, you'll see all the values in [myColumn] in table [myTable].
(Don't forget to replace these names with names of actual objects in your table, and adapt the URL of the JSP according to your hostname and port of your running weblogic instance.)

If any error occurs, this results in a HTTP 500 status on your browser, but you can view the stacktrace of the error in the console window where you started the weblogic server.
The most probably error to be encountered is the classpath being incorrect, resulting in a NoClassDefFoundError for com.mysql.jdbc.Driver, and the username/password not being accepted by the MySQL database (you can use grant all privileges on myDatabase.* to myAccount@yourhost.yourdomain identified by 'yourPassword' in MySQL to grant permissions to a username/password combination)

Well, you've got your WebLogic running with a MySQL Connection and a DataSource object configured.
Information on how to proceed from this point is far beyond the scope of this article, and there are much better writers than me that can explain you the usage of Relational Database resources from within J2EE components.

If you have any questions about this article, please post on this sites' forums, our drop me a mail at gunther@javacoding.net...