Batch Insert in hibernate generating mulitiple queries

Question

I have 10 records and I am trying to insert 5 records at a time using batch insert in hibernate.But the problem here is after every session.save() it is generating insert query. So if i want to insert 1000 records it will generate 1000 insert queries. I wanted to insert 5 records at a time. Below is the code snippet

public void saveobjects(List<DBDimAssetGroup> objects) {
     Session session = super.getsession();
    for (int i = 1; i <= 10; i++) {
        System.out.println("Statement Queued : " + i);
        DBDimAssetGroup obj = objects.get(i);
        session.save(obj);      
        if (i % 5 == 0) {
            session.flush();
            session.clear();        
        }
    }
    session.close();
}

Below is the ScreenShot of console generating 10 queries for 10 inserts

Below is the Hibernate Configuration

EDIT 1: After changing GenerationType to Sequence in Entity Class , I am getting below error

Below is the Entity class

EDIT 2: Even after setting generationtype to sequence It is generating multiple queries Below is the console

Below is the Entity Class

@Id
@GeneratedValue(strategy=GenerationType.SEQUENCE,generator="users_seq")
@SequenceGenerator(name="users_seq" , allocationSize = 1, sequenceName = "DIM_ASSET_GROUP_SEQ")
@Column(name="ASSET_GROUP_ID", updatable = false, insertable = false)
private int assetgroupid;

I didn't understand your problem, is the query being printed after the insert your problem? — Arun Sudhakaran, Apr 18 '23 at 06:04
@ArunSudhakaran If i am inserting 10 insert queries in db with batch size 5 , I would like to see only 2 insert queries in console — Geeth, Apr 18 '23 at 08:46
https://stackoverflow.com/questions/42002249/hibernate-how-to-verify-if-batch-insert-is-really-performed https://stackoverflow.com/questions/46184020/hibernate-batch-processing — Arun Sudhakaran, Apr 18 '23 at 09:59
Please use code tags to post code and not images. ALso you are using MySQL which needs additional configuration on the driver level for batching. At least according to your tags you are using MySQL and not SQL Server). Finally check at the database side what you are actually receiving, the logging is from hibernate while the actual batching might take place at the driver level (which isn't being logged), this is for instance the case with MySQL. Hibernate will log indidivual queries while in the end it will be a single one. — M. Deinum, Apr 19 '23 at 09:07

V-CHO · Answer 1 · 2023-04-18T09:16:46.487

0

This feature works only if hibernate_sequence is set for ID's generation process for your entities, not with AUTO or IDENTITY managed by DB provider.

Update (example):

@Id @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="users_seq") @SequenceGenerator(name="users_seq" , allocationSize = 1)

edited Apr 18 '23 at 09:16

answered Apr 18 '23 at 05:53

V-CHO

58
9

@V_CHO I have edited question with the observations after changing generationtype to sequence, Can you pls look over it – Geeth Apr 18 '23 at 08:51
Its also recommended to define schema in entity pojo kinda @Table(name="bills", schema = "bankdemo") – V-CHO Apr 18 '23 at 09:13
@V_CHO Do i need to create dbo.hibernate_sequence sequence in Database as well? – Geeth Apr 18 '23 at 15:34
spring.jpa.hibernate.ddl-auto=create should work; hibernate.id.new_generator_mappings=true probably would be required too, depends on Hibernate's version. Anyway its better to take a closer look at docs or articles from aces. – V-CHO Apr 19 '23 at 05:27
@V_CHO Will it fire a select query for sequence table every time at session.save()?? – Geeth Apr 19 '23 at 05:47
Kindly check Edit 2 – Geeth Apr 19 '23 at 09:21

Batch Insert in hibernate generating mulitiple queries

1 Answers1