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);
String namecount = "SELECT count(*) FROM People WHERE LOWER(NAME) LIKE :pname "; String finalName= "%" + nameParam.toLowerCase().trim() + "%"; MapSqlParameterSource namedParams= new MapSqlParameterSource(); namedParams.addValue("pname", finalName); int count= this.namedParamJdbcTemplate.queryForInt(namecount, namedParams);
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);
@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); } }
@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); } }
@Override public void write(List<? extends Game> games) { for (Game game : games) { SqlParameterSource values = new MapSqlParameterSource().addValue("player_id", game.getId()).addValue( "year_no", game.getYear()).addValue("team", game.getTeam()).addValue("week", game.getWeek()) .addValue("opponent", game.getOpponent()).addValue("completes", game.getCompletes()).addValue( "attempts", game.getAttempts()).addValue("passing_yards", game.getPassingYards()).addValue( "passing_td", game.getPassingTd()).addValue("interceptions", game.getInterceptions()) .addValue("rushes", game.getRushes()).addValue("rush_yards", game.getRushYards()).addValue( "receptions", game.getReceptions()).addValue("receptions_yards", game.getReceptionYards()) .addValue("total_td", game.getTotalTd()); this.insertGame.execute(values); } }
@Override public void write(List<? extends Game> games) { for (Game game : games) { SqlParameterSource values = new MapSqlParameterSource().addValue("player_id", game.getId()).addValue( "year_no", game.getYear()).addValue("team", game.getTeam()).addValue("week", game.getWeek()) .addValue("opponent", game.getOpponent()).addValue("completes", game.getCompletes()).addValue( "attempts", game.getAttempts()).addValue("passing_yards", game.getPassingYards()).addValue( "passing_td", game.getPassingTd()).addValue("interceptions", game.getInterceptions()) .addValue("rushes", game.getRushes()).addValue("rush_yards", game.getRushYards()).addValue( "receptions", game.getReceptions()).addValue("receptions_yards", game.getReceptionYards()) .addValue("total_td", game.getTotalTd()); this.insertGame.execute(values); } }
@Test public void substituteNamedParameters() { MapSqlParameterSource namedParams = new MapSqlParameterSource(); namedParams.addValue("a", "a").addValue("b", "b").addValue("c", "c"); assertEquals("xxx ? ? ?", NamedParameterUtils.substituteNamedParameters("xxx :a :b :c", namedParams)); assertEquals("xxx ? ? ? xx ? ?", NamedParameterUtils.substituteNamedParameters("xxx :a :b :c xx :a :a", namedParams)); }
@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); }
@Test public void testAddInvoiceProcWithMetaDataUsingMapParamSource() throws Exception { initializeAddInvoiceWithMetaData(false); SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withProcedureName("add_invoice"); Number newId = adder.executeObject(Number.class, new MapSqlParameterSource() .addValue("amount", 1103) .addValue("custid", 3)); assertEquals(4, newId.intValue()); verifyAddInvoiceWithMetaData(false); 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); }
@Test public void testAddInvoiceFuncWithMetaDataUsingMapParamSource() throws Exception { initializeAddInvoiceWithMetaData(true); SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withFunctionName("add_invoice"); Number newId = adder.executeFunction(Number.class, new MapSqlParameterSource() .addValue("amount", 1103) .addValue("custid", 3)); assertEquals(4, newId.intValue()); verifyAddInvoiceWithMetaData(true); verify(connection, atLeastOnce()).close(); }
@Test public void testQueryForListWithParamMap() throws Exception { given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, true, false); given(resultSet.getObject(1)).willReturn(11, 12); 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", 2, li.size()); assertEquals("First row is Integer", 11, ((Integer) li.get(0).get("age")).intValue()); assertEquals("Second row is Integer", 12, ((Integer) li.get(1).get("age")).intValue()); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID < ?"); verify(preparedStatement).setObject(1, 3); }
@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); }
@Test public void testQueryForListWithParamMapAndSingleRowAndColumn() 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); List<Map<String, Object>> li = template.queryForList( "SELECT AGE FROM CUSTMR WHERE ID < :id", params); assertEquals("All rows returned", 1, li.size()); assertEquals("First row is Integer", 11, ((Integer) li.get(0).get("age")).intValue()); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID < ?"); verify(preparedStatement).setObject(1, 3); }
@Test public void testQueryForListWithParamMapAndIntegerElementAndSingleRowAndColumn() throws Exception { given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getInt(1)).willReturn(11); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("id", 3); List<Integer> li = template.queryForList("SELECT AGE FROM CUSTMR WHERE ID < :id", params, Integer.class); assertEquals("All rows returned", 1, li.size()); assertEquals("First row is Integer", 11, li.get(0).intValue()); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID < ?"); verify(preparedStatement).setObject(1, 3); }
@Test public void testQueryForObjectWithParamMapAndListOfExpressionLists() throws Exception { given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getInt(1)).willReturn(22); MapSqlParameterSource params = new MapSqlParameterSource(); List<Object[]> l1 = new ArrayList<>(); l1.add(new Object[] {3, "Rod"}); l1.add(new Object[] {4, "Juergen"}); params.addValue("multiExpressionList", l1); Object o = template.queryForObject( "SELECT AGE FROM CUSTMR WHERE (ID, NAME) IN (:multiExpressionList)", params, Integer.class); assertTrue("Correct result type", o instanceof Integer); verify(connection).prepareStatement( "SELECT AGE FROM CUSTMR WHERE (ID, NAME) IN ((?, ?), (?, ?))"); verify(preparedStatement).setObject(1, 3); }
@Test public void testQueryForObjectWithParamMapAndList() throws Exception { String sql = "SELECT AGE FROM CUSTMR WHERE ID IN (:ids)"; String sqlToUse = "SELECT AGE FROM CUSTMR WHERE ID IN (?, ?)"; given(resultSet.getMetaData()).willReturn(resultSetMetaData); given(resultSet.next()).willReturn(true, false); given(resultSet.getInt(1)).willReturn(22); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("ids", Arrays.asList(3, 4)); Object o = template.queryForObject(sql, params, Integer.class); assertTrue("Correct result type", o instanceof Integer); verify(connection).prepareStatement(sqlToUse); verify(preparedStatement).setObject(1, 3); }
@Test public void testQueryForObjectWithParamMapAndInteger() 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); Object o = template.queryForObject("SELECT AGE FROM CUSTMR WHERE ID = :id", params, Integer.class); assertTrue("Correct result type", o instanceof Integer); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID = ?"); verify(preparedStatement).setObject(1, 3); }
@Test public void testQueryForObjectWithParamMapAndRowMapper() throws Exception { given(resultSet.next()).willReturn(true, false); given(resultSet.getInt(1)).willReturn(22); MapSqlParameterSource params = new MapSqlParameterSource(); params.addValue("id", 3); Object o = template.queryForObject("SELECT AGE FROM CUSTMR WHERE ID = :id", params, new RowMapper<Object>() { @Override public Object mapRow(ResultSet rs, int rowNum) throws SQLException { return rs.getInt(1); } }); assertTrue("Correct result type", o instanceof Integer); verify(connection).prepareStatement("SELECT AGE FROM CUSTMR WHERE ID = ?"); verify(preparedStatement).setObject(1, 3); }