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 theint orderByIndex = sql.toLowerCase().indexOf("order by");if (hasDistinct(sql)) {pagingSelect.append(" row_.* from ( ");if (orderByIndex > 0) // whithout ordey bypagingSelect.append(sql.substring(startOfSelect, orderByIndex));elsepagingSelect.append(sql.substring(startOfSelect));pagingSelect.append(" ) as row_"); // close off the inner nested// select} else {if (orderByIndex > 0) // whithout ordey bypagingSelect.append(sql.substring(startOfSelect + 6,orderByIndex));elsepagingSelect.append(sql.substring(startOfSelect + 6));}pagingSelect.append(" ) as temp_ where rownumber_ ");// add the restriction to the outer selectif (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好，参考
http://www.blogjava.net/kelefa/archive/2007/08/27/90246.html，可我测试时却发现使用DB2Dialect情况下CPU使用率一直会冲高，先记录个草稿，有时间再研究一下。
页:
[1]