1.EntityManager

group by 指定字段

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> query = cb.createTupleQuery();
Root<Student> root = query.from(Student.class);

List<Predicate> predicates = Lists.newArrayList();
predicates.add(cb.in(root.get("status")).value(Lists.newArrayList(0, 1)));
if (StringUtils.isNotBlank(name)) {
    predicates.add(cb.like(root.get("name"), "%" + name + "%"));
}

CriteriaQuery<Tuple> criteriaQuery = query.select(cb.tuple(root.get("name"), root.get("id"))).where(predicates.toArray(new Predicate[0])).groupBy(root.get("name"), root.get("id"));
entityManager.createQuery(criteriaQuery).getResultList();

2.query dsl

QStudent student = QStudent.Student;
jpaQueryFactory.selectFrom(student).where(student.name.eq("lucy")).fetchFirst();

以上两种是定义DAO的实现类。在实现类中写查询方法，第一种需要注入EntityManager，第二种需要注入JpaQueryFactory。

下面在接口中写动态sql

3.JpaSpecificationExecutor

// duty 包含 post ，post 包含 department
default List<Duty> list(String start, String end, Collection<Department> departments, Collection<String> names) {
    return this.findAll((Specification<Duty>) (root, query, cb) -> {
        List<Predicate> predicates = Lists.newArrayList();
        predicates.add(cb.equal(root.get("flag"), true));
        if (StringUtils.isNotBlank(start)) {
            predicates.add(cb.greaterThanOrEqualTo(root.get("date"), start));
        }
        if (StringUtils.isNotBlank(end)) {
            predicates.add(cb.lessThanOrEqualTo(root.get("date"), end));
        }
        if (CollectionUtils.isNotEmpty(departments)) {
            predicates.add(cb.in(root.get("post").get("department")).value(departments));
        }
        if (CollectionUtils.isNotEmpty(names)) {
            predicates.add(cb.in(root.get("post").get("name")).value(names));
        }
        return cb.and(predicates.toArray(new Predicate[0]));
    });
}

4.QuerydslPredicateExecutor

default Iterable<Student> findByParams(String name, String telephone) {
    QStudent student = QStudent.student;
    BooleanBuilder builder = new BooleanBuilder();
    if (StringUtils.isNotBlank(name)) {
        builder.and(messageMember.name.like("%" + name + "%"));
    }
    if (StringUtils.isNotBlank(telephone)) {
        builder.and(messageMember.telephone.like("%" + telephone + "%"));
    }
    return this.findAll(builder);
}

代码结构

实现类和接口方式组合。下列接口StudentDao就具有了selectCustom方法

interface StudentDao extends StudentCustomDao, JpaRepository<Student, Integer>, JpaSpecificationExecutor<Student>, QuerydslPredicateExecutor<Student> {
    Page<Student> selectAll(Pageable of);

    default Student selectOne(Integer id){

    }

}



interface StudentCustomDao {
    Page<Student> selectCustom();
}

class StudentCustomDaoImpl implments StudentCustomDao {

    EntityManager entityManager;
    

    public Page<Student> selectCustom(){

    }

}