Friday, 3 June 2011
Connection pooling with java, tomcat 6, MySql and Eclipse IDE
Here, are the steps I tried for implementing connection pooling in Java.
Please reply with feedbacks if I'm wrong or need further improvement.
Thank you!
1. Open Eclipse and create a new ->dynamic web project, name it DBTest
2. That JSP page makes use of JSTL's SQL and Core taglibs. You can get it from Sun's Java Web Services Developer Pack or Jakarta Taglib Standard 1.1 project - just make sure you get a 1.1.x release
3. Once you have JSTL, copy
jstl.jar and standard.jar to your web app's WEB-INF/libdirectory, in DBTest->WebContent->WEB-INF->lib folder
4. Next modify the web.xml under the WEB-INF folder to something given below,
<web-app xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee
http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd"
version="2.4">
<description>MySQL Test App</description>
<resource-ref>
<description>DB Connection</description>
<res-ref-name>jdbc/TestDB</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
</resource-ref>
</web-app>
5. Create a file context.xml under META-INF directory and modify it as follows,
<Context path="/DBTest" docBase="DBTest"
reloadable="true" crossContext="true">
<!-- maxActive: Maximum number of dB connections in pool. Make sure you
configure your mysqld max_connections large enough to handle
all of your db connections. Set to -1 for no limit.
-->
<!-- maxIdle: Maximum number of idle dB connections to retain in pool.
Set to -1 for no limit. See also the DBCP documentation on this
and the minEvictableIdleTimeMillis configuration parameter.
-->
<!-- maxWait: Maximum time to wait for a dB connection to become available
in ms, in this example 10 seconds. An Exception is thrown if
this timeout is exceeded. Set to -1 to wait indefinitely.
-->
<!-- username and password: MySQL dB username and password for dB connections -->
<!-- driverClassName: Class name for the old mm.mysql JDBC driver is
org.gjt.mm.mysql.Driver - we recommend using Connector/J though.
Class name for the official MySQL Connector/J driver is com.mysql.jdbc.Driver.
-->
<!-- url: The JDBC connection url for connecting to your MySQL dB.
-->
<Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="javauser" password="javadude" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://localhost:3306/javatest"/>
</Context>
6. Create a test.jsp in the WebContent folder like given,
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<sql:query var="rs" dataSource="jdbc/TestDB">
select id, foo, bar from testdata
</sql:query>
<html>
<head>
<title>DB Test</title>
</head>
<body>
<h2>Results</h2>
<c:forEach var="row" items="${rs.rows}">
Foo ${row.foo}<br/>
Bar ${row.bar}<br/>
</c:forEach>
</body>
</html>
7. Now let’s configure our MySql server:
Ensure that you follow these instructions as variations can cause problems.
Create a new test user, a new database and a single test table. Your MySQL user must have a password assigned. The driver will fail if you try to connect with an empty password.
 | | |
|
mysql> GRANT ALL PRIVILEGES ON *.* TO javauser@localhost
-> IDENTIFIED BY 'javadude' WITH GRANT OPTION;
mysql> create database javatest;
mysql> use javatest;
mysql> create table testdata (
-> id int not null auto_increment primary key,
-> foo varchar(25),
-> bar int);
| |
| | |
Note: the above user should be removed once testing is complete!
Next insert some test data into the testdata table.
| | |
|
mysql> insert into testdata values(null, 'hello', 12345);
Query OK, 1 row affected (0.00 sec)
mysql> select * from testdata;
+----+-------+-------+
| ID | FOO | BAR |
+----+-------+-------+
| 1 | hello | 12345 |
+----+-------+-------+
1 row in set (0.00 sec)
mysql>
| |
| |
8. Deploy your web app into
$CATALINA_BASE/webapps either as a warfile calledDBTest.war or into a sub-directory called DBTest. Right click DBTest in eclipse ->Export->browse to webapp, where you installed apache tomcat and save as .war file.
9. Goto bin folder for apache tomcat and run as administrator startup.bat
10. Point a browser at
http://localhost:8080/DBTest/test.jsp to view the fruits of your hard work.
P.S. if we try to modify context.xml again in eclipse, it does not actually get modified in the original context.xml located in /conf folder, so, we have to make sure it is further modified in the context.xml file located in /conf folder.
For servlets:
package my.connpooling.test;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;
public class TestServlet extends HttpServlet {
private DataSource dataSource;
private Connection connection;
private Statement statement;
public void init() throws ServletException {
try {
// Get DataSource
Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
dataSource = (DataSource)envContext.lookup("jdbc/TestDB");
} catch (NamingException e) {
e.printStackTrace();
}
}
public void doGet(HttpServletRequest req, HttpServletResponse resp)
throws ServletException, IOException {
ResultSet resultSet = null;
try {
// Get Connection and Statement
connection = dataSource.getConnection();
statement = connection.createStatement();
String query = "SELECT * FROM basicinfo";
resultSet = statement.executeQuery(query);
PrintWriter out = resp.getWriter();//will print in the browser
out.println("<h2>This is resultset from a MySql database using servlets showing connection pooling...</h2>");
while (resultSet.next()) {
System.out.println(resultSet.getInt(1)+" " + resultSet.getString(2)+" " + resultSet.getString(3));
//print result in browser
out.println(resultSet.getInt(1)+" " + resultSet.getString(2)+" " + resultSet.getString(3));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
try { if(null!=resultSet)resultSet.close();} catch (SQLException e)
{e.printStackTrace();}
try { if(null!=statement)statement.close();} catch (SQLException e)
{e.printStackTrace();}
try { if(null!=connection)connection.close();} catch (SQLException e)
{e.printStackTrace();}
}
}
}
Web.xml:
<?xml version="1.0" encoding="UTF-8"?>
<web-app id="WebApp_ID" version="2.4"
xmlns="http://java.sun.com/xml/ns/j2ee"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/j2ee http://java.sun.com/xml/ns/j2ee/web-app_2_4.xsd">
<display-name>TomcatConnectionPooling</display-name>
<welcome-file-list>
<welcome-file>test.jsp</welcome-file>
</welcome-file-list>
<servlet>
<servlet-name>TestServlet</servlet-name>
<servlet-class>
my.connpooling.test.TestServlet
</servlet-class>
</servlet>
<servlet-mapping>
<servlet-name>TestServlet</servlet-name>
<url-pattern>/TestServlet</url-pattern>
</servlet-mapping>
</web-app>
References:
Http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html
http://viralpatel.net/blogs/2009/09/database-connection-pooling-tomcat-eclipse-db.html
Subscribe to: Post Comments (Atom)
No comments:
Post a Comment