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); }
private void backfillNodesTables() { // If complete ways is set, select all nodes contained by the ways into // the node temp table. LOG.finer("Selecting all nodes for selected ways."); jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_way_nodes (id bigint) ON COMMIT DROP"); jdbcTemplate.queryForList("SELECT unnest_bbox_way_nodes()"); jdbcTemplate.update( "CREATE TEMPORARY TABLE bbox_missing_way_nodes ON COMMIT DROP AS " + "SELECT buwn.id FROM (SELECT DISTINCT bwn.id FROM bbox_way_nodes bwn) buwn " + "WHERE NOT EXISTS (" + " SELECT * FROM bbox_nodes WHERE id = buwn.id" + ");"); jdbcTemplate.update( "ALTER TABLE ONLY bbox_missing_way_nodes" + " ADD CONSTRAINT pk_bbox_missing_way_nodes PRIMARY KEY (id)"); jdbcTemplate.update("ANALYZE bbox_missing_way_nodes"); int rowCount = jdbcTemplate.update( "INSERT INTO bbox_nodes " + "SELECT n.* FROM nodes n INNER JOIN bbox_missing_way_nodes bwn ON n.id = bwn.id;"); LOG.finer(rowCount + " rows affected."); LOG.finer("Updating query analyzer statistics on the temporary nodes table."); jdbcTemplate.update("ANALYZE bbox_nodes"); }
/** * 按企业规模类型 和 担保方式 汇总 * * @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 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[] {}); }
/** * 根据分支机构编码查询分支机构信息 * * @param username * @return object[]:result[0]是分支机构id,result[1]是分支机构所在租户id */ public Object[] getBranchInfoByCode(String companyCode) { List<Map<String, Object>> list = jdbcTemplate.queryForList(DEPARTMENT_SQL, companyCode); if (list.isEmpty()) return null; Object[] result = new Object[2]; result[0] = list.get(0).get("id"); result[1] = list.get(0).get("fk_company_id"); return result; }
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 Boolean checkNameExists(SimpleJdbcTemplate template, String rsltName) throws Exception { log.info("checkNameExists enter----> "); boolean namePrsnt = false; String getRsltSet = "SELECT * FROM LAU_QUERY_RESULT_SETS WHERE RESULT_SET_NAME = ?"; List<Map<String, Object>> rows = template.queryForList(getRsltSet, rsltName); log.info("Size of name list----> " + rows.size()); int listSize = rows.size(); if (listSize != 0) { namePrsnt = true; } return namePrsnt; }
public List<String> findPkgTypeByPkgCode(final String pkgCode) { SimpleJdbcTemplate stj = getSimpleJdbcTemplate(); String sql = ""; List<Map<String, Object>> result; if (pkgCode != null && pkgCode.length() > 0) { sql = "select distinct TRAD_PKG_TYPE from PIDB_CSP where PKG_CODE = ? order by TRAD_PKG_TYPE"; logger.debug(sql); result = stj.queryForList(sql, new Object[] {pkgCode}); } else { sql = "select distinct TRAD_PKG_TYPE from PIDB_CSP order by TRAD_PKG_TYPE"; logger.debug(sql); result = stj.queryForList(sql, new Object[] {}); } List<String> pkgTypes = new ArrayList<String>(); if (result != null && result.size() > 0) { for (Map<String, Object> item : result) { pkgTypes.add((String) item.get("CSP_TYPE")); } } return pkgTypes; }
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; }
/** * 在总公司(非分支机构内)内查询用户ID和用户姓名 * * @param username * @return null 用户不存在 */ public Object[] getUser(String username, Long companyId) { List<Map<String, Object>> list = jdbcTemplate.queryForList(USER_COMPANY_SQL, username, companyId); if (list.size() <= 0) return null; Object[] result = new Object[6]; result[0] = list.get(0).get("id"); result[1] = list.get(0).get("name"); result[2] = list.get(0).get("fk_company_id"); result[3] = list.get(0).get("sub_company_id"); result[4] = list.get(0).get("password"); result[5] = list.get(0).get("login_name"); return result; }
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 List<String> findPkgCodeByPkgCode(final String pkgCode) { SimpleJdbcTemplate stj = getSimpleJdbcTemplate(); String sql = "select distinct PKG_CODE from PIDB_CSP where (" + super.getSmartSearchQueryString("PKG_CODE", pkgCode) + ") order by PKG_CODE"; logger.debug(sql); List<Map<String, Object>> result = stj.queryForList(sql, new Object[] {}); List<String> pkgCodes = new ArrayList<String>(); if (result != null && result.size() > 0) { for (Map<String, Object> item : result) { pkgCodes.add((String) item.get("PKG_CODE")); } } return pkgCodes; }
public List<String> findFab() { SimpleJdbcTemplate stj = getSimpleJdbcTemplate(); String sql = "select distinct FAB from PIDB_PROJECT where " + getAssertEmptyString("FAB") + " order by FAB"; logger.debug(sql); List<Map<String, Object>> result = stj.queryForList(sql, new Object[] {}); List<String> ret = new ArrayList<String>(); if (result != null && result.size() > 0) { for (Map<String, Object> item : result) { ret.add((String) item.get("FAB")); } } return ret; }
public void dbupdate() { String sql = " SELECT version FROM oncows_db_version " + " WHERE type='structure' ORDER BY version DESC "; log.debug(sql); // if(true) return; Map<String, Object> map = simpleJdbc.queryForList(sql).get(0); Timestamp lastUpdate = (Timestamp) map.get("version"); log.debug(lastUpdate); setFlushModeCommit(); for (DbUpdate dbUpdate1 : getDbUpdateL()) { if (dbUpdate1.getDbUpdateDate().after(lastUpdate)) { log.debug(dbUpdate1.getDbUpdateDate()); if (dbUpdate1 instanceof DbIndexes) { DbIndexes dbUpdate2 = (DbIndexes) dbUpdate1; dbUpdate2.updateIndexes(); } else dbUpdate1.update("structure"); } } }
/** * 根据用户名查询公司id * * @param username * @return */ public Long getCompanyId(String username) { List<Map<String, Object>> list = jdbcTemplate.queryForList(COMPANY_ID_SQL, username); if (list.isEmpty()) return null; Object obj = list.get(0).get("fk_company_id"); return Long.valueOf(obj.toString()); }
/** {@inheritDoc} */ public ReleasableIterator<EntityContainer> iterateBoundingBox( double left, double right, double top, double bottom, boolean completeWays) { List<Bound> bounds; Point[] bboxPoints; Polygon bboxPolygon; int rowCount; List<ReleasableIterator<EntityContainer>> resultSets = new ArrayList<ReleasableIterator<EntityContainer>>(); if (!initialized) { initialize(); } // Build the bounds list. bounds = new ArrayList<Bound>(); bounds.add(new Bound(right, left, top, bottom, "Osmosis " + OsmosisConstants.VERSION)); // PostgreSQL sometimes incorrectly chooses to perform full table scans, // these options prevent this. Note that this is not recommended // practice according to documentation but fixing this would require // modifying the table statistics gathering configuration to produce // better plans. jdbcTemplate.update("SET enable_seqscan = false"); jdbcTemplate.update("SET enable_mergejoin = false"); jdbcTemplate.update("SET enable_hashjoin = false"); // Build a polygon representing the bounding box. // Sample box for query testing may be: // GeomFromText('POLYGON((144.93912192855174 -37.82981987499741, // 144.93912192855174 -37.79310006709244, 144.98188026000003 // -37.79310006709244, 144.98188026000003 -37.82981987499741, // 144.93912192855174 -37.82981987499741))', -1) bboxPoints = new Point[5]; bboxPoints[0] = new Point(left, bottom); bboxPoints[1] = new Point(left, top); bboxPoints[2] = new Point(right, top); bboxPoints[3] = new Point(right, bottom); bboxPoints[4] = new Point(left, bottom); bboxPolygon = PolygonBuilder.createPolygon(bboxPoints); // Select all nodes inside the box into the node temp table. LOG.finer("Selecting all nodes inside bounding box."); rowCount = jdbcTemplate.update( "CREATE TEMPORARY TABLE bbox_nodes ON COMMIT DROP AS" + " SELECT * FROM nodes WHERE ST_Intersects(geom, ?)", new PGgeometry(bboxPolygon)); LOG.finer("Adding a primary key to the temporary nodes table."); jdbcTemplate.update( "ALTER TABLE ONLY bbox_nodes ADD CONSTRAINT pk_bbox_nodes PRIMARY KEY (id)"); LOG.finer("Updating query analyzer statistics on the temporary nodes table."); jdbcTemplate.update("ANALYZE bbox_nodes"); // Select all ways inside the bounding box into the way temp table. if (capabilityChecker.isWayLinestringSupported()) { LOG.finer("Selecting all ways inside bounding box using way linestring geometry."); // We have full way geometry available so select ways // overlapping the requested bounding box. String sql = "CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS" + " SELECT * FROM ways WHERE ST_Intersects(linestring, ?)"; LOG.info("Exec SQL: " + sql + " -- args: " + bboxPolygon); rowCount = jdbcTemplate.update(sql, new PGgeometry(bboxPolygon)); } else if (capabilityChecker.isWayBboxSupported()) { LOG.finer( "Selecting all ways inside bounding box using dynamically built" + " way linestring with way bbox indexing."); // The inner query selects the way id and node coordinates for // all ways constrained by the way bounding box which is // indexed. // The middle query converts the way node coordinates into // linestrings. // The outer query constrains the query to the linestrings // inside the bounding box. These aren't indexed but the inner // query way bbox constraint will minimise the unnecessary data. rowCount = jdbcTemplate.update( "CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS" + " SELECT w.* FROM (" + " SELECT c.id AS id, First(c.version) AS version, First(c.user_id) AS user_id," + " First(c.tstamp) AS tstamp, First(c.changeset_id) AS changeset_id, First(c.tags) AS tags," + " First(c.nodes) AS nodes, MakeLine(c.geom) AS way_line FROM (" + " SELECT w.*, n.geom AS geom FROM nodes n" + " INNER JOIN way_nodes wn ON n.id = wn.node_id" + " INNER JOIN ways w ON wn.way_id = w.id" + " WHERE (w.bbox && ?) ORDER BY wn.way_id, wn.sequence_id" + " ) c " + " GROUP BY c.id" + " ) w " + "WHERE (w.way_line && ?)", new PGgeometry(bboxPolygon), new PGgeometry(bboxPolygon)); } else { LOG.finer("Selecting all way ids inside bounding box using already selected nodes."); // No way bbox support is available so select ways containing // the selected nodes. rowCount = jdbcTemplate.update( "CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS" + " SELECT w.* FROM ways w" + " INNER JOIN (" + " SELECT wn.way_id FROM way_nodes wn" + " INNER JOIN bbox_nodes n ON wn.node_id = n.id GROUP BY wn.way_id" + ") wids ON w.id = wids.way_id"); } LOG.finer(rowCount + " rows affected."); LOG.finer("Adding a primary key to the temporary ways table."); jdbcTemplate.update("ALTER TABLE ONLY bbox_ways ADD CONSTRAINT pk_bbox_ways PRIMARY KEY (id)"); LOG.finer("Updating query analyzer statistics on the temporary ways table."); jdbcTemplate.update("ANALYZE bbox_ways"); // Select all relations containing the nodes or ways into the relation // table. LOG.finer("Selecting all relation ids containing selected nodes or ways."); 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"); 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"); // Include all relations containing the current relations into the // relation table and repeat until no more inclusions occur. do { LOG.finer("Selecting parent relations of selected relations."); rowCount = jdbcTemplate.update( "INSERT INTO bbox_relations " + "SELECT r.* FROM relations r INNER JOIN (" + " SELECT rm.relation_id FROM relation_members rm" + " INNER JOIN bbox_relations br ON rm.member_id = br.id" + " WHERE rm.member_type = 'R' AND NOT EXISTS (" + " SELECT * FROM bbox_relations br2 WHERE rm.relation_id = br2.id" + " ) GROUP BY rm.relation_id" + ") rids ON r.id = rids.relation_id"); LOG.finer(rowCount + " rows affected."); } while (rowCount > 0); LOG.finer("Updating query analyzer statistics on the temporary relations table."); jdbcTemplate.update("ANALYZE bbox_relations"); // If complete ways is set, select all nodes contained by the ways into // the node temp table. if (completeWays) { LOG.finer("Selecting all nodes for selected ways."); jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_way_nodes (id bigint) ON COMMIT DROP"); jdbcTemplate.queryForList("SELECT unnest_bbox_way_nodes()"); jdbcTemplate.update( "CREATE TEMPORARY TABLE bbox_missing_way_nodes ON COMMIT DROP AS " + "SELECT buwn.id FROM (SELECT DISTINCT bwn.id FROM bbox_way_nodes bwn) buwn " + "WHERE NOT EXISTS (" + " SELECT * FROM bbox_nodes WHERE id = buwn.id" + ");"); jdbcTemplate.update( "ALTER TABLE ONLY bbox_missing_way_nodes" + " ADD CONSTRAINT pk_bbox_missing_way_nodes PRIMARY KEY (id)"); jdbcTemplate.update("ANALYZE bbox_missing_way_nodes"); rowCount = jdbcTemplate.update( "INSERT INTO bbox_nodes " + "SELECT n.* FROM nodes n INNER JOIN bbox_missing_way_nodes bwn ON n.id = bwn.id;"); LOG.finer(rowCount + " rows affected."); } LOG.finer("Updating query analyzer statistics on the temporary nodes table."); jdbcTemplate.update("ANALYZE bbox_nodes"); // Create iterators for the selected records for each of the entity // types. LOG.finer("Iterating over results."); resultSets.add( new UpcastIterator<EntityContainer, BoundContainer>( new BoundContainerIterator( new ReleasableAdaptorForIterator<Bound>(bounds.iterator())))); resultSets.add( new UpcastIterator<EntityContainer, NodeContainer>( new NodeContainerIterator(nodeDao.iterate("bbox_")))); resultSets.add( new UpcastIterator<EntityContainer, WayContainer>( new WayContainerIterator(wayDao.iterate("bbox_")))); resultSets.add( new UpcastIterator<EntityContainer, RelationContainer>( new RelationContainerIterator(relationDao.iterate("bbox_")))); // Merge all readers into a single result iterator and return. return new MultipleSourceIterator<EntityContainer>(resultSets); }
/** * 根据公司编码查询公司id * * @param username * @return */ public Long getCompanyIdByCode(String companyCode) { List<Map<String, Object>> list = jdbcTemplate.queryForList(COMPANY_SQL, companyCode); if (list.isEmpty()) return null; Object obj = list.get(0).get("id"); return Long.valueOf(obj.toString()); }
public ReleasableIterator<EntityContainer> iterateSelectedWays( List<? extends Selector> tagSelectors) { int rowCount; List<ReleasableIterator<EntityContainer>> resultSets = new ArrayList<ReleasableIterator<EntityContainer>>(); ArrayList<Bound> bounds = new ArrayList<Bound>(); if (!initialized) { initialize(); } String whereStr = buildSelectorWhereClause(tagSelectors); if (capabilityChecker.isWayLinestringSupported()) { whereStr = whereStr.replace("geom", "linestring"); } else if (capabilityChecker.isWayBboxSupported()) { whereStr = whereStr.replace("geom", "bbox"); } List<Object> whereObj = buildSelectorWhereParameters(tagSelectors); for (Selector selector : tagSelectors) { if (selector instanceof Selector.Polygon) { Selector.Polygon boundingBox = (Selector.Polygon) selector; double right = boundingBox.getRight(); double left = boundingBox.getLeft(); double top = boundingBox.getTop(); double bottom = boundingBox.getBottom(); bounds.add(new Bound(right, left, top, bottom, "Osmosis " + OsmosisConstants.VERSION)); } } // PostgreSQL sometimes incorrectly chooses to perform full table scans, // these options prevent this. Note that this is not recommended // practice according to documentation but fixing this would require // modifying the table statistics gathering configuration to produce // better plans. jdbcTemplate.update("SET enable_seqscan = false"); jdbcTemplate.update("SET enable_mergejoin = false"); jdbcTemplate.update("SET enable_hashjoin = false"); LOG.finer("Creating empty nodes table."); rowCount = jdbcTemplate.update( "CREATE TEMPORARY TABLE bbox_nodes ON COMMIT DROP AS" + " SELECT * FROM nodes WHERE FALSE"); // Select all ways inside the bounding box into the way temp table. LOG.finer("Selecting all ways inside bounding box using way linestring geometry."); // We have full way geometry available so select ways // overlapping the requested bounding box. String sql = "CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS SELECT * FROM ways WHERE " + whereStr; rowCount = jdbcTemplate.update(sql, whereObj.toArray()); LOG.finer(rowCount + " rows affected."); LOG.finer("Adding a primary key to the temporary ways table."); jdbcTemplate.update("ALTER TABLE ONLY bbox_ways ADD CONSTRAINT pk_bbox_ways PRIMARY KEY (id)"); LOG.finer("Updating query analyzer statistics on the temporary ways table."); jdbcTemplate.update("ANALYZE bbox_ways"); LOG.finer("Selecting all nodes for selected ways."); jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_way_nodes (id bigint) ON COMMIT DROP"); jdbcTemplate.queryForList("SELECT unnest_bbox_way_nodes()"); jdbcTemplate.update( "CREATE TEMPORARY TABLE bbox_missing_way_nodes ON COMMIT DROP AS " + "SELECT buwn.id FROM (SELECT DISTINCT bwn.id FROM bbox_way_nodes bwn) buwn " + "WHERE NOT EXISTS (" + " SELECT * FROM bbox_nodes WHERE id = buwn.id" + ");"); jdbcTemplate.update( "ALTER TABLE ONLY bbox_missing_way_nodes" + " ADD CONSTRAINT pk_bbox_missing_way_nodes PRIMARY KEY (id)"); jdbcTemplate.update("ANALYZE bbox_missing_way_nodes"); rowCount = jdbcTemplate.update( "INSERT INTO bbox_nodes " + "SELECT n.* FROM nodes n INNER JOIN bbox_missing_way_nodes bwn ON n.id = bwn.id;"); LOG.finer(rowCount + " rows affected."); LOG.finer("Updating query analyzer statistics on the temporary nodes table."); jdbcTemplate.update("ANALYZE bbox_nodes"); // Create iterators for the selected records for each of the entity // types. LOG.finer("Iterating over results."); resultSets.add( new UpcastIterator<EntityContainer, BoundContainer>( new BoundContainerIterator( new ReleasableAdaptorForIterator<Bound>(bounds.iterator())))); resultSets.add( new UpcastIterator<EntityContainer, NodeContainer>( new NodeContainerIterator(nodeDao.iterate("bbox_")))); resultSets.add( new UpcastIterator<EntityContainer, WayContainer>( new WayContainerIterator(wayDao.iterate("bbox_")))); // Merge all readers into a single result iterator and return. return new MultipleSourceIterator<EntityContainer>(resultSets); }
public ReleasableIterator<EntityContainer> iterateWays(List<Long> ids) { int rowCount; List<ReleasableIterator<EntityContainer>> resultSets = new ArrayList<ReleasableIterator<EntityContainer>>(); if (!initialized) { initialize(); } // PostgreSQL sometimes incorrectly chooses to perform full table scans, // these options prevent this. Note that this is not recommended // practice according to documentation but fixing this would require // modifying the table statistics gathering configuration to produce // better plans. jdbcTemplate.update("SET enable_seqscan = false"); jdbcTemplate.update("SET enable_mergejoin = false"); jdbcTemplate.update("SET enable_hashjoin = false"); LOG.finer("Creating empty nodes table."); rowCount = jdbcTemplate.update( "CREATE TEMPORARY TABLE bbox_nodes ON COMMIT DROP AS" + " SELECT * FROM nodes WHERE FALSE"); String idsSql = buildListSql(ids); rowCount = jdbcTemplate.update( "CREATE TEMPORARY TABLE bbox_ways ON COMMIT DROP AS SELECT * FROM ways WHERE id IN " + idsSql, ids.toArray()); LOG.finer(rowCount + " rows affected."); LOG.finer("Updating query analyzer statistics on the temporary ways table."); jdbcTemplate.update("ANALYZE bbox_ways"); LOG.finer("Selecting all nodes for selected ways."); jdbcTemplate.update("CREATE TEMPORARY TABLE bbox_way_nodes (id bigint) ON COMMIT DROP"); jdbcTemplate.queryForList("SELECT unnest_bbox_way_nodes()"); jdbcTemplate.update( "CREATE TEMPORARY TABLE bbox_missing_way_nodes ON COMMIT DROP AS " + "SELECT buwn.id FROM (SELECT DISTINCT bwn.id FROM bbox_way_nodes bwn) buwn " + "WHERE NOT EXISTS (" + " SELECT * FROM bbox_nodes WHERE id = buwn.id" + ");"); jdbcTemplate.update( "ALTER TABLE ONLY bbox_missing_way_nodes" + " ADD CONSTRAINT pk_bbox_missing_way_nodes PRIMARY KEY (id)"); jdbcTemplate.update("ANALYZE bbox_missing_way_nodes"); rowCount = jdbcTemplate.update( "INSERT INTO bbox_nodes " + "SELECT n.* FROM nodes n INNER JOIN bbox_missing_way_nodes bwn ON n.id = bwn.id;"); LOG.finer(rowCount + " rows affected."); LOG.finer("Updating query analyzer statistics on the temporary nodes table."); jdbcTemplate.update("ANALYZE bbox_nodes"); // Create iterators for the selected records for each of the entity // types. LOG.finer("Iterating over results."); resultSets.add( new UpcastIterator<EntityContainer, NodeContainer>( new NodeContainerIterator(nodeDao.iterate("bbox_")))); resultSets.add( new UpcastIterator<EntityContainer, WayContainer>( new WayContainerIterator(wayDao.iterate("bbox_")))); // Merge all readers into a single result iterator and return. return new MultipleSourceIterator<EntityContainer>(resultSets); }