Invalid Object Name for Hibernate Sequence Generat

Question

I am developing an application that accesses a database running SQL Server 2012 through the Hibernate framework. However, I cannot figure out how to make an instance of the SequenceGenerator annotation work; I get an exception whenever I attempt to save a new object instance to my database table. The class to be saved is the following:

import java.io.Serializable;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.SequenceGenerator;
import javax.persistence.Table;

@Entity
@Table(name="MESSAGES")
public class Message implements Serializable {

    private static final long serialVersionUID = -3535373804021266134L;

    @Id
    @Column(name="MESSAGE_ID")
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="MESSAGE_GEN")
    @SequenceGenerator(name="MESSAGE_GEN", sequenceName="MESSAGE_SEQ", initialValue=1, allocationSize=1) 
    private Long id;

    @Column(name="TEXT")
    private String text;

    public Message(String text) { 
        this.text = text;
    }

    public Long getId() {
        return id;
    }

    public String getText() {
        return text;
    }

}

An issue-provoking scenario could be the following transaction:

import org.hibernate.Session;

public class Manager {

    public static void main(String[] args) {
        Session session = Database.getSessionFactory().getCurrentSession();

        session.beginTransaction();

        Message message = new Message("Hello, World!");
        session.save(message);

        session.getTransaction().commit();

        Database.getSessionFactory().close();
    }

}

This results in the following stack trace related to the identifier sequence:

Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not extract ResultSet
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:123)
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:91)
    at org.hibernate.id.SequenceGenerator.generateHolder(SequenceGenerator.java:122)
    at org.hibernate.id.SequenceHiLoGenerator.generate(SequenceHiLoGenerator.java:73)
    at org.hibernate.event.internal.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:117)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:209)
    at org.hibernate.event.internal.DefaultSaveEventListener.saveWithGeneratedOrRequestedId(DefaultSaveEventListener.java:55)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:194)
    at org.hibernate.event.internal.DefaultSaveEventListener.performSaveOrUpdate(DefaultSaveEventListener.java:49)
    at org.hibernate.event.internal.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:90)
    at org.hibernate.internal.SessionImpl.fireSave(SessionImpl.java:715)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:707)
    at org.hibernate.internal.SessionImpl.save(SessionImpl.java:702)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.hibernate.context.internal.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:356)
    at com.sun.proxy.$Proxy8.save(Unknown Source)
    at dk.radiometer.tests.hibernate.service.Manager.doTransaction(Manager.java:35)
    at dk.radiometer.tests.hibernate.service.Manager.main(Manager.java:16)
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'MESSAGE_SEQ'.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
    at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
    at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:82)
    ... 19 more

The issue seems related to the name of the sequence. My guess is that I need to manually create a sequence table of some sort for Hibernate to use, however, I do not know how to do this an have been unable to find a resource that seems related to my environment. If this is indeed the issue, please redirect me to some documentation for the protocol to follow. In addition, I am using the following programmatic Hibernate configuration:

import org.hibernate.SessionFactory;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.hibernate.service.ServiceRegistry;

public class Database {

    private static final SessionFactory SESSION_FACTORY = buildSessionFactory();

    private static SessionFactory buildSessionFactory() {
        try {
            Configuration cfg = buildConfiguration();
            ServiceRegistry sr = new StandardServiceRegistryBuilder().applySettings(cfg.getProperties()).build();
            SessionFactory sf = cfg.buildSessionFactory(sr);
            return sf;
        } catch (Throwable t) {
            System.err.println("Initial SessionFactory creation failed: " + t);
            throw new ExceptionInInitializerError(t);
        }
    }

    private static Configuration buildConfiguration() {
        return new Configuration()
            .addAnnotatedClass(Message.class)
            .setProperty("hibernate.connection.driver_class", "com.microsoft.sqlserver.jdbc.SQLServerDriver")
            .setProperty("hibernate.connection.url", "jdbc:sqlserver://SERVER_NAME;databaseName=DATABASE_NAME;integratedSecurity=true;")
            .setProperty("hibernate.connection.pool_size", "1")
            .setProperty("hibernate.dialect", "org.hibernate.dialect.SQLServer2012Dialect")
            .setProperty("hibernate.current_session_context_class", "thread")
            .setProperty("hibernate.cache.provider_class", "org.hibernate.cache.internal.NoCacheProvider")
            .setProperty("hibernate.show_sql", "true")
            .setProperty("hibernate.hbm2ddl.auto", "update");
    }

    public static SessionFactory getSessionFactory() {
        return SESSION_FACTORY;
    }

}

Thanks in advance!

Answer 1

The solution to this issue is adding the catalog and schema properties to your @Table annotation.

@Table(name="<<TableName>>", catalog="<<DatabaseName>>", schema="SchemaName")

e.g

@Table(name="EVENTS", catalog="EVENTMANAGER", schema="DBO")

1. Catalog refers to the database name where the given table belongs to.
2. Schema refers to the container (using oracle definition) to which the database belongs, in microsoft SQL Server, databases are generally created under the Database Operator (DBO) schema.

e.g. Hibernata Mapping File (hbm) which works for me.

<class name="Event" table="EVENTS" catalog="EVENTMANAGER" schema="DBO">
    <id name="id" column="EVENT_ID">
        <generator class="native"/>
    </id>
    <property name="date" type="timestamp" column="EVENT_DATE"/>
    <property name="title" column="TITLE" not-null="true"/>
</class>