how to start from "0" an UNSIGNED AUTO_INCREMENT field?

Question

I have the following tables : enter image description here

enter image description here

wherein idclient is unsigned auto_increment.

code of the Client entity:

import java.io.Serializable;

import java.util.List;

import javax.persistence.*;

import javax.xml.bind.annotation.XmlRootElement;

import javax.xml.bind.annotation.XmlTransient;


@Entity
@Table(name = "CLIENT", catalog = "TEST", schema = "PUBLIC")
@XmlRootElement
@NamedQueries({
@NamedQuery(name = "Client.findAll", query = "SELECT c FROM Client c"),
@NamedQuery(name = "Client.findByIdclient", query = "SELECT c FROM Client c WHERE c.idclient = :idclient"),
@NamedQuery(name = "Client.findByLibel", query = "SELECT c FROM Client c WHERE c.libel = :libel"),
@NamedQuery(name = "Client.findByAdresse", query = "SELECT c FROM Client c WHERE c.adresse = :adresse"),
@NamedQuery(name = "Client.findByNomResp", query = "SELECT c FROM Client c WHERE c.nomResp = :nomResp"),
@NamedQuery(name = "Client.findByTelPortable", query = "SELECT c FROM Client c WHERE c.telPortable = :telPortable"),
@NamedQuery(name = "Client.findByTelFixe", query = "SELECT c FROM Client c WHERE c.telFixe = :telFixe"),
@NamedQuery(name = "Client.findByFax", query = "SELECT c FROM Client c WHERE c.fax = :fax"),
@NamedQuery(name = "Client.findByCodeTva", query = "SELECT c FROM Client c WHERE c.codeTva = :codeTva"),
@NamedQuery(name = "Client.findByCodeExo", query = "SELECT c FROM Client c WHERE c.codeExo = :codeExo"),
@NamedQuery(name = "Client.findByBanque", query = "SELECT c FROM Client c WHERE c.banque = :banque"),
@NamedQuery(name = "Client.findByRib", query = "SELECT c FROM Client c WHERE c.rib = :rib"),
@NamedQuery(name = "Client.findByCredit", query = "SELECT c FROM Client c WHERE c.credit = :credit"),
@NamedQuery(name = "Client.findByEchance", query = "SELECT c FROM Client c WHERE c.echance = :echance"),
@NamedQuery(name = "Client.findByMail", query = "SELECT c FROM Client c WHERE c.mail = :mail"),
@NamedQuery(name = "Client.findByEtat", query = "SELECT c FROM Client c WHERE c.etat = :etat")})
public class Client implements Serializable {
private static final long serialVersionUID = 1L;
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Basic(optional = false)
@Column(name = "IDCLIENT", nullable = false)
private Integer idclient;
@Basic(optional = false)
@Column(name = "LIBEL", nullable = false, length = 100)
private String libel;
@Basic(optional = false)
@Column(name = "ADRESSE", nullable = false, length = 100)
private String adresse;
@Basic(optional = false)
@Column(name = "NOM_RESP", nullable = false, length = 60)
private String nomResp;
@Basic(optional = false)
@Column(name = "TEL_PORTABLE", nullable = false, length = 16)
private String telPortable;
@Basic(optional = false)
@Column(name = "TEL_FIXE", nullable = false, length = 16)
private String telFixe;
@Basic(optional = false)
@Column(name = "FAX", nullable = false, length = 16)
private String fax;
@Basic(optional = false)
@Column(name = "CODE_TVA", nullable = false, length = 30)
private String codeTva;
@Basic(optional = false)
@Column(name = "CODE_EXO", nullable = false, length = 30)
private String codeExo;
@Basic(optional = false)
@Column(name = "BANQUE", nullable = false, length = 60)
private String banque;
@Basic(optional = false)
@Column(name = "RIB", nullable = false, length = 22)
private String rib;
@Basic(optional = false)
@Column(name = "CREDIT", nullable = false)
private double credit;
@Basic(optional = false)
@Column(name = "ECHANCE", nullable = false)
private int echance;
@Basic(optional = false)
@Column(name = "MAIL", nullable = false, length = 70)
private String mail;
@Basic(optional = false)
@Column(name = "ETAT", nullable = false)
private char etat;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "clientIdclient")
private List<Facture> factureList;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "clientIdclient")
private List<FactProforma> factProformaList;
@OneToMany(cascade = CascadeType.ALL, mappedBy = "clientIdclient")
private List<Bl> blList;

public Client() {
}

public Client(Integer idclient) {
    this.idclient = idclient;
}

public Client(Integer idclient, String libel, String adresse, String nomResp, String telPortable, String telFixe, String fax, String codeTva, String codeExo, String banque, String rib, double credit, int echance, String mail, char etat) {
    this.idclient = idclient;
    this.libel = libel;
    this.adresse = adresse;
    this.nomResp = nomResp;
    this.telPortable = telPortable;
    this.telFixe = telFixe;
    this.fax = fax;
    this.codeTva = codeTva;
    this.codeExo = codeExo;
    this.banque = banque;
    this.rib = rib;
    this.credit = credit;
    this.echance = echance;
    this.mail = mail;
    this.etat = etat;
}

public Integer getIdclient() {
    return idclient;
}

public void setIdclient(Integer idclient) {
    this.idclient = idclient;
}

public String getLibel() {
    return libel;
}

public void setLibel(String libel) {
    this.libel = libel;
}

public String getAdresse() {
    return adresse;
}

public void setAdresse(String adresse) {
    this.adresse = adresse;
}

public String getNomResp() {
    return nomResp;
}

public void setNomResp(String nomResp) {
    this.nomResp = nomResp;
}

public String getTelPortable() {
    return telPortable;
}

public void setTelPortable(String telPortable) {
    this.telPortable = telPortable;
}

public String getTelFixe() {
    return telFixe;
}

public void setTelFixe(String telFixe) {
    this.telFixe = telFixe;
}

public String getFax() {
    return fax;
}

public void setFax(String fax) {
    this.fax = fax;
}

public String getCodeTva() {
    return codeTva;
}

public void setCodeTva(String codeTva) {
    this.codeTva = codeTva;
}

public String getCodeExo() {
    return codeExo;
}

public void setCodeExo(String codeExo) {
    this.codeExo = codeExo;
}

public String getBanque() {
    return banque;
}

public void setBanque(String banque) {
    this.banque = banque;
}

public String getRib() {
    return rib;
}

public void setRib(String rib) {
    this.rib = rib;
}

public double getCredit() {
    return credit;
}

public void setCredit(double credit) {
    this.credit = credit;
}

public int getEchance() {
    return echance;
}

public void setEchance(int echance) {
    this.echance = echance;
}

public String getMail() {
    return mail;
}

public void setMail(String mail) {
    this.mail = mail;
}

public char getEtat() {
    return etat;
}

public void setEtat(char etat) {
    this.etat = etat;
}

@XmlTransient
public List<Facture> getFactureList() {
    return factureList;
}

public void setFactureList(List<Facture> factureList) {
    this.factureList = factureList;
}

@XmlTransient
public List<FactProforma> getFactProformaList() {
    return factProformaList;
}

public void setFactProformaList(List<FactProforma> factProformaList) {
    this.factProformaList = factProformaList;
}

@XmlTransient
public List<Bl> getBlList() {
    return blList;
}

public void setBlList(List<Bl> blList) {
    this.blList = blList;
}

@Override
public int hashCode() {
    int hash = 0;
    hash += (idclient != null ? idclient.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 Client)) {
        return false;
    }
    Client other = (Client) object;
    if ((this.idclient == null && other.idclient != null) || (this.idclient != null && !this.idclient.equals(other.idclient))) {
        return false;
    }
    return true;
}

@Override
public String toString() {
    return "glob.entitys.Client[ idclient=" + idclient + " ]";
  }

}

when I try to insert a row into the data base :

Utilisateur user=new Utilisateur(loginActuel);
Client client=new Client(0);// the error comes from here
Facture fact=new Facture(null,new Date());
fact.setClientIdclient(client);
fact.setUtilisateurLogin(user);
FactureJpaController fjc=new FactureJpaController(emf);
fjc.create(fact);

I get this ugly error(but when i set new Client(1) it works well):

Exception in thread "AWT-EventQueue-0" javax.persistence.RollbackException: Exception [EclipseLink-7197] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.ValidationException

Exception Description: Null or zero primary key encountered in unit of work clone [glob.entitys.Client[ idclient=0 ]], primary key [0]. Set descriptors IdValidation or the "eclipselink.id-validation" property.

how to solve this problem ?

remark: the client idclient = 0 is already inserted in the Database(but manually) I'd like once and for all overcome this "problem" , how to prevent JPA or H2 Database to start from 0 ?

score 2 · Answer 1 · answered Apr 03 '13 at 10:00

2

There are two ways how to allow zeroes in primary keys in Eclipselink:

Parameter in persistence.xml:

<property name="eclipselink.id-validation" value="NULL"/>

PrimaryKey annotation on concerned entity class:

@PrimaryKey(validation = IdValidation.NULL)

answered Apr 03 '13 at 10:00

Ondrej Bozek

10,987
7
54
70

Thomas Mueller · Accepted Answer · 2012-03-15T06:05:38.133

2

H2 does allow to use 0 as the primary key. The error message doesn't come from H2.

However, it seems to me that some (older?) version of EclipseLink doesn't allow to use 0.

the client idclient = 0 is already inserted in the Database

It seems this is not supported by this version of EclipseLink. It looks like to work around this problem, you should not use the value 0.

edited Mar 15 '12 at 06:05

answered Mar 14 '12 at 19:00

Thomas Mueller

48,905
14
116
132

yes, as he said Mikko Maunu JPA does not support a zero value as primary key , thank you for clarifying – Marwen Trabelsi Mar 14 '12 at 22:27
JPA specification supports 0 as primary key. Older version of EclipseLink as a one of the implementations doesn't support and that is a reported bug in EclipseLink. – Mikko Maunu Mar 15 '12 at 05:09
Updated my answer to say "older versions of EclipseLink don't support 0 as a primary key value" instead of "the JPA specification doesn't support it". – Thomas Mueller Mar 15 '12 at 06:06
As the error states, to allow 0, set "eclipselink.id-validation" property. – James May 29 '12 at 12:23

score 1 · Answer 3 · answered May 29 '12 at 11:23

I had this error and adding the following annotations to my jpa identity resolved it:

  @Column(name = "ID_SEARCH_LOG", nullable = false, insertable = true, updatable = true, length = 10, precision = 0)
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
private int idSearchLog;

score 1 · Answer 4 · answered Sep 26 '12 at 13:32

The docs say

By default, EclipseLink interprets zero as null for primitive types that cannot be null (such as int and long) causing zero to be an invalid value for primary keys.

but also that that it is possible to change this behaviour in either the persistence.xml or on a particular entity.

http://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Entities/Ids/Id#Allowing_Zero_Value_Primary_Keys

Mikko Maunu · Answer 5 · 2012-03-14T20:24:06.133

1

For JPA (specification 2.0) having (or negative) value for id is fine. And also as primary key value for H2.

Older versions of EclipseLink do consider value 0 or smaller as invalid primary key. See for example following:Bug 249948. So updating EclipseLink can help.

By the way, why you do set in constructor value for idclient that is supposed to be generated?

edited Mar 14 '12 at 20:24

answered Mar 14 '12 at 20:10

Mikko Maunu

41,366
10
132
135

Thanks for Link , Just The entity Facture must use an instance of Client entity( fact.setClientIdclient(client); )... – Marwen Trabelsi Mar 14 '12 at 22:22

how to start from "0" an UNSIGNED AUTO_INCREMENT field?

5 Answers5