建立实体类
@Entity
@Table(name = "t_person")
public class Person {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
protected Long id;
@Column(nullable = false)
private String name;
@Column(nullable = false)
private int age;
@Temporal(TemporalType.DATE)
private Date birthday;
@OneToMany(cascade = CascadeType.REFRESH, mappedBy = "person", fetch = FetchType.EAGER)
private List<Shop> shopList;
...
@Entity
@Table(name = "t_shop")
public class Shop {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
protected Long id;
@Column(nullable = false)
private String name;
@ManyToOne(cascade = CascadeType.REFRESH, optional = false, fetch = FetchType.EAGER)
@JoinColumn(name = "per_id", referencedColumnName = "id")
private Person person;
@OneToMany(cascade = CascadeType.REFRESH, mappedBy = "shop", fetch = FetchType.EAGER)
private List<Commodity> commodityList;
...
@Entity
@Table(name = "t_commodity")
public class Commodity {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
protected Long id;
@Column(nullable = false)
private String name;
@Column(nullable = false)
private BigDecimal price = BigDecimal.ZERO;
@ManyToOne(cascade = CascadeType.REFRESH, fetch = FetchType.EAGER)
@JoinColumn(name = "shop_id", referencedColumnName = "id")
private Shop shop;
...
测试Service
package com.ipan.system.service;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;
import com.ipan.core.persistence.DynamicSpecifications;
import com.ipan.core.persistence.SearchFilter;
import com.ipan.system.dao.PersonTestDAO;
import com.ipan.system.entity.Commodity;
import com.ipan.system.entity.Person;
// JPA查询测试
@Service("personTestService")
@Transactional(propagation = Propagation.REQUIRED)
public class PersonTestService {
@Autowired
private PersonTestDAO personTestDAO;
@PersistenceContext
private EntityManager entityManager;
// -- JPQL查询测试 -- //
// IN查询参数测试
public void query_1() {
String jpql = "select t from Person t where t.age in (?1)";
Query query = entityManager.createQuery(jpql); // JPA1.0支持
List value = new ArrayList<Integer>();
value.add(24);
value.add(25);
query.setParameter(1, value);
// JPQL的IN查询参数必须是集合Collection类型,而HQL还可以是数组类型;
// query.setParameter(1, new String[]{"24", "25"}); // 报参数异常IllegalArgumentException
List result = query.getResultList();
if (result != null && result.size() > 0) {
for (Object obj : result) {
System.out.println(obj);
}
}
}
// IN查询参数测试
public void query_2() {
String jpql = "select t from Person t where t.age in (?1)";
Query query = entityManager.createQuery(jpql, Person.class); // JPA2.0支持
List value = new ArrayList<Integer>();
value.add(24);
value.add(25);
query.setParameter(1, value); // JPQL的IN查询参数必须是集合Collection类型,而HQL还可以是数组类型;
List<Person> result = query.getResultList();
if (result != null && result.size() > 0) {
for (Person obj : result) {
System.out.println(obj);
}
}
}
// -- SQL查询测试 -- //
// IN查询参数测试
public void query_3() {
String sql = "select t.* from t_person t where t.age in (?1)";
Query query = entityManager.createNativeQuery(sql, Person.class); // SQL查询测试
List value = new ArrayList<Integer>();
value.add(24);
value.add(25);
query.setParameter(1, value); // JPQL的IN查询参数必须是集合Collection类型,而HQL还可以是数组类型;
List<Person> result = query.getResultList();
if (result != null && result.size() > 0) {
for (Person obj : result) {
System.out.println(obj);
}
}
}
// -- CriteriaBuilder查询测试 -- //
// IN查询参数测试
public void query_4() {
List value = new ArrayList();
value.add(24);
value.add(25);
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Person> query
= builder.createQuery(Person.class); // 查询返回类型是Person
Root<Person> root = query.from(Person.class); // from Person
query.where(root.get("age").in(value)); // where age in (24, 25)
List<Person> result = entityManager.createQuery(query).getResultList();
if (result != null && result.size() > 0) {
for (Person obj : result) {
System.out.println(obj);
}
}
}
// 关联查询参数测试 (正向):直接使用get做向上遍历;
public void query_5() {
List value = new ArrayList();
value.add(24);
value.add(25);
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Commodity> query
= builder.createQuery(Commodity.class); // 查询返回类型是Commodity
Root<Commodity> root = query.from(Commodity.class); // from Commodity
query.where(root.get("shop").get("person").get("age").in(value)); // where shop.person.age in (24, 25)
List<Commodity> result = entityManager.createQuery(query).getResultList();
if (result != null && result.size() > 0) {
for (Commodity obj : result) {
System.out.println(obj);
}
}
}
// 关联查询参数测试(反向):需要使用join向下与被关联的表建立连接,然后这个返回的Join就指向了被关联的表了;
// 跟hibernate的别名查询不同,hibernate使用别名来间接指定被关联表的位置;而JPA使用Join对象直接指向这个被关联的表,不需要别名;
public void query_6() {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Person> query
= builder.createQuery(Person.class); // 查询返回类型是Person
Root root = query.from(Person.class); // from Person
Join joinCommodity = root.join("shopList").join("commodityList"); // Person t1 inner join Shop t2 on t1.id = t2.per_id
// inner join Commodity t3 on t2.id = t3.shop_id
query.where(builder.equal(joinCommodity.get("name"), "商品-1")); // where t3.name = '商品-1'
List<Person> result = entityManager.createQuery(query).getResultList();
if (result != null && result.size() > 0) {
for (Person obj : result) {
System.out.println(obj);
}
}
}
// 类型转换测试:简单类型会自动转换
// 记得hibernate是需要明确类型的,JPA对简单类型有支持,但是特殊类型还是需要自己转换的;
public void query_7() {
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<Person>query
= builder.createQuery(Person.class); // 查询返回类型是Person
Root root = query.from(Person.class); // from Person
Predicate p1 = builder.equal(root.get("id"), "1"); // 类型转换正确
Predicate p2 = builder.equal(root.get("name"), "小张"); // 不需要转换
Predicate p3 = builder.equal(root.get("age"), "25"); // 类型转换正确
Predicate p4 = builder.equal(root.get("birthday"), "1990-01-09");
// 类型转换错误
query.where(p1, p2, p3, p4);
List<Person> result = entityManager.createQuery(query).getResultList();
if (result != null && result.size() > 0) {
for (Person obj : result) {
System.out.println(obj);
}
}
}
}