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

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)?

Answer

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.