Native Query with PostGIS Throws HttpMessageNotWritableException

48 views Asked by At

I have two queries in my Spring Data JPA repository, one written in JPQL and another in native SQL. The JPQL query works flawlessly, while the native query throws a HttpMessageNotWritableException. Both queries are nearly identical in terms of the selected fields and conditions.

Here are the queries:

Native Query:

@Query(value = "SELECT " +
        "o.geometry\\:\\:geometry as geometry, " +
        "o.center\\:\\:geometry as center, " +
        "FROM overlay as o " +
        "JOIN company_layer as cl on cl.id = o.layer_id " +
        "WHERE o.ts_overlaytext @@ to_tsquery(:searchString) AND o.disabled = false",
        nativeQuery = true)
List<MyProjection> findWithNativeQuery(@Param("searchString") String searchString);

JPQL Query:

@Query("SELECT " +
        "o.geometry as geometry, " +
        "o.center as center, " +
        "FROM Overlay o " +
        "WHERE o.layer.id = :layerId AND o.disabled = false AND o.layer.company.id = :companyId")
List<MyProjection> findWithJPQLQuery(@Param("layerId") Long layerId, @Param("companyId") Long companyId);

Both queries aim to select data from the Overlay entity, and the only significant difference is the usage of native SQL in the first one.

The JPQL query works as expected, but when I execute the native query, and try to send data to the frontend from the controller, I get the following exception:

org.springframework.http.converter.HttpMessageNotWritableException: Could not write JSON: Cannot project org.geolatte.geom.Polygon to org.locationtech.jts.geom.Geometry; Target type is not an interface and no matching Converter found

What could be causing this issue specifically in the context of the native query?

Any insights or suggestions on resolving this issue would be greatly appreciated. Thank you!

0

There are 0 answers