什么是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.引入相关依赖
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-jpa</artifactId>
</dependency>
<dependency>
<groupId>com.querydsl</groupId>
<artifactId>querydsl-apt</artifactId>
</dependency>
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<Complaint,Serializable>,QueryDslPredicateExecutor<Complaint>{
}
3.使用queryqsdl插件生成查询实体:
pom引入插件:
<build>
<plugins>
<plugin>
<groupId>com.mysema.maven</groupId>
<artifactId>apt-maven-plugin</artifactId>
<version>1.1.3</version>
<executions>
<execution>
<goals>
<goal>process</goal>
</goals>
<configuration>
<outputDirectory>target/generated-sources/java</outputDirectory>
<processor>com.querydsl.apt.jpa.JPAAnnotationProcessor</processor>
</configuration>
</execution>
</executions>
</plugin>
</plugins>
</build>
右键项目,debug as maven generate source成功生成查询实体:
复制到项目当中:下面开始测试 查询:
@RequestMapping("/jpa")
public void testJpa(){
QComplaint qc=QComplaint.complaint;
Predicate pre=qc.id.eq("ff8080816034d03c016034ddccde0003");
List<Complaint> list= (List<Complaint>) 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.pdf
jpa的查询方案官方文档
贴出一些自己项目当中的使用场景:
public Page<ComplaintDTO> 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<Complaint> page_data = complaintRepository.findAll(pre,pageable);
List<Complaint> list = null;
if (page_data != null) {
list = page_data.getContent();
}
List<ComplaintDTO> data = new ArrayList<ComplaintDTO>();
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<ComplaintDTO> data_ = new PageImpl<ComplaintDTO>(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<OrderItem>{
@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='finished' and payStatus = 'paid') group by t.literId order by a desc")
List<Object[]> 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<SaleActivity> {
@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<SaleActivity> findSaleActivities(@Param("startTime") Date startTime,@Param("endTime") Date endTime,@Param("keyWord") String keyWord);
@Query("select DISTINCT t.advertisingPosition from SaleActivity t where t.advertisingPosition!='null' ")
List<String> findAdvertisingPosition();
}