Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

JSP and JDBC

 
Mani Balasubramanian
Greenhorn
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1209
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 15
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1209
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13459
Android Eclipse IDE Ubuntu
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1209
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 243
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic