JPA : @SequenceGenerator is not generating the sequence

Question

I my application I have used JPA with Hibernate vendor and Oracle 11G DB.

Here I am using the native query as follow on my MST_EMP table ..

  Query query  = this.entityManager.createNativeQuery("INSERT  INTO MST_EMP emp (" +
                    "EMP_NAME,EMP_MAIL_ID) VALUES ('dasdas',?)");
            query.setParameter(1,"dhrumil");
            query.executeUpdate();

Here is my MST_EMP entity details..

@Table(name = "MST_EMP")
public class MstEmp implements Serializable, IsEntity {

    private static final long serialVersionUID = 1L;

    @Id 
    @Column(name = "EMP_CODE")
    @SequenceGenerator( name = "EMP_CODE_SEQ", sequenceName = "EMP_CODE_SEQ")   
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "EMP_CODE_SEQ")
    private String empCode;


    @Column(name="EMP_MAIL_ID")
    private String empMailId;


    @Column(name="EMP_NAME")
    private String empName;

    public MstEmp() {
    }

    public String getEmpCode() {
        return this.empCode;
    }

    public void setEmpCode(String empCode) {
        this.empCode = empCode;
    }

    public String getCreatedBy() {
        return this.createdBy;
    }


    public void setEmpMailId(String empMailId) {
        this.empMailId = empMailId;
    }



    public String getEmpName() {
        return this.empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

}

As per my understanding, we dont need to give value to EMP_CODE in the native query. Because sequence is associated with it.

But this query gives me error like this ..

SEVERE: ORA-01400: cannot insert NULL into ("PERK"."MST_EMP"."EMP_CODE")

SEVERE: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: could not execute native bulk manipulation query
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1179)
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1112)

Can any one tell me, Do we need to provide EMP_CODE in the native query ?

Will native query , do not refer the sequence automatically declared in the Entity?

Thanks.

1. Why do you need to resort to native query for such a trivial task? — Hasan Ceylan, Nov 03 '12 at 15:10

score 2 · Accepted Answer · answered Nov 02 '12 at 09:33

JPA only generates the sequence automatically when you persist a new object via EntityManager.persist() method:

E.g.

EntityManager em = \\ ...  Initialise
MstEmp newMstEmp = new MstEmp();
newMstEmp.setEmpCode(...);
newMstEmp.setEmpMailId(...);
newMstEmp.setEmpName(...);
EntityTransaction tx = em.getTransaction();
tx.begin();
em.persist(newMstEmp);
tx.commit();

When you apply JPQL directly to the DB (via entityManager.createQuery()) or raw SQL directly to the DB (via entityManager.createNativeQuery() - as you do here), you must insert your own sequence:

Query query  = this.entityManager.createNativeQuery("INSERT  INTO MST_EMP emp (" +
                    "EMP_CODE,EMP_NAME,EMP_MAIL_ID) VALUES (EMP_CODE_SEQ.nextval,'dasdas',?)");
query.setParameter(1,"dhrumil");
query.executeUpdate();

Thanks for the reply. After your suggestion, I am adding sequence id manually in the native query. I am using SEQUENCE_NAME.nextvalue in the native query. It works fine. — Gunjan Shah, Nov 02 '12 at 11:57
That's a best practice. In JPA to reduce DB roundtrips, sequences usually generated with `allocationSize`s. If you manually get the next sequence you will create large id windows. — Hasan Ceylan, Nov 03 '12 at 15:09

JPA : @SequenceGenerator is not generating the sequence

1 Answers1