How to create a personalized query using JPA?

Question

How to create a personalized query using JPA?

Navigation

#1 by (0 votes)

1

I am migrating a Java Swing application to JavaFX and please if someone could help me to the following:

Use PostgreSQL database.

In native SQL I have this query that works perfect:

SELECT count(t.idremision) as entregados, COUNT(*)-count(t.idremision) as pendientes, 
e.identrada, c.nombrecliente, ciu.nombreciudad, e.lote, e.fecharecepcion::date, e.fecharegistrado,
e.contrato, e.representante FROM transformador t
LEFT JOIN despacho d ON d.iddespacho=t.iddespacho
LEFT JOIN remision r ON r.idremision=t.idremision
INNER JOIN entrada e ON e.identrada=t.identrada
INNER JOIN cliente c ON c.idcliente=e.idcliente
INNER JOIN ciudad ciu ON e.idciudad = ciu.idciudad
GROUP BY c.idcliente, ciu.idciudad, e.identrada
ORDER BY fecharecepcion DESC;

As you can see, for each record in the entry table there is usually more than one record in the associated transformer table ...

Example:

Entry 1 - > Total 20 .... remaining columns of the entry table and the other associated ...

Entry 2 - > Total 15 .... other columns of the entry table and the other associated ...

Entry 3 - > Total 130 .... other columns of the input table and the other associated ...

There is a column called idremission that is given an UPDATE when the transformer is delivered, and not all are delivered at the same time, that is, that column can be 0 or greater than 0. It is 0 (zero) when it has not been delivered, and it is greater than 0 (zero) when the ID of an invoice is assigned from another table ...

What I need is this same result that I show in the image:

but without native SQL if possible, only with JPA or JPQL, I was exactly fine.

By default JPA created a class with this code:

private List<Entrada> findEntradaEntities(boolean all, int maxResults, int firstResult) {
        EntityManager em = getEntityManager();
        try {
            CriteriaBuilder cb = em.getCriteriaBuilder();
            CriteriaQuery cq = cb.createQuery();
            cq.select(cq.from(Entrada.class)).orderBy(cb.desc(cq.from(Entrada.class).get("fecharecepcion")));            
            Query q = em.createQuery(cq);
            if (!all) {
                q.setMaxResults(maxResults);
                q.setFirstResult(firstResult);
            }
            return q.getResultList();
        } finally {
            em.close();
        }
    }

Although you need to sort by date and I had to add this part that was not:

cq.select(cq.from(Entrada.class)).orderBy(cb.desc(cq.from(Entrada.class).get("fecharecepcion")));

I hope you can help me how to create this same query in some way with JPA, or JPQL, I do not understand this framework very well, until now I am starting. If anyone can advise me and advise me to use another method to make queries based on data, I will appreciate it ...

sql java java-swing jpa postgresql

asked by Nelson Castiblanco 10.12.2018 в 13:00

source

1 answer

I'm getting java.lang.IllegalArgumentException Symfony login returns Invalid credentials

score 0 · Accepted Answer

You can do it by writing your query in String , having your query in that String you can call it with createNativeQuery , you can send them parameters that you have only have to concatenate them where you have your where , like this example that I leave you with your data:

private List<Entrada> findEntradaEntities(boolean all, int maxResults, 
int firstResult){
    EntityManager em = getEntityManager();

String queryStringBaseAll = "SELECT count(t.idremision) as entregados, COUNT(*)-  
count(t.idremision) as pendientes, 
e.identrada, c.nombrecliente, ciu.nombreciudad, 
e.lote, e.fecharecepcion::date, e.fecharegistrado,
e.contrato, e.representante FROM transformador t
LEFT JOIN despacho d ON d.iddespacho=t.iddespacho
LEFT JOIN remision r ON r.idremision=t.idremision
INNER JOIN entrada e ON e.identrada=t.identrada
INNER JOIN cliente c ON c.idcliente=e.idcliente
INNER JOIN ciudad ciu ON e.idciudad = ciu.idciudad
GROUP BY c.idcliente, ciu.idciudad, e.identrada
ORDER BY fecharecepcion DESC ";

List<Entrada> listEntradas = null;
    try {

        listEntradas = em.createNativeQuery(queryStringBaseAll, 
     Entrada.class)
                .getResultList();
    } catch (Exception ex) {

    }

    if (listEntradas  == null) {
        listEntradas  = new ArrayList<>();
    }

    return listEntradas ;

}