Hibernate insert failing when embedded key contains identity column on SQL Server

Question

I'm trying to map an entity with hibernate, but with SQL Server, I am not able to proceed.

Following are the details.

SQL Server Entity

CREATE TABLE [dbo].[BOOK_EMBEDDED](
  [row_id] [bigint] IDENTITY(1,1) NOT NULL,
  [group_no] [int]                NOT NULL,
  [book_name] [varchar](255)      NULL,
  CONSTRAINT [PK_BOOK_EMBEDDED] PRIMARY KEY CLUSTERED 
(
  [group_no] ASC,
  [row_id] ASC
) WITH (PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
) ON [PRIMARY]

=============================

The embedded key

@Embeddable 
public class EmbeddedKey implements Serializable { 
    private static final long serialVersionUID = 1L; 

    @GeneratedValue(strategy = GenerationType.IDENTITY) 
    @Column(name = "row_id") 
    private Long rowId; 

    @Column(name = "group_no") 
    private int groupNo; 

    public Long getRowId() { 
        return rowId; 
    } 

    public void setRowId(Long rowId) { 
        this.rowId = rowId; 
    } 

    public static long getSerialversionuid() { 
        return serialVersionUID; 
    } 

    @Override 
    public int hashCode() { 
        final int prime = 31; 
        int result = 1; 

        result = (int) (prime * result + rowId); 
        result = prime * result + groupNo; 

        return result; 
    } 

    @Override 
    public boolean equals(Object obj) { 
        if (this == obj) 
            return true; 

        if (obj == null) 
            return false; 

        if (getClass() != obj.getClass()) 
            return false; 

        EmbeddedKey other = (EmbeddedKey) obj; 

        if (rowId != other.rowId) 
            return false; 

        if (groupNo != other.groupNo) 
            return false; 

        return true; 
    } 

    @Override 
    public String toString() { 
        return this.getRowId() + "  " + this.getGroupNo() + " "; 
    } 
 
    public int getGroupNo() { 
        return groupNo; 
    } 
 
    public void setGroupNo(int groupNo) { 
        this.groupNo = groupNo; 
    } 
} 

The Entity

@Entity(name = "BOOK_EMBEDDED") 
public class BookMySQL implements Serializable { 
    private static final long serialVersionUID = 1L; 

    @Column(name = "BOOK_NAME") 
    private String book_Name; 

    @EmbeddedId 
    private EmbeddedKey key; 

    public BookMySQL() { 
    } 

    public String getBook_Name() { 
        return book_Name; 
    } 

    public void setBook_Name(String book_Name) { 
        this.book_Name = book_Name; 
    } 

    public static long getSerialversionuid() { 
        return serialVersionUID; 
    } 

    public EmbeddedKey getKey() { 
        return key; 
    } 

    public void setKey(EmbeddedKey key) { 
        this.key = key; 
    } 

    @Override 
    public String toString() { 
        return this.getKey().toString() + "  " + this.getBook_Name(); 
    } 
}

Entity Manager class

public class LocalEntityManager { 
    private static EntityManagerFactory emf; 
    private static EntityManager em; 

    private LocalEntityManager() { 
    } 

    public static EntityManager getEntityManger() { 
        if (emf == null) { 
            synchronized (LocalEntityManager.class) { 
                if (emf == null) { 
                    emf = Persistence.createEntityManagerFactory("BookEntities"); 
                    em = emf.createEntityManager(); 
                } 
            } 
        } 

        return em; 
    } 
}

Book Service

public class MySQLBookService { 

    public Long persistBook(String bookName) { 
        EmbeddedKey key = new EmbeddedKey(); 
        key.setGroupNo(1); 

        BookMySQL book = new BookMySQL(); 
        book.setBook_Name(bookName); 
        book.setKey(key); 

        EntityManager em = LocalEntityManager.getEntityManger(); 
        EntityTransaction tx = em.getTransaction(); 

        tx.begin(); 
        em.persist(book); 
        tx.commit(); 

        em.close(); 

        return book.getKey().getRowId(); 
    } 

    public BookMySQL findBook(int bookId) { 
        EntityManager em = LocalEntityManager.getEntityManger();
 
        EmbeddedKey key = new EmbeddedKey(); 
        key.setGroupNo(1); 
        key.setRowId(1L); 

        BookMySQL bookMySQL = em.find(BookMySQL.class, key); 

        System.out.println(bookMySQL); 

        return bookMySQL; 
    } 

    public static void main(String... args) { 
        MySQLBookService bookService = new MySQLBookService(); 
        // bookService.findBook(1); 
        bookService.persistBook("Lord of the rings"); 
    } 
}

The problem is I cannot use a sequence and by executing this findBook always works and persist fails with error.

ERROR: Cannot insert explicit value for identity column in table 'BOOK_EMBEDDED' when IDENTITY_INSERT is set to OFF.

Any help will be greatly appreciated.

Answer 1

The only way to make it work is to overwrite the SQLInsert and trick Hibernate which expects to set the identifier column. This can be done if you provide your own custom INSERT statement so that, instead of setting the rowId to null, you set the version instead:

@Entity(name = "BOOK_EMBEDDED")
@SQLInsert( sql = "insert into BOOK_EMBEDDED (BOOK_NAME, group_no, version) values (?, ?, ?)")
public static class Book implements Serializable {

    @EmbeddedId
    private EmbeddedKey key;

    @Column(name = "BOOK_NAME")
    private String bookName;

    @Version
    @Column(insertable = false)
    private Integer version;

    public EmbeddedKey getKey() {
        return key;
    }

    public void setKey(EmbeddedKey key) {
        this.key = key;
    }

    public String getBookName() {
        return bookName;
    }

    public void setBookName(String bookName) {
        this.bookName = bookName;
    }
}

With this change in place, you run the following test:

doInJPA(entityManager -> {

    EmbeddedKey key = new EmbeddedKey();
    key.setGroupNo(1);

    Book book = new Book();
    book.setBookName( "High-Performance Java Persistence");

    book.setKey(key);

    entityManager.persist(book);
});

doInJPA(entityManager -> {
    EmbeddedKey key = new EmbeddedKey();

    key.setGroupNo(1);
    key.setRowId(1L);

    Book book = entityManager.find(Book.class, key);
    assertEquals( "High-Performance Java Persistence", book.getBookName() );
});

And Hibernate will generate the right SQL statements:

Query:["insert into BOOK_EMBEDDED (BOOK_NAME, group_no, version) values (?, ?, ?)"], Params:[(High-Performance Java Persistence, 1, NULL(BIGINT))]

Query:["select compositei0_.group_no as group_no1_0_0_, compositei0_.row_id as row_id2_0_0_, compositei0_.BOOK_NAME as BOOK_NAM3_0_0_, compositei0_.version as version4_0_0_ from BOOK_EMBEDDED compositei0_ where compositei0_.group_no=? and compositei0_.row_id=?"], Params:[(1, 1)]

The test is available on GitHub.

Answer 2

You have to set the identity_insert to ON to make it work.

Check this post - How to set IDENTITY_INSERT

So run below command:

SET IDENTITY_INSERT BOOK_EMBEDDED ON

Answer 3

Simply delete & remove the EmbeddedKey class and add the fields to your entity.

import java.io.*;
import javax.persistence.*;

@Entity(name = "Book")
@Table(name = "book")
public class Book implements Serializable
{

  @Id
  @Basic(optional = false)
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "registration_number")
  private Long registrationNumber;

  @Column(name = "publisher_id")
  private Integer publisherId;

  @Column(name = "title")
  private String title;

  //Getters and setters omitted for brevity
}

CREATE TABLE book (
        publisher_id INT NOT NULL,
        registration_number BIGINT IDENTITY NOT NULL,
        title VARCHAR(255),
        PRIMARY KEY (publisher_id, registration_number)
    )