Insert values in Postgresql in Java

I try to insert same values in my table and I write this code:

String sql = "INSERT INTO points (gid, osm_id, name, type, geom)
VALUES (?, ?, ?, ?,ST_GeomFromText('POINT(?, ?)', 2100));";
pst = con.prepareStatement(sql);
pst.setInt(1, 40001);
pst.setInt(2, 2);
pst.setString(3, "General");
pst.setString(4, "Heller");
pst.setDouble(5, 69.6);
pst.setDouble(6, 80.3);

pst.executeUpdate();


but is show me an exception

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

If someone can help me.

Solved it by using:

pst.setString(5,"POINT("+lat+" "+log+")" );

"INSERT INTO points (gid, osm_id, name, type, geom)VALUES (?, ?, ?, ?,ST_GeomFromText(?, 2100));"

• This is a JDBC issue -- The "?" is supposed to correspond to a column value, not as a generic substitution tool. Instead, use String.format to format the WKT. – Vince Nov 19 '14 at 17:31
• I try to pass as two double number – vagelis Nov 19 '14 at 17:37
• Right, but that's not how that functionality is supposed to work. – Vince Nov 19 '14 at 18:32
• If lat is latitude and log is longitude, you have to swap them, since the correct axis order for PostGIS is POINT(log lat) – Mike T Nov 20 '14 at 22:01

2 Answers

This:

'POINT(?, ?)'


is within a literal string, so placeholders are not interpreted.

You must instead keep the placeholders outside a literal and use concatenation or function call syntax.

In this case there's actually a point(x,y) function, so you can just write:

POINT(?,?)


without the single quotes, but if that weren't the case and you needed to compose a value you'd instead concatenate:

'POINT('||?||','||?||')'


or replace the whole thing with a single parameter:

?


and send the string POINT(blah,blah), composed client-side, to the server.

This is sort-of the same answer as this, except adapted a bit more for your situation. Basically, you are mixing SQL parameters in WKT. Since you have numeric data, you shouldn't be making WKT anyway, and directly make a point instead.

String sql = "INSERT INTO points (gid, osm_id, name, type, geom) "
+ "VALUES (?, ?, ?, ?, ST_SetSRID(ST_MakePoint(?, ?), 2100));";
pst = con.prepareStatement(sql);
pst.setInt(1, 40001);
pst.setInt(2, 2);
pst.setString(3, "General");
pst.setString(4, "Heller");
pst.setDouble(5, 69.6); // longitude
pst.setDouble(6, 80.3); // latitude
pst.executeUpdate();


Also, if your gid column is a serial type, you should remove it here and let the database assign it.