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.

could Yoy format code according to SO rules? – Jacek Cz Sep 04 '17 at 11:22 — Jacek Cz, Sep 04 '17 at 11:22

Vlad Mihalcea · Accepted Answer · 2021-01-09T22:50:15.347

7

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.

edited Jan 09 '21 at 22:50

answered Sep 04 '17 at 12:34

Vlad Mihalcea

142,745
71
566
911

Interesting, but actually hibernate forum (https://forum.hibernate.org/viewtopic.php?f=1&t=1006818) suggest that can't be done. Does it work with venilla hibernate, I have tried with only hibernate (no JPA) but could not able to achieve.. if you want, I can post error – Shiva Dec 04 '18 at 13:02
That forum answer is from 2010, mine is from 2017 and has a test case that works just fine which you can find on GitHub. Just fork my High-Performance Java Persistence book repository and run the test. – Vlad Mihalcea Dec 04 '18 at 13:23
yes. sure, I did look into it. I will try with it and update you. Again, can I expect it work with **vanilla(no jpa)** hibernate? – Shiva Dec 04 '18 at 13:28
1

You can make all tests in that repository run in Hibernate mode as well. Take a look on the AbstractTest and check the native bootstrap. – Vlad Mihalcea Dec 04 '18 at 13:29
Sure @Vlad Mihalcea, Thanks .. I will try to workout today. – Shiva Dec 04 '18 at 13:30
This workaround doesn't seem to work properly, at least not with Hibernate 5.2.10.Final. I'm getting errors like: "Parameter index out of range (29 > number of parameters, which is 28)". Hibernate clearly adds the id to the query parameters, which is visible in debug mode. Insertable = false on the id column in the embeddable doesn't change a thing. – MarcelK May 21 '19 at 15:17
OK, I see what you did there - you've added a dummy column called "version" which will always be null. Not very clean, but it works I guess. Calling a procedure that would ignore the extra argument could be a cleaner solution. – MarcelK May 21 '19 at 15:49
That's not a dummy column. That's the optimistic locking version column. – Vlad Mihalcea May 21 '19 at 16:33
If someone is not using optimistic locking, he will need a dummy column (or perhaps a procedure call will work). The fact that you've annotated it as a @Version column in your example doesn't change that. Also, it seems this workaround doesn't work combined with a persist cascade - Hibernate puts null/zero to the FK column instead of the identity generated identifier of the parent entity (maybe it needs another workaround, which would be very Hibernatesque :)). Anyway I'd advise proceeding with caution. – MarcelK May 22 '19 at 12:41
more details is here [hear](https://vladmihalcea.com/how-to-map-a-composite-identifier-using-an-automatically-generatedvalue-with-jpa-and-hibernate/) – Arash Jul 17 '23 at 06:48

score 1 · Answer 2 · answered Sep 04 '17 at 12:20

1

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

answered Sep 04 '17 at 12:20

Chaitanya

15,403
35
96
137

score 0 · Answer 3 · answered May 17 '19 at 13:28

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)
    )

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

3 Answers3

Linked