jpa实现查询

清疚 2024-03-31 17:20 69阅读 0赞

#### 一、通过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();

##### 3、排序 #####

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

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

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

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

##### 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是单页查询数量