灵活的 QueryWrapper ​

@SpringBootTest
@Slf4j
public class JpaQueryDSLTest {

    @Autowired
    private ICategoryService categoryService;

    @Autowired
    private JPAQueryFactory queryWrapper;


    /**
     * select() 和 fetch() 的常用写法
     * 使用fetch()查询时，数据库没有符合该条件的数据时，返回的是空集合，而不是null
     */

    /**
     * 查询字段-select()
     */
    @Test
    public void fetchColum() {
        QCategory qCategory = QCategory.category;
        List<String> a = queryWrapper
                .select(qCategory.title)
                .from(qCategory)
                .fetch();
        log.info("返回数量{}", a.size());
    }

    /**
     * 查询实体-selectFrom()
     */
    @Test
    public void fetchEntity() {
        QCategory qCategory = QCategory.category;
        List<Category> categories = queryWrapper.selectFrom(qCategory).fetch();
        log.info("返回数量{}", categories.size());

    }

    /**
     * 查询并将结果封装至dto中
     */
    @Test
    public void fetchDto() {
        QCategory qCategory = QCategory.category;
        List<CategoryDto> categoryDtos = queryWrapper.select(
                        Projections.bean(CategoryDto.class, qCategory.title)
                )
                .from(qCategory).fetch();
        log.info("返回数量{}", categoryDtos.size());

    }

    /**
     * 去重查询-selectDistinct()
     */
    @Test
    public void fetchDistinct() {
        QCategory qCategory = QCategory.category;
        List<String> c = queryWrapper
                .selectDistinct(qCategory.title)
                .from(qCategory)
                .fetch();
        log.info("返回数量{}", c.size());
    }

    /**
     * 获取首个查询结果-fetchFirst() 单条记录 limit 1
     */
    @Test
    public void fetchFirst() {
        QCategory qCategory = QCategory.category;
        Category category = queryWrapper
                .selectFrom(qCategory)
                .fetchFirst();
        log.info("返回数量{}", category.toString());
    }

    /**
     * 获取唯一查询结果-fetchOne()
     * 当fetchOne()根据查询条件从数据库中查询到多条匹配数据时，会抛`NonUniqueResultException`
     */
    @Test
    public void fetchOne() {
        QCategory qCategory = QCategory.category;
        Category category = queryWrapper
                .selectFrom(qCategory)
                .fetchOne();
        log.info("返回数量{}", category.toString());
    }


    /**
     * where 子句查询条件的常用写法
     */
    @Test
    public void fetchWhere() {
        QCategory qCategory = QCategory.category;
        List<Category> categories = queryWrapper
                .selectFrom(qCategory)
                .where(qCategory.title.eq("更新")
                        .and(qCategory.subTitle.like('%' + "测试" + '%')))
                .fetch();
        log.info("返回数量{}", categories.size());

    }

    /**
     * where 动态条件查询
     */

    /**
     * 使用QueryDSL提供的BooleanBuilder来进行查询条件管理。
     */
    @Test
    public void fetchWhereDynamic() {
        QCategory qCategory = QCategory.category;
        BooleanBuilder builder = new BooleanBuilder();
        String title = "a";
        if (StrUtil.isNotEmpty(title)) {
            builder.and(qCategory.title.eq(title));
        }
        String subTitle = "";
        if (StrUtil.isNotEmpty(subTitle)) {
            builder.and(qCategory.subTitle.eq(subTitle));
        }
        List<Category> categories = queryWrapper
                .selectFrom(qCategory)
                .where(builder)
                .fetch();
        log.info("返回数量{}", categories.size());

    }

    /**
     * 复杂的查询关系
     */
    @Test
    public void fetchWhereDynamicComplex() {
        QCategory qCategory = QCategory.category;

        BooleanBuilder builder = new BooleanBuilder();
        builder.or(qCategory.id.eq(1l));

        String title = "a";
        if (StrUtil.isNotEmpty(title)) {
            builder.and(qCategory.title.eq(title));
        }
        String subTitle = "";
        if (StrUtil.isNotEmpty(subTitle)) {
            builder.and(qCategory.subTitle.eq(subTitle));
        }


        List<Category> categories = queryWrapper
                .selectFrom(qCategory)
                .where(builder)
                .fetch();
        log.info("返回数量{}", categories.size());
    }


    /**
     * 自定义封装查询的结果集
     * JPAQueryFactory查询工厂的select方法可以将Projections方法返回的QBean作为参数，通过Projections的bean方法来构建返回的结果集映射到实体内，有点像Mybatis内的ResultMap的形式，不过内部处理机制肯定是有着巨大差别的！
     * <p>
     * bean方法第一个参数需要传递一个实体的泛型类型作为返回集合内的单个对象类型，如果QueryDSL查询实体内的字段与DTO实体的字段名字不一样时，可以采用as方法来处理，为查询的结果集指定的字段添加别名，这样就会自动映射到DTO实体内。
     */

    /**
     * 使用Projections的Bean方法
     */
    @Test
    public void fetchBean() {
        QCategory qCategory = QCategory.category;
        QModule qModule = QModule.module;

        List<CategoryDto> categoryDtos = queryWrapper
                .select(
                        Projections.bean(CategoryDto.class
                                , qCategory.title, qModule.code)
                ).from(qCategory, qModule).fetch();
        log.info("返回数量{}", categoryDtos.size());
    }

    /**
     * 使用Projections的fields方法
     */
    @Test
    public void fetchFields() {
        QCategory qCategory = QCategory.category;
        List<CategoryDto> categoryDtos = queryWrapper
                .select(
                        Projections.fields(CategoryDto.class
                                , qCategory.title)
                ).from(qCategory).fetch();
        log.info("返回数量{}", categoryDtos.size());
    }

    /**
     * 使用集合的stream转换
     * 从方法开始到fetch()结束完全跟QueryDSL没有任何区别，采用了最原始的方式进行返回结果集，但是从fetch()获取到结果集后处理的方式就有所改变了。
     * <p>
     * fetch()方法返回的类型是泛型List（List），List继承了Collection，完全存在使用Collection内非私有方法的权限，通过调用stream方法可以将集合转换成Stream泛型对象，该对象的map方法可以操作集合内单个对象的转换，具体的转换代码可以根据业务逻辑进行编写。
     * <p>
     * 在map方法内有个lambda表达式参数tuple，通过tuple对象get方法就可以获取对应select方法内的查询字段。
     * ————————————————
     */
    @Test
    public void selectWithStream() {
        QCategory qCategory = QCategory.category;
        List<CategoryDto> categoryDtos = queryWrapper
                .select(qCategory.title, qCategory.subTitle)
                .from(qCategory)
                .fetch().stream().map(tuple -> {
                    CategoryDto c = new CategoryDto();
                    c.setTitle(tuple.get(qCategory.title));
                    return c;
                }).collect(Collectors.toList());

        log.info("返回数量{}", categoryDtos.size());
    }


    @Test
    public void findByQuery() {
        QCategory qCategory = QCategory.category;
        //该Predicate为querydsl下的类,支持嵌套组装复杂查询条件
        BooleanBuilder builder = new BooleanBuilder();
        String title = "a";
        if (StrUtil.isNotEmpty(title)) {
            builder.and(qCategory.title.eq(title));
        }
        String subTitle = "";
        if (StrUtil.isNotEmpty(subTitle)) {
            builder.and(qCategory.subTitle.eq(subTitle));
        }
        List<Category> c = categoryService.list(builder);
       log.info("条数{}",c.size());
    }

    @Test
    public void findByQueryWrapper(){
        QCategory qCategory = QCategory.category;
        JPAQueryFactory queryWrapper=QueryWrapper.of();
        List<String> c = queryWrapper
                .selectDistinct(qCategory.title)
                .from(qCategory)
                .fetch();
        log.info("返回数量{}", c.size());
    }

}

@Service
@Transactional
public class UserService {

	@Autowired
    private JPAQueryFactory queryFactory;

	/**
	 * attention:
	 * Details：查询user表中的所有记录
	 */
	public List<User> findAll(){
		QUser quser = QUser.user;
		return queryFactory.selectFrom(quser)
					.fetch();
	}

	/**
	 * Details：单条件查询
	 */
	public User findOneByUserName(final String userName){
		QUser quser = QUser.user;
		return queryFactory.selectFrom(quser)
			.where(quser.name.eq(userName))
			.fetchOne();
	}

	/**
	 * Details：单表多条件查询
	 */
	public User findOneByUserNameAndAddress(final String userName, final String address){
		QUser quser = QUser.user;
		return queryFactory.select(quser)
			.from(quser) // 上面两句代码等价与selectFrom
			.where(quser.name.eq(userName).and(quser.address.eq(address)))// 这句代码等同于where(quser.name.eq(userName), quser.address.eq(address))
			.fetchOne();
	}

	/**
	 * Details：使用join查询
	 */
	public List<User> findUsersByJoin(){
		QUser quser = QUser.user;
		QUser userName = new QUser("name");
		return queryFactory.selectFrom(quser)
			.innerJoin(quser)
			.on(quser.id.intValue().eq(userName.id.intValue()))
			.fetch();
	}

	/**
	 * Details：将查询结果排序
	 */
	public List<User> findUserAndOrder(){
		QUser quser = QUser.user;
		return queryFactory.selectFrom(quser)
			.orderBy(quser.id.desc())
			.fetch();
	}

	/**
	 * Details：Group By使用
	 */
	public List<String> findUserByGroup(){
		QUser quser = QUser.user;
		return queryFactory.select(quser.name)
					.from(quser)
					.groupBy(quser.name)
					.fetch();
	}

	/**
	 * Details：删除用户
	 */
	public long deleteUser(String userName){
		QUser quser = QUser.user;
		return queryFactory.delete(quser).where(quser.name.eq(userName)).execute();
	}

	/**
	 * Details：更新记录
	 */
	public long updateUser(final User u, final String userName){
		QUser quser = QUser.user;
		return queryFactory.update(quser).where(quser.name.eq(userName))
			.set(quser.name, u.getName())
			.set(quser.age, u.getAge())
			.set(quser.address, u.getAddress())
			.execute();
	}

	/**
	 * Details：使用原生Query
	 */
	public User findOneUserByOriginalSql(final String userName){
		QUser quser = QUser.user;
		Query query = queryFactory.selectFrom(quser)
				.where(quser.name.eq(userName)).createQuery();
		return (User) query.getSingleResult();
	}


    /**
     *分页查询所有的实体,根据uIndex字段排序
     *
     * @return
     */
    public QueryResults<User> findAllPage(Pageable pageable) {
        QUser user = QUser.user;
        return jpaQueryFactory
                .selectFrom(user)
                .orderBy(user.uIndex.asc())
                .offset(pageable.getOffset())   //偏移量，计算：offset = ( 当前页 - 1) * 每页条数，这里直接使用的是Pageable中的Offset
                .limit(pageable.getPageSize())  //每页大小
                .fetchResults();    //获取结果，该结果封装了实体集合、分页的信息，需要这些信息直接从该对象里面拿取即可
    }


    /**
     * 部分字段映射查询
     * 投影为UserRes,lambda方式(灵活，类型可以在lambda中修改)
     *
     * @return
     */
    public List<UserDTO> findAllUserDto(Pageable pageable) {
        QUser user = QUser.user;
        List<UserDTO> dtoList = jpaQueryFactory
                .select(
                        user.username,
                        user.userId,
                        user.nickName,
                        user.birthday
                )
                .from(user)
                .offset(pageable.getOffset())
                .limit(pageable.getPageSize())
                .fetch()
                .stream()
                .map(tuple -> UserDTO.builder()
                        .username(tuple.get(user.username))
                        .nickname(tuple.get(user.nickName))
                        .userId(tuple.get(user.userId).toString())
                        .birthday(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(tuple.get(user.birthday)))
                        .build()
                )
                .collect(Collectors.toList());
        return dtoList;
    }


    /**
     * 部分字段映射查询
     * 投影为UserRes，自带的Projections方式，不能转换类型，但是可以使用as转换名字
     *
     * @return
     */
    public List<UserDTO> findAllDto2() {
        QUser user = QUser.user;
        List<UserDTO> dtoList = jpaQueryFactory
                .select(
                        Projections.bean(
                                UserDTO.class,
                                user.username,
                                user.userId,
                                user.nickName,
                                user.birthday
                        )
                )
                .from(user)
                .fetch();
        return dtoList;
    }


}

/**
 * @Description 查询全部
 * @Author 程序员三时
 * @Date  10:53
 * @return java.util.List<com.cs.querydsl.model.Loc>
 **/
@Override
public List<Loc> findAll(Loc loc) {
    // 使用 QueryDSL 进行查询
    QLoc qLoc = QLoc.loc1;
    QUser qUser = QUser.user;
    // 定于获取条件
    BooleanBuilder booleanBuilder = new BooleanBuilder();
    // 要查询的条件
    if(!StringUtils.isEmpty(loc.getLoc())){
        // 放入要查询的条件信息
        booleanBuilder.and(qLoc.loc.contains(loc.getLoc()));
    }
    //连接查询条件（Loc.id = User.id ）
    booleanBuilder.and(qLoc.id.eq(qUser.id));
    // 使用 QueryDSL 进行多表联合查询
    QueryResults<Tuple> listResult = queryFactory
            .select(QLoc.loc1,QUser.user)
            .from(qLoc, qUser)//查询两表
            .where(booleanBuilder)
            .fetchResults();
    //遍历 java8 自带流转换成集合
    List<Loc> collect = listResult.getResults().stream().map(tuple -> {
        Loc lcs = tuple.get(qLoc);
        return lcs;
    }).collect(Collectors.toList());
    return collect;
}



部分字段映射的投影查询：

当使用`@ManyToOne`、`@ManyToMany`建立关联时：

/**
 * 根据部门的id查询用户的基本信息+用户所属部门信息，并且使用UserDeptDTO进行封装返回给前端展示
 * @param departmentId
 * @return
 */
public List<UserDeptDTO> findByDepatmentIdDTO(int departmentId) {
    QUser user = QUser.user;
    QDepartment department = QDepartment.department;
    //直接返回
    return jpaQueryFactory
            //投影只去部分字段
            .select(
                    user.username,
                    user.nickName,
                    user.birthday,
                    department.deptName,
                    department.createDate

            )
            .from(user)
            //联合查询
            .join(user.department, department)
            .where(department.deptId.eq(departmentId))
            .fetch()
            //lambda开始
            .stream()
            .map(tuple ->
                    //需要做类型转换，所以使用map函数非常适合
                    UserDeptDTO.builder()
                            .username(tuple.get(user.username))
                            .nickname(tuple.get(user.nickName))
                            .birthday(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(tuple.get(user.birthday)))
                            .deptName(tuple.get(department.deptName))
                            .deptBirth(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(tuple.get(department.createDate)))
                            .build()
            )
            .collect(Collectors.toList());
}


当使用id建立关联时：
/**
 * 根据部门的id查询用户的基本信息+用户所属部门信息，并且使用UserDeptDTO进行封装返回给前端展示
 *
 * @param departmentId
 * @return
 */
public List<UserDeptDTO> findByDepatmentIdDTO(int departmentId) {
    QUser user = QUser.user;
    QDepartment department = QDepartment.department;
    //直接返回
    return jpaQueryFactory
            //投影只去部分字段
            .select(
                    user.username,
                    user.nickName,
                    user.birthday,
                    department.deptName,
                    department.createDate

            )
            .from(user, department)
            //联合查询
            .where(
                    user.departmentId.eq(department.deptId).and(department.deptId.eq(departmentId))
            )
            .fetch()
            //lambda开始
            .stream()
            .map(tuple ->
                    //需要做类型转换，所以使用map函数非常适合
                    UserDeptDTO.builder()
                            .username(tuple.get(user.username))
                            .nickname(tuple.get(user.nickName))
                            .birthday(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(tuple.get(user.birthday)))
                            .deptName(tuple.get(department.deptName))
                            .deptBirth(new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(tuple.get(department.createDate)))
                            .build()
            )
            .collect(Collectors.toList());
}


使用 Projections 自定义返回 Bean：

/**
 * Details：方式一：使用Bean投影
 */
public List<PersonIDCardDto> findByDTOUseBean(){
    Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
    return queryFactory.select(
            Projections.bean(PersonIDCardDto.class, QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name))
            .from(QIDCard.iDCard, QPerson.person)
            .where(predicate)
            .fetch();
}

/**
 * Details：方式二：使用fields来代替setter
 */
public List<PersonIDCardDto> findByDTOUseFields(){
    Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
    return queryFactory.select(
            Projections.fields(PersonIDCardDto.class, QIDCard.iDCard.idNo, QPerson.person.address, QPerson.person.name))
            .from(QIDCard.iDCard, QPerson.person)
            .where(predicate)
            .fetch();
}

/**
 * Details：方式三：使用构造方法，注意构造方法中属性的顺序必须和构造器中的顺序一致
 */
public List<PersonIDCardDto> findByDTOUseConstructor(){
    Predicate predicate = (QPerson.person.id.intValue()).eq(QIDCard.iDCard.person.id.intValue());
    return queryFactory.select(
            Projections.constructor(PersonIDCardDto.class, QPerson.person.name, QPerson.person.address, QIDCard.iDCard.idNo))
            .from(QIDCard.iDCard, QPerson.person)
            .where(predicate)
            .fetch();
}