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;
}
/** {@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);
}
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});
}
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.");
}
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"));
}
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});
}
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[] {});
}
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[] {});
}
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});
}
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");
}
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;
}
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;
}
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;
}
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;
}
}
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);
}
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[] {});
}
/* (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;
}
@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;
}
/**
* 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);
}
}
/**
* 按企业规模类型 和 担保方式 汇总
*
* @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);
}
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;
}
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);
}
