But I am sure there must be many first timers in databases and application deployment who will find this useful.
What is Connection Pooling?
Well, connection pooling is a way of creating a pool of database connections on the server.
When you create a single dedicated connection as in:
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/myDB", "username", "password");
the driver instance stays dedicated to this thread that has created it. However, the application may require to use this connection for a very short time (few milliseconds) while for the rest of the time, it would be sitting idle but blocking resources.
In such a case, it is beneficial to create a connection pool which just picks a connection from the pool and 'loans' it to the application thread using it.
This gives a very nice overview of connection pooling.
How to perform Connection Pooling?
1. Open the ${CATALINA_HOME}/conf/server.xml file and enter the following inside the <host> </host> tags:
<Context path="/myApp" docbase="myApp" debug="5" reloadable="true" crosscontext="true">
<resource name="jdbc/myDB" auth="Container" type="javax.sql.DataSource" maxactive="100" maxidle="30" maxwait="10000" username="DBusername" password="DBpassword" driverclassname="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/myAppDB">
</resource>
</context>
2. If not already there, add this to WEB-INF/web.xml of your application (which you must have placed under ${CATALINA_HOME}/webapps - so the location will be something like ${CATALINA_HOME}/webapps/
<resource-ref>
<description> My DataSource </description>
<res-ref-name> jdbc/myDB </res-ref-name>
<res-type> javax.sql.DataSource </res-type>
<res-auth> CONTAINER </res-auth>
</resource-ref>
3. Now, your code to access this connection pool should look something like this:
dsname = "java:comp/env/jdbc/myDB";
ctx = new InitialContext();
ds = (DataSource)ctx.lookup(dsname);
conn = ds.getConnection();
..followed by the usual conn.createStatement(), etc. code.
How I got here:
I was trying to set up a jUDDI server on my machine. However, I never found a correspondence between what is written in their user guide and what is available for download. The only consistency I found was when I used the instructions given on the apache wiki for the deployment of this version of jUDDI given on sourceforge.net. So, I will discuss these instructions here.
All the instructions given therein are correct except the stuff suggested to be put under the <host> tag in ${CATALINA_HOME}/conf/server.xml. The following needs to be added instead:
<Context path="/juddi" docbase="juddi" debug="5" reloadable="true" crosscontext="true">
<resource name="jdbc/juddiDB" auth="Container" type="javax.sql.DataSource" maxactive="100" maxidle="30" maxwait="10000" username="juddi" password="123456" driverclassname="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/juddi?autoReconnect=true">
</resource>
</context>
Also, in the WEB-INF/classes/juddi.properties file inside juddi, set the juddi.useConnectionPool property to 'true'.
Now, the http://localhost:8080/juddi/happyjuddi.jsp page should be visible without any red lines - a problem which many people have tried to solve and write blogs about, but seems there is always something that gets missed out. It is possible that I miss out something too. Hence, here are some very good references; if something doesn't work, do try these out too:
Have a good day!
- Dhaval
No comments:
Post a Comment