1. brand类
package pr.cgl.entity;
import javax.persistence.*;
import java.util.Set;
/**
* Created by CGL on 2015/9/16.
*/
@Entity
@Table(name = "xx_brand")
public class Brand {
@Id
@GeneratedValue
private Long id;
private String name;
@OneToMany(targetEntity = Product.class, fetch = FetchType.LAZY)
private Set<Product> products;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Set<Product> getProducts() {
return products;
}
public void setProducts(Set<Product> products) {
this.products = products;
}
@Override
public String toString() {
return "Brand{" +
"id=" + id +
", name='" + name + '\'' +
", products=" + products +
'}';
}
}
2. product 类
package pr.cgl.entity;
import javax.persistence.*;
import java.util.Set;
/**
* Created by LL on 2015/9/16.
*/
@Entity
@Table(name ="xx_product")
public class Product {
@Id
@GeneratedValue
private Long id;
private String name;
@ManyToMany(targetEntity = Promotion.class)
@JoinTable(name = "xx_promotion_product", joinColumns = {@JoinColumn(name = "products")},inverseJoinColumns = {@JoinColumn(name="promotions")})
private Set<Promotion> promotions;
@ManyToOne(fetch = FetchType.EAGER)
@JoinColumn(name = "b_id",insertable = true)
private Brand brand;
public Brand getBrand() {
return brand;
}
public void setBrand(Brand brand) {
this.brand = brand;
}
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Set<Promotion> getPromotions() {
return promotions;
}
public void setPromotions(Set<Promotion> promotions) {
this.promotions = promotions;
}
@Override
public String toString() {
return "Product{" +
"id=" + id +
", name='" + name + '\'' +
", promotions=" + promotions +
// ", brand=" + brand +
'}';
}
}
3. 练习
package pr.cgl.test;
import org.junit.Test;
import pr.cgl.entity.Brand;
import pr.cgl.entity.Product;
import pr.cgl.entity.Promotion;
import javax.persistence.*;
import javax.persistence.criteria.*;
import javax.sql.rowset.Predicate;
import java.util.*;
/**
* Created by LL on 2015/9/16.
*/
public class TestMain {
static EntityManagerFactory emp = null;
static EntityManager em = null;
static CriteriaBuilder cb = null;
static {
emp = Persistence.createEntityManagerFactory("jpaUnit");
em = emp.createEntityManager();
cb = emp.getCriteriaBuilder();
}
public static void main(String[] args) {
Map properties = new HashMap();
properties.put("hibernate.show_sql", "true");
EntityManagerFactory emp = Persistence.createEntityManagerFactory("jpaUnit");
EntityManager em = emp.createEntityManager();
CriteriaBuilder cb = emp.getCriteriaBuilder();
CriteriaQuery<Product> c = cb.createQuery(Product.class);
Root<Product> root = c.from(Product.class);
c.select(root);
Join<Product, Promotion> innerJoin = root.join("promotions", JoinType.LEFT);
Join<Product, Brand> rightJoin = root.join("brand", JoinType.LEFT);
List<Predicate> criteria = new ArrayList<Predicate>();
ParameterExpression<Long> productId = cb.parameter(Long.class, "id");
ParameterExpression<Long> bId = cb.parameter(Long.class, "bId");
// criteria.add(cb.equal(root.get("id"), productId));
// criteria.add(cb.equal(root.get("brand").get("id"), bId));
// c.where(cb.and(criteria.toArray(new Predicate[0])));
// c.where(criteria.get(1));
TypedQuery<Product> q = em.createQuery(c);
System.out.println(q.toString());
q.setParameter("id", 855L);
q.setParameter("bId", 2L);
List<Product> productList = q.getResultList();
for(Product pp: productList){
System.out.println("id="+pp.getId()+" name="+pp.getName());
Set<Promotion> promotionSet = pp.getPromotions();
/* for(Promotion promotion: promotionSet){
System.out.println(promotion.getName());
}*/
}
/* System.out.println(emp);
EntityManager em = emp.createEntityManager();
Query query = em.createNativeQuery("select id, name from xx_product", Product.class);
List<Product> productList = query.getResultList();
for(Product p: productList){
System.out.println("id="+p.getId()+" name="+p.getName());
}*/
}
//练习conjuction
@Test
public void conjunction(){
CriteriaQuery<Product> q = cb.createQuery(Product.class);
Root<Product> productRoot = q.from(Product.class);
Root<Brand> brandRoot = q.from(Brand.class);
javax.persistence.criteria.Predicate predicate = cb.conjunction();
predicate = cb.and(predicate, cb.equal(productRoot.get("brand").get("id"), brandRoot.get("id")));
q.select(productRoot);
q.where(predicate);
TypedQuery<Product> query = em.createQuery(q);
List<Product> list = query.getResultList();
for(Product p: list){
System.out.println(p.toString());
}
}
//练习join 1.inner join
@Test
public void join1(){
CriteriaQuery<Product> q = cb.createQuery(Product.class);
Root<Product> productRoot = q.from(Product.class);
// Root<Brand> brandRoot = q.from(Brand.class);
productRoot.join("brand", JoinType.INNER);
TypedQuery<Product> query = em.createQuery(q);
List<Product> list = query.getResultList();
for(Product p: list){
System.out.println(p.toString());
}
}
//练习join 2.left join
@Test
public void join2(){
CriteriaQuery<Product> q = cb.createQuery(Product.class);
Root<Product> productRoot = q.from(Product.class);
// Root<Brand> brandRoot = q.from(Brand.class);
productRoot.join("brand", JoinType.LEFT);
TypedQuery<Product> query = em.createQuery(q);
List<Product> list = query.getResultList();
for(Product p: list){
System.out.println(p.toString());
}
}
//练习join 3.left join 重要 一关联多
@Test
public void join3(){
CriteriaQuery<Brand> q = cb.createQuery(Brand.class);
// Root<Product> productRoot = q.from(Product.class);
Root<Brand> brandRoot = q.from(Brand.class);
SetJoin<Brand, Product> productJoin = brandRoot.joinSet("products", JoinType.LEFT);
TypedQuery<Brand> query = em.createQuery(q);
List<Brand> list = query.getResultList();
for(Brand b: list){
System.out.println(b.toString());
}
}
@Test
public void subQuery(){
CriteriaQuery<Product> criteriaQuery = cb.createQuery(Product.class);
Root<Product> from = criteriaQuery.from(Product.class);
Path<Object> path = from.get("brand"); // field to map with sub-query
/*from.fetch("name");
from.fetch("id");*/
CriteriaQuery<Product> select = criteriaQuery.select(from);
Subquery<Brand> subquery = criteriaQuery.subquery(Brand.class);
Root fromBrand = subquery.from(Brand.class);
// ParameterExpression<String> name = cb.parameter(String.class, "name");
subquery.select(fromBrand.get("id")); // field to map with main-query
subquery.where(cb.and(cb.equal(fromBrand.get("name"), "brand1"), cb.equal(fromBrand.get("id"),1L)));
select.where(cb.in(path).value(subquery));
TypedQuery<Product> typedQuery = em.createQuery(select);
List<Product> list = typedQuery.getResultList();
for(Product p: list){
System.out.println(p.toString());
}
}
@Test
public void subQuery2(){
CriteriaQuery<Product> criteriaQuery = cb.createQuery(Product.class);
Root<Product> root = criteriaQuery.from(Product.class);
criteriaQuery.where(cb.and(cb.in(root.get("id")).value(1L).value(2L)));
TypedQuery<Product> typedQuery = em.createQuery(criteriaQuery);
List<Product> list = typedQuery.getResultList();
for(Product p: list){
System.out.println(p.toString());
}
}
// 查询多个字段
@Test
public void test3(){
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Product> root = cq.from(Product.class);
cq.select(cb.tuple(root.get("id").alias("id"), root.get("name").alias("name")));
cq.where(cb.and(cb.in(root.get("id")).value(888L).value(899L)));
TypedQuery<Tuple> typedQuery = em.createQuery(cq);
List<Tuple> list = typedQuery.getResultList();
for(Tuple p: list){
System.out.println("id="+p.get("id")+" name="+p.get("name"));
}
}
// count 函数
@Test
public void test4(){
CriteriaQuery<Tuple> cq = cb.createTupleQuery();
Root<Product> root = cq.from(Product.class);
cq.select(cb.tuple(root.get("id").alias("id"), root.get("name").alias("name"), cb.count(root.get("id")).alias("priceAll"), cb.sum(root.<Long>get("id")).alias("sum") ));
cq.where(cb.and(cb.in(root.get("id")).value(888L).value(899L)));
TypedQuery<Tuple> typedQuery = em.createQuery(cq);
List<Tuple> list = typedQuery.getResultList();
for(Tuple p: list){
System.out.println("id="+p.get("id")+" name="+p.get("name")+" priceAll="+p.get("priceAll")+" sum="+p.get("sum"));
}
}
// 子查询
@Test
public void test5(){
CriteriaQuery<Brand> brandQuery = cb.createQuery(Brand.class);
Root<Brand> brandRoot = brandQuery.from(Brand.class);
javax.persistence.criteria.Predicate predicate = cb.gt(brandRoot.<Long>get("id"), 2L);
brandQuery.where(predicate);
TypedQuery<Brand> typedQuery = em.createQuery(brandQuery);
List<Brand> list = typedQuery.getResultList();
for(Brand p: list){
System.out.println("id="+p.getId()+" name="+p.getName()+" sum="+p.toString());
}
}
// expression min max
// 打印sql
@Test
public void test6(){
CriteriaQuery<Tuple> brandQuery = cb.createTupleQuery();
Root<Brand> brandRoot = brandQuery.from(Brand.class);
Expression min = cb.min(brandRoot.<Long>get("id"));
Expression max = cb.max(brandRoot.<Long>get("id"));
brandQuery.select(cb.tuple(min.alias("id1"), max.alias("id2")));
TypedQuery<Tuple> typedQuery = em.createQuery(brandQuery);
List<Tuple> list = typedQuery.getResultList();
System.out.println("a:"+typedQuery.unwrap(org.hibernate.Query.class).getQueryString());
for(Tuple p: list){
System.out.println("id="+p.get("id2")+" "+p.get(1));
}
}
@Test
public void test7(){
String sql = "select p.id, p.name, p.b_id from xx_product p";
Query query = em.createNativeQuery(sql, Product.class);
List<Product> list = query.getResultList();
System.out.println(list);
for(Product p: list){
if(p.getBrand() != null){
System.out.println("id="+p.getId()+" name="+p.getName()+" promotions="+p.getPromotions()+" brand="+p.getBrand());
}
}
}
}
