账号交易明细查询(分页)

/**
 * 账号交易明细查询(分页)
 * @author 
 *
 */
public class BusiSubAcctTransLogListServiceImpl implements BusiSubAcctTransLogListService {

@Autowired
private AcctTranInfoMapper mapper;
@Autowired
private SubAccountService subAccountService;


@Override
public BusiSubAcctTranLogListRspBO list(BusiSubAcctTransLogReqBO busiSubAcctTransLogReqBO) {

BusiSubAcctTranLogListRspBO resp=new BusiSubAcctTranLog ListRspBO();


String subAcctNo=busiSubAcctTransLogReqBO.getSubAcctNo();
BigDecimal balance=BigDecimal.ZERO;
if( subAcctNo!=null ) {
SubAcctInfoExt actInfoExt=subAccountService.getWithMainAccount(subAcctNo);  子账号联动主账号查询，查询的SQL语句见下面绿色背景
if( actInfoExt==null )
throw new BusinessException("RSP_CODE_PARA_NOT_NULL","子账号不存在");

resp.setBalance(actInfoExt.getBalance()); //取得余额
resp.setMainacctNo(actInfoExt.getMainAcctNo());
resp.setSubAcctName(actInfoExt.getSubAcctName());
resp.setSubAcctNo(actInfoExt.getSubAcctNo());
}


Pagination<AcctTranInfoExt> pageInfo=queryTranLog(busiSubAcctTransLogReqBO); 见黄色部分


resp.setTotal(pageInfo.getPageCount());  //总页数
resp.setRecordsTotal(pageInfo.getTotalCount());  //总记录数
resp.setRows(new ArrayList<BusiSubAcctTransLogRspBO>()); //数据行


//数据填充到返回对象
List<AcctTranInfoExt> ls=pageInfo.getRows();
for(int i=0;i<ls.size();i++) {
AcctTranInfoExt inf=ls.get(i);

BusiSubAcctTransLogRspBO respBo=new BusiSubAcctTransLogRspBO();
resp.getRows().add(respBo);

BeanUtils.copyProperties(inf, respBo);
if( respBo.getTranAmt()!=null ) {
if( "DR".equals(inf.getDrcrFlag()) )  //借方变成负数
respBo.setTranAmt(respBo.getTranAmt().negate());
}

}
return resp;
}

private Pagination<AcctTranInfoExt> queryTranLog(BusiSubAcctTransLogReqBO bo) {
AcctTranInfoExt extInfo=new AcctTranInfoExt();
BeanUtils.copyProperties(bo, extInfo);


Pagination<AcctTranInfoExt> pg=new Pagination<AcctTranInfoExt>();   分页信息详情见蓝色部分
pg.setPage(bo.getPageNo()); 
pg.setPageSize(bo.getPageSize());  //每页笔数

int tot=mapper.selectForCount(extInfo);  //计算总记录数   见红色部分
pg.setTotalCount(tot);  //设置(计算页数)

int rowIndex= pg.getRowIndex(); //计算起始记录的位置


extInfo.setPageSize(pg.getPageSize()); //每页多少条
extInfo.setRowIndex(rowIndex); //当前的行序号(从0起)

List<AcctTranInfoExt> ls=mapper.queryPageSelective(extInfo);  //分页查询     见红色部分
pg.setRows(ls);
return pg;
}

}

<!-- 子账号联动主账号查询 -->
  <select id="selectWihtMainAcct" parameterType="Map" resultMap="ExtResultMap">
    select 
    *
    from
    (
select 
s.*,
m.org_id as SUPERIOR_ORG_ID,m.MAIN_ACCT_NAME,m.source
from d_sub_acct_info s
inner join d_main_acct_info m
on s.main_acct_no=m.MAIN_ACCT_NO        
where 1=1
        <if test="subAcctNo != null">
          AND SUB_ACCT_NO=#{subAcctNo,jdbcType=VARCHAR}
        </if>
        <if test="serviceType != null">
          AND  SERVICE_TYPE=#{serviceType,jdbcType=INTEGER}
        </if>    
        <if test="orgId != null">
           AND s.ORG_ID=#{orgId,jdbcType=BIGINT}
        </if>       
        <if test="serviceType != null">
           AND SERVICE_TYPE=#{serviceType,jdbcType=INTEGER}
        </if>              
        <if test="projectId != null">
           AND PROJECT_ID=#{projectId,jdbcType=BIGINT}
        </if>  
     ) as n
     where 1=1
     <if test="superiorOrgId != null">
        AND SUPERIOR_ORG_ID=#{superiorOrgId,jdbcType=BIGINT}
     </if>
     <if test="source != null">
        AND SOURCE=#{source,jdbcType=VARCHAR}
     </if>                      
  </select>

/**
 * 分页信息对象
 * @author  2017年9月6日
 *
 * @param <T> 指定List元素里的类型
 */
public class Pagination<T> {


private int page;  //页码
private int pageCount; //页数
private int totalCount; //总记录数
private int pageSize;   //每页多少条
private String orderBy;
private List<T> rows;

public int getPage() {
if( page<1 ) page=1;
return page;
}
public void setPage(int page) {
if( page<1) page=1;
this.page = page;
}
public int getPageCount() {
return pageCount;
}
public void setPageCount(int pageCount) {
this.pageCount = pageCount;
}
public int getTotalCount() {
return totalCount;
}
public void setTotalCount(int totalCount) {
int pgCnt= totalCount / getPageSize();
if( pgCnt % getPageSize()>0 ) pgCnt++;
pageCount=pgCnt; //同时更新总页数
if( page>pageCount ) setPage(pageCount); //页面超出总页数
this.totalCount = totalCount;
}
public int getPageSize() {
if( pageSize<=0 ) pageSize=10;
return pageSize;
}
public void setPageSize(int countPerPage) {
if( countPerPage<=0 ) countPerPage=1;
this.pageSize = countPerPage;
}
public String getOrderBy() {
return orderBy;
}
public void setOrderBy(String orderBy) {
this.orderBy = orderBy;
}
public List<T> getRows() {
return rows;
}
public void setRows(List<T> rows) {
this.rows = rows;
}

public int getRowIndex() {
int idx=(getPage()-1) * getPageSize();
if( idx<0) idx=0;
return idx;
}
@Override
public String toString() {
return "Pagination [page=" + page + ", pageCount=" + pageCount + ", totalCount=" + totalCount + ", pageSize="
+ pageSize + ", rowIndex=" + getRowIndex() + ", orderBy=" + orderBy + "]";
}




}

<!-- 根据条件统计笔数 By 20170906 -->
  <select id="selectForCount" parameterType="Map" resultType="Integer"> 
       select count(seq) as cnt
       from d_acct_tran_info
 where 1=1
      <if test="subAcctNo != null">
        AND SUB_ACCT_NO=#{subAcctNo,jdbcType=VARCHAR}
      </if>
      <if test="txnno != null">
        AND txnno=#{TXNNO,jdbcType=VARCHAR}
      </if>      
      <if test="tranAmt!= null"><![CDATA[
        AND TRAN_AMT >=#{subAcctNo,jdbcType=VARCHAR}
      ]]></if>
      <if test="maxTranAmt!= null"><![CDATA[
        AND TRAN_AMT <=#{subAcctNo,jdbcType=VARCHAR}
      ]]></if>
      <if test="tranDate!= null"><![CDATA[
        and TRAN_DATE >=to_date(#{tranDate}, 'yyyy-mm-dd')
      ]]></if>    
      <if test="endTranDate!= null"><![CDATA[
        and TRAN_DATE <=to_date(#{tranDate}, 'yyyy-mm-dd')
      ]]></if>


  </select>

<!-- 根据条件分页查询记录 By Xie.Kaizu 20170906 -->
  <select id="queryPageSelective" parameterType="com.cgd.pay.atom.bo.AcctTranInfoExt" resultMap="ExtResultMap">
    select 
    tr.*
,sub.org_id as acct_org_id,sub.sub_acct_name,sub.service_type,sub.main_acct_no,sub.project_id
,main.org_id as superior_org_id,main.source,main.main_acct_name
    from
    (
     (
 select 
 <include refid="Base_Column_List" />
 from d_acct_tran_info
 where 1=1
      <if test="subAcctNo != null">
        AND SUB_ACCT_NO=#{subAcctNo,jdbcType=VARCHAR}
      </if>
      <if test="txnno != null">
        AND txnno=#{TXNNO,jdbcType=VARCHAR}
      </if>      
      <if test="tranAmt!= null"><![CDATA[
        AND TRAN_AMT >=#{subAcctNo,jdbcType=VARCHAR}
      ]]></if>
      <if test="maxTranAmt!= null"><![CDATA[
        AND TRAN_AMT <=#{subAcctNo,jdbcType=VARCHAR}
      ]]></if>
      <if test="tranDate!= null"><![CDATA[
        and TRAN_DATE >=to_date(#{tranDate}, 'yyyy-mm-dd')
      ]]></if>    
      <if test="endTranDate!= null"><![CDATA[
        and TRAN_DATE <=to_date(#{tranDate}, 'yyyy-mm-dd')
      ]]></if>        
      limit #{rowIndex},#{pageSize} 
     ) tr
     inner join d_sub_acct_info sub 
     on sub.sub_acct_no= tr.sub_acct_no
   )
   inner join d_main_acct_info main 
   on sub.main_acct_no=main.main_acct_no
      
  </select>