MS SQL Server 2008 R2标识列的JPA批注

Question

我想知道正确的JPA注释设置来处理MS Server2008 R2标识列。

MS Server 2008 R2(RTM)不支持序列，但当我使用原始SQL测试时，标识会自动递增。

但是，在JPA中，我不能通过任何策略(IDENTITY、AUTO、TABLE)在@generatedvalue注释上使其工作，而且我不允许更改DB模式，因为它是其他的。我没有尝试用顺序，因为没有顺序。

CREATE TABLE [dbo].[invoice_header](
    [invoice_id] [int] IDENTITY(1,1) NOT NULL,
    [ApprovalDate] [datetime] NULL,
    [ApproverUserName] [nvarchar](50) NULL,
 CONSTRAINT [PK_invoice_header] PRIMARY KEY CLUSTERED 
(
    [invoice_id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

@Entity
@Table(name = "invoice_header")
public class InvoiceHeader {

    @Id
    @GeneratedValue( strategy = GenerationType.IDENTITY )
    @Column(name="invoice_id")
    private Long invoiceId;

Hibernate: insert into tbl_acct_textura_invoice_header (invoice_id, ApprovalDate, ApproverUserName) values (null, ?, ?)
2015-06-16 11:12:24.149 ERROR 7157 --- [tp1024240671-18] o.h.engine.jdbc.spi.SqlExceptionHelper   : DEFAULT or NULL are not allowed as explicit identity values.

由于在ID上传递null，它失败了。我可以在@column注释上通过insertable=false和updatable=false进行insert时使HQL不包含invoice_id列，但是hibernate不喜欢不包含Id列，如下所示。有什么建议吗？另外，我使用的是spring-data JpaRepository

插入期间不包括id列时出现Hibernate错误

nested exception is org.hibernate.id.IdentifierGenerationException: ids for this class must be manually assigned before calling save(): ...InvoiceHeader

Answer 1

我刚刚发现我没有在LocalContainerEntityManagerFactoryBean上设置hibernate方言。在将org.hibernate.dialt.SQLServer2008Dialction设置为Hibernate方言之后，GenerationType.Identity就像Neil提到的那样工作得很好。

下面是我的spring数据配置和application.properties。我有多个数据源，一个是H2嵌入式数据源供内部使用，另一个是MS SQL2008服务器供远程数据访问，这是我发布的。

MS SQL 2008 server的Spring数据配置

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "adapterEntityManagerFactory",
        transactionManagerRef = "adapterTransactionManager",
        basePackages = {"com.textura.client.erp.database.repository"})
public class ErpRepositoryConfig {
    @Autowired
    JpaVendorAdapter jpaVendorAdapter;

    @Value("${adapter.datasource.url}")
    private String databaseUrl;

    @Value("${adapter.datasource.username}")
    private String username;

    @Value("${adapter.datasource.password}")
    private String password;

    @Value("${adapter.datasource.hibernate.dialect}")
    private String dialect;

    public DataSource dataSource() {
        return new DriverManagerDataSource(databaseUrl, username, password);
    }
    @Bean(name = "adapterEntityManager")
    public EntityManager entityManager() {
        return entityManagerFactory().createEntityManager();
    }

    @Bean(name = "adapterEntityManagerFactory")
    public EntityManagerFactory entityManagerFactory() {
        Properties properties = new Properties();
        properties.setProperty("hibernate.dialect", dialect);  //"org.hibernate.dialect.SQLServer2008Dialect"
        LocalContainerEntityManagerFactoryBean lef = new LocalContainerEntityManagerFactoryBean();
        lef.setDataSource(dataSource());
        lef.setJpaVendorAdapter(jpaVendorAdapter);
        lef.setPackagesToScan("com.textura.client.erp.database.model");
        lef.setPersistenceUnitName("adapterPersistenceUnit");
        lef.setJpaProperties(properties);
        lef.afterPropertiesSet();
        return lef.getObject();
    }

    @Bean(name = "adapterTransactionManager")
    public PlatformTransactionManager transactionManager() {
        return new JpaTransactionManager(entityManagerFactory());
    }
}

# database configuration
spring.datasource.url=jdbc:h2:file:~/internal;AUTO_SERVER=TRUE
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.schema=schema.sql
spring.datasource.data=data.sql
spring.datasource.initialize=false
#spring.datasource.initialize=true only for first time to create table, after that switch to false

# JPA. Hibernate
spring.jpa.database=H2
spring.jpa.database-platform=org.hibernate.dialect.H2Dialect
spring.jpa.hibernate.ddl-auto=create-drop
#spring.jpa.hibernate.ddl-auto=choose one of [create-drop, create, update, validate, none]
spring.jpa.hibernate.naming-strategy: org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.show-sql=true
spring.data.jpa.repositories.enabled=true


# MS SQL Server 2008 datasource
adapter.datasource.url=jdbc:sqlserver://some.ip.address:1433;databaseName=Remote
adapter.datasource.username=sa
adapter.datasource.password=password
adapter.datasource.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect

Hibernate: insert into invoice_header (ApprovalDate, ApproverUserName) values (?, ?)