最近開發(fā)遇到的一些查詢用例驰凛，簡單記錄下

一、定義Repository
@Repository
public interface TransTaskManager extends JpaRepository<TransTask, Long>, JpaSpecificationExecutor<TransTask> {}

二摊沉、Repository查詢語句

1，對象作為參數(shù)傳輸
@Modifying
@Transactional
@Query("update TransTask t set t.flowStatus =:#{#transTask.flowStatus} where t.flowStatus =:#{#oldStatus}")
int updateFlowStatus(@Param("transTask") TransTask transTask, @Param("oldStatus") int oldStatus);

2舶替，字符串作為參數(shù)：
@Modifying
@Transactional
@Query("update TransTask t set t.transId =:transId where t.taskId =:taskId")
int updateTransId(@Param("transId") String transId, @Param("taskId") String taskId);

3豆赏，參數(shù)定位
@Query(" from TransTask t where t.taskId = ?1 and t.taskName like ?2 and t.flowStatus=100 and t.isDelete=2 ")
Page<TransTask> queryByParams(@Param("taskId") String taskId, @Param("taskName") String taskName, Pageable pageable);

4束凑，原生sql
@Query(value = "select t.playUrl from ty_trans_task t where t.taskId =:taskId", nativeQuery = true)
String getPlayUrls(@Param("taskId") String taskId);

5晒旅，Example_實例查詢
Person person = new Person();
person.setFirstname("Dave"); //Firstname = 'Dave'

ExampleMatcher matcher = ExampleMatcher.matching() .withMatcher("name", GenericPropertyMatchers.startsWith()) //姓名采用“開始匹配”的方式查詢
.withIgnorePaths("int"); //忽略屬性：是否關(guān)注。因為是基本類型汪诉，需要忽略掉

Example<Person> example = Example.of(person, matcher); //Example根據(jù)域?qū)ο蠛团渲脛?chuàng)建一個新的ExampleMatcher

6废恋，specification查詢

@Transactional(readOnly = true)
public Page<TransTaskRelExt> query(TransTaskReq req) {

PageRequest pageRequest = new PageRequest(req.getPageNum(), req.getPageSize(), Sort.Direction.valueOf(req.getOrder()), req.getSort());

Specification<TransTaskRelExt> specification = new Specification<TransTaskRelExt>() {
@Override
public Predicate toPredicate(Root<TransTaskRelExt> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

                List<Predicate> predicates = new ArrayList<>();

            
                //任務(wù)編號
                if (StringUtils.isNotBlank(req.getTaskId())) {
                    predicates.add(cb.equal(root.get("taskId").as(String.class), req.getTaskId()));
                }

                //任務(wù)名稱
                if (StringUtils.isNotBlank(req.getTaskName())) {
                    predicates.add(cb.like(root.get("taskName").as(String.class), "%" + req.getTaskName() + "%"));
                }

        // in 第一種寫法
        Expression<String> exp = root.<String>get("id");//root.get("id").as(String.class)
                predicates.add(exp.in(noticeIds));
                
        // in 第二種寫法
        CriteriaBuilder.In<Object> in = cb.in(root.get("id"));
        in.value("個人");
        in.value("企業(yè)");
        predicates.add(in);
                
        //in例子
                
        In<Long> in = cb.in(root.get(BailPay_.customerId));
        in.value(customer.id());
        predicates.add(in);
                     
                    
                    
        //or
        Predicate p = cb.equal(root.get("status").as(String.class), status);
        for (int i = 1; i < statuses.length; ++i) {
            p = cb.or(p, cb.equal(root.get("status").as(String.class), statuses[i]));
                }
        list.add(p);
                        
                        
        //or 例子
        Predicate p1=cb.like(root.get(“name”).as(String.class), “%”+uqm.getName()+“%”);
        Predicate p2=cb.equal(root.get("uuid").as(Integer.class), uqm.getUuid());
        Predicate p3=cb.gt(root.get("age").as(Integer.class), uqm.getAge());

        構(gòu)建組合的Predicate示例：
        Predicate p = cb.and(p3,cb.or(p1,p2));
                    
                Predicate[] pre = new Predicate[predicates.size()];
                return query.where(predicates.toArray(pre)).getRestriction();
            }
        };

        return transTaskRelExtManager.findAll(specification, pageRequest);
    }