CODEWITHSUNDEEP
javaspring
Julian
Julian

Reputation: 4085

How to use NamedParameterJdbcTemplate for batch inserts

I am using spring NamedParameterJdbcTemplate to batch insert records into a database table.

CREATE TABLE test (x number);

And my repository class is something like this:

@Repository
public class TestRepository {
    private static final String TEMP_INSERT = "INSERT INTO test(x) VALUES (:x)";

    @Autowired
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    public int batchTestInsert(SqlParameterSource[] parameters) {
        return Arrays.stream(
            namedParameterJdbcTemplate.batchUpdate(TEMP_INSERT, parameters)
        ).sum();
    }
}

The inserts are executed all right however the returned result it is an unexpected negative value of -6. This is because every single element in the array returned bu the namedParameterJdbcTemplate equals to -2.

My question is am I misunderstand the way to use this spring template or it is a Spring bug (hard to believe). If I am not using it properly what would be the correct way of doing it.

Here is the unit tests to reproduce the issue:

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes=MyConfiguration.class)
public class TestRepositoryTest {
    @Autowired
    private TestRepository repository;

    @Test
    public void shoudReturnTheNumberOfInsertedRecords() {
        assertEquals(3, repository.batchTestInsert(new SqlParameterSource[] {
                new MapSqlParameterSource("x", 1),
                new MapSqlParameterSource("x", 2),
                new MapSqlParameterSource("x", 3)
        }));
    }
}


java.lang.AssertionError: 
Expected :3
Actual   :-6
<Click to see difference>

Upvotes: 1

Views: 10894

Answers (1)

Ivan
Ivan

Reputation: 8758

If you are using Oracle database then -2 means that SQL statement was executed successfully but information about exact rows affected is not available (ExecuteBatch method return array of value -2 in java)

To test how many rows were inserted you could use namedJdbcTemplate to execute SELECT statement and check its results.

You need to inject 

@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

into your test class and then use it to query database.

Integer count = namedParameterJdbcTemplate.queryForObject("SELECT COUNT(1) FROM test", new HashMap<String, Object>, Integer.class); //I pass empty HashMap since query doesn't need parameters
assertEquals(3, count);

Upvotes: 2

Related Questions