Is it correct to use @Mappedsuperclass instead of @Entity to not create a table in the database using JPA?

Question

Explaining the problem, I need to consult a function in POSTGRES:

SELECT * from pgr_dijkstra('SELECT gid AS id, source, target, length AS cost FROM ways', 11111, 22222);

"pgr_dijkstra" is a POSTGIS function, not a table. It returns me the shortest path between "source"(11111) and "target(2222)". The result is 6 columns: "seq, path_seq, Node, edge, cost and agg_cost".

Because "pgr_dijkstra" is a function I cannot simply create a class in my code and annotate it with @Entity, also adding @Id to "seq" and creating the other 5 fields (path_seq, Node, edge, cost and agg_cost). This would create a new table in the database. After searching a lot, I found a solution that I believe is far from ideal, which would not be good practice. NOTE: I am using Spring Boot + Java. The value of "source" and "target" will not be fixed, users will send via browser, put fixed only to test this part faster.

POJO class

public class Dijkstra3 {

    public Integer seq;
    public Integer path_seq;
    public BigInteger node;
    public BigInteger edge;
    public double cost;
    public double agg_cost;

    public Dijkstra3(Integer seq, Integer path_seq, BigInteger node, BigInteger edge, double cost, double agg_cost) {
        super();
        this.seq = seq;
        this.path_seq = path_seq;
        this.node = node;
        this.edge = edge;
        this.cost = cost;
        this.agg_cost = agg_cost;
    }

    //GETTERS ...

}

Mapping of the query

Abstract class with my @Sqlresultsetmapping and @Namednativequery. The @Sqlresultsetmapping annotation is mapping the resulting to Dijkstra3.class, which is my POJO. @Namednativequery is my query, which I will use, I point to the map "Dijkstramapping". I wrote down the class with @Mappedsuperclass, if I didn’t I would have to write it down with @Entity and a new table in the database would be created.

@SqlResultSetMapping(
    name = "DijkstraMapping",
    classes = {
        @ConstructorResult(
            columns = {
                @ColumnResult(name="seq", type=Integer.class),
                @ColumnResult(name="path_seq", type=Integer.class),
                @ColumnResult(name="node", type=BigInteger.class),
                @ColumnResult(name="edge", type=BigInteger.class),
                @ColumnResult(name="cost", type=Double.class),
                @ColumnResult(name="agg_cost", type=Double.class)
            },
            targetClass = Dijkstra3.class
        )
    }
)

@NamedNativeQueries({
    @NamedNativeQuery(
         name = "GetDijkstra",
         query = "SELECT * from pgr_dijkstra('SELECT gid AS id, source, target, length AS cost FROM ways', 11111, 22222)",
          resultSetMapping = "DijkstraMapping"
    )
}) 

@MappedSuperclass
public abstract class DijkstraSqlMap{

}

Repository

@Repository
public class TestRepository2 {      

    @PersistenceContext
    EntityManager em;

    public List<Dijkstra3> callDijkstra(){
  
        TypedQuery<Dijkstra3> query = em.createNamedQuery("GetDijkstra", Dijkstra3.class);
        List<Dijkstra3> lista = query.getResultList();

        return lista;
    }
}

Controller or Service:

@Autowired
TestRepository2 testeRepository2;

...

List<Dijkstra3> callHelloWorld = testeRepository2.callDijkstra();

...     

That works. After searching and testing everything I found, this was the best way I could find to get the result of a query using JPA, pass the data to an object / POJO, and mainly NOT CREATE A NEW TABLE IN THE DATABASE, because "pgr_dijkstra" is a function and its return data does not represent a table.

Is it correct to annotate the Dijkstrasqlmap class with @Mappedsuperclass so you just don’t need to annotate with @Entity? Is there any other better way to take the result of a query in the database and pass to an / POJO object without creating a new table in the database? I appreciate your time and help.

Answer 1

Correct is relative in this case. You are dealing with a JPA limitation - the fact of annotations @NamedNativeQuery and @SqlResultSetMapping they need to be tied to some entity so JPA can find them. Your code works, but it’s really weird to create a superclass mapped just to have some place to park your named query.

Of course there are alternatives to the use of @MappedSuperclass. Each with its pros and cons.

1. "Lend" the scope of any other problem-related entity.

Like Dijkstra3 is a simple POJO you can not move to named query for this class. That said, you may have a related entity. For example, maybe your table Ways is mapped to an entity Way in your code. As at the bottom this is an indirect table query Ways, i would see no problem in defining the query at the top of that entity.

2. Move the query to an XML file

This is also an option, for example using META-INF/orm.xml:

<named-native-query name="GetDijkstra" result-set-mapping="DijkstraMapping">
    <query>SELECT * from pgr_dijkstra('SELECT gid AS id, source, target, length AS cost FROM ways', 11111, 22222)</query>
</named-native-query>

<sql-result-set-mapping name="DijkstraMapping">
    <constructor-result target-class="meu.pacote.Dijkstra3">
        <column name="seq" class="java.lang.Integer"/>
        <column name="path_seq" class="java.lang.Integer"/>
        <column name="node" class="java.math.BigInteger"/>
        <column name="edge" class="java.math.BigInteger"/>
        <column name="seq" class="java.lang.Integer"/>
        <column name="path_seq" class="java.lang.Integer"/>
    </constructor-result>
</sql-result-set-mapping>

3. Make your inquiry directly

You don’t need to define a named query, how do you have direct access to Entitymanager just make the query with SQL. For example, calling the methods createNativeQuery or createStoredProcedureQuery in your repository.

Query query = em.createNativeQuery("SELECT * from pgr_dijkstra('SELECT gid AS id, source, target, length AS cost FROM ways', 11111, 22222)", "DijkstraMapping");
@SuppressWarnings("unchecked")
List<Dijkstra3> lista = query.getResultList();

Of course in this case DijkstraMapping must have been set somewhere. Alternatively you may not use the Mapping and map to the result (List<Object[]>) manually or using a library such as Mapstruct.

4. Native query + ResultTransformer hibernate

In accordance with that article by Vlad Mihalcea it is also possible to use a ResultTransformer from Hibernate to return a DTO from query native (see also official documentation):

List<Dijkstra3> distances = em.createNativeQuery("SELECT * from pgr_dijkstra('SELECT gid AS id, source, target, length AS cost FROM ways', 11111, 22222)")
    .unwrap(org.hibernate.query.NativeQuery.class)
    .setResultTransformer(
        Transformers.aliasToBean(Dijkstra3.class))
    .getResultList();

The method setResultTransformer was marked as deprecated in version 5.2 of Hibernate, however, as per another article by Vlad, for now you still have to use this method, since s alternative with @FunctionInterface will only be available in Hibernate 6.

5. Abuse of the @Subselect

Another Hibernate option is the annotation @Subselect. The purpose of the annotation is to make Dijkstra3 is connected to a query instead of a table.

@Entity
@Subselect("SELECT * from pgr_dijkstra('SELECT gid AS id, source, target, length AS cost FROM ways', 11111, 22222)")
@Synchronize({ "ways" })
public class Dijkstra3 { // ... }

That said, I don’t know a good way to pass parameters dynamically to this query.

6. Using a library on top of JPA

As you noticed JPA has its limitations and is already showing its age. The latest version of JPA (2.2) is from 2017. I wish all the luck in the world for the project Jakarta EE, and I don’t want to be unfair (migrating the project is being a huge effort), but so far the focus has been rebranding and renaming package (the Jakarta Persistence 3.0 is basically JPA 2.2 in another package). Who knows Jakarta EE 10 will not bring some novelties to the persistence specification.

Spring Data

Meanwhile Libraries as Spring Data are gaining space. As you are already using Spring maybe it is worth considering something like this. With Spring Data JPA, for example, you can combine the annotation @Query with projections to do what you need (For more details, see that answer user’s Michal Stochmal Stack Overflow in English):

@Query(value = "SELECT * from pgr_dijkstra('SELECT gid AS id, source, target, length AS cost FROM ways', ?1, ?2)", nativeQuery = true)
List<Dijkstra3> callDijkstra(int source, int target);

But you still need to have an entity for the Repository as well as make the query columns match the projection.

Other options

In some situations it might be worth taking JPA and Hibernate out of play. You can always work with an abstraction on top of JDBC like Spring Data JDBC, JOOQ or JDBI. I admit that although I created the tag Hibernate here at Stack Overflow in Portuguese, nowadays when the technological choice is up to me I hardly go directly to a ORM.