Eliminate Select query and do the update with just the right

raster3 · **Joined:** Sat Feb 18, 2006 1:44 am **Posts:** 15

1. My entity is configured for dynamic inserts and updates
2. I obtain my entity using getReference on the entityManager
3. I update a single field on this entity and then try to persist it

The update sql will be of just the updated column.Thats exactly whats happening.

Based on what I read the entity should be fetched lazily and its fields should not be loaded at all.
However I noticed that the sql is actually being executed for a select on all the fields even though the entity is fetched lazily using getReference.

Q1) Is that happening because any attempt to set any basic field will cause first all the basic fields to be loaded even if the fields specify lazy fetch?
Q2) Is the problem this that unless hibernate executes a select it will not be able to tell which fields are dirty and need an update? If so is there any way of informing hibernate about actual dirty field name?
Q3) Any suggestions on what if done will eliminate the select sql?

I tried playing with entityManager.setFlushMode(FlushModeType.COMMIT) and also setting "selectBeforeUpdate=false" but did not work.

Code:

@Entity
@org.hibernate.annotations.Entity(dynamicInsert=true, dynamicUpdate=true)
public class VerifyColControlInSql
{
@Id
@GeneratedValue
private long id;

@Basic(fetch=LAZY)
private String col1;
@Basic(fetch=LAZY)

private String col2;
String getCol1()
{
   return col1;
}
void setCol1(String col1)
{
   this.col1 = col1;
}
String getCol2()
{
   return col2;
}
void setCol2(String col2)
{
   this.col2 = col2;
}
long getId()
{
   return id;
}
}

A code :
EntityManagerFactory emf = Persistence
            .createEntityManagerFactory("learnJpa");
      EntityManager entityManager = emf.createEntityManager();

//insert code. Please  ignore
      entityManager.setFlushMode(FlushModeType.COMMIT);
      EntityTransaction tx = entityManager.getTransaction();
      tx.begin();

      VerifyColControlInSql v= new VerifyColControlInSql();
      v.setCol1("col1");
      entityManager.persist(v);
       v= new VerifyColControlInSql();
         v.setCol1("col1.1");
         entityManager.persist(v);
      
      
      tx.commit();
      entityManager.close();
      //long id=v.getId();//1
      //new em
//code to look at. Please look at this code

      entityManager = emf.createEntityManager();
      entityManager.setFlushMode(FlushModeType.COMMIT);
      tx = entityManager.getTransaction();
      
      tx.begin();
      v=entityManager.getReference(VerifyColControlInSql.class, 1l);
      v.setCol2("col2");
      
      tx.commit();
      entityManager.close();
      emf.close();

SQLS:
insert 
    into
        VerifyColControlInSql
        (col1) 
    values
        (?)

 select
        verifycolc0_.id as id0_0_,
        verifycolc0_.col1 as col2_0_0_,
        verifycolc0_.col2 as col3_0_0_ 
    from
        VerifyColControlInSql verifycolc0_ 
    where
        verifycolc0_.id=?


update
        VerifyColControlInSql 
    set
        col2=? 
    where
        id=? 
        and col2 is null

[/code]

mmerder · **Posted:** Mon Jan 26, 2009 12:48 pm

Q1) Yes, hibernate initializes a proxy when you access one of its fields (unless the identifier-getter)

Q2) Actually, I don't know. dynamic-update could be the reason.

Q3) Try to annotate your getters instead of your fields, as this could cause hibernate only to initialize a proxy when one of its getters is called (what you are not doing)

mmerder · **Posted:** Mon Jan 26, 2009 12:51 pm

Hibernate also supports lazy property fetching, have you tried this?

raster3 · **Joined:** Sat Feb 18, 2006 1:44 am **Posts:** 15

I am using JPA over Hibernate.
I am intersted in understanding some aspects of how exactly hibernate works.

I have configured my entity to do dynamic updates using - @org.hibernate.annotations.Entity(dynamicInsert=true, dynamicUpdate=true)

The update sql accordingly uses only the correct subset of the fields.

My current question is this?
What logic does hibernate use to figure out which fields are dirty so that it can create the corresponding dynamic update sql query? Does it keep track of which all setters have been invoked? Does it do a select to get the actual state and compare the state with the state of the entity?

mmerder · **Posted:** Tue Jan 27, 2009 4:36 am

No it does not keep track of the setters invoked. It needs to select the entity to get it's old state, if it is detached. If it is still in the session, there is no select necessary.
I don't know if if you are working on detached objects, if yes, you have to do a merge (in JPA), do reattach it to the session. So an extra select before update is done anyway and dynamic-update will not cause another one.

raster3 · **Joined:** Sat Feb 18, 2006 1:44 am **Posts:** 15

No this is not about detached objects.
Also I read that Hibernate actually caches a copy of the original entity and that copy helps it determine what exactly are the dirty fields.
So there is no need why it does the select.
But the select is happening. Wonder why?

I even tried using ((Session)entityManager.getDelegate()).setFlushMode(FlushMode.MANUAL); with a flush before commit.

mmerder · **Posted:** Wed Jan 28, 2009 4:50 am

mmerder wrote:

Q3) Try to annotate your getters instead of your fields, as this could cause hibernate only to initialize a proxy when one of its getters is called (what you are not doing)

Have you tried this? Annotating fields instead of getters tells hibernate, that a proxy is to be initialized, when you access a field directly. remember that you can not mix annotations on fields and getters within a class.

raster3 · **Joined:** Sat Feb 18, 2006 1:44 am **Posts:** 15

Well I tried using the annotation on the getter same results.
The proxy is getting created but it gets loaded each time I try to update the fields or even when I want to just delete the entity.

It appears to me that the proxy will not prevent the select query for the basic fields even if all you want is to do a delete or just an update on certain fields.

To execute the dynamicupdate query it needs to keep track of the modified fields. This is done by making a copy of the entity with the original fields so that the copy can be compared with the modified actual entity instance. But the original fields can be got only by doing a select.

I am trying to understand the concepts and any more help will be appreciated.

Code:

package pack1;

import static javax.persistence.FetchType.LAZY;

import javax.persistence.Basic;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;

@Entity
@org.hibernate.annotations.Entity(dynamicInsert = true, dynamicUpdate = true, selectBeforeUpdate = true)
public class VerifyColControlInSql1
{

   public VerifyColControlInSql1()
   {
      
   }

   private long id;

   private String col1;

   private String col2;

   @Basic(fetch = LAZY)
   public String getCol1()
   {
      return col1;
   }

   void setCol1(String col1)
   {
      this.col1 = col1;
   }

   @Basic(fetch = LAZY)
   public String getCol2()
   {
      return col2;
   }

   void setCol2(String col2)
   {
      this.col2 = col2;
   }

   @Id
   @GeneratedValue
   public long getId()
   {
      return id;
   }

   private void setId(long id)
   {
      this.id = id;
   }
}

Code:

package pack1;

import java.sql.SQLException;

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

import junit.framework.TestCase;

import org.hibernate.FlushMode;
import org.hibernate.Session;

public class NewTest1 extends TestCase
{

   public void testColControl() throws SQLException
   {
      EntityManagerFactory emf = Persistence
            .createEntityManagerFactory("learnJpa");
      EntityManager entityManager = emf.createEntityManager();
      
      EntityTransaction tx = entityManager.getTransaction();
      tx.begin();

      VerifyColControlInSql1 v= new VerifyColControlInSql1();
      v.setCol1("col1");
      entityManager.persist(v);
       
      
      
      tx.commit();
      entityManager.close();
      long id=v.getId();//1
      //new em
      entityManager = emf.createEntityManager();
      ((Session)entityManager.getDelegate()).setFlushMode(FlushMode.MANUAL);
      tx = entityManager.getTransaction();
      
      tx.begin();
      v=entityManager.getReference(VerifyColControlInSql1.class, id);
      v.setCol2("col2");
      entityManager.flush();
      tx.commit();
      entityManager.close();
      emf.close();
   }
   
   public void testDelete() throws SQLException
   {
      EntityManagerFactory emf = Persistence
            .createEntityManagerFactory("learnJpa");
      EntityManager entityManager = emf.createEntityManager();
      
      EntityTransaction tx = entityManager.getTransaction();
      tx.begin();

      VerifyColControlInSql1 v= new VerifyColControlInSql1();
      v.setCol1("col1");
      entityManager.persist(v);
      
      
      tx.commit();
      entityManager.close();
      long id=v.getId();//1
      //new em
      entityManager = emf.createEntityManager();
      ((Session)entityManager.getDelegate()).setFlushMode(FlushMode.MANUAL);
      tx = entityManager.getTransaction();
      
      tx.begin();
      v=entityManager.getReference(VerifyColControlInSql1.class, id);
      entityManager.remove(v);
      entityManager.flush();
      tx.commit();
      entityManager.close();
      emf.close();
   }
   

}