7

We all know that we should rather reuse a JDBC PreparedStatement than creating a new instance within a loop.

But how to deal with PreparedStatement reuse between different method invocations? Does the reuse-"rule" still count?

Should I really consider using a field for the PreparedStatement or should I close and re-create the prepared statement in every invocation (keep it local)? (Of course an instance of such a class would be bound to a Connection which might be a disadvantage in some architectures)

I am aware that the ideal answer might be "it depends".
But I am looking for a best practice for less experienced developers that they will do the right choice in most of the cases.

Community
  • 1
  • 1
MRalwasser
  • 15,605
  • 15
  • 101
  • 147

4 Answers4

13

Of course an instance of such a class would be bound to a Connection which might be a disadvantage

Might be? it would be a huge disadvantage. You'd either need to synchronize access to it, which would kill your multi-user performance stone-dead, or create multiple instances and keep them in a pool. Major pain in the ass.

Statement pooling is the job of the JDBC driver, and most, if not all, of the current crop of drivers do this for you. When you call prepareStatement or prepareCall, the driver will handle re-use of existing resource and pre-compiled statements.

Statement objects are tied to a connection, and connections should be used and returned to the pool as quickly as possible.

In short, the standard practice of obtaining a PreparedStatement at the start of the method, using it repeatedly within a loop, then closing it at the end of the method, is best practice.

skaffman
  • 398,947
  • 96
  • 818
  • 769
  • I don't get why this is a big deal. You have one thread per Connection. Each service class (containing your CRUD methods) constructs PreparedStatements up-front and reuses them across all the methods. The PreparedStatements get closed when the connection is closed. – Gili Jan 19 '11 at 00:53
6

Many database workloads are CPU-bound, not IO-bound. This means that the database ends up spending more time doing work such as parsing SQL queries and figuring out how to handle them (doing the 'execution plan'), than it spends accessing the disk. This is more true of 'transactional' workloads than 'reporting' workloads, but in both cases the time spent preparing the plan may be more than you expect.

Thus it is always a good idea, if the statement is going to be executed frequently and the hassle of making (correct) arrangements to cache PreparedStatements 'between method invocations' is worth your developer time. As always with performance, measurement is key, but if you can do it cheaply enough, cache your PreparedStatement out of habit.

Some JDBC drivers and/or connection pools offer transparent 'prepared statement caching', so that you don't have to do it yourself. So long as you understand the behaviour of your particular chosen transparent caching strategy, it's fine to let it keep track of things ... what you really want to avoid is the hit on the database.

David Bullock
  • 6,112
  • 3
  • 33
  • 43
0

Yes it can be reused, but I believe this only counts if the same Connection object is being used and if you are using a Database Connection Pool (from within a Web Application, for example) then the Connection objects will be potentially different each time.

I always recreate the PreparedStatement before each use within a Web Application for this reason.

If you aren't using a Connection Pool then you are golden!

trojanfoe
  • 120,358
  • 21
  • 212
  • 242
0

I don't see the difference: If I execute the same statement repeatedly against the same connection, why not reuse the PreparedStatement in any way? If multiple methods execute the same statement, then maybe that statement needs to be encapsulated in its own method (or even its own class). That way you wouldn't need to pass around a PreparedStatement.

Joachim Sauer
  • 302,674
  • 57
  • 556
  • 614
  • 1
    Since a PreparedStatement "belongs" to a specific Connection you just cannot reuse it among different Connections. Of course you can reuse some code that will be executed along different Connections, but the good thing about a PreparedStatment is to leave it prepared once and reuse it, which is not possible once you use a different Connection. – antonio.fornie Dec 12 '13 at 10:29