Hibernate批量插入失效问题排查求助

问题场景 #

使用saveAll批量保存Question实体到PostgreSQL数据库时，Hibernate未启用批量插入。已将ID生成策略从IDENTITY改为SEQUENCE，并在YAML配置中设置jdbc.batch_size: 25，但日志仍输出多条独立的insert语句。Hibernate统计信息显示执行了3个批次，但实际SQL无批量操作表现。尝试在数据库URL后添加?reWriteBatchedInserts=true也未解决问题。

相关代码与配置 #

实体类代码 #

@Entity
@Table(name = "questions")
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class Question {

    @Id
    @GeneratedValue
    @Column(name = "id")
    private long id;

    @Column(name = "text")
    private String text;

    @Column(name = "is_question_with_text_answer")
    private boolean isQuestionWithTextAnswer;

    @ManyToOne
    @JoinColumn
    private ActualQuiz actualQuiz;
}

服务方法代码 #

@Transactional
@Override
public void saveQuestions(List<QuestionRequestDTO> questionsDTO) {
    var actualQuiz = actualQuizService.findActualQuiz();
    var questionList = new ArrayList<Question>();
    for (QuestionRequestDTO question : questionsDTO) {
        questionList.add(Question.builder()
                .text(question.getQuestion())
                .isQuestionWithTextAnswer(question.isQuestionWithTextAnswer())
                .actualQuiz(actualQuiz)
                .build());
    }
    questionRepository.saveAll(questionList);
}

Hibernate统计信息 #

275400 nanoseconds spent acquiring 1 JDBC connections;
0 nanoseconds spent releasing 0 JDBC connections;
954100 nanoseconds spent preparing 12 JDBC statements;
4377100 nanoseconds spent executing 9 JDBC statements;
2998100 nanoseconds spent executing 3 JDBC batches;
0 nanoseconds spent performing 0 L2C puts;
0 nanoseconds spent performing 0 L2C hits;
0 nanoseconds spent performing 0 L2C misses;
32059500 nanoseconds spent executing 3 flushes (flushing a total of 17 entities and 0 collections);
476700 nanoseconds spent executing 7 partial-flushes (flushing a total of 7 entities and 7 collections)

日志输出的SQL语句 #

Hibernate: insert into questions (actual_quiz_id,is_question_with_text_answer,text,id) values (?,?,?,?)
Hibernate: insert into questions (actual_quiz_id,is_question_with_text_answer,text,id) values (?,?,?,?)
Hibernate: insert into questions (actual_quiz_id,is_question_with_text_answer,text,id) values (?,?,?,?)

配置文件（application.yml） #

spring:
  datasource:
    driver-class-name: org.postgresql.Driver
    username: 
    password: 
    url: jdbc:postgresql://dbUrl:5432/quiz_bot
  jpa:
    show-sql: true
    generate-ddl: false
    hibernate:
      ddl-auto: none
    properties:
      hibernate:
        jdbc:
          batch_size: 25
          order_inserts: true
          order_updates: true
        generate_statistics: true
  liquibase:
    change-log: classpath:liquibase/db.changelog-master.xml

解决方案 #

1. 显式配置序列生成策略与分配大小 #

当前实体类的@GeneratedValue未指定策略，可能导致Hibernate仍使用默认的IDENTITY（取决于数据库方言）。需显式配置为SEQUENCE，并设置序列分配大小与批量大小一致：

@Id
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "question_seq")
@SequenceGenerator(
    name = "question_seq",
    sequenceName = "questions_id_seq", // 需与数据库中实际序列名一致
    allocationSize = 25 // 必须与hibernate.jdbc.batch_size值相同
)
@Column(name = "id")
private long id;

注意：需确保数据库中存在questions_id_seq序列（可通过Liquibase或手动创建），分配大小匹配后，Hibernate会一次性获取25个ID，避免每次插入都查询序列，为批量插入创造条件。

2. 验证PostgreSQL驱动配置 #

确保reWriteBatchedInserts=true正确添加到数据库URL，且驱动版本不低于42.2.19（该版本后此参数稳定生效）：

spring:
  datasource:
    url: jdbc:postgresql://dbUrl:5432/quiz_bot?reWriteBatchedInserts=true

此参数会让PostgreSQL驱动将多条独立的insert语句合并为真正的批量插入请求，降低数据库交互次数。

3. 确认Hibernate批量配置生效 #

检查配置中是否包含以下参数（已配置的无需修改）：

hibernate:
  jdbc:
    batch_size: 25
    order_inserts: true # 强制Hibernate按实体类型排序插入语句，确保同一类型的插入能被批量处理
    order_updates: true

order_inserts=true是关键，它确保Hibernate将相同实体类型的插入语句放在一起，才能触发批量操作。

4. 理解日志与统计信息的差异 #

Hibernate的show-sql日志会打印每条预编译的insert语句，但实际执行时驱动会将它们合并为批量请求。Hibernate统计信息中的executing 3 JDBC batches是真实的批量执行次数，说明批量逻辑已生效，日志显示的多条语句只是预编译的模板，并非实际发送的SQL。

内容的提问来源于stack exchange，提问作者KripDo