Mailing-List: contact users-help@tomcat.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Tomcat Users List" <users@tomcat.apache.org>
Received-SPF: pass (herse.apache.org: local policy)
Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Subject: RE: jsp optimization for db driver load and connection
Date: Mon, 4 Dec 2006 14:00:41 -0600
Message-ID: <CCB7F2CDB16FAF48A0DD0490AFABFDCF1DFFE3@DFW1WN05.atmosenergy.com>
Thread-Topic: jsp optimization for db driver load and connection
Thread-Index: AccX1w0Er1Sl4gnrT7mJGv+hA/LkRAAA+j0Q
From: "Miller, Steve" <Steve.Miller@atmosenergy.com>
To: "Tomcat Users List" <users@tomcat.apache.org>
X-AtmosEnergy-Mail-1-MailScanner-Information: Please contact the HelpDesk for
 more information
X-AtmosEnergy-Mail-1-MailScanner: Found to be clean
X-AtmosEnergy-Mail-1-MailScanner-SpamCheck: not spam (whitelisted),
	SpamAssassin (not cached, score=-4.399, required 3,
	autolearn=not spam, ALL_TRUSTED -1.80, BAYES_00 -2.60)
X-AtmosEnergy-Mail-1-MailScanner-From: steve.miller@atmosenergy.com
X-Virus-Checked: Checked by ClamAV on apache.org

Ugh. Model 1 architecture :-)

Connection pooling is the way to go here, definitely. Setup Tomcat to do
it for you automatically on startup. Then, just grab a connection from
the pool when you need it. Some good documentation on setting up and
configuring Tomcat to do that is at
http://tomcat.apache.org/tomcat-5.5-doc/jndi-datasource-examples-howto.h
tml

In you JSP then, all you would have to write is (after importing
java.sql.* and javax.naming.*):

            Context initContext =3D new InitialContext();
            Context envContext =3D
(Context)initContext.lookup("java:/comp/env");
            DataSource ds =3D (DataSource)envContext.lookup("jdbc/foo"); =

		Connection conn =3D ds.getConnection();
		Statement stmt =3D
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
ResultSet.CONCUR_READ_ONLY);
		ResultSet rs =3D stmt.executeQuery(query1);
		//....run iterator loop and display stuff
		ResultSet rs =3D stmt.executeQuery(query2);
		//....run iterator loop and display stuff
		.....
		.....

		rs.close();
		conn.close(); //returns connection to tomcat-managed
pool...always a good practice to insert this!!


The advantage to this of course is that several database connections are
established when tomcat is started and then placed into a pool. All you
do is grab one that exists....saving a *considerable* amount of
transaction time and thus speeding up your page loads. In addition,
should the database parameters change such as URL, username, or
password, then you only have to change it in one place..the server.xml
file. This is also important if you move between dev/test/prod
environments and have to point at different database instances.=20

I don't know how many jsp pages you'll have to alter, but I would
suggest looking at using a bean that does all of the stuff I wrote out
above and returns an iterator or result set through a method. Then, do
the bean stuff in the jsp code (sorry...running out of time here...lol).


Again, I don't see any other way to improve performance within the
scope/limitations you have defined.=20

Steve Miller
"Grabbing A Tomcat By The Tail"

---------------------------------------------------------------------
To start a new topic, e-mail: users@tomcat.apache.org
To unsubscribe, e-mail: users-unsubscribe@tomcat.apache.org
For additional commands, e-mail: users-help@tomcat.apache.org