Wednesday, August 6, 2008

MySQL Connection Pool in Tomcat

Connection Pool in Tomcat


Configure the JNDI DataSource in Tomcat by adding a declaration for your resource to your context.xml.
<Context>

<Resource name="jdbc/dbname" 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/dbname?autoReconnect=true"/>
Context>

web.xml configuration


<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 Appdescription>

<
resource-ref>
<
description>DB Connectiondescription>
<
res-ref-name>jdbc/dbnameres-ref-name>
<
res-type>javax.sql.DataSourceres-type>
<
res-auth>Containerres-auth>
<
resource-ref><web-app>

Code sample in JSP/JSTL


<%@ 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/dbname">
select id, foo, bar from testdata
</sql:query>

<
html>
<
head>
<
title>DB Testtitle>
<
head>

<
body>
<
h2>Resultsh2>
<
c:forEach var="row" items="${rs.rows}">
Foo ${row.foo}<
br/>
Bar ${row.bar}<
br/>
c:forEach>
<
body>
<
html>

Code sample in Java


Context initContext = new InitialContext();
Context envContext = (Context)initContext.lookup("java:/comp/env");
DataSource ds = (DataSource)envContext.lookup("jdbc/dbname");
Connection conn = ds.getConnection();
//etc.

No comments: