jpa实现查询

jpa实现查询

一、通过Specification查询

import com.google.common.collect.Lists;
import com.meritdata.cloud.dao.SyslogRepository;
import com.meritdata.cloud.entity.LogEntity;
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.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import javax.persistence.criteria.Predicate;
import java.util.Date;
import java.util.List;
/**
 * @author dxj
 * @since 2022-12-10 16:07
 */
@Service
public class DongService {
    @Autowired
    private SyslogRepository syslogRepository;
    public Page<LogEntity> pageList(LogEntity logEntity, Date startDate, Date endDate,List<String> idList) {
        // 分页排序
        Pageable pageable = PageRequest.of(1, 10, Sort.by(Sort.Direction.DESC, "operateTime"));
        Specification<LogEntity> spe = (Specification<LogEntity>) (root, query, criteriaBuilder) -> {
            List<Predicate> predicates = Lists.newArrayList();
            // 精确查询
            predicates.add(criteriaBuilder.equal(root.get("id"), logEntity.getId()));
            // 模糊查询
            predicates.add(criteriaBuilder.like(root.get("user_name"), "%" + logEntity.getUsername() + "%"));
            // in条件查询
            Expression<String> exp = root.<String>get("departmentId");
            predicates.add(exp.in(idList));
            // 开始时间
            predicates.add(criteriaBuilder.greaterThanOrEqualTo(root.get("operate_time"), startDate));
            // 结束时间
            predicates.add(criteriaBuilder.lessThanOrEqualTo(root.get("operate_time"), endDate));
            return criteriaBuilder.and(predicates.toArray(new Predicate[predicates.size()]));
        };
        Page<LogEntity> list = syslogRepository.findAll(spe, pageable);
        return list;
    }
}

二、通过JPAQueryFactory实现查询

import com.meritdata.cloud.entity.*;
import com.querydsl.core.types.ExpressionUtils;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.Projections;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import java.util.List;
/**
 * @author dongxiajun
 * @since 2022-12-15 15:30
 */
@Service
public class Dong1Service {
    @Autowired
    private JPAQueryFactory jpaQueryFactory;
    public List<ProjectEntity> getProjects(List<String> ids, String proName, String type, int pageNum, int pageSize) {
        QProjectEntity qProjectEntity = QProjectEntity.projectEntity;
        Predicate predicate1 = qProjectEntity.isNotNull().or(qProjectEntity.isNull());
        QLogEntity qLogEntity = QLogEntity.logEntity;
        Predicate predicate2 = qLogEntity.isNotNull().or(qLogEntity.isNull());
        // in 条件
        if (!CollectionUtils.isEmpty(ids)) {
            predicate1 = ExpressionUtils.and(predicate1, qProjectEntity.id.in(ids));
        }
        // 模糊
        if (StringUtils.isNotEmpty(proName)) {
            predicate1 = ExpressionUtils.and(predicate1, qProjectEntity.id.like("%" + proName + "%"));
        }
        // 相等
        if (StringUtils.isNotEmpty(proName)) {
            predicate2 = ExpressionUtils.and(predicate2, qLogEntity.type.eq(type));
        }
        // 不带条件 如果是单表查询
        List<ProjectEntity> projectEntities = jpaQueryFactory.select(qProjectEntity).from(qProjectEntity).fetch();
        // 带条件 如果是单表查询
        List<ProjectEntity> projectEntities1 = jpaQueryFactory.select(qProjectEntity)
                .from(qProjectEntity)
                .where(predicate1).fetch();
        // 查询 单表中的某个字段
        List<String> projectNames = jpaQueryFactory.select(qProjectEntity.name).from(qProjectEntity).fetch();
        // 多表联合查询(根据类型、时间对列表进行排序)
        List<ProLogEntity> proLogs = jpaQueryFactory.select(Projections.bean(ProLogEntity.class,
                qProjectEntity.name, qProjectEntity.type, qLogEntity.operation, qLogEntity.message))
                .from(qProjectEntity)
                .leftJoin(qLogEntity)
                .on(qProjectEntity.type.eq(qLogEntity.type))
                .where(predicate1, predicate2)
                .orderBy(qLogEntity.type.asc(), qLogEntity.time.asc())
                .fetch();
        // 实现分页
        int start = (pageNum - 1) * pageSize;
        JPAQuery<ProjectEntity> jpaQuery = jpaQueryFactory.select(qProjectEntity).from(qProjectEntity);
        List<ProjectEntity> result = jpaQuery.offset(start).limit(pageSize).fetch();
        // 总条数
        long count = jpaQuery.fetchCount();
        return null;
    }
}

JPAQueryFactory实现查询，功能分解

1、无条件，单表查询

List<ProjectEntity> projectEntities = jpaQueryFactory.select(qProjectEntity).from(qProjectEntity).fetch();

2、 有条件，单表查询

List<ProjectEntity> projectEntities1 = jpaQueryFactory.select(qProjectEntity)
                .from(qProjectEntity)
                .where(predicate1).fetch();
1
2
3

3、排序

List<ProjectEntity> projectEntities1 = jpaQueryFactory.select(qProjectEntity)
                .from(qProjectEntity)
                .where(predicate1)
                .orderBy(qProjectEntity.type.asc(), qProjectEntity.time.asc())
                .fetch();
1
2
3
4
5

上面的排序规则，先按照类型排序，再按照时间排序

4、查询单表中某个字段的值

List<String> projectNames = jpaQueryFactory.select(qProjectEntity.name).from(qProjectEntity).fetch();
1

5、多表联合查询

List<ProLogEntity> proLogs = jpaQueryFactory.select(Projections.bean(ProLogEntity.class,
                qProjectEntity.name, qProjectEntity.type, qLogEntity.operation, qLogEntity.message))
                .from(qProjectEntity)
                .leftJoin(qLogEntity)
                .on(qProjectEntity.type.eq(qLogEntity.type))
                .where(predicate1, predicate2)
                .orderBy(qLogEntity.type.asc(), qLogEntity.time.asc())
                .fetch();

这里需要注意Projections.bean()里面的东西，ProLogEntity.class是要输出的对象；后面的值是要输出的参数

6、分页

 int start = (pageNum - 1) * pageSize;
 JPAQuery<ProjectEntity> jpaQuery = jpaQueryFactory.select(qProjectEntity).from(qProjectEntity);
 List<ProjectEntity> result = jpaQuery.offset(start).limit(pageSize).fetch();

offset是偏移量，limit是单页查询数量