PreparedStatement does not read all my PostGIS geography options

Question

I have the following JDBC code. Please note that I am trying 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 get the following exception in the last line of code:

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

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

Answer 1

Your problem is this:

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

is an SQL string literal that simply contains eight question marks. Since this is an SQL string literal, not a single question mark inside it is considered a placeholder. This leaves you with two placeholders: the one at the very beginning of the VALUES list and the one at the very end.

You will have to build your polygon differently. It may be better than ST_GeographyFromText , but, alas, I do not know what it is, and I do not have PostGIS configured anywhere. If necessary, you can build the POLYGON line manually with standard line breaks, and then use a placeholder for this:

 VALUES (?, ST_GeographyFromText(?), ?) 

The placeholder inside ST_GeographyFromText will be seen as a placeholder, as it is not inside the string literal, and you could user stmt.setString to assign a value to it.

Answer 2

Since mu is too briefly said, the problem is that the placeholder is not recognized inside the quotes.

In the event that building the entire string in Java is not possible (for example, 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, for example:

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

In your case, the solution would be:

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

Not very readable, but it works ...