Insert values in Postgresql in Java

Question

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

score 1 · Answer 1 · answered Nov 20 '14 at 00:23

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.

score 1 · Answer 2 · edited Apr 13 '17 at 12:33

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.

Insert values in Postgresql in Java

2 Answers2