IDENTITY_INSERT with JPA

Question

I am attempting to insert some fake data into an SQL Server Express database. I am using some simple code like this:

@Entity
@Table(name = "People")
public class Peeps implements Serializable {

    @Id
    @Column(name = "ID", columnDefinition = "Decimal(10,0)")
    private String id;

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

}

I call the entity manager to create the above class as follows:

private EntityManager em = createManager();
private EntityTransaction utx = em.getTransaction();

final Peeps entity = new Peeps();
entity.setId("10002");
entity.setName("Joe");
utx.begin();
em.persist(entity);
utx.commit();

However, when doing this, I get an error::

 Cannot insert explicit value for identity column in table 'People' when IDENTITY_INSERT is set to OFF

So what I tried doing is something like this:

em.createNativeQuery("SET IDENTITY_INSERT People ON").executeUpdate();
em.persist(entity);
em.createNativeQuery("SET IDENTITY_INSERT People OFF").executeUpdate();

However, I still get the same error. My intuition leads me to believe that the connection is not being shared. Is there something I can do to instruct hibernate to set IDENTITY_INSERT to ON before I invoke my persistence?

Answer 1

You are missing the next line under your id field:

@GeneratedValue(strategy=GenerationType.IDENTITY)

So your id field should look like this:

@Entity
@Table(name = "People")
public class Peeps implements Serializable {

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "ID", columnDefinition = "Decimal(10,0)")
    private String id;

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

}

Second, you are not supposed to set an ID to your entity by yourself, the ID will be generated automatically when you'll persist it using hibernate.

so get rid of: entity.setId("10002");

and just do this:

private EntityManager em = createManager();
private EntityTransaction utx = em.getTransaction();

final Peeps entity = new Peeps();
entity.setName("Joe");
utx.begin();
em.persist(entity);
utx.commit();

something else make sure that you configured your primary key in your DB table, with auto increment.