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));
}
} // ;
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));
}
} // ;
/**
* 查詢，查詢結果為List<Map<key, value>>
*
* @param <T> bean
* @param sqlId sqlId
* @param appendDynamicSql append sql
* @param args 傳入參數
* @param rm RowMapper
* @return List<T>
*/
public <T> List<T> query(
String sqlId, String appendDynamicSql, Map<String, ?> args, RowMapper<T> rm) {
StringBuffer sql = new StringBuffer((String) sqlp.getValue(sqlId, sqlId));
if (appendDynamicSql != null) {
sql.append(' ').append(appendDynamicSql);
}
if (!sql.toString().trim().toUpperCase().startsWith("CALL")) {
sql.append(' ').append(sqltemp.getValue(CapJdbcContants.SQLQuery_Suffix, ""));
}
if (logger.isTraceEnabled()) {
logger.trace(
new StringBuffer("SqlId=")
.append(sqlp.containsKey(sqlId) ? sqlId : "")
.append("\n\t")
.append(CapDbUtil.convertToSQLCommand(sql.toString(), args))
.toString());
}
long cur = System.currentTimeMillis();
try {
return super.query(
sql.toString(), (Map<String, ?>) args, new RowMapperResultSetExtractor<T>(rm));
} 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);
}
}
/**
* wrapper SqlRowSet queryForRowSet(String sql, Object[] args) from
* org.springframework.jdbc.core.JdbcTemplate
*
* @param sqlId sqlId
* @param args 參數
* @return SqlRowSet
*/
public SqlRowSet queryForRowSet(String sqlId, Map<String, ?> args) {
StringBuffer sql = new StringBuffer((String) sqlp.getValue(sqlId, sqlId));
sql.append(' ').append(sqltemp.getValue(CapJdbcContants.SQLQuery_Suffix, ""));
if (logger.isTraceEnabled()) {
logger.trace(
new StringBuffer("SqlId=")
.append(sqlp.containsKey(sqlId) ? sqlId : "")
.append("\n\t")
.append(CapDbUtil.convertToSQLCommand(sql.toString(), args))
.toString());
}
long cur = System.currentTimeMillis();
try {
return super.queryForRowSet(sql.toString(), args);
} catch (Exception e) {
throw new CapDBException(e, causeClass);
} finally {
logger.info("CapNamedJdbcTemplate spend {} ms", (System.currentTimeMillis() - cur));
}
} // ;
/**
* 新增、修改、刪除
*
* @param sqlId sqlId
* @param args Map<String, ?>
* @return int
* @throws GWException
*/
@SuppressWarnings({"unchecked", "rawtypes"})
public int update(String sqlId, Map<String, ?> args) {
String sql = sqlp.getValue(sqlId, sqlId);
if (logger.isTraceEnabled()) {
logger.trace(
new StringBuffer("SqlId=")
.append(sqlp.containsKey(sqlId) ? sqlId : "")
.append("\n\t")
.append(CapDbUtil.convertToSQLCommand(sql, args))
.toString());
}
long cur = System.currentTimeMillis();
try {
return super.update(sql, (Map) args);
} catch (Exception e) {
throw new CapDBException(e, causeClass);
} finally {
logger.info("CapNamedJdbcTemplate spend {} ms", (System.currentTimeMillis() - cur));
}
} // ;
public void query(String sqlId, Map<String, ?> args, RowCallbackHandler rch) {
StringBuffer sql = new StringBuffer((String) sqlp.getValue(sqlId, sqlId));
if (!sql.toString().trim().toUpperCase().startsWith("CALL")) {
sql.append(' ').append(sqltemp.getValue(CapJdbcContants.SQLQuery_Suffix, ""));
}
if (logger.isTraceEnabled()) {
logger.trace(
new StringBuffer("SqlId=")
.append(sqlp.containsKey(sqlId) ? sqlId : "")
.append("\n\t")
.append(CapDbUtil.convertToSQLCommand(sql.toString(), args))
.toString());
}
long cur = System.currentTimeMillis();
try {
super.query(sql.toString(), args, rch);
} catch (Exception e) {
throw new CapDBException(e, causeClass);
} finally {
logger.info("CapNamedJdbcTemplate spend {} ms", (System.currentTimeMillis() - cur));
}
} // ;
public int batchUpdate(
String sqlId, Map<String, Integer> sqlTypes, final List<Map<String, Object>> batchValues) {
long cur = System.currentTimeMillis();
try {
int[] batchCount = null;
String sql = sqlp.getValue(sqlId, sqlId);
ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(sql);
if (sqlTypes != null && !sqlTypes.isEmpty()) {
MapSqlParameterSource[] batch = new MapSqlParameterSource[batchValues.size()];
for (int i = 0; i < batchValues.size(); i++) {
Map<String, Object> valueMap = batchValues.get(i);
batch[i] = new MapSqlParameterSource();
for (Entry<String, Integer> entry : sqlTypes.entrySet()) {
if (!valueMap.containsKey(entry.getKey())) {
valueMap.put(entry.getKey(), null);
}
batch[i].addValue(entry.getKey(), valueMap.get(entry.getKey()), entry.getValue());
}
if (logger.isTraceEnabled()) {
logger.trace(
new StringBuffer("SqlId=")
.append(sqlp.containsKey(sqlId) ? sqlId : "")
.append("\n#")
.append((i + 1))
.append("\t")
.append(CapDbUtil.convertToSQLCommand(sql, valueMap))
.toString());
}
}
cur = System.currentTimeMillis();
batchCount =
NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(
parsedSql, batch, super.getJdbcOperations());
} else {
SqlParameterSource[] batch =
SqlParameterSourceUtils.createBatch(
batchValues.toArray(new HashMap[batchValues.size()]));
cur = System.currentTimeMillis();
batchCount =
NamedParameterBatchUpdateUtils.executeBatchUpdateWithNamedParameters(
parsedSql, batch, super.getJdbcOperations());
}
int rows = 0;
for (int i : batchCount) {
rows += i;
}
return rows;
} catch (Exception e) {
Throwable cause = (Throwable) e;
String msg = cause.getMessage();
while ((cause = cause.getCause()) != null) {
if (cause instanceof BatchUpdateException) {
cause = ((BatchUpdateException) cause).getNextException();
}
msg = cause.getMessage();
}
throw new CapDBException(msg, e, causeClass);
} finally {
logger.info("CapNamedJdbcTemplate spend {} ms", (System.currentTimeMillis() - cur));
}
} // ;