How to retrieve a java object from table that has Geometry type using sql2o

Question

I'm using sql2o to map PostgreSQL rows to java objects.

I have a table that has a column of type POINT and a java model with two Doubles, both representing latitude and longitude.

To save an object in database I'm doing something like this:

public void save()
{
    try(Connection con = DB.sql2o.open()) {
        String sql = "INSERT INTO my_table(geom) VALUES (SetSRID(CAST(:geom AS geometry), 4326));";
        con.createQuery(sql)
            .addParameter("geom", "POINT(" + this.latitude + " " + this.longitude+ ")")
            .executeUpdate();
    }
}

But now I want to retrieve my stored object. How can I do this? How can I map a POINT to two Doubles.

Answer 1

I managed to get the answer to my own question, here is the code:

public List<MyModel> models()
{
    try(Connection con = DB.sql2o.open()) {
        String sql = "SELECT ST_X(geom) AS latitude, ST_Y(geom) AS longitude FROM my_table;";
        List<MyModel> models = con.createQuery(sql)
            .executeAndFetch(MyModel.class);

        return models;
    }
}