File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes JSP and JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JSP and JDBC" Watch "JSP and JDBC" New topic
Author

JSP and JDBC

Mani Balasubramanian
Greenhorn

Joined: Jan 03, 2002
Posts: 15
Hi,
I have a jsp page in which i am fetching some data from the database and showing it on the screen. The page gets reloaded every 5 mins. I am using stmt.executeQuery() to fetch the datas. So, every time the page is reloaded, the query has to be executed in the backend.
Is there any way to cache a Statement object and reuse it again (without compiling the query again in database)?
Will the use of preparedStatement solve the problem?
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Depending on the database you use and its support for PreparedStatements, yes, it should prevent the database from having to continually having to compile the statement.
There is a short article from TheServerSide here
Dave.
Mani Balasubramanian
Greenhorn

Joined: Jan 03, 2002
Posts: 15
Originally posted by David O'Meara:
Depending on the database you use and its support for PreparedStatements, yes, it should prevent the database from having to continually having to compile the statement.
There is a short article from TheServerSide here
Dave.


Thanks for your reply. I am using Oracle 8i. In my case, i am not calling the same query many times in a JSP page. But I am reloading the entire JSP page, which means that i am once again creating another instance of preparedStatement object. Is there any way to avoid this?
Thanks again.
prav mane
Greenhorn

Joined: Nov 21, 2001
Posts: 16
am not sure if this helps.
put the object in the session for the first time and then keep pulling it out of the session everytime you refresh.
Since this page gets refreshed throught out.
session scope can be declared as "application".
prav.
Originally posted by Mani Balasubramani:


Thanks for your reply. I am using Oracle 8i. In my case, i am not calling the same query many times in a JSP page. But I am reloading the entire JSP page, which means that i am once again creating another instance of preparedStatement object. Is there any way to avoid this?
Thanks again.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

My usual reply to this (possibly overstated) is AAaaaaaarrrrggghhhhh don't put database resources on the session!!!
If you store a PreparedStatement, then you need to put the Connection as well, then you start getting into trouble.
Karthik Guru
Ranch Hand

Joined: Mar 06, 2001
Posts: 1209
i guess the person meant that we need to cache the PreparedStatement somewhere.
Probably one solution could be to maintain a pool of PreparedStatements.
can use a singeon here i guess...
class PreparedPool{
//initialize the pool with prepared statement
PreparedPool getPreparedPool()
getPreparedStatement(String someName)
}
this way you need not create prepared statement everytime.
In the JSP you can get the statement from the PreparedPool.
karthik.
Originally posted by David O'Meara:
My usual reply to this (possibly overstated) is AAaaaaaarrrrggghhhhh don't put database resources on the session!!!
If you store a PreparedStatement, then you need to put the Connection as well, then you start getting into trouble.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

If you have Connection pooling and support for PreparedStatements at the database end, I'm doubtful of the advantage to caching PreparedStatements as well, since all you'd be doing is reducing object creation (not worth while for its own sake). ( I think the impact of creating a new PreparedStatement would be driver dependant and not consistant.)
An example of why not to do it is that transactions are managed by the database on a per Connection-basis and therefore if you cache PreparedStatements (and leave the Connection open) you won't be able to share these transactions.
Dave.
Mani Balasubramanian
Greenhorn

Joined: Jan 03, 2002
Posts: 15
I think the singleton obj doesn't solve the problem either, because when u need to cache the preparedstmt u also need to cache the corresponding connection object in the singleton object, ur thoughts on this please
Karthik Guru
Ranch Hand

Joined: Mar 06, 2001
Posts: 1209
consider this...
1. I create a PreparedStatement p1 using connection c1 supplying a query q1.
2. Then i use p1 and set it to null or it gets GCed.
3. Sometime later i create p2 using connection c2 supplying the same query q1.
will the database recognize the query and not compile it again? or does it compile it again?
This was an answer from one of my colleagues:
.....
I guess it is highly database dependent. The database may do both the things
depending on how big the cache is and the difference in creation of the prepared
statements p1 and p2.
The other possibility is as soon as you set the PreparedStatement p1 to null ,
the database may throw away the compiled query.
.....
So looking at these possiblities the only solution is a stored procedure (can i call this extreme :-)) which again is not portable across databases...now that's ok i guess.
this enables us to maintain a connection pool (now nothing needs to be cached) and improve performance as well.
let's keep this dicussion going.
David O'Meara
Rancher

Joined: Mar 06, 2001
Posts: 13459

Firstly I'd like to re-iterate that PreparedStatements are represented by driver objects but are supported and compiled at the database end. GCing the PreparedStatement or whatever happens to the driver and doesn't have to have any effect on the database (ie the PS can still exist compiled on the db)
How about this scenario though:
User A gets a Connection c1 from the database and uses it to create PreparedStatement ps1 (based on query q1). User A finishes the query and releases the db resources.
User two wants to execute the exact same query but with different values. They get their own Connection c2, but when they ask for a PreparedStatement the driver recognises they are the same and returns the 'cleaned' instance of ps1.
The point is that either way (if ps1 is returned or a new PreparedStatement ps2), we only really care what happens at the database. If the db supports PreparedStatements the query won't be compiled again and the query will execute faster. If the db doesn't support them, it should still have the same performance (worst case) as a Statement. Therefore I argue that you always use PreparedStatements over Statements and that there is little to no advantage to caching them.
next?
Karthik Guru
Ranch Hand

Joined: Mar 06, 2001
Posts: 1209
Originally posted by David O'Meara:

The point is that either way (if ps1 is returned or a new PreparedStatement ps2), we only really care what happens at the database. If the db supports PreparedStatements the query won't be compiled again and the query will execute faster. If the db doesn't support them, it should still have the same performance (worst case) as a Statement. Therefore I argue that you always use PreparedStatements over Statements and that there is little to no advantage to caching them.
next?

okay :-) to conclude it..
so what you are pointing out is that
IF a database supports PreparedStatements,
and i create a prepared statement.
The next time i try creating another prepared statement with the same query , the database would NOT re-compile it.
So This DEFINITELY eliminates this situation:
>> The other possibility is as soon as you set
>> the PreparedStatement p1 to null ,
>> the database may throw away the compiled query.
Stanley Tan
Ranch Hand

Joined: May 17, 2001
Posts: 243
That's right. The database will only compile the query once and you just supply different values next time (think bind variables in Oracle).
You should also be aware that PreparedStatements take a lot longer to create than regular Statements. So if you are really only going use a query once, a Statement yields better results.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JSP and JDBC
 
Similar Threads
Jsp page is not displaying anything
Setting request attributes on reloading page
reloading of JSP page
How much to use session variables?
Reload different image for every second