这里是ORALCE。
mysql也可以参考。思路是一样的。
sqlmap
<select id="selectAccType" resultClass="java.util.HashMap"
parameterClass="com.xx.acctype.dto.AccTypeQueryDTO"
remapResults="true">
<include refid="Commons.prefixSql" />
select T2.ACC_TYPE_ID as "accTypeId", T2.ACC_TYPE_NAME as
"accTypeName",
DECODE(T2.ISSUER_ID,0,T3.ISSUER_GROUP_NAME,T1.ISSUER_NAME) as
"issuerName" from TB_ENT_ISSUER T1, TB_ENT_ACCTYPE T2,
TB_ENT_ISSUER_GROUP T3 where T1.ISSUER_ID(+)=T2.ISSUER_ID and
T3.ISSUER_GROUP_ID(+)=T2.ISSUER_GROUP_ID and T2.DATA_STATE = 1
<dynamic>
<isNotEmpty prepend="and" property="accTypeId">
ACC_TYPE_ID = #accTypeId:DECIMAL#
</isNotEmpty>
</dynamic>
<include refid="Commons.suffixSql" />
</select>
这里的AccTypeQueryDTO继承PageQueryDTO,包含pageNo等属性。
其中的Commons.xml是这样的,里面有一个prefixSql和suffixSql。用来做分页和查出count
<sql id="prefixSql">
<dynamic>
<isEqual property="doCount" compareValue="true">
select count(*) as $COUNT_ALL_NAME$ from (
</isEqual>
<isEqual property="doCount" compareValue="false">
<isEqual property="queryAll" compareValue="false">
SELECT * FROM (select row_.*, rownum start_rownum_ from (
</isEqual>
<isNotEmpty property="sort">
SELECT * FROM (
</isNotEmpty>
</isEqual>
</dynamic>
</sql>
<sql id="suffixSql">
<dynamic>
<isEqual property="doCount" compareValue="true">
)
</isEqual>
<isEqual property="doCount" compareValue="false">
<isEqual property="sort" compareValue="asc">
) order_by_ ORDER BY order_by_."$sortFieldName$" asc
</isEqual>
<isEqual property="sort" compareValue="desc">
) order_by_ ORDER BY order_by_."$sortFieldName$" desc
</isEqual>
<isEqual property="queryAll" compareValue="false">
<![CDATA[
) row_ WHERE rownum <= #lastCursorPosition# ) page_ WHERE start_rownum_ >= #firstCursorPosition#
]]>
</isEqual>
</isEqual>
</dynamic>
</sql>
这里的关键就是doCount。来看DAO
public PageDataDTO query(String statement, PageQueryDTO parameter, boolean count) {
PageQueryDTO pageQuery = parameter;
if (pageQuery == null) {
pageQuery = new PageQueryDTO();
pageQuery.setQueryAll(true);
}
PageDataDTO pageDataDTO = new PageDataDTO();
int totalRecord = 0;
if (!pageQuery.isQueryAll() && count) {
parameter.setDoCount(true);
Map result = (Map) getSqlMapClientTemplate().queryForObject(statement, pageQuery);
Number value = (Number) result.get(PageQueryDTO.COUNT_ALL_NAME);
totalRecord = value.intValue();
}
pageQuery.setDoCount(false);
DefaultRowHandler defaultRowHandler = new DefaultRowHandler();
getSqlMapClientTemplate().queryWithRowHandler(statement, pageQuery, defaultRowHandler);
pageDataDTO.setData(defaultRowHandler.getList());
if (pageQuery.isQueryAll())
totalRecord = defaultRowHandler.getList().size();
pageDataDTO.setTotalRecord(totalRecord);
return pageDataDTO;
}
Service
PageDataDTO dto = pageQueryDAO.query("ACCTYPE.selectAccTypetest", accTypeQueryDTO);
在DAO里面,先设置doCount为true,查出count,然后设置为false,查出分页数据。
对于mysql。试了一下
SELECT
COUNT(*)
FROM
(SELECT
*
FROM
nwd_member t1) t2
发现慢了几十倍。继续研究研究。
--------------------------------------------------------------------------
mysql也许可以这么做:
在select 与 from 之间加isEqual,queryCount=true时,选择count(*),否则为具体字段。resultClass包含count(*)属性。