Java Hibernate JPA 序列（非 Id）

Hibernate JPA Sequence (non-Id)

提问by Miguel Ping

Is it possible to use a DB sequence for some column that is not the identifier/is not part of a composite identifier?

是否可以对某些不是标识符/不是复合标识符的一部分的列使用 DB 序列？

I'm using hibernate as jpa provider, and I have a table that has some columns that are generated values (using a sequence), although they are not part of the identifier.

我使用 hibernate 作为 jpa 提供程序，并且我有一个表，其中包含一些生成值的列（使用序列），尽管它们不是标识符的一部分。

What I want is to use a sequence to create a new value for an entity, where the column for the sequence is NOT(part of) the primary key:

我想要的是使用序列为实体创建一个新值，其中序列的列不是主键（的一部分）：

@Entity
@Table(name = "MyTable")
public class MyEntity {

    //...
    @Id //... etc
    public Long getId() {
        return id;
    }

   //note NO @Id here! but this doesn't work...
    @GeneratedValue(strategy = GenerationType.AUTO, generator = "myGen")
    @SequenceGenerator(name = "myGen", sequenceName = "MY_SEQUENCE")
    @Column(name = "SEQ_VAL", unique = false, nullable = false, insertable = true, updatable = true)
    public Long getMySequencedValue(){
      return myVal;
    }

}

Then when I do this:

然后当我这样做时：

em.persist(new MyEntity());

the id will be generated, but the mySequenceValproperty will be also generated by my JPA provider.

将生成 id，但mySequenceVal我的 JPA 提供程序也会生成该属性。

Just to make things clear: I want Hibernateto generate the value for the mySequencedValueproperty. I know Hibernate can handle database-generated values, but I don't want to use a trigger or any other thing other than Hibernate itself to generate the value for my property. If Hibernate can generate values for primary keys, why can't it generate for a simple property?

只是为了说明清楚：我希望Hibernate为该mySequencedValue属性生成值。我知道 Hibernate 可以处理数据库生成的值，但我不想使用触发器或除 Hibernate 本身以外的任何其他东西来为我的属性生成值。如果 Hibernate 可以为主键生成值，为什么不能为简单的属性生成值？

采纳答案by Morten Berg

Looking for answers to this problem, I stumbled upon this link

寻找这个问题的答案，我偶然发现了这个链接

It seems that Hibernate/JPA isn't able to automatically create a value for your non-id-properties. The @GeneratedValueannotation is only used in conjunction with @Idto create auto-numbers.

似乎 Hibernate/JPA 无法自动为您的非 id 属性创建值。该@GeneratedValue注释只有配合使用@Id，以创建自动编号。

The @GeneratedValueannotation just tells Hibernate that the database is generating this value itself.

该@GeneratedValue注释只是告诉Hibernate数据库已生成该值本身。

The solution (or work-around) suggested in that forum is to create a separate entity with a generated Id, something like this:

该论坛中建议的解决方案（或变通方法）是使用生成的 ID 创建一个单独的实体，如下所示：

@Entity
public class GeneralSequenceNumber {
  @Id
  @GeneratedValue(...)
  private Long number;
}

@Entity 
public class MyEntity {
  @Id ..
  private Long id;

  @OneToOne(...)
  private GeneralSequnceNumber myVal;
}

回答by Frederic Morin

I've been in a situation like you (JPA/Hibernate sequence for non @Id field) and I ended up creating a trigger in my db schema that add a unique sequence number on insert. I just never got it to work with JPA/Hibernate

我遇到过像你这样的情况（非@Id 字段的 JPA/Hibernate 序列），我最终在我的数据库模式中创建了一个触发器，在插入时添加一个唯一的序列号。我只是从来没有让它与 JPA/Hibernate 一起工作

回答by alasdairg

Hibernate definitely supports this. From the docs:

Hibernate 绝对支持这一点。从文档：

"Generated properties are properties which have their values generated by the database. Typically, Hibernate applications needed to refresh objects which contain any properties for which the database was generating values. Marking properties as generated, however, lets the application delegate this responsibility to Hibernate. Essentially, whenever Hibernate issues an SQL INSERT or UPDATE for an entity which has defined generated properties, it immediately issues a select afterwards to retrieve the generated values."

“生成的属性是其值由数据库生成的属性。通常，Hibernate 应用程序需要刷新包含数据库正在为其生成值的任何属性的对象。然而，将属性标记为已生成，让应用程序将此责任委托给 Hibernate。从本质上讲，每当 Hibernate 为已定义生成属性的实体发出 SQL INSERT 或 UPDATE 时，它随后立即发出选择以检索生成的值。”

For properties generated on insert only, your property mapping (.hbm.xml) would look like:

对于仅在插入时生成的属性，您的属性映射 (.hbm.xml) 将如下所示：

<property name="foo" generated="insert"/>

For properties generated on insert and update your property mapping (.hbm.xml) would look like:

对于在插入和更新时生成的属性，您的属性映射 (.hbm.xml) 将如下所示：

<property name="foo" generated="always"/>

Unfortunately, I don't know JPA, so I don't know if this feature is exposed via JPA (I suspect possibly not)

不幸的是，我不知道 JPA，所以我不知道这个功能是否通过 JPA 公开（我怀疑可能没有）

Alternatively, you should be able to exclude the property from inserts and updates, and then "manually" call session.refresh( obj ); after you have inserted/updated it to load the generated value from the database.

或者，您应该能够从插入和更新中排除该属性，然后“手动”调用 session.refresh( obj ); 在您插入/更新它以从数据库加载生成的值之后。

This is how you would exclude the property from being used in insert and update statements:

这是您将如何排除在插入和更新语句中使用的属性：

<property name="foo" update="false" insert="false"/>

Again, I don't know if JPA exposes these Hibernate features, but Hibernate does support them.

同样，我不知道 JPA 是否公开了这些 Hibernate 功能，但 Hibernate 确实支持它们。

回答by alasdairg

"I don't want to use a trigger or any other thing other than Hibernate itself to generate the value for my property"

“我不想使用触发器或除 Hibernate 本身以外的任何其他东西来为我的财产生成价值”

In that case, how about creating an implementation of UserType which generates the required value, and configuring the metadata to use that UserType for persistence of the mySequenceVal property?

在这种情况下，如何创建生成所需值的 UserType 实现，并配置元数据以使用该 UserType 来持久化 mySequenceVal 属性？

回答by alasdairg

I run in the same situation like you and I also didn't find any serious answers if it is basically possible to generate non-id propertys with JPA or not.

我在和你一样的情况下运行，如果基本上可以用 JPA 生成非 id 属性，我也没有找到任何严肃的答案。

My solution is to call the sequence with a native JPA query to set the property by hand before persisiting it.

我的解决方案是使用本机 JPA 查询调用序列以在持久化之前手动设置属性。

This is not satisfying but it works as a workaround for the moment.

这并不令人满意，但目前可以作为一种解决方法。

Mario

马里奥

回答by kammy

This is not the same as using a sequence. When using a sequence, you are not inserting or updating anything. You are simply retrieving the next sequence value. It looks like hibernate does not support it.

这与使用序列不同。使用序列时，您不会插入或更新任何内容。您只是在检索下一个序列值。看起来 hibernate 不支持它。

回答by Paul

As a followup here's how I got it to work:

作为后续，我是如何让它工作的：

@Override public Long getNextExternalId() {
    BigDecimal seq =
        (BigDecimal)((List)em.createNativeQuery("select col_msd_external_id_seq.nextval from dual").getResultList()).get(0);
    return seq.longValue();
}

回答by Gustavo Orair

I've found this specific note in session 9.1.9 GeneratedValue Annotation from JPA specification: "[43] Portable applications should not use the GeneratedValue annotation on other persistent fields or properties." So, I presume that it is not possible to auto generate value for non primary key values at least using simply JPA.

我在来自 JPA 规范的会话 9.1.9 GeneratedValue Annotation 中找到了这个特定注释：“[43] 便携式应用程序不应在其他持久字段或属性上使用 GeneratedValue 注释。” 因此，我认为至少使用 JPA 不可能为非主键值自动生成值。

回答by Sergey Vedernikov

I found that @Column(columnDefinition="serial")works perfect but only for PostgreSQL. For me this was perfect solution, because second entity is "ugly" option.

我发现这很@Column(columnDefinition="serial")完美，但仅适用于 PostgreSQL。对我来说，这是完美的解决方案，因为第二个实体是“丑陋”的选项。

回答by Sebastian G?tz

Although this is an old thread I want to share my solution and hopefully get some feedback on this. Be warned that I only tested this solution with my local database in some JUnit testcase. So this is not a productive feature so far.

虽然这是一个旧线程，但我想分享我的解决方案，并希望得到一些反馈。请注意，我仅在某些 JUnit 测试用例中使用本地数据库测试了此解决方案。因此，到目前为止，这还不是一个富有成效的功能。

I solved that issue for my by introducing a custom annotation called Sequence with no property. It's just a marker for fields that should be assigned a value from an incremented sequence.

我通过引入一个没有属性的名为 Sequence 的自定义注释为我解决了这个问题。它只是应该从递增序列中分配值的字段的标记。

@Retention(RetentionPolicy.RUNTIME)
@Target(ElementType.FIELD)
public @interface Sequence
{
}

Using this annotation i marked my entities.

使用此注释我标记了我的实体。

public class Area extends BaseEntity implements ClientAware, IssuerAware
{
    @Column(name = "areaNumber", updatable = false)
    @Sequence
    private Integer areaNumber;
....
}

To keep things database independent I introduced an entity called SequenceNumber which holds the sequence current value and the increment size. I chose the className as unique key so each entity class wil get its own sequence.

为了保持数据库独立，我引入了一个名为 SequenceNumber 的实体，它保存序列当前值和增量大小。我选择 className 作为唯一键，因此每个实体类都将获得自己的序列。

@Entity
@Table(name = "SequenceNumber", uniqueConstraints = { @UniqueConstraint(columnNames = { "className" }) })
public class SequenceNumber
{
    @Id
    @Column(name = "className", updatable = false)
    private String className;

    @Column(name = "nextValue")
    private Integer nextValue = 1;

    @Column(name = "incrementValue")
    private Integer incrementValue = 10;

    ... some getters and setters ....
}

The last step and the most difficult is a PreInsertListener that handles the sequence number assignment. Note that I used spring as bean container.

最后一步也是最困难的一步是处理序列号分配的 PreInsertListener。请注意，我使用 spring 作为 bean 容器。

@Component
public class SequenceListener implements PreInsertEventListener
{
    private static final long serialVersionUID = 7946581162328559098L;
    private final static Logger log = Logger.getLogger(SequenceListener.class);

    @Autowired
    private SessionFactoryImplementor sessionFactoryImpl;

    private final Map<String, CacheEntry> cache = new HashMap<>();

    @PostConstruct
    public void selfRegister()
    {
        // As you might expect, an EventListenerRegistry is the place with which event listeners are registered
        // It is a service so we look it up using the service registry
        final EventListenerRegistry eventListenerRegistry = sessionFactoryImpl.getServiceRegistry().getService(EventListenerRegistry.class);

        // add the listener to the end of the listener chain
        eventListenerRegistry.appendListeners(EventType.PRE_INSERT, this);
    }

    @Override
    public boolean onPreInsert(PreInsertEvent p_event)
    {
        updateSequenceValue(p_event.getEntity(), p_event.getState(), p_event.getPersister().getPropertyNames());

        return false;
    }

    private void updateSequenceValue(Object p_entity, Object[] p_state, String[] p_propertyNames)
    {
        try
        {
            List<Field> fields = ReflectUtil.getFields(p_entity.getClass(), null, Sequence.class);

            if (!fields.isEmpty())
            {
                if (log.isDebugEnabled())
                {
                    log.debug("Intercepted custom sequence entity.");
                }

                for (Field field : fields)
                {
                    Integer value = getSequenceNumber(p_entity.getClass().getName());

                    field.setAccessible(true);
                    field.set(p_entity, value);
                    setPropertyState(p_state, p_propertyNames, field.getName(), value);

                    if (log.isDebugEnabled())
                    {
                        LogMF.debug(log, "Set {0} property to {1}.", new Object[] { field, value });
                    }
                }
            }
        }
        catch (Exception e)
        {
            log.error("Failed to set sequence property.", e);
        }
    }

    private Integer getSequenceNumber(String p_className)
    {
        synchronized (cache)
        {
            CacheEntry current = cache.get(p_className);

            // not in cache yet => load from database
            if ((current == null) || current.isEmpty())
            {
                boolean insert = false;
                StatelessSession session = sessionFactoryImpl.openStatelessSession();
                session.beginTransaction();

                SequenceNumber sequenceNumber = (SequenceNumber) session.get(SequenceNumber.class, p_className);

                // not in database yet => create new sequence
                if (sequenceNumber == null)
                {
                    sequenceNumber = new SequenceNumber();
                    sequenceNumber.setClassName(p_className);
                    insert = true;
                }

                current = new CacheEntry(sequenceNumber.getNextValue() + sequenceNumber.getIncrementValue(), sequenceNumber.getNextValue());
                cache.put(p_className, current);
                sequenceNumber.setNextValue(sequenceNumber.getNextValue() + sequenceNumber.getIncrementValue());

                if (insert)
                {
                    session.insert(sequenceNumber);
                }
                else
                {
                    session.update(sequenceNumber);
                }
                session.getTransaction().commit();
                session.close();
            }

            return current.next();
        }
    }

    private void setPropertyState(Object[] propertyStates, String[] propertyNames, String propertyName, Object propertyState)
    {
        for (int i = 0; i < propertyNames.length; i++)
        {
            if (propertyName.equals(propertyNames[i]))
            {
                propertyStates[i] = propertyState;
                return;
            }
        }
    }

    private static class CacheEntry
    {
        private int current;
        private final int limit;

        public CacheEntry(final int p_limit, final int p_current)
        {
            current = p_current;
            limit = p_limit;
        }

        public Integer next()
        {
            return current++;
        }

        public boolean isEmpty()
        {
            return current >= limit;
        }
    }
}

As you can see from the above code the listener used one SequenceNumber instance per entity class and reserves a couple of sequence numbers defined by the incrementValue of the SequenceNumber entity. If it runs out of sequence numbers it loads the SequenceNumber entity for the target class and reserves incrementValue values for the next calls. This way I do not need to query the database each time a sequence value is needed. Note the StatelessSession that is being opened for reserving the next set of sequence numbers. You cannot use the same session the target entity is currently persisted since this would lead to a ConcurrentModificationException in the EntityPersister.

从上面的代码可以看出，侦听器为每个实体类使用一个 SequenceNumber 实例，并保留了由 SequenceNumber 实体的 incrementValue 定义的一对序列号。如果序列号用完，它会为目标类加载 SequenceNumber 实体，并为下一次调用保留 incrementValue 值。这样我就不需要在每次需要序列值时查询数据库。请注意为保留下一组序列号而打开的 StatelessSession。您不能使用目标实体当前持久化的同一会话，因为这会导致 EntityPersister 中的 ConcurrentModificationException。

Hope this helps someone.

希望这可以帮助某人。