CODEWITHSUNDEEP
sql-serverhibernatespring-bootid-generationsequence-generators
Leon
Leon

Reputation: 71

Schema-validation: missing table [SEQUENCE_NAME] when using Hibernate Sequence Generator Strategy

I have some problems with the hibernate id generation and a ms sql server.

I am using the GenerationType.SEQUENCE in my application to generate Ids with hibernate. 

@Id
@SequenceGenerator(name = "SequenceGenerator", sequenceName = "SEQUENCE_NAME")
@GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "SequenceGenerator")
private Long id;

As DB I use a microsoft sql server 2017.

I create the sequence via Liquibase:

<createSequence incrementBy="50" sequenceName="SEQUENCE_NAME" startValue="100000"/>

While starting the Spring Boot Application I get the error:

Schema-validation: missing table [SEQUENCE_NAME]

When I manually query for the sequence it can be found:

SELECT COUNT(*) FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[SEQUENCE_NAME]') AND type = 'SO'

I am a little bit confused that it says "missing table". With oracle it works fine. Does mssql not support id generation via sequences.

My Configuration:

spring:
  datasource:
    url: "jdbc:sqlserver://localhost:1433"
    username: sa
    password: ABc12345!
  jpa:
    properties:
      hibernate.dialect: org.hibernate.dialect.SQLServerDialect

I use this docker image: mcr.microsoft.com/mssql/server:2017-CU12-ubuntu

Upvotes: 3

Views: 2083

Answers (2)

Leon
Leon

Reputation: 71

I used the wrong dialect in the configuration. The correct one is:

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

The other one is for older servers which doesn't support sequences.

Upvotes: 4

Nikolai Shevchenko
Nikolai Shevchenko

Reputation: 7521

Try adding default_schema to application.yml:

spring:
   jpa:
      properties:
         hibernate:
            default_schema: "dbo"

Upvotes: -1

Related Questions