Insert commands don't commit as expected

jimbodude21 · **Joined:** Mon Jun 16, 2008 12:11 pm **Posts:** 7

I am unable to get records inserted into the database predictably. I've been looking all over for an answer, but can't find one.

I'm running Hibernate 3.2.6 and HSQL 1.8.0.10 in standalone mode.

When I create and commit a transaction, I am not seeing any changes in the database. Not only do I not get the inserts, I also am not getting the create table queries. There is SQL outputted to the console, and it is correct, but it is clearly not being executed. I have found a few workarounds, but none are going to work out in the long run.

I tried running this and didn't get anything:

Code:

   public Long save(){
      Session session = HibernateUtil.getSessionFactory().getCurrentSession();
      Transaction trans = session.beginTransaction();
      Long id = (Long)session.save(this);
      trans.commit();
      return id;
   }

I tried the code below, with the same result:

Code:

   public Long save(){
      Session session = HibernateUtil.getSessionFactory().getCurrentSession();
      Transaction trans = session.beginTransaction();
      Long id = (Long)session.save(this);
      session.flush();
      session.clear();
      trans.commit();

      return id;

If I step through either implementation in the debugger, it works fine. This led me to believe it might be some sort of overloading, so I added a Thred.sleep() before the return. I found that the sleep time had to be at least 500ms to get things into the database.

Another way to get things working is to run a select after the insert, like this:

Code:

      Session session = HibernateUtil.getSessionFactory().openSession();
        session.beginTransaction();

        List result = session.createQuery("from team").list();

        session.getTransaction().commit();
        
        return result;

This would place things into the database, but it runs a ridiculous amount of insert and delete queries on the database, an example is shown here (from the HSQL log):

Code:

/*C2*/SET SCHEMA PUBLIC
CONNECT USER SA
SET AUTOCOMMIT FALSE
DELETE FROM TEAMS WHERE TEAM_ID=1
INSERT INTO TEAMS VALUES(1,'2008-06-16 12:59:02.094000000','2008-06-16 12:59:24.208000000','COL',NULL)
COMMIT
SET AUTOCOMMIT TRUE
SET AUTOCOMMIT FALSE
DELETE FROM TEAMS WHERE TEAM_ID=1
INSERT INTO TEAMS VALUES(1,'2008-06-16 12:59:02.094000000','2008-06-16 12:59:24.222000000','COL','COL')
COMMIT
SET AUTOCOMMIT TRUE
/*C1*/DELETE FROM TEAMS WHERE TEAM_ID=1
INSERT INTO TEAMS VALUES(1,'2008-06-16 12:59:02.094000000','2008-06-16 12:59:24.222000000','COL','COL')
COMMIT
SET AUTOCOMMIT TRUE
DISCONNECT
/*C2*/SET AUTOCOMMIT FALSE
INSERT INTO TEAMS VALUES(2,'2008-06-16 12:59:02.098000000','2008-06-16 12:59:02.098000000','FRG','FRG')
COMMIT
SET AUTOCOMMIT TRUE
SET AUTOCOMMIT FALSE
/*C3*/SET SCHEMA PUBLIC
CONNECT USER SA
SET AUTOCOMMIT FALSE
DELETE FROM TEAMS WHERE TEAM_ID=1
INSERT INTO TEAMS VALUES(1,'2008-06-16 12:59:02.094000000','2008-06-16 12:59:30.886000000','COL',NULL)
COMMIT
SET AUTOCOMMIT TRUE
SET AUTOCOMMIT FALSE
DELETE FROM TEAMS WHERE TEAM_ID=1
INSERT INTO TEAMS VALUES(1,'2008-06-16 12:59:02.094000000','2008-06-16 12:59:30.901000000','COL','COL')
COMMIT
SET AUTOCOMMIT TRUE
SET AUTOCOMMIT FALSE
DELETE FROM TEAMS WHERE TEAM_ID=2
INSERT INTO TEAMS VALUES(2,'2008-06-16 12:59:02.098000000','2008-06-16 12:59:30.905000000','FRG',NULL)
COMMIT
SET AUTOCOMMIT TRUE
SET AUTOCOMMIT FALSE
DELETE FROM TEAMS WHERE TEAM_ID=2
INSERT INTO TEAMS VALUES(2,'2008-06-16 12:59:02.098000000','2008-06-16 12:59:30.908000000','FRG','FRG')
COMMIT
SET AUTOCOMMIT TRUE
/*C2*/DELETE FROM TEAMS WHERE TEAM_ID=1
INSERT INTO TEAMS VALUES(1,'2008-06-16 12:59:02.094000000','2008-06-16 12:59:30.901000000','COL','COL')
DELETE FROM TEAMS WHERE TEAM_ID=2
INSERT INTO TEAMS VALUES(2,'2008-06-16 12:59:02.098000000','2008-06-16 12:59:30.908000000','FRG','FRG')
COMMIT
SET AUTOCOMMIT TRUE
DISCONNECT

I thought that might be because there was no equals() method implemented on the class, so I added one. Same result.

Why do I need to sleep to get the insert to go through? Why are there so many insert/delete queries run when I'm doing a select? What do I do to fix these problems?

Thanks in advance.

The mapping file for the class in question is below for reference.

Code:

<hibernate-mapping>
   <class name="datacontainers.Team" table="TEAMS" >
      <id name="id" column="TEAM_ID">
         <generator class="native"></generator></id>
      <property name="created" type="timestamp" column="date_created"></property>
      <property name="modified" type="timestamp" column="date_modified"></property>
      <property name="name"></property>
      <property name="abbreviation"></property>
   </class>
</hibernate-mapping>

Gayatri · **Joined:** Mon Feb 21, 2005 2:15 pm **Posts:** 9

Hmm... that seems weird.
Can you check the transaction attributes in the cfg.xml file or some spring xml file if you are using the spring framework.

-Gayatri

jimbodude21 · **Joined:** Mon Jun 16, 2008 12:11 pm **Posts:** 7

Gayatri wrote:

Hmm... that seems weird.
Can you check the transaction attributes in the cfg.xml file or some spring xml file if you are using the spring framework.

-Gayatri

I'm not using Spring. I'm configuring Hibernate programatically so I can reconfigure based on some circumstances, so I don't have a cfg.xml. Right now, I'm not doing any reconfiguration, just a single hard-coded config - here's the setup method:

Code:

   private static void configureAndStartHibernate(boolean newDb) {
      Configuration hiberCfg = new Configuration();
      hiberCfg.setProperty("hibernate.connection.url", jdbcConnectionURL);
      hiberCfg.setProperty("hibernate.connection.driver_class", "org.hsqldb.jdbcDriver");
      hiberCfg.setProperty("hibernate.connection.username","sa");
      hiberCfg.setProperty("hibernate.connection.password","");
      hiberCfg.setProperty("hibernate.connection.pool_size","1");
      hiberCfg.setProperty("hibernate.dialect","org.hibernate.dialect.HSQLDialect");
      hiberCfg.setProperty("hibernate.current_session_context_class","thread");
      hiberCfg.setProperty("hibernate.cache.provider_class","org.hibernate.cache.NoCacheProvider");
      hiberCfg.setProperty("hibernate.show_sql","true");
      hiberCfg.setProperty("hibernate.connection.autocommit","true");
      if (newDb){
         hiberCfg.setProperty("hibernate.hbm2ddl.auto", "create");
      }
      
      hiberCfg.addClass(datacontainers.Team.class);
      
      hiberSessionFactory = hiberCfg.buildSessionFactory();
   }

jimbodude21 · **Joined:** Mon Jun 16, 2008 12:11 pm **Posts:** 7

I've been hacking away at this for quite a while. I've even tried different versions of Hibernate. I couldn't get 3.3 or 3.0 to run, but 3.1 and 3.2 had the same problem. I'm completely out of ideas now. Any other suggestions are welcomed...

jimbodude21 · **Joined:** Mon Jun 16, 2008 12:11 pm **Posts:** 7

I'm still working on this problem with no luck... I've noticed that the schema export is not being committed to the database. I thought maybe if I got the schema set up, the rest of the transactions would work properly, so I tried to apply the my "sleep a long time and hope something happens" technique to the schema setup by adding this to my setup method:

Code:

hiberSessionFactory = hiberCfg.buildSessionFactory();
try{
  Thread.sleep(6000); //A little excessive, but still doesn't work...
} catch (InterruptedException e) {}

As noted above, it doesn't work. The schema SQL happens in the console, but it doesn't happen in the database.

I also tried to see if I could make it happen if I ran the sleep only on the first addition to the database, like this:

Code:

private static first = true;
public Long save(){
  ... see original method from previous post

  if (first){
    try{
      Thread.sleep(5000); //A little excessive
    } catch (InterruptedException e) {}
    first = false;
  }
}

I found that with a wait time of 500ms or 5000ms, it didn't work at all. Just to check my logic, I made first always true with a 5000ms wait - boy did that take a long time to run. I found out that my previous method of having a 500ms wait per insert was actually too short!! I got more data in the database with the 5000ms wait from the same set of test data...

What is going on?!? I'm about ready to throw Hibernate away and do this with plain SQL...

Let me know if you have any thoughts please....

Thanks in advance.

jimbodude21 · **Joined:** Mon Jun 16, 2008 12:11 pm **Posts:** 7

I guess that's all then.

For anyone who has this same problem and stumbles upon this thread - I have not found a solution, and I'm giving up on Hibernate. By now, I've wasted enough time trying to get it to work that I could have got to my goal with straight JDBC.

Cameron McKenzie · **Posted:** Fri Jun 27, 2008 10:06 am

Is there any way that your'e doing a SchemaExport, or have a create-database setting of true, which is recreating the database every time you create the SessionFactory? That would end up blowing away your inserts, and make it look like Hibernate isn't working.

Hibernate really is worth the effort. Maybe you just need a simpler tutorial to get started. Here's a fairly simple Hibernate3 setup verification. Give it a try, and see if you can't get Hibernate working.

http://jpa.ezhibernate.com/Javacode/learn.jsp?tutorial=02validatingthehibernateenvironment

jimbodude21 · **Joined:** Mon Jun 16, 2008 12:11 pm **Posts:** 7

No, I'm quite sure there is no SQL being executed on the database unless I follow the exact steps noted in my previous posts. I have a log that tells me all the insert and create SQL statements executed on the database produced by HSQL, as I mentioned in my earlier posts, to verify database activity.

Hibernate does work for me, I just have to Thread.sleep() all the time to make the changes commit. I do not believe reviewing a simpler tutorial will help this problem.

Unfortunately, I cannot justify investing anymore time into this without a definitive answer to what is causing this problem or at least a suggestion on what to do to fix it.

basm · **Joined:** Fri May 08, 2009 11:15 am **Posts:** 5

Hi jimbodude21,

did you really not find a solution? I am having exactly the same problem as you do.
I use hibernate 3.3.1 and hsqldb 1.8.0.10 and from what i can tell it's not a hibernate problem. Hibernate is doing it's job and executes the sql queries. It's the hsqldb which is not accepting them.

I was using exactly the same code and hibernate setup without the programmatic configuration and it worked fine. After I switched configuration methods all changes to the db are not permanently executed until i shutdown the program.

My programmatic configuration looks like this:

Code:

public synchronized static void init(String path) throws Exception{
        logger.debug("Der Pfad für die DB wurde gesetzt: "+path);

        if (entityManager != null) {
            throw new SecondInitException();
        }
        Map emProperties = new HashMap();

        emProperties.put("hibernate.connection.url", "jdbc:hsqldb:file:"+path+"/varianten");
        emProperties.put("hibernate.connection.username", "sa");
        emProperties.put("hibernate.connection.password", "");
        emProperties.put("hibernate.connection.driver_class", "org.hsqldb.jdbcDriver");
        emProperties.put("hibernate.connection.shutdown", "true");
        emProperties.put("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");
        emProperties.put("hibernate.current_session_context_class", "thread");
        emProperties.put("hibernate.show_sql", "true");
        //emProperties.put("hibernate.connection.autocommit", "false");
        //emProperties.put("hibernate.hbm2ddl.auto", "");

        entityManagerFactory = Persistence.createEntityManagerFactory("manager1", emProperties);
        entityManager = entityManagerFactory.createEntityManager();
    }

How did you finally fix your problem?

Greeting bas

basm · **Joined:** Fri May 08, 2009 11:15 am **Posts:** 5

Haha :-) i feel kind of dumb now that i have figured out my mistake.

The db and hibernate did work very well, i just tried to use it in a mixed up way.

I have a persisted object A with a collection of other persisted objects B. When i tried to add an object B to the collection i used a controller to create a new object B. The problem was that the old object A did still have the collection without the new B. Even after recreating A by fetching it from the db it didn't have the new B.

I should have used the addMethod of A to add B to it's collection which works perfect :-D

Greetings,
bas