示例#1
0
  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));
    }
  } // ;
示例#2
0
 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));
   }
 } // ;
示例#3
0
 /**
  * 查詢,查詢結果為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));
   }
 } // ;
示例#4
0
 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);
   }
 }
示例#5
0
 /**
  * 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));
   }
 } // ;
示例#6
0
 /**
  * 新增、修改、刪除
  *
  * @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));
   }
 } // ;
示例#7
0
 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));
   }
 } // ;
示例#8
0
  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));
    }
  } // ;