org.springframework.data.repository.query.Param Java Examples

@Transactional
@Compensable(retries = 3, compensationMethod = "updateBalanceByMerchantIdRollback")
public int updateBalanceByIdRetry(@Param("merchantid") long merchantid, @Param("balance") double balance) {
    LOG.error("Executing method 'updateBalanceByMerchantIdRetry'.");
    int result = merchantRepository.updateBalanceById(merchantid, balance);
    if (balance == 2) {
        throw new RuntimeException("Retry, can not deduct money");
    }

    return result;
}
 

@RequestMapping(method = RequestMethod.POST, value = "/search")
public String search(@Param("name") String name, Model model) {
    MvcResult result = bookAnalysisService.searchByName(name);
    if (!result.isSuccess()) {
        model.addAttribute("msg", result.getMessage());
        return "public/error";
    }
    model.addAttribute("list", result.getVal("list"));
    model.addAttribute("name", name);
    return "front/search/list";
}
 

@Transactional
@Modifying(clearAutomatically = true)
@Query("UPDATE org.apache.servicecomb.saga.alpha.core.Command c "
    + "SET c.status = :toStatus "
    + "WHERE c.globalTxId = :globalTxId "
    + "  AND c.localTxId = :localTxId "
    + "  AND c.status = :fromStatus")
void updateStatusByGlobalTxIdAndLocalTxId(
    @Param("fromStatus") String fromStatus,
    @Param("toStatus") String toStatus,
    @Param("globalTxId") String globalTxId,
    @Param("localTxId") String localTxId);
 

/**
 * Fetch a listing of products, given a limit and offset.
 */
@RequestMapping(method = RequestMethod.GET, value = "/products", produces = "application/json")
public @ResponseBody ResponseEntity<List<ProductMetadata>> getProducts(@Param("limit") int limit,
                                                                       @Param("offset") int offset) {
  List<ProductMetadata> products = productCatalogServiceRest.getProducts(limit, offset);
  return new ResponseEntity<List<ProductMetadata>>(products, HttpStatus.OK);
}
 

public int updateBalanceByMerchantIdRollback(@Param("merchantid") long merchantid, @Param("balance") double balance) {
    try {
        LOG.error("Executing method 'updateBalanceByUserIdRollback'.");
        int result = merchantRepository.updateBalanceById(merchantid, -balance);
        if (result < 1) {
            LOG.error("Failed to execute 'merchantRepository.updateBalanceById([{}], -[{}])'.", merchantid, balance);
        }
        return result;
    } catch (Exception e) {
        JsonObject jsonParams = new JsonObject();
        try {
            jsonParams.addProperty("type", AccidentHandleType.ROLLBACK_ERROR.toInteger());
            jsonParams.addProperty("globaltxid", omegaContext.globalTxId());
            jsonParams.addProperty("localtxid", omegaContext.localTxId());
            jsonParams.addProperty("remark", "Failed to execute Compensable SQL [UPDATE MerchantEntity T SET T.balance = T.balance + " + balance + " WHERE id = " + merchantid + "].");
            MerchantEntity merchantEntity = null;
            try {
                merchantEntity = merchantRepository.findOne(merchantid);
            } catch (Exception e1) {
                // 如果被补偿的接口是因为数据库连接数过大等数据库原因，那么此处findOne方法也会执行失败，所以捕获下
                LOG.error("Failed to execute method 'merchantRepository.findOne([{}])', params [{}].", merchantid, jsonParams.toString(), e1);
            }
            if (merchantEntity != null) {
                jsonParams.addProperty("bizinfo", merchantEntity.toJsonString());
            } else {
                jsonParams.addProperty("bizinfo", "{\"merchantid\": " + merchantid + "}");
            }
            LOG.error("Failed to execute method 'updateBalanceByMerchantIdRollback', params [{}].", jsonParams.toString(), e);
            clientAccidentHandlingService.reportMsgToAccidentPlatform(jsonParams.toString());
        } catch (Exception e2) {
            LOG.error("Failed to report accident for method 'updateBalanceByMerchantIdRollback', params [{}].", jsonParams.toString(), e2);
        }
        // 不要抛出异常，否则org.apache.servicecomb.saga.omega.context.CompensationContext中报(IllegalAccessException | InvocationTargetException)错误
    }
    return 0;
}
 

@Transactional
@Compensable(compensationMethod = "updateBalanceByMerchantIdRollback")
public int updateBalanceById(@Param("merchantid") long merchantid, @Param("balance") double balance) {
    int result = merchantRepository.updateBalanceById(merchantid, balance);
    // 手动补偿场景：由业务人员自行收集所影响的数据信息
    messageSender.reportMessageToServer(new KafkaMessage(drivername, dburl, dbusername, "txle_sample_merchant", "update", merchantid + ""));
    if (balance > 200) {
        throw new RuntimeException("The 'Merchant' Service threw a runtime exception in case of balance was more than 200.");
    }
    return result;
}
 

@Query("MATCH (user:User) WHERE exists(user.pagerank) AND exists(user.screenName) AND coalesce(user.imported, false) = true\n" +
        "WITH user\n" +
        "ORDER BY user.pagerank DESC\n" +
        "SKIP {skip}\n" +
        "LIMIT {limit}\n" +
        "RETURN user")
Set<User> findRankedUsers(@Param("skip") Integer skip, @Param("limit") Integer limit);
 

@Transactional
@AutoCompensable
public int updateBalanceByIdAuto(@Param("merchantid") long merchantid, @Param("balance") double balance) {
    if (balance == 2) {
        throw new RuntimeException("Merchant auto normal throw exception");
    }
    return merchantRepository.updateBalanceById(merchantid, balance);
}
 

@Query(" SELECT childAttribute FROM PluginPackageAttribute childAttribute " +
        "LEFT OUTER JOIN PluginPackageAttribute parentAttribute ON childAttribute.pluginPackageAttribute.id=parentAttribute.id " +
        "LEFT OUTER JOIN PluginPackageEntity entity ON parentAttribute.pluginPackageEntity.id=entity.id " +
        "LEFT OUTER JOIN PluginPackageDataModel dataModel ON entity.pluginPackageDataModel.id=dataModel.id " +
        "WHERE dataModel.packageName=:packageName AND entity.name=:entityName and dataModel.version=:dataModelVersion")
Optional<List<PluginPackageAttribute>> findAllChildrenAttributes(
        @Param("packageName") String packageName,
        @Param("entityName") String entityName,
        @Param("dataModelVersion") int dataModelVersion);
 

@Query(value = "SELECT * FROM sys_advertise s WHERE s.sort>=:sort AND s.status=0 AND s.sys_advertise_location=:cate ORDER BY sort ASC ",nativeQuery = true)
List<SysAdvertise> querySysAdvertise(@Param("sort") int sort,@Param("cate") int cate);
 

@Query("SELECT n FROM Note n WHERE n.title = :title")
List<Note> findByTitleNamedBind(@Param("title") String title);
 

@Modifying
@Query("update Advertise a set a.dealAmount=a.dealAmount-:amount where a.dealAmount>=:amount  and a.id=:id")
int updateAdvertiseDealAmount( @Param("id") Long id, @Param("amount") BigDecimal amount);
 

@Query(
        value = "select shift from Shift shift where shift.teamId = :teamId and shift.jobId = :jobId and shift.start >= :startTime and shift.start < :endTime"
)
List<Shift> listShiftByJobId(@Param("teamId") String teamId, @Param("jobId") String jobId, @Param("startTime") Instant start, @Param("endTime") Instant end);
 

@Query("select t from TaskNodeExecParamEntity t " + " where t.requestId = :requestId and t.paramType = :paramType ")
List<TaskNodeExecParamEntity> findAllByRequestIdAndParamType(@Param("requestId") String requestId,
        @Param("paramType") String paramType);
 

@Modifying
@Query("update Advertise a set a.remainAmount=a.remainAmount-:amount,a.dealAmount=a.dealAmount+:amount where a.remainAmount>=:amount and a.status=:sta and a.id=:id")
int updateAdvertiseAmount(@Param("sta") AdvertiseControlStatus status, @Param("id") Long id, @Param("amount") BigDecimal amount);
 

@Query("MATCH (p:PersonWithRelationshipWithProperties)-[l:LIKES]->(h:Hobby) return p, collect(l), collect(h)")
Mono<PersonWithRelationshipWithProperties> loadFromCustomQuery(@Param("id") Long id);
 

@Query("MATCH (n:PersonWithAllConstructor) where n.name = $name return n")
Mono<PersonProjection> findByNameWithCustomQueryAndNodeReturn(@Param("name") String name);
 

@Query(value = "select * from member_wallet where  coin_id=:coinId and member_id=:memberId ",nativeQuery =true)
MemberWallet getMemberWalletByCoinAndMemberId(@Param("coinId") String coinId, @Param("memberId") long memberId);
 

@Query(value = "SELECT * FROM Employee e WHERE e.fistName = :firstName ORDER BY e.salary ASC",
        nativeQuery = true)
List<Employee> findByFirstNameNativeSQL(@Param("firstName") String firstName);
 

@Query("MATCH (c:CustomTypes) WHERE c.customType = $differentType return c")
ThingWithCustomTypes findByDifferentTypeCustomQuery(@Param("differentType") ThingWithCustomTypes.DifferentType differentType);
 

@Query("MATCH (c:CustomTypes) WHERE c.customType = $customType return c")
ThingWithCustomTypes findByCustomTypeCustomQuery(@Param("customType") ThingWithCustomTypes.CustomType customType);
 

@Select("select id from sys_depart where org_code=#{orgCode}")
public String queryDepartIdByOrgCode(@Param("orgCode") String orgCode);
 

@Query("SELECT u FROM User u WHERE " +
        "lower(u.name) LIKE lower(CONCAT('%', :keyword, '%')) OR " +
        "lower(u.email) LIKE lower(CONCAT('%', :keyword, '%'))")
List<User> searchUsers(@Param("keyword") String keyword);
 

@Transactional
@Modifying
@Query(value="UPDATE member_wallet SET balance=balance+:allBalance,frozen_balance=frozen_balance-:forzenBalance where coin_id=:coinId AND member_id=:memberId",nativeQuery = true)
int updateMemberWalletByMemberIdAndCoinId(@Param("allBalance")BigDecimal allBalance,@Param("forzenBalance")BigDecimal forzenBalance,@Param("coinId")String coinId,@Param("memberId")long memberId);
 

@Query("select sum(t.amount)  as amount from MemberTransaction t where t.flag = 0  and t.memberId = :memberId and t.symbol = :symbol and t.type = :type and t.createTime >= :startTime and t.createTime <= :endTime")
Map<String,Object> findMatchTransactionSum(@Param("memberId") Long memberId,@Param("symbol") String symbol,@Param("type") TransactionType type,@Param("startTime") Date startTime,@Param("endTime") Date endTime);
 

@Query("MATCH (n:PersonWithAllConstructor{name:$name}) return n")
Optional<PersonWithAllConstructor> getOptionalPersonViaQuery(@Param("name") String name);
 

@RequestMapping(method = RequestMethod.GET, value = "/products", produces = "application/json")
public List<ProductMetadata> getProducts(@Param("limit") int limit, @Param("offset") int offset) {
  return productService.findAllProductsPageable(limit, offset);
}
 

@Transactional
@Modifying(clearAutomatically = true)
@Query("UPDATE TransferEntity T SET T.status = :newStatus WHERE userid = :userid AND merchantid = :merchantid AND amount = :amount AND payway = :payway AND status = :status AND version = :version AND unix_timestamp(createtime) = unix_timestamp(:createtime)")
int updateTransferStatusById(@Param("userid") long userid, @Param("merchantid") long merchantid, @Param("amount") double amount, @Param("payway") int payway, @Param("status") int status, @Param("version") int version, @Param("createtime") Date createtime, @Param("newStatus") int newStatus);
 

@Query("SELECT n FROM Note n WHERE n.title = :title AND n.featured = :featured")
List<Note> findByTitleAndFeaturedNamedBind(@Param("featured") boolean featured,
                                           @Param("title") String title);
 

@Query(value = "SELECT SUM(mem_bouns) from member_bonus  where member_id=:memberId" ,nativeQuery = true)
BigDecimal getBonusAmountByMemberId(@Param("memberId")long memberId);