关于分页及增删改查

一、分页

（1）、第一种分页查询函数，使用根据传入的查询语句来分页查询(部分查询语句) 会调用XML文件中的SELECTBYPAGE 与PAGECOUNT

例如：

public AjaxPage loadPageDate(Integer page, Integer rows, MortgageInfo obj, SortBean sortBean) throws Exception {
StringBuffer sql = new StringBuffer();
sql.append(SqlBuilderUtil.popuLikeSql("m", "mortgcode_", obj.getMortgcode_()));// 合同编号
sql.append(SqlBuilderUtil.popuLikeSql("bas", "assetsname", obj.getAssetsname()));// 资产名称
sql.append(SqlBuilderUtil.popuEqSql("m", "assetsinfoid_", obj.getAssetsinfoid_()));// 资产id
sql.append(SqlBuilderUtil.popuEqSql("m", "mortgstate_", obj.getMortgstate_()));// 抵押状态
sql.append(SqlBuilderUtil.popuBeginDateOra("m", "startTime_", obj.getBeginTime()));// 抵押时间
sql.append(SqlBuilderUtil.popuEndDateOra("m", "startTime_", obj.getEndTime()));// 抵押时间
sql.append(SqlBuilderUtil.popuOrderSql(sortBean.getAliases(), sortBean.getProperty(), sortBean.getDirection(),
"createTime_", "desc"));
return super.ajaxPagedQuery(sql.toString(), page, rows);
}

xml中的sql语句

<select id="com.app.mortgage.domain.MortgageInfo.selectPage"
parameterClass="java.lang.String" resultClass="MortgageInfo">
<![CDATA[
 select m.*, b.name_ bankname, bas.assetsName, bas.unitname_
 from tbl_assets_mortg m
 left join tbl_bank_info b on m.bankid_ = b.id_
 left join (select tat.mortgcode_,
                   wmsys.wm_concat(tab.name_ || ' ') assetsName, wmsys.wm_concat(s.unitname_ || ' ') unitname_
              from tbl_assets_mortg tat
              left join tbl_assets_mortgchild ta on ta.mortgcode_ =
                                                        tat.mortgcode_
                  left join tbl_assets_base_info tab on tab.id_ =
                                                        ta.assetsinfoid_
                    left join s_unit s on s.unituuid_ = tab.unituuid_
                 group by tat.mortgcode_) bas on m.mortgcode_ =
                                                                bas.mortgcode_
                 
]]>
<isNotEmpty>where 1=1 $value$</isNotEmpty>
</select>

<select id="com.app.mortgage.domain.MortgageInfo.pageCount"
parameterClass="java.lang.String" resultClass="java.lang.Integer">
<![CDATA[
select count(*) as xx
 from tbl_assets_mortg m
 left join tbl_bank_info b on m.bankid_ = b.id_
 left join (select tat.mortgcode_,
                   wmsys.wm_concat(tab.name_ || ' ') assetsName, wmsys.wm_concat(s.unitname_ || ' ') unitname_
              from tbl_assets_mortg tat
              left join tbl_assets_mortgchild ta on ta.mortgcode_ =
                                                         tat.mortgcode_
                   left join tbl_assets_base_info tab on tab.id_ =
                                                         ta.assetsinfoid_
                     left join s_unit s on s.unituuid_ = tab.unituuid_
                  group by tat.mortgcode_) bas on m.mortgcode_ =
                                                                 bas.mortgcode_
]]>
<isNotEmpty>where 1=1 $value$</isNotEmpty>
</select>

（2）、往往一个页面对同一个实体对象需要两个或多个分页查询，这就需要指定自定义的sql语句了

public AjaxPage loadPageDataHis(Integer page, Integer rows, MortgageInfo obj, SortBean sortBean) throws Exception {
StringBuffer sql = new StringBuffer();
sql.append(SqlBuilderUtil.popuEqSql("bas", "assetsid", obj.getAssetsinfoid_()));// 资产id
sql.append(SqlBuilderUtil.popuLikeSql("bas", "mortgcode_", obj.getMortgcode_()));// 合同编码
sql.append(SqlBuilderUtil.popuEqSql("m", "mortgstate_", obj.getMortgstate_()));// 抵押状态
sql.append(SqlBuilderUtil.popuOrderSql(sortBean.getAliases(), sortBean.getProperty(), sortBean.getDirection(),
"starttime_", "asc"));
return super.ajaxPagedQuery(sql.toString(), page, rows, ".selectPageHistorySepara", ".pageCountHistorySepara");
}

xml中的sql语句

<select id="com.app.mortgage.domain.MortgageInfo.selectPageHistory"
parameterClass="java.lang.String" resultClass="MortgageInfo">
<![CDATA[
 select m.*, b.name_ bankname, bas.assetsName, bas.unitname_
 from tbl_assets_mortg m
 left join tbl_bank_info b on m.bankid_ = b.id_
 left join TBL_ASSETS_MORTGCHILD amc on M.MORTGCODE_ = AMC.MORTGCODE_
 left join (select tat.mortgcode_,
                   wmsys.wm_concat(tab.name_ || ' ') assetsName, wmsys.wm_concat(s.unitname_ || ' ') unitname_
              from tbl_assets_mortg tat
              left join tbl_assets_mortgchild ta on ta.mortgcode_ =
                                                        tat.mortgcode_
                  left join tbl_assets_base_info tab on tab.id_ =
                                                        ta.assetsinfoid_
                    left join s_unit s on s.unituuid_ = tab.unituuid_
                 group by tat.mortgcode_) bas on m.mortgcode_ =
                                                                bas.mortgcode_
                 
]]>
<isNotEmpty>where 1=1 $value$</isNotEmpty>
</select>
<select id="com.app.mortgage.domain.MortgageInfo.pageCountHistory"
parameterClass="java.lang.String" resultClass="java.lang.Integer">
<![CDATA[
select count(*) as xx
 from tbl_assets_mortg m
 left join tbl_bank_info b on m.bankid_ = b.id_
 left join TBL_ASSETS_MORTGCHILD amc on M.MORTGCODE_ = AMC.MORTGCODE_
 left join (select tat.mortgcode_,
                   wmsys.wm_concat(tab.name_ || ' ') assetsName, wmsys.wm_concat(s.unitname_ || ' ') unitname_
              from tbl_assets_mortg tat
              left join tbl_assets_mortgchild ta on ta.mortgcode_ =
                                                         tat.mortgcode_
                   left join tbl_assets_base_info tab on tab.id_ =
                                                         ta.assetsinfoid_
                     left join s_unit s on s.unituuid_ = tab.unituuid_
                  group by tat.mortgcode_) bas on m.mortgcode_ =
                                                                 bas.mortgcode_
]]>
<isNotEmpty>where 1=1 $value$</isNotEmpty>
</select>

二、增删改查

使用的方法都在父类IBatisEntityDao中，可自行查询，后期补充