jdbcTemplateObject.batchUpdate() raised a TransientDataAccessResourceException exception, parameter index was out of range (4 > 3)

Question

Using Java 1.8 and Spring Framework 4.0.3-RELEASE, I am trying to insert a row into a MySQL database after getting a value from an external source.

Try this:

private static JdbcTemplate jdbcTemplateObject = null;
private static final String INSERT_QUERY = "insert into order_table(id,order_id,created_time,updated_time)VALUES(?,?,now(),now())";

parseFeedAndStoreIntoDB() {
    List<Object[]> insertData = new ArrayList<>();
    SqlRowSet sqlRowSet = null;

    // id, order_id, created_time, updated_time有值
    insertData.add(new Object[] {id, order_id, created_time, updated_time});
    if (insertData.size() > 0) {
        // 这里出错了
        jdbcTemplateObject.batchUpdate(INSERT_QUERY, insertData);
    }
}

When I run this method, I get the following exception:

Exception in parseFeedAndStoreIntoDB() method
org.springframework.dao.TransientDataAccessResourceException: PreparedStatementCallback; SQL 
[insert into order_table(id,order_id,created_time,updated_time)VALUES(?,?,now(), now());]; Parameter index out of range (4 > 3).; nested exception is java.sql.SQLException: 
Parameter index out of range (4 > 3).

I have counted the number of rows, there are 4 rows in both my Java code and the MySQL database table I created.

Answer 1

Your query has bind parameters on id and order_id, the other two fields are set to now() in the query. Will

insertData.add(new Object[] {id, order_id, created_time, updated_time});

changed to

insertData.add(new Object[] {id, order_id});

or will be

private static final String INSERT_QUERY = "insert into "
        + "order_table(id,order_id,created_time,updated_time) "
        + "VALUES(?,?,now(),now())";

changed to

private static final String INSERT_QUERY = "insert into "
        + "order_table(id,order_id,created_time,updated_time) "
        + "VALUES(?,?,?,?)";