List of usage examples for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate query
@Override
public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper)
throws DataAccessException
From source file:com.joliciel.jochre.boundaries.BoundaryDaoJdbc.java
@Override public List<Split> findSplits(Shape shape) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); String sql = "SELECT " + SELECT_SPLIT + " FROM ocr_split WHERE split_shape_id=:split_shape_id" + " ORDER BY split_position"; MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("split_shape_id", shape.getId()); LOG.debug(sql);/*from w w w .j av a 2 s.c om*/ logParameters(paramSource); @SuppressWarnings("unchecked") List<Split> splits = jt.query(sql, paramSource, new SplitMapper(this.getBoundaryServiceInternal())); return splits; }
From source file:com.joliciel.jochre.doc.DocumentDaoJdbc.java
@Override public List<JochreDocument> findDocuments() { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); String sql = "SELECT " + SELECT_DOCUMENT + " FROM ocr_document ORDER BY doc_name"; MapSqlParameterSource paramSource = new MapSqlParameterSource(); LOG.info(sql);/*from ww w . j a v a 2 s .com*/ logParameters(paramSource); @SuppressWarnings("unchecked") List<JochreDocument> documents = jt.query(sql, paramSource, new JochreDocumentMapper(this.getDocumentServiceInternal())); return documents; }
From source file:nl.surfnet.coin.teams.service.impl.TeamExternalGroupDaoImpl.java
@Override public List<TeamExternalGroup> getByExternalGroupIdentifiers(Collection<String> identifiers) { try {/*from ww w . j a va 2s. c o m*/ NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(this.jdbcTemplate); Map<String, Object> params = new HashMap<String, Object>(); params.put("identifiers", identifiers); String s = "SELECT teg.id AS teg_id, teg.grouper_team_id, eg.id AS eg_id, eg.identifier, eg.name, eg.description, eg.group_provider " + "FROM team_external_groups AS teg " + "INNER JOIN external_groups AS eg " + "ON teg.external_groups_id = eg.id " + "WHERE eg.identifier in (:identifiers) "; return template.query(s, params, new RowMapper<TeamExternalGroup>() { @Override public TeamExternalGroup mapRow(ResultSet rs, int rowNum) throws SQLException { return mapRowToTeamExternalGroup(rs); } }); } catch (EmptyResultDataAccessException er) { return new ArrayList<TeamExternalGroup>(); } }
From source file:com.joliciel.jochre.doc.DocumentDaoJdbc.java
@Override public List<Author> findAuthors() { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); String sql = "SELECT " + SELECT_AUTHOR + " FROM ocr_author" + " ORDER BY author_last_name, author_first_name"; MapSqlParameterSource paramSource = new MapSqlParameterSource(); LOG.info(sql);/*from w ww . ja va 2 s. c om*/ logParameters(paramSource); @SuppressWarnings("unchecked") List<Author> authors = jt.query(sql, paramSource, new AuthorMapper(this.getDocumentServiceInternal())); return authors; }
From source file:org.surfnet.cruncher.repository.StatisticsRepositoryImpl.java
@Override public List<LoginEntry> getUnprocessedLoginEntries(int nrOfRecords) { Long aggregateStartingPoint = cruncherJdbcTemplate .queryForLong("select aggregatepoint from aggregate_meta_data"); NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(ebJdbcTemplate); String query = "select * from log_logins where id > :startingPoint order by id LIMIT :batchSize"; Map<String, Object> parameterMap = new HashMap<String, Object>(); parameterMap.put("batchSize", nrOfRecords); parameterMap.put("startingPoint", aggregateStartingPoint); return namedJdbcTemplate.query(query, parameterMap, new RowMapper<LoginEntry>() { @Override//from ww w.j av a 2s . c o m public LoginEntry mapRow(ResultSet rs, int rowNum) throws SQLException { Long id = rs.getLong("id"); String idpEntityId = rs.getString("idpentityid"); String idpEntityName = rs.getString("idpentityname"); Date loginDate = new Date(rs.getTimestamp("loginstamp").getTime()); String spEntityId = rs.getString("spentityid"); String spEntityName = rs.getString("spentityname"); String userId = rs.getString("userid"); return new LoginEntry(id, idpEntityId, idpEntityName, loginDate, spEntityId, spEntityName, userId); } }); }
From source file:com.joliciel.jochre.doc.DocumentDaoJdbc.java
@Override public List<JochrePage> findJochrePages(JochreDocument jochreDocument) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); String sql = "SELECT " + SELECT_PAGE + " FROM ocr_page WHERE page_doc_id=:page_doc_id" + " ORDER BY page_index"; MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("page_doc_id", jochreDocument.getId()); LOG.info(sql);/* w ww .j ava2s.c o m*/ logParameters(paramSource); @SuppressWarnings("unchecked") List<JochrePage> jochrePages = jt.query(sql, paramSource, new JochrePageMapper(this.getDocumentServiceInternal())); return jochrePages; }
From source file:org.surfnet.cruncher.repository.StatisticsRepositoryImpl.java
@Override public List<SpStatistic> getActiveServices(String userid, String idpEntityId) { NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(cruncherJdbcTemplate); String query = "select loginstamp as loginstamp, spentityid as spentityid, " + "spentityname as spentityname from user_log_logins " + "where " + "userid = :userId AND " + "idpentityid = :idpEntityId "; Map<String, Object> parameterMap = new HashMap<String, Object>(); parameterMap.put("userId", userid); parameterMap.put("idpEntityId", idpEntityId); return namedJdbcTemplate.query(query, parameterMap, new RowMapper<SpStatistic>() { @Override// w w w .j av a2 s . c o m public SpStatistic mapRow(ResultSet rs, int row) throws SQLException { SpStatistic result = new SpStatistic(); result.setEntryTime(rs.getTimestamp("loginstamp").getTime()); result.setSpEntityId(rs.getString("spentityid")); result.setSpName(rs.getString("spentityname")); return result; } }); }
From source file:com.joliciel.jochre.doc.DocumentDaoJdbc.java
@Override public List<Author> findAuthors(JochreDocument jochreDocument) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); String sql = "SELECT " + SELECT_AUTHOR + " FROM ocr_author" + " INNER JOIN ocr_doc_author_map ON docauthor_author_id = author_id" + " WHERE docauthor_doc_id=:docauthor_doc_id" + " ORDER BY author_last_name, author_first_name"; MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("docauthor_doc_id", jochreDocument.getId()); LOG.info(sql);// www . j a v a2 s . c o m logParameters(paramSource); @SuppressWarnings("unchecked") List<Author> authors = jt.query(sql, paramSource, new AuthorMapper(this.getDocumentServiceInternal())); return authors; }
From source file:org.surfnet.cruncher.repository.StatisticsRepositoryImpl.java
/** * {@inheritDoc}/*from w w w .j a v a 2s .c o m*/ */ @Override public List<LoginData> getLogins(final LocalDate start, final LocalDate end, final String idpEntityId, final String spEntityId) { final List<LoginData> result = new ArrayList<LoginData>(); NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(cruncherJdbcTemplate); String query = "select * from aggregated_log_logins " + "where " + "entryday >= :startDate AND " + "entryday <= :endDate AND " + "(:spEntityId IS NULL OR spentityid = :spEntityId) AND " + "(:idpEntityId IS NULL OR idpentityid = :idpEntityId) " + "order by idpentityid, spentityid, entryday "; Map<String, Object> parameterMap = getParameterMap(start, end, idpEntityId, spEntityId); namedJdbcTemplate.query(query, parameterMap, new RowMapper<Object>() { private Map<LocalDate, Integer> queryResult = new HashMap<LocalDate, Integer>(); private LoginData currentAggregate = null; @Override public Object mapRow(ResultSet rs, int row) throws SQLException { LoginData currentRow = getLoginDataFromRow(rs); /* * aggregate if sp/idp entityid differs from previous record * do not aggregate if on first record * if on last record, aggregate last entries */ if (!currentRow.equals(currentAggregate) && !rs.isFirst()) { //record is different, aggregate previous one and start fresh result.add(aggregateCurrentEntry(currentAggregate, start, end)); queryResult = new HashMap<LocalDate, Integer>(); } queryResult.put(new LocalDate(rs.getDate("entryday")), rs.getInt("entrycount")); currentAggregate = currentRow; if (rs.isLast()) { // aggregate last set result.add(aggregateCurrentEntry(currentAggregate, start, end)); } /* * This is kinda weird, but single row results are stored in * queryResult (hashmap) or aggregated in result (List<loginData) */ return null; } private LoginData aggregateCurrentEntry(final LoginData loginData, final LocalDate start, final LocalDate end) { LocalDate current = start; int total = 0; while (current.isBefore(end.plusDays(1))) { Integer count = queryResult.get(current); if (count == null) { loginData.getData().add(0); } else { loginData.getData().add(count); total += count; } current = current.plusDays(1); } loginData.setTotal(total); loginData.setPointStart(start.toDate().getTime()); loginData.setPointEnd(end.toDate().getTime()); loginData.setPointInterval(POINT_INTERVAL); return loginData; } private LoginData getLoginDataFromRow(ResultSet rs) throws SQLException { LoginData result = new LoginData(); result.setIdpEntityId(rs.getString("idpentityid")); result.setIdpname(rs.getString("idpentityname")); result.setSpEntityId(rs.getString("spentityid")); result.setSpName(rs.getString("spentityname")); return result; } }); return result; }
From source file:info.raack.appliancelabeler.data.JDBCDatabase.java
public List<SecondData> getEnergyMeasurementsForMonitor(EnergyMonitor energyMonitor, Date start, Date end, int ticks) { EnergyMeasurementQueryItems items = getEnergyMeasurementQueryItems(energyMonitor, start, end, ticks); NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(jdbcTemplate); return template.query(items.sql, items.parameters, secondDataRowMapper); }