List of usage examples for org.springframework.jdbc.core ResultSetExtractor ResultSetExtractor
ResultSetExtractor
From source file:org.LexGrid.lexevs.metabrowser.impl.MetaBrowserServiceImpl.java
/** * Builds the by source tab results.//ww w .j a v a 2s. c o m * * @param rs the rs * @param map the map * @param direction the direction * * @return the map< string, list< by source tab results>> * * @throws SQLException the SQL exception */ @SuppressWarnings({ "unchecked" }) private Map<String, List<BySourceTabResults>> buildBySourceTabResults(String sql, String cui, final Map<String, List<BySourceTabResults>> map, final Direction direction) throws SQLException { return (Map<String, List<BySourceTabResults>>) this.getJdbcTemplate().query(sql, new String[] { cui }, new ResultSetExtractor() { public Object extractData(ResultSet rs) throws SQLException, DataAccessException { while (rs.next()) { BySourceTabResults result = new BySourceTabResults(); String rel = rs.getString(REL_COL); if (direction.equals(Direction.TARGETOF)) { rel = reverseRel(rel); } String targetConceptCode = null; if (direction.equals(Direction.TARGETOF)) { targetConceptCode = rs.getString("sourceEntityCode"); } else { targetConceptCode = rs.getString("targetEntityCode"); } String termText = rs.getString(SQLTableConstants.TBLCOL_PROPERTYVALUE); String sourceQualValue = rs.getString(SOURCE_QUAL_COL); String repForm = rs.getString(SQLTableConstants.TBLCOL_REPRESENTATIONALFORM); String sourceCode = rs.getString(SOURCE_CODE_QUAL_COL); String relaQualValue = rs.getString(RELA_QUAL_COL); if (direction.equals(Direction.TARGETOF)) { relaQualValue = reverseRela(relaQualValue); } result.setCui(targetConceptCode); result.setTerm(termText); result.setRel(rel); result.setSource(sourceQualValue); result.setRela(relaQualValue); result.setType(repForm); result.setCode(sourceCode); map.get(rel).add(result); } return map; } }); }
From source file:nz.geek.caffe.spring.hdb.HANAExceptionMappingTest.java
/** */// w ww .j av a 2 s .c o m @Test public void testInvalidResultSetColumnRowStore2() { this.jdbcTemplate.execute("INSERT INTO TEST_PARENT_ROW VALUES (1, 'test')"); try { this.jdbcTemplate.query(new PreparedStatementCreator() { public PreparedStatement createPreparedStatement(Connection con) throws SQLException { return con.prepareStatement("SELECT STR_ FROM TEST_PARENT_ROW"); } }, new ResultSetExtractor<String>() { public String extractData(final ResultSet rs) throws SQLException, DataAccessException { return rs.getString(7); } }); Assert.fail("query should have failed"); } catch (final InvalidResultSetAccessException e) { // expected } }
From source file:com.hygenics.parser.getDAOTemplate.java
/** * Returns a Json Based Extractor/* w w w .j av a 2 s . co m*/ * * @return */ public ResultSetExtractor<ArrayList<String>> getJsonExtractor() { return (new ResultSetExtractor<ArrayList<String>>() { @Override public ArrayList<String> extractData(ResultSet rs) throws SQLException, DataAccessException { // TODO Auto-generated method stub int j = 0; ArrayList<String> results = new ArrayList<String>(); JsonObject obj = null; int columns = 0; while (rs.next()) { if (columns == 0) { columns = rs.getMetaData().getColumnCount(); if (columns == 0) { return results; } } obj = null; obj = new JsonObject(); for (int i = 1; i <= columns; i++) { if (rs.getString(i) != null) { obj.add(rs.getMetaData().getColumnName(i), rs.getString(i).replaceAll("\t|\r|\n|\r\n|", "")); } else { obj.add(rs.getMetaData().getColumnName(i), ""); } } if (obj != null) { results.add(obj.toString()); } j++; } if (rs.getFetchSize() <= j) { rs.close(); } return results; } }); }
From source file:org.ohmage.query.impl.ClassQueries.java
@Override public QueryResultsList<Clazz> getClassesInformation(final String username, final Collection<String> classIds, final Collection<String> classNameTokens, final Collection<String> classDescriptionTokens, final Clazz.Role role) throws DataAccessException { // Build the default part of the query. StringBuilder sqlBuilder = new StringBuilder( // This is the outer part of the query that will allow // us to attach the user's role on the resulting // classes. "SELECT r.urn, r.name, r.description, ucr.role " + "FROM (" + // Get the basic information about the class as // well as the requesting user's ID and the class' // ID. "SELECT u.id AS user_id, c.id AS class_id, " + "c.urn, c.name, c.description " + "FROM class c, user u " + "WHERE u.username = ? " + // ACL. "AND (" + "(u.admin = true)" + " OR " + "(" + "c.id IN (" + "SELECT uc.class_id " + "FROM user_class uc " + "WHERE u.id = uc.user_id" + ")" + ")" + ")"); // Add the requesting user's username to the parameters as this is // always required for the ACL. List<Object> parameters = new LinkedList<Object>(); parameters.add(username);/*from w ww . j a va 2 s .c o m*/ // If we are going to intentionally limit the results to only a set of // classes, then do so here. if (classIds != null) { if (classIds.size() == 0) { return (new QueryResultListBuilder<Clazz>()).getQueryResult(); } sqlBuilder.append(" AND c.urn IN " + StringUtils.generateStatementPList(classIds.size())); parameters.addAll(classIds); } // If there are name search tokens, add them as a group of ORs. if (classNameTokens != null) { if (classNameTokens.size() == 0) { return (new QueryResultListBuilder<Clazz>()).getQueryResult(); } boolean firstPass = true; sqlBuilder.append(" AND ("); for (String nameToken : classNameTokens) { if (firstPass) { firstPass = false; } else { sqlBuilder.append(" OR "); } sqlBuilder.append("c.name LIKE ?"); parameters.add('%' + nameToken + '%'); } sqlBuilder.append(")"); } // If there are description search tokens, add them as a group of ORs. if (classDescriptionTokens != null) { if (classDescriptionTokens.size() == 0) { return (new QueryResultListBuilder<Clazz>()).getQueryResult(); } boolean firstPass = true; sqlBuilder.append(" AND ("); for (String descriptionToken : classDescriptionTokens) { if (firstPass) { firstPass = false; } else { sqlBuilder.append(" OR "); } sqlBuilder.append("c.description LIKE ?"); parameters.add('%' + descriptionToken + '%'); } sqlBuilder.append(")"); } // Finally, tack on the JOIN that will give us the user's role or limit // the results by the given role if one was given. sqlBuilder .append(") AS r " + "LEFT JOIN (user_class uc, user_class_role ucr) " + "ON r.user_id = uc.user_id " + "AND r.class_id = uc.class_id " + "AND ucr.id = uc.user_class_role_id"); if (role != null) { sqlBuilder.append(" WHERE ucr.role = ?"); parameters.add(role.toString()); } try { return getJdbcTemplate().query(sqlBuilder.toString(), parameters.toArray(), new ResultSetExtractor<QueryResultsList<Clazz>>() { /** * Creates class objects for each of the classes that * match the results. */ @Override public QueryResultsList<Clazz> extractData(final ResultSet rs) throws SQLException, org.springframework.dao.DataAccessException { QueryResultListBuilder<Clazz> resultBuilder = new QueryResultListBuilder<Clazz>(); try { while (rs.next()) { Clazz.Role role = null; String roleString = rs.getString("role"); if (roleString != null) { try { role = Clazz.Role.getValue(roleString); } catch (IllegalArgumentException e) { throw new SQLException("The class role is unknown: " + roleString, e); } } resultBuilder.addResult(new Clazz(rs.getString("urn"), rs.getString("name"), rs.getString("description"), role)); } return resultBuilder.getQueryResult(); } catch (DomainException e) { throw new SQLException(e); } } }); } catch (org.springframework.dao.DataAccessException e) { throw new DataAccessException( "Error executing SQL '" + sqlBuilder.toString() + "' with parameters: " + parameters, e); } }
From source file:org.ohmage.query.impl.SurveyResponseQueries.java
@Override public int retrieveSurveyResponses(final Campaign campaign, final String username, final Set<UUID> surveyResponseIds, final Collection<String> usernames, final DateTime startDate, final DateTime endDate, final SurveyResponse.PrivacyState privacyState, final Collection<String> surveyIds, final Collection<String> promptIds, final String promptType, final Set<String> promptResponseSearchTokens, final Collection<ColumnKey> columns, final List<SortParameter> sortOrder, final long surveyResponsesToSkip, final long surveyResponsesToProcess, final List<SurveyResponse> result) throws DataAccessException { if (((surveyIds != null) && (surveyIds.size() == 0)) || ((promptIds != null) && (promptIds.size() == 0)) || ((columns != null) && (columns.size() == 0))) { return 0; }// w w w . j a va2s. co m List<Object> parameters = new LinkedList<Object>(); String sql = buildSqlAndParameters(campaign, username, surveyResponseIds, usernames, startDate, endDate, privacyState, surveyIds, promptIds, promptType, promptResponseSearchTokens, columns, sortOrder, parameters); // This is necessary to map tiny integers in SQL to Java's integer. final Map<String, Class<?>> typeMapping = new HashMap<String, Class<?>>(); typeMapping.put("tinyint", Integer.class); // This is a silly, hacky way to get the total count, but it is the // only real way I have found thus far. final Collection<Integer> totalCount = new ArrayList<Integer>(1); try { result.addAll(getJdbcTemplate().query(sql, parameters.toArray(), new ResultSetExtractor<List<SurveyResponse>>() { /** * First, it skips a set of rows based on the parameterized * number of survey responses to skip. Then, it aggregates * the information from the number of desired survey * responses. * * There must be some ordering on the results in order for * subsequent results to skip / process the same rows. The * agreed upon ordering is by time taken time stamp. * Therefore, if a user were viewing results as they were * being generated and/or uploaded, it could be that * subsequent calls return the same result as a previous * call. This is analogous to viewing a page of feed data * and going to the next page and seeing some feed items * that you just saw on the previous page. It was decided * that this is a common and acceptable way to view live * data. */ @Override public List<SurveyResponse> extractData(ResultSet rs) throws SQLException, org.springframework.dao.DataAccessException { // If the result set is empty, we can simply return an // empty list. if (!rs.next()) { totalCount.add(0); return Collections.emptyList(); } // Keep track of the number of survey responses we have // skipped. int surveyResponsesSkipped = 0; // Continue while there are more survey responses to // skip. while (surveyResponsesSkipped < surveyResponsesToSkip) { // Get the ID for the survey response we are // skipping. String surveyResponseId = rs.getString("uuid"); surveyResponsesSkipped++; // Continue to skip rows as long as there are rows // to skip and those rows have the same survey // response ID. while (surveyResponseId.equals(rs.getString("uuid"))) { // We were skipping the last survey response, // therefore, there are no survey responses to // return and we can return an empty list. if (!rs.next()) { totalCount.add(surveyResponsesSkipped); return Collections.emptyList(); } } } // Create a list of the results. List<SurveyResponse> result = new LinkedList<SurveyResponse>(); // Cycle through the rows until the maximum number of // rows has been processed or there are no more rows to // process. int surveyResponsesProcessed = 0; while (surveyResponsesProcessed < surveyResponsesToProcess) { // We have not yet processed this survey response, // so we need to process it and then continue // processing this and all of its survey responses. // First, create the survey response object. SurveyResponse surveyResponse; try { JSONObject locationJson = null; String locationString = rs.getString("location"); if (locationString != null) { locationJson = new JSONObject(locationString); } surveyResponse = new SurveyResponse( campaign.getSurveys().get(rs.getString("survey_id")), UUID.fromString(rs.getString("uuid")), rs.getString("username"), rs.getString("urn"), rs.getString("client"), rs.getLong("epoch_millis"), DateTimeUtils.getDateTimeZoneFromString(rs.getString("phone_timezone")), new JSONObject(rs.getString("launch_context")), rs.getString("location_status"), locationJson, SurveyResponse.PrivacyState.getValue(rs.getString("privacy_state"))); if (columns != null) { surveyResponse.setCount(rs.getLong("count")); } } catch (IllegalArgumentException e) { throw new SQLException("The TimeZone is unknown.", e); } catch (JSONException e) { throw new SQLException("Error creating a JSONObject.", e); } catch (DomainException e) { throw new SQLException("Error creating the survey response information object.", e); } // Add the current survey response to the result // list and increase the number of survey responses // processed. result.add(surveyResponse); surveyResponsesProcessed++; // Get a string representation of the survey // response's unique identifier. String surveyResponseId = surveyResponse.getSurveyResponseId().toString(); boolean processPrompts = true; try { rs.getString("prompt_id"); } catch (SQLException e) { processPrompts = false; } if (processPrompts) { // Now, process this prompt response and all // subsequent prompt responses. do { try { // Retrieve the corresponding prompt // information from the campaign. Prompt prompt = campaign.getPrompt(surveyResponse.getSurvey().getId(), rs.getString("prompt_id")); // Generate the prompt response and add it to // the survey response. surveyResponse.addPromptResponse(prompt.createResponse( (Integer) rs.getObject("repeatable_set_iteration", typeMapping), rs.getObject("response"))); } catch (DomainException e) { throw new SQLException( "The prompt response value from the database is not a valid response value for this prompt.", e); } } while ( // Get the next prompt response unless we // just read the last prompt response in // the result, rs.next() && // and continue as long as that prompt // response pertains to this survey // response. surveyResponseId.equals(rs.getString("uuid"))); } else { rs.next(); } // If we exited the loop because we passed the last // record, break out of the survey response // processing loop. if (rs.isAfterLast()) { break; } } // Now, if we are after the last row, we need to set // the total count to be the total number skipped plus // the total number processed. if (rs.isAfterLast()) { totalCount.add(surveyResponsesSkipped + surveyResponsesProcessed); } else { int otherIds = 1; String id = rs.getString("uuid"); while (rs.next()) { if (!rs.getString("uuid").equals(id)) { otherIds++; id = rs.getString("uuid"); } } totalCount.add(surveyResponsesSkipped + surveyResponsesProcessed + otherIds); } // Finally, return only the survey responses as a list. return result; } })); return totalCount.iterator().next(); } catch (org.springframework.dao.DataAccessException e) { StringBuilder errorBuilder = new StringBuilder("Error executing SQL '" + sql + "' with parameters: "); boolean firstPass = true; for (Object parameter : parameters) { if (firstPass) { firstPass = false; } else { errorBuilder.append(", "); } errorBuilder.append(parameter.toString()); } throw new DataAccessException(errorBuilder.toString(), e); } }
From source file:com.ineunet.knife.persist.Jdbc.java
/** * @param sql/* w ww. j a v a 2s . c om*/ * @param args * @return string or <code>null</code> * @throws PersistException * if result size > 1 */ public String queryString(String sql, Object... args) { return jdbcTemplate.query(sql, args, new ResultSetExtractor<String>() { @Override public String extractData(ResultSet rs) throws SQLException, DataAccessException { if (rs.next()) { String s = rs.getString(1); if (rs.next()) throw new PersistException("Expected 1, actual many."); return s; } return null; } }); }
From source file:net.sf.infrared.collector.impl.persistence.ApplicationStatisticsDaoImpl.java
AggregateOperationTree fetchAggregateOperationTree(Collection appNames, Collection instanceIds, Date fromDate,
Date toDate) {// ww w . j a v a 2 s . c o m
Object[] stringAndArray = getWhereStringAndArgArray(appNames, instanceIds, fromDate, toDate);
String whereClause = (String) stringAndArray[0];
Object[] argArray = (Object[]) stringAndArray[1];
// making use of a spring ResultSetExtractor implementation to get the merged tree.
AggregateOperationTree aggOpTree = (AggregateOperationTree) getJdbcTemplate()
.query(SQL_FETCH_TREE + whereClause, argArray, new ResultSetExtractor() {
public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
AggregateOperationTree newTree = null;
AggregateOperationTree aggOpTree = new AggregateOperationTree();
while (rs.next()) {
try {
InputStream is = rs.getBinaryStream("TREE");
ObjectInputStream ois = new ObjectInputStream(is);
Tree tree = (Tree) ois.readObject();
newTree = new AggregateOperationTree();
newTree.setAggregateTree(tree);
aggOpTree.merge(newTree);
} catch (IOException e) {
e.printStackTrace();
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
}
return aggOpTree;
}
});
return aggOpTree;
}
From source file:org.ohmage.query.impl.ClassQueries.java
@Override public Map<String, Clazz.Role> getUserRolePairs(final String username, final String classId) throws DataAccessException { String sql = "SELECT u.username, " + "CASE WHEN (" + "SELECT EXISTS (" + "SELECT ru.id " + "FROM user ru, " + "user_class ruc, user_class_role rucr " + "WHERE ru.username = ? " + "AND (" + "(ru.admin = true)" + " OR " + "(c.id = ruc.class_id " + "AND ru.id = ruc.user_id " + "AND rucr.id = ruc.user_class_role_id " + "AND rucr.role = '" + Clazz.Role.PRIVILEGED.toString().toLowerCase() + "'" + ")" + ")" + ")" + ") " + "THEN ucr.role " + "ELSE NULL " + "END " + "AS role " + "FROM user u, user_class uc, user_class_role ucr, class c " + "WHERE c.urn = ? " + "AND c.id = uc.class_id " + "AND u.id = uc.user_id " + "AND ucr.id = uc.user_class_role_id"; try {// w w w .j ava 2 s . c o m return getJdbcTemplate().query(sql, new Object[] { username, classId }, new ResultSetExtractor<Map<String, Clazz.Role>>() { @Override public Map<String, Role> extractData(final ResultSet rs) throws SQLException, org.springframework.dao.DataAccessException { Map<String, Clazz.Role> result = new HashMap<String, Clazz.Role>(); while (rs.next()) { String role = rs.getString("role"); if (role == null) { result.put(rs.getString("username"), null); } else { try { result.put(rs.getString("username"), Clazz.Role.getValue(role)); } catch (IllegalArgumentException e) { throw new SQLException("The role is unknown.", e); } } } return result; } }); } catch (org.springframework.dao.DataAccessException e) { throw new DataAccessException( "Error executing SQL_EXISTS_CLASS '" + sql + "' with parameter: " + classId, e); } }
From source file:com.ineunet.knife.persist.Jdbc.java
public boolean existsTable(final String tableName, JdbcTemplate jdbcTemplate) { return jdbcTemplate.query("show tables", new ResultSetExtractor<Boolean>() { @Override/* w w w .j ava 2s . co m*/ public Boolean extractData(ResultSet rs) throws SQLException, DataAccessException { while (rs.next()) { String tableNameOther = rs.getString(1); if (tableName.equalsIgnoreCase(tableNameOther)) return true; } return false; } }); }
From source file:org.ohmage.query.impl.UserCampaignQueries.java
@Override public List<CampaignMask> getCampaignMasks(final MaskId maskId, final DateTime startDate, final DateTime endDate, final String assignerUserId, final String assigneeUserId, final String campaignId) throws DataAccessException { // Build the default SQL. StringBuilder sqlBuilder = new StringBuilder("SELECT " + "cm.mask_id AS mask_id, " + "cm.creation_time AS creation_time, " + "assigner_user.username AS assigner_user_id, " + "assignee_user.username AS assignee_user_id, " + "c.urn AS campaign_id, " + "cmpi.survey_id AS survey_id, " + "cmpi.prompt_id AS prompt_id " + "FROM " + "user assigner_user, " + "user assignee_user, " + "campaign c, " + "campaign_mask cm, " + "campaign_mask_survey_prompt_map cmpi " + "WHERE assigner_user.id = cm.assigner_user_id " + "AND assignee_user.id = cm.assignee_user_id " + "AND c.id = cm.campaign_id " + "AND cm.id = cmpi.campaign_mask_id"); // Build the list of required parameters. List<Object> parameters = new LinkedList<Object>(); // If the mask is given, add it to the SQL and the parameters list. if (maskId != null) { sqlBuilder.append(" AND cm.id = ?"); parameters.add(maskId.toString()); }/*w w w . j av a 2s . c o m*/ // If the start date is given, add it to the SQL and the parameters // list. if (startDate != null) { sqlBuilder.append(" AND cm.creation_time >= ?"); parameters.add(startDate.getMillis()); } // If the end date is given, add it to the SQL and the parameters. if (endDate != null) { sqlBuilder.append(" AND cm.creation_time <= ?"); parameters.add(endDate.getMillis()); } // If the assigner's user ID was given, add it to the SQL and the // parameters. if (assignerUserId != null) { sqlBuilder.append(" AND assigner_user.username = ?"); parameters.add(assignerUserId); } // If the assignee's user ID was given, add it to the SQL and the // parameters. if (assigneeUserId != null) { sqlBuilder.append(" AND assignee_user.username = ?"); parameters.add(assigneeUserId); } // If the campaign's ID was given, add it to the SQL and the // parameters. if (campaignId != null) { sqlBuilder.append(" AND c.urn = ?"); parameters.add(campaignId); } // Make the query and return the results. try { return getJdbcTemplate().query(sqlBuilder.toString(), parameters.toArray(), new ResultSetExtractor<List<CampaignMask>>() { /* * (non-Javadoc) * @see org.springframework.jdbc.core.ResultSetExtractor#extractData(java.sql.ResultSet) */ @Override public List<CampaignMask> extractData(final ResultSet resultSet) throws SQLException, org.springframework.dao.DataAccessException { // Create a map of mask IDs to their builders. Map<MaskId, CampaignMask.Builder> builders = new HashMap<MaskId, CampaignMask.Builder>(); // For each of the results, create a new builder if // the mask has never been seen before or just // append to the list of survey IDs if it has been // seen before. while (resultSet.next()) { MaskId maskId; try { maskId = MaskId.decodeString(resultSet.getString("mask_id")); } catch (DomainException e) { throw new SQLException("Error decoding the mask ID.", e); } // Attempt to get the builder. CampaignMask.Builder builder = builders.get(maskId); // If the builder doesn't exist, create it. if (builder == null) { // Create the builder. builder = new CampaignMask.Builder(); // Add the builder to the map. builders.put(maskId, builder); // Set the mask ID. builder.setMaskId(maskId); // Get the creation time and set it. DateTime creationTime = new DateTime(resultSet.getLong("creation_time")); builder.setCreationTime(creationTime); // Get the assigner's user ID and set it. String assignerUserId = resultSet.getString("assigner_user_id"); builder.setAssignerUserId(assignerUserId); // Get the assignee's user ID and set it. String assigneeUserId = resultSet.getString("assignee_user_id"); builder.setAssigneeUserId(assigneeUserId); // Get the campaign's ID and set it. String campaignId = resultSet.getString("campaign_id"); builder.setCampaignId(campaignId); // Set the default set of survey IDs to be // empty. builder.setPromptIds(new HashMap<String, Set<String>>()); } // Add the survey ID. builder.addPromptId(resultSet.getString("survey_id"), resultSet.getString("prompt_id")); } // Cycle through the builders, build them, and add // them to the result list. List<CampaignMask> result = new ArrayList<CampaignMask>(builders.size()); for (CampaignMask.Builder builder : builders.values()) { try { result.add(builder.build()); } catch (DomainException e) { throw new SQLException("There was a problem building the campaign mask.", e); } } // Return the result. return result; } }); } catch (org.springframework.dao.DataAccessException e) { throw new DataAccessException( "Error executing SQL '" + sqlBuilder.toString() + "' with parameters: " + parameters.toString(), e); } }