Re: [h2] understanding preparestatement reuse
Hi, Actually, within the same session, if you use the exact same SQL statement, then the prepared statement is also re-used. But it's much better to re-use the same PreparedStatement object. Regards, Thomas On Wed, Jun 4, 2014 at 11:55 AM, Noel Grandin wrote: > > On 2014-06-03 08:37 PM, Adam McMahon wrote: > >> >> Now lets say that I want to later reuse that prepared statement, which is >> more accurate: >> >> 1) do I need to keep a reference to the actual PreparedStatment object >> (in this case "ps"). >> > > Yes. > > > If this is the case, do I not close the prepared statement? >> > > No, you do not close it. > > > -- > You received this message because you are subscribed to the Google Groups > "H2 Database" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to h2-database+unsubscr...@googlegroups.com. > To post to this group, send email to h2-database@googlegroups.com. > Visit this group at http://groups.google.com/group/h2-database. > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] understanding preparestatement reuse
Noel, Thanks for helping out. For some reason I thought that JDBC or the database handles caching of prepared statements (perhaps it does on some databases), and I am sure many of the java connection pools take care of this out of the box. But, we use a custom connection pool, and it was easy enough to add a simple statement cache to each connection. For anyone interested in implementing something like this, I found this link helpful: https://code.google.com/p/h2database/source/browse/trunk/h2/src/test/org/h2/samples/CachedPreparedStatements.java -Adam On Wednesday, June 4, 2014 5:55:42 AM UTC-4, Noel Grandin wrote: > > > On 2014-06-03 08:37 PM, Adam McMahon wrote: > > > > Now lets say that I want to later reuse that prepared statement, which > is more accurate: > > > > 1) do I need to keep a reference to the actual PreparedStatment object > (in this case "ps"). > > Yes. > > > If this is the case, do I not close the prepared statement? > > No, you do not close it. > > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
Re: [h2] understanding preparestatement reuse
On 2014-06-03 08:37 PM, Adam McMahon wrote: Now lets say that I want to later reuse that prepared statement, which is more accurate: 1) do I need to keep a reference to the actual PreparedStatment object (in this case "ps"). Yes. If this is the case, do I not close the prepared statement? No, you do not close it. -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
[h2] understanding preparestatement reuse
Hi, I frequently use PreparedStatements, but I am a bit unsure how to properly "reuse" them. I create a statement String sql = "select * from users where score>?"; PreparedStatement ps = con.prepareStatement(sql); ps.setInt(1, n); ResultSet rs = ps.executeQuery(); // do stuff rs.close(); ps.close(); con.close(); Now lets say that I want to later reuse that prepared statement, which is more accurate: 1) do I need to keep a reference to the actual PreparedStatment object (in this case "ps"). If this is the case, do I not close the prepared statement? 2) can I "reuse" the preapred statment by just sending the same sql String when creating a PresparedStatement from a connection. In other words, does the programmer need to keep around references of prepared statement objects, or does the database (in this case H2) keep a cache based on the parametrized sql String that is sent to the connection. Thanks for any help. -Adam -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to h2-database+unsubscr...@googlegroups.com. To post to this group, send email to h2-database@googlegroups.com. Visit this group at http://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.