springboot学习笔记(四)使用querydsl完成条件查询

什么是QueryDSL

1，QueryDSL仅仅是一个通用的查询框架，专注于通过Java API构建类型安全的SQL查询。

2，Querydsl可以通过一组通用的查询API为用户构建出适合不同类型ORM框架或者是SQL的查询语句，也就是说QueryDSL是基于各种ORM框架以及SQL之上的一个通用的查询框架。

3，借助QueryDSL可以在任何支持的ORM框架或者SQL平台上以一种通用的API方式来构建查询。目前QueryDSL支持的平台包括JPA,JDO,SQL,Java Collections,RDF,Lucene,Hibernate Search。

下面开始讲述项目当中如何使用：

1.引入相关依赖

com.querydslquerydsl-jpacom.querydslquerydsl-apt

2.Repository继承QueryDslPredicateExecutor接口

/*** Test_myspringboot* JpaTest.java*/
package com.springboot.Test_myspringboot.repostory;import java.io.Serializable;import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;import com.springboot.Test_myspringboot.entity.Complaint;/*** @author xuchuang* @updateTime 2017年12月20日 下午5:25:41*/
public interface JpaTest extends JpaRepository,QueryDslPredicateExecutor{}


3.使用queryqsdl插件生成查询实体：

pom引入插件：

com.mysema.maven apt-maven-plugin 1.1.3 process target/generated-sources/java

com.querydsl.apt.jpa.JPAAnnotationProcessor

右键项目，debug as maven generate source成功生成查询实体：

复制到项目当中：下面开始测试 查询：

@RequestMapping("/jpa")public void testJpa(){QComplaint qc=QComplaint.complaint;Predicate pre=qc.id.eq("ff8080816034d03c016034ddccde0003");List list= (List) jpatest.findAll(pre);System.out.println(list.size());}

成功查询到一条id为条件中id的记录。

https://docs.spring.io/spring-data/jpa/docs/1.10.x/reference/pdf/spring-data-jpa-reference.pdfjpa的查询方案官方文档

public Page getComplaintInfos(Integer page, Integer size, HttpServletRequest request) {if (page>0)page=page-1;String title=request.getParameter("title");String createTime=request.getParameter("createTime");String keyWord=request.getParameter("keyWord");QComplaint qc=QComplaint.complaint;Predicate pre=qc.id.isNotNull();if (StringUtils.isNotEmpty(title)) {pre = ExpressionUtils.and(pre, qc.title.like("%" + title + "%"));} if (StringUtils.isNotEmpty(keyWord)) {pre = ExpressionUtils.and(pre, qc.title.like("%" + keyWord + "%").or(qc.details.like("%" + keyWord + "%")));} if (StringUtils.isNotEmpty(createTime)) {pre = ExpressionUtils.and(pre, qc.createdDate.goe(DateUtils.getDate(createTime)));}if (StringUtils.isNotEmpty(createTime)) {pre = ExpressionUtils.and(pre, qc.createdDate.loe(DateUtils.getDateAfter(DateUtils.getDate(createTime), 1)));}Pageable pageable = new PageRequest(page, size, Direction.DESC, "status","createdDate");Page page_data = complaintRepository.findAll(pre,pageable);List list = null;if (page_data != null) {list = page_data.getContent();}List data = new ArrayList();for (Complaint co : list) {ComplaintDTO dto = new ComplaintDTO();BeanUtils.copyProperties(co, dto);if(StringUtils.isNotEmpty(co.getHandledBy())){try {User u=userRepository.findOne(co.getHandledBy());dto.setHandledBy(u.getRealname());} catch (Exception e) {dto.setHandledBy("管理员");}}if(StringUtils.isEmpty(dto.getHandledDetails())){dto.setHandledDetails("");}dto.setCreatedDate(DateUtils.date2String(co.getCreatedDate(), "yyyy-MM-dd"));if (null != co.getHandledDate()) {dto.setHandledDate(DateUtils.date2String(co.getHandledDate(), "yyyy-MM-dd"));}data.add(dto);}Page data_ = new PageImpl(data, pageable, page_data.getTotalElements());return data_;}

分页，排序，模糊查询，条件拼凑

jpa自定义sql案例

package com.bigdata.lab.ymlib.repository;
import java.util.Date;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import com.bigdata.lab.ymlib.model.OrderItem;
public interface OrderItemRepository extends CommonMysqlRepository{@Query("SELECT t.literId,t.title,count(1)*t.count,sum(t.count*t.price) as a from OrderItem t where t.createdDate>=:startTime and t.createdDate<=:endTime "+ "and t.orderId in(select id from Order where status=&＃39;finished&＃39; and payStatus = &＃39;paid&＃39;) group by t.literId order by a desc")List findSaleLites(@Param("startTime") Date startTime,@Param("endTime") Date endTime);}

package com.bigdata.lab.ymlib.repository;
import java.util.Date;
import java.util.List;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import com.bigdata.lab.ymlib.model.SaleActivity;/** ** @author: xuchuang * @date: 2017年10月18日 上午9:59:48 * @version V1.0 */
public interface SaleActivityRepository extends CommonMysqlRepository {@Query("SELECT t from SaleActivity t where ((beginDate>=:startTime and endDate<=:endTime) "+ "or (endDate>=:endTime and beginDate<=:endTime) "+ "or (beginDate<=:startTime and endDate>=:endTime) "+ "or (beginDate<=:startTime and endDate>=:startTime)) and status=1 and name like %:keyWord%" )List findSaleActivities(@Param("startTime") Date startTime,@Param("endTime") Date endTime,@Param("keyWord") String keyWord);@Query("select DISTINCT t.advertisingPosition from SaleActivity t where t.advertisingPosition!=&＃39;null&＃39; ")List findAdvertisingPosition();
}