spring boot DAO @query查询示例01

package com.guohuai.mmp.investor.bankcard;

import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

public interface InvestorBankCardDao extends JpaRepository<InvestorBankCardQueryEntity,String>, JpaSpecificationExecutor<InvestorBankCardQueryEntity> {

@Query(value ="select * from t_money_investor_instgroup_bankcard where investoroid = :investorOid \n-- #pageable\n",
countQuery = "SELECT count(*) FROM t_money_investor_instgroup_bankcard WHERE investoroid = ?1 ",
nativeQuery = true)
Page<InvestorBankCardQueryEntity> findInvestorBankCardQueryEntityAllStatus(@Param("investorOid")String investorOid,Pageable pageable);

@Modifying
@Query(value ="update t_money_investor_instgroup_bankcard set auditStatus = 'auditPass', updateTime = now(),auditAnno = :auditAnno where bankCardNumber = :bankCardNumber ",nativeQuery = true)
int changeInvestorAuditStatusToAuditPass(@Param("bankCardNumber")String bankCardNumber,@Param("auditAnno") String auditAnno);

@Modifying
@Query(value ="update t_money_investor_instgroup_bankcard set auditStatus ='auditRefuse' , updateTime = now() ,auditAnno = :auditAnno where bankCardNumber = :bankCardNumber ",nativeQuery = true)
int changeInvestorAuditStatusToAuditRefuse(@Param("bankCardNumber")String bankCardNumber,@Param("auditAnno") String auditAnno);

@Query(value ="select * from t_money_investor_instgroup_bankcard where oid = :oid ",nativeQuery = true)
InvestorBankCardQueryEntity getInvestorBankCardInfo(@Param("oid")String oid);

@Query(value ="select auditStatus from t_money_investor_instgroup_bankcard where investorOid = :investorOid and auditStatus = 'toAudit' ",nativeQuery = true)
String getInvestorBankCardInfoByInvestorOidToAudit(@Param("investorOid")String investorOid);

@Query(value ="select * from t_money_investor_instgroup_bankcard where investorOid = :investorOid ",nativeQuery = true)
InvestorBankCardQueryEntity getInvestorBankCardInfoByInvestorOid(@Param("investorOid")String investorOid);


@Query(value ="select * from t_money_investor_instgroup_bankcard where investorOid = :investorOid order by createTime asc ",nativeQuery = true)
List<InvestorBankCardQueryEntity> getListInvestorBankCardInfo(@Param("investorOid")String investorOid);

@Query(value ="select count(1) from t_money_investor_bankorder where investorOid= :investorOid and (frozenStatus='frozened' or frozenStatus='toIceOut');",nativeQuery = true)
int checkInvestorBinkOrUnlockStatus(@Param("investorOid")String investorOid);

@Modifying
@Query(value ="update t_money_investor_instgroup_bankcard set auditStatus ='auditRefuse' , updateTime = now() ,auditAnno = :auditAnno where bankCardNumber = :bankCardNumber ",nativeQuery = true)
int changeAllInvestorAuditStatusToAuditRefuse(@Param("bankCardNumber")String bankCardNumber,@Param("auditAnno") String auditAnno);

@Query(value = "select count(1) from t_money_investor_instgroup_bankcard "
+ " where investorOid = ?1 and auditStatus = ?2 ", nativeQuery = true)
public int findByInvestorOidAndAuditStatus(String investorOid,String auditStatus);

@Query(value ="select * from t_money_investor_instgroup_bankcard where investorOid = ?1 and auditStatus = ?2",nativeQuery = true)
public InvestorBankCardQueryEntity findbyInvestorOidAndStatus(String investorOid,String auditStatus);


@Query(value = "select branchName from t_money_investor_instgroup_bankcard where investorOid = :investorOid and auditStatus = 'auditPass'", nativeQuery = true)
public String findBranchNameData(@Param("investorOid")String investorOid);

@Query(value = "select companyName from t_money_investor_instgroup_bankcard where investorOid = :investorOid and auditStatus = 'auditPass' ", nativeQuery = true)
public String findCompanyNameData(@Param("investorOid")String investorOid);

@Query(value = "select * from t_money_investor_instgroup_bankcard where investorOid = :investorOid and auditStatus = 'auditPass' ", nativeQuery = true)
public InvestorBankCardQueryEntity findAuditPassCardData(@Param("investorOid")String investorOid);


@Query(value = "select * from t_money_investor_instgroup_bankcard where investorOid = :investorOid and auditStatus = 'toAudit' ", nativeQuery = true)
public InvestorBankCardQueryEntity findToAuditCardData(@Param("investorOid")String investorOid);


@Query(value ="select * from t_money_investor_instgroup_bankcard where investorOid = :investorOid AND passOrNot = 'pass' LIMIT 1 ",nativeQuery = true)
List<InvestorBankCardQueryEntity> getListPassInvestorBankCardInfo(@Param("investorOid")String investorOid);

}