Doubt about Hibernate Criteria

1

I'm trying to make a work order system just for me to train. In work orders I can have several categories. And my doubt is when it is time to display, for example, a ranking of the most used categories for a period x using criteria of hibernate . Something that the result was organized like this:

Category A - 10 times

Category B - 8 times
Category C - 5 times

What can I use in this case?

Classes are briefly mapped so

ClassService

@SuppressWarnings("serial")
@Entity
@Table(name = "ordem_servico")
public class OrdemServico extends GenericModel {

@NotNull
@Temporal(TemporalType.TIMESTAMP)
@Column(name = "data_os", nullable = false)
private Date dataOS;   

@ManyToMany(cascade = { CascadeType.MERGE, CascadeType.REFRESH })
@JoinTable(name = "os_categoria", joinColumns = @JoinColumn(name = "id_os"),inverseJoinColumns = @JoinColumn(name = "id_categoria"))
private List<Categoria> categorias;     
}

Class Category

@SuppressWarnings("serial")
@Entity
@Table(name = "categoria_os")
public class Categoria extends GenericModel {
@Column(name = "nome_cat", nullable = false, length = 40)

@NotBlank
private String nome;

public String getNome() {
    return nome;
}

public void setNome(String nome) {
    this.nome = nome;
}

}
    
asked by anonymous 13.03.2016 / 05:09

1 answer

0

The way I thought to solve this was so, should have better k

Create a table that represents the ManyToMany link:

@Entity
public class OrdemServicoCategoria {

@Id
@GeneratedValue
private Long idOsCategoria;

@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name="idOrdem")
private OrdemServico os;
@ManyToOne(cascade = CascadeType.ALL)
@JoinColumn(name="nome")
private Categoria categoria; //Atributo nome vira ID na tabela categoria
//...Outros métodos
}

In order service has the attribute

@OneToMany(mappedBy = "os", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
@Fetch(value = FetchMode.SUBSELECT)
private List<OrdemServicoCategoria> osCategorias = new ArrayList<OrdemServicoCategoria>();

A non-persistent Ranking class, just to get the data:

public class Ranking {

private Categoria categoria;
private Long vezes; 


public Ranking(Categoria categoria, Long vezes) {
    this.categoria = categoria;
    this.vezes = vezes;     
}

//Outros métodos
}

The DAO method to recover looks like this:

public List<Ranking> getRanking() {
        String consulta = "select new org.autocomplete.models.Ranking(os_cat.categoria, COUNT(idOsCategoria))"
                + " FROM OrdemServicoCategoria os_cat group by os_cat.categoria";
        TypedQuery<Ranking> query = manager.createQuery(consulta, Ranking.class);       
        return query.getResultList();       
    }

In case, I did not use Criteria, I do not know what your need to use criteria, with criteria would look something like this link link , but I could not solve it k

You can put the date in the middle table and use the where clause for the periods.

    
19.03.2016 / 01:55