I have one doubt about usage of preparedStatement. when preparedStatement is used then SQL Statement is cached in the memory. My doubt is whether it cached at COnnection level or at database level. i.e. if i execute an SQL statement with preparedStatement object using one connection (at this time SQL statement is cached in memory) and then close the connection object. If i open a new connection object and execute the same SQL statement then earlier cached statement will be used or if this statement is cached again. I personally believes that caching is done at connection level. Will appeciate any detail information for the same. Thanks & regards, Tarun Dewan.
Originally posted by tarun: My doubt is whether it cached at COnnection level or at database level. [...] I personally believes that caching is done at connection level.
This is, at least in principle, implementation-dependent. The database I'm most familiar with (Oracle) implements statement caching at the database level; completely independently from the Java driver, even. - Peter
Joined: Sep 05, 2002
Hi Peter, As per your message caching is implementation dependent. This means whether i use statement object or preparedStatement object, this will not make any difference. Pls. clarify. Thanks & regards, Tarun Dewan. :roll:
Peter den Haan
Joined: Apr 20, 2000
It makes all the difference! Even in the case of Oracle, where the cache is in fact in the database, you will generally need to use PreparedStatement for it to work properly. The Oracle statement cache only works if your statement text is exactly the same. Now considerThis won't use the query cache because the two statements are different. Compare this withThe second query will re-use the execution plan of the first one because the statement text SELECT * FROM FOO WHERE BAR=? is exactly the same. - Peter [ May 20, 2003: Message edited by: Peter den Haan ]