Hi,

I am using Hibernate with JPA support in my application. I am facing a problem in update operation which is given below,

I have two Enitity objects named SearchProfile and SearchCriteriaAssociation. for two tables
[u]
[b]Search_Profile[/b][/u]

SEARCH_PROFILE_SEQNO - Number, (PK)
SEARCH_PROFILE_NAME - varchar
SHORT_DESCRIPTION - varchar
[u][b]
Search_Criteria_Association[/b[/u]]

SEARCH_PROFILE_SEQNO - Number, (PK)
SELECT_CRITERIA - varchar (PK)
SELECT_VALUE- varchar

SearchProfile has One - many relationship with Search_Criteria_Association.

[b]In SearchProfile Entity[/b]

@OneToMany(targetEntity = SearchCriteriaAssociation_impl.class, fetch = FetchType.EAGER, cascade={javax.persistence.CascadeType.ALL})
@Cascade({org.hibernate.annotations.CascadeType.DELETE })
@JoinColumn(name = "SEARCH_PROFILE_SEQNO", referencedColumnName = "SEARCH_PROFILE_SEQNO", insertable = true, updatable = true)
private List<SearchCriteriaAssociation> searchCriteriaAssociationList;

[b]In SearchCriteriaAssociation entity[/b]

@ManyToOne(targetEntity = SearchProfile_impl.class, fetch = FetchType.LAZY)
@OnDelete(action = org.hibernate.annotations.OnDeleteAction.CASCADE)
@JoinColumn(name = "SEARCH_PROFILE_SEQNO", referencedColumnName = "SEARCH_PROFILE_SEQNO", insertable = false, updatable = false)
private SearchProfile searchProfile;

My code for Update is
SearchProfile searchProfile = this.findSearchProfileById(searchProfileToBeUpdated.getSeqNo());
Session session = (Session) entityManager.getDelegate();
session.evict(searchProfile);
entityManager.clear();
entityManager.getTransaction().begin();
searchProfile.setName(searchProfileToBeUpdated.getProfileName());
searchProfile.setShortDescription(searchProfileToBeUpdated.getShortDescription());
searchProfile.setUserGroupName(searchProfileToBeUpdated.getUserGroupName());
searchProfile.setApplicationId(searchProfileToBeUpdated.getApplicationId());
searchProfile.setSearchCriteriaAssociationList(searchProfileToBeUpdated.getSearchCriteriaAssociationList());
searchProfile=entityManager.merge(searchProfile);
entityManager.getTransaction().commit();
entityManager.flush();


I have two records in Search_Criteria_Association table associated to one recorrd in Search_Profile table.

I am trying to modify the value of first record in Search_Criteria_Association and trying to add a new record and hence no need for the existing second record.

I am geting the following error

[b]org.springframework.orm.hibernate3.HibernateJdbcException: JDBC exception on Hibernate data access: SQLException for SQL [update M_SEARCH_CRITERIA set SEARCH_PROFILE_SEQNO=null where SEARCH_PROFILE_SEQNO=? and SEARCH_PROFILE_SEQNO=? and SELECT_CRITERIA=?]; SQL state [72000]; error code [1407]; Could not execute JDBC batch update; nested exception is org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update[/b]

Can you please help me in this regard,

best Regards,
Sudheesh K S

I see SEARCH_PROFILE_SEQNO=? twice in the sql. Is it this expected?



Is SearchCriteriaAssociation a join table of SearchProfile and SearchCriteria? If so, why are you using a link table for manytoone association?


And I think the SQL error is caused coz of setting null to a non-null column. Most probably the Search_Criteria_Association.SEARCH_PROFILE_SEQNO which is part of PK of that table.

Hi Litty,

Thanks a lot for your help. But I don't know why Hibernate is generating query like this.

update M_SEARCH_CRITERIA set SEARCH_PROFILE_SEQNO=null where SEARCH_PROFILE_SEQNO=? and SEARCH_PROFILE_SEQNO=? and SELECT_CRITERIA=?]

SEARCH_PROFILE_SEQNO is listed twice in the query.

More over your guess is correct.

M_SEARCH_CRITERIA is a connection table between M_Search_Profile and M-Select_Criteria. But in addition to PK that we have got one more additional field for Select Criteria Value.

Is it the correct way?

Best regards,
Sudheesh K S

Okk now I got it.

Search_Criteria_Association is a link table but as you have extra info in the link table you have made the link table an entity. And instead of many-to-many b/w SearchProfile and SelectCriteria you made :

SearchProfile one-to-many SearchCriteriaAssociation
SelectCriteria one-to-many SearchCriteriaAssociation

and

SearchCriteriaAssociation many-to-one SearchProfile
SearchCriteriaAssociation many-to-one SelectCriteria

But the problem with your case is that your SearchCriteriaAssociation is having a composite primary key made of foreign keys referring to SearchProfile and SelectCriteria. Now if you delete an association hibernate simply try to set null to the corresponding foriegn key column which in your case is part of primary key also and that gives you an exception (You set null to a non-null column).

So to solve this issue you should have delete-orphan in the cascade for the one-to-many relations. So that instead of setting null to the foreign key field hibernate will delete the link table row when an association is deleted. But if you think that deleting an association is not worth deleting the link table entity (ie the link table entity has an existence of its own) then instead of having a composite PK your link table should be having a surrogate id column. Like:
SearchCriteriaAssociation
@Id
@GeneratedValue
@Column(name = "id")
private long id;

Search_Criteria_Association
ID - Number (PK)
SEARCH_PROFILE_SEQNO - Number, (FK to Search_Profile)
SELECT_CRITERIA - varchar (FK to Select_Criteria)
SELECT_VALUE- varchar

Hi Litty,

Thanks a lot fr the valuable help :)

Your approach is good enough but i am not sue whether we can change the structure.

However i am happy to inform you that it seems to be working now. I just changed the following declarations in searchProfile entity


@OneToMany(targetEntity = SearchCriteriaAssociation_impl.class, fetch = FetchType.EAGER, cascade={CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
@Cascade({org.hibernate.annotations.CascadeType.SAVE_UPDATE,org.hibernate.annotations.CascadeType.DELETE, org.hibernate.annotations.CascadeType.REMOVE, org.hibernate.annotations.CascadeType.DELETE_ORPHAN})
@JoinColumn(name = "SEARCH_PROFILE_SEQNO", referencedColumnName = "SEARCH_PROFILE_SEQNO",insertable = false, updatable = false)
private List<SearchCriteriaAssociation> searchCriteriaAssociationList;


It is updating to the databse... But i am getting a new error which is listed below,

org.springframework.dao.InvalidDataAccessApiUsageException: no transaction is in progress; nested exception is javax.persistence.TransactionRequiredException: no transaction is in progress
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:289)

Once data is persisted, i am getting this exception.

The code for update methos is given below,


entityManager = getJpaTemplate().getEntityManagerFactory().createEntityManager();

SearchProfile searchProfile = this.findSearchProfileById(searchProfileToBeUpdated.getSeqNo());
Session session = (Session) entityManager.getDelegate();
session.evict(searchProfile);
entityManager.clear();
entityManager.joinTransaction();
System.out.println("################# "+searchProfileToBeUpdated);
searchProfile.setName(searchProfileToBeUpdated.getProfileName());
searchProfile.setShortDescription(searchProfileToBeUpdated.getShortDescription());
searchProfile.setUserGroupName(searchProfileToBeUpdated.getUserGroupName());
searchProfile.setApplicationId(searchProfileToBeUpdated.getApplicationId());
searchProfile.setSearchCriteriaAssociationList(searchProfileToBeUpdated.getSearchCriteriaAssociationList());
searchProfile=entityManager.merge(searchProfile);
entityManager.getTransaction().commit();

In the beginning of the method i have declared transaction attribute as

@Transactional(propagation = Propagation.REQUIRED)


Best Regards,
Sudheesh K S

Hi Litty,

Thanks a lot fr the valuable help :)

Your approach is good enough but i am not sue whether we can change the structure.

However i am happy to inform you that it seems to be working now. I just changed the following declarations in searchProfile entity


@OneToMany(targetEntity = SearchCriteriaAssociation_impl.class, fetch = FetchType.EAGER, cascade={CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
@Cascade({org.hibernate.annotations.CascadeType.SAVE_UPDATE,org.hibernate.annotations.CascadeType.DELETE, org.hibernate.annotations.CascadeType.REMOVE, org.hibernate.annotations.CascadeType.DELETE_ORPHAN})
@JoinColumn(name = "SEARCH_PROFILE_SEQNO", referencedColumnName = "SEARCH_PROFILE_SEQNO",insertable = false, updatable = false)
private List<SearchCriteriaAssociation> searchCriteriaAssociationList;


It is updating to the databse... But i am getting a new error which is listed below,

org.springframework.dao.InvalidDataAccessApiUsageException: no transaction is in progress; nested exception is javax.persistence.TransactionRequiredException: no transaction is in progress
at org.springframework.orm.jpa.EntityManagerFactoryUtils.convertJpaAccessExceptionIfPossible(EntityManagerFactoryUtils.java:289)

Once data is persisted, i am getting this exception.

The code for update methos is given below,

try{
entityManager = getJpaTemplate().getEntityManagerFactory().createEntityManager();

SearchProfile searchProfile = this.findSearchProfileById(searchProfileToBeUpdated.getSeqNo());
Session session = (Session) entityManager.getDelegate();
session.evict(searchProfile);
entityManager.clear();
entityManager.joinTransaction();

searchProfile.setName(searchProfileToBeUpdated.getProfileName());
searchProfile.setShortDescription(searchProfileToBeUpdated.getShortDescription());
searchProfile.setUserGroupName(searchProfileToBeUpdated.getUserGroupName());
searchProfile.setApplicationId(searchProfileToBeUpdated.getApplicationId());
searchProfile.setSearchCriteriaAssociationList(searchProfileToBeUpdated.getSearchCriteriaAssociationList());
searchProfile=entityManager.merge(searchProfile);
entityManager.getTransaction().commit();
entityManager.flush();
}
finally
{
entityManager.close();

}

In the beginning of the method i have declared transaction attribute as

@Transactional(propagation = Propagation.REQUIRED)


Best Regards,
Sudheesh K S

I see that you have added the DELETE ORPHAN cascade type which have solved the first problem. But I have no expertise in JTA or this @Transactional annotation. So I wont be able to help you here.