Inserting data into multiple tables with spring jdbcTemplate

java, mysql, spring, spring-jdbc

Solution

You can use multilane statements and `LAST_INSERT_ID()` MySql function:

String sql = "insert into role(name, code) values(?,?);" +
    "insert into person(first_name, last_name, description, role_id) values(?,?,?,(SELECT LAST_INSERT_ID()));";

int[] arr = template.batchUpdate(sql, new BatchPreparedStatementSetter() {

    @Override
    public void setValues(PreparedStatement ps, int i) throws SQLException {
        Role role = roles.get(i);
        Person person = list.get(i);
        ps.setObject(1, role.getName());
        ps.setObject(2, role.getCode();
        ps.setObject(3, person.getFirstName());
        ps.setObject(4, person.getLastName());
        ps.setObject(5, person.getDescription());
    }

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

Problem

I am working to insert n number of records into two tables with using java, spring jdbc template. some like this assume daos.xml correctly configured. ``` ApplicationContext ctxt = new ClassPathXmlApplicationContext("daos.xml"); JdbcTemplate template = (JdbcTemplate) ctxt.getBean("jdbcTemplate"); final List<Person> list = new ArrayList<>(); final List<Role> roles = new ArrayList<>(); for(int i =1; i<=100; i++){ Person item = new Person(); item.setFirstName("Naveen" + i); item.setLastName("kumar" + i); item.setDescription("D" + i); list.add(item); Role role = new Role(); role.setName("Admin"); role.setCode("c" + i); roles.add(role); } String sql = "insert into person(first_name, last_name, description) values(?,?,?)"; int[] arr = template.batchUpdate(sql, new BatchPreparedStatementSetter() { @Override public void setValues(PreparedStatement ps, int i) throws SQLException { Person person = list.get(i); ps.setObject(1, person.getFirstName()); ps.setObject(2, person.getLastName()); ps.setObject(3, person.getDescription()); } @Override public int getBatchSize() { return list.size() } }); ``` I am also configured Transaction Manager. So my question is how can i insert data into both person and role table using batch. Because Person can have role. when i insert into person it require role id to be insert together. In this case person insertion query will looks like this. ``` String sql = "insert into person(first_name, last_name, description, role_id) values(?,?,?, ?)"; ``` I want to perform it into batch batch. because in my case i have min 10k person list to parse using file. So it can be a performance killer i insert role into table than get it and they insert person again.

Original source