Batch Insert with SequenceGenerator

Question

I have 2 objects which are related to each other as One OrderHeader has many OrderItems. I'm creating an OrderHeader and adding 100 OrderItems to it, then I'm trying to persist it using batch insert but it simply doesn't work. My objects use a SequenceGenerator to create their ID's.

I tried using JDBCTemplate, but as I'm using SequenceGenerator, the objects have their ID null when inserting them into the database.

I'm using SpringBoot 2.7.0 and here are my application.properties settings Does anybody have any suggestion to make this batch insert work?

application.properties

spring.datasource.driver-class-name=org.postgresql.Driver
spring.datasource.url=jdbc:postgresql://XXX.XXX.XXX.XXX:5432/db-main?reWriteBatchedInserts=true
spring.datasource.username=dbuser
spring.datasource.password=dbpassword

spring.jpa.properties.hibernate.jdbc.batch_size=100
spring.jpa.properties.hibernate.order_inserts=true
spring.jpa.properties.hibernate.order_updates=true
spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true
spring.jpa.properties.hibernate.generate_statistics=true

Here are objects

@MappedSuperclass
public abstract class BaseAbstractEntity implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "entity_generator")
    @SequenceGenerator(name="entity_generator", sequenceName = "entity_seq", allocationSize=50)
    @Column(name = "id", updatable = false, nullable = false)
    protected long id;

    public long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    @Override
    public int hashCode() {
        final int prime = 31;
        int result = 1;
        result = prime * result + (int) (id ^ (id >>> 32));
        return result;
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj)
            return true;
        if (obj == null)
            return false;
        if (getClass() != obj.getClass())
            return false;
        BaseAbstractEntity other = (BaseAbstractEntity) obj;
        if (id != other.id)
            return false;
        return true;
    }

}

public class OrderHeader extends BaseAbstractEntity {
    @ManyToOne
    private User user;
    
    @Temporal(TemporalType.TIMESTAMP)
    private Date time;
    
    @OneToMany(mappedBy = "orderHeader", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<OrderItem> items;
    
    public User getUser() {
        return user;
    }
    public void setUser(User User) {
        this.user = kUser;
    }
    public Date getTime() {
        return time;
    }
    public void setTime(Date time) {
        this.time = time;
    }
    public List<OrderItem> getItems() {
        return items;
    }
    public void setItems(List<OrderItem> items) {
        this.items = items;
    }
}

public class OrderItem extends BaseAbstractEntity {
    
    @ManyToOne(optional = false)
    @JoinColumn(nullable = false)
    private OrderHeader orderHeader;

    @Column( precision = 11, scale = 2)
    private BigDecimal price;
    
    @Column(nullable = false)
    private String description;
    
    public OrderHeader getOrderHeader() {
        return orderHeader;
    }
    public void setOrderHeader(OrderHeader orderHeader) {
        this.orderHeader = orderHeader;
    }

    public BigDecimal getPrice() {
        return price;
    }
    public void setPrice(BigDecimal price) {
        this.price = price;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
}

---

This is the piece of code I'm using to persist the object into the database.

        OrderHeader orderHeader = new orderHeader();
        orderHeader.setUser(user);
        orderHeader.setTime(new Date());
        orderHeader.setItems(new ArrayList<>());

        for (int i = 0; i < 100; i++) {
            OrderItem item = new OrderItem();
            item.setOrderHeader(orderHeader);
            item.setPrice(new BigDecimal(173));
            item.setDescription("Description");
            orderHeader.getItems().add(item);
        }

        this.orderHeaderRepository.save(orderHeader);

---

This is what I see in the console

Hibernate: select nextval ('entity_seq')
Hibernate: select nextval ('entity_seq')
Hibernate: select nextval ('entity_seq')
Hibernate: insert into orderheader (time, user_id, id) values (?, ?, ?)
Hibernate: insert into orderitem (order_header_id, price, description, id) values (?, ?, ?, ?)
Hibernate: insert into orderitem (order_header_id, price, description, id) values (?, ?, ?, ?)
Hibernate: insert into orderitem (order_header_id, price, description, id) values (?, ?, ?, ?)
Hibernate: insert into orderitem (order_header_id, price, description, id) values (?, ?, ?, ?)
Hibernate: insert into orderitem (order_header_id, price, description, id) values (?, ?, ?, ?)
Hibernate: insert into orderitem (order_header_id, price, description, id) values (?, ?, ?, ?)
Hibernate: insert into orderitem (order_header_id, price, description, id) values (?, ?, ?, ?)
.
. 100x
.
Hibernate: insert into orderitem (order_header_id, price, description, id) values (?, ?, ?, ?)

    2388737292 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    2415478608 nanoseconds spent preparing 142 JDBC statements;
    28768578308 nanoseconds spent executing 142 JDBC statements;
    0 nanoseconds spent executing 0 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    16329894081 nanoseconds spent executing 3 flushes (flushing a total of 2211 entities and 2207 collections);
    42104100 nanoseconds spent executing 3 partial-flushes (flushing a total of 2110 entities and 2110 collections)

---

OrderHeaderRepository

@Repository
public interface OrderHeaderRepository extends JpaRepository<OrderHeader, Long>{

}

---

Sequence Definition

select * from information_schema.sequences where sequence_name='entity_seq'
sequence_catalog|sequence_schema|sequence_name|data_type|numeric_precision|numeric_precision_radix|numeric_scale|start_value|minimum_value|maximum_value      |increment|cycle_option|
----------------|---------------|-------------|---------|-----------------|-----------------------|-------------|-----------|-------------|-------------------|---------|------------|
ordersapp-main  |public         |entity_seq   |bigint   |               64|                      2|            0|9054600    |1            |9223372036854775807|50       |NO          |

Answer 1

After debugging hibernate, I found this piece of code below in JdbcEnvironmentInitiator.java, then I realized I had set this setting spring.jpa.properties.hibernate.temp.use_jdbc_metadata_defaults=false to get rid of the annoying CLOB warning, as suggested in the thread Disabling contextual LOB creation as createClob() method threw error.

Once I set it to true, the batch inserts worked again, and now I'm using hibernate.jdbc.lob.non_contextual_creation = true to not see the CLOB warning again.

        // 'hibernate.temp.use_jdbc_metadata_defaults' is a temporary magic value.
        // The need for it is intended to be alleviated with future development, thus it is
        // not defined as an Environment constant...
        //
        // it is used to control whether we should consult the JDBC metadata to determine
        // certain Settings default values; it is useful to *not* do this when the database
        // may not be available (mainly in tools usage).
        boolean useJdbcMetadata = ConfigurationHelper.getBoolean(
                "hibernate.temp.use_jdbc_metadata_defaults",
                configurationValues,
                true
        );

        if ( useJdbcMetadata ) {
            final JdbcConnectionAccess jdbcConnectionAccess = buildJdbcConnectionAccess( configurationValues, registry );
            try {
                final Connection connection = jdbcConnectionAccess.obtainConnection();
                try {
                    final DatabaseMetaData dbmd = connection.getMetaData();

Thank you