JPA CriteriaQuery Arayüzü

Giriş
Bu arayüz sql cümlesini kurar. Cüme daha sonra EntityManager'a geçilir ve TypedQuery nesnesi oluşturulur. Bu nesnenin metodları ile veriye erişilir.

Kullanım
Örnek- select id where name = ?
Tek bir sütun listesi okumak için şöyle yaparız.

public List<Integer> getUserIdsByName(String name) {

  CriteriaBuilder builder = session.getCriteriaBuilder();
  CriteriaQuery<Integer> query = builder.createQuery( Integer.class );

  Root<User> root = query.from( User.class );
  query.select( root.get( "id" ) );
  query.where( builder.equal( root.get( "name" ) , name ) );

  return session.createQuery( query ).getResultList();
}

Örnek - select tariff,id,name where name = ?
Çoklu sütun okuyarak UserIdAndName nesne listesi dönmek için şöyle yaparız.

public List<UserIdAndName> getUserIdAndNameByName(String name) {

  CriteriaBuilder builder = session.getCriteriaBuilder();
  CriteriaQuery<UserIdAndName> query = builder.createQuery( UserIdAndName.class );

  Root<User> root = query.from( User.class );
  query.where( builder.equal( root.get( "name" ) , "9" ) );
  query.select( builder.construct( UserIdAndName.class ,
    root.get( "tariff" ).get( "id" ) , root.get( "name" ) ) );

  return session.createQuery( query ).getResultList();
}

Örnek - select *
Çoklu sütun okuyarak Father nesne listesi dönmek için şöyle yaparız.

CriteriaQuery<Father> criteriaQueryForFatherDTOs = ...;
...
TypedQuery<Father> typedQuery = entityManager.createQuery(criteriaQueryForFatherDTOs);
...
List<Father> listOfFathers = typedQuery.getResultList();

Örnek - select * from Gust where propertyIdentifier = ? and firstName like %?%
Şöyle yaparız.

public List<Guest> findGuests(Guest searchedGuest) {

  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery<Guest> cq = cb.createQuery(Guest.class);
  Root<Guest> quest = cq.from(Guest.class);
  List<Predicate> predicates = new ArrayList<Predicate>();

  if (searchedGuest.getPropertyIdentifier != null) {
    predicates.add(cb.equal(quest.get("propertyIdentifier"),
      searchedGuest.getPropertyIdentifier));
  }
  if (searchedGuest.getFirstName != null) {
    predicates.add(cb.like(quest.get("firstName"), "%" +
      searchedGuest.getFirstName + "%"));
  }
  // other predicates

  cq.select(quest).where(predicates.toArray(new Predicate[] {}));
  List<Guest> guests = em.createQuery(cq).getResultList();
  return guests;
}

Örnek
Çoklu sütun okuyarak B ve A nesne listeleri dönmek için ş Şöyle yaparız.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();

// Select distinct aid from B
CriteriaQuery<B> bQuery = cb.createQuery(B.class);
Root<B> bRoot = bQuery.from(B.class);
bQuery.select(bRoot.get("a").get("id")).distinct(true);

// Select * from A where aid not in ()
CriteriaQuery<A> aQuery = cb.createQuery(A.class);
Root<A> aRoot = aQuery.from(A.class);
aQuery.select(aRoot).where(cb.not(aRoot.get("id").in(bQuery)));

TypedQuery<A> query = entityManager.createQuery(aQuery);
List<A> result = query.getResultList();

consructor
CriteriaBuilder tarafından yaratılır. Şöyle yaparız.

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Father> criteriaQueryForFatherDTOs = builder.createQuery(Father.class);

distinct metodu
Şöyle yaparız.

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Father> criteriaQueryForFatherDTOs = builder.createQuery(Father.class);
Root<Father> rootForFather = criteriaQueryForFatherDTOs.from(Father.class);
criteriaQueryForFatherDTOs.select(rootForFather).distinct(true);

from metodu
Şöyle yaparız.

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Father> criteriaQueryForFatherDTOs = builder.createQuery(Father.class);
Root<Father> rootForFather = criteriaQueryForFatherDTOs.from(Father.class);

orderBy metodu

Şöyle yaparız.

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<UserDTO> cq = cb.createQuery(UserDTO.class);
Root<UserDTO> iRoot = cq.from(UserDTO.class);
... //set where clause on CriteriaBuilder
List<Order> orders = new ArrayList<Order>(2);
orders.add(cb.asc(iRoot.get("firstName")));
orders.add(cb.asc(iRoot.get("lastName")));

cq.orderBy(orders);
TypedQuery<UserDTO> query = em.createQuery(cq);

List<UserDTO> result = query.getResultList();

select metodu - Root
Şöyle yaparız.

CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Father> criteriaQueryForFatherDTOs = builder.createQuery(Father.class);
Root<Father> rootForFather = criteriaQueryForFatherDTOs.from(Father.class);
criteriaQueryForFatherDTOs.select(rootForFather).distinct(true);

select metodu - Sütun isimleri
Seçilecek sütun ismini belirtir. Şöyle yaparız.

public Long calculateCount(String filters) {
  CriteriaBuilder cb = em.getCriteriaBuilder();
  CriteriaQuery<Long> sc = cb.createQuery(Long.class);
  Root<UserDTO> iRoot = sc.from(UserDTO.class);
  List<Predicate> predicates = new ArrayList<Predicate>();

  predicates.add(cb.like(cb.lower(iRoot.<String>get("login")),
    "%" + filters.toLowerCase() + "%"));
  

  Predicate[] predArray = new Predicate[predicates.size()];
  predicates.toArray(predArray);

  sc.select(cb.count(iRoot));

  sc.where(predArray);
  Long count = em.createQuery(sc).getSingleResult();

  return count;

}

where metodu - Predicate
Şöyle yaparız.

String columnName = ...;
String filters = ...
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<UserDTO> cq = cb.createQuery(UserDTO.class);
Root<UserDTO> iRoot = cq.from(UserDTO.class);
List<Predicate> predicates = new ArrayList<Predicate>();

predicates.add(cb.like(cb.lower(iRoot.<String>get(columnName)),
  "%" + filters.toLowerCase() + "%"));


Predicate[] predArray = new Predicate[predicates.size()];
predicates.toArray(predArray);

cq.where(predArray);

List<Order> orders = new ArrayList<Order>(2);
orders.add(cb.asc(iRoot.get("firstName")));
orders.add(cb.asc(iRoot.get("lastName")));

cq.orderBy(orders);
TypedQuery<UserDTO> query = em.createQuery(cq);

List<UserDTO> result = query.getResultList();