Duplicate key when insert records with JPA and Hibernate

Question

Good day. I'm doing tests of my database before start programming. In this test i have four entities: Employee, Product, SaleBill and SaleBilsDetail.

The ER is easy (i can't put images yet):

https://i.stack.imgur.com/Dd8fC.png

And the process of Billings is like this:

Each sale bill is made by one employee.
Each sale bill have many details.
Each sale bill detail has one product.

Employee

import java.io.Serializable;

import javax.persistence.*;

import java.util.Date;


@Entity
@Table(name="employees")
@NamedQuery(name="Employee.findAll", query="SELECT e FROM Employee e")
public class Employee implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id_employee")
    private Integer idEmployee;

    private String address;

    @Temporal(TemporalType.DATE)
    @Column(name="birth_date")
    private Date birthDate;

    @Temporal(TemporalType.DATE)
    @Column(name="created_at")
    private Date createdAt;

    @Column(name="dni", columnDefinition="bpchar")
    private String dni;

    private String email;

    private String names;

    @Column(name="state", columnDefinition="bpchar")
    private String state;

    private String surnames;

    @Temporal(TemporalType.DATE)
    @Column(name="updated_at")
    private Date updatedAt;

    public Employee() {
    }

    public Integer getIdEmployee() {
        return this.idEmployee;
    }

    public void setIdEmployee(Integer idEmployee) {
        this.idEmployee = idEmployee;
    }

    public String getAddress() {
        return this.address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public Date getBirthDate() {
        return this.birthDate;
    }

    public void setBirthDate(Date birthDate) {
        this.birthDate = birthDate;
    }

    public Date getCreatedAt() {
        return this.createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public String getDni() {
        return this.dni;
    }

    public void setDni(String dni) {
        this.dni = dni;
    }

    public String getEmail() {
        return this.email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getNames() {
        return this.names;
    }

    public void setNames(String names) {
        this.names = names;
    }

    public String getState() {
        return this.state;
    }

    public void setState(String state) {
        this.state = state;
    }

    public String getSurnames() {
        return this.surnames;
    }

    public void setSurnames(String surnames) {
        this.surnames = surnames;
    }

    public Date getUpdatedAt() {
        return this.updatedAt;
    }

    public void setUpdatedAt(Date updatedAt) {
        this.updatedAt = updatedAt;
    }


}

Product

import java.io.Serializable;

import javax.persistence.*;

import java.math.BigDecimal;
import java.util.Date;


@Entity
@Table(name="products")
@NamedQuery(name="Product.findAll", query="SELECT p FROM Product p")
public class Product implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id_product")
    private Integer idProduct;

    @Temporal(TemporalType.DATE)
    @Column(name="created_at")
    private Date createdAt;

    private String description;

    @Column(name="id_category")
    private Integer idCategory;

    private String name;

    private BigDecimal price;

    @Column(name="state", columnDefinition="bpchar")
    private String state;

    private Short stock;

    @Temporal(TemporalType.DATE)
    @Column(name="updated_at")
    private Date updatedAt;

    public Product() {
    }

    public Integer getIdProduct() {
        return this.idProduct;
    }

    public void setIdProduct(Integer idProduct) {
        this.idProduct = idProduct;
    }

    public Date getCreatedAt() {
        return this.createdAt;
    }

    public void setCreatedAt(Date createdAt) {
        this.createdAt = createdAt;
    }

    public String getDescription() {
        return this.description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getIdCategory() {
        return this.idCategory;
    }

    public void setIdCategory(Integer idCategory) {
        this.idCategory = idCategory;
    }

    public String getName() {
        return this.name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public BigDecimal getPrice() {
        return this.price;
    }

    public void setPrice(BigDecimal price) {
        this.price = price;
    }

    public String getState() {
        return this.state;
    }

    public void setState(String state) {
        this.state = state;
    }

    public Short getStock() {
        return this.stock;
    }

    public void setStock(Short stock) {
        this.stock = stock;
    }

    public Date getUpdatedAt() {
        return this.updatedAt;
    }

    public void setUpdatedAt(Date updatedAt) {
        this.updatedAt = updatedAt;
    }


}

SaleBill

import java.io.Serializable;

import javax.persistence.*;

import java.util.Date;
import java.util.List;


@Entity
@Table(name="sale_bills")
@NamedQuery(name="SaleBill.findAll", query="SELECT s FROM SaleBill s")
public class SaleBill implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id_sale_bill")
    private Integer idSaleBill;

    @Column(name="company_name")
    private String companyName;

    @Column(name="company_ruc", columnDefinition="bpchar")
    private String companyRuc;

    @Temporal(TemporalType.DATE)
    @Column(name="sale_date")
    private Date saleDate;

    @Column(name="state", columnDefinition="bpchar")
    private String state;

    @ManyToOne(/*fetch = FetchType.EAGER,*/ optional=false)
    @JoinColumn(name="id_employee")
    private Employee employee;

    @OneToMany(cascade = CascadeType.ALL, /*fetch = FetchType.EAGER,*/ targetEntity = SaleBillsDetail.class)
    @JoinColumn(name="id_sale_bill_details")
    private List<SaleBillsDetail> saleBillsDetails;

    public SaleBill() {
    }

    public Integer getIdSaleBill() {
        return this.idSaleBill;
    }

    public void setIdSaleBill(Integer idSaleBill) {
        this.idSaleBill = idSaleBill;
    }

    public String getCompanyName() {
        return this.companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public String getCompanyRuc() {
        return this.companyRuc;
    }

    public void setCompanyRuc(String companyRuc) {
        this.companyRuc = companyRuc;
    }

    public Date getSaleDate() {
        return this.saleDate;
    }

    public void setSaleDate(Date saleDate) {
        this.saleDate = saleDate;
    }

    public String getState() {
        return this.state;
    }

    public void setState(String state) {
        this.state = state;
    }

    public Employee getEmployee() {
        return this.employee;
    }

    public void setEmployee(Employee employee) {
        this.employee = employee;
    }

    public List<SaleBillsDetail> getSaleBillsDetails() {
        return this.saleBillsDetails;
    }

    public void setSaleBillsDetails(List<SaleBillsDetail> saleBillsDetails) {
        this.saleBillsDetails = saleBillsDetails;
    }

    public SaleBillsDetail addSaleBillsDetail(SaleBillsDetail saleBillsDetail) {
        getSaleBillsDetails().add(saleBillsDetail);
        saleBillsDetail.setSaleBill(this);

        return saleBillsDetail;
    }

    public SaleBillsDetail removeSaleBillsDetail(SaleBillsDetail saleBillsDetail) {
        getSaleBillsDetails().remove(saleBillsDetail);
        saleBillsDetail.setSaleBill(null);

        return saleBillsDetail;
    }

}

SaleBillsDetail

import java.io.Serializable;
import javax.persistence.*;


@Entity
@Table(name="sale_bills_details")
@NamedQuery(name="SaleBillsDetail.findAll", query="SELECT s FROM SaleBillsDetail s")
public class SaleBillsDetail implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(name="id_sale_bill_details")
    private Integer idSaleBillDetails;

    @Column(name="cuantity", nullable=false)
    private Short cuantity;

    @ManyToOne(/*fetch = FetchType.EAGER,*/ optional=false)
    @JoinColumn(name="id_product")
    private Product product;

    @ManyToOne
    @JoinColumn(name="id_sale_bill")
    private SaleBill saleBill;

    public SaleBillsDetail() {
    }

    public Integer getIdSaleBillDetails() {
        return this.idSaleBillDetails;
    }

    public void setIdSaleBillDetails(Integer idSaleBillDetails) {
        this.idSaleBillDetails = idSaleBillDetails;
    }

    public Short getCuantity() {
        return this.cuantity;
    }

    public void setCuantity(Short cuantity) {
        this.cuantity = cuantity;
    }

    public Product getProduct() {
        return this.product;
    }

    public void setProduct(Product product) {
        this.product = product;
    }

    public SaleBill getSaleBill() {
        return this.saleBill;
    }

    public void setSaleBill(SaleBill saleBill) {
        this.saleBill = saleBill;
    }

}

And Test

import java.util.ArrayList;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityTransaction;
import javax.persistence.Persistence;

import org.jboss.logging.Logger;

public class Test {

    @SuppressWarnings("unchecked")
    public static void main(String[] args) {
        EntityManagerFactory emf = Persistence
                .createEntityManagerFactory("BillingTest");
        EntityManager em = null;
        EntityTransaction tx = null;
        final Logger logger = Logger.getLogger(Test.class.getName());

        try {
            em = emf.createEntityManager();
            tx = em.getTransaction();
            tx.begin();

            List<Product> products = em.createQuery("SELECT p FROM Product p")
                    .getResultList();
            List<Employee> employees = em.createQuery(
                    "SELECT e FROM Employee e").getResultList();

            SaleBill sale = new SaleBill();
            sale.setCompanyName("Centro de entrenamiento animal REX E.I.R.L");
            sale.setCompanyRuc("04844013110");
            sale.setSaleDate(new java.util.Date());
            sale.setState("1");
            sale.setEmployee(employees.get(0));

            SaleBillsDetail saleDetail = new SaleBillsDetail();
            List<SaleBillsDetail> details = new ArrayList<>();

            saleDetail.setSaleBill(sale);
            saleDetail.setProduct(products.get(0));
            saleDetail.setCuantity(Short.valueOf(3 + ""));
            details.add(saleDetail);

            saleDetail = new SaleBillsDetail();
            saleDetail.setSaleBill(sale);
            saleDetail.setProduct(products.get(1));
            saleDetail.setCuantity(Short.valueOf(5 + ""));
            details.add(saleDetail);

            sale.setSaleBillsDetails(details);
            em.persist(sale);
            em.flush();
            tx.commit();
        } catch (Exception ex) {
            logger.warn(ex.getMessage());
            System.exit(0);
        } finally {
            if(em != null) em.close();
            if(emf != null) emf.close();
        }

    }

}

The exception message:

INFO: HHH000037: Columns: [id_product, id_sale_bill_details, id_sale_bill, cuantity]
Hibernate: select product0_.id_product as id_produ1_1_, product0_.created_at as created_2_1_, product0_.description as descript3_1_, product0_.id_category as id_categ4_1_, product0_.name as name5_1_, product0_.price as price6_1_, product0_.state as state7_1_, product0_.stock as stock8_1_, product0_.updated_at as updated_9_1_ from products product0_
Hibernate: select employee0_.id_employee as id_emplo1_0_, employee0_.address as address2_0_, employee0_.birth_date as birth_da3_0_, employee0_.created_at as created_4_0_, employee0_.dni as dni5_0_, employee0_.email as email6_0_, employee0_.names as names7_0_, employee0_.state as state8_0_, employee0_.surnames as surnames9_0_, employee0_.updated_at as updated10_0_ from employees employee0_
Hibernate: insert into sale_bills (company_name, company_ruc, id_employee, sale_date, state) values (?, ?, ?, ?, ?)
Hibernate: insert into sale_bills_details (cuantity, id_product, id_sale_bill) values (?, ?, ?)
Hibernate: insert into sale_bills_details (cuantity, id_product, id_sale_bill) values (?, ?, ?)
Hibernate: update sale_bills_details set id_sale_bill_details=? where id_sale_bill_details=?
Hibernate: update sale_bills_details set id_sale_bill_details=? where id_sale_bill_details=?
mar 05, 2015 10:40:40 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
WARN: SQL Error: 0, SQLState: 23505
mar 05, 2015 10:40:40 AM org.hibernate.engine.jdbc.spi.SqlExceptionHelper logExceptions
ERROR: ERROR: duplicate key violates unique constraint «pk_sale_bill_details»
  Detail: Key is already exists (id_sale_bill_details)=(14).
mar 05, 2015 10:40:40 AM org.hibernate.engine.jdbc.batch.internal.AbstractBatchImpl release
INFO: HHH000010: On release of batch it still contained JDBC statements
mar 05, 2015 10:40:40 AM com.company.billing.model.entities.Test main
WARN: org.hibernate.exception.ConstraintViolationException: could not execute statement

I don't understand why the update sentence: update sale_bills_details set id_sale_bill_details=? where id_sale_bill_details=?

Thanks for read. I appreciated any help.

How do you generate IDs? Try changing: @GeneratedValue(strategy=GenerationType.IDENTITY) to: @GeneratedValue(strategy=GenerationType.AUTO) in all entities. — misko321, Mar 05 '15 at 16:19
In postgresql i use smallserial for auto incrementable PK's. If change IDENTITY by AUTO, throws an SQLGramarException: `ERROR: ERROR: «hibernate_sequence» doesn't exists Position: 17 mar 05, 2015 11:27:55 AM com.company.billing.model.entities.Test main WARN: org.hibernate.exception.SQLGrammarException: could not extract ResultSet` — Rex Garcia, Mar 05 '15 at 16:31
Can you try removing @JoinColumn and adding 'mappedBy' in: @OneToMany(cascade = CascadeType.ALL, /*fetch = FetchType.EAGER,*/ targetEntity = SaleBillsDetail.class, mappedBy="saleBill") private List saleBillsDetails; ? — misko321, Mar 05 '15 at 16:45
Yeah! It Works. Please move your comment as response for select it as Best Answer. Aditionally, please explain me why with mappedBy Works and with JoinColumn don't? Thank you misko! — Rex Garcia, Mar 05 '15 at 17:00

score 0 · Accepted Answer · answered Mar 05 '15 at 20:47

Remove @JoinColumn and add mappedBy in SaleBill class:

@OneToMany(cascade = CascadeType.ALL,
           /*fetch = FetchType.EAGER,*/
           targetEntity = SaleBillsDetail.class,
           mappedBy="saleBill")
private List<SaleBillsDetail> saleBillsDetails;

@OneToMany (so @ManyToOne) mapping should be managed only by one side of the relationship.
By using saleBill in mappedBy you are telling Hibernate that the owner of this relationship is field named saleBill contained in SaleBillsDetail class (that is deducted from saleBillsDetails field type, i.e. List<SaleBillsDetail>). You use @JoinColumn only in @Many side of the relation.

Read more about it here:
Can someone please explain mappedBy in hibernate?

Thank you sir, it Works and i understand the difference between mappedBy and JoinColumn now. Thanks again! — Rex Garcia, Mar 05 '15 at 23:56

Duplicate key when insert records with JPA and Hibernate

1 Answers1