jpa使用方法的几种分类

见结算项目

 

 

用jpa的都是单实体，如果需要复杂bo查询需要再dao上嫁接一个接口，其实现类用jdmctemlpate,或mybatis（用的时候还是用主的dao接口，否则无需嫁接到其上（这是只能用另起的接口的））

jpa使用大致分二种：（都是基于单个实体的），此二者都有自带分页的参数方法（都是单体）

 

 

1，继承JpaRepository的dao可以用@query注解 也可根据名字判别查询(简单条件)

 

 

2，继承JpaSpecificationExecutor实现复杂条件（有为空情况下的查询），自动有传入分页信息的接口（不必再PagingAndSortingRepository）

 

另：如果需要查询bo复杂bo需要再dao上嫁接一个接口，其实现类用jdmctemlpate,或mybatis（用的时候还是用主的dao接口）

 

 

1根据名称判别  实体（这种没有继承任何的是为了查询bo的嫁接接口）

public interface ISettFundRepository {

 

public Page<TbSettFundVo> findAll(FundQueryVo vo);

 

}

 

2@Query   只有继承了JpaRepository才有@query（）才有根据名字查询特性

 

  2.1  实体

public interface SettFundRepository extends JpaRepository<TbSettFund, TbSettFundPK>,ISettFundRepository{

 

/**

* 根据类型查询单个账户 用于查询清收、利息、手续费子账户

* 手续费子账户 3

* 利息子账户 4

* 清收子账户 5

* @param custType

* @return

*/

public TbSettFund findFirstByCustType(int custType);

 

@Query("select t from TbSettFund t where t.id.custAcctId=?1")

public TbSettFund findByCustAcctId(String custAcctId);

}

 

bo

 

///对于有bo查询就不能用jpa了，方式1，用模版中的query,需用其他结合如jdbctemplate  ，看项目中封装的PageQuery.java，此时和jpa无关，嫁接到其上而已

 

public class SettFundRepositoryImpl implements ISettFundRepository {

 

 

@Autowired

private PageQuery pageQuery;

 

@Override

public Page<TbSettFundVo> findAll(FundQueryVo vo) {

 

StringBuilder sb = new StringBuilder("select b.cust_name,a.* from tb_sett_fund a ,tb_sett_account b where a.cust_acct_id=b.cust_acct_id");

 

if (!StringUtils.isEmpty(vo.getThirdCustId())){

sb.append(" and a.third_cust_id like '%"+vo.getThirdCustId()+"%'");

}

if (!StringUtils.isEmpty(vo.getCustName())){

sb.append(" and b.cust_name like '%"+vo.getCustName()+"%'");

}

sb.append(" order by a.tran_date desc");

 

return pageQuery.query(sb.toString(), vo, TbSettFundVo.class);

}

 

}

 

///druid分页

private String pageSql(String sql,Pageable pageable){

 

return PagerUtils.limit(sql,  JdbcConstants.ORACLE, pageable.getOffset(), pageable.getPageSize());

 

//StringBuilder sb = new StringBuilder("SELECT *  FROM (SELECT row_.*, ROWNUM rownum_   FROM (");

//sb.append(sql);

//sb.append(") row_ where rownum <= ");

//sb.append(pageable.getOffset()+pageable.getPageSize());

//sb.append(") WHERE rownum_ > ");

//sb.append(pageable.getOffset());

//

//return sb.toString();

}

 

public <T> Page<T> query(String sql,BaseQueryVo vo,Class<T> voType) {

logger.info(sql);

Pageable pageable = vo.getPageable();

long total = count(sql);

List<T> list = jdbcTemplate.query(pageSql(sql, pageable), getMapper(voType));//////pageSql通过自己写的这个工具类分页（其实也是druid的）

Page<T> page = new PageImpl<T>(list, pageable, total);

//返回页数超出总页数 则取最后一页的数据

if (pageable.getOffset()>page.getTotalElements()){

PageRequest newPageable = new  PageRequest(page.getTotalPages()-1,pageable.getPageSize());

list = jdbcTemplate.query(pageSql(sql, newPageable), getMapper(voType));

page = new PageImpl<T>(list, newPageable, total);

}

return page;

 

}

 

 

 

////返回bo的方式2，模版中query中的用rowmapper

 

@SuppressWarnings("unchecked")

@Override

public List<TbSettInmoney> selectInmoney(TbSettInmoney entity) {

// TODO Auto-generated method stub

// String str= "2017-04-07 00:00:00";

SimpleDateFormat SFDate = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

String strb=SFDate.format(entity.getBeginDate());//entity.getTranDate();

String stre=SFDate.format(entity.getEndDate());//entity.getTranDate();

List<TbSettInmoney> tb= jdbcTemplate.query("select * from TB_SETT_INMONEY t where t.acct_date>=to_date (? , 'YYYY-MM-DD HH24:MI:SS' ) and t.acct_date<=to_date (? , 'YYYY-MM-DD HH24:MI:SS' )", new Object[]{strb,stre},new RowMapper(){

 

@Override

public Object mapRow(ResultSet rs, int rowNum) throws SQLException {

// TODO Auto-generated method stub

TbSettInmoney tb= new TbSettInmoney();

tb.setInId(rs.getLong("IN_ID"));

tb.setCcyCode(rs.getString("CCY_CODE"));

tb.setCounterId(rs.getString("COUNTER_ID"));

tb.setSupAcctId(rs.getString("SUP_ACCT_ID"));

tb.setInAcctId(rs.getString("IN_ACCT_ID"));

tb.setInAcctIdName(rs.getString("IN_ACCT_ID_NAME"));

tb.setLogNo(rs.getString("LOG_NO"));

tb.setTranStatus(rs.getInt("TRAN_STATUS"));

// tb.setCustFlag(rs.getInt("CUST_FLAG"));

// tb.setTranDate(rs.getDate("TRAN_DATE"));

return tb;

}

 

});

System.out.println(tb.size());

return tb;

}

 

 

 

 

 

2.2

public interface SettPlatInfoRepository extends JpaRepository<TbSettPlatInfo, Long> {

 

@Query("select  t from TbSettPlatInfo t where t.infoId=(select max(s.infoId) from TbSettPlatInfo s)")

public List<TbSettPlatInfo> findTbSettPlatInfo();

 

}

 

 

 

 

 

3 复杂查询 JpaSpecificationExecutor  实体（只有继承了JpaSpecificationExecutor才有Specification方法）

 

 

public interface SettAccountRepository extends JpaRepository<TbSettAccount, TbSettAccountPK>,JpaSpecificationExecutor<TbSettAccount> {

 

@Query("select t from TbSettAccount t where t.id.custAcctId=?1")

public TbSettAccount findByCustAcctId(String custAcctId);

 

@Query("select t from TbSettAccount t where t.id.thirdCustId=?1")

public TbSettAccount findByThirdCustId(String thirdCustId);

 

@Query(value="select u.customer_key from tb_cus_firm u where u.customer_id=?1",nativeQuery=true)

public Long thirdIdSize(String thirdCustId);

 

/**

* 根据customer_key获得组织机构代码证

* @param customerKey

* @return

*/

@Query(value="select u.papers_num from tb_cus_firm_affix_chg u where u.customer_key=?1 and u.affix_kind='E'",nativeQuery=true)

public String getOrgCode(long customerKey);

}

 

@RequestMapping("accountList1")

@ResponseBody

public  Page<TbSettAccount> findAll(){

Page<TbSettAccount> pages = null;

 

Pageable page = new PageRequest(0,10);

pages = settAccountService.findAll(page);

 

List<TbSettAccount> list = pages.getContent();///////////spring的自带分页

 

 

return pages;

}

 

@Override

public Page<TbSettAccount> findAll(Pageable pageable) {

 

Specification<TbSettAccount> spec = new Specification<TbSettAccount>(){

 

@Override

public Predicate toPredicate(Root<TbSettAccount> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

Expression<String> custId = root.get("id").get("thirdCustId").as(String.class);

 

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

 

Predicate p = cb.equal(custId, "E00000062");

list.add(p);

Predicate p1 = cb.equal(root.get("idCode").as(String.class), "12343011-9");

// list.add(p1);

Predicate[] predicates = new Predicate[list.size()];

 

// return query.where(list.toArray(predicates)).getRestriction();

return null;

}};

 

Page<TbSettAccount> page = settAccountRepository.findAll(spec, pageable);

 

// Page<TbSettAccount> page = settAccountRepository.findAll(pageable);

List<TbSettAccount> list = page.getContent();

for (TbSettAccount account:list){

account.setFund(settFundService.findByCustAcctId(account.getId().getCustAcctId()));

}

return page;

}

 

 

 

 

 

 

 

参考：

 

http://www.cnblogs.com/jiangxiaoyaoblog/p/5635152.html

http://blog.csdn.net/lsk12162012/article/details/50442792