DB2Dialect和DB2400Dialect中getLimitString()方法的区别

1、DB2Dialect方法

public String getLimitString(String sql, boolean hasOffset) {
		int startOfSelect = sql.toLowerCase().indexOf("select");

		StringBuffer pagingSelect = new StringBuffer(sql.length() + 100)
				.append(sql.substring(0, startOfSelect)) // add the comment
				.append("select * from ( select ") // nest the main query in an
				.append(getRowNumber(sql)); // add the rownnumber bit into the
		int orderByIndex = sql.toLowerCase().indexOf("order by");
		if (hasDistinct(sql)) {
			pagingSelect.append(" row_.* from ( ");
			if (orderByIndex > 0) // whithout ordey by
				pagingSelect.append(sql.substring(startOfSelect, orderByIndex));
			else
				pagingSelect.append(sql.substring(startOfSelect));
			pagingSelect.append(" ) as row_"); // close off the inner nested
												// select
		} else {
			if (orderByIndex > 0) // whithout ordey by
				pagingSelect.append(sql.substring(startOfSelect + 6,
						orderByIndex));
			else
				pagingSelect.append(sql.substring(startOfSelect + 6));
		}
		pagingSelect.append(" ) as temp_ where rownumber_ ");
		// add the restriction to the outer select
		if (hasOffset) {
			pagingSelect.append("between ?+1 and ?");
		} else {
			pagingSelect.append("<= ?");
		}
		return pagingSelect.toString();
	}

输出的sql语句：

select * from( select rownumber() over(order by nocardtran0_.id desc) as rownumber_,
            nocardtran0_.id as id13_,
            nocardtran0_.account_number1 as account2_13_,
            nocardtran0_.account_number2 as account3_13_,
            nocardtran0_.acq_id as acq4_13_,
            nocardtran0_.acq_inst_resvd as acq5_13_,
            nocardtran0_.auth_resp_code as auth6_13_,
            nocardtran0_.cell_phone as cell7_13_,
            nocardtran0_.cert_info as cert8_13_,
            nocardtran0_.content_provider_id as content9_13_,
            nocardtran0_.create_time as create10_13_,
            nocardtran0_.imei as imei13_,
            nocardtran0_.mcc as mcc13_,
            nocardtran0_.merchant_country as merchant13_13_,
            nocardtran0_.merchant_id as merchant14_13_,
            nocardtran0_.merchant_name as merchant15_13_,
            nocardtran0_.merchant_type_code as merchant16_13_,
            nocardtran0_.mobile_type as mobile17_13_,
            nocardtran0_.order_generate_time as order18_13_,
            nocardtran0_.order_id as order19_13_,
            nocardtran0_.order_info as order20_13_,
            nocardtran0_.order_type as order21_13_,
            nocardtran0_.orig_submit_time as orig22_13_,
            nocardtran0_.orig_trans_serial as orig23_13_,
            nocardtran0_.os_info as os24_13_,
            nocardtran0_.resp_code as resp25_13_,
            nocardtran0_.retrvl_ref_no as retrvl26_13_,
            nocardtran0_.service_info as service27_13_,
            nocardtran0_.session_id as session28_13_,
            nocardtran0_.settle_date as settle29_13_,
            nocardtran0_.submit_time as submit30_13_,
            nocardtran0_.system_provider_id as system31_13_,
            nocardtran0_.terminal_id as terminal32_13_,
            nocardtran0_.trans_amount as trans33_13_,
            nocardtran0_.trans_currency as trans34_13_,
            nocardtran0_.trans_serial as trans35_13_,
            nocardtran0_.transmit_time as transmit36_13_,
            nocardtran0_.user_dev_id as user37_13_ 
        from
            MP_ONLDB.tbl_nocard_trans_info_process nocardtran0_ 
        order by
            nocardtran0_.id desc ) as temp_ 
    where
        rownumber_ between ?+1 and ?

2、DB2400Dialect方法

public String getLimitString(String sql, int offset, int limit) {
		if ( offset > 0 ) {
			throw new UnsupportedOperationException( "query result offset is not supported" );
		}
		return new StringBuffer( sql.length() + 40 )
				.append( sql )
				.append( " fetch first " )
				.append( limit )
				.append( " rows only " )
				.toString();
	}

输出的sql语句：

fetch first ? rows

网上有人说使用DB2Dialect方式性能比DB2400Dialect好，参考
[url]http://www.blogjava.net/kelefa/archive/2007/08/27/90246.html[/url]，可我测试时却发现[color=red]使用DB2Dialect情况下CPU使用率一直会冲高[/color]，先记录个草稿，有时间再研究一下。