Home > Default > How to re-use a PreparedStatement from poolled connections?

How to re-use a PreparedStatement from poolled connections?

November 30Hits:0
Advertisement
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

  • How to re-use a PreparedStatement from poolled connections?November 30

    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

  • Reuse of preparedstatements and the connection poolNovember 30

    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

  • PreparedStatement and open cursorsOctober 11

    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

  • Statement or preparedStatementNovember 30

    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

  • Print the query content of PreparedStatementNovember 30

    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 " +&

  • Problem getting correct data from MS Access after doing an UpdateOctober 11

    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

  • Problem with Unicode and Oracle NCLOB fieldsOctober 11

    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

  • What a mess !! : ORA-01000: maximum open cursors exceededOctober 11

    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

  • Error in jsp pageOctober 11

    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

  • Java.lang.VerifyError - Incompatible object argument for function callOctober 11

    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

Copyright (C) 2019 wisumpire.com, All Rights Reserved. webmaster#wisumpire.com 14 q. 0.739 s.