无法使用临时表时的批量ID策略

本文介绍了HHH-11262 JIRA问题，现在允许批量ID策略在无法创建临时表的情况下也能工作。

类图

假设我们有以下实体

Person实体是这个实体继承模型的基础类，映射如下

@Entity(name = "Person")
@Inheritance(
    strategy = InheritanceType.JOINED
)
public class Person
    implements Serializable {

    @Id
    private Integer id;

    @Id
    private String companyName;

    private String name;

    private boolean employed;

    //Getters and setters omitted for brevity

    @Override
    public boolean equals(Object o) {
        if ( this == o ) {
            return true;
        }
        if ( !( o instanceof Person ) ) {
            return false;
        }
        Person person = (Person) o;
        return Objects.equals(
            getId(),
            person.getId()
        ) &&
        Objects.equals(
            getCompanyName(),
            person.getCompanyName()
        );
    }

    @Override
    public int hashCode() {
        return Objects.hash(
            getId(), getCompanyName()
        );
    }
}

Doctor和Engineer实体类都扩展了Person基础类

@Entity(name = "Doctor")
public class Doctor
    extends Person {
}

@Entity(name = "Engineer")
public class Engineer
    extends Person {

    private boolean fellow;

    //Getters and setters omitted for brevity
}

继承树批量处理

现在，当你尝试执行一个批量实体查询时

int updateCount = session.createQuery(
    "delete from Person where employed = :employed" )
.setParameter( "employed", false )
.executeUpdate();

Hibernate执行以下语句

create temporary table
    HT_Person
(
    id int4 not null,
    companyName varchar(255) not null
)

insert
into
    HT_Person
    select
        p.id as id,
        p.companyName as companyName
    from
        Person p
    where
        p.employed = ?

delete
from
    Engineer
where
    (
        id, companyName
    ) IN (
        select
            id,
            companyName
        from
            HT_Person
    )

delete
from
    Doctor
where
    (
        id, companyName
    ) IN (
        select
            id,
            companyName
        from
            HT_Person
    )

delete
from
    Person
where
    (
        id, companyName
    ) IN (
        select
            id,
            companyName
        from
            HT_Person
    )

HT_Person是Hibernate创建的一个临时表，用于存储所有要由批量ID操作更新或删除的实体标识符。该临时表可以是全局的或本地的，具体取决于底层数据库的能力。

如果不能创建临时表怎么办？

如HHH-11262问题所述，在某些情况下，应用程序开发人员无法使用临时表，因为数据库用户没有这个权限。

在这种情况下，我们定义了几个选项，您可以根据自己的数据库能力进行选择

InlineIdsInClauseBulkIdStrategy
InlineIdsSubSelectValueListBulkIdStrategy
InlineIdsOrClauseBulkIdStrategy
CteValuesListBulkIdStrategy

`InlineIdsInClauseBulkIdStrategy`

要使用此策略，您需要配置以下配置属性

<property name="hibernate.hql.bulk_id_strategy"
          value="org.hibernate.hql.spi.id.inline.InlineIdsInClauseBulkIdStrategy"
/>

现在，当运行之前的测试用例时，Hibernate生成了以下SQL语句

select
    p.id as id,
    p.companyName as companyName
from
    Person p
where
    p.employed = ?

delete
from
    Engineer
where
        ( id, companyName )
    in (
        ( 1,'Red Hat USA' ),
        ( 3,'Red Hat USA' ),
        ( 1,'Red Hat Europe' ),
        ( 3,'Red Hat Europe' )
    )

delete
from
    Doctor
where
        ( id, companyName )
    in (
        ( 1,'Red Hat USA' ),
        ( 3,'Red Hat USA' ),
        ( 1,'Red Hat Europe' ),
        ( 3,'Red Hat Europe' )
    )

delete
from
    Person
where
        ( id, companyName )
    in (
        ( 1,'Red Hat USA' ),
        ( 3,'Red Hat USA' ),
        ( 1,'Red Hat Europe' ),
        ( 3,'Red Hat Europe' )
    )

因此，首先选择实体标识符，然后用于每个特定的更新或删除语句。

IN子句行值表达式已经由Oracle、PostgreSQL支持很长时间了，现在MySQL 5.7也支持。然而，SQL Server 2014不支持此语法，因此您必须使用不同的策略。

`InlineIdsSubSelectValueListBulkIdStrategy`

要使用此策略，您需要配置以下配置属性

<property name="hibernate.hql.bulk_id_strategy"
          value="org.hibernate.hql.spi.id.inline.InlineIdsSubSelectValueListBulkIdStrategy"
/>

现在，当运行之前的测试用例时，Hibernate生成了以下SQL语句

select
    p.id as id,
    p.companyName as companyName
from
    Person p
where
    p.employed = ?

delete
from
    Engineer
where
    ( id, companyName ) in (
        select
            id,
            companyName
        from (
        values
            ( 1,'Red Hat USA' ),
            ( 3,'Red Hat USA' ),
            ( 1,'Red Hat Europe' ),
            ( 3,'Red Hat Europe' )
        ) as HT
            (id, companyName)
    )

delete
from
    Doctor
where
    ( id, companyName ) in (
         select
            id,
            companyName
        from (
        values
            ( 1,'Red Hat USA' ),
            ( 3,'Red Hat USA' ),
            ( 1,'Red Hat Europe' ),
            ( 3,'Red Hat Europe' )
        ) as HT
            (id, companyName)
    )

delete
from
    Person
where
    ( id, companyName ) in (
        select
            id,
            companyName
        from (
        values
            ( 1,'Red Hat USA' ),
            ( 3,'Red Hat USA' ),
            ( 1,'Red Hat Europe' ),
            ( 3,'Red Hat Europe' )
        ) as HT
            (id, companyName)
    )

底层数据库必须支持VALUES列表子句，例如PostgreSQL或SQL Server 2008。然而，此策略需要IN子句行值表达式来表示复合标识符，因此您只能使用PostgreSQL。

`InlineIdsOrClauseBulkIdStrategy`

要使用此策略，您需要配置以下配置属性

<property name="hibernate.hql.bulk_id_strategy"
          value="org.hibernate.hql.spi.id.inline.InlineIdsOrClauseBulkIdStrategy"
/>

现在，当运行之前的测试用例时，Hibernate生成了以下SQL语句

select
    p.id as id,
    p.companyName as companyName
from
    Person p
where
    p.employed = ?

delete
from
    Engineer
where
    ( id = 1 and companyName = 'Red Hat USA' )
or  ( id = 3 and companyName = 'Red Hat USA' )
or  ( id = 1 and companyName = 'Red Hat Europe' )
or  ( id = 3 and companyName = 'Red Hat Europe' )

delete
from
    Doctor
where
    ( id = 1 and companyName = 'Red Hat USA' )
or  ( id = 3 and companyName = 'Red Hat USA' )
or  ( id = 1 and companyName = 'Red Hat Europe' )
or  ( id = 3 and companyName = 'Red Hat Europe' )

delete
from
    Person
where
    ( id = 1 and companyName = 'Red Hat USA' )
or  ( id = 3 and companyName = 'Red Hat USA' )
or  ( id = 1 and companyName = 'Red Hat Europe' )
or  ( id = 3 and companyName = 'Red Hat Europe' )

此策略的优点是所有主要的关系数据库系统都支持它（例如Oracle、SQL Server、MySQL和PostgreSQL）。

`CteValuesListBulkIdStrategy`

要使用此策略，您需要配置以下配置属性

<property name="hibernate.hql.bulk_id_strategy"
          value="org.hibernate.hql.spi.id.inline.CteValuesListBulkIdStrategy"
/>

现在，当运行之前的测试用例时，Hibernate生成了以下SQL语句

select
    p.id as id,
    p.companyName as companyName
from
    Person p
where
    p.employed = ?

with HT_Person (id,companyName ) as (
    select id, companyName
    from (
    values
        (?, ?),
        (?, ?),
        (?, ?),
        (?, ?)
    ) as HT (id, companyName) )
delete
from
    Engineer
where
    ( id, companyName ) in (
        select
            id, companyName
        from
            HT_Person
    )

with HT_Person (id,companyName ) as (
    select id, companyName
    from (
    values
        (?, ?),
        (?, ?),
        (?, ?),
        (?, ?)
    ) as HT (id, companyName) )
delete
from
    Doctor
where
    ( id, companyName ) in (
        select
            id, companyName
        from
            HT_Person
    )


with HT_Person (id,companyName ) as (
    select id, companyName
    from (
    values
        (?, ?),
        (?, ?),
        (?, ?),
        (?, ?)
    ) as HT (id, companyName) )
delete
from
    Person
where
    ( id, companyName ) in (
        select
            id, companyName
        from
            HT_Person
    )

底层数据库必须支持CTE（公共表表达式），这些表达式可以从非查询语句中引用，例如从9.1版本的PostgreSQL或从2005版本的SQL Server开始。底层数据库还必须支持VALUES列表子句，例如PostgreSQL或SQL Server 2008。

然而，此策略需要IN子句行值表达式来表示复合标识符，因此您只能使用PostgreSQL。

结论

如果可以使用临时表，那么这可能是最好的选择。然而，如果不允许创建临时表，就必须选择这些四种策略中的一种，使其与底层数据库兼容。在做出决定之前，应该对它们在当前负载下的表现进行基准测试。例如，CTE是PostgreSQL中的优化围栏，因此在做出决定之前请确保进行测量。

如果您使用Oracle或MySQL 5.7，可以选择InlineIdsOrClauseBulkIdStrategy或InlineIdsInClauseBulkIdStrategy。对于MySQL的旧版本，则只能使用InlineIdsOrClauseBulkIdStrategy。

如果您使用SQL Server，那么InlineIdsOrClauseBulkIdStrategy是您唯一的选择。

如果您使用PostgreSQL，那么您可以选择这四种策略中的任何一种。

相关内容

类图

继承树批量处理

如果不能创建临时表怎么办？

`InlineIdsInClauseBulkIdStrategy`

`InlineIdsSubSelectValueListBulkIdStrategy`

`InlineIdsOrClauseBulkIdStrategy`

`CteValuesListBulkIdStrategy`

结论

项目

关注我们

贡献和社区