@Override public <T> List<T> query(String sql, RowMapper<T> rowMapper) throws DataAccessException { return query(sql, EmptySqlParameterSource.INSTANCE, rowMapper); }
@Override public void query(String sql, RowCallbackHandler rch) throws DataAccessException { query(sql, EmptySqlParameterSource.INSTANCE, rch); }
@Override @Nullable public <T> T query(String sql, ResultSetExtractor<T> rse) throws DataAccessException { return query(sql, EmptySqlParameterSource.INSTANCE, rse); }
@Override public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper) throws DataAccessException { return query(sql, new MapSqlParameterSource(paramMap), rowMapper); }
@Override public <T> List<T> queryForList(String sql, SqlParameterSource paramSource, Class<T> elementType) throws DataAccessException { return query(sql, paramSource, new SingleColumnRowMapper<>(elementType)); }
@Override public List<Map<String, Object>> queryForList(String sql, SqlParameterSource paramSource) throws DataAccessException { return query(sql, paramSource, new ColumnMapRowMapper()); }
@Override @Nullable public <T> T query(String sql, Map<String, ?> paramMap, ResultSetExtractor<T> rse) throws DataAccessException { return query(sql, new MapSqlParameterSource(paramMap), rse); }
@Override public void query(String sql, Map<String, ?> paramMap, RowCallbackHandler rch) throws DataAccessException { query(sql, new MapSqlParameterSource(paramMap), rch); }
NamedParameterJdbcTemplate db = ...; List paramList = ...; Map idsMap = Collections.singletonMap("ids", paramList); db.query("select * from mytable m where m.group_id in (:ids)", idsMap);
@Override public E get(int index) { if (index >= size) { throw new ArrayIndexOutOfBoundsException(index); } if (current == null || index - start >= pageSize || index < start) { current = parameterJdbcTemplate.query(limitSqlAdapter.getLimitSql(sql, index, pageSize), args, mapper); start = index; } return current.get(index - start); }
@Override public List<T> query(String filter, String sortBy, boolean ascending, String zoneId) { validateOrderBy(queryConverter.map(sortBy)); if (StringUtils.hasText(filter)) { filter = "("+ filter+ ") and"; } filter += " identity_zone_id eq \""+ zoneId +"\""; SearchQueryConverter.ProcessedFilter where = queryConverter.convert(filter, sortBy, ascending); logger.debug("Filtering groups with SQL: " + where); List<T> result; try { String completeSql = getQuerySQL(filter, where); logger.debug("complete sql: " + completeSql + ", params: " + where.getParams()); if (pageSize > 0 && pageSize < Integer.MAX_VALUE) { result = pagingListFactory.createJdbcPagingList(completeSql, where.getParams(), rowMapper, pageSize); } else { result = jdbcTemplate.query(completeSql, where.getParams(), rowMapper); } return result; } catch (DataAccessException e) { logger.debug("Filter '" + filter + "' generated invalid SQL", e); throw new IllegalArgumentException("Invalid filter: " + filter); } }
@Test public void testQueryWithRowCallbackHandlerNoParameters() throws SQLException { given(resultSet.next()).willReturn(true, false); given(resultSet.getInt("id")).willReturn(1); given(resultSet.getString("forename")).willReturn("rod"); final List<Customer> customers = new LinkedList<>(); namedParameterTemplate.query(SELECT_NO_PARAMETERS, rs -> { Customer cust = new Customer(); cust.setId(rs.getInt(COLUMN_NAMES[0])); cust.setForename(rs.getString(COLUMN_NAMES[1])); customers.add(cust); }); assertEquals(1, customers.size()); assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1); assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod")); verify(connection).prepareStatement(SELECT_NO_PARAMETERS); verify(preparedStatement).close(); verify(connection).close(); }
@Test public void testQueryWithRowCallbackHandler() throws SQLException { given(resultSet.next()).willReturn(true, false); given(resultSet.getInt("id")).willReturn(1); given(resultSet.getString("forename")).willReturn("rod"); params.put("id", new SqlParameterValue(Types.DECIMAL, 1)); params.put("country", "UK"); final List<Customer> customers = new LinkedList<>(); namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params, rs -> { Customer cust = new Customer(); cust.setId(rs.getInt(COLUMN_NAMES[0])); cust.setForename(rs.getString(COLUMN_NAMES[1])); customers.add(cust); }); assertEquals(1, customers.size()); assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1); assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod")); verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED); verify(preparedStatement).setObject(1, 1, Types.DECIMAL); verify(preparedStatement).setString(2, "UK"); verify(preparedStatement).close(); verify(connection).close(); }
@Test public void testQueryWithRowMapperNoParameters() throws SQLException { given(resultSet.next()).willReturn(true, false); given(resultSet.getInt("id")).willReturn(1); given(resultSet.getString("forename")).willReturn("rod"); List<Customer> customers = namedParameterTemplate.query(SELECT_NO_PARAMETERS, (rs, rownum) -> { Customer cust = new Customer(); cust.setId(rs.getInt(COLUMN_NAMES[0])); cust.setForename(rs.getString(COLUMN_NAMES[1])); return cust; }); assertEquals(1, customers.size()); assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1); assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod")); verify(connection).prepareStatement(SELECT_NO_PARAMETERS); verify(preparedStatement).close(); verify(connection).close(); }
@Test public void testQueryWithRowMapper() throws SQLException { given(resultSet.next()).willReturn(true, false); given(resultSet.getInt("id")).willReturn(1); given(resultSet.getString("forename")).willReturn("rod"); params.put("id", new SqlParameterValue(Types.DECIMAL, 1)); params.put("country", "UK"); List<Customer> customers = namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params, (rs, rownum) -> { Customer cust = new Customer(); cust.setId(rs.getInt(COLUMN_NAMES[0])); cust.setForename(rs.getString(COLUMN_NAMES[1])); return cust; }); assertEquals(1, customers.size()); assertTrue("Customer id was assigned correctly", customers.get(0).getId() == 1); assertTrue("Customer forename was assigned correctly", customers.get(0).getForename().equals("rod")); verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED); verify(preparedStatement).setObject(1, 1, Types.DECIMAL); verify(preparedStatement).setString(2, "UK"); verify(preparedStatement).close(); verify(connection).close(); }
@Test public void testQueryWithResultSetExtractorNoParameters() throws SQLException { given(resultSet.next()).willReturn(true); given(resultSet.getInt("id")).willReturn(1); given(resultSet.getString("forename")).willReturn("rod"); Customer cust = namedParameterTemplate.query(SELECT_NO_PARAMETERS, rs -> { rs.next(); Customer cust1 = new Customer(); cust1.setId(rs.getInt(COLUMN_NAMES[0])); cust1.setForename(rs.getString(COLUMN_NAMES[1])); return cust1; }); assertTrue("Customer id was assigned correctly", cust.getId() == 1); assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod")); verify(connection).prepareStatement(SELECT_NO_PARAMETERS); verify(preparedStatement).close(); verify(connection).close(); }
@Test public void testQueryWithResultSetExtractor() throws SQLException { given(resultSet.next()).willReturn(true); given(resultSet.getInt("id")).willReturn(1); given(resultSet.getString("forename")).willReturn("rod"); params.put("id", new SqlParameterValue(Types.DECIMAL, 1)); params.put("country", "UK"); Customer cust = namedParameterTemplate.query(SELECT_NAMED_PARAMETERS, params, rs -> { rs.next(); Customer cust1 = new Customer(); cust1.setId(rs.getInt(COLUMN_NAMES[0])); cust1.setForename(rs.getString(COLUMN_NAMES[1])); return cust1; }); assertTrue("Customer id was assigned correctly", cust.getId() == 1); assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod")); verify(connection).prepareStatement(SELECT_NAMED_PARAMETERS_PARSED); verify(preparedStatement).setObject(1, 1, Types.DECIMAL); verify(preparedStatement).setString(2, "UK"); verify(preparedStatement).close(); verify(connection).close(); }
MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("ids", transIdList); query = namedTemplate.query(selectTransDetailsByIds, paramSource,new BeanPropertyRowMapper<DataBaseTransactionLogDetail>(DataBaseTransactionLogDetail.class));
messages = namedParameterJdbcTemplate.query(query, parameters, this.messageRowMapper);