How to store geometry Point in Postgis database using java

Question

I'm trying to store a geometry object into my postgist database, which has a table with a geometry column. I got the geometry value from another table with a geometry column, and I print the value I got previously and it's ok. To store geometry value I use next function:

static void insertaGeometria( Geometry geom, int idInstalacion) throws ClassNotFoundException, SQLException{

        Connection congeom = conectarPGA();

        String geomsql ="INSERT INTO georrepositorio.geometria(id, point) VALUES (?,?)";
        PreparedStatement psSE= congeom.prepareStatement(geomsql);
        psSE.setInt(1, idInstalacion);
        psSE.setObject(2, geom);    

        psSE.execute();
        psSE.close();   
        congeom.close();    
    }

But I always get this error:

org.postgresql.util.PSQLException: Can't infer the SQL type to use for an instance of org.postgis.Point. Use setObject() with an explicit Types value to specify the type to use.

Do anyone know how to store it? ='(

Thanks in advance!

Answer 1

See the manual for the Java client. From this I see two ideas. Try using PGgeometry instead of Geometry types for geom. Then, add the geometry type to the connection congeom:

((org.postgresql.PGConnection)congeom).addDataType("geometry",Class.forName("org.postgis.PGgeometry"));

Answer 2

To my experience I have managed to add point using such expression (note the Point which the request is iterated by is my own class):

    java.sql.Connection conpg;

    try {
/*
* Load the JDBC driver and establish a connection.
*/

        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://localhost:5432/postgis_22_sample";
        conpg = DriverManager.getConnection(url, "postgres", "nypassw");
/*
* Add the geometry types to the connection. Note that you
* must cast the connection to the pgsql-specific connection
* implementation before calling the addDataType() method.
*/
        ((org.postgresql.PGConnection) conpg).addDataType("geometry", Class.forName("org.postgis.PGgeometry"));
        //((org.postgresql.PGConnection)conpg).addDataType("point",Class.forName("org.postgis.Point"));
/*
* Create a statement and execute a select query.
*/
        conpg.setAutoCommit(false);

        for (Point p : points) {

            org.postgis.Point pointToAdd = new org.postgis.Point();
            pointToAdd.setX(p.getLongitude());
            pointToAdd.setY(p.getLatitude());

            //Statement s = conn.createStatement();
            //String geomsql = ;
            PreparedStatement psSE = conpg.prepareStatement("INSERT INTO public.\"poi-point\" (name,geom,leisure) VALUES (?,?,?)");
            psSE.setString(1, p.getDescription());
            psSE.setObject(2, new org.postgis.PGgeometry(pointToAdd));
            psSE.setString(3, "marina");

            psSE.execute();
            //ResultSet r = s.executeQuery("select geom,id from geomtable");
            //while (r.next()) {
  /*
  * Retrieve the geometry as an object then cast it to the geometry type.
  * Print things out.
  */
            //    PGgeometry geom = (PGgeometry) r.getObject(1);
            //    int id = r.getInt(2);
            //    System.out.println("Row " + id + ":");
            //    System.out.println(geom.toString());
            //}
            //s.close();
        }
        conpg.commit();
        conpg.close();
    } catch (Exception e) {
        e.printStackTrace();
    }

maven dependencies to make it working are

    <dependency>
        <groupId>net.postgis</groupId>
        <artifactId>postgis-jdbc</artifactId>
        <version>2.2.0</version>
        <exclusions>
            <exclusion>
                <!-- NOTE: Version 4.2 has bundled slf4j -->
                <groupId>org.slf4j</groupId>
                <artifactId>slf4j-api</artifactId>
            </exclusion>
            <exclusion>
                <!-- NOTE: Version 4.2 has bundled slf4j -->
                <groupId>ch.qos.logback</groupId>
                <artifactId>logback-classic</artifactId>
            </exclusion>
            <exclusion>
                <!-- NOTE: Version 4.2 has bundled slf4j -->
                <groupId>ch.qos.logback</groupId>
                <artifactId>logback-core</artifactId>
            </exclusion>
        </exclusions>

Note, that you not nessesarilly need to exclude the dependencies (it was needed for my own compatiblity)