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));"

Answer 1

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.

Answer 2

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.