Is it okay 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 function of POSTGIS, 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 can't just 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 researching a lot, I found a solution that I believe is far from ideal, which would not be good practice. NOTE: I'm using Spring Boot + Java. The value of "source" and "target" will not be fixed, users will send via browser, I put it fixed just to test this part more quickly.

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 ...

}

Abstract class with my @SqlResultSetMapping and @NamedNativeQuery. The @SqlResultSetMapping annotation is mapping the result to Dijkstra3.class, which is my POJO. @NamedNativeQuery is my query, which I will use, I point to the "DijkstraMapping" mapping. I annotated the class with @MappedSuperclass, if I didn't do that I would have to annotate 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;
}
}

On my Controller or Service:

@Autowired
TestRepository2 testeRepository2;

...

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

...     

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

The question is: Is it correct to annotate the DijkstraSqlMap class with @MappedSuperclass so you don't just need to annotate with @Entity? Is there any other better way to get the result of a query in the database and pass it to an object / POJO without creating a new table in the database? I thank you for your time and help.

Answer 1

You can use the Hibernate annotation @Subselect for this purpose and map it as ordinary entity.

@Entity
@Subselect("SELECT * from pgr_dijkstra('SELECT gid AS id, source, target, length AS cost FROM ways', 11111, 22222)")
public class Dijkstra3 {
  public Integer seq;
  public Integer path_seq;
  public BigInteger node;
  public BigInteger edge;
  public double cost;
  public double agg_cost;
}