Archive for August, 2009

Connection Pooling in jBoss with MySQL and Oracle

Sunday, August 2nd, 2009
Handling Connection Pooling in Weblogic will be much easier, since WL itself provides the driver classes.But handling in jBoss is little trickier since, we need little diff configurations. Connection pooling in jBoss can be handled through the DataSouce. jBoss can manage the database connections by pooling them and by providing connections to the application when it needs them. Application developers need to specify the configurations about their connection.
OK, Now How will i configure DS for my application in jBoss. The DS in jBoss can be configured by using the config file called ABCApplication-ds.xml. What is this ABCApplication-ds.xml,OK here it goes, jBoss allows you to configure multiple DS’’s and picks automatically when the container loads, Since the -ds.xml is needed in naming our data source config file. So how it’’s loading the file, I don”t have the answer, I think it uses the Annotations/Reflection concept available in java. I”m telling this because the Action Class in Struts 2.0 will be picked on the same manner, i mean all the action class in Struts 2.0 should be the following format *Action.java. Hmm Now we”ll be back to our topic. For example if we are going to configure DS for my Travel application so i”ll be naming it as Travel-ds.xml. OKie, My First step is done, What is my next step. Where will i pick up my template specific to my database vendor i”m using in my application.
Yeahh, I have the answer donworry, This is where i like the Open Source always. Here goes Jboss 4.0.5docsexamplesjca. You have the templete for all the database vendors. For MySQL we have the template called “mysql-ds.xml”, So How i have modified for my application lool @ the following Travel-ds.xml
		<?xml version="1.0" encoding="UTF-8"?>
			<datasources>
				<local-tx-datasource>
					<jndi-name>MySqlDS</jndi-name>
					<connection-url>jdbc:mysql://localhost:3306/Travel</connection-url>				<driver-class>com.mysql.jdbc.Driver</driver-class>
					<user-name>root</user-name>
					<password>myTravelAppDB</password>
					<min-pool-size>10</min-pool-size>
					<max-pool-size>30</max-pool-size>
					<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.MySQLExceptionSorter</exception-sorter-class-name>
						<metadata>
							<type-mapping>mySQL</type-mapping>
						</metadata>
					</local-tx-datasource>
			</datasources>
		
OKie, in this MySqlDS is my datasource name. Apart from that i”m specifying some other parameters like driver-class, username, password.
For Oracle we have the template called “oracle-ds.xml”. So Travel-ds.xml will become like the following
			<?xml version="1.0" encoding="UTF-8"?>
				<datasources>
					<local-tx-datasource>
						<jndi-name>OracleDS</jndi-name>
						<connection-url>jdbc:oracle:thin:@localhost:1521:travelsid</connection-url>			<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
						<user-name>oracleUser</user-name>
						<password>myTravelAppDB</password>
						<min-pool-size>10</min-pool-size>
						<max-pool-size>30</max-pool-size>
						<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>	<metadata>
							<type-mapping>Oracle9i</type-mapping>
						</metadata>
					</local-tx-datasource>
				</datasources>
			
As of now, We are done with the configurations, Where will i place this file. First i kept it in WEB-INF folder since we used to do that for most of the time, Where i went wrong. Then where should we place this, Alright, this is my path Jboss 4.0.5serverdefaultdeployTravel-ds.xml. So jBoss automatically picks the file and loads it when it starts-up. jBoss treats this as an seperate application/service.
And now What will i do with the library, OKie go ahead and place it under here Jboss 4.0.5serverdefaultlib. We are down with our donfiguations. I”m ready to write my DAOUtil class. You know what i did, wrongly i kept the JDBC library for MySQL in Jboss 4.0.5/server/default/deploy. But still need to find out the reason, How it’s picking, may be it’’s picking while starting itself. :-) , I read some wher if you keep your file in deploy folder, then jBoss will treat it as a Service/Application, get back here after a week, Will let you know.
			package com.techmaddy;
			/****
			*	@author Kumarasamy Mani<kumarasamy@techmaddy.com>
			****/
			import java.sql.Connection;
			import java.sql.ResultSet;
			import java.sql.SQLException;
			import java.sql.Statement;
			import javax.naming.InitialContext;
			import javax.naming.NamingException;
			import javax.sql.DataSource;

			public class DAOUtil {
				public void excuteQueries() {
					Connection con = null;
					try {
						InitialContext jndiCntx = new InitialContext();
						DataSource ds = (DataSource)jndiCntx.lookup("java:/MySqlDS");
						con = ds.getConnection();
						Statement stmt = con.createStatement();
						ResultSet rs = stmt.executeQuery("select * from book");
						while(rs.next()) {
							System.out.println("n************nnThe title is "+rs.getString("title")+"nn*************n");
						}
					}
					catch(Exception e) {
						e.printStackTrace();
					}
					finally {
						try {
							con.close();
						}
						catch(Exception ex) {
							ex.printStackTrace();
						}
					}
				} //End of excuteQueries
			} //End of DAOUtil
		
We are done with DS implementation in jBoss.
Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)
Get Adobe Flash player