SpringBoot JdbcTemplate batch operation example code

Preface

When we are doing back-end service development, especially when big data is inserted in batches, ordinary ORM frameworks (Mybatis, hibernate, JPA) cannot meet the performance requirements of the program. Of course, it is impossible for us to use native JDBC to operate, so that although the efficiency will be high, the complexity will increase.

Considering comprehensively, we use JdbcTemplate in Spring and named ParameterJdbcTemplate to perform batch operations.

Before the transformation

Before we start the explanation, let’s first look at how JPA was operated in batches.

Entity User:

 public class AppStudent { private Integer id; private Integer classId; private String name; private Integer age; //pseudocode, omitted constructs and get, set methods}

DynamicQuery pseudocode:

 @Repositorypublic class DynamicQueryImpl implements DynamicQuery { @PersistenceContext private EntityManager em; public EntityManager getEntityManager() { return em; } //In fact, it is just for loop and using EntityManager's persist method to save @Override public <T> void saveList(List<T> resultList) { for (int i = 0; i < resultList.size(); i++) { T t = resultList.get(i); em.persist(t); } }}

After the transformation

JdbcTemplate

The main methods provided by JdbcTemplate:

1. execute method: can be used to execute any SQL statement, generally used to execute DDL statements;
2. update method and batchUpdate method: the update method is used to execute new statements, modify, delete, etc.; the batchUpdate method is used to execute batch-related statements;
3. query method and queryForXXX method: used to execute query-related statements;
4. call method: used to execute stored procedures and function-related statements.

We just need to use @Autowired for injection in the jdbcTemplate class:

 @Autowiredprivate JdbcTemplate jdbcTemplate;

Batch insert operation:

 public void batchSave(){ List<Object[]> batchArgs=new ArrayList<Object[]>(); batchArgs.add(new Object[]{1,"Xiao Ming",21}); batchArgs.add(new Object[]{2,"Xiaohong",22}); batchArgs.add(new Object[]{3,"Lucy",23}); String sql = "insert into user (username,password) values (?,?)"; jdbcTemplate.batchUpdate(sql, batchArgs);}

The above basically implements the batch insert function, but when there are many database fields, it may not be so good to encode it in the form of a placeholder. Here spring also provides SimpleJdbcTemplate (Spring3.1+ is marked as outdated later, but it is completely removed in Spring 4.3. The latter one can fully meet the needs) and NamedParameterJdbcTemplate template engine.

NamedParameterJdbcTemplate

I believe that students who have used Hibernate know that it can be used in HQL? Or: * to configure query parameters externally. In the Spring JDBC framework, a way to bind SQL parameters is also provided, using named parameters.

We just need to use @Autowired for injection in the NamedParameterJdbcTemplate class:

 @Autowiredprivate NamedParameterJdbcTemplate namedParameterJdbcTemplate;

Batch insert operation:

 public void batchSave(){ List<User> list = new ArrayList<User>(); //Add new user list.add(new AppStudent(1,"Zhang San",21)); list.add(new AppStudent(1,"Li Si",22)); list.add(new AppStudent(1,"Wang Ermazi",23)); //Batch to array SqlParameterSource[] beanSources = SqlParameterSourceUtils.createBatch(list.toArray()); String sql = "INSERT INTO app_student(class_id,name,age) VALUES (:classId,:name,:age)"; namedParameterJdbcTemplate.batchUpdate(sql, beanSources);}

Finally, we use System.currentTimeMillis() to compare and print the execution time before and after the specific transformation.

 long start = System.currentTimeMillis();//Remodel the code before and after, and supplement it yourself long end = System.currentTimeMillis();System.out.println("Second time:"+(end-start));

Fast is definitely fast. As for how fast it is, it needs to make a relevant comparison based on the data volume and machine configuration.

Project source code: https://gitee.com/52itstyle/spring-data-jpa

The above is all the content of this article. I hope it will be helpful to everyone's learning and I hope everyone will support Wulin.com more.