Inserting data into multiple tables with spring jdbcTemplate

Question

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.

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()
    }
});

Comments

[keepmoving]

Dear Nailgun, First Question - Will it be the right approach when multiple thread is concurrently running to insert this kind of data.means getting the last id. Second question - how i will insert role. If i use jdbcTemplate.update(). then again it is thousands of hitting towards db. Thats why i was looking some solution to person this two insertion into single batch execution.

[Nailgun]

1) LAST_INSERT_ID() returns last id inserted from the current connection so that's ok 2) Then you need to little change your code to use public <T> int[][] batchUpdate(String sql, final Collection<T> batchArgs, final int batchSize, final ParameterizedPreparedStatementSetter<T> pss) method from JdbcTemplate. I recommend to set batchSize parameter to 1000.

[keepmoving]

Thanks, i hope it will work. I also did same thing as are telling.

[gene b.]

In Postgres the Multiline Insert with ; doesn't work: PSQLException: Too many update results were returned. See: postgresql.org/message-id/…

[7er]

you can use CTE stackoverflow.com/questions/37567741/…

[Waheed Khan]

Hi, I have a similar case to insert data into two different tables but I am getting PreparedStatementCallback; bad SQL grammar at the second insert statement. any comments please. code is exact the same as above.