Spring Boot连接SQL Server 2022序列生成ID报错求助

问题背景 #

Spring Boot服务连接SQL Server 2022数据库时，使用序列生成实体ID报错，但同schema下其他表可正常插入数据。

实体代码 #

public class SomeEntity {
    @Id
    @SequenceGenerator(name = "some_seq", schema = "sch_some", sequenceName = "some_seq", allocationSize = 1)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "some_seq")
    @Column(name = "id", columnDefinition = "decimal")
    private Long id;
    ...
}

插入数据时的错误信息 #

{"date":"2024-07-07T20:50:08,250Z","env":"test","app":"some-service","version":"1.32.0","host":"_","level":"ERROR","logger":"org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet]","msg":"Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: error performing isolated work; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: error performing isolated work] with root cause","thread":"http-nio-8080-exec-1","context":[],"eventId":"com.microsoft.sqlserver.jdbc.SQLServerException","stack":"com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'some_db.sch_some.some_seq'.\n\tat com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:262)\n\tat com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1624)\n\tat com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:594)\n\tat com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:524)\n\tat com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7194)\n\tat com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2979)\n\tat com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:248)\n\tat com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:223)\n\tat com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:446)\n\tat com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)\n\tat com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)\n\tat

## 创建序列的SQL语句
```sql
create sequence SCH_SOME.SOME_SEQ start with 1 increment by 1;

权限尝试问题 #

怀疑权限问题，按Oracle方式执行grant select on SCH_SOME.SOME_SEQ to [用户名];时，报错：

SQL Error [4606] [S0001]: Granted or revoked privilege SELECT is not compatible with object.

尝试授予UPDATE、ALTER权限也无效果。

Hibernate生成的SQL语句 #

调试发现Hibernate生成的SQL不符合SQL Server语法：

Hibernate: 
    select
        next_val as id_val 
    from
        some_db.sch_some.some_seq with (updlock,
        rowlock)

解决方案 #

1. 修正Hibernate方言配置 #

SQL Server获取序列值的语法是NEXT VALUE FOR [schema].[sequence_name]，需确保使用适配SQL Server 2012+的方言：

• Hibernate 5.x配置：

  spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServer2012Dialect
  

• Hibernate 6.x配置：

  spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect
  

2. 修正序列权限 #

SQL Server中访问序列不需要SELECT权限，执行以下语句授予正确权限：

-- 授予足够获取序列值的REFERENCES权限
GRANT REFERENCES ON SCH_SOME.SOME_SEQ TO [你的数据库用户名];
-- 或者授予ALTER权限（权限范围更大）
GRANT ALTER ON SCH_SOME.SOME_SEQ TO [你的数据库用户名];

3. 验证序列存在性 #

执行SQL确认序列是否存在且归属正确schema：

SELECT * FROM sys.sequences WHERE name = 'SOME_SEQ' AND schema_id = SCHEMA_ID('SCH_SOME');

4. 检查实体序列配置 #

当前@SequenceGenerator配置无冗余，但需确保数据库用户能正确识别sch_some schema（可通过指定用户默认schema或在序列名前显式加schema解决）。

内容的提问来源于stack exchange，提问作者kayelbb