Entities removed from database after commit still available via query

[dmpolvo]

Hello,


here's the set-up:
* I'm using JPA, Spring's JPA Support Daos, the LocalContainerEntityManagerFactoryBean, transactions are configured via annotations.

* All daos are wired with with entitymanager in spring app context file.

* A Project can have any number of designs. In the Project class the designs property is annotated with Cascade all.

Here's the Controller with output explained in comments. The problem is explained after the code.



//project has 5 designs, we are deleting one.

project = projectManager.removeDesign(Long.parseLong(designI d), project.getId());
design = null;


// output is "showForm() - updated project returned by entityManager has 4 designs" - THIS IS CORRECT
log.debug("showForm() - updated project returned by entityManager has " + project.getDesigns().size() + " designs.");

Project projectQueried = projectManager.getProjectWithId(project.getObjectI d());
int size = projectQueried.getDesigns().size();


// output is "showForm() - projectQueried has 5 designs" - THIS IS NOT CORRECT. Should be 4.
log.debug("showForm() - projectQueried has " + size + " designs.");


Problem: Why is projectQueried showing 5 designs and not 4???

If I look in MySQL i can see that the design was indeed deleted from the database.


projectManagerImp:



@Transactional
public Project removeDesign(long designId, long projectId) {


log.debug("removeDesign() - removing design with id '" + designId + "'");
//List<PersistentDesign> projectsNewDesigns = new ArrayList<PersistentDesign>();

Project project = dao.findWithId(projectId);
Iterator<PersistentDesign> it = project.getDesigns().iterator();
log.debug("removeDesign() - project has " + (project.getDesigns()!= null ? project.getDesigns().size() : '0') + " designs");
while (it.hasNext()) {
PersistentDesign persistentDesign = it.next();
long persistentDesignId = persistentDesign.getObjectId();
if (persistentDesignId == designId) {
it.remove();
persistentDesign.setProject(null);
persistentDesignManager.deleteDesign((PersistentPh otovoltaicGridTieSystemDesign)persistentDesign);
break;
}
}
project.setLastUpdatedDate(new Date());
return dao.update(project);
}

daos:

deleting design:


public void delete(T object) {
object = getJpaTemplate().merge(object);
getJpaTemplate().remove(object);
}

updating project:





@Override
public Project update(Project project) {
return super.save(project);

}


super#save



public T save(T object) {
return getJpaTemplate().merge(object);
}

[Marten Deinum]

You should be removing the element from the collection NOT setting the project to null. It still is part of the collection, the query isn't going to the database and the cached object is returned.

So again, remove the element from the collection and persist project, element should get removed...

[dmpolvo]

Thanks for responding so quickly.

I removed the line:

persistentDesign.setProject(null);

This didn't seem to fix the issue.

Here's the console output:



24.09.2009 11:40:38 DEBUG ProjectManagerImp: removeDesign() - project has 4 designs
24.09.2009 11:40:38 DEBUG ProjectManagerImp: removeDesign() - project has 3 designs
24.09.2009 11:40:39 DEBUG PhotovoltaicGridTieSystemDesignController: showForm() - updated project returned by entityManager has 3 designs
24.09.2009 11:40:39 DEBUG PhotovoltaicGridTieSystemDesignController: showForm() - projectQueried has 4 designs.


The design is removed from the collection, the design is deleted from the database, and the entityManager returns the updated instance of project with the correct number of designs. But when I query the database via the dao after the transaction it returns the cached instance with the deleted design. I restart tomcat, and everything is correct.

As I understand it, you need to handle both sides of the relationship, no?

[Marten Deinum]

You are removing it from the database NOT the collection. You need to remove it from the collection inside the Project not set the reference to null.

The Project is retrieved and put in first level case your 're-query' isn't going to hit the database it merely check the first level cache and finds the object.

As I stated you aren't thinking in objects but in database relations. So remove the element from the collection in Project (not the database) and then simply update the Project that should take care of things...

[dmpolvo]

>You need to remove it from the collection inside the Project not set the reference to null.

In ProjectManagerImp#removeDesign, I call it.remove() that removes it from the collection.

I've also tried removing from the collection using the collections own remove method:



PersistentPhotovoltaicGridTieSystemDesign design = persistentDesignManager.getPersistentDesignWithId( designId);
project.getDesigns().remove(design);
project.setLastUpdatedDate(new Date());
log.debug("removeDesign() - project has " + (project.getDesigns()!= null ? project.getDesigns().size() : '0') + " designs");
Project updatedProject = dao.update(project);
persistentDesignManager.deleteDesign(design);
return updatedProject;


same result.

removing design from collection, but not deleting the design:



PersistentPhotovoltaicGridTieSystemDesign design = persistentDesignManager.getPersistentDesignWithId( designId);
project.getDesigns().remove(design);
project.setLastUpdatedDate(new Date());
log.debug("removeDesign() - project has " + (project.getDesigns()!= null ? project.getDesigns().size() : '0') + " designs");
Project updatedProject = dao.update(project);
design.setProject(null);
persistentDesignManager.saveDesign(design);
return updatedProject;



Interestingly, sometimes, the queried project will show the correct number of designs, (e.g. 6-1=5), but then I delete *another* design, the design is removed from the collection and deleted from the database, but then re-queried project this time does not change, getting stuck at 4. All subsequent deletes do nothing and the re-queried project always remains at 4.

[Marten Deinum]

I missed the it.remove().

normally removing it from the collection and updating the object holding thtat collection should be enough. It can be a matter of your mapping...

[dmpolvo]

OK, thanks. Do you see anything that's amiss here?



@Entity(name="Project")
@Table(name="project")
public class Project extends PersistentDomainObjectWithMetaData implements SecuredDomainObject {
[...]
@OneToMany(mappedBy="project", cascade=CascadeType.ALL)
private List<PersistentDesign> designs = new ArrayList<PersistentDesign>();
[...]
}


Super:


@MappedSuperclass
public abstract class PersistentDomainObjectWithMetaData extends PersistentDomainObject {
[...]
}


Super (common to all persistable classes):



@MappedSuperclass
public abstract class PersistentDomainObject implements Serializable {
[...]
}


PersistentPhotovoltaicGridTieSystemDesign:


@Entity(name="PVGTDesign")
@DiscriminatorValue("PVGTDesign")
public class PersistentPhotovoltaicGridTieSystemDesign extends PersistentDesign {
[...]
}

Super:



@Entity(name="Design")
@Inheritance(strategy=InheritanceType.SINGLE_TABLE )
@DiscriminatorColumn(name="DESIGN_TYPE")
@Table(name="design")
public abstract class PersistentDesign extends PersistentDomainObjectWithMetaData {
[...]
@ManyToOne
Project project;
[...]
}



Persistence.xml



<properties>
<property name="toplink.logging.level" value="WARNING" />
<property name="toplink.cache.type.GridInteractiveInverter" value="Full"/>
<property name="toplink.cache.type.Module" value="Full"/>
<property name="toplink.cache.type.VoltageConfiguration" value="Full"/>
</properties>

[dmpolvo]

Well for users of toplink essentials (and presumably eclipselink as well), a work-around seems to be to query the modified object using the toplink hit "toplink.refresh". I'm calling this refresh method every time I modify the collection of Designs that is a property of the Project object.



@Override
public void refresh(Project project) {
long id = project.getId();
EntityManager em = this.getJpaTemplate().getEntityManagerFactory().cr eateEntityManager();
Project queriedProject = null;
try {
queriedProject = (Project) em.createQuery("SELECT p FROM Project p WHERE p.id = :id")
.setHint("toplink.refresh", "true")
.setParameter("id", id)
.getSingleResult();
} catch (NoResultException ex) {
// queriedProject = null;
// return queriedProject
}
}


I'm using this instead of entityManager.refresh() because this doesn't need a transaction.

I really would like to find out though why the code in the above posts leaves a stale version of the object in the cache.