JPA api 学习

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());
            }
        }
    }
}