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); }