Sunday 9 August 2009

Create and use an oracle connection pool in Tomcat 6.xx

NOTE: this post is in fact a translation and a shorter version of post that I wrote on my other blog ( www.baze-podataka.net ). It is translated using Google translate tool and than slightly modified by me. So, if you have any complaints, please complain to Google ;-)

Creating a connection pool is a very good practice when developing applications that often connect to the database because it is much "cheaper" to use one of the already created connections, then to repeat the entire process of Connecting to the database every time we need a connection.
It’s such a good practice that many application servers already have their own implementation of connection pooling.

Such server is Tomcat.

All that is left for us to do, of course, is to set all the properties of server that we need to use, and to provide the appropriate drivers that work with a specific database.
Then, if necessary, just take the connection from the pool, and use it, but take care of their proper use.

In this post, I will set Tomcat 6.0.18.
A database for which I’ll create a connection pool is Oracle 10g.

There’s a lot of tutorials, texts and examples about this on the Internet, but very often related to older versions of Tomcat (ver. 4, 5, 5.5).
Unfortunately, for all of these earlier versions of Tomcat many specifications and settings are different, so it probably won’t work with version 6.xx. At least that was the case with me.

So, let’s create an oracle connection pool!

Prerequisites:
First thing you need to have is , of course, Tomcat.
You can download it following this link.
If you use NetBeans to write a code, as I do, you can use Tomcat that comes bundled with the installation of the IDE itself.

Next, we need the appropriate JDBC drivers for Oracle 10g. This means you need an ojdbc14.jar file that comes within the Oracle itself.

Copy this file to <Tomcat home dir>/lib/ folder.

And, of course, we need a database installed too.
Check whether the database you wish to use is available and whether you can connect to it.

create a connection pool

Creating the pool is a quite fast and easy job.
For example, if your application context path is /myapp , it is necessary for you to:

1. Edit the context.xml file, which is located within the META-INF folder of your project like this:

<?xml version=”1.0? encoding=”UTF-8??>
<Context path=”/myapp” docBase=”myapp” reloadable=”false”>
<Resource name=”jdbc/oracleconnection” auth=”Container”
type=”javax.sql.DataSource” username=”existing_username” password=”user_password”
driverClassName=”oracle.jdbc.driver.OracleDriver” url=”jdbc:oracle:thin:@xxx.xxx.xxx.xxx:1521:sid”
maxActive=”10? maxIdle=”5?/>
</Context>


By setting these parameters you determine how this pool will “look” like.
Visit Tomcat’s website for a list of all available parameters.

2. In the web.xml file of your application, you need to insert:

<resource-ref>
<res-ref-name>jdbc/oracleconnection</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
inside tags <web-app> and </web-app>

And that's it!
Of course, this is not the only way to create connection pool in Tomcat-6, but it is quite simple, don't you think?

Hot to use connections within our web applications

Using connection pool’s connections within our web applications is as easy as creating the pool itself.

In Java EE, DataSource objects are obtained using the JNDI lookup mechanism.
All you need to do to get a connection is something like this:

Connection connection = null;
InitCtx context = new InitialContext ();
EnvCtx context = (Context) initCtx.lookup ( "java:comp/ENV");
DataSource ds = (DataSource) envCtx.lookup ("JDBC/oracleconnection");
connection = ds.getConnection ();

and that’s it.

If you are interested in learning more about connection pool, and the best ways of using it, I suggest you to visit my earlier post , and read the 11th chapter of the book I wrote about...

3 comments:

Unknown said...

Hello Sir when i create Struts.xml file its not keep in correct place , it kept in java resource :src , not in side WEB-INF-->classes-->struts.xml? please tell me the answer. or mail me tikamjobs@gmail.com

Darko Kalinic said...

Well, you have to do right click on the WEB-INF-->classes folder in order to make a file in it...

Try doing so and let me know if it worked...

Bharathireddy said...

Hello sir,should i create seperate contex.xml file in my project folder(i.e.,mypapp/WEB-INF/context.xml) or should I edit in Tomcat's conf/context.xml to create jndi datasource in tomcat..........
pls reply me sir.............