Hibernate "Invalid object name 'TABLENAME_SEQ'. " when inserting

Question

I am writing a spring-boot application with hibernate. The application connects to a MSSQL 2014 database. I have used this tutorial for the basic architecture:

https://www.baeldung.com/spring-boot-hibernate

When I do a save() operation using the JpaRepository implementation for the entity, I am getting the following error:

org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:257) ~[spring-orm-6.0.4.jar:6.0.4]
    at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:233) ~[spring-orm-6.0.4.jar:6.0.4]

[More stack trace]

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'TABLENAME_SEQ'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265) ~[mssql-jdbc-12.2.0.jre11.jar:na]
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1695) ~[mssql-jdbc-12.2.0.jre11.jar:na]
...

Here is the logs for the sql generated by Hibernate:

Hibernate: 
    select
        next value for TABLENAME_SEQ

I have checked the database and the sequence table indeed does not exist. For reference here is an abstraction of my entity:

@Entity
@Table(name = "TABLENAME")
public class EntTableName {

    @Id
    @Getter
    @Setter
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "ID")
    private Long id;

    @Getter
    @Setter
    @Column(name = "COLUMN_1")
    private String column1;

// More columns here

}

Also the application.properties:

#database:
spring.datasource.url=jdbc:sqlserver://****
spring.datasource.username=****
spring.datasource.password=***
#mssql driver
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.jpa.hibernate.ddl-auto=update
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect

I have tried changing the dialect to org.hibernate.dialect.SQLServer2012Dialect but it did not change the error. I think changing the ID generation strategy to TABLE could fix this but it also disables batch updates, so I left it as a last resort.

I have also seen this as the same error but it uses an hbm configuration, which I don't use:

Invalid Object Name for Hibernate Sequence Generator

If you want to use sequence, better define it directly. That way you can also create your own sequence in the database. I am not sure if `spring.jpa.hibernate.ddl-auto=update` does also create tables, which seems what you are relying on — XtremeBaumer, Mar 06 '23 at 07:36
Update option did create the tables for the entities, so I would assume it should create sequence tables. That said, the tables still do not exist in the database. I will see how to define a sequence and update my question. — Semih.Toprak, Mar 06 '23 at 07:37
You can check [this link](https://www.baeldung.com/hibernate-identifiers#3-sequence-generation) for sequence mapping. — XtremeBaumer, Mar 06 '23 at 07:48

Semih.Toprak · Accepted Answer · 2023-03-06T08:19:36.867

It would seem the spring.jpa.hibernate.ddl-auto=update config does not create sequence tables. After some research, I have decided to disable the auto configuration and created each sequence with the following code:

create sequence TABLENAME_SEQ

I also had to update the entity as follows:

@Id
@Getter
@Setter
@GeneratedValue(strategy= GenerationType.SEQUENCE, generator="tableNameSeq")
@SequenceGenerator(name = "tableNameSeq", sequenceName = "TABLENAME_SEQ")
@Column(name = "ID")
private Long id = 1L;

I have also read that auto update is risky to use, so if anyone has a similar problem, this looks to be the recommended solution.

Hibernate "Invalid object name 'TABLENAME_SEQ'. " when inserting

1 Answers1