For configuring a connection pool, follow this URL:
http://jakarta.apache.org/commons/dbcp/configuration.html
1. The JDBC driver JAR must go in the common/lib
directory (because for connection pooling it needs to
be accessible to
both Tomcat and the web app).
2. DBCP is built into Tomcat so you don't need to
install a JAR for that.
3. Always close the connection when you're done with
it.
The configuration shown below is what in my Tomcat's
server.xml file:
<DefaultContext>
<Resource name="jdbc/MySQLDB" auth="Container"
type="javax.sql.DataSource"/>
<ResourceParams name="jdbc/MySQLDB">
<parameter>
<name>factory</name>
<value>org.apache.commons.dbcp.BasicDataSourceFactory</value>
</parameter>
<parameter>
<name>driverClassName</name>
<value>com.mysql.jdbc.Driver</value>
</parameter>
<parameter>
<name>url</name>
<value>jdbc:mysql://localhost:3306/artimus?autoReconnect=true</value>
</parameter>
<parameter>
<name>username</name>
<value>javauser</value>
</parameter>
<parameter>
<name>password</name>
<value>javadude</value>
</parameter>
<parameter>
<name>maxActive</name>
<value>20</value>
</parameter>
<parameter>
<name>maxIdle</name>
<value>30</value>
</parameter>
<parameter>
<name>maxWait</name>
<value>10000</value>
</parameter>
<parameter>
<name>removeAbandoned</name>
<value>true</value>
</parameter>
<parameter>
<name>removeAbandonedTimeout</name>
<value>60</value>
</parameter>
<parameter>
<name>logAbandoned</name>
<value>true</value>
</parameter>
</ResourceParams>
</DefaultContext>
</Host>
Then, the code to get a DB connection is:
import java.sql.Connection;
import java.sql.SQLException;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
public class DBConnection
{
public static Connection getDBConnection() throws
SQLException
{
Connection conn = null;
try
{
InitialContext ctx = new InitialContext();
DataSource ds = ( DataSource ) ctx.lookup(
"java:comp/env/jdbc/MySQLDB" );
try
{
conn = ds.getConnection();
}
catch( SQLException e )
{
System.out.println( "Open connection
failure: " + e.getMessage() );
}
}
catch( NamingException nEx )
{
nEx.printStackTrace();
}
return conn;
}
}
Then, the code to use the DB connection:
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import org.apache.artimus.message.ThreadBean;
import org.apache.artimus.message.PostBean;
import org.apache.artimus.message.AttachmentBean;
import
org.apache.artimus.message.exceptions.MessageDAOSysException;
import
org.apache.artimus.message.exceptions.ObjectNotFoundException;
import
org.apache.artimus.message.exceptions.AssertionException;
import
org.apache.artimus.message.exceptions.DatabaseException;
import
org.apache.artimus.message.exceptions.BadInputException;
import org.apache.artimus.ConnectionPool.DBConnection;
public class MySQLMessageDAO implements MessageDAO
{
public void createThread( String receiver, String
sender, String title,
String
lastPostMemberName, String threadTopic,
String threadBody,
Timestamp threadCreationDate,
Timestamp
threadLastPostDate, int threadType,
int threadOption, int
threadStatus, int threadViewCount,
int threadReplyCount, int
threadDuration )
throws
MessageDAOSysException
{
Connection conn = null;
PreparedStatement stmt = null;
String insertSQL = "INSERT INTO message_thread(
message_receiver, message_sender, article_title,
last_post_member_name, thread_topic, thread_body,
thread_creation_date, thread_last_post_date,
thread_type, thread_option, thread_status,
thread_view_count, thread_reply_count, thread_duration
) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
try
{
conn = DBConnection.getDBConnection();
stmt = conn.prepareStatement( insertSQL );
stmt.setString( 1, receiver );
stmt.setString( 2, sender );
stmt.setString( 3, title );
stmt.setString( 4, lastPostMemberName );
stmt.setString( 5, threadTopic );
stmt.setString( 6, threadBody );
stmt.setTimestamp( 7, threadCreationDate );
stmt.setTimestamp( 8, threadLastPostDate );
stmt.setInt( 9, threadType );
stmt.setInt( 10, threadOption );
stmt.setInt( 11, threadStatus );
stmt.setInt( 12, threadViewCount );
stmt.setInt( 13, threadReplyCount );
stmt.setInt( 14, threadDuration );
stmt.executeUpdate();
// m_dirty = true;
}
catch( SQLException se )
{
System.out.println( se.getMessage() );
se.printStackTrace();
throw new MessageDAOSysException( "Error
executing SQL in ThreadHandler.createThread." );
}
finally
{
if ( conn != null )
{
try
{
stmt.close();
stmt = null;
conn.close();
}
catch( SQLException sqlEx )
{
System.out.println( "Problem occurs
while closing " + sqlEx );
}
conn = null;
}
}
}
}
-Caroline
--- Michael Ni <[EMAIL PROTECTED]> wrote:
> thx caroline
>
>
> >From: Caroline Jen <[EMAIL PROTECTED]>
> >Reply-To: "Tomcat Users List"
> <users@tomcat.apache.org>
> >To: Tomcat Users List <users@tomcat.apache.org>
> >Subject: Re: web application - student need help
> >Date: Fri, 5 Jan 2007 13:42:18 -0800 (PST)
> >
> >Did you configure a connection pool in Tomcat?
> Then,
> >get a connection object from the pool for each data
> >search method invocation.
> >
> >I am at work now. I do not have the configuration
> >with me. I can post the configuration and the
> >database access code when I get home.
> >
> >Meanwhile, do a search on the internet.
> >--- Michael Ni <[EMAIL PROTECTED]> wrote:
> >
> > > i don't get any error when there isn't that much
> > > traffic
> > >
> > > but i dont close my jdbc connections, could that
> be
> > > a problem?
> > >
> > > this is the function i use to query
> > >
> > >
>
>----------------------------------------------------------------------------------------
> > > public ResultSet getData(String queryStr) throws
> > > Exception
> > > {
> > >
> > > try {
> > > DBConstants db = new DBConstants();
> > > Class.forName(db.getDrivername());
> > > Connection conn;
> > > conn =
> > >
>
>DriverManager.getConnection("jdbc:microsoft:sqlserver://"
> > > +
> > > db.getHostname() + "","" + db.getUsername() +
> "",""
> > > + db.getPassword() +
> > > "");
> > >
> > >
> > > Statement stmt = conn.createStatement();
> > > ResultSet rs = stmt.executeQuery(queryStr);
> > > return rs;
> > > }
> > > catch(Exception e) {
> > > e.printStackTrace();
> > > System.out.println("getData error");
> > > throw new Exception();
> > > }
> > > }
> > >
>
>----------------------------------------------------------------------------------
> > >
> > > then if i want to use it in a jsp page i will do
> > > something like this
> > >
>
>------------------------------------------------------------------------
> > >
> > > <%
> > > DBPoolBean dbpb = new DBPoolBean();
> > >
> > > ResultSet rs = dbpb.getData("SELECT * FROM
> drops
> > > order by hnm,
> > > itemname_en");
> > > while ( rs.next() ) {
> > > out.println("<tr><td width=\"50\">" +
> > > rs.getString("hnm") + " </td>");
> > > out.println("<td width=\"50\">" +
> > > rs.getString("itemname_en") + "
> > > </td></tr>");
> > > }
> > > %>
> > >
> > >
> > >
> > > >From: Darek Czarkowski
> > > <[EMAIL PROTECTED]>
> > > >Reply-To: "Tomcat Users List"
> > > <users@tomcat.apache.org>
> > > >To: Tomcat Users List <users@tomcat.apache.org>
> > > >Subject: Re: web application - student need
> help
> > > >Date: Fri, 05 Jan 2007 12:52:19 -0800
> > > >
> > > >Michael Ni wrote:
> > > >>
> > > >>the web application uses simple queries, like
> > > search a table for a certain
> > > >>condition. i realize when multiple people
> access
> > > the database it hangs,
> > > >>and causes the jsp pages to error.
> > > >Just a guess, your connection to the database
> is a
> > > problem, perhaps errors
> > > >in queries, not closed connections.
> > > >What are the error messages? (page/log)
> > > >
> > > >--
> > > >Darek Czarkowski
> > > >Ph: 604 294 6557 (Ext. 113)
> > > >Fx: 604 294 6507
> > > >www.infinitesource.ca
> > > >darekc at infinitesource dot ca
> > > >
> > > >
> > >
> >
>
>---------------------------------------------------------------------
> > > >To start a new topic, e-mail:
> > > users@tomcat.apache.org
> > > >To unsubscribe, e-mail:
> > > [EMAIL PROTECTED]
> > > >For additional commands, e-mail:
> > > [EMAIL PROTECTED]
> > > >
> > >
> > >
>
>_________________________________________________________________
> > > Type your favorite song. Get a customized
> station.
> > > Try MSN Radio powered
> > > by Pandora.
> > > http://radio.msn.com/?icid=T002MSN03A07001
> > >
> > >
> > >
>
>---------------------------------------------------------------------
> > > To start a new topic, e-mail:
> > > users@tomcat.apache.org
> > > To unsubscribe, e-mail:
> > > [EMAIL PROTECTED]
> > > For additional commands, e-mail:
> > > [EMAIL PROTECTED]
> > >
> > >
> >
> >
> >__________________________________________________
> >Do You Yahoo!?
> >Tired of spam? Yahoo! Mail has the best spam
> protection around
> >http://mail.yahoo.com
> >
>
>---------------------------------------------------------------------
> >To start a new topic, e-mail:
> users@tomcat.apache.org
> >To unsubscribe, e-mail:
> [EMAIL PROTECTED]
> >For additional commands, e-mail:
> [EMAIL PROTECTED]
> >
>
>
_________________________________________________________________
> From photos to predictions, The MSN Entertainment
> Guide to Golden Globes has
> it all.
> http://tv.msn.com/tv/globes2007/?icid=nctagline1
>
>
>
---------------------------------------------------------------------
> To start a new topic, e-mail:
> users@tomcat.apache.org
> To unsubscribe, e-mail:
> [EMAIL PROTECTED]
> For additional commands, e-mail:
> [EMAIL PROTECTED]
>
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
