Hibernate Crertira returns duplicates

Question

I have a table purchase, with DDL of :

CREATE TABLE purchase
(
  idpurchase serial NOT NULL,
  code character varying(50),
  date timestamp without time zone,
  totalht double precision,
  tva double precision,
  totalttc double precision,
  CONSTRAINT purchase_pkey PRIMARY KEY (idpurchase)
)

Purchase table has one to many relationship with purchaseproduct, the DDL for purchaseproduct is :

CREATE TABLE purchaseproduct
(
  idpurchaseproduct serial NOT NULL,
  idpurchase integer,
  idproduct integer,
  qty double precision,
  price double precision,
  CONSTRAINT purchaseproduct_pkey PRIMARY KEY (idpurchaseproduct),
  CONSTRAINT purchaseproduct_idproduct_fkey FOREIGN KEY (idproduct)
      REFERENCES product (idproduct) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT purchaseproduct_idpurchase_fkey FOREIGN KEY (idpurchase)
      REFERENCES purchase (idpurchase) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

Here are my POJOS:

Purchase: @Entity @Table(name = "purchase") @Access(AccessType.PROPERTY) public class Purchase { private LongProperty idPurchase; private StringProperty codePurchase; private ObjectProperty datePurchase; private DoubleProperty totalHt; private DoubleProperty tva; private DoubleProperty totalTTC;

    private Set<LineCommand> lineItems = new HashSet<LineCommand>(0);

    public Purchase() {
        this.idPurchase = new SimpleLongProperty();
        this.codePurchase = new SimpleStringProperty();
        this.datePurchase = new SimpleObjectProperty<>();
        this.totalHt = new SimpleDoubleProperty();
        this.tva = new SimpleDoubleProperty();
        this.totalTTC = new SimpleDoubleProperty();


    }

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "purchase_seq_gen")
    @SequenceGenerator(name = "purchase_seq_gen", sequenceName = "purchase_idpurchase_seq", initialValue = 1, allocationSize = 1)
    @Column(name = "idpurchase", unique = true, nullable = false)
    public long getIdPurchase() {
        return idPurchase.get();
    }

    public LongProperty idPurchaseProperty() {
        return idPurchase;
    }

    public void setIdPurchase(long idPurchase) {
        this.idPurchase.set(idPurchase);
    }

    @Column(name = "code")
    public String getCodePurchase() {
        return codePurchase.get();
    }

    public StringProperty codePurchaseProperty() {
        return codePurchase;
    }

    public void setCodePurchase(String codePurchase) {
        this.codePurchase.set(codePurchase);
    }

    @Column(name = "date")
    @Convert(converter = LocalDatePersistanceConverter.class)
    public LocalDate getDatePurchase() {
        return datePurchase.get();
    }

    public ObjectProperty<LocalDate> datePurchaseProperty() {
        return datePurchase;
    }

    public void setDatePurchase(LocalDate datePurchase) {
        this.datePurchase.set(datePurchase);
    }

    @Column(name = "totalHt")
    public double getTotalHt() {
        return totalHt.get();
    }

    public DoubleProperty totalHtProperty() {
        return totalHt;
    }

    public void setTotalHt(double totalHt) {
        this.totalHt.set(totalHt);
    }

    @Column(name = "tva")
    public double getTva() {
        return tva.get();
    }

    public DoubleProperty tvaProperty() {
        return tva;
    }

    public void setTva(double tva) {
        this.tva.set(tva);
    }

    @Column(name = "totalTTC")
    public double getTotalTTC() {
        return totalTTC.get();
    }

    public DoubleProperty totalTTCProperty() {
        return totalTTC;
    }

    public void setTotalTTC(double totalTTC) {
        this.totalTTC.set(totalTTC);
    }


    @OneToMany(mappedBy = "purchase", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
    public Set<LineCommand> getLineItems() {
        return this.lineItems;
    }

    public void setLineItems(Set<LineCommand> lineItems) {
        this.lineItems = lineItems;
    }

    @Override
    public boolean equals(Object obj) {
        if (obj == null) {
            return false;
        } else {

            if (this.idPurchase.getValue() == ((Purchase) obj).getIdPurchase())
                return true;
            else
                return false;
        }
    }

}

LineCommand :

@Entity
@Table(name = "purchaseProduct")
@Access(AccessType.PROPERTY)
public class LineCommand implements Serializable {


    private LongProperty idPurchaseProduct;
    private Product product;
    private Purchase purchase;
    private DoubleProperty sellPrice = new SimpleDoubleProperty();
    private DoubleProperty qty = new SimpleDoubleProperty();
    private DoubleProperty subTotal = new SimpleDoubleProperty();


    public LineCommand() {
        this.idPurchaseProduct = new SimpleLongProperty();
        this.product = new Product();
        this.purchase = new Purchase();

        this.sellPrice = new SimpleDoubleProperty();
        this.qty = new SimpleDoubleProperty();
        this.subTotal = new SimpleDoubleProperty();

        NumberBinding subTotalBinding = Bindings.multiply(this.qty, this.sellPrice);
        subTotal.bind(subTotalBinding);

    }

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "purchase_seq_gen")
    @SequenceGenerator(name = "purchase_seq_gen", sequenceName = "purchaseproduct_idpurchaseproduct_seq", initialValue = 1, allocationSize = 1)
    @Column(name = "idpurchaseproduct ", unique = true, nullable = false)
    public long getIdPurchaseProduct() {
        return idPurchaseProduct.get();
    }

    public LongProperty idPurchaseProductProperty() {
        return idPurchaseProduct;
    }

    public void setIdPurchaseProduct(long idPurchaseProduct) {
        this.idPurchaseProduct.set(idPurchaseProduct);
    }

    @ManyToOne
    @JoinColumn(name = "idproduct")
    public Product getProduct() {
        return product;
    }

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

    @ManyToOne
    @JoinColumn(name = "idpurchase")
    public Purchase getPurchase() {
        return purchase;
    }

    public void setPurchase(Purchase purchase) {
        this.purchase = purchase;
    }


    @Column(name = "price")
    public double getSellPrice() {
        return sellPrice.get();
    }

    public DoubleProperty sellPriceProperty() {
        return sellPrice;
    }

    public void setSellPrice(double sellPrice) {
        this.sellPrice.set(sellPrice);
    }

    @Column(name = "qty")
    public double getQty() {
        return qty.get();
    }

    public DoubleProperty qtyProperty() {
        return qty;
    }

    public void setQty(double qty) {
        this.qty.set(qty);
    }

    @Transient
    public double getSubTotal() {
        return subTotal.get();
    }

    public DoubleProperty subTotalProperty() {
        return subTotal;
    }

    public void setSubTotal(double subTotal) {
        this.subTotal.set(subTotal);
    }

    @Override
    public boolean equals(Object obj) {
        if (obj == null) {
            return false;
        } else {
            Product product = ((LineCommand) obj).getProduct();
            Purchase purchase = ((LineCommand) obj).getPurchase();

            if (this.product.equals(product) && this.purchase.equals(purchase))
                return true;
            else
                return false;
        }
    }
}

After getting all purchase result from database, i notice duplicates in purchases with two entries in purchaseproduct table.

Here is the way i retreive the list of purchases from database:

public ObservableList<Purchase> findAllByDate(LocalDate from, LocalDate to) {
        try {
            if (!session.isOpen())
                session = DatabaseUtil.getSessionFactory().openSession();
            session.beginTransaction();

            Criterion ctr1 = Restrictions.le("datePurchase", to);
            Criterion ctr2 = Restrictions.ge("datePurchase", from);
            List<Purchase> resultList = session.createCriteria(Purchase.class).add(Restrictions.and(ctr1, ctr2)).list();

            ObservableList<Purchase> list =
                    FXCollections.observableList(resultList);
            session.getTransaction().commit();
            session.close();
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

The usual way to debug this, is to look at the query your obfuscation layer generated. — , Feb 05 '16 at 08:37
after debuging i notice that hibernate it self returns duplicates. — abdou amer, Feb 05 '16 at 08:41
hope this will help you. http://stackoverflow.com/questions/25536868/criteria-distinct-root-entity-vs-projections-distinct — Musaddique S, Feb 05 '16 at 09:11
Please, add your persistent classes. DDL is not enough to understand how Hibernate fetches data. — v.ladynev, Feb 05 '16 at 09:15

score 0 · Answer 1 · edited May 23 '17 at 11:52

As he mentionned @Tipu Sultan , Her. the solution will be

using :

criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)

in my case the code will be :

public ObservableList<Purchase> findAllByDate(LocalDate from, LocalDate to) {
        try {
            if (!session.isOpen())
                session = DatabaseUtil.getSessionFactory().openSession();
            session.beginTransaction();

            Criterion ctr1 = Restrictions.le("datePurchase", to);
            Criterion ctr2 = Restrictions.ge("datePurchase", from);

            List<Purchase> resultList = session.createCriteria(Purchase.class)
                    .setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
                    .add(Restrictions.and(ctr1, ctr2)).list();

            ObservableList<Purchase> list = FXCollections.observableList(resultList);
            session.getTransaction().commit();
            session.close();
            return list;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

Hibernate Crertira returns duplicates

1 Answers1