PreparedStatement is not reading all my parameters for PostGIS Geography

Question

I have the following JDBC code. Note that I am attempting to use PostGIS geography:

PreparedStatement stmt = db.prepareStatement("INSERT INTO " +
                    "source_imagery (image_path, boundary, image_time)" +
                    " VALUES (?, ST_GeographyFromText('POLYGON((" +
                    "? ?, ? ?, ? ?, ? ?))'), ?)");

            stmt.setString(1, file.getAbsolutePath());
            stmt.setDouble(2, bounds.getY());
            stmt.setDouble(3, bounds.getX());
            ...

I am getting the following exception on the last line of code:

org.postgresql.util.PSQLException: The column index is out of range: 3, number of columns: 2.

I understand that it thinks I only have 2 parameters there, but you can see that I intended there to be 10. I'm not sure why it is not reading any of the parameters within the POLYGON. I know that this SQL statement works if I use it directly in the database, but I'm not sure what I have to change to make it work in my Java code. Any ideas?

Answer 1

Your problem is that this:

'POLYGON((? ?, ? ?, ? ?, ? ?))'

is an SQL string literal that just happens to contain eight question marks. Since that is an SQL string literal, none of the question marks inside it are considered to be placeholders. That leaves you with two placeholders: the one at the very beginning of the VALUES list and the one at the very end.

You'll have to build your polygon some other way. There might be a better way than ST_GeographyFromText but, alas, I don't know what it is and I don't have PostGIS set up anywhere. If necessary, you can build the POLYGON string by hand with standard string wrangling and then use a placeholder for it:

VALUES (?, ST_GeographyFromText(?), ?)

The placeholder inside ST_GeographyFromText will be seen as a placeholder as it isn't inside a string literal and you could user stmt.setString to give it a value.

Answer 2

As mu is too short correctly said the problem is that the placeholder is not recognized inside inside the quotes.

In case building the whole string in Java is not possible (e.g. in my case it was too intrusive) you can work around the problem by moving the placeholder outside the literal and then using the PgSQL string concatenation operator, like this:

ST_GeographyFromText('SRID=4326;POINT(' || ? || ' ' || ? || ')')

In your case the solution would be:

ST_GeographyFromText('POLYGON((' || ? || ' ' || ? || ', ' || ? || ' ' || ? ||
    ', ' || ? || ' ' || ? || ', ' || ? || '' || ? || '))')

Not very readable, but it works...