List of usage examples for org.springframework.jdbc.core JdbcTemplate queryForList
@Override
public List<Map<String, Object>> queryForList(String sql, @Nullable Object... args) throws DataAccessException
From source file:org.apache.syncope.core.logic.init.CamelRouteLoader.java
private void loadRoutes(final String domain, final DataSource dataSource, final Resource resource, final AnyTypeKind anyTypeKind) { JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); boolean shouldLoadRoutes = jdbcTemplate.queryForList( String.format("SELECT * FROM %s WHERE ANYTYPEKIND = ?", CamelRoute.class.getSimpleName()), new Object[] { anyTypeKind.name() }).isEmpty(); if (shouldLoadRoutes) { try {/*from w w w. jav a2 s .c om*/ TransformerFactory tf = null; DOMImplementationLS domImpl = null; NodeList routeNodes; if (IS_JBOSS) { tf = TransformerFactory.newInstance(); tf.setFeature(javax.xml.XMLConstants.FEATURE_SECURE_PROCESSING, true); DocumentBuilderFactory dbFactory = DocumentBuilderFactory.newInstance(); dbFactory.setFeature(javax.xml.XMLConstants.FEATURE_SECURE_PROCESSING, true); DocumentBuilder dBuilder = dbFactory.newDocumentBuilder(); Document doc = dBuilder.parse(resource.getInputStream()); routeNodes = doc.getDocumentElement().getElementsByTagName("route"); } else { DOMImplementationRegistry reg = DOMImplementationRegistry.newInstance(); domImpl = (DOMImplementationLS) reg.getDOMImplementation("LS"); LSInput lsinput = domImpl.createLSInput(); lsinput.setByteStream(resource.getInputStream()); LSParser parser = domImpl.createLSParser(DOMImplementationLS.MODE_SYNCHRONOUS, null); routeNodes = parser.parse(lsinput).getDocumentElement().getElementsByTagName("route"); } for (int s = 0; s < routeNodes.getLength(); s++) { Node routeElement = routeNodes.item(s); String routeContent = IS_JBOSS ? nodeToString(routeNodes.item(s), tf) : nodeToString(routeNodes.item(s), domImpl); String routeId = ((Element) routeElement).getAttribute("id"); jdbcTemplate.update( String.format("INSERT INTO %s(ID, ANYTYPEKIND, CONTENT) VALUES (?, ?, ?)", CamelRoute.class.getSimpleName()), new Object[] { routeId, anyTypeKind.name(), routeContent }); LOG.info("[{}] Route successfully loaded: {}", domain, routeId); } } catch (Exception e) { LOG.error("[{}] Route load failed", domain, e); } } }
From source file:org.cloudfoundry.identity.uaa.db.Create_Groups_For_Zones_2_5_2.java
@Override public void migrate(JdbcTemplate jdbcTemplate) throws Exception { String groupCreateSQL = "INSERT INTO groups (id,displayName,created,lastModified,version,identity_zone_id) VALUES (?,?,?,?,?,?)"; Map<String, Map<String, String>> zoneIdToGroupNameToGroupId = new HashMap<>(); //duplicate all existing groups across zones List<String> zones = jdbcTemplate.queryForList("SELECT id FROM identity_zone WHERE id <> 'uaa'", String.class); List<String> groups = jdbcTemplate .queryForList("SELECT displayName FROM groups WHERE identity_zone_id = 'uaa'", String.class); for (String zoneId : zones) { Map<String, String> groupNameToGroupId = new HashMap<>(); zoneIdToGroupNameToGroupId.put(zoneId, groupNameToGroupId); Timestamp now = new Timestamp(System.currentTimeMillis()); for (String displayName : groups) { if (displayName.startsWith("zones.")) { continue; }//w ww.ja v a 2 s . co m String id = UUID.randomUUID().toString(); jdbcTemplate.update(groupCreateSQL, id, displayName, now, now, 0, zoneId); groupNameToGroupId.put(displayName, id); } } //convert all user memberships from other zones String userSQL = "SELECT gm.group_id, gm.member_id, g.displayName, u.identity_zone_id FROM group_membership gm, groups g, users u WHERE gm.member_type='USER' AND gm.member_id = u.id AND gm.group_id = g.id AND u.identity_zone_id <> 'uaa'"; List<Map<String, Object>> userMembers = jdbcTemplate.queryForList(userSQL); for (Map<String, Object> userRow : userMembers) { String zoneId = (String) userRow.get("identity_zone_id"); String displayName = (String) userRow.get("displayName"); String memberId = (String) userRow.get("member_id"); String oldGroupId = (String) userRow.get("group_id"); Map<String, String> groupNameToGroupId = zoneIdToGroupNameToGroupId.get(zoneId); if (groupNameToGroupId == null) { //this zone doesnt exist anymore. delete the row int count = jdbcTemplate.update("DELETE FROM group_membership WHERE group_id=? AND member_id=?", oldGroupId, memberId); if (count != 1) { logger.error("Unable to delete membership for non existent zone(group:" + oldGroupId + ", member:" + memberId + ")"); } } else { String groupId = groupNameToGroupId.get(displayName); if (StringUtils.hasText(groupId)) { int count = jdbcTemplate.update( "UPDATE group_membership SET group_id=? WHERE group_id=? AND member_id=?", groupId, oldGroupId, memberId); if (count != 1) { logger.error("Unable to update group membership for migrated zone(old group:" + oldGroupId + ", member:" + memberId + ", new group:" + groupId + ")"); } } else { logger.error( "Will not migrate (old group:" + oldGroupId + ", member:" + memberId + ", new group:" + groupId + "). Incorrectly mapped zones group? (" + displayName + ")"); } } } userMembers.clear(); }
From source file:org.gbif.portal.harvest.workflow.activity.tag.CollectorTagActivity.java
/** * @see org.gbif.portal.util.workflow.Activity#execute(org.gbif.portal.util.workflow.ProcessContext) *///ww w. ja v a2 s . c om @SuppressWarnings("unchecked") public ProcessContext execute(ProcessContext processContext) throws Exception { logger.info("Generating collect tags"); Long dataResourceId = (Long) processContext.get(contextKeyDataResourceId); if (dataResourceId == null) { throw new ContextCorruptException("No data resource id in context"); } JdbcTemplate template = new JdbcTemplate(dataSource); Connection conn = dataSource.getConnection(); //clear old tags for this resource template.update("DELETE FROM string_tag WHERE tag_id=? AND entity_id=?", new Object[] { TagIds.DATA_RESOURCE_COLLECTOR, dataResourceId }); PreparedStatement ps = conn.prepareStatement("INSERT INTO string_tag " + "(tag_id,entity_id,value,is_system_generated) " + "VALUES (?,?,?,true)"); List<Map<String, Object>> collectorNames = template.queryForList( "SELECT DISTINCT collector_name FROM raw_occurrence_record " + "WHERE data_resource_id=? AND collector_name IS NOT NULL LIMIT ?", new Object[] { dataResourceId, maxNumberOfCollectorsToRecord }); int count = 0; for (Map<String, Object> collectorNameMap : collectorNames) { try { String collectorName = (String) collectorNameMap.get("collector_name"); //escape single quotes StringBuffer oldSe = new StringBuffer("'"); StringBuffer newSe = new StringBuffer("\\'"); collectorName = collectorName.replace(oldSe, newSe); ps.setInt(1, TagIds.DATA_RESOURCE_COLLECTOR); ps.setLong(2, dataResourceId); ps.setString(3, collectorName); ps.execute(); count++; } catch (Exception e) { logger.error(e.getMessage(), e); } } ps.close(); conn.close(); logger.info("Collector tags inserted: " + count); return processContext; }
From source file:org.gbif.portal.harvest.workflow.activity.tag.CommonNameTagActivity.java
@SuppressWarnings("unchecked") public ProcessContext execute(ProcessContext processContext) throws Exception { logger.info("Generating common name tags"); Long dataResourceId = (Long) processContext.get(contextKeyDataResourceId); if (dataResourceId == null) { throw new ContextCorruptException("No data resource id in context"); }//from w w w .j av a2 s . co m JdbcTemplate template = new JdbcTemplate(dataSource); Connection conn = dataSource.getConnection(); //clear old tags for this resource template.update("DELETE from bi_relation_tag where tag_id=? and from_entity_id=?", new Object[] { TagIds.DATA_RESOURCE_COMMON_NAMES, dataResourceId }); List<Map> commonNames = template.queryForList( "SELECT DISTINCT cn.id as id FROM taxon_concept tc " + "INNER JOIN taxon_concept nc ON tc.partner_concept_id=nc.id " + "INNER JOIN common_name cn ON cn.taxon_concept_id=nc.id " + "WHERE tc.data_resource_id=?", new Object[] { dataResourceId }); int count = 0; PreparedStatement ps = conn.prepareStatement("INSERT INTO bi_relation_tag " + "(tag_id,from_entity_id,to_entity_id,is_system_generated) " + "VALUES (?,?,?,true)"); for (Map commonName : commonNames) { if (count >= maxCommonNamesToStore) break; Integer commonNameId = (Integer) commonName.get("id"); ps.setLong(1, TagIds.DATA_RESOURCE_COMMON_NAMES); ps.setLong(2, dataResourceId); ps.setLong(3, commonNameId); ps.execute(); count++; } ps.close(); conn.close(); return processContext; }
From source file:org.gbif.portal.harvest.workflow.activity.tag.PolygonTagActivity.java
/** * @see org.gbif.portal.harvest.workflow.activity.tag.BaseTagActivity#execute(org.gbif.portal.util.workflow.ProcessContext) *//* w ww. jav a 2 s .c o m*/ @SuppressWarnings("unchecked") @Override public ProcessContext execute(ProcessContext processContext) throws Exception { logger.info("Generating polygon tags"); Long dataResourceId = (Long) processContext.get(contextKeyDataResourceId); if (dataResourceId == null) { throw new ContextCorruptException("No data resource id in context"); } JdbcTemplate template = new JdbcTemplate(dataSource); Connection conn = dataSource.getConnection(); //clear old tags for this resource template.update("DELETE from string_tag where tag_id=? and entity_id=?", new Object[] { TagIds.DATA_RESOURCE_OCCURRENCES_WKT_POLYGON, dataResourceId }); List<Map> results = template.queryForList("select distinct(latitude), longitude from occurrence_record" + " where data_resource_id=? and latitude is not null", new Object[] { dataResourceId }); Coordinate[] coordinates = new Coordinate[results.size()]; int i = 0; for (Map result : results) { coordinates[i] = new Coordinate((Float) result.get("longitude"), (Float) result.get("latitude")); i++; } GeometryFactory gf = new GeometryFactory(); ConvexHull convexHull = new ConvexHull(coordinates, gf); Geometry g = convexHull.getConvexHull(); Coordinate[] coords = g.getCoordinates(); for (Coordinate coord : coords) { coord.x = (Math.floor(coord.x * 100)) / 100; coord.y = (Math.floor(coord.y * 100)) / 100; } String polygon = g.toString(); if (polygon.startsWith(WKT_POLYGON_PREFIX)) { PreparedStatement ps = conn.prepareStatement("INSERT INTO string_tag " + "(tag_id,entity_id,value,is_system_generated) " + "VALUES (?, ?, ?, true)"); //escape single quotes ps.setLong(1, TagIds.DATA_RESOURCE_OCCURRENCES_WKT_POLYGON); ps.setLong(2, dataResourceId); ps.setString(3, polygon); ps.execute(); ps.close(); } conn.close(); return processContext; }
From source file:org.gbif.portal.harvest.workflow.activity.tag.TaxonomicCoverageTagActivity.java
/** * @see launcher.DataResourceTagLoader#process(javax.sql.DataSource, org.springframework.jdbc.core.JdbcTemplate, java.lang.Integer) */// ww w . j ava2s . c om @SuppressWarnings("unchecked") public ProcessContext execute(ProcessContext processContext) throws Exception { logger.info("Generating Taxonomic scope and associated kingdom tags"); Long dataResourceId = (Long) processContext.get(contextKeyDataResourceId); if (dataResourceId == null) { throw new ContextCorruptException("No data resource id in context"); } JdbcTemplate template = new JdbcTemplate(dataSource); Connection conn = dataSource.getConnection(); //clear old tags for this resource template.update("DELETE from string_tag where (tag_id=? or tag_id=?) and entity_id=?", new Object[] { TagIds.DATA_RESOURCE_TAXONOMIC_SCOPE, TagIds.DATA_RESOURCE_ASSOCIATED_KINGDOM, dataResourceId }); //determine root taxa List<Map> rootTaxaResults = template.queryForList( "SELECT tc.id id,tc.partner_concept_id as partner_concept_id,tc.rank, " + "nc.kingdom_concept_id as kingdom_concept_id " + "FROM taxon_concept tc " + "LEFT OUTER JOIN taxon_concept nc ON tc.partner_concept_id = nc.id " + "WHERE tc.parent_concept_id IS NULL AND tc.data_resource_id=? ORDER BY tc.rank", new Object[] { dataResourceId }); boolean tagsInserted = false; int taxonomicScopeTagsAdded = 0; int MAX_TAGS = 10; PreparedStatement ps = conn.prepareStatement("INSERT INTO bi_relation_tag " + "(tag_id,from_entity_id,to_entity_id,is_system_generated) " + "VALUES (?, ?, ?,true)"); //for each kingdom, go down each root until there is more than one child for (Map rootTaxa : rootTaxaResults) { Long taxonConceptId = (Long) rootTaxa.get("id"); Long nubConceptId = (Long) rootTaxa.get("partner_concept_id"); //determine root taxa List<Map> childResults = template.queryForList( "SELECT id,partner_concept_id, rank FROM taxon_concept tc " + "WHERE tc.parent_concept_id is null AND data_resource_id=?", new Object[] { dataResourceId }); //while there is only one child, iterate down while (childResults.size() == 1) { taxonConceptId = (Long) childResults.get(0).get("id"); nubConceptId = (Long) childResults.get(0).get("partner_concept_id"); childResults = template.queryForList("SELECT tc.id, tc.partner_concept_id FROM taxon_concept tc " + "WHERE tc.parent_concept_id = ?", new Object[] { taxonConceptId }); } if (nubConceptId != null) { //we have found the root taxon for this branch - create root taxon tag ps.setLong(1, TagIds.DATA_RESOURCE_TAXONOMIC_SCOPE); ps.setLong(2, dataResourceId); ps.setLong(3, nubConceptId); ps.execute(); tagsInserted = true; taxonomicScopeTagsAdded++; if (taxonomicScopeTagsAdded > MAX_TAGS) break; } } ps.close(); //if nothing inserted, insert tags for the first few concepts in root list if (!tagsInserted) { ps = conn.prepareStatement("INSERT INTO bi_relation_tag " + "(tag_id,from_entity_id,to_entity_id,is_system_generated) " + "VALUES (?,?,?,true)"); int count = 0; for (Map rootTaxa : rootTaxaResults) { if (count >= maxConceptsToStore) break; Long nubConceptId = (Long) rootTaxa.get("partner_concept_id"); if (nubConceptId != null) { ps.setLong(1, TagIds.DATA_RESOURCE_TAXONOMIC_SCOPE); ps.setLong(2, dataResourceId); ps.setLong(3, nubConceptId); ps.execute(); count++; } } ps.close(); } //set kingdom coverage ps = conn.prepareStatement("INSERT INTO bi_relation_tag " + "(tag_id,from_entity_id,to_entity_id,is_system_generated) " + "VALUES (?,?,?,true)"); List<Long> addedKingdoms = new ArrayList<Long>(); for (Map rootTaxa : rootTaxaResults) { Long nubConceptId = (Long) rootTaxa.get("kingdom_concept_id"); if (nubConceptId != null && !addedKingdoms.contains(nubConceptId)) { ps.setLong(1, TagIds.DATA_RESOURCE_ASSOCIATED_KINGDOM); ps.setLong(2, dataResourceId); ps.setLong(3, nubConceptId); ps.execute(); addedKingdoms.add(nubConceptId); } } ps.close(); conn.close(); return processContext; }
From source file:org.opendatakit.common.persistence.engine.mysql.DatastoreImpl.java
@Override public void afterPropertiesSet() throws Exception { if (dataSource == null) { throw new IllegalStateException("dataSource property must be set!"); }/*from w ww . j a va 2 s . com*/ if (schemaName == null) { JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource); List<?> databaseNames = jdbcTemplate.queryForList("SELECT DATABASE()", String.class); schemaName = (String) databaseNames.get(0); } }
From source file:org.springframework.jdbc.core.JdbcTemplateQueryTests.java
public void testQueryForListWithIntegerElement() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID < 3"; mockResultSetMetaData.getColumnCount(); ctrlResultSetMetaData.setReturnValue(1); mockResultSet.getMetaData();/*from w ww .ja v a 2 s . c o m*/ ctrlResultSet.setReturnValue(mockResultSetMetaData); mockResultSet.next(); ctrlResultSet.setReturnValue(true); mockResultSet.getInt(1); ctrlResultSet.setReturnValue(11); mockResultSet.wasNull(); ctrlResultSet.setReturnValue(false); mockResultSet.next(); ctrlResultSet.setReturnValue(false); mockResultSet.close(); ctrlResultSet.setVoidCallable(); mockStatement.executeQuery(sql); ctrlStatement.setReturnValue(mockResultSet); if (debugEnabled) { mockStatement.getWarnings(); ctrlStatement.setReturnValue(null); } mockStatement.close(); ctrlStatement.setVoidCallable(); mockConnection.createStatement(); ctrlConnection.setReturnValue(mockStatement); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource); List li = template.queryForList(sql, Integer.class); assertEquals("All rows returned", 1, li.size()); assertEquals("Element is Integer", 11, ((Integer) li.get(0)).intValue()); }
From source file:org.springframework.jdbc.core.JdbcTemplateQueryTests.java
private void doTestQueryForListWithArgs(String sql) throws Exception { mockResultSetMetaData.getColumnCount(); ctrlResultSetMetaData.setReturnValue(1, 2); mockResultSetMetaData.getColumnLabel(1); ctrlResultSetMetaData.setReturnValue("age", 2); mockResultSet.getMetaData();/*from w w w . ja v a 2s . c o m*/ ctrlResultSet.setReturnValue(mockResultSetMetaData, 2); mockResultSet.next(); ctrlResultSet.setReturnValue(true); mockResultSet.getObject(1); ctrlResultSet.setReturnValue(new Integer(11)); mockResultSet.next(); ctrlResultSet.setReturnValue(true); mockResultSet.getObject(1); ctrlResultSet.setReturnValue(new Integer(12)); mockResultSet.next(); ctrlResultSet.setReturnValue(false); mockResultSet.close(); ctrlResultSet.setVoidCallable(); mockPreparedStatement.setObject(1, new Integer(3)); ctrlPreparedStatement.setVoidCallable(); mockPreparedStatement.executeQuery(); ctrlPreparedStatement.setReturnValue(mockResultSet); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(sql); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource); List li = template.queryForList(sql, new Object[] { new Integer(3) }); assertEquals("All rows returned", 2, li.size()); assertEquals("First row is Integer", 11, ((Integer) ((Map) li.get(0)).get("age")).intValue()); assertEquals("Second row is Integer", 12, ((Integer) ((Map) li.get(1)).get("age")).intValue()); }
From source file:org.springframework.jdbc.core.JdbcTemplateQueryTests.java
public void testQueryForListWithArgsAndEmptyResult() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID < ?"; ctrlResultSet = MockControl.createControl(ResultSet.class); mockResultSet = (ResultSet) ctrlResultSet.getMock(); mockResultSet.next();/*from ww w. j a va 2 s . co m*/ ctrlResultSet.setReturnValue(false); mockResultSet.close(); ctrlResultSet.setVoidCallable(); mockPreparedStatement.setObject(1, new Integer(3)); ctrlPreparedStatement.setVoidCallable(); mockPreparedStatement.executeQuery(); ctrlPreparedStatement.setReturnValue(mockResultSet); if (debugEnabled) { mockPreparedStatement.getWarnings(); ctrlPreparedStatement.setReturnValue(null); } mockPreparedStatement.close(); ctrlPreparedStatement.setVoidCallable(); mockConnection.prepareStatement(sql); ctrlConnection.setReturnValue(mockPreparedStatement); replay(); JdbcTemplate template = new JdbcTemplate(mockDataSource); List li = template.queryForList(sql, new Object[] { new Integer(3) }); assertEquals("All rows returned", 0, li.size()); }