2039859.jpg
前提是已经搭好了 JPA 的框架。。。。。
1.使用 JPAQueryFactory 进行查询操作
1.1简单查询
分页构造信息
package cn.superfw.genesis.common.core;
public class ApiPagination {
/** 当前页码 */
private Long page = 1L;
/** 一页表示件数 */
private Long limit = 10L;
/** 总件数 */
private Long count = 0L;
public ApiPagination() {
}
public ApiPagination(Long page, Long limit, Long count) {
if (page != null && page > 1L) {
this.page = page;
}
if (limit != null && limit > 0L) {
this.limit = limit;
}
if (count != null && count > 0L) {
this.count = count;
}
}
public ApiPagination(Long page, Long limit) {
this.page = page;
this.limit = limit;
}
public Long getOffset() {
return (this.page - 1) * this.limit;
}
public Long getOffsetLimit() {
if (getOffset() + getLimit() > getCount()) {
return getCount();
} else {
return getOffset() + getLimit();
}
}
/**
* 获取 当前页码
*
* @return page 当前页码
*/
public Long getPage() {
return this.page;
}
/**
* 设置 当前页码
*
* @param page 当前页码
*/
public void setPage(Long page) {
this.page = page;
}
/**
* 获取 一页表示件数
*
* @return limit 一页表示件数
*/
public Long getLimit() {
return this.limit;
}
/**
* 设置 一页表示件数
*
* @param limit 一页表示件数
*/
public void setLimit(Long limit) {
this.limit = limit;
}
/**
* 获取 总件数
*
* @return count 总件数
*/
public Long getCount() {
return this.count;
}
/**
* 设置 总件数
*
* @param count 总件数
*/
public void setCount(Long count) {
this.count = count;
}
}
自定义返回类
package cn.superfw.genesis.common.core;
public class PlatformServiceResult<T> {
/** 数据 */
private T data;
/** 分页构造器 */
private ApiPagination pagination;
/** 构造函数 */
public PlatformServiceResult(T data, ApiPagination pagination) {
super();
this.data = data;
this.pagination = pagination;
}
/**
* @return the data
*/
public T getData() {
return data;
}
/**
* @param data the data to set
*/
public void setData(T data) {
this.data = data;
}
/**
* @return the pagination
*/
public ApiPagination getPagination() {
return pagination;
}
/**
* @param pagination the pagination to set
*/
public void setPagination(ApiPagination pagination) {
this.pagination = pagination;
}
}
UserRepositoryDsl
package cn.superfw.genesis.zs.repository;
import cn.superfw.genesis.zs.domain.UserEntity;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.querydsl.QuerydslPredicateExecutor;
import org.springframework.data.repository.query.Param;
import java.util.List;
public interface UserRepositoryDsl extends JpaRepository<UserEntity, Long>, QuerydslPredicateExecutor<UserEntity> {
@Query(value="select * from user where (:name is null or name like %:name%) and (coalesce (:ageList,:isnull) is null or age in (:ageList))",
countQuery="select * from user where (:name is null or name like %:name%) and (coalesce (:ageList,:isnull) is null or age in (:ageList))",nativeQuery=true)
Page<UserEntity> getUserByDsl(@Param(value="name") String name,
@Param(value = "ageList") List<Integer> ageList,
@Param(value="isnull") Long isnull,
Pageable pageable);
}
逻辑实现类
//需要用的 jar 包
import com.querydsl.jpa.impl.JPAQueryFactory;
////////////////////////////////////////////////////////////////////////////////
package cn.superfw.genesis.zs.service.Impl;
import cn.superfw.genesis.common.core.ApiPagination;
import cn.superfw.genesis.common.core.PlatformServiceResult;
import cn.superfw.genesis.zs.domain.QUserEntity;
import cn.superfw.genesis.zs.domain.UserEntity;
import cn.superfw.genesis.zs.repository.UserRepositoryDsl;
import cn.superfw.genesis.zs.service.UserService;
import cn.superfw.genesis.zs.vo.RunScheduleVo;
import cn.superfw.genesis.zs.vo.UserVo;
import com.querydsl.core.types.Projections;
import com.querydsl.core.types.dsl.BooleanExpression;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Service;
import java.sql.Timestamp;
import java.util.List;
@Service
public class UserServiceImpl implements UserService {
@Autowired
JPAQueryFactory queryFactory;
@Autowired
UserRepositoryDsl userRepositoryDsl;
/**
* 动态条件查询 hql
* @param id
* @param name
* @param age
* @param money
* @param createdTime
* @return
*/
@Override
public PlatformServiceResult<List<UserEntity>> findUsers(Long id, String name, Integer age, Double money, Timestamp createdTime,Long page, Long limit) {
QUserEntity qUserEntity = QUserEntity.userEntity;
// 拼造查询条件
BooleanExpression whereExp = qUserEntity.deleteStatus.eq(1);
if (id != null){
whereExp = whereExp.and(qUserEntity.id.eq(id));
}
//姓名模糊查询
if(StringUtils.isNotEmpty(name)){
whereExp = whereExp.and(qUserEntity.name.contains(name));
}
if (age != null){
whereExp = whereExp.and(qUserEntity.age.eq(age));
}
if (money != null){
whereExp = whereExp.and(qUserEntity.money.eq(money));
}
if (createdTime != null){
whereExp = whereExp.and(qUserEntity.createdTime.eq(createdTime));
}
//查询所有(使用了上面自己拼接的查询条件)
List<UserEntity> userEntities = queryFactory.selectFrom(qUserEntity).where(whereExp).fetch();
//简单查询（查询 name 叫 周杰伦 的人）单个人 注：.fetch()结尾查询的是列表，.fetchFirst() 或 .fetchOne() 查询的是单个。
UserEntity userEntity = queryFactory.selectFrom(qUserEntity).where(qUserEntity.name.eq("周杰伦")).fetchOne();
//查询某一个字段（查询 id 为 1 的人的名字）
String nName = queryFactory.select(qUserEntity.name).from(qUserEntity).where(qUserEntity.id.eq(id)).fetchFirst();
//查询返回自定义实体类(select中的字段顺序需要和自定义实体类中我的构造器顺序一致)
List<UserVo> userVos = queryFactory.select(Projections.constructor(UserVo.class,qUserEntity.id,qUserEntity.name,qUserEntity.age,qUserEntity.gender,qUserEntity.money))
.from(qUserEntity).fetch();
//分页查询
//查询到的数据数量
Long count = queryFactory.select(qUserEntity.id).from(qUserEntity).where(whereExp).fetchCount();
// 分页控制信息构造
ApiPagination pagination = new ApiPagination(page == null ? 1 : page, limit == null ? 20 : limit,count);
//查询数据
List<UserEntity> data = queryFactory.selectFrom(qUserEntity).where(whereExp).offset(pagination.getOffset()).limit(pagination.getLimit()).fetch();
return new PlatformServiceResult<List<UserEntity>>(data,pagination);
}
/**
* 条件查询人类 sql
* @param name
* @param page
* @param limit
* @return
*/
@Override
public PlatformServiceResult<List<UserEntity>> findUsersForDSL(String name,List<Integer> ageList, Long page, Long limit) {
//DSl中ageList判空时出错，所以自己定义了一个isnull
Long isnull = null;
if (ageList!=null){
if (ageList.size()==0){
ageList=null;
}
}
if (page==null){
page = Long.valueOf(0);
}else {
page = page - 1;
}
//构造分页查询
Sort sort = new Sort(Sort.Direction.DESC, "id");
Pageable pageable = PageRequest.of(page.intValue(),limit == null ? 20 : limit.intValue(),sort );
Page<UserEntity> data = userRepositoryDsl.getUserByDsl(name,ageList,isnull,pageable);
Long count = data.getTotalElements();
ApiPagination pagination = new ApiPagination(page == null ? 1 : page, limit == null ? 20 : limit,count);
return new PlatformServiceResult<List<UserEntity>>(data.getContent(),pagination);
}
}
