Hibernate not getting correct records using query.list();

Question

I'm building an application that uses Hibernate and Mysql, My entire databse has 15 tables.

Here is the problem: I start inserting records into the database and viewing them using query.list(); to get the added records, howerver after a while of getting "correct" results, I start not having the last record I added, I add again and it's the same(the one that was showing before shows but not the last one I added), I refresh and I get the right records, I refresh again and I get 32 records instead of 43, I'm not using any complicated queries just a Select with a condition, as you can see this is really weird.

Note that I'm saving the objects and then imidiately fetching, so an insert directly followed by a select(I don't know if that can cause problems in Mysql), the records are also added into the databse perfectly, using Mysql workbench I can see that my records are added corretly into the database, I really hope someone can atleast help me debug this, because I'm not getting any errors.

I'm using Hibernate 4.3.10 and java version 1.8.0_131

Here is a piece of code that "sometimes" gives me problems when getting the results from one of the entities that I use:

getting a list of Products:

public static List<Product> productsList() {
        //singleton factory object
        SessionsGenerator FactoryObject = new SessionsGenerator();
        Session session = SessionsGenerator.getFactory().openSession();
        List<Product> list = new ArrayList<>();
        try {
            list = session.createQuery("from Product where deleted= false").list();
            System.out.println("-------------- list product: "+list); // testing from console here I get the same results on the user interface, so it can't be a Javafx problem.
        } finally {
            session.close();
        }
        return list;
    }

Code for inserting a product:

public static boolean SaveOrUpdate(Product product) {
        SessionsGenerator FactoryObject = new SessionsGenerator();
        Session session = SessionsGenerator.getFactory().openSession();
        try {
            session.beginTransaction();
            session.saveOrUpdate(product);
            session.getTransaction().commit();
        } catch (Exception e) {
            return false;
        } finally {
            session.close();
            return true;
        }
    }

Here is the Product entity class:

@Entity
@Table(name = "Product")
public class Product{
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id", nullable = false)
    int id;
    @Column(name = "code", nullable = false)
    String code;
    @Column(name = "matricule", nullable = false)
    String matricule;
    @Column(name = "marque", nullable = false)
    String marque;
    @Column(name = "type", nullable = false)
    String type;
    @OneToMany(targetEntity = Facture.class, mappedBy = "product", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Facture> factures;
    @OneToMany(targetEntity = Achat.class, mappedBy = "product", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Achat> achats;
    @Column(name = "deleted", nullable = false)
    boolean deleted;

    public Product() {
    }

    public Product(String code, String matricule, String marque,String type) {
        this.code = code;
        this.matricule = matricule;
        this.marque = marque;
        this.type = type;
        this.deleted = false;
    }
//setters and getters

Here is my hibernate configuration file:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC "-//Hibernate/Hibernate Configuration DTD 3.0//EN" "http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
  <session-factory>
    <property name="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</property>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.username">root</property>
    <property name="hibernate.connection.password">root</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/gestioncommerciale</property>
    <property name="connection_pool_size">1</property>
    <property name="hbm2ddl.auto">update</property>
    <property name="show_sql">true</property>
    <property name="hibernate.current_session_context_class">thread</property>
  </session-factory>
</hibernate-configuration>

Edit: I tried session.flush() and session.clear(), cause I though the problem has to do with cashing, but I still have the same problem, I'm starting to think this is a problem with the Mysql Workbench server.

It has been five days, I can't believe no one in the entire stackoverflow community even has the slightest idea about this, this is as strange as the problem I'm having.

Answer 1

It's definitely strange that this is happening. At first glance, the code seems fine and I don't see any issues with how you're fetching the data.

You mentioned that you are getting the correct responses in the beginning but then after awhile the incorrect data is returned.

I think this is because you're creating a new session each time and not actually closing it once you're done.

Basically, change this:

SessionsGenerator.getFactory().openSession();

To:

SessionsGenerator.getFactory().getCurrentSession();

When using openSession(), you need to explicitly flush and close the object. This may be the cause of your problem.

getCurrentSession() opens a new session if one does not exist and also automatically flushes and closes the session once done.

Also, add this into your hibernate.cfg.xml

<session-factory>
<!--  Put other elements here -->
<property name="hibernate.current_session_context_class">
          thread
</property>
</session-factory>

otherwise, you'll get exceptions occurring because you haven't configured to use getCurrentSession properly.

Additionally, try calling

session.flush(); 
session.close(); 

instead, if you don't want to take the above approach.

Hopefully, that fixes the issue!

Answer 2

I think random result is due to lack of transaction boundary for your select statement. According to this hibernate doc, you should

Always use clear transaction boundaries, even for read-only operations

Try to change your code and check the result.

Transaction tx = null;
try {
    tx = session.beginTransaction();
    list = session.createQuery("from Product where deleted= false").list();
    tx.commit();
} catch (Exception e) {
      if (tx != null) tx.rollback();
} finally {
      session.close();
}

A detailed discussion could be found here