JPA GenerationType.IDENTITY not considering column with auto increment in MS SQL

Question

I have a table with a simple int id column with Identity auto increment in SQL Server.

    USE [Hot]
GO

/****** Object:  Table [dbo].[InstagramRequest]    Script Date: 24.10.2015 18:49:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[InstagramRequest](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [instUserId] [int] NULL,
    [request] [nvarchar](max) NULL,
    [intime] [datetime] NULL,
 CONSTRAINT [PK_InstagramRequest] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 10) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[InstagramRequest] ADD  CONSTRAINT [DF_InstagramRequest_intime]  DEFAULT (getdate()) FOR [intime]
GO

Entity class is ;

    import java.io.Serializable;
import java.util.Date;
import javax.persistence.Basic;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Lob;
import javax.persistence.NamedQueries;
import javax.persistence.NamedQuery;
import javax.persistence.Table;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;
import javax.xml.bind.annotation.XmlRootElement;

/**
 *
 * @author z
 */
@Entity
@Table(name = "InstagramRequest")
@XmlRootElement
@NamedQueries({
    @NamedQuery(name = "InstagramRequest.findAll", query = "SELECT i FROM InstagramRequest i"),
    @NamedQuery(name = "InstagramRequest.findById", query = "SELECT i FROM InstagramRequest i WHERE i.id = :id"),
    @NamedQuery(name = "InstagramRequest.findByInstUserID", query = "SELECT i FROM InstagramRequest i WHERE i.instUserID = :instUserID"),
    @NamedQuery(name = "InstagramRequest.findByIntime", query = "SELECT i FROM InstagramRequest i WHERE i.intime = :intime")})
public class InstagramRequest implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @Basic(optional = false)
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name = "ID")
    private Integer id;
    @Column(name = "instUserID")
    private Integer instUserID;
    @Lob
    @Column(name = "request")
    private String request;
    @Column(name = "intime")
    @Temporal(TemporalType.TIMESTAMP)
    private Date intime;

    public InstagramRequest() {
    }

    public InstagramRequest(Integer id) {
        this.id = id;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public Integer getInstUserID() {
        return instUserID;
    }

    public void setInstUserID(Integer instUserID) {
        this.instUserID = instUserID;
    }

    public String getRequest() {
        return request;
    }

    public void setRequest(String request) {
        this.request = request;
    }

    public Date getIntime() {
        return intime;
    }

    public void setIntime(Date intime) {
        this.intime = intime;
    }

    @Override
    public int hashCode() {
        int hash = 0;
        hash += (id != null ? id.hashCode() : 0);
        return hash;
    }

    @Override
    public boolean equals(Object object) {
        // TODO: Warning - this method won't work in the case the id fields are not set
        if (!(object instanceof InstagramRequest)) {
            return false;
        }
        InstagramRequest other = (InstagramRequest) object;
        if ((this.id == null && other.id != null) || (this.id != null && !this.id.equals(other.id))) {
            return false;
        }
        return true;
    }

    @Override
    public String toString() {
        return "com.instagramparse.entity.InstagramRequest[ id=" + id + " ]";
    }

}

The error message is as below .;

Internal Exception: java.sql.SQLException: Cannot insert the value NULL into column 'ID', table 'master.dbo.InstagramRequest'; column does not allow nulls. INSERT fails. Error Code: 515 Call: INSERT INTO InstagramRequest (instUserID, intime, request) VALUES (?, ?, ?) bind => [3 parameters bound] Query: InsertObjectQuery(com.instagramparse.entity.InstagramRequest[ id=null ])

Which GenerationType should I use in this case ?

OndroMih OndroMih · Accepted Answer · 2015-10-24T20:18:31

Your GenerationType is correct. You need to remove @Basic(optional = false) - it does not make sense to enforce that this field is set by JPA if it is to be autogenerated by the DB.

In fact, what seems to happen is that your JPA provider tries to insert NULL value instead of not setting anything for the id column. As the column is autogenerated, no value can be inserted into that column in an INSERT. Making the field optional will work as expected - JPA will not try to insert any value for I'd into the db, but will read the generated value after insert, making the value to always be non-null after persist.

JPA GenerationType.IDENTITY not considering column with auto increment in MS SQL

1 Answers