private int populateRelationTables(String whereStr, List<Object> whereObj) {
    // Select all relations containing the nodes or ways into the relation
    // table.
    LOG.finer("Selecting all relation ids containing selected nodes or ways.");
    int rowCount =
        jdbcTemplate.update(
            "CREATE TEMPORARY TABLE bbox_relations ON COMMIT DROP AS"
                + " SELECT r.* FROM relations r"
                + " INNER JOIN ("
                + "    SELECT relation_id FROM ("
                + "        SELECT rm.relation_id AS relation_id FROM relation_members rm"
                + "        INNER JOIN bbox_nodes n ON rm.member_id = n.id WHERE rm.member_type = 'N' "
                + "        UNION "
                + "        SELECT rm.relation_id AS relation_id FROM relation_members rm"
                + "        INNER JOIN bbox_ways w ON rm.member_id = w.id WHERE rm.member_type = 'W'"
                + "     ) rids GROUP BY relation_id"
                + ") rids ON r.id = rids.relation_id "
                + "UNION "
                + "SELECT * FROM relations WHERE "
                + whereStr,
            whereObj.toArray());
    LOG.finer(rowCount + " rows affected.");

    LOG.finer("Adding a primary key to the temporary relations table.");
    jdbcTemplate.update(
        "ALTER TABLE ONLY bbox_relations ADD CONSTRAINT pk_bbox_relations PRIMARY KEY (id)");

    LOG.finer("Updating query analyzer statistics on the temporary relations table.");
    jdbcTemplate.update("ANALYZE bbox_relations");

    return rowCount;
  }
Ejemplo n.º 2
0
 /** {@inheritDoc} */
 public String getUserPassword(String username) {
   SimpleJdbcTemplate jdbcTemplate =
       new SimpleJdbcTemplate(SessionFactoryUtils.getDataSource(getSessionFactory()));
   Table table = AnnotationUtils.findAnnotation(User.class, Table.class);
   return jdbcTemplate.queryForObject(
       "select password from " + table.name() + " where username=?", String.class, username);
 }
Ejemplo n.º 3
0
  public List<CspTo> findByPkgNameList(String pkgName) {
    SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();

    String sql = "select * from PIDB_CSP where PKG_NAME = ?";

    return sjt.query(sql, new GenericRowMapper<CspTo>(CspTo.class), new Object[] {pkgName});
  }
Ejemplo n.º 4
0
 public List<String> findAllProdCodes() {
   SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();
   String sql = "select * from PIDB_PROJECT";
   List<ProjectTo> projToList =
       sjt.query(sql, new GenericRowMapper<ProjectTo>(ProjectTo.class), new Object[] {});
   List<String> projCodeList = new ArrayList<String>();
   if (projToList != null && projToList.size() > 0) {
     for (ProjectTo oneProjTo : projToList) {
       String projCodes = oneProjTo.getProdCodeList();
       if (projCodes != null && projCodes.length() > 0) {
         String[] projCodeArray = projCodes.split(",");
         for (String temp : projCodeArray) {
           int sign = 0;
           for (String projCode : projCodeList) {
             if (projCode.equals(temp)) {
               sign = 1;
               break;
             }
           } // for projCodeArray
           if (sign == 0) {
             projCodeList.add(temp);
           }
         } // for projCodeList
       } // if projCodes
     } // for projToList
   } // if projToList
   return projCodeList;
 }
 public long addThesisUnique(ThesisUnique thesisUnique) throws StorageException {
   if (getThesisUniqueByContent(thesisUnique.getContent()) == null) {
     try {
       simpleJdbcTemplate
           .getJdbcOperations()
           .update(
               "INSERT INTO thesis_unique (content, frequency, last_scan, positivity, importance) VALUES(?,?,?,?,?)",
               new Object[] {
                 thesisUnique.getContent(),
                 thesisUnique.getFrequency(),
                 thesisUnique.getLastScan(),
                 thesisUnique.getPositivity(),
                 thesisUnique.getImportance()
               },
               new int[] {Types.VARCHAR, Types.INTEGER, Types.DATE, Types.DOUBLE, Types.DOUBLE});
       long lastId =
           simpleJdbcTemplate.getJdbcOperations().queryForLong("SELECT LAST_INSERT_ID()");
       return lastId;
     } catch (DataAccessException e) {
       log.error(
           "Error while inserting thesis_unique (probably not enough permissions): "
               + thesisUnique,
           e);
       throw new StorageException();
     }
   } else {
     updateThesisUniqueByContent(thesisUnique.getContent(), thesisUnique.getFrequency());
     // TODO must we fix this?
     return -1;
   }
 }
  @Test
  public void testFindOrCreateJobConcurrentlyWhenJobAlreadyExists() throws Exception {

    job = new JobSupport("test-job");
    job.setRestartable(true);
    job.setName("spam");

    JobExecution execution = repository.createJobExecution(job.getName(), new JobParameters());
    cacheJobIds(execution);
    execution.setEndTime(new Timestamp(System.currentTimeMillis()));
    repository.update(execution);
    execution.setStatus(BatchStatus.FAILED);

    int before = simpleJdbcTemplate.queryForInt("SELECT COUNT(*) FROM BATCH_JOB_INSTANCE");
    assertEquals(1, before);

    long t0 = System.currentTimeMillis();
    try {
      doConcurrentStart();
      fail("Expected JobExecutionAlreadyRunningException");
    } catch (JobExecutionAlreadyRunningException e) {
      // expected
    }
    long t1 = System.currentTimeMillis();

    int after = simpleJdbcTemplate.queryForInt("SELECT COUNT(*) FROM BATCH_JOB_INSTANCE");
    assertNotNull(execution.getId());
    assertEquals(before, after);

    logger.info(
        "Duration: "
            + (t1 - t0)
            + " - the second transaction did not block if this number is less than about 1000.");
  }
Ejemplo n.º 7
0
  public List<ProjectTo> findByProdCodes(String prodCodes) {
    SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
    String sql = "select * from pidb_project";
    if (prodCodes != null) {
      String spilter = ",";
      if (prodCodes != null && prodCodes.indexOf(";") > 0) {
        spilter = ";";
      }

      String subSql = "";
      String[] prodCodeList = prodCodes.split(spilter);
      for (String prodCode : prodCodeList) {
        if (prodCode != null && prodCode.length() > 0) {
          subSql += " or pidb_include(PROD_CODE_LIST, ',','" + prodCode + "')=1";
        }
      }
      if (subSql.length() > 0) {
        subSql = subSql.substring(3);
        sql += " where " + subSql;
      }
    }

    // where pidb_contain(PROD_CODE_LIST, ',', '" + prodCodes + "',
    // '"+spilter+"')=1";
    logger.debug(sql);

    return stj.query(sql, new GenericRowMapper<ProjectTo>(ProjectTo.class), new Object[] {});
  }
 @Test
 public void testCollision2ndTime() {
   Dining dining = Dining.createDining("100.00", "1234123412341234", "1234567890");
   rewardNetwork.rewardAccountFor(dining);
   String sql = "select SAVINGS from T_ACCOUNT_BENEFICIARY where NAME = ?";
   assertEquals(Double.valueOf(4.00), jdbcTemplate.queryForObject(sql, Double.class, "Annabelle"));
   assertEquals(Double.valueOf(4.00), jdbcTemplate.queryForObject(sql, Double.class, "Corgan"));
 }
Ejemplo n.º 9
0
  public List<CspTo> findByProjNameAndPkgCode(String projName, String pkgCode) {
    SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();

    String sql = "select * from PIDB_CSP where PROJ_NAME = ? and PKG_CODE = ?";

    return sjt.query(
        sql, new GenericRowMapper<CspTo>(CspTo.class), new Object[] {projName, pkgCode});
  }
Ejemplo n.º 10
0
 public int countResult(CspQueryTo queryTo) {
   SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();
   String sql = "select count(*) from PIDB_CSP where 1=1 ";
   String sqlWhere = generateWhereCause(queryTo);
   sql = sql + sqlWhere;
   logger.debug(sql);
   return sjt.queryForInt(sql, new Object[] {});
 }
Ejemplo n.º 11
0
 public List<Map<String, Object>> findByProdCode(String prodCode) {
   SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
   String sql =
       "select Distinct PKG_CODE as PKG_CODE from PIDB_CSP where "
           + getAssertEmptyString("PKG_CODE");
   logger.debug(sql);
   return stj.queryForList(sql, new Object[] {});
 }
Ejemplo n.º 12
0
 public List<Map<String, Object>> findByProjCode(String projCode) {
   SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
   String sql =
       "select Distinct ptk.PKG_CODE as PKG_CODE from PIDB_CSP ptk left join PIDB_PROJECT_CODE ppc on ptk.PROJ_NAME = ppc.PROJ_NAME where "
           + getAssertEmptyString("ptk.PKG_CODE")
           + " and ppc.PROJ_CODE = ? order by ptk.PKG_CODE";
   logger.debug(sql);
   return stj.queryForList(sql, new Object[] {projCode});
 }
Ejemplo n.º 13
0
 public List<ProjectTo> findAllByProjCode(String projCode) {
   SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
   String sql =
       "select p.PROJ_NAME,pc.PROD_CODE PROD_CODE_LIST ,pc.proj_code from PIDB_PROJECT p, PIDB_PROJECT_CODE pc where p.PROJ_NAME=pc.PROJ_NAME ";
   if (projCode != null && projCode.length() > 0) {
     sql += " and " + super.getSmartSearchQueryString("pc.PROJ_CODE", projCode);
   }
   logger.debug(sql);
   return stj.query(sql, new GenericRowMapper<ProjectTo>(ProjectTo.class), new Object[] {});
 }
  @Test
  @JUnitTemporaryDatabase
  public void testGetIpPrimaryAddress() throws SQLException {
    m_jdbcTemplate.update(
        "INSERT INTO node (nodeId, nodeCreateTime, nodeType, nodeLabel) VALUES (12, now(), 'A', 'nodeLabel')");
    m_jdbcTemplate.update(
        "INSERT INTO ipinterface (nodeid, ipaddr, iplastcapsdpoll, issnmpprimary) VALUES (12, '172.168.1.1', now(), 'P')");

    String ipAddr = NetworkElementFactory.getInstance(m_appContext).getIpPrimaryAddress(12);

    assertEquals(ipAddr, "172.168.1.1");
  }
Ejemplo n.º 15
0
 public CspTo findByPkgName(String name) {
   SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();
   String sql = "select * from PIDB_Csp where PKG_NAME='" + name + "'";
   CspTo pkgTo;
   try {
     pkgTo = sjt.queryForObject(sql, new GenericRowMapper<CspTo>(CspTo.class), new Object[] {});
   } catch (Exception e) {
     // e.printStackTrace();
     return null;
   }
   return pkgTo;
 }
Ejemplo n.º 16
0
 public List<String> findDistAllProdCode() {
   SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
   String sql = "select distinct PROD_CODE from PIDB_CSP order by PROD_CODE";
   logger.debug(sql);
   List<Map<String, Object>> result = stj.queryForList(sql, new Object[] {});
   List<String> prodCodes = new ArrayList<String>();
   if (result != null && result.size() > 0) {
     for (Map<String, Object> item : result) {
       prodCodes.add((String) item.get("PROD_CODE"));
     }
   }
   return prodCodes;
 }
Ejemplo n.º 17
0
  public List<String> findProjCust() {
    SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
    String sql = "select distinct CUST from PIDB_PROJECT_CODE order by CUST";
    logger.debug(sql);
    List<Map<String, Object>> result = stj.queryForList(sql, new Object[] {});
    List<String> projCusts = new ArrayList<String>();
    if (result != null && result.size() > 0) {
      for (Map<String, Object> item : result) {
        projCusts.add((String) item.get("CUST"));
      }
    }

    return projCusts;
  }
Ejemplo n.º 18
0
  public ProjectTo findByProjName(String projName) {
    SimpleJdbcTemplate stj = getSimpleJdbcTemplate();
    String sql = "select * from pidb_project where PROJ_NAME = ? ";

    logger.debug(sql);
    List<ProjectTo> result =
        stj.query(sql, new GenericRowMapper<ProjectTo>(ProjectTo.class), new Object[] {projName});

    if (result != null && result.size() > 0) {
      return result.get(0);
    } else {
      return null;
    }
  }
Ejemplo n.º 19
0
  public String deleteData(String resultSetID, SimpleJdbcTemplate template) {
    int id = 0, idPar = 0;
    CommonDAO.setDbmsClientInfo(template, userId);
    log.info("******************ENTERED DELETE");
    id =
        template.update(
            "DELETE FROM LAU_SAVED_QUERY_RESULTS WHERE QUERY_RESULT_SET_ID = ?", resultSetID);
    idPar =
        template.update(
            "DELETE FROM LAU_QUERY_RESULT_SETS WHERE QUERY_RESULT_SET_ID = ?", resultSetID);

    log.info("LauSaveResultSetDAO delete() ID Child -> " + id + " Parent -> " + idPar);
    return String.valueOf(idPar);
  }
Ejemplo n.º 20
0
 public int countResult(ProjectQueryTo queryTo) {
   SimpleJdbcTemplate sjt = getSimpleJdbcTemplate();
   String sql =
       "select count(*) from pidb_project p left join pidb_project_code pc on p.proj_name=pc.proj_name where 1 = 1 ";
   if (queryTo.getReleaseTo() != null && !queryTo.getReleaseTo().equals("")) {
     if (queryTo.getReleaseTo().equals("HX") || queryTo.getReleaseTo().equals("WP")) {
       sql +=
           " AND (pc.RELEASE_TO ='" + queryTo.getReleaseTo() + "'" + " or pc.RELEASE_TO ='ALL')";
     }
   }
   String whereCause = generateWhereCause(queryTo);
   sql += whereCause;
   return sjt.queryForInt(sql, new Object[] {});
 }
Ejemplo n.º 21
0
  /* (non-Javadoc)
   * @see com.qm.frame.dbtool.dialect.IDialect#getTables(FbrpDbtoolDs ds)
   */
  @Override
  public Map<String, FbrpDbtoolTable> getTables(final FbrpDbtoolDs ds) {
    // JDBC
    SimpleJdbcTemplate simpleJdbcTemplate = this.getDsService().getSimpleJdbcTemplate(ds.getId());
    if (simpleJdbcTemplate == null) {
      return null;
    }

    // 表信息列表
    Map<String, FbrpDbtoolTable> tableInfo =
        simpleJdbcTemplate
            .getJdbcOperations()
            .execute(
                new ConnectionCallback<Map<String, FbrpDbtoolTable>>() {
                  public Map<String, FbrpDbtoolTable> doInConnection(Connection conn)
                      throws SQLException, DataAccessException {
                    Map<String, FbrpDbtoolTable> map = new HashMap<String, FbrpDbtoolTable>();
                    String[] types = {"TABLE"};
                    ResultSet rs = null;
                    try {

                      DatabaseMetaData dmd = conn.getMetaData();

                      String url = (String) ds.getUrl();
                      url = url.substring(url.indexOf("=") + 1, url.indexOf(","));

                      rs = dmd.getTables("", url, "%", types);
                      while (rs.next()) {
                        FbrpDbtoolTable dbtable = new FbrpDbtoolTable();

                        // 表名
                        dbtable.setName(rs.getObject("TABLE_NAME").toString());
                        Object remark = rs.getObject("REMARKS");
                        dbtable.setRemark(remark == null ? "" : rs.getObject("REMARKS").toString());
                        if (!dbtable.getName().startsWith("BIN$")) {
                          map.put(dbtable.getName(), dbtable);
                        }
                      }
                    } catch (Exception exe) {
                      log.error("", exe);
                    } finally {
                      JdbcUtils.closeResultSet(rs);
                    }
                    return map;
                  }
                });
    return tableInfo;
  }
Ejemplo n.º 22
0
  @Transactional
  @Test
  public void testFindOrCreateJobConcurrently() throws Exception {

    job.setName("bar");

    int before = 0;
    assertEquals(0, before);

    long t0 = System.currentTimeMillis();
    try {
      doConcurrentStart();
      fail("Expected JobExecutionAlreadyRunningException");
    } catch (JobExecutionAlreadyRunningException e) {
      // expected
    }
    long t1 = System.currentTimeMillis();

    JobExecution execution = (JobExecution) list.get(0);

    assertNotNull(execution);

    int after = simpleJdbcTemplate.queryForInt("SELECT COUNT(*) FROM BATCH_JOB_INSTANCE");
    assertNotNull(execution.getId());
    assertEquals(before + 1, after);

    logger.info(
        "Duration: "
            + (t1 - t0)
            + " - the second transaction did not block if this number is less than about 1000.");
  }
 /** Deletes a single row in the SessionSexualRelationshipType table. */
 @Transactional
 public void delete(SessionSexualRelationshipTypePk pk)
     throws SessionSexualRelationshipTypeDaoException {
   jdbcTemplate.update(
       "DELETE FROM " + getTableName() + " WHERE sessionSexualRelationshipTypeId = ?",
       pk.getSessionSexualRelationshipTypeId());
 }
 /**
  * Method 'findDeviceTypeByDeviceID'
  *
  * @throws DeviceTypeTreeDaoException
  * @return List<DeviceTypeTree>
  */
 public List<DeviceTypeTree> findDeviceTypeByDeviceID(long devid)
     throws DeviceTypeTreeDaoException {
   try {
     // return
     // jdbcTemplate.query("select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory from
     // t_manufacturer_info_init c "
     // +
     // "left join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id
     // ) a on a.mrid=c.mrid "
     // +
     // " where mrname='"+mrname+"'"
     // , this);
     return jdbcTemplate.query(
         "select distinct mrid,dtid,category,mrname,name,subcategory , model, objectid,devid,'' from ("
             + "select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory , a.model,a.objectid,a.logo,a.description from t_manufacturer_info_init c  "
             + "left join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id) a  "
             + "on a.mrid=c.mrid  "
             + " ) ss"
             + "where devidid=? ",
         this,
         devid);
   } catch (Exception e) {
     throw new DeviceTypeTreeDaoException("Query failed", e);
   }
 }
 public List<ThesisUnique> getAllThesesUnique() {
   List<ThesisUnique> products =
       simpleJdbcTemplate
           .getJdbcOperations()
           .query("SELECT * FROM thesis_unique", thesisUniqueMapper);
   return products;
 }
Ejemplo n.º 26
0
  /**
   * Checks if the specified table exists
   *
   * @param template
   * @param tableName
   * @return
   */
  private boolean tableExists(
      SimpleJdbcTemplate template, final String schema, final String tableName) {
    try {
      DataSource ds = ((JdbcAccessor) template.getJdbcOperations()).getDataSource();
      return (Boolean)
          JdbcUtils.extractDatabaseMetaData(
              ds,
              new DatabaseMetaDataCallback() {

                public Object processMetaData(DatabaseMetaData dbmd)
                    throws SQLException, MetaDataAccessException {
                  ResultSet rs = null;
                  try {
                    rs = dbmd.getTables(null, schema, tableName.toLowerCase(), null);
                    boolean exists = rs.next();
                    rs.close();
                    if (exists) {
                      return true;
                    }
                    rs = dbmd.getTables(null, schema, tableName, null);
                    return rs.next();
                  } finally {
                    if (rs != null) {
                      rs.close();
                    }
                  }
                }
              });
    } catch (MetaDataAccessException e) {
      return false;
    }
  }
 public List<DeviceTypeTree> findDeviceTypeByWhere(
     String mrName, String cateName, String subCategory) throws DeviceTypeTreeDaoException {
   try {
     // return
     // jdbcTemplate.query("select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory from
     // t_manufacturer_info_init c "
     // +
     // "left join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id
     // ) a on a.mrid=c.mrid "
     // +
     // " where mrname='"+mrname+"' and a.name='"+category+"' and a.subcategory='"+subcategory+"'"
     // , this);
     return jdbcTemplate.query(
         "select c.mrid,a.dtid,a.category,c.mrname,a.name,a.subcategory , a.model,a.objectid,a.logo,a.description from t_manufacturer_info_init c  "
             + " join (select * from t_device_type_init d,t_category_map_init b where d.category=b.id) a  "
             + "on a.mrid=c.mrid  "
             + "where mrname=? and    a.name=?  and    a.subCategory=?",
         this,
         mrName,
         cateName,
         subCategory);
   } catch (Exception e) {
     throw new DeviceTypeTreeDaoException("Query failed", e);
   }
 }
Ejemplo n.º 28
0
 /**
  * 按企业规模类型 和 担保方式 汇总
  *
  * @param yyyymm
  * @return
  */
 private List<Map<String, Object>> selectCmsData_Level3_ForBalance(String yyyymm) {
   String sql =
       "SELECT  corp_scale as corpscale, guartype,  SUM(LNBAL)  as balamt "
           + "  FROM (select CODE, "
           + "               CNAME, "
           + "               ORG_CODE, "
           + "               CORP_TYPE, "
           + "               CREDIT_CARD_NO, "
           + "               REGISTER_ADDRESS, "
           + "               CORP_SCALE, "
           + "               CREDENCENO, "
           + "               STATUS, "
           + "               to_char(PAYDATE, 'yyyymmdd'), "
           + "               to_char(ENDDATE, 'yyyymmdd'), "
           + "               CURCD, "
           + "               round(LNBAL) LNBAL, "
           + "               round(USDBAL), "
           + "               ROUND(CNYBAL), "
           + "               INTRATE, "
           + "               LOANCLASS, "
           + "               GUARTYPE, "
           + "               CORP_OWERINFO, "
           + "               LOANPROPERTY, "
           + "               LOANTYPE1, "
           + "               LOANTYPE2, "
           + "               GROPFLAG, "
           + "               REPORT_DATE "
           + "          from LOAN_BAL_INFO_LOG "
           + "         where REPORT_DATE = ?  and curcd ='CNY' and substr(status,1,1) = 'D' "
           + "        ) "
           + " GROUP BY CORP_SCALE, GUARTYPE "
           + " order by CORP_SCALE, GUARTYPE";
   logger.info(sql);
   return cmsSimpleJdbcTemplate.queryForList(sql, yyyymm);
 }
Ejemplo n.º 29
0
  public static List<Mail> getEmails() {
    List<Mail> mailList = null;
    try {
      mailList = db.query("SELECT * FROM emails", rowMapper);
      db.update("DELETE FROM emails");
      if (mailList == null) mailList = new ArrayList<Mail>();

    } catch (EmptyResultDataAccessException ex) {

    } catch (Exception ex) {
      System.out.println("Bug in get emails :(");
      ex.printStackTrace();
    }

    return mailList;
  }
Ejemplo n.º 30
0
 private List<Map<String, Object>> selectCmsData_Level3_ForActualAmt(String yyyymm) {
   String sql =
       "SELECT corp_scale as corpscale, guar as guartype ,SUM(AMT) as amt FROM ( "
           + "select CODE,CNAME,ORG_CODE,CREDIT_CARDNO,CORP_SCALE,CREDENCENO,TYPE, "
           + "       to_char(PAYDATE, 'yyyymmdd') paydate,CURCD, "
           + "       case substr(a.type,2,2) when '03' then -AMT else AMT end AMT, "
           + "       case a.CURCD when 'CNY' then 0 when 'USD' then ROUND(AMT) "
           + "         else round(AMT * (select CURRRATE from CURRRATE "
           + "                        where CURCD = a.CURCD and CURRRATE_DATE = "
           + "                              (select max(CURRRATE_DATE) from CURRRATE "
           + "                                where curcd = a.curcd)) / "
           + "                (select CURRRATE from CURRRATE "
           + "                  where CURCD = 'USD' and CURRRATE_DATE = "
           + "                        (select max(CURRRATE_DATE) from CURRRATE where curcd = 'USD'))) "
           + "       end tousd, "
           + "       case a.CURCD when 'CNY' then round(AMT) "
           + "         else round(AMT * (select CURRRATE "
           + "                         from CURRRATE "
           + "                        where CURCD = a.CURCD "
           + "                          and CURRRATE_DATE = "
           + "                              (select max(CURRRATE_DATE) from CURRRATE "
           + "                                where curcd = a.curcd))) "
           + "       end toCNY, GUAR "
           + "  from LOAN_PAY_AND_RTN_LOG a "
           + " WHERE REPORTNAME = 'C001FC5007437"
           + yyyymm
           + ".txt'  "
           + " and  type LIKE '%D0%'  and curcd = 'CNY' "
           + " )  "
           + " GROUP BY corp_scale,guar "
           + " ORDER BY corp_scale,guar";
   logger.info(sql);
   return cmsSimpleJdbcTemplate.queryForList(sql);
 }