@Test public void testNoSuchTable() throws Exception { ResultSet resultSet = mock(ResultSet.class); given(resultSet.next()).willReturn(false); given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB"); given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB"); given(databaseMetaData.getDatabaseProductVersion()).willReturn("1.0"); given(databaseMetaData.getUserName()).willReturn("me"); given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true); given(databaseMetaData.getTables(null, null, "x", null)).willReturn(resultSet); SimpleJdbcInsert insert = new SimpleJdbcInsert(dataSource).withTableName("x"); // Shouldn't succeed in inserting into table which doesn't exist thrown.expect(InvalidDataAccessApiUsageException.class); try { insert.execute(new HashMap<>()); } finally { verify(resultSet).close(); } }
@Transactional public class Test { @Autowired DataSource ds; public void test1() throws Exception { Map<String, Object> params = new HashMap<String, Object>(); params.put("c1", "test"); SimpleJdbcInsert insert = new SimpleJdbcInsert(ds).withTableName("t1").usingColumns("c1") .usingGeneratedKeyColumns("id"); long id = insert.executeAndReturnKey(params).longValue(); params = new HashMap<String, Object>(); params.put("stuff", "stuff"); params.put("id_fk", id); SimpleJdbcInsert insert2 = new SimpleJdbcInsert(ds).withTableName( "table2").usingColumns("stuff", "id_fk"); insert2.execute(params); NamedParameterJdbcTemplate tmpl = new NamedParameterJdbcTemplate(ds); params = new HashMap<String, Object>(); params.put("id", id); String c1 = tmpl.queryForObject("select c1 from t1 where id = :id", params, String.class); }
@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); } }
SimpleJdbcInsert insertUser = new SimpleJdbcInsert(jdbcTemplate) .withTableName("UserType").usingColumns("Id", "Name", "Company", "PNumber", "FClass"); Map<String,Object> insertParameters = new HashMap<String, Object>(); /* Put Values */ insertParameters.put("Id", 1); insertParameters.put("Name", "Sam"); insertParameters.put("Company", "123"); insertParameters.put("PNumber", "123"); insertParameters.put("FClass", "N/A"); Number generatedId = insertUser.execute(insertParameters);
@Override public void write(List<? extends Payment> payments) throws Exception { for (Payment payment : payments) { MapSqlParameterSource parameterSource = new MapSqlParameterSource(); parameterSource.addValue("RECIPIENT", payment.getDestinationAccountNo()).addValue("PAYEE", payment.getSourceAccountNo()) .addValue("AMOUNT", payment.getAmount()).addValue("DATE", payment.getDate()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE + ? WHERE ID = ?", payment.getAmount(), payment.getDestinationAccountNo()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE - ? WHERE ID = ?", payment.getAmount(), payment.getSourceAccountNo()); paymentInsert.execute(parameterSource); LOGGER.info("Executing step: " + payment); } } }
@Override public void write(List<? extends Payment> payments) throws Exception { for (Payment payment : payments) { MapSqlParameterSource parameterSource = new MapSqlParameterSource(); parameterSource.addValue("RECIPIENT", payment.getDestinationAccountNo()).addValue("PAYEE", payment.getSourceAccountNo()) .addValue("AMOUNT", payment.getAmount()).addValue("DATE", payment.getDate()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE + ? WHERE ID = ?", payment.getAmount(), payment.getDestinationAccountNo()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE - ? WHERE ID = ?", payment.getAmount(), payment.getSourceAccountNo()); paymentInsert.execute(parameterSource); LOGGER.info("Executing step: " + payment); } } }
@Override public void write(List<? extends Payment> payments) throws Exception { for (Payment payment : payments) { MapSqlParameterSource parameterSource = new MapSqlParameterSource(); parameterSource.addValue("RECIPIENT", payment.getDestinationAccountNo()).addValue("PAYEE", payment.getSourceAccountNo()) .addValue("AMOUNT", payment.getAmount()).addValue("DATE", payment.getDate()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE + ? WHERE ID = ?", payment.getAmount(), payment.getDestinationAccountNo()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE - ? WHERE ID = ?", payment.getAmount(), payment.getSourceAccountNo()); paymentInsert.execute(parameterSource); logger.info("Executing step: " + payment); } } }
@Override public void write(List<? extends Payment> payments) throws Exception { for (Payment payment : payments) { MapSqlParameterSource parameterSource = new MapSqlParameterSource(); parameterSource.addValue("RECIPIENT", payment.getDestinationAccountNo()).addValue("PAYEE", payment.getSourceAccountNo()) .addValue("AMOUNT", payment.getAmount()).addValue("DATE", payment.getDate()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE + ? WHERE ID = ?", payment.getAmount(), payment.getDestinationAccountNo()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE - ? WHERE ID = ?", payment.getAmount(), payment.getSourceAccountNo()); paymentInsert.execute(parameterSource); LOGGER.info("Executing step: " + payment); } } }
@Override public void write(List<? extends Payment> payments) throws Exception { for (Payment payment : payments) { MapSqlParameterSource parameterSource = new MapSqlParameterSource(); parameterSource.addValue("RECIPIENT", payment.getDestinationAccountNo()).addValue("PAYEE", payment.getSourceAccountNo()) .addValue("AMOUNT", payment.getAmount()).addValue("DATE", payment.getDate()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE + ? WHERE ID = ?", payment.getAmount(), payment.getDestinationAccountNo()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE - ? WHERE ID = ?", payment.getAmount(), payment.getSourceAccountNo()); paymentInsert.execute(parameterSource); LOGGER.info("Executing step: " + payment); } } }
@Override public void write(List<? extends Payment> payments) throws Exception { for (Payment payment : payments) { MapSqlParameterSource parameterSource = new MapSqlParameterSource(); parameterSource.addValue("RECIPIENT", payment.getDestinationAccountNo()).addValue("PAYEE", payment.getSourceAccountNo()) .addValue("AMOUNT", payment.getAmount()).addValue("DATE", payment.getDate()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE + ? WHERE ID = ?", payment.getAmount(), payment.getDestinationAccountNo()); accountUpdate.update("UPDATE ACCOUNTS SET BALANCE = BALANCE - ? WHERE ID = ?", payment.getAmount(), payment.getSourceAccountNo()); paymentInsert.execute(parameterSource); System.out.println("Executing the step " + payment.getDate()); } } }