Spring-data-jpa: batch inserts are not working

user_5284890 0
Viewed 1225

I used this article to implement batch inserts with spring-data-jpa for one of my entities. I am using MS SQL DB.

I changed the primary key generation strategy from GenerationType.IDENTITY to GenerationType.SEQUENCE:

@Entity
@Table(name = "Answers")
public class AnswerDMO implements java.io.Serializable {
    private static final long serialVersionUID = 1L;
    private long id;
    
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "answer_new_generator")
    @SequenceGenerator(name = "answer_new_generator", sequenceName = "answer_new_sequence", allocationSize = 15)
    @Column(name = "Id", unique = true, nullable = false)
    public long getId() {
        return this.id;
    }
    public void setId(long id) {
        this.id = id;
    }

The service method saves list of answer entities by calling <S extends T> List<S> save(Iterable<S> entities);:

@Transactional
public List<AnswersDMO> saveAnswers () {
       /* other logic..... */
       if(!CollectionUtils.isEmpty(answersToSave)) {
            supplementalAnswerNewDAO.save(answersToSave);
        }
}

To tell Hibernate to use batching while inserts and check it is actually working, I set following JPA properties:

<prop key="hibernate.generate_statistics">true</prop>
<prop key="hibernate.hibernate.jdbc.batch_size">5</prop>
<prop key="hibernate.order_inserts">true</prop>

However, upon inserting 11 entities the JDBC batches count is 0: enter image description here

(Log4j2-TF-1-AsyncLogger[AsyncContext@44f54c5e]-1)     0 nanoseconds spent releasing 0 JDBC connections;
(Log4j2-TF-1-AsyncLogger[AsyncContext@44f54c5e]-1)     65217828 nanoseconds spent preparing 6 JDBC statements;
(Log4j2-TF-1-AsyncLogger[AsyncContext@44f54c5e]-1)     727057697 nanoseconds spent executing 6 JDBC statements;
(Log4j2-TF-1-AsyncLogger[AsyncContext@44f54c5e]-1)     0 nanoseconds spent executing 0 JDBC batches;

Hibernate queries:

08:02:35,739 INFO  [stdout] (default task-1) Hibernate: 
08:02:35,740 INFO  [stdout] (default task-1)     select
08:02:35,742 INFO  [stdout] (default task-1)         next value for supplemental_answer_new_sequence

08:02:57,941 INFO  [stdout] (default task-1) Hibernate: 
08:02:57,941 INFO  [stdout] (default task-1)          insert 
08:02:57,941 INFO  [stdout] (default task-1)         into
08:02:57,941 INFO  [stdout] (default task-1)             my_schema.dbo.Answers
08:02:57,941 INFO  [stdout] (default task-1)             (CreatedDate, InstanceId, ProfileId, QuestionId, UpdatedDate, Value, Id) 
08:02:57,941 INFO  [stdout] (default task-1)         values
08:02:57,941 INFO  [stdout] (default task-1)             (?, ?, ?, ?, ?, ?, ?)

Q1: Can anyone tell me why it is not working?

Q2: Also, after I switched from GenerationType.IDENTITY to GenerationType.SEQUENCE, I started getting following exceptions in the logs:

[jdbc.spi.SqlExceptionHelper] – SQL Error: 544, SQLState: S0001
[jdbc.spi.SqlExceptionHelper] – Cannot insert explicit value for identity column in table 'Answers' when IDENTITY_INSERT is set to OFF.

Is it because id column has identity property? If yes, what would be the steps to solve it?

UPDATED: I just noticed that I have a wrong property name <prop key="hibernate.hibernate.jdbc.batch_size">5</prop>, it should be <prop key="hibernate.jdbc.batch_size">5</prop>.

So the main problem now is the

[jdbc.spi.SqlExceptionHelper] – SQL Error: 544, SQLState: S0001
[jdbc.spi.SqlExceptionHelper] – Cannot insert explicit value for identity column in table 'Answers' when IDENTITY_INSERT is set to OFF.

Any ideas how I can solve it?

spring-data-jpa jdbc jpa hibernate java
1 Answers

I was facing the exact same issue. The problem is that in the tables created by the JPA, the ID columns are flagged as IDENTITY columns and as a consequence the exlpicit values for these ID columns in the insert statements that are generated by the JPA are blocked by the MSSql Server. Another interesting fact is that if you have created the "problematic" tables via the IntelliJ Database functionality and you have annotated the ID column as Primary Key and Auto-Increment then it is flagged as IDENTITY column as well. So the solution is the following:

  1. Right click on the "problematic" table -> SQL Scripts -> Generate DDL to query console
  2. Drop the table
  3. Delete the "identity" keyword from the generated query
  4. Execute the altered query
  5. Restart the sequence in the DB: ALTER SEQUENCE answer_new_sequence RESTART WITH 1;
  6. I strongly suggest that the allocationSize value matches the hibernate.hibernate.jdbc.batch_size value as this will result in the minimum passed over values from the sequence. Also the allocationSize must match the step of the sequence in the DB. Hopefully this helps. Cheers.
user_154157120
Related