关于jpa查询

最近项目用到了hibernate的jpa查询，以前这方面接触的少，所以自己熟悉了下jpa的CriteriaQuery查询api。

package com.tc.test;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Tuple;
import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Join;
import javax.persistence.criteria.JoinType;
import javax.persistence.criteria.ParameterExpression;
import javax.persistence.criteria.Root;
import javax.persistence.criteria.Subquery;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import com.tc.entity.Goods;
import com.tc.entity.Merchant;
import com.tc.entity.MerchantVo;
import com.tc.entity.MerchantVoo;
import com.tc.entity.Product;
import com.tc.entity.Promotion;
import com.tc.entity.PromotionProduct;
import com.tc.entity.TradeCity;
import com.tc.service.PromotionService;

/**
* @Title: JpaQuery.java
* @Package com.tc.test
* @author chensai
* @date 2016年10月12日 下午8:59:46
* @description jpa查询 select的几种用法
*              总结:multiselect对于普通对象没有意义，因为hibernate是对象关联的，但是有一个地方有用，
*              即聚合查询的时候，汇总一些数据有用.multiselect返回的是一个数组,可以通过构造函数的形式构造对象
*              疑问:Hibernate
*              为什么用multiselect查询的时候必须用构造函数注入,此处很不方便意味着，每个类都要创建构造函数。
*/
@Controller
public class JpaQuerySelect {
@Autowired
private EntityManagerFactory entityManagerFactory;

/**
* 不带where条件的简单查询，发现在select中只查一个字段返回的String不是对象
* 由此联想到了jpa规范，所有的对象都是映射的数据库中的一条记录，所以进行投影查询的时候 只能封装为String或者数组
*/
@Autowired
private PromotionService promotionService;

@RequestMapping(value = "/query1")
public String query1() {
// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
// 相当于from
Root<Product> root = criteriaQuery.from(Product.class);
// 创建select,加上distinct去重复
criteriaQuery.select(root.get("fullName")).distinct(true);
// 执行查询
List<Product> lists = entityManager.createQuery(criteriaQuery).getResultList();
/*
* for(Product product : lists){ System.out.println(product); }
*/

System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}

/** select中查询多个字段返回数组 */
@RequestMapping(value = "/query2")
public String query2() {
/// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
// 相当于from
Root<Product> root = criteriaQuery.from(Product.class);
// 创建select,加上distinct去重复,多个字段用multiselect
criteriaQuery.multiselect(root.get("fullName"), root.get("goods").get("name")).distinct(true);
// 执行查询
List<Object[]> lists = entityManager.createQuery(criteriaQuery).getResultList();
/*
* for(Product product : lists){ System.out.println(product); }
*/

System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}

/** 可以用Tuple接口代替array */
@RequestMapping(value = "/query3")
public String query3() {
/// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createQuery(Tuple.class);
// 相当于from
Root<Product> root = criteriaQuery.from(Product.class);
// 创建select,加上distinct去重复,多个字段用multiselect
criteriaQuery.multiselect(root.get("fullName"), root.get("goods").get("name")).distinct(true);
// 执行查询
List<Tuple> lists = entityManager.createQuery(criteriaQuery).getResultList();
/*
* for(Product product : lists){ System.out.println(product); }
*/

System.out.println(lists.get(0).get(0));
System.out.println(lists.size());
return null;
}

/** 构造函数查询,可以返回自定义的vo但是每个vo都要写构造函数，麻烦 */
@RequestMapping(value = "/query4")
public String query4() {
/// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<MerchantVo> criteriaQuery = criteriaBuilder.createQuery(MerchantVo.class);
// 相当于from
Root<Merchant> root = criteriaQuery.from(Merchant.class);
// 创建select,加上distinct去重复
criteriaQuery.select(criteriaBuilder.construct(MerchantVo.class, root.get("name"), root.get("logo")))
.distinct(true);
// 执行查询
List<MerchantVo> lists = entityManager.createQuery(criteriaQuery).getResultList();
/*
* for(Product product : lists){ System.out.println(product); }
*/

System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}

/** from的时候构造join */
@RequestMapping(value = "/query5")
public String query5() {
/// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createQuery(Tuple.class);
// 相当于from
Root<Product> root = criteriaQuery.from(Product.class);
Join<Product, Goods> join = root.join("goods", JoinType.LEFT);
// 创建select,加上distinct去重复
criteriaQuery.multiselect(root.get("fullName"), join.get("description")).distinct(true);
// 执行查询
List<Tuple> lists = entityManager.createQuery(criteriaQuery).getResultList();
for (Tuple product : lists) {
System.out.println(product.get(1));
}

System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}

/** from的时候构造join,并且用构造函数封装对象 */
@RequestMapping(value = "/query6")
public String query6() {
/// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<MerchantVoo> criteriaQuery = criteriaBuilder.createQuery(MerchantVoo.class);
// 相当于from
Root<Merchant> root = criteriaQuery.from(Merchant.class);
Join<Merchant, TradeCity> join = root.join("tradeCity", JoinType.LEFT);
// 创建select,加上distinct去重复
criteriaQuery.multiselect(root, join.get("description")).distinct(true);
// 执行查询
List<MerchantVoo> lists = entityManager.createQuery(criteriaQuery).getResultList();
for (MerchantVoo product : lists) {
System.out.println(product.getProduct().getArea().getFullName());
}

System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}

/** 增加where条件过滤数据 ,单条件 */
@RequestMapping(value = "/query7")
public String query7() {
// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
// 相当于from
Root<Product> root = criteriaQuery.from(Product.class);
// 创建select,加上distinct去重复
criteriaQuery.select(root).distinct(true);
// 创建where条件过滤
ParameterExpression<Integer> para = criteriaBuilder.parameter(Integer.class);
criteriaQuery.where(criteriaBuilder.gt(root.get("stock"), para));
// 执行查询
List<Product> lists = entityManager.createQuery(criteriaQuery).setParameter(para, 100).getResultList();

for (Product product : lists) {
System.out.println(product.getStock());
}

System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}

/**
* 增加where条件过滤数据 ,and条件 生成的sql为 Hibernate: select distinct product0_.id as
* id43_, product0_.create_date as create2_43_, product0_.modify_date as
* modify3_43_, product0_.allocated_stock as allocated4_43_,
* product0_.attribute_value0 as attribute5_43_, product0_.attribute_value1
* as attribute6_43_, product0_.attribute_value10 as attribute7_43_,
* product0_.attribute_value11 as attribute8_43_,
* product0_.attribute_value12 as attribute9_43_,
* product0_.attribute_value13 as attribute10_43_,
* product0_.attribute_value14 as attribute11_43_,
* product0_.attribute_value15 as attribute12_43_,
* product0_.attribute_value16 as attribute13_43_,
* product0_.attribute_value17 as attribute14_43_,
* product0_.attribute_value18 as attribute15_43_,
* product0_.attribute_value19 as attribute16_43_,
* product0_.attribute_value2 as attribute17_43_, product0_.attribute_value3
* as attribute18_43_, product0_.attribute_value4 as attribute19_43_,
* product0_.attribute_value5 as attribute20_43_, product0_.attribute_value6
* as attribute21_43_, product0_.attribute_value7 as attribute22_43_,
* product0_.attribute_value8 as attribute23_43_, product0_.barcode as
* barcode43_, product0_.favorites as favorites43_, product0_.full_name as
* full26_43_, product0_.goods as goods43_, product0_.hits as hits43_,
* product0_.image as image43_, product0_.introduction as introdu29_43_,
* product0_.is_gift as is30_43_, product0_.is_list as is31_43_,
* product0_.is_marketable as is32_43_, product0_.is_recommend as is33_43_,
* product0_.keyword as keyword43_, product0_.max_point as max35_43_,
* product0_.memo as memo43_, product0_.mobile_price as mobile37_43_,
* product0_.month_hits as month38_43_, product0_.month_hits_date as
* month39_43_, product0_.month_sales as month40_43_,
* product0_.month_sales_date as month41_43_, product0_.point as point43_,
* product0_.point_price as point43_43_, product0_.price as price43_,
* product0_.revise_price as revise45_43_, product0_.sales as sales43_,
* product0_.score as score43_, product0_.score_count as score48_43_,
* product0_.seo_description as seo49_43_, product0_.seo_keywords as
* seo50_43_, product0_.seo_title as seo51_43_, product0_.sn as sn43_,
* product0_.stock as stock43_, product0_.stock_memo as stock54_43_,
* product0_.total_score as total55_43_, product0_.use_point as use56_43_,
* product0_.week_hits as week57_43_, product0_.week_hits_date as
* week58_43_, product0_.week_sales as week59_43_, product0_.week_sales_date
* as week60_43_, product0_.weight as weight43_ from tc_product product0_
* where product0_.stock>? and product0_.allocated_stock>?
*/
@RequestMapping(value = "/query8")
public String query8() {
// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
// 相当于from
Root<Product> root = criteriaQuery.from(Product.class);
// 创建select,加上distinct去重复
criteriaQuery.select(root).distinct(true);
// 创建where条件过滤
ParameterExpression<Integer> para = criteriaBuilder.parameter(Integer.class);
ParameterExpression<Integer> para2 = criteriaBuilder.parameter(Integer.class);
// 写法一
// criteriaQuery.where(criteriaBuilder.gt(root.get("stock"),
// para),criteriaBuilder.gt(root.get("allocatedStock"), para2));
// 写法二
criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.gt(root.get("stock"), para)),
criteriaBuilder.gt(root.get("allocatedStock"), para2));
// 执行查询
List<Product> lists = entityManager.createQuery(criteriaQuery).setParameter(para, 10).setParameter(para2, 2)
.getResultList();

for (Product product : lists) {
System.out.println(product.getStock());
}

System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}

/**
* 增加where条件过滤数据 ,or sql打印出来为: Hibernate: select distinct product0_.id as
* id43_, product0_.create_date as create2_43_, product0_.modify_date as
* modify3_43_, product0_.allocated_stock as allocated4_43_,
* product0_.attribute_value0 as attribute5_43_, product0_.attribute_value1
* as attribute6_43_, product0_.attribute_value10 as attribute7_43_,
* product0_.attribute_value11 as attribute8_43_,
* product0_.attribute_value12 as attribute9_43_,
* product0_.attribute_value13 as attribute10_43_,
* product0_.attribute_value14 as attribute11_43_,
* product0_.attribute_value15 as attribute12_43_,
* product0_.attribute_value16 as attribute13_43_,
* product0_.attribute_value17 as attribute14_43_,
* product0_.attribute_value18 as attribute15_43_,
* product0_.attribute_value19 as attribute16_43_,
* product0_.attribute_value2 as attribute17_43_, product0_.attribute_value3
* as attribute18_43_, product0_.attribute_value4 as attribute19_43_,
* product0_.attribute_value5 as attribute20_43_, product0_.attribute_value6
* as attribute21_43_, product0_.attribute_value7 as attribute22_43_,
* product0_.attribute_value8 as attribute23_43_, product0_.barcode as
* barcode43_, product0_.favorites as favorites43_, product0_.full_name as
* full26_43_, product0_.goods as goods43_, product0_.hits as hits43_,
* product0_.image as image43_, product0_.introduction as introdu29_43_,
* product0_.is_gift as is30_43_, product0_.is_list as is31_43_,
* product0_.is_marketable as is32_43_, product0_.is_recommend as is33_43_,
* product0_.keyword as keyword43_, product0_.max_point as max35_43_,
* product0_.memo as memo43_, product0_.mobile_price as mobile37_43_,
* product0_.month_hits as month38_43_, product0_.month_hits_date as
* month39_43_, product0_.month_sales as month40_43_,
* product0_.month_sales_date as month41_43_, product0_.point as point43_,
* product0_.point_price as point43_43_, product0_.price as price43_,
* product0_.revise_price as revise45_43_, product0_.sales as sales43_,
* product0_.score as score43_, product0_.score_count as score48_43_,
* product0_.seo_description as seo49_43_, product0_.seo_keywords as
* seo50_43_, product0_.seo_title as seo51_43_, product0_.sn as sn43_,
* product0_.stock as stock43_, product0_.stock_memo as stock54_43_,
* product0_.total_score as total55_43_, product0_.use_point as use56_43_,
* product0_.week_hits as week57_43_, product0_.week_hits_date as
* week58_43_, product0_.week_sales as week59_43_, product0_.week_sales_date
* as week60_43_, product0_.weight as weight43_ from tc_product product0_
* where product0_.stock>? or product0_.allocated_stock<?
*/
@RequestMapping(value = "/query9")
public String query9() {
// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
// 相当于from
Root<Product> root = criteriaQuery.from(Product.class);
// 创建select,加上distinct去重复
criteriaQuery.select(root).distinct(true);
// 创建where条件过滤
ParameterExpression<Integer> para = criteriaBuilder.parameter(Integer.class);
ParameterExpression<Integer> para2 = criteriaBuilder.parameter(Integer.class);
criteriaQuery.where(criteriaBuilder.or(criteriaBuilder.gt(root.get("stock"), para),
criteriaBuilder.lt(root.get("allocatedStock"), para2)));
// 执行查询
List<Product> lists = entityManager.createQuery(criteriaQuery).setParameter(para, 10).setParameter(para2, 2)
.getResultList();

for (Product product : lists) {
System.out.println(product.getStock());
}

System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}

/**
* 增加where条件过滤数据 ,like sql打印出来为: Hibernate: select distinct product0_.id as
* id43_, product0_.create_date as create2_43_, product0_.modify_date as
* modify3_43_, product0_.allocated_stock as allocated4_43_,
* product0_.attribute_value0 as attribute5_43_, product0_.attribute_value1
* as attribute6_43_, product0_.attribute_value10 as attribute7_43_,
* product0_.attribute_value11 as attribute8_43_,
* product0_.attribute_value12 as attribute9_43_,
* product0_.attribute_value13 as attribute10_43_,
* product0_.attribute_value14 as attribute11_43_,
* product0_.attribute_value15 as attribute12_43_,
* product0_.attribute_value16 as attribute13_43_,
* product0_.attribute_value17 as attribute14_43_,
* product0_.attribute_value18 as attribute15_43_,
* product0_.attribute_value19 as attribute16_43_,
* product0_.attribute_value2 as attribute17_43_, product0_.attribute_value3
* as attribute18_43_, product0_.attribute_value4 as attribute19_43_,
* product0_.attribute_value5 as attribute20_43_, product0_.attribute_value6
* as attribute21_43_, product0_.attribute_value7 as attribute22_43_,
* product0_.attribute_value8 as attribute23_43_, product0_.barcode as
* barcode43_, product0_.favorites as favorites43_, product0_.full_name as
* full26_43_, product0_.goods as goods43_, product0_.hits as hits43_,
* product0_.image as image43_, product0_.introduction as introdu29_43_,
* product0_.is_gift as is30_43_, product0_.is_list as is31_43_,
* product0_.is_marketable as is32_43_, product0_.is_recommend as is33_43_,
* product0_.keyword as keyword43_, product0_.max_point as max35_43_,
* product0_.memo as memo43_, product0_.mobile_price as mobile37_43_,
* product0_.month_hits as month38_43_, product0_.month_hits_date as
* month39_43_, product0_.month_sales as month40_43_,
* product0_.month_sales_date as month41_43_, product0_.point as point43_,
* product0_.point_price as point43_43_, product0_.price as price43_,
* product0_.revise_price as revise45_43_, product0_.sales as sales43_,
* product0_.score as score43_, product0_.score_count as score48_43_,
* product0_.seo_description as seo49_43_, product0_.seo_keywords as
* seo50_43_, product0_.seo_title as seo51_43_, product0_.sn as sn43_,
* product0_.stock as stock43_, product0_.stock_memo as stock54_43_,
* product0_.total_score as total55_43_, product0_.use_point as use56_43_,
* product0_.week_hits as week57_43_, product0_.week_hits_date as
* week58_43_, product0_.week_sales as week59_43_, product0_.week_sales_date
* as week60_43_, product0_.weight as weight43_ from tc_product product0_
* where (product0_.full_name like ?) and product0_.stock>? 传值注意带上百分号
*/
@RequestMapping(value = "/query10")
public String query10() {
// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
// 相当于from
Root<Product> root = criteriaQuery.from(Product.class);
// 创建select,加上distinct去重复
criteriaQuery.select(root).distinct(true);
// 创建where条件过滤
ParameterExpression<String> para = criteriaBuilder.parameter(String.class);
ParameterExpression<Integer> para2 = criteriaBuilder.parameter(Integer.class);
criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.like(root.get("fullName"), para),
criteriaBuilder.gt(root.get("stock"), para2)));
// 执行查询
List<Product> lists = entityManager.createQuery(criteriaQuery).setParameter(para, "%积分%")
.setParameter(para2, 100).getResultList();

for (Product product : lists) {
System.out.println(product.getStock());
System.out.println(product.getFullName());
}

System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}

/**
* group by having 和order排序 sql打印出来为: Hibernate: select distinct
* merchant0_.id as col_0_0_, count(merchant0_.id) as col_1_0_ from
* tc_merchant merchant0_ where merchant0_.name like ? group by
* merchant0_.id , merchant0_.address having merchant0_.address like ? order
* by merchant0_.id desc
*/
@RequestMapping(value = "/query11")
public String query11() {
// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<MerchantVoo> criteriaQuery = criteriaBuilder.createQuery(MerchantVoo.class);
// 相当于from
Root<Merchant> root = criteriaQuery.from(Merchant.class);
// 创建select,加上distinct去重复
criteriaQuery.multiselect(root, criteriaBuilder.count(root.get("id")).alias("mcount")).distinct(true);
// 创建where条件过滤
ParameterExpression<String> para = criteriaBuilder.parameter(String.class);
criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.like(root.get("name"), para)));
criteriaQuery.groupBy(root, root.get("address"));
criteriaQuery.having(criteriaBuilder.like(root.get("address"), "%青羊区%"));
criteriaQuery.orderBy(criteriaBuilder.desc(root.get("id")));
// 执行查询
List<MerchantVoo> lists = entityManager.createQuery(criteriaQuery).setParameter(para, "%团购%").getResultList();

for (MerchantVoo product : lists) {
System.out.println(product.getMcount());
// System.out.println(product.getProduct().getImages());
}
System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}

/**
* 增加子查询 打印出的sql为Hibernate: select distinct product0_.id as id44_,
* product0_.create_date as create2_44_, product0_.modify_date as
* modify3_44_, product0_.allocated_stock as allocated4_44_,
* product0_.attribute_value0 as attribute5_44_, product0_.attribute_value1
* as attribute6_44_, product0_.attribute_value10 as attribute7_44_,
* product0_.attribute_value11 as attribute8_44_,
* product0_.attribute_value12 as attribute9_44_,
* product0_.attribute_value13 as attribute10_44_,
* product0_.attribute_value14 as attribute11_44_,
* product0_.attribute_value15 as attribute12_44_,
* product0_.attribute_value16 as attribute13_44_,
* product0_.attribute_value17 as attribute14_44_,
* product0_.attribute_value18 as attribute15_44_,
* product0_.attribute_value19 as attribute16_44_,
* product0_.attribute_value2 as attribute17_44_, product0_.attribute_value3
* as attribute18_44_, product0_.attribute_value4 as attribute19_44_,
* product0_.attribute_value5 as attribute20_44_, product0_.attribute_value6
* as attribute21_44_, product0_.attribute_value7 as attribute22_44_,
* product0_.attribute_value8 as attribute23_44_, product0_.barcode as
* barcode44_, product0_.favorites as favorites44_, product0_.full_name as
* full26_44_, product0_.goods as goods44_, product0_.hits as hits44_,
* product0_.image as image44_, product0_.introduction as introdu29_44_,
* product0_.is_gift as is30_44_, product0_.is_list as is31_44_,
* product0_.is_marketable as is32_44_, product0_.is_recommend as is33_44_,
* product0_.keyword as keyword44_, product0_.max_point as max35_44_,
* product0_.memo as memo44_, product0_.mobile_price as mobile37_44_,
* product0_.month_hits as month38_44_, product0_.month_hits_date as
* month39_44_, product0_.month_sales as month40_44_,
* product0_.month_sales_date as month41_44_, product0_.point as point44_,
* product0_.point_price as point43_44_, product0_.price as price44_,
* product0_.revise_price as revise45_44_, product0_.sales as sales44_,
* product0_.score as score44_, product0_.score_count as score48_44_,
* product0_.seo_description as seo49_44_, product0_.seo_keywords as
* seo50_44_, product0_.seo_title as seo51_44_, product0_.sn as sn44_,
* product0_.stock as stock44_, product0_.stock_memo as stock54_44_,
* product0_.total_score as total55_44_, product0_.use_point as use56_44_,
* product0_.week_hits as week57_44_, product0_.week_hits_date as
* week58_44_, product0_.week_sales as week59_44_, product0_.week_sales_date
* as week60_44_, product0_.weight as weight44_ from tc_product product0_
* where (product0_.full_name like ?) and product0_.stock>? and (exists
* (select promotionp1_.id from tc_promotion_product promotionp1_ inner join
* tc_promotion promotion2_ on promotionp1_.promotion=promotion2_.id where
* product0_.id=promotionp1_.product and promotion2_.id=?))
*/
@RequestMapping(value = "/query12")
public String query12() {
// 得到EntityManager
EntityManager entityManager = entityManagerFactory.createEntityManager();
// 创建查询
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
// 创建返回值
CriteriaQuery<Product> criteriaQuery = criteriaBuilder.createQuery(Product.class);
// 相当于from
Root<Product> root = criteriaQuery.from(Product.class);
// 创建select,加上distinct去重复
criteriaQuery.select(root).distinct(true);
// 创建where条件过滤
ParameterExpression<String> para = criteriaBuilder.parameter(String.class);
ParameterExpression<Integer> para2 = criteriaBuilder.parameter(Integer.class);

Promotion promotion = promotionService.find(35L);
// 子查询
Subquery<PromotionProduct> subquery = criteriaQuery.subquery(PromotionProduct.class);
Root<PromotionProduct> proot = subquery.from(PromotionProduct.class);
subquery.select(proot);
subquery.where(criteriaBuilder.equal(root.get("id"), proot.get("product")),
criteriaBuilder.equal(proot.join("promotion"), promotion));
criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.like(root.get("fullName"), para),
criteriaBuilder.gt(root.get("stock"), para2)), criteriaBuilder.exists(subquery));
// 执行查询
List<Product> lists = entityManager.createQuery(criteriaQuery).setParameter(para, "%积分%")
.setParameter(para2, 100).getResultList();
if (lists != null && lists.size() > 0) {
for (Product product : lists) {
System.out.println(product.getStock());
System.out.println(product.getFullName());
}
}

System.out.println(lists.get(0));
System.out.println(lists.size());
return null;
}
}