×
首页数据库正文

Spring Data -Specification用法和常用查询方法（in，join，equal等）

前言

在这一年里技术更新，从使用Mybatis转为Spring Data,总体感受是终于不用在自己写映射了，也可以少写方法和对应字段了。接下来总结在工作中常用的查询方式和方法，例如equal，join,in等。积少成多，在这里只收藏了用过查询与方法（如果方法可以用得更有，有更多的用法和不足之处请联系我）。

入门例子

controller层，这里是个简单查询获取所有用户并分页

  @GetMapping("/list") 
    @ApiOperation(value = "所有用户列表") 
    public Result list(@RequestParam Map<String, Object> params) { 
        Page page = userService.queryPage(params); 
        return Result.ok().put("page", page); 
    }

接下对service层的实现,功能是实现关键字搜索，这里因为简单并没有单独将Specification提出来，主要是对Specification接口有个大概的认识。

@Override 
    public page queryPage(Map<String, Object> params) { 
        //MapUtils方法用来取除params中的方法，来自于 org.apache.commons.collections.MapUtils; 
        String keyword = MapUtils.getSrting(params,"keyword"); 
        Page page = sysUserRepository.findAll(new Specification<SysUserEntity>() { 
            @Override 
            public Predicate toPredicate(Root<SysUserEntity> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) { 
                List<Predicate> predicates = new ArrayList<>(); 
                if (StringUtils.isNotBlank(keyword)) { 
                    List<Predicate> temp = new ArrayList<>(); 
                    for (String oneKeyword : keywordCopyStr) { 
                        temp.add(criteriaBuilder.like(root.<String>get("mobile"), "%" + oneKeyword + "%")); 
                        temp.add(criteriaBuilder.like(root.<String>get("trueName"), "%" + oneKeyword + "%")); 
                    } 
                    predicates.add(criteriaBuilder.or(temp.toArray(new Predicate[temp.size()]))); 
                } 
                return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction(); 
            } 
        }); 
        return page; 
    }

Repository层中为了支持这样的查询，sysUserRepository需要继承JpaRepository（基本查询），JpaSpecificationExecutor（分页），这个接口是不需要再去实现的，到了Repository层就行，再对此进行扩充（比Mybatis简单多了）。

public interface sysUserRepository extends JpaRepository<SysCaptchaEntity, String>, JpaSpecificationExecutor<SysCaptchaEntity> { 

}

Repository层常用写法

  1. 按着类字段查询 按着类关系关联查询不需要写语句的查询就不在详细讲解，给个例子看看就行
//查询第一个 
EngineerVersionControl findTopByProjectIdAndOldOrderByVersionDesc(String projectId, int old); 
//查询存在 
boolean existsByProjectId(String projectId); 
//排序 
List<EngineerVersionControl> findByProjectIdOrderByVersionDesc(String projectId);
  1. @Query语句查询

一般不会做物理删除，而是逻辑删除。保存操作使用save或者saveAll方法

//更新 必有@Modifying，和使用hiberna一样，HQL语句的写法 
@Transactional(rollbackFor = Exception.class) 
@Modifying 
@Query("update SysUserEntity  u set u.isDelete = ?2 ,u.gmtModified= ?3  where u.id = ?1 ") 
int deleteIsUpdate(String id, int deleted, Date date); 

//查询
 
@Query("select u from SysUserEntity u where  u.id in ?1 and u.isDelete = 0 ")
 
Page<SysUserEntity> findAllUser(List<String> userIds, Pageable pageable);
 

//多表查询
 
@Query(value = " select p  from DictionaryEntity p , DictionaryContentEntity w " +
 
" Where w.ContentEntity.id = ?1 and p.id = w.DictionaryEntity.id and p.deleted = ?2 ORDER BY p.dictionary")
 
List<WebsiteDictionaryEntity> webOnwDictionary(String id,int isDeleted);

3.使用@Query实现写sql语句的查询
再spring data 中不仅有HQl语句，在功能太复杂的时候，可以使用sql语句进行本地查询

  @Query(value="select serve.* from service_serve serve " + 
            "left join company_info_user cominfo on serve.company_info_user_id=cominfo.company_info_user_id" + 
            " left  join user_company company on cominfo.company_info_user_id=company.company_info_user_id " + 
            " left  join employee employeeen3_ on company.user_company_id=employeeen3_.user_company_id " + 
            " left  join user userentity4_  on employeeen3_.user_id=userentity4_.user_id " + 
            " where userentity4_.user_id=?1 and employeeen3_.activity_management_power=1" + 
            " order by serve.gmt_create desc " + 
            "limit ?2 , ?3 " ,nativeQuery = true) 
    List<ServiceServeEntity> queryByCompany(String userId,int startPoint,int endPoint);

4.@Param（value = “name”）查询，这是两种写法。一种?，一种@Param()

 @Query(value="select activity.* from service_activity activity " + 
            "left join company_info_user cominfo on activity.company_id=cominfo.company_info_user_id" + 
            " left  join user_company company on cominfo.company_info_user_id=company.company_info_user_id " + 
            " left  join employee employeeen3_ on company.user_company_id=employeeen3_.user_company_id " + 
            " left  join user userentity4_  on employeeen3_.user_id=userentity4_.user_id " + 
            " where userentity4_.user_id=:userId and employeeen3_.service_management_power=1 and activity.status=:status" + 
            " order by activity.gmt_create desc " + 
            "limit :startPoint , :endPoint " ,nativeQuery = true) 
    List<ServiceActivityEntity> queryByCompanyAndStatus(@Param(value="userId")String userId,@Param(value="status")int status, @Param(value="startPoint")int startPoint, @Param(value="endPoint")int endPoint);

Specification 的用法

下面是个较为全面的例子，将一个较为复杂的查询提取成一个方法。这个方法时使用and的方式拼接，接下来的每一个查询都需要使用把finalConditions拼上，如同 finalConditions = criteriaBuilder.and(finalConditions, taskFastPre)。

public class TaskProjectSpecs { 
    public static Specification<Task> where(Map params, String userId, List<String> taskIds) { 
        //lambda表达式 
        return (Root<Task> root, CriteriaQuery<?> query, CriteriaBuilder criteriaBuilder) -> { 
            //开始 
            Predicate finalConditions = criteriaBuilder.conjunction(); 

        //提取参数 
        String taskFast = MapUtils.getString(params, "taskFast"); 
 
        //lile 和join 用法 join可跟,JoinType.LEFT等 
        if (StringUtils.isNotBlank(taskFast)) { 
            Predicate taskFastPre = criteriaBuilder.like(root.join("taskType",JoinType.LEFT).&lt;String&gt;get("id"), "%" + taskFast + "%"); 
            finalConditions = criteriaBuilder.and(finalConditions, taskFastPre); 
        } 
        //between用法 
        if ((null != createBegin) &amp;&amp; (null != createEnd)) { 
            Predicate datePredicate = null; 
            if (createBegin.after(createEnd)) { 
                datePredicate = criteriaBuilder.between(root.get("gmtCreate"), createEnd, createBegin); 
            } else { 
                datePredicate = criteriaBuilder.between(root.get("gmtCreate"), createBegin, createEnd); 
            } 
            finalConditions = criteriaBuilder.and(finalConditions, datePredicate); 
        } 
        //equale 
        if (null != emergency &amp;&amp; 0 != emergency) { 
            finalConditions = criteriaBuilder.and(finalConditions, criteriaBuilder.equal(root.get("emergencyLevel"), emergency)); 
        } 
        //大于 不等于 
        if (status != null) { 
            finalConditions = criteriaBuilder.and(finalConditions, criteriaBuilder.greaterThan(root.get("startDate"), new Date())); 
            finalConditions = criteriaBuilder.and(finalConditions, criteriaBuilder.notEqual(root.get("status"), 1)); 
             
        } 
        // or  
        if (StringUtils.isNotBlank(keyword)) { 
            finalConditions = criteriaBuilder.and(finalConditions, criteriaBuilder.or( 
                    criteriaBuilder.like(root.get("taskName"), "%" + keyword + "%"), 
                    criteriaBuilder.like(root.join("project").get("name"), "%" + keyword + "%")) 
            ); 
        } 
        //in 
        if (taskIds.size() &gt; 0) { 
            CriteriaBuilder.In&lt;Object&gt; in = criteriaBuilder.in(root.get("id")); 
            for (String id : taskIds) { 
                in.value(id); 
            } 
            finalConditions = criteriaBuilder.and(finalConditions, in); 
        } 
        return query.where(finalConditions).getRestriction(); 
    }; 
} 

 

}

上面的方法是and凭借，还有一种add的方法，本质一样，都是构建query.where（）查询。

public class UserSpecs { 
    public static Specification<SysUserEntity> where(String keyword, Date createdAtBegin, Date createdAtEnd, List<String> userIds) { 
        return (Root<SysUserEntity> root, CriteriaQuery<?> query, CriteriaBuilder cb) -> { 
            List<Predicate> predicates = new ArrayList<>(); 
            if (StringUtils.isNotBlank(keyword)) { 
                List<Predicate> temp = new ArrayList<>(); 
                Set<String> keywordCopyStr = StringUtil.cutToArray(keyword); 
                for (String oneKeyword : keywordCopyStr) { 
                    temp.add(cb.like(root.<String>get("mobile"), "%" + oneKeyword + "%")); 
                    temp.add(cb.like(root.<String>get("trueName"), "%" + oneKeyword + "%")); 
                } 
                predicates.add(cb.or(temp.toArray(new Predicate[temp.size()]))); 
            } 
            //未删除 
            predicates.add(cb.equal(root.get("isDelete"), Constant.NOT_DELETED)); 
            query.where(predicates.toArray(new Predicate[predicates.size()])); 
            return query.getRestriction(); 
        }; 
    } 
} 
return query.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();

总结

基本将常用的都包含完了，以后遇到了新的写法再更新上去。

      </div>

评论关闭
IT序号网
IT序号网 Spring

微信公众号号：IT虾米 （左侧二维码扫一扫）欢迎添加！