Is it possible to create a prepared statement and reuse it later with Java under postgres?

user_3175046 0
Viewed 1356

I am trying to test a few things out with postgres. I want to know if it's possible to create define a PreparedStatement such as

String statement = "Insert into table_one values (?)";
PreparedStatement insert = con.prepareStatement(statement);
insert.execute()
// tell postgres to create this Prepared statement without

When I tried, I received an error stating:
SQLState: 22023 No value specified for parameter 1

Additionally, is if I were to do the following instead

    PreparedStatement insert = con.prepareStatement(statement);
    insert.setInt(1, 10); //insert into table_one values (10);
    insert.execute()
    .... //commit other transactions to postgres
    insert.setInt(1, 20);
    insert.execute();

Will postgres "remember"/has registered the above as a prepared statement (I'm not talking about caching the actual statement)?

prepared-statement postgresql sql java
2 Answers

When you use a java.sql.PreparedStatement with the PostgreSQL JDBC driver, it will at first not create a real prepared statement on the database server, but just construct a simple SQL statement to send to the database server. Only at the sixth execution it will think that it is worth the effort to create a named prepared statement on the server that it reuses for future executions.

You can use the prepareThreshold connection property to influence the behavior, see the documentation. So to make your second example use a server prepared statement, you would have to lower the threshold to 0. This is useful only if you know you will reuse all your prepared statements; consider that prepared statements are often used for other purposes like avoiding SQL injection problems.

On the database server there is a similar functionality: the first five times a prepared statement is executed, PostgreSQL computes a custom plan for it. Only at the sixth execution it will consider switching to a generic plan instead, so that you can avoid the overhead of planning from then on. This can be influenced with the PostgreSQL parameter plan_cache_mode from v12 on.

So, with the default settings, it will take ten executions of a java.sql.PreparedStatement before you see a performance increase from avoiding planning costs.

user_64643080

As your second snippet shows, yup, you can do that.

You can create a preparedstatement, then wait ages and ages, then set each 'parameter' (call e.g. .setInt(1, ...), for every question mark in the SQL string you passed), and call one of the execute methods, and then... wait some more ages and do that again, as many times as you like.

A preparedstatement lives as long as the connection lives, or until you close it, which you should. The real rub lies there: Closing them. You do have to close them explicitly. They are like files and net sockets that way.

I you mess that up, your app won't immediately crash and no tests will fail. However, the connection is permanently 'damaged', as it has only room for a limited amount of prepared statements. As you keep making more and never closing them, eventually you run out, and bizarre SQL Exceptions will occur. Your app is a ticking time bomb. This is the worst class of bugs: Hard to test for, will inevitably explode your app, exactly as things get busy. When bossman starts asking the legal team if they can sue you for gross negligence, that's when you go: Oof, maybe I shouldn't toy with code styles that lead to bugs that cannot easily be tested for, are hard to spot, and will only cause issues riight when things are busy.

So, follow (rigidly) protocols to avoid such things:

  1. Any resource that is created (via either new SomeResource(), or via a method that is crystal clear that it is a creator, such as Files.newInputStream, must be closed by the same code, OR, that code must in turn be a closable resource.
  2. Sharpening up #1, unless you are a closable entity yourself, you can only open resources in a try-with block.

In other words, either you do this:

try (PreparedStatement ps = ....) {
    // use ps here, as often as you want....
} // ps is closed here

or you do this:

class WhateverTool implements AutoClosable {
    private PreparedStatement ps; // long-lived

    @Override public void close() throws SQLException {
        if (ps != null) ps.close();
    }
}

and your only interaction with this class is, itself, in the form of:

try (WhateverTool t = new WhateverTool()) {
    // do whatever you want here...
} // but this is where it ends

And there are no other options allowed in your code base to interact with resources.

Java doesn't enforce this, but you are definitely going to have a buggy mess if you don't follow these rules.

user_7686440
Related