I need to do Bulk inserts(nearly 10000) in my MySQL database. I am using JPA/hibernate and spring boot. I read performing bulk insert/update in hibernate from hibernate documentation, I think my code is not working as it is sequentially inserting the hibernate queries instead of performing them in a batch insert. Below is my code. Am I missing something?
Here is my DataSource configuration.
@Component
public class Datasource {
@Autowired
EnvConfiguration configuration;
private static final Logger logger = LoggerFactory.getLogger(Datasource.class);
@Bean
public DataSource dataSource(){
logger.info("DataSource Bean creation...");
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(configuration.getDBDriver());
dataSource.setUrl("jdbc:mysql://"+configuration.getDBIp()+":"+configuration.getDBPort()+"/"+configuration.getDBName()+"?autoReconnect=true&useSSL=false");
dataSource.setUsername(configuration.getDBUser());
dataSource.setPassword(configuration.getDBPass().trim());
return dataSource;
}
@Bean
public HibernateJpaSessionFactoryBean sessionFactory() {
return new HibernateJpaSessionFactoryBean();
}
}
Code for my Role domain
//Role.java
@Entity
@Table(name = "Role",uniqueConstraints = @UniqueConstraint(
columnNames = { "roleName"}))
public class Role {
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long roleId;
@NotNull
private String roleName;
public Role(){}
public Role(String roleName){
this.roleName = roleName;
}
public Long getRoleId() {
return roleId;
}
public void setRoleId(Long roleId) {
this.roleId = roleId;
}
public String getRoleName() {
return roleName;
}
public void setRoleName(String roleName) {
this.roleName = roleName;
}
}
Below is my service code. Here I am manually flushing the session. I have added a sleep function in order to find whether insert query executed one by one or they are executing in batch of 10 as happen in JDBC batch.
@Service
public class RoleService{
@Autowired
private SessionFactory factory;
@Autowired
private DataSource source;
private static final Logger logger = LoggerFactory.getLogger(WalletService.class);
public void insertRole(Collection<RegisterWallet> walletMetaCollection){
if(factory==null){
System.out.println("factory is null");
}else{
System.out.println("factory is working");
Session session = factory.openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i<100000; i++ ) {
Role role=new Role(""+i);
session.persist(role);
System.out.println("this is the role id "+role.getRoleId());
try {
Thread.sleep(1000);
} catch (InterruptedException e){
// TODO Auto-generated catch block
e.printStackTrace();
}
if ( i % 10 == 0 ) { //20, same as the JDBC batch size
//flush a batch of inserts and release memory:
session.flush();
session.clear();
}
}
tx.commit();
session.close();
}
}
}
As per my understanding over batch operation, 10 roles should be inserted at once, decreasing number of jdbc round trips used. But output of the above code is quiet unexpected. It is executing one insert per session.persist(..) call.
//This is log of the above code.
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 14
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 15
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 16
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 17
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 18
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 19
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 20
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 21
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 22
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 23
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 24
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 25
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 26
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 27
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 28
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 29
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 30
Hibernate: insert into role (active, role_description, role_name) values (?, ?, ?)
this is the role id 31
</pre>
-------------------------------------------------------------
Following is my application.properties configuration
spring.jpa.properties.hibernate.current_session_context_class=org.springframework.orm.hibernate4.SpringSessionContext
spring.jpa.properties.hibernate.jdbc.batch_size=10
Am I missing something?
Please help.
