List of usage examples for org.springframework.jdbc.core ResultSetExtractor ResultSetExtractor
ResultSetExtractor
From source file:org.ohmage.query.impl.UserMobilityQueries.java
@Override public Set<DateTime> getDates(final DateTime startDate, final DateTime endDate, final String username) throws DataAccessException { List<Object> parameters = new ArrayList<Object>(3); parameters.add(username);//w w w .j a v a 2 s.com parameters.add(startDate.getMillis()); parameters.add(endDate.getMillis()); try { return getJdbcTemplate().query( SQL_GET_MIN_MAX_MILLIS_FOR_USER_WITHIN_RANGE_GROUPED_BY_TIME_AND_TIMEZONE, parameters.toArray(), new ResultSetExtractor<Set<DateTime>>() { /** * Gathers the applicable dates based on their time * zone. */ @Override public Set<DateTime> extractData(ResultSet rs) throws SQLException, org.springframework.dao.DataAccessException { Set<DateTime> result = new HashSet<DateTime>(); while (rs.next()) { DateTimeZone timeZone = DateTimeZone.forID(rs.getString("time_zone")); result.add(new DateTime(rs.getLong("time"), timeZone)); } return result; } }); } catch (org.springframework.dao.DataAccessException e) { throw new DataAccessException("Error executing SQL '" + SQL_GET_MIN_MAX_MILLIS_FOR_USER_WITHIN_RANGE_GROUPED_BY_TIME_AND_TIMEZONE + "' with parameters: " + parameters, e); } }
From source file:com.hygenics.parser.getDAOTemplate.java
/** * Column Extractor//from ww w . j a v a 2 s . com * * @return */ public ResultSetExtractor<String> getColumnExtractor(final String column) { return (new ResultSetExtractor<String>() { @Override public String extractData(ResultSet rs) throws SQLException, DataAccessException { String result = null; if (rs.next()) { result = rs.getString((column.trim())); } rs.close(); return result; } }); }
From source file:in.sc.dao.ProductHelper.java
public HashMap getProductFeatures(HashMap pMap) { StringBuilder sql = new StringBuilder(); sql.append(" select * from " + pMap.get(featureTable) + " where product_id= :" + product_id); namedParameterJdbcTemplate = getTemplate(); HashMap featureMap = namedParameterJdbcTemplate.query(sql.toString(), pMap, new ResultSetExtractor<HashMap>() { @Override//from ww w . j a va2 s . c o m public HashMap extractData(ResultSet rs) throws SQLException, DataAccessException { HashMap dataMap = new HashMap(); ResultSetMetaData rsmd = rs.getMetaData(); while (rs.next()) { for (int i = 1; i < rsmd.getColumnCount(); i++) { if (rsmd.getColumnName(i).contains("f") && !rsmd.getColumnName(i).contains("pf_id")) { dataMap.put(rsmd.getColumnName(i), rs.getString(i)); } } } return dataMap; } }); return featureMap; }
From source file:in.sc.dao.ProductHelper.java
public HashMap getCatPatterns(HashMap pMap) { StringBuilder sql = new StringBuilder(); sql.append(//from w w w .j a v a 2 s .c om " select cat_name,category_id,productPattern,brandPattern,homePattern,catpattern from linkgenerator"); namedParameterJdbcTemplate = getTemplate(); HashMap featureMap = namedParameterJdbcTemplate.query(sql.toString(), pMap, new ResultSetExtractor<HashMap>() { @Override public HashMap extractData(ResultSet rs) throws SQLException, DataAccessException { HashMap dataMap = new HashMap(); LinkedList li = null; while (rs.next()) { li = new LinkedList(); li.add(rs.getInt("category_id")); li.add(rs.getString("homePattern")); li.add(rs.getString("productPattern")); li.add(rs.getString("brandPattern")); li.add(rs.getString("catPattern")); dataMap.put(rs.getString("cat_name"), li); } return dataMap; } }); return featureMap; }
From source file:annis.administration.DefaultAdministrationDao.java
@Override @Transactional(readOnly = true)/*from w ww . j a v a2 s . c om*/ public AnnisUserConfig retrieveUserConfig(final String userName) { String sql = "SELECT * FROM user_config WHERE id=?"; AnnisUserConfig config = jdbcTemplate.query(sql, new Object[] { userName }, new ResultSetExtractor<AnnisUserConfig>() { @Override public AnnisUserConfig extractData(ResultSet rs) throws SQLException, DataAccessException { // default to empty config AnnisUserConfig c = new AnnisUserConfig(); c.setName(userName); if (rs.next()) { try { c = jsonMapper.readValue(rs.getString("config"), AnnisUserConfig.class); } catch (IOException ex) { log.error("Could not parse JSON that is stored in database (user configuration)", ex); } } return c; } }); return config; }
From source file:in.sc.dao.ProductHelper.java
public LinkedHashMap selectBrand(int cat_id) { StringBuilder sql = new StringBuilder(); LinkedHashMap bMap = new LinkedHashMap(); try {//from w w w .j ava 2 s . co m sql.append( " SELECT distinct b.BRAND_ID,B_NAME,b.B_DESC,B_RATING,B_POPULARITY,B_UNIQUE_NAME,B_IMG,b.STATUS,li.brand_url_Exp,li.cat_name,cb.brand_rating FROM brand_details b,product_transaction pt,linkgenerator li,cat_brand_map cb " + " where b.brand_id=pt.brand_id and cb.cat_id =pt.category_id and cb.brand_id=b.brand_id and li.category_id=pt.category_id and pt.category_id=" + cat_id + " order by cb.brand_rating desc "); namedParameterJdbcTemplate = getTemplate(); bMap = namedParameterJdbcTemplate.query(sql.toString(), new ResultSetExtractor<LinkedHashMap>() { @Override public LinkedHashMap extractData(ResultSet rs) throws SQLException, DataAccessException { LinkedHashMap dataMap = new LinkedHashMap(); while (rs.next()) { String url = generateUrl(rs.getString("cat_name") + "/" + rs.getString("brand_url_exp"), null, rs.getString("b_Name"), null); dataMap.put(rs.getString("B_NAME"), rs.getInt("BRAND_ID") + "#" + url + "#" + rs.getString("B_IMG")); } return dataMap; } }); } catch (Exception e) { e.printStackTrace(); } finally { } return bMap; }
From source file:org.ohmage.query.impl.CampaignQueries.java
@Override public QueryResultsList<Campaign> getCampaignInformation(final String username, final Collection<String> campaignIds, final Collection<String> classIds, final Collection<String> nameTokens, final Collection<String> descriptionTokens, final DateTime startDate, final DateTime endDate, final Campaign.PrivacyState privacyState, final Campaign.RunningState runningState, final Campaign.Role role) throws DataAccessException { try {/*w w w . ja va 2 s.c o m*/ // Begin with a common set of elements to select, and the tables to // which those elements belong. StringBuilder builder = new StringBuilder("SELECT ca.urn, ca.name, ca.description, " + "ca.icon_url, ca.authored_by, " + "crs.running_state, cps.privacy_state, " + "ca.creation_timestamp, " + "ca.xml " + "FROM " + "user u, " + "campaign ca, " + "campaign_running_state crs, " + "campaign_privacy_state cps " + "WHERE u.username = ? " + "AND ca.running_state_id = crs.id " + "AND ca.privacy_state_id = cps.id " + // ACL "AND (" + "(u.admin = true)" + " OR " + "EXISTS (" + "SELECT id " + "FROM user_role_campaign urc " + "WHERE u.id = urc.user_id " + "AND ca.id = urc.campaign_id" + ")" + ")"); List<Object> parameters = new LinkedList<Object>(); parameters.add(username); if (campaignIds != null) { if (campaignIds.size() == 0) { return (new QueryResultListBuilder<Campaign>()).getQueryResult(); } builder.append(" AND ca.urn IN ").append(StringUtils.generateStatementPList(campaignIds.size())); parameters.addAll(campaignIds); } if (classIds != null) { if (classIds.size() == 0) { return (new QueryResultListBuilder<Campaign>()).getQueryResult(); } builder.append(" AND (" + "ca.id IN (" + "SELECT cc.campaign_id " + "FROM campaign_class cc " + "WHERE cc.class_id IN (" + "SELECT cl.id " + "FROM class cl " + "WHERE cl.urn IN " + StringUtils.generateStatementPList(classIds.size()) + ")" + ")" + ")"); parameters.addAll(classIds); } if (nameTokens != null) { if (nameTokens.size() == 0) { return (new QueryResultListBuilder<Campaign>()).getQueryResult(); } boolean firstPass = true; builder.append(" AND ("); for (String nameToken : nameTokens) { if (firstPass) { firstPass = false; } else { builder.append(" OR "); } builder.append("ca.name LIKE ?"); parameters.add('%' + nameToken + '%'); } builder.append(")"); } if (descriptionTokens != null) { if (descriptionTokens.size() == 0) { return (new QueryResultListBuilder<Campaign>()).getQueryResult(); } boolean firstPass = true; builder.append(" AND ("); for (String descriptionToken : descriptionTokens) { if (firstPass) { firstPass = false; } else { builder.append(" OR "); } builder.append("ca.description LIKE ?"); parameters.add('%' + descriptionToken + '%'); } builder.append(")"); } if (startDate != null) { builder.append(" AND creation_timestamp >= ?"); parameters.add(DateTimeUtils.getIso8601DateString(startDate, true)); } if (endDate != null) { builder.append(" AND creation_timestamp <= ?"); parameters.add(DateTimeUtils.getIso8601DateString(endDate, true)); } if (runningState != null) { builder.append(" AND crs.running_state = ?"); parameters.add(runningState.toString()); } if (privacyState != null) { builder.append(" AND cps.privacy_state = ?"); parameters.add(privacyState.toString()); } if (role != null) { builder.append(" AND (" + "ca.id IN (" + "SELECT urc.campaign_id " + "FROM user_role ur, user_role_campaign urc " + "WHERE u.id = urc.user_id " + "AND ur.id = urc.user_role_id " + "AND ur.role = ?" + ")" + ")"); parameters.add(role.toString()); } return getJdbcTemplate().query(builder.toString(), parameters.toArray(), new ResultSetExtractor<QueryResultsList<Campaign>>() { /** * Counts the total number of results and converts each * of the actual results into a Campaign object. */ @Override public QueryResultsList<Campaign> extractData(ResultSet rs) throws SQLException, org.springframework.dao.DataAccessException { try { QueryResultListBuilder<Campaign> result = new QueryResultListBuilder<Campaign>(); while (rs.next()) { result.addResult(new Campaign(null, null, rs.getString("description"), Campaign.RunningState .valueOf(rs.getString("running_state").toUpperCase()), Campaign.PrivacyState .valueOf(rs.getString("privacy_state").toUpperCase()), new DateTime(rs.getTimestamp("creation_timestamp").getTime()).toDate(), rs.getString("xml"))); } return result.getQueryResult(); } catch (DomainException e) { throw new SQLException(e); } } }); } catch (org.springframework.dao.DataAccessException e) { throw new DataAccessException(e); } }
From source file:annis.administration.AdministrationDao.java
/** * Provides a list where the keys are the aliases and the values are the * corpus names.// ww w . j a v a 2 s .c o m * * @param databaseProperties * @return */ public Multimap<String, String> listCorpusAlias(File databaseProperties) { Multimap<String, String> result = TreeMultimap.create(); DataSource origDataSource = getDataSource().getInnerDataSource(); try { if (databaseProperties != null) { getDataSource().setInnerDataSource(createDataSource(databaseProperties)); } result = getJdbcTemplate().query( "SELECT a.alias AS alias, c.name AS corpus\n" + "FROM corpus_alias AS a, corpus AS c\n" + "WHERE\n" + " a.corpus_ref = c.id", new ResultSetExtractor<Multimap<String, String>>() { @Override public Multimap<String, String> extractData(ResultSet rs) throws SQLException, DataAccessException { Multimap<String, String> data = TreeMultimap.create(); while (rs.next()) { // alias -> corpus name data.put(rs.getString(1), rs.getString(2)); } return data; } }); } catch (IOException | URISyntaxException | DataAccessException ex) { if (databaseProperties == null) { log.error("Could not query corpus list", ex); } else { log.error("Could not query corpus list for the file " + databaseProperties.getAbsolutePath(), ex); } } finally { getDataSource().setInnerDataSource(origDataSource); } return result; }
From source file:annis.administration.AdministrationDao.java
@Transactional(readOnly = true) public UserConfig retrieveUserConfig(final String userName) { String sql = "SELECT * FROM user_config WHERE id=?"; UserConfig config = getJdbcTemplate().query(sql, new Object[] { userName }, new ResultSetExtractor<UserConfig>() { @Override//w w w .java2 s .c o m public UserConfig extractData(ResultSet rs) throws SQLException, DataAccessException { // default to empty config UserConfig c = new UserConfig(); if (rs.next()) { try { c = jsonMapper.readValue(rs.getString("config"), UserConfig.class); } catch (IOException ex) { log.error("Could not parse JSON that is stored in database (user configuration)", ex); } } return c; } }); return config; }
From source file:annis.administration.DefaultAdministrationDao.java
/** * Generates example queries if no example queries tab file is defined by the * user./* ww w .j a v a 2 s .c o m*/ */ private void generateExampleQueries() { // set in the annis.properties file. if (!generateExampleQueries) { log.info("skip generating example queries"); return; } // check if something was already imported int count = querySqlFromScript("count_example_queries.sql", new ResultSetExtractor<Integer>() { @Override public Integer extractData(ResultSet rs) throws SQLException, DataAccessException { while (rs.next()) { return (rs.getInt("amount")); } return -1; } }); if (count == 0) { log.info("generate example queries"); return; } log.error("generating example queries failed"); }