如何在Amazon中使用Hibernate插入实体

Question

我正在尝试使用Hibernate对表进行插入。问题是Redshift不接受序列，而是接受为行创建标识。

我有以下情况：

我的桌子结构：

CREATE TABLE rsds_ops_latam.log( idlog BIGINT PRIMARY KEY IDENTITY(0,1), idaccount varchar(255), username varchar(255), oldowner varchar(255), newowner varchar(255), oldclass varchar(255), newclass varchar(255), date timestamp DEFAULT GETDATE() );

我的实体：

@Entity
@NamedQuery(name="Log.findAll", query="SELECT l FROM Log l")
@Table(name = "log")
public class Log implements Serializable {
private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
@Column(name = "idlog")
private long idLog;

private String userName;

@Temporal(TemporalType.TIMESTAMP)
@GeneratedValue()
@Column(name = "date")
private Date date;

@Column(name = "idaccount")
private String idAccount;

@Column(name = "oldowner")
private String oldOwner;

@Column(name = "newowner")
private String newOwner;

@Column(name = "oldclass")
private String oldClass;

@Column(name = "newclass")
private String newClass;

当我试图持久化该实体时，日志中会出现以下内容：

11:38:16,702 INFO  [stdout] (default task-3) Hibernate: 
11:38:16,702 INFO  [stdout] (default task-3)     insert 
11:38:16,702 INFO  [stdout] (default task-3)     into
11:38:16,702 INFO  [stdout] (default task-3)         rsds_ops_latam.log
11:38:16,702 INFO  [stdout] (default task-3)         (date, idaccount, newclass, newowner, oldclass, oldowner, userName) 
11:38:16,702 INFO  [stdout] (default task-3)     values
11:38:16,703 INFO  [stdout] (default task-3)         (?, ?, ?, ?, ?, ?, ?)
11:38:18,340 INFO  [stdout] (default task-3) Hibernate: 
11:38:18,346 INFO  [stdout] (default task-3)     select
11:38:18,347 INFO  [stdout] (default task-3)         currval('rsds_ops_latam.log_idlog_seq')
11:38:18,876 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-3) SQL Error: 500310, SQLState: 42P01
11:38:18,878 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-3) [Amazon](500310) Invalid operation: relation "rsds_ops_latam.log_idlog_seq" does not exist;

我的persistence.xml如下：

<persistence-unit name="accounts2" transaction-type="JTA">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>

    <jta-data-source>java:/AccountMovementsRS</jta-data-source>
     <properties>
        <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQL9Dialect" />
        <property name="hibernate.connection.driver_class" value="org.postgresql.Driver" />
        <property name="hibernate.default_schema" value="rsds_ops_latam"/>
        <property name="hibernate.connection.url" value="myconnection" />
        <property name="hibernate.connection.username" value="user" />
        <property name="hibernate.connection.password" value="password" />
         <property name="hibernate.show_sql" value="true"/>
        <property name="hibernate.format_sql" value="true"/>
        <property name="hibernate.flushMode" value="FLUSH_AUTO" />
        <property name="hibernate.hbm2ddl.auto" value="validate" />
     </properties>
</persistence-unit>

我能做什么有什么想法吗？

谢谢!

Answer 1

红移是基于Postgres，但它缺少许多Postgres功能。其中之一是序列。您可以尝试用GenerationType.TABLE注释您的GenerationType.TABLE列。然后Hibernate应该创建一个与序列类似的表，并在普通sql (与Redshift兼容)中这样做。

请注意，这种方法有一个性能损失，因为hibernate将锁定每个插入的序列表，这可能会导致代价高昂。

我也支持@John的观点，即Redshift不适用于单个插入(因此缺少功能)。