SQL query with Hibernate Criteria

Question

SQL query with Hibernate Criteria

Navigation

#1 by (0 votes)

0

I'm making reports with Hibernate but I had a problem and I can not find a way to do it. The query is this:

SELECT
  mes as mes,
  COUNT(mes) AS t_mes,
  SUM(total) AS totales,
  SUM(cantidad) AS cantidad,
  SUM(facturado) AS facturado,
  SUM(descuentos) AS descuentos
FROM(
  SELECT
    MONTH(p.fechaP) AS mes,
    SUM(p.montoP) AS total,
    COUNT(p.montoP) AS cantidad,
    SUM(f.montoF) AS facturado,
    SUM(f.descuentoF) AS descuentos
  FROM
    pagos p
  INNER JOIN
    factura f ON p.idFactura = f.idFactura
  WHERE
    f.estadoF = 'Pagado'
  GROUP BY
    p.fechaP
)
GROUP BY
  mes

The fomato I use:

    public List ListarPagoPTotal() {
        Criteria l = getSession().createCriteria(Pagos.class, "pagos")
                .createAlias("pagos.factura", "factura")
                .add(Restrictions.eq("factura.estadoF", "Pagado"))
                .setProjection(Projections.projectionList()
                        .add(Projections.groupProperty("usuario"))
                        .add(Projections.count("montoP").as("contador"))
                        .add(Projections.sum("montoP").as("suma"))
                        .add(Projections.sum("factura.montoF").as("facturados"))
                        .add(Projections.sum("factura.descuentoF").as("descuentos")));
        return l.list();
    }

I can only get the first query, but for the next one, or in this part

select * from(select * from primero)segundo order by id

Any ideas on how to continue?

DB

Script

@Entity
@Table(name = "pagos",
         catalog = "facturacion"
)
public class Pagos implements java.io.Serializable {

    private Integer idPagos;
    private Detalle detalle;
    private Factura factura;
    private Date fechaP;
    private BigDecimal montoP;
    private String usuario;

    public Pagos() {
    }

    public Pagos(Detalle detalle, Factura factura) {
        this.detalle = detalle;
        this.factura = factura;
    }

    public Pagos(Detalle detalle, Factura factura, Date fechaP, BigDecimal montoP, String usuario) {
        this.detalle = detalle;
        this.factura = factura;
        this.fechaP = fechaP;
        this.montoP = montoP;
        this.usuario = usuario;
    }

    @Id
    @GeneratedValue(strategy = IDENTITY)

    @Column(name = "idPagos", unique = true, nullable = false)
    public Integer getIdPagos() {
        return this.idPagos;
    }

    public void setIdPagos(Integer idPagos) {
        this.idPagos = idPagos;
    }

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "idCliente", nullable = false)
    public Detalle getDetalle() {
        return this.detalle;
    }

    public void setDetalle(Detalle detalle) {
        this.detalle = detalle;
    }

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "idFactura", nullable = false)
    public Factura getFactura() {
        return this.factura;
    }

    public void setFactura(Factura factura) {
        this.factura = factura;
    }

    @Temporal(TemporalType.DATE)
    @Column(name = "fechaP", length = 10)
    public Date getFechaP() {
        return this.fechaP;
    }

    public void setFechaP(Date fechaP) {
        this.fechaP = fechaP;
    }

    @Column(name = "montoP", precision = 8)
    public BigDecimal getMontoP() {
        return this.montoP;
    }

    public void setMontoP(BigDecimal montoP) {
        this.montoP = montoP;
    }

    @Column(name = "usuario", length = 15)
    public String getUsuario() {
        return this.usuario;
    }

    public void setUsuario(String usuario) {
        this.usuario = usuario;
    }

}

sql java hibernate

asked by user75463 04.08.2018 в 01:28

source

1 answer

How do I create a vehicle program with struct in C? Telegram.Bot (Get file upload progress)

score 0 · Answer 1

Your problem is that you are using the Criteria interface.

This library is designed to solve queries thought of as objects, and not intended as SQL .

To solve this type of query you should use the Query interface, and write the query in language HQL as follows:

public List ListarPagoPTotal() {
    Query query =getSession().createQuery("SELECT
      mes as mes,
      COUNT(mes) AS t_mes,
      SUM(total) AS totales,
      SUM(cantidad) AS cantidad,
      SUM(facturado) AS facturado,
      SUM(descuentos) AS descuentos
    FROM(
      SELECT
        MONTH(p.fechaP) AS mes,
        SUM(p.montoP) AS total,
        COUNT(p.montoP) AS cantidad,
        SUM(f.montoF) AS facturado,
        SUM(f.descuentoF) AS descuentos
      FROM
        pagos p
      INNER JOIN
        factura f ON p.idFactura = f.idFactura
      WHERE
        f.estadoF = 'Pagado'
      GROUP BY
        p.fechaP
    )
    GROUP BY
      mes")

    return query.list();
}

Note: the name of the fields in the HQL query must be the same as those declared in the java class, it would be useful to publish the model classes you are trying to query.