I’m using JPA (3.1.1) on all my project and I managed to make everything run smoothly so far, but I’m encountering issues on bacth inserting in an Oracle GTT (global temporary table). (Oracle is in 19C)

My table has no PK (id) and only contains four columns; I need to load up to millions of keys in it, and then run queries on the database side for efficiency.

My problem lies with the loading process; it is very slow because Hibernate runs the insert statements one by one.

Here is what my log looks like. I would expect some kind of batching, like “INSERT ALL”, and definitely less time communicating with the server. At least I got rid of the select before inserts with the “isNew()” logic of Persistable implementation…

2024-12-19 18:33:57,594 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger [scheduling-3] insert into XXX.MY_TMP_TABLE (ADDITIONAL_DATA,AN_ENUM,A_BOOLEAN,A_STRING) values (?,?,?,?)
2024-12-19 18:33:57,594 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [1] as [VARCHAR] - [xx1]
2024-12-19 18:33:57,594 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [2] as [VARCHAR] - [I]
2024-12-19 18:33:57,594 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [3] as [BOOLEAN] - [true]
2024-12-19 18:33:57,594 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [4] as [VARCHAR] - [abcd]
2024-12-19 18:33:57,595 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger [scheduling-3] insert into XXX.MY_TMP_TABLE (ADDITIONAL_DATA,AN_ENUM,A_BOOLEAN,A_STRING) values (?,?,?,?)
2024-12-19 18:33:57,595 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [1] as [VARCHAR] - [xx1]
2024-12-19 18:33:57,595 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [2] as [VARCHAR] - [I]
2024-12-19 18:33:57,595 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [3] as [BOOLEAN] - [true]
2024-12-19 18:33:57,596 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [4] as [VARCHAR] - [abce]
2024-12-19 18:33:57,596 DEBUG org.hibernate.engine.jdbc.spi.SqlStatementLogger [scheduling-3] insert into XXX.MY_TMP_TABLE (ADDITIONAL_DATA,AN_ENUM,A_BOOLEAN,A_STRING) values (?,?,?,?)
2024-12-19 18:33:57,596 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [1] as [VARCHAR] - [xx1]
2024-12-19 18:33:57,596 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [2] as [VARCHAR] - [I]
2024-12-19 18:33:57,596 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [3] as [BOOLEAN] - [true]
2024-12-19 18:33:57,597 TRACE org.hibernate.type.descriptor.JdbcBindingLogging [scheduling-3] binding parameter [4] as [VARCHAR] - [abcf]

Here is my entity:

@Entity
@Table(schema = "XXX", name = "MY_TMP_TABLE")
public class MyEntity implements Persistable<MyPK> {

    @EmbeddedId
    private MyPK id;

    @Column(name = "ADDITIONAL_DATA", nullable = false)
    private String additionalData;

    @Transient
    private boolean isNew;

    /**
     * Creator with "isNew" flag to avoid SELECT before INSERT in batch treatments
     * 
     * @param aIsNew true if we are certain the key does not exist in the database
     */
    public MyEntity(boolean aIsNew) {
        super();
        this.isNew = aIsNew;
    }

    /**
     * @return the composite ID
     */
    @Override
    public MyPK getId() {
        return id;
    }

    /**
     * Sets the composite ID
     * 
     * @param anId the ID
     */
    public void setId(MyPK anId) {
        this.id = anId;
    }

    /**
     * @return the additional data
     */
    public String getAdditionalData() {
        return additionalData;
    }

    /**
     * @param anAdditionalData the additional data to set
     */
    public void setAdditionalData(String anAdditionalData) {
        this.additionalData = anAdditionalData;
    }

    @Override
    public boolean isNew() {
        return isNew;
    }
}

And embedded Id:

@Embeddable
public class MyPK {
    @Column(name = "A_STRING", nullable = false)
    private String aString;

    @Column(name = "AN_ENUM", nullable = false)
    @Convert(converter = AConverter.class)
    private AnEnum anEnum;

    @Column(name = "A_BOOLEAN", nullable = false)
    private boolean aBoolean;

    /**
     * @return the String
     */
    public String getAString() {
        return aString;
    }

    /**
     * @param aString the String to set
     */
    public void setAString(String aString) {
        this.aString = aString;
    }

    /**
     * @return the enum
     */
    public AnEnum getAnEnum() {
        return anEnum;
    }

    /**
     * @param anEnum the enum to set
     */
    public void setAnEnum(AnEnum anEnum) {
        this.anEnum  = anEnum;
    }

    /**
     * @return the boolean
     */
    public boolean getABoolean() {
        return aBoolean;
    }

    /**
     * @param aBoolean the boolean to set
     */
    public void setBoolean(boolean aBoolean) {
        this.aBoolean = aBoolean;
    }

}

Here is what my insertions look like (and I tried saveAll() with collections too with no success)

    myEntityRepository.save(myEntity);
    if (nbTotal % batchSize == 0) {
        myEntityRepository.flush();
    }

And finally my jpa configuration:

        properties.setProperty("hibernate.jdbc.batch_size", dbOkcJpaBatchSize); --100
        properties.setProperty("hibernate.order_inserts", dbOkcJpaOrderInserts); --true
        properties.setProperty("hibernate.order_updates", dbOkcJpaOrderUpdates); --true

I’m getting desperate, I would like to avoid using jdbc statements with native queries, or modifying the database to add a numerical ID + a sequence that seem useless to me…

I’m hoping some other devs found a solution for something similar…

Thanks y’all!