Hibernate在每次插入之前选择标识列值

问题描述

我正在尝试在数据库中插入30行，ID生成策略是 IDENTITY ，而我的数据库(Exasol)支持它.在每次插入之前，Hibernate都会从数据库中选择标识值，如下所示(从Hibernate日志中):

I'm trying to insert 30 rows into the database, the ID generation strategy is IDENTITY and my database (Exasol) supports it. Before each insert, Hibernate selects identity value from the database, like this (from Hibernate logs):

Hibernate:
    SELECT
            COLUMN_IDENTITY
        FROM
            EXA_ALL_COLUMNS
        WHERE
            COLUMN_NAME='ENTRY_ID'
            AND COLUMN_SCHEMA='TEST'
            AND COLUMN_TABLE='CAMPAIGN'
Hibernate:
    insert
    into
        ecombi_mdm_test.CSV_ADCAMPAIGN
        (bla1, bla2, bla3, bla4, bla5, bla6, bla7, bla8, bla9, bla10, bla11, bla12, bla13, bla14, bla15, bla16, bla17, bla18, bla19, bla20, bla21, bla22, bla23, bla24, bla25, bla26, bla27, bla28, bla29, bla30)
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate:
    SELECT
            COLUMN_IDENTITY
        FROM
            EXA_ALL_COLUMNS
        WHERE
            COLUMN_NAME='ENTRY_ID'
            AND COLUMN_SCHEMA='TEST'
            AND COLUMN_TABLE='CAMPAIGN'
Hibernate:
    insert
    into
        ecombi_mdm_test.CSV_ADCAMPAIGN
        (bla1, bla2, bla3, bla4, bla5, bla6, bla7, bla8, bla9, bla10, bla11, bla12, bla13, bla14, bla15, bla16, bla17, bla18, bla19, bla20, bla21, bla22, bla23, bla24, bla25, bla26, bla27, bla28, bla29, bla30)
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

...和另外28个.最后，我花了9秒钟才能插入30行.

... and 28 more. In the end, it takes me 9 seconds to insert 30 rows.

这是我配置数据源的方式:

This is how I configure my data source:

        configuration = new Configuration();
        configuration.setProperty("hibernate.connection.driver_class", properties.getProperty(...);
        configuration.setProperty("hibernate.connection.url", properties.getProperty(...);
        configuration.setProperty("hibernate.connection.username", properties.getProperty(...);
        configuration.setProperty("hibernate.connection.password", properties.getProperty(...);
        configuration.setProperty("hibernate.dialect", properties.getProperty(com.bla.exasol.ExasolDialect);
        configuration.setProperty("hibernate.show_sql", "true");
        configuration.setProperty("hibernate.format_sql", "true");
        configuration.setProperty("hibernate.default_schema", properties.getProperty(...);
        configuration.addAnnotatedClass(Some.class);

        ServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().applySettings(configuration.getProperties()).build();
        SessionFactory sessionFactory = configuration.buildSessionFactory(serviceRegistry);

这是我的DAO课上的

Session session = MultiTenantDBAccess.getSessionFactory().openSession();
Transaction transaction = null;

try {
    transaction = session.beginTransaction();
    for (T o : oList) {
        session.persist(o);
    }
    transaction.commit();
} catch (HibernateException e) {
    transaction.rollback();
    e.printStackTrace();
} finally {
    session.close();
}

这是我数据库的自定义身份支持类:

And this is my DB's custom identity support class:

public class ExasolIdentityColumnSupport extends IdentityColumnSupportImpl {

    @Override
    public String appendIdentitySelectToInsert(String arg0) {
        return arg0;
    }

    @Override
    public String getIdentityColumnString(int type) throws MappingException {
        return type==Types.BIGINT ?
                "decimal(36, 0) identity not null" :
                "decimal(19, 0) identity not null";
    }

    @Override
    public String getIdentitySelectString(String table, String column, int type) throws MappingException {
        return "SELECT COLUMN_IDENTITY FROM EXA_ALL_COLUMNS WHERE COLUMN_NAME='"+column.toUpperCase()+"' AND COLUMN_SCHEMA='"+table.substring(0, table.indexOf(".")).toUpperCase()+"' AND COLUMN_TABLE='"+(table.substring(table.indexOf(".")+1)).toUpperCase()+"'";
    }

    @Override
    public boolean hasDataTypeInIdentityColumn() {
        return false;
    }

    @Override
    public boolean supportsIdentityColumns() {
        return true;
    }

    public boolean supportsInsertSelectIdentity() {
        return false;
    }

}

我使用的数据库不支持 InsertSelectIdentity 是一个问题吗?Hibernate不能在每次插入之前不检查id值就向数据库发送30个查询吗?在这种情况下，我不能使用Hibernate达到比每9秒30行更好的性能吗?

Is the problem in the fact that the DB I use doesn't support InsertSelectIdentity? Can't just Hibernate send 30 queries to DB without checking the id value before every insert? Can't I achieve in this case a better performance with Hibernate than 30 rows per 9 seconds?

推荐答案

该ID是必需的，因为该实体使用ID作为键存储在会话中.

The id is needed because the entity is stored in the Session using the id as the key.

规避数据库的唯一方法是，如果数据库支持使用序列，则使用数据库序列.

The only way to circumvent it is to use a database sequence if the DB supports using sequences.

这篇关于Hibernate在每次插入之前选择标识列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！