Querying

★クエリの結果

Query#getSingleResult

Query#getResultList

Query#executeUpdate DELETE and UPDATE

TypedQuery#getSingleResult

TypedQuery#getResultList

TypedQuery<Country> query =

em.createQuery("SELECT c FROM Country c", Country.class);

List<Country> results = query.getResultList();

TypedQuery<Long> query = em.createQuery(

"SELECT COUNT(c) FROM Country c", Long.class);

long countryCount = query.getSingleResult();

int count = em.createQuery("DELETE FROM Country").executeUpdate();

List<Country> results =

query.setFirstResult(pageIndex * pageSize)

.setMaxResults(pageSize)

.getResultList();

★Metamodel API

エンティティクラス

@Entity

public class Order {

@Id

@GeneratedValue

private Integer id;

@ManyToOne

private Customer customer;

@OneToMany

Set<Item> items;

...

}

メタモデルクラス

import javax.persistence.metamodel.SingularAttribute;

import javax.persistence.metamodel.SetAttribute;

import javax.persistence.metamodel.StaticMetamodel;

@StaticMetamodel(Order.class)

public class Order_ {

public static volatile SingularAttribute<Order, Integer> id;

public static volatile SingularAttribute<Order, Customer> customer;

public static volatile SetAttribute<Order, Item> items;

}

補足：Eclipseでメタモデルクラスを自動生成する設定

1.プロジェクトのプロパティを開く

2.Project FacetsでJPAにチェックを付けて、プロジェクトにJPAサポートを追加

3.JPA欄の一番上「Platform」で適切な対象項目を選択

4.JPA欄の一番下「Canonical metamodel(JPA 2.0)」でソースフォルダを設定

※「Discover annotated classes automatically」にチェックすると、エンティティを追加した時に便利

利用例

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<Order> cq = cb.createQuery(Order.class);

SetJoin<Order, Item> itemNode = cq.from(Order.class).join(Order_.items);

cq.where( cb.equal(itemNode.get(Item_.id), 3 ) ).distinct(true);

Hibernate Metamodel Generator

<dependency>

<groupId>org.hibernate</groupId>

<artifactId>hibernate-jpamodelgen</artifactId>

<version>...</version>

</dependency>

★Native SQL vs JPQL vs Criteria

全件検索

SELECT b FROM Book b

public List<Book> allEntries() {

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<Book> cq = cb.createQuery(Book.class);

Root<Book> rootEntry = cq.from(Book.class);

CriteriaQuery<Book> all = cq.select(rootEntry);

TypedQuery<Book> allQuery = em.createQuery(all);

return allQuery.getResultList();

}

public class CompoundElement {

...

public CompoundElement(String name, Double value) {

this.name = name;

this.value = value;

}

//Needed for Criteria

public CompoundElement(String name, Object value) {

this.name = name;

this.value = (Double) value;

}

...

}

Native SQL

SELECT cmf.name, AVG(c.mileage)

FROM Car c

INNER JOIN CarModel cm

ON c.carModel = cm.uuid

INNER JOIN CarManufacturer cmf

ON cm.manufacturer = cmf.uuid

GROUP BY cmf.name

JPQL

public List<CompoundElement> find() {

Query query = em.createQuery(

"SELECT NEW xxx.CompoundElement(cmf.name, AVG(c.mileage)) " +

"FROM Car c " +

"JOIN c.carModel cm " +

"JOIN cm.manufacturer cmf " +

"GROUP BY cmf.name"

);

return (List<CompoundElement>)query.getResultList();

}

Criteria

public List<CompoundElement> find() {

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery<CompoundElement> q =

cb.createQuery(CompoundElement.class);

Root<Car> c = q.from(Car.class);

Join<Car, CarModel> cmJoin = c.join(Car_.carModel);

Join<CarModel, CarManufacturer> cmfJoin =

cmJoin.join(CarModel_.manufacturer);

CompoundSelection<CompoundElement> cSelect =

cb.construct(CompoundElement.class,

cmfJoin.get(CarManufacturer_.name),

cb.avg(c.get(Car_.mileage)));

q.select(cSelect);

q.groupBy(cmfJoin.get(CarManufacturer_.name));

TypedQuery<CompoundElement> query = em.createQuery(q);

return (List<CompoundElement>)query.getResultList();

}

★Initialize lazy relations

Call a method on the mapped relation

Should be avoided because of an additional query

Order order = em.find(Order.class, orderId);

order.getItems().size();

Fetch Join in JPQL

Query q = em.createQuery("SELECT o FROM Order o JOIN FETCH o.items i WHERE o.id = :id");

q.setParameter("id", orderId);

Order order = (Order) q.getSingleResult();

Fetch Join in Criteria API

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaQuery q = cb.createQuery(Order.class);

Root o = q.from(Order.class);

o.fetch("items", JoinType.INNER);

q.select(o);

q.where(cb.equal(o.get("id"), orderId));

Order order = (Order) em.createQuery(q).getSingleResult();

Named Entity Graph (since JPA 2.1)

@Entity

@NamedEntityGraph(name = "graph.Order.items",

attributeNodes = @NamedAttributeNode("items"))

public class Order implements Serializable { ... }

Dynamic Entity Graph (since JPA 2.1)

EntityGraph graph = em.createEntityGraph(Order.class);

Subgraph itemGraph = graph.addSubgraph("items");

Map hints = new HashMap();

hints.put("javax.persistence.loadgraph", graph);

Order order = em.find(Order.class, orderId, hints);

★Criteria Update/Delete (JPA2.1)

public void updateOrder(Double oldAmount, Double newAmount) {

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaUpdate<Order> update = cb.createCriteriaUpdate(Order.class);

Root e = update.from(Order.class);

update.set("amount", newAmount);

update.where(cb.greaterThanOrEqualTo(e.get("amount"), oldAmount));

em.createQuery(update).executeUpdate();

}

public void deleteOrder(Double amount) {

CriteriaBuilder cb = em.getCriteriaBuilder();

CriteriaDelete<Order> delete = cb.createCriteriaDelete(Order.class);

Root e = delete.from(Order.class);

delete.where(cb.lessThanOrEqualTo(e.get("amount"), amount));

em.createQuery(delete).executeUpdate();

}

★Get JPQL/SQL String From a CriteriaQuery

Hibernate

TypedQuery<Book> query = em.createQuery(...);

query.unwrap(org.hibernate.Query.class).getQueryString() // return SQL string (not JPQL String)