How to do custom query with JPA+Hibernate

Question

Hello,

I would like to know an elegant way to make a custom consultation using JPA.

Basically I want to run the query and that it return me an object list, but usually it will be a query with N tables and only with the fields I will need.

I understand that I have to do the POJO query return, but would have some annotation or something else to help me make this link between the return of the query and the object? I don’t want to have to pass row by row instantiating the object and column by column filling its properties.

Answer 1

You can use the keyword NEW of JPQL in the query, specifying a class. For each row returned, an instance of this class will be created. And then you can pass the type of this your data structure as parameter when requesting the query to Entitymanager.

This data structure (or "class") needs to declare a constructor that receives the values of the fields in the order they are selected in the query.

So, considering this data structure,:

package org.learning;

class ContaAtrasada {
    final int idConta;
    final double valor;
    final int diasEmAtraso;

    ContaAtrasada(int idConta, double valor, int diasEmAtraso) {
        this.idConta = idConta;
        this.valor = valor;
        this.diasEmAtraso = diasEmAtraso;
    }
}

The method that executes the query would look something like this:

public List<ContaAtrasada> findContasAtrasadas() {
    return em.createQuery("select NEW org.learning.ContaAtrasada(c.idConta, c.valor, " +
            "today - c.dataVencimento as diasEmAtraso) " +
            "from Conta c where c.dataVencimento < today", ContaAtrasada.class)
            .getResultList();
}

I explained the namespace (package) class because it is very important to inform you in your JPQL query, since the query can be executed in another context where your class will not be directly accessible.