Spring insert using jdbc

Question

I have a table in SQLYog where one column is varchar limit to 200 length. I wrote the method below for the insert, but when testing the code the column "Name" is not been filled by the data in the DB.

@Override
public void insert(final List<InfoRow> rows) {
    String sql = "INSERT INTO info_table (ID, NAME, AGE) VALUES (?, SUBSTRING(?, 0, 200), ?)";

    jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {

        @Override
        public void setValues(PreparedStatement ps, int i)
                throws SQLException {
            InfoRow row= rows.get(i);
            ps.setInt(1, (int) row.getId());
            ps.setString(2, row.getName());
            ps.setInt(3,(int) row.getAge());
        }

        @Override
        public int getBatchSize() {
            return rows.size();
        }
    });

}

What is the correct way to validate the 'Name' size while insert?

Thanks!

Answer 1

Substring expressions in MySQL (this is a guess based on the sqlyog tag) are 1 based, not 0 based. Try this instead:

SUBSTRING(?, 1, 200)

Generally, I would keep validation logic out of the database layer, and handle "bad input" in the application layer. This allows me to log the fact that data would be dropped/truncated.

But this approach is fine.