JBoss 3.2.x, MySQL and JNDI

[mindschreck]

Greetings,
I have been studing books and forums for hours now trying to understand how to implement connection pooling on JBoss so that my non-web client applications can utilize these connections through JNDI. It has been particularly frustrating due to the fact that so much of the documentation points at older releases of JBoss, specifically the mechanisms on how to configure the JBoss and MySQL connection pooling.

After reading all of this information, I have come to the following conclusion: setting up a MySQL connection pool on JBoss should be relatively simple and straight forward. This can be gleaned from the following URL: http://www.mysql.com/articles/connection_pooling_with_connectorj.html. The JBoss section (way down at the bottom) states:

JBoss
Place a copy of mysql-connector-java-[version]-bin.jar in $JBOSS_HOME/server/all/lib. Then, follow the example configuration file named mysql-ds.xml in the $JBOSS_HOME/docs/examples/jca directory that comes with a JBoss binary installation. To activate your DataSource, place an xml file that follows the format of mysql-ds.xml in the deploy subdirectory in either $JBOSS_HOME/server/all, $JBOSS_HOME/server/default, or $JBOSS_HOME/server/[yourconfig] as appropriate.

In an effort to clear up the confusion, I have documented everything I have tried so far, following the simple instructions above. I apologize for the verbosity, but I felt that it was important for me to be as detailed as possible in an effort to try and justify why I did certain things the way I did. After I summarize all of my steps, I'll pose some questions at the end.

Here is what I did:

I installed a fresh copy of mySQL (mysql-4.0.14b-win-noinstall.zip) on my local WinXP system.

I ran mysqld. It starts fine and runs as a process on WinXP (mysqld.exe)

I ran mysql:
use test;
create table tbl_employee ...
insert into tbl_employee ...
select * from tbl_employee ...
[3 rows shown]

Milestone: mySQL is set up OK with a test database containing tbl_employee with 3 records in it.

I installed a fresh copy of JBoss bundled with Tomcat (jboss-3.2.1_tomcat-4.1.24.zip) on my local WinXP system.

I test ran JBoss. It starts up fine:

Milestone: JBoss is set up with Tomcat.

I stopped JBoss.

I copied mysql-connector-java-3.0.8-stable-bin.jar from mysql-connector-java-3.0.8-stable.zip into jboss-3.2.1_tomcat-4.1.24\server\all\lib.

I navigated to jboss-3.2.1_tomcat-4.1.24\docs\examples\jca.

I copied mysql-ds.xml to jboss-3.2.1_tomcat-4.1.24\server\default\conf.

I edited mysql-ds.xml:

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

<local-tx-datasource>
<jndi-name>MySqlDS</jndi-name>
<connection-url>jdbc:mysql://localhost:3306/test</connection-url>
<driver-class>org.gjt.mm.mysql.Driver</driver-class>
<user-name>admin</user-name>
admin
</local-tx-datasource>


I then started JBoss again. It started fine.

I then examined http://localhost:8080/jmx-console and looked at:
jboss.jca
=========
name=DefaultDS,service=LocalTxCM
name=DefaultDS,service=ManagedConnectionFactory
name=DefaultDS,service=ManagedConnectionPool
name=JBoss JDBC XATransaction ResourceAdapter,service=RARDeployment
name=JBoss LocalTransaction JDBC Wrapper,service=RARDeployment
name=JMS Adapter,service=RARDeployment
name=JmsXA,service=ManagedConnectionFactory
name=JmsXA,service=ManagedConnectionPool
name=JmsXA,service=TxCM
name=MySqlDS,service=LocalTxCM
name=MySqlDS,service=ManagedConnectionFactory
name=MySqlDS,service=ManagedConnectionPool
service=CachedConnectionManager
service=ConnectionFactoryDeployer
service=RARDeployer

It looks as if everything is fine. My MySqlDS datasource is there.

I checked to see if my datasource was set up OK for JNDI by examining server.log in jboss-3.2.1_tomcat-4.1.24\server\default\log for 'java:MySqlDS':

2003-08-18 00:12:03,218 INFO [org.jboss.resource.connectionmanager.TxConnectionManager] Starting
2003-08-18 00:12:03,218 DEBUG [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.MySqlDS] Binding object 'org.jboss.resource.adapter.jdbc.WrapperDataSource@d337d3' into JNDI at 'java:/MySqlDS'
2003-08-18 00:12:03,218 INFO [org.jboss.resource.adapter.jdbc.local.LocalManagedConnectionFactory.MySqlDS] Bound connection factory for resource adapter for ConnectionManager 'jboss.jca:service=LocalTxCM,name=MySqlDS to JNDI name 'java:/MySqlDS'
2003-08-18 00:12:03,218 INFO [org.jboss.resource.connectionmanager.TxConnectionManager] Started

Milestone: AFAICT, I have a mySQL datasource pointing to a database that has one table. It can be referenced through JNDI via a lookup as java:/MySqlDS.

At this point, I have what I think I need to create a simple client program in Eclipse that can do a lookup to my JNDI datasource, get a connection, and then do stuff with it. My program is as follows:

import javax.sql.*;
import java.sql.*;
import javax.naming.*;
import java.util.*;

public class TestMySql {
public static void main(String[] args) {
Connection con = null ;
try {
Hashtable env = new Hashtable();
env.put(Context.INITIAL_CONTEXT_FACTORY,
"org.jnp.interfaces.NamingContextFactory");
env.put(Context.PROVIDER_URL, "localhost:1099");
env.put("java.naming.factory.url.pkgs",
"org.jboss.naming rg.jnp.interfaces");

Context jndiCntx = new InitialContext(env);
System.out.println("Looking up java:/MySqlDS");
DataSource ds = (javax.sql.DataSource)jndiCntx.lookup("java:/MySqlDS");
System.out.println("Found datasource. Connecting to java:/MySqlDS");
con = ds.getConnection();
if (null != con) {
System.out.println("Connection successful!");
}
} catch (Exception ex) {
System.out.println("getConnection failed.");
ex.printStackTrace();
} finally {
// close the Connection
try {
if (con!=null) con.close() ;
} catch(SQLException sqle) {
con = null ;
}
}
}
}

When I execute the program, I get the following:

Looking up java:/MySqlDS
javax.naming.NameNotFoundException: MySqlDS not bound
getConnection failed.
at org.jnp.server.NamingServer.getBinding(NamingServer.java:495)
at org.jnp.server.NamingServer.getBinding(NamingServer.java:503)
at org.jnp.server.NamingServer.getObject(NamingServer.java:509)
at org.jnp.server.NamingServer.lookup(NamingServer.java:282)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at sun.rmi.server.UnicastServerRef.dispatch(UnicastServerRef.java:261)
at sun.rmi.transport.Transport$1.run(Transport.java:148)
at java.security.AccessController.doPrivileged(Native Method)
at sun.rmi.transport.Transport.serviceCall(Transport.java:144)
at sun.rmi.transport.tcp.TCPTransport.handleMessages(TCPTransport.java:460)
at sun.rmi.transport.tcp.TCPTransport$ConnectionHandler.run(TCPTransport.java:701)
at java.lang.Thread.run(Thread.java:536)
at sun.rmi.transport.StreamRemoteCall.exceptionReceivedFromServer(StreamRemoteCall.java:247)
at sun.rmi.transport.StreamRemoteCall.executeCall(StreamRemoteCall.java:223)
at sun.rmi.server.UnicastRef.invoke(UnicastRef.java:133)
at org.jnp.server.NamingServer_Stub.lookup(Unknown Source)
at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:492)
at org.jnp.interfaces.NamingContext.lookup(NamingContext.java:471)
at javax.naming.InitialContext.lookup(InitialContext.java:347)
at TestMySql.main(TestMySql.java:33)

As I am new at all of this, here are some questions that I have:

This datasource is supposed hand out javax.sql.DataSource objects. Don't I need to configure that somewhere? Like in mysql-ds.xml? I saw some examples that implied adding an mbean with code="org.jboss.jdbc.XADataSourceLoader" to mysql-ds.xml. Do I need to do this? The Hypersonic hsqldb-ds.xml has one:

<!-- this mbean should be used only when using tcp connections -->

1701
true
default
false
true


If I have to use an mbean in the mysql-ds.xml, is the port arbitrary, or does it need to be the same port as mySQL (3306)?

Can I even create a standalone client program to fetch connections from JNDI running on JBoss?

Since this program would be running in a separate JVM, how does JBoss JNDI know that I am priveledged to fetch connections from it? Do I need to set something in my env hashtable to symbolize that it's ok?

If I can't create a standalone client program that utilizes connections from the JBoss hosted connection pool, and I'm not prepared to move to BMP or CMP, how do I allow my standalone client programs to leverage the pool?

Is the URL for accessing the connection pool always at port 1099? I am unsure of where this port number comes from. Apparently, 1099 is a 'jnp' port, which I am unfamiliar with.

When configuring the JDBC driver connection pool, should I use org.gjt.mm.mysql.Driver or should I use com.mysql.jdbc.Driver?

I am planning on setting up a simple servlet that will run under JBoss, which I am fairly sure will work just fine with JNDI lookups. I have had Jsp and Servlet code doing emulated JNDI lookups of pooled connections on Tomcat, but went down the path of JBoss for connection pooling to satisfy the requirements of my non-web applications needing the connection pooling as well.

Thanks you anyone who has sacrificed their time and energy to read this. I appreciate it immensely.

Cheers,
Jason

P.S. Original post at: http://saloon.javaranch.com/cgi-bin/ubb/ultimatebb.cgi?ubb=get_topic&f=63&t=000372

[jonlee]

Your mysql-ds.xml should go in your deploy directory - server/default/deploy in your example.

The JNDI port for doing your remote lookups (anything not performing a JNDI lookup from within the same JVM as JBoss) is port 1099 - a default that can be changed by editing server/default/jboss-service.xml.

Your DataSource provides a JDBC connection. This connection is a handle to the underlying, physical connection to the DBMS and the physical connection is created within the JVM.

A standard JDBC connection is not a proxy implementation, so external entities to the JVM cannot use the connection - you cannot hand over the physical connection to a remote client. The JBoss JNDI binding for DataSources prevents clients external to the JVM from locating the resource - you will get a "Name not bound" message. AFAIK, WebLogic is the only application server to implement a proxy-type datasource service for remote clients. However, much of the advantage of having a pooled connection is offset by making continual remote connections to the DataSource connection factory.

You would usually use stateless session beans or stateful session beans to perform the necessary business logic (they reside within the JBoss JVM) - either they can directly use the datasource or indirectly via CMP/BMP. Essentially, you move the traditional client business logic into the second tier.

Most people in the forums have got their MySQL databases working with org.gjt.mm.mysql.Driver. But if the other driver delivers a JDBC connection, then I cannot see a problem with using it. However, I am not a MySQL user so that would be better answered by someone who has used it. In either case, the driver library will need to go into server/default/lib.

Hope that answers most of your questions.

[mindschreck]

Thanks for the info.

I now understand that java: is private to components running on the server. That makes sense.

I did set up the connection pooling with a servlet running on JBoss and it worked fine. That clears up alot of confusion!

[rberehoudougou]

Hi,
I have been struggking with the same issue for a couple of days now.
I have deployed a CMP 1.1 and everything just seems fine.
I run the client and get the lookups I expected ! THE OMLY Problem is when I go to MySQL and open the TABLE in the database, there IS NOTHING ! NONE OF THE RECORDS I CREATED !
Can someone explain what is going on ?

Many Thanks

[jonlee]

Most likely this is not a datasource related problem since you are not getting any errors related to the datasource connectivity. You should probably direct this to the CMP/Persistence forum.

If you are destroying the entities when your client finishes its work, this is the same as deleting the records from your database. In the default mode, if the container created the table as part of CMP, it will also delete the table when the table is empty. If this is not the issue, then you will need to direct the question to the CMP forum and provide some more details on what you are doing.

[saed]

Hi,

i have succeeded to bound a DataSource object to JNDI. What if i need to bind ConnectionPoolDataSource object what should i do? How can i bind this new object to a new JNDI?

For example the datasource object is bounded to "java:/MySqlDS" which id referenced in "mysql-service.xml" file so what do i need to reference a ConnectionPoolDataSource object?

thanks.