Parse string as json array from Postgre

Question

I have a table in PostgreSQL with 2 columns - Id and coord. Column "coord" - geo coordinates stored as a string in JSON format.

Example:

[{"lat":49.09693425316379,"lng":33.61747393628419},{"lat":49.11835977646441,"lng":33.638456496907},{"lat":49.12103137811804,"lng":33.63866144845382},{"lat":49.09694682809236,"lng":33.61746879914138},{"lat":49.08920750204137,"lng":33.61734796797724},{"lat":49.07643862058337,"lng":33.61246117651179}]

How to send such string as JSON Array of objects(POST request).

Entity without getters and setters

public class Lepcoord implements Serializable {

private static final long serialVersionUID = 1L;
@Id
@Basic(optional = false)
@NotNull
@Size(min = 1, max = 30)
@Column(name = "tplnr")
private String tplnr;
@Size(max = 2147483647)
@Column(name = "coord")
private String coord;

Controller

@POST
@RequestMapping(value= "/lep/{voltage}", method = RequestMethod.POST, headers = "Accept=application/json")
@ResponseBody
public ResponseEntity<List<Lepcoord>> lep (@PathVariable  String voltage) 
{
    return new ResponseEntity<>(gisDaoService.lep(voltage), HttpStatus.OK);
}

And service

 @Transactional(readOnly = true)
    public List <Lepcoord> lep (String voltage) {
         Query  query = this.em.createQuery(    
             " From Lepcoord ");   
    List <Lepcoord> rez = null;
        try {
                rez = (List<Lepcoord>) query.getResultList();
            } catch (PersistenceException r) {
                return null;
            }
            return rez;
        }

Hibernate cant handle json type If i storeing coord as json in Postgre. May be someone knows easier way. Not to write own classes to work with Postgres json type

It's very clear what you are actually asking. How to parse? How to send over AJAX? Do you have the code of what you've already tried so we can understand the problem? — JCooke
– JCooke, Commented Aug 18, 2017 at 12:07
Updated my question. All is correct. But as you can see answer will be - tplnr :"String", coord :"String". I need to send coord as json array. — Eugene
– Eugene, Commented Aug 18, 2017 at 12:35

Omid P · Accepted Answer · 2017-08-19 05:25:51Z

0

You are using Hibernate so it is good to use a custom UserType which knows how to handle json.

create a hibernate usertype

public class GeoJsonType implements UserType

{

    protected static final int[] SQL_TYPES = { java.sql.Types.VARCHAR };

    @Override
    public int[] sqlTypes()
    {
        return SQL_TYPES;
    }

    @Override
    public Class returnedClass()
    {
        return GeoEntity.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException
    {
        if (x == y)
        {
            return true;
        }
        else if (x == null || y == null)
        {
            return false;
        }
        else
        {
            return x.equals(y);
        }
    }

    @Override
    public int hashCode(Object x) throws HibernateException
    {
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, Object owner)     throws HibernateException, SQLException

    {

//        if (rs.wasNull())
//        {
//            return null;
//        }

    //this is your json stored in db

        String rsArr = rs.getString(names[0]);

        if (rsArr == null)
            return null;        
        GeoEntity detailAttr = JSON.toObject(rsArr, GeoEntity.class, null);
        return detailAttr;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index) throws HibernateException, SQLException
    {
        if (value == null)
        {
            st.setNull(index, SQL_TYPES[0]);
        }
        else
        {
            //when stroing object into db convert it to json
            GeoEntity castObject = (GeoEntity) value;
            String json = JSON.toJson(castObject);
            st.setString(index, json);
        }
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException
    {
        return value;
    }

    @Override
    public boolean isMutable()
    {
        return true;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException
    {
        return null;
    }

    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException
    {
        return null;
    }

    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException
    {
        return original;
    }

}

Your Entity.java

@Type(type = "FQN to your GeoJsonType")

@Column(name = "geo")

public GeoEntity getGeo()

{

return geo;

}

edited Aug 19, 2017 at 5:25

answered Aug 19, 2017 at 5:20

Omid P

2114 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eugene Over a year ago

Thank you! But can we do the same with postgresql JSON Processing Functions? May be something like this: query = this.em.createNativeQuery( "CREATE type json_type AS (lat decimal, lng decimal);" + " SELECT tplnr, (json_array_elements(coord)).* FROM Lepcoord");

Omid P Over a year ago

of course you can but first of all you will lose portability then you will lose HQL and have to use only nativequery unless you extends hibernate postgresqlDialect.

Mekicha · Accepted Answer · 2017-08-18 11:59:03Z

0

Postgres supports the json_to_array function that should be of help here. Take a look at the documentation here. Alternatively, there is this answer on SO: How to turn a json array into rows in postgres that could point you in the right direction.

answered Aug 18, 2017 at 11:59

Mekicha

85111 silver badges23 bronze badges

Collectives™ on Stack Overflow

Parse string as json array from Postgre

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related