How to use AND in hibernate 5.2 Criteria?

user_3629015 0
Viewed 3805

I have two objects User and House. One user can have several houses.

Annotations from House.class:

@Entity
@Table(name="house")
public class House {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="house_id")
private int id;

@ManyToOne
@JoinColumn(name="user_id")
private User user;

@Column(name="country")
private String country;

@Column(name="city")
private String city;

@Column(name="street")
private String street;

@Column(name="post_code")
private String postCode;

@Column(name="house_number")
private String houseNumber;

@Column(name="flats")
private int flats;

@Column(name="picture")
private String picture;

@Column(name="surname_first") // order names will be written in receipt
private Boolean writeSurnameFirst;

@Column(name="cut_name") // Nado ili net sokrashat imena
private Boolean cutName;

@ManyToOne
@JoinColumn(name="currency_id")
private Currency currency;

@ManyToOne
@JoinColumn(name="documentation_lang_id")
private Languages documentationLang;

@Column(name="default_house")
private Boolean defaultHouse;

I need to get a House object based on User and column defaultHouse = true.

I tried this code, but I can't get how to implement User into it:

tx = sess.beginTransaction();
// create criteria builder
CriteriaBuilder builder = sess.getCriteriaBuilder();
// create criteria
CriteriaQuery<House> query = builder.createQuery(House.class);
// specify criteria root
Root<House> root = query.from(House.class);
query.select(root).where(builder.equal(root.get("default_house"), true)
     .and(builder.equal(root.get(House.getUser), user)));
house = sess.createQuery(query).getSingleResult();
tx.commit();
criteria jpa hibernate java
3 Answers

In fact builder.and() takes two parameters which are the two restrictions to be joined with AND keyword in SQL, and the builder.and() should be used inside builder.where() method.

change the following code:

query.select(root).where(builder.equal(root.get("default_house"), true)
     .and(builder.equal(root.get(House.getUser), user)));

Like this:

query.select(root).where(builder.and(builder.equal(root.get("default_house"),  true), builder.equal(root.get(House.getUser), user))));

Please refer to Hibernate ORM 5.2.11.Final User Guide for further details and more examples.

Note:

Hibernate is concerned about objects and not tables and when we use its modules like Criteria we use attributes names in the Object and not DB columns names.

user_36696240

In CriteriaBuilder methods you must use the entity field names like following:

builder.equal(root.get("defaultHouse"), true)

and not the database column names:

builder.equal(root.get("default_house"), true)
user_22024150

I attached my example:

CriteriaBuilder builder = session.getCriteriaBuilder();
CriteriaQuery<Eje> query = builder.createQuery(Eje.class);
Root<Eje> productRoot = query.from(Eje.class);

query.select(productRoot)
     .where(builder.and(builder.equal(productRoot.get("id"), 1),                    builder.equal(productRoot.get(Eje_.factorCriticos).get(FactorCritico_.id), 1)));

result =(List<T>) session.createQuery(query).getResultList();
user_79942690
Related