aspose file tools*
The moose likes JBoss/WildFly and the fly likes JBoss 3.2.x, MySQL and Connection Pooling Question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » JBoss/WildFly
Bookmark "JBoss 3.2.x, MySQL and Connection Pooling Question" Watch "JBoss 3.2.x, MySQL and Connection Pooling Question" New topic
Author

JBoss 3.2.x, MySQL and Connection Pooling Question

Jason Mowat
Ranch Hand

Joined: Aug 17, 2003
Posts: 79
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"?>
<datasources>
<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>
<password>admin</password>
</local-tx-datasource>
</datasources>
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 -->
<mbean code="org.jboss.jdbc.HypersonicDatabase"
name="jboss:service=Hypersonic">
<attribute name="Port">1701</attribute>
<attribute name="Silent">true</attribute>
<attribute name="Database">default</attribute>
<attribute name="Trace">false</attribute>
<attribute name="No_system_exit">true</attribute>
</mbean>
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
norman richards
Author
Ranch Hand

Joined: Jul 21, 2003
Posts: 367
You can't lookup datasources from a client via JNDI. The only way I could think of to do this would be some sort of proxy datasource, but JBoss doesn't have anything like this.
If you want to see what JBoss exposes to clients via JNDI, look at the global namespace in JNDIView. That's what is exposed. The datasources are bound in the java: namespace, which isn't exposed via JNDI by JBoss.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JBoss 3.2.x, MySQL and Connection Pooling Question