Home
>
Default >
How to re-use a PreparedStatement from poolled connections?
How to re-use a PreparedStatement from poolled connections?
November 30Hits:0
Consider poolled connections....
one advantage of poolled connections are resource sharing...preventing the system from creating lots of connections or from creating and destroying similar connections.
Connection and thread pools are specially usefull on multi-threaded servers.
Consider now an application that has a service that logs the number of times a user has connected.
This service is thread-safe, it is synchronized (bad) or it doesn�t have state (good).
Every time a thead is started in this service a connection is requested from the pool que the service finish logging it needs to release the connection back to the pool.
This is done automatically by the Connection wrapper. When we do conn.close(); actually the wrapper is releasing the connection so that other threads and services can use it.
Suppose the log service uses this PreparedStatement: "Update users set accessCount=accessCount+1 where userId=?"
Because we are using connection pooling, every time the service is requested we possibly get a different connection object. Then, we can�t store a PreparedStatement object locally.
If we use a conn.prepareStatement, then we are creating a new PreparedStatement on every request. Isn�t it? Then, we are not able to re-use PreparedStatements.
Does anyone know a pratical way to re-use PreparedStatements in a multi-threaded server? In my case, this is a servlet.
Thanks
Answers
I can only speak about DB2 on the ISeries (AS/400) but to get the benifits of prepared statments it is not required to leave the preperedstatment object open. The first time a prepared statment is prepared against a connection: it takes a significant amount of time, subsiquent prepares for exactly the same SQL take practically no time.
It may be different on with your database and driver but you should have a test!
So if this is the case it is not a problem to create and close a prepared statment with every request, you will get the preformance increase after the 1st time the statment is prepared against the connection in question.
Hope this helps!
Read other 7 answers
Tags:
Related Articles
-
Consider poolled connections.... one advantage of poolled connections are resource sharing...preventing the system from creating lots of connections or from creating and destroying similar connections. Connection and thread pools are specially useful
-
It seems useful (for performance reasons) to reuse a prepared statement in subsequent calls to the database. In WebLogic, however, after each call we return the Connection object to the WebLogic connection pool. A prepared statement is created on a s
-
Hi, i write an application where it is necessary to get a lot of preparedStatements in one connection. The preparedStatements are used only once and they are closed after the usage. But it seems to me that this close has no effect. Depending on the v
-
Hi all guys, Please tell me when we use Statement and when preparedStatement? As many times we use preparedStatement so what is the use of Statement ? And what is the advantage of using preparedStatement ? What is difference between in both ? please
-
hi all, Is there any possible to print a PreparedStatement content? I have the following code: try { PreparedStatement stmt = this.connection.prepareStatement( "Insert into table " +" ( " +" field1, " +" field1 " +&
-
Hi all, I have a problem getting correct data after doing an update. This is the scenario I am selecting some(Eg: All records where Column X = �7� ) records and update a column with a particular value (SET Column X = �c� ) in all these records by goi
-
When I try to INSERT a new (N)CLOB into an Oracle database, all is fine until I use a non-ASCII character, such as an accented roman letter, like the "�" (that's '\u00E9') in "caf�" or the Euro Currency symbol "?" (that's '\u
-
I have read a lot of article about this error I m also having this problem with 8.1.6.0 or 8.1.6.1 oracle db ( and classes112 jdbc drivers ) and my application I have checked that I close every statment,preparedstatment,recordset and connection ( con
-
1.i create a database 2.then i create a jsp page for welcome 3.then another jsp page for view result 4.then i write code for java here is my 1st jsp page <html> <head> <title>login</title> </head> <body bgcolor=pink> &l
-
Hi all, I'm developing a JSP application (powered by Tomcat 4.0.1 in JDK 1.3, in Eclipse 3.3). Among other stuff I have 3 classes interacting with an Oracle database, covering 3 use cases - renaming, adding and deleting an database object. The renami
