Refine search
Set<Integer> ids = ...; MapSqlParameterSource parameters = new MapSqlParameterSource(); parameters.addValue("ids", ids); List<Foo> foo = getJdbcTemplate().query("SELECT * FROM foo WHERE a IN (:ids)", getRowMapper(), parameters);
@Test public void sqlParameterValueRegistersSqlType() throws Exception { MapSqlParameterSource msps = new MapSqlParameterSource("FOO", new SqlParameterValue(2, "Foo")); assertEquals("Correct SQL Type not registered", 2, msps.getSqlType("FOO")); MapSqlParameterSource msps2 = new MapSqlParameterSource(); msps2.addValues(msps.getValues()); assertEquals("Correct SQL Type not registered", 2, msps2.getSqlType("FOO")); }
NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource); MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("name", name); paramSource.addValue("city", city); jdbcTemplate.queryForRowSet("SELECT * FROM customers WHERE name = :name AND city = :city", paramSource);
@Test(expected = IllegalArgumentException.class) public void getValueChokesIfParameterIsNotPresent() throws Exception { MapSqlParameterSource source = new MapSqlParameterSource(); source.getValue("pechorin was right!"); }
@Test public void testQueryForIntWithParamMap() throws Exception { given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getInt(1)).willReturn(22); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("id", 3); int i = template.queryForObject("SELECT AGE FROM CUSTMR WHERE ID = :id", params, Integer.class).intValue(); assertEquals("Return of an int", 22, i); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID = ?"); verify(preparedStatement).setObject(1, 3); }
@Override public void createCopyEvent(int repositoryId, long revision, String copyFromPath, long copyFromRevision, String copyToPath) { getNamedParameterJdbcTemplate().update( "INSERT INTO EXT_SVN_COPY (REPOSITORY, REVISION, COPYFROMPATH, COPYFROMREVISION, COPYTOPATH) VALUES (:repository, :revision, :copyFromPath, :copyFromRevision, :copyToPath)", params("revision", revision) .addValue("repository", repositoryId) .addValue("copyFromPath", copyFromPath) .addValue("copyFromRevision", copyFromRevision) .addValue("copyToPath", copyToPath) ); }
@Override public void write(List<? extends PlayerSummary> summaries) { for (PlayerSummary summary : summaries) { MapSqlParameterSource args = new MapSqlParameterSource().addValue("id", summary.getId()).addValue("year", summary.getYear()).addValue("completes", summary.getCompletes()).addValue("attempts", summary.getAttempts()).addValue("passingYards", summary.getPassingYards()).addValue("passingTd", summary.getPassingTd()).addValue("interceptions", summary.getInterceptions()).addValue("rushes", summary.getRushes()).addValue("rushYards", summary.getRushYards()).addValue("receptions", summary.getReceptions()).addValue("receptionYards", summary.getReceptionYards()).addValue( "totalTd", summary.getTotalTd()); namedParameterJdbcTemplate.update(INSERT_SUMMARY, args); } }
NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(localJdbcTemplate); MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("ids", transIdList); query = namedTemplate.query(selectTransDetailsByIds, paramSource,new BeanPropertyRowMapper<DataBaseTransactionLogDetail>(DataBaseTransactionLogDetail.class));
@Override @Nullable public <T> T queryForObject(String sql, Map<String, ?> paramMap, RowMapper<T>rowMapper) throws DataAccessException { return queryForObject(sql, new MapSqlParameterSource(paramMap), rowMapper); }
@Override public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper) throws DataAccessException { return query(sql, new MapSqlParameterSource(paramMap), rowMapper); }
@Test public void testQueryForListWithParamMapAndEmptyResult() throws Exception { given(resultSet.next()).willReturn(false); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("id", 3); List<Map<String, Object>> li = template.queryForList( "SELECT AGE FROM CUSTMR WHERE ID < :id", params); assertEquals("All rows returned", 0, li.size()); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID < ?"); verify(preparedStatement).setObject(1, 3); }
@Override public int update(String sql, Map<String, ?> paramMap) throws DataAccessException { return update(sql, new MapSqlParameterSource(paramMap)); }
/** * Create an array of {@link MapSqlParameterSource} objects populated with data from * the values passed in. This will define what is included in a batch operation. * @param valueMaps array of {@link Map} instances containing the values to be used * @return an array of {@link SqlParameterSource} * @see MapSqlParameterSource * @see NamedParameterJdbcTemplate#batchUpdate(String, Map[]) */ public static SqlParameterSource[] createBatch(Map<String, ?>[] valueMaps) { SqlParameterSource[] batch = new SqlParameterSource[valueMaps.length]; for (int i = 0; i < valueMaps.length; i++) { batch[i] = new MapSqlParameterSource(valueMaps[i]); } return batch; }
@Test public void testBatchUpdateWithSqlParameterSourcePlusTypeInfo() throws Exception { SqlParameterSource[] ids = new SqlParameterSource[2]; ids[0] = new MapSqlParameterSource().addValue("id", 100, Types.NUMERIC); ids[1] = new MapSqlParameterSource().addValue("id", 200, Types.NUMERIC); final int[] rowsAffected = new int[] {1, 2}; given(preparedStatement.executeBatch()).willReturn(rowsAffected); given(connection.getMetaData()).willReturn(databaseMetaData); namedParameterTemplate = new NamedParameterJdbcTemplate(new JdbcTemplate(dataSource, false)); int[] actualRowsAffected = namedParameterTemplate.batchUpdate( "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = :id", ids); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]); assertEquals(rowsAffected[1], actualRowsAffected[1]); verify(connection).prepareStatement("UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"); verify(preparedStatement).setObject(1, 100, Types.NUMERIC); verify(preparedStatement).setObject(1, 200, Types.NUMERIC); verify(preparedStatement, times(2)).addBatch(); verify(preparedStatement, atLeastOnce()).close(); verify(connection, atLeastOnce()).close(); }
@Override public List<Employee> employeesWithSalaryGreaterThan(Integer minSalary) { logger.info("Looking for employeesWithSalaryGreaterThan using JDBCTemplate"); String query = "SELECT * " + " FROM employee" + " WHERE salary > :salary"; MapSqlParameterSource params = new MapSqlParameterSource() .addValue("salary", minSalary); // using BeanPropertyRowMapper is easier, but with much worse performance than custom RowMapper return jdbcTemplate.query(query, params, BeanPropertyRowMapper.newInstance(Employee.class)); }
@Test public void testBatchUpdateWithSqlParameterSource() throws Exception { SqlParameterSource[] ids = new SqlParameterSource[2]; ids[0] = new MapSqlParameterSource("id", 100); ids[1] = new MapSqlParameterSource("id", 200); final int[] rowsAffected = new int[] {1, 2}; given(preparedStatement.executeBatch()).willReturn(rowsAffected); given(connection.getMetaData()).willReturn(databaseMetaData); namedParameterTemplate = new NamedParameterJdbcTemplate(new JdbcTemplate(dataSource, false)); int[] actualRowsAffected = namedParameterTemplate.batchUpdate( "UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = :id", ids); assertTrue("executed 2 updates", actualRowsAffected.length == 2); assertEquals(rowsAffected[0], actualRowsAffected[0]); assertEquals(rowsAffected[1], actualRowsAffected[1]); verify(connection).prepareStatement("UPDATE NOSUCHTABLE SET DATE_DISPATCHED = SYSDATE WHERE ID = ?"); verify(preparedStatement).setObject(1, 100); verify(preparedStatement).setObject(1, 200); verify(preparedStatement, times(2)).addBatch(); verify(preparedStatement, atLeastOnce()).close(); verify(connection, atLeastOnce()).close(); }
@Test public void testQueryForMapWithParamMapAndSingleRowAndColumn() throws Exception { given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getObject(1)).willReturn(11); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("id", 3); Map<String, Object> map = template.queryForMap("SELECT AGE FROM CUSTMR WHERE ID < :id", params); assertEquals("Row is Integer", 11, ((Integer) map.get("age")).intValue()); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID < ?"); verify(preparedStatement).setObject(1, 3); }
private void batchUpdateDepartments(List<Department> departmentsToInsert, String statement) { MapSqlParameterSource[] paramsList = departmentsToInsert .stream() .map(department -> new MapSqlParameterSource() .addValue("pid", department.getPid()) .addValue("company_pid", department.getCompanyPid()) .addValue("name", department.getName()) ) .toArray(MapSqlParameterSource[]::new); jdbcTemplate.batchUpdate(statement, paramsList); }
given(databaseMetaData.getColumns(null, USER, TABLE, null)).willReturn(columnsResultSet); MapSqlParameterSource map = new MapSqlParameterSource(); map.addValue("id", 1); map.addValue("name", "Sven"); map.addValue("customersince", new Date()); map.addValue("version", 0); map.registerSqlType("customersince", Types.DATE); map.registerSqlType("version", Types.NUMERIC);
@Override public <T> List<T> queryForList(String sql, Map<String, ?> paramMap, Class<T> elementType) throws DataAccessException { return queryForList(sql, new MapSqlParameterSource(paramMap), elementType); }