connection pooling dilemma

[josh]

Hi, I wrote an application that interact with a database and first
every page like

isert_db.jsp
delete_db.jsp

had the following code:

Class.forName((String)session.getAttribute("driver_name"));

conn =
DriverManager.getConnection((String)session.getAttribute("db_url"),
(String)session.getAttribute("username"),
(String)session.getAttribute("pwd"))

so every time I reloaded the driver and so on...

so I learned some concepts to configure Tomcat to use connection
pooling to improve my db application
and now every page has the following code:

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

but the mystery for me is that the first time I load the page I have a
net speed measurement of 200 to 400 ms
and then a speed of 10 to 20 ms. But the same I have with no
connection pooling! How is possible?

May be a JNDI lookup is the problem?

How can I set only once the lookup in JNDI and have for every page a
DataSource object?

 

[Lew]

Hi, I wrote an application that interact with a database and first
every page like

isert_db.jsp
delete_db.jsp

had the following code:

Class.forName((String)session.getAttribute("driver_name"));

conn =
DriverManager.getConnection((String)session.getAttribute("db_url"),
(String)session.getAttribute("username"),
(String)session.getAttribute("pwd"))

so every time I reloaded the driver and so on...

You don't actually reload the driver, nor do you need to call the forName()
for the driver more than once in an application.

Subsequent calls to load a class that's already loaded through the same class
loader do not reload the class.

so I learned some concepts to configure Tomcat to use connection
pooling to improve my db application
and now every page has the following code:

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

but the mystery for me is that the first time I load the page I have a
net speed measurement of 200 to 400 ms
and then a speed of 10 to 20 ms. But the same I have with no
connection pooling! How is possible?

Do you mean the first time after the application has started in Tomcat? If
so, then most likely you are seeing the overhead to translate the JSP into
Java and compile it into bytecode. You might see this go away if you
pre-compile the JSPs before executing the app.

What is the mystery for me is why you have any Java code (a.k.a. "scriptlet")
in your JSP at all.

 

[Robert Klemme]

What is the mystery for me is why you have any Java code (a.k.a.
"scriptlet") in your JSP at all.

Absolutely. There is another thread about passing on a "connection"
through JSP's which shows similar bad pratices. I recommend all web
developers to read and understand MVC, for example from this page

http://www.ibm.com/developerworks/library/j-struts/

It's a bit dated but the concept part is pretty clear.

Cheers

robert

 

[Lew]

Absolutely. There is another thread about passing on a "connection"
through JSP's which shows similar bad pratices. I recommend all web
developers to read and understand MVC, for example from this page

http://www.ibm.com/developerworks/library/j-struts/

It's a bit dated but the concept part is pretty clear.

<http://en.wikipedia.org/wiki/Model-view-controller>
gives a good introduction and a host of links about MVC.

The Struts implementation is really the Sun "Model 2" limited MVC model.
Full-blown MVC is more complex and useful than that. Java Server Faces (JSF)
is a more full-blown MVC implementation.

 

[josh]

You don't actually reload the driver, nor do you need to call the forName()
for the driver more than once in an application.

Subsequent calls to load a class that's already loaded through the same class
loader do not reload the class.




Do you mean the first time after the application has started in Tomcat? If
so, then most likely you are seeing the overhead to translate the JSP into
Java and compile it into bytecode. You might see this go away if you
pre-compile the JSPs before executing the app.

What is the mystery for me is why you have any Java code (a.k.a. "scriptlet")
in your JSP at all.

in every jsp file I make

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

is there a way to init a DataSource one time only and than reuse it in
all my pages?

should it improve further on my appl?

-- Josh

 

[Lew]

in every jsp [sic] file I make

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

is there a way to init a DataSource one time only and than reuse it in
all my pages?

should it improve further on my appl?

Yes, follow Robert Klemme's advice to look into MVC and take all the scriptlet
out of your JSPs.

This kind of thing is much easier to manage from a plain-ol' Java class. You
could init() the DataSource from a static initializer or such.

There are also a number of declarative ways to create a DataSource, e.g., via
the context.xml of a Tomcat app.

 

[Robert Klemme]

in every jsp [sic] file I make

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

is there a way to init a DataSource one time only and than reuse it in
all my pages?

should it improve further on my appl?

Yes, follow Robert Klemme's advice to look into MVC and take all the
scriptlet out of your JSPs.

This kind of thing is much easier to manage from a plain-ol' Java
class. You could init() the DataSource from a static initializer or such.

There are also a number of declarative ways to create a DataSource,
e.g., via the context.xml of a Tomcat app.

And of course one can use listeners for various events, namely
application startup, shutdown, session creation, destruction, request
processing (filters) etc. But IMHO the best way is the declarative as
you pointed out.

Kind regards

robert