How to rewrite SQL statements for PreparedStatements?

Question

I want to rewrite the folling SQL statement into a dynamic PreparedStatement java:

UPDATE table SET field='C' WHERE id=3;
INSERT INTO table (id, field)
       SELECT 3, 'C'
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

Especially I don't know how to rewrite the SELECT 3, 'C' line to dynamic statements.

UPDATE table SET name=:name WHERE id=:id
INSERT INTO table (id, name)
       SELECT 3, 'C'  <-- how could I rewrite these values to take the dynamic values from PreparedStatement?
       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=3);

My object, similar:

@Entity
public class MyEntity {
   long id, String name;
}

through jdbc? or what kind of abstraction are you using? – eis Jan 09 '15 at 15:14 — eis, Jan 09 '15 at 15:14

score 7 · Accepted Answer · 2015-01-09T15:30:47.550

7

You have to do this in two statements:

PreparedStatement update = connection.prepareStatement(
    "UPDATE table SET field=? WHERE id=?");

PreparedSTatement insert = connection.prepareStatement(
    "INSERT INTO table (id, field) \n" +
    "       SELECT ?, ? \n" +
    "       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=?)";

update.setString(1, "C");
update.setInt(2, 3);
update.executeUpdate();

insert.setInt(1, 3);
insert.setString(2, "C");
insert.setInt(3, 3);
insert.executeUpdate();

connection.commit();

Edit I forgot that Postgres allows multiple SQL statements in one PreparedStatement:

PreparedStatement stmt = connection.prepareStatement(
    "UPDATE table SET field=? WHERE id=?;\n" + 
    "INSERT INTO table (id, field) \n" +
    "       SELECT ?, ? \n" +
    "       WHERE NOT EXISTS (SELECT 1 FROM table WHERE id=?)";

stmt.setString(1, "C");
stmt.setInt(2, 3);
stmt.setInt(3, 3);
stmt.setString(4, "C");
stmt.setInt(5, 3);
stmt.executeUpdate();

connection.commit();

Edit2 the only way I can think of where you only specify the values once is this:

String sql = 
  "with data (id, col1) as ( \n" +
  "  values (?, ?) \n" +
  "), updated as ( \n" +
  " \n" +
  "  UPDATE foo  \n" +
  "     SET field = (select col1 from data)  \n" +
  "  WHERE id  = (select id from data) \n" +
  ") \n" +
  "insert into foo  \n" +
  "select id, col1  \n" +
  "from data  \n" +
  "where not exists (select 1  \n" +
  "                  from foo \n" +
  "                  where id = (select id from data))";


pstmt = connection.prepareStatement(sql);
pstmt.setInt(1, 3);
pstmt.setString(2, "C");
pstmt.executeUpdate();

edited Jan 09 '15 at 15:30

answered Jan 09 '15 at 15:19

which is the case also without prepared statements. OP has there two statements to begin with, separated by semicolon. – eis Jan 09 '15 at 15:19
Is there no chance of backreference, so I don't have to set the same param for 1+3? Something like indexed wildcards like `?1` to reference the first param? – membersound Jan 09 '15 at 15:23
1

@membersound: no there is no such feature in plain JDBC. – Jan 09 '15 at 15:24
1

@membersound: there is a way to do this with a single statement and only two placeholders – Jan 09 '15 at 15:31
Technically, executing two statements using a single prepared statements is a violation of the JDBC spec (although, you do need to read between the lines). – Mark Rotteveel Jan 09 '15 at 15:56
@MarkRotteveel: that's probably why I always forget that Postgres allows this. However the third solution only runs a single statement anyway – Jan 09 '15 at 15:58
The third one is very interesting, I didn't know you could do that with CTEs in Postgres; not sure what I think of it though, somehow it feels dirty ;) – Mark Rotteveel Jan 09 '15 at 16:00
@MarkRotteveel: that's a variation of this solution: http://stackoverflow.com/a/8702291/330315 – Jan 09 '15 at 16:56
Don't you need to reference the `updated` CTE (it is in the linked question) or is its existence sufficient for the `UPDATE` to get called? – Mark Rotteveel Jan 09 '15 at 16:59

score 1 · Answer 2 · answered Jan 09 '15 at 15:16

1

You would do:

SELECT ?, ?

And then with the resulting PreparedStatement object, you would bind your parameters:

myPreparedStatement.setLong(1, 3); //The first ? found
myPreparedStatement.setString(2, "C");

This is more advantageous when you are doing loops or using variables passed to you through a method, rather than constants you already know in the code (in case that would come up)

answered Jan 09 '15 at 15:16

Rogue

11,105
5
45
71

And how would you rewrite the full statement then? Because when I set the parameter index of the questionmarks, I cannot use named parameters in the sql query anymore. – membersound Jan 09 '15 at 15:17
@membersound perhaps you could simply have a third parameter and set it with the `long` again? I cannot remember if there is some sort of backreferencing for `?`s in PreparedStatements. – Rogue Jan 09 '15 at 15:20

How to rewrite SQL statements for PreparedStatements?

2 Answers2