connection pooling configuration

[ameenjaradat]

Hi All

i am a new user for jboss and i have complete subscription for the jboss documentation , i want to deploy a web application that use connection pooling to access oracle database , can anyone tell about the steps that i need to configure the connection pool or tell me where can i find these steps in jboss documentaion.

[catharine]

I was just about to post a similar question myself.

I have found examples showing how to get a connection from the connection pool, but I am not sure what I need to add to which xml files.

I was hoping there would be a step by step guide in the documentation somewhere but I couldn't find one.

Anyones help on this would be appreciated.
I am using SQL Server BTW.

Catharine

[catharine]

I have replied to this once and it didn't show up so if this appears twice at some point then I apologise.

I was about to post a similar question about connection pooling configuration myself.

I have managed to find some example code showing how to get a connection, but I am unsure of what needs adding to which xml files.

I was hoping there would be a step by step guide in the documentation, but I couldn't find one.

BTW - I am using SQL Server.

Any information on this would be appreciated.

Catharine

[jonlee]

OK. I'm going to assume that you are using JBoss 3.2.x. Things are much simpler in this version in terms of configuration of the actual pool.

Get the datasource configuration of your choice from docs/examples/jca. In my example, I'm going to use the db2 configuration example, called db2-ds.xml. Copy it to the deployment directory of your JBoss instance. For example, if you are running the default instance, copy the file to server/default/deploy.

Now, you want to modify the pool definition for your particular circumstances. You will want to give it a JNDI name so that your EJB can reference it (locate and use it). I've called mine AmityPool.

Next, you need to specify the JDBC connection URL - the same URL you would use if you were using the JDBC driver directly. Here, I'm connecting to a DB2 7.2 UDB database instance called AMITY on server 172.16.1.2.

You need to specify the JDBC Driver connection class for your connection. This would be the class you would use for establishing a JDBC database connection to your database.

The username and password supplied here is your database login - you should have sufficient permission with that login to achieve the database operations you are going to execute with the EJBs. Note that you should take security precautions in a production environment with this file as it has sensitive information in clear text.

Finally, you probably want to tune the pool parameters. Usually most of the defaults are fine but you want to probably control the number of connections - for example you don't want a berserk application to create so many connections that your database can no longer function. Similarly, you want sufficient minimum connections to service normal load adequately. You do this with min-pool-size and max-pool-size tags. The file looks like the following now.

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

<!-- ===================================================================== -->
<!-- -->
<!-- JBoss Server Configuration -->
<!-- -->
<!-- ===================================================================== -->

<!-- $Id: db2-ds.xml,v 1.1 2002/07/22 22:57:24 d_jencks Exp $ -->



<local-tx-datasource>
<jndi-name>AmityPool</jndi-name>
<connection-url>jdbc:db2://172.16.1.2:6789/AMITY</connection-url>
<driver-class>ibm.db2.jdbc.net.DB2Driver</driver-class>
<user-name>database-username</user-name>
database-password
<min-pool-size>2</min-pool-size>
<max-pool-size>10</max-pool-size>
</local-tx-datasource>



Right. You want to put the JDBC driver in the JBoss instance library directory, server/default/lib in this case. For DB2, this would be db2java.zip.

Now you can start JBoss and you should see it start up fine and you should be able to see some connections to your database.

Next, for your beans, you need to specify that it has these resources available. First, declare the resource in ejb-jar.xml. The bean here is a stateless session bean called Items. Note the resource-ref declaration for the pool, AmityPool as this is the connection pool we defined above.

<?xml version="1.0"?>
<!DOCTYPE ejb-jar PUBLIC "-//Sun Microsystems, Inc.//DTD Enterprise JavaBeans 2.0//EN" "http://java.sun.com/j2ee/dtds/ejb-jar_2_0.dtd">
<ejb-jar>
Items
<enterprise-beans>

<display-name>Items</display-name>
Items
<ejb-name>Items</ejb-name>
com.amity.items.ItemsHome
com.amity.items.Items
<ejb-class>com.amity.items.ItemsBean</ejb-class>
<session-type>Stateless</session-type>
<transaction-type>Bean</transaction-type>
<resource-ref>
AmityPool
<res-ref-name>AmityPool</res-ref-name>
<jndi-name>java:/AmityPool</jndi-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Application</res-auth>
</resource-ref>

</enterprise-beans>
</ejb-jar>

Now, for JBoss, you will need to provide an extension in jboss.xml that specifies the references. So you want to declare AmityPool in the resource managers and in the enterprise-beans sections.

<?xml version="1.0"?>


<resource-managers>
<resource-manager res-class="org.jboss.ejb.deployment.JDBCResource">
<res-name>AmityPool</res-name>
<res-jndi-name>AmityPool</res-jndi-name>
</resource-manager>
</resource-managers>
<enterprise-beans>

<ejb-name>Items</ejb-name>
<jndi-name>Items</jndi-name>
<configuration-name>Standard Stateless SessionBean</configuration-name>
<resource-ref>
<res-ref-name>AmityPool</res-ref-name>
<resource-name>AmityPool</resource-name>
</resource-ref>

false
</enterprise-beans>


Ok. Now all you need to do is to establish connections by reference. With session beans, you need to do the leg work.

So you will want to do something like this:
Context naming = new InitialContext();
dataSource = (DataSource)naming.lookup("java:/AmityPool");
java.sqlConnection connection = dataSource.getConnection();

When you've finished with the connection, remember to give it back to the pool with:
connection.close();

Otherwise you will hold up the connection until the bean is eventually recycled or dies. Believe, me in the early days I actually did this (4 years back) and the sell-side application ground to a halt.

You can of course bind the JNDI to something other than "java:/xxx" but it is ultimately up to you.

Hope that gets you started.

[seeteshh]

Hi,

I had configured JBoss2.4.10Tomcat3.2.3 for SQL Server ie I had created a ConnectionPool in the jboss.jcml present in jboss-tomcat\jboss\conf\tomcat folder.

But the same doesnt work for Oracle 8i as I am not able to set the Driver Class in the same file.

Any help on this will be appreciated.

Seetesh

[catharine]

jonlee,

Thanks for your detailed response on this. It has made things a lot clearer and has been very useful.

Thanks,
Catharine