public Page<Map<String, Object>> queryForPage(String sqlId, ISearch search) {
CapSqlSearchQueryProvider provider = new CapSqlSearchQueryProvider(search);
String _sql = sqlp.getValue(sqlId, sqlId);
StringBuffer sourceSql =
new StringBuffer(_sql)
.append(_sql.toUpperCase().lastIndexOf("WHERE") > 0 ? " AND " : " WHERE ")
.append(provider.generateWhereCause());
Map<String, Object> params = new HashMap<String, Object>();
params.put(CapJdbcContants.SQLPaging_SourceSQL, sourceSql.toString());
// 準備查詢筆數sql
StringBuffer sql =
new StringBuffer()
.append(
CapDbUtil.spelParser(
(String) sqltemp.getValue(CapJdbcContants.SQLPaging_TotalPage),
params,
sqlp.getParserContext()));
sql.append(' ').append(sqltemp.getValue(CapJdbcContants.SQLQuery_Suffix, ""));
if (logger.isTraceEnabled()) {
logger.trace(
new StringBuffer("\n\t")
.append(CapDbUtil.convertToSQLCommand(sql.toString(), provider.getParams()))
.toString());
}
String sqlRow = sql.toString();
// 準備查詢list sql
sourceSql.append(provider.generateOrderCause());
params.put(CapJdbcContants.SQLPaging_SourceSQL, sourceSql.toString());
sql =
new StringBuffer()
.append(
CapDbUtil.spelParser(
(String) sqltemp.getValue(CapJdbcContants.SQLPaging_Query),
params,
sqlp.getParserContext()));
sql.append(' ').append(sqltemp.getValue(CapJdbcContants.SQLQuery_Suffix, ""));
if (logger.isTraceEnabled()) {
logger.trace(
new StringBuffer("\n\t")
.append(CapDbUtil.convertToSQLCommand(sql.toString(), provider.getParams()))
.toString());
}
long cur = System.currentTimeMillis();
try {
int totalRows = super.queryForInt(sqlRow, provider.getParams());
List<Map<String, Object>> list = super.queryForList(sql.toString(), provider.getParams());
return new Page<Map<String, Object>>(
list, totalRows, search.getMaxResults(), search.getFirstResult());
} catch (Exception e) {
throw new CapDBException(e, causeClass);
} finally {
logger.info("CapNamedJdbcTemplate spend {} ms", (System.currentTimeMillis() - cur));
}
} // ;
public Page<Map<String, Object>> queryForPage(
String sqlId, Map<String, Object> args, int startRow, int fetchSize) {
Map<String, Object> params = new HashMap<String, Object>();
params.put(CapJdbcContants.SQLPaging_SourceSQL, getSourceSql(sqlId, args, startRow, fetchSize));
StringBuffer sql =
new StringBuffer()
.append(
CapDbUtil.spelParser(
(String) sqltemp.getValue(CapJdbcContants.SQLPaging_TotalPage),
params,
sqlp.getParserContext()));
sql.append(' ').append(sqltemp.getValue(CapJdbcContants.SQLQuery_Suffix, ""));
if (logger.isTraceEnabled()) {
logger.trace(
new StringBuffer("\n\t")
.append(CapDbUtil.convertToSQLCommand(sql.toString(), args))
.toString());
}
// find list
List<Map<String, Object>> list = this.queryPaging(sqlId, args, startRow, fetchSize);
long cur = System.currentTimeMillis();
try {
return new Page<Map<String, Object>>(
list, super.queryForInt(sql.toString(), args), fetchSize, startRow);
} catch (Exception e) {
throw new CapDBException(e, causeClass);
} finally {
logger.info("CapNamedJdbcTemplate spend {} ms", (System.currentTimeMillis() - cur));
}
} // ;
private String getSourceSql(String sqlId, Map<String, Object> args, int startRow, int fetchSize) {
// Rewrite sql map. (ex: SQL Server => top n)
String countAllSqlId = sqlId + ".countAll()";
if (sqltemp.containsKey(sqlId)) {
Map<String, Object> preParams = new HashMap<String, Object>();
if (sqltemp.containsKey(countAllSqlId)) {
preParams.put("countAll", sqltemp.getValue(countAllSqlId, countAllSqlId));
}
return CapDbUtil.spelParser(
sqltemp.getValue(sqlId, sqlId), preParams, sqltemp.getParserContext());
} else {
return sqlp.getValue(sqlId, sqlId);
}
}