Scrolling and merging

[paul.setzermann]

Hi all,

I have the following problem:

I try to scroll through a big data set and simultaenously start a nested criteria query and manipulate specific objects and then write them back to the database.

I tried to flush() the session every 50 counts. I tried to use transactions. I tried several approaches but either it is not working or I get a time out exception from the transaction after scrolling and modifying 3000 objects.

Is there a possiblity to commit those modifications to the database after an interval of let´s say 50 objects!?

I also tried to set the jdbc batch parameter in persistence.xml.

Thanks in advance.

Cheers,

Paul

@Stateless
public class PredictRefsManager {
  
  /**
     * injected JPA entity manager
     */
    @PersistenceContext(unitName = "AnnotationDatabase")
    private EntityManager em;
    
    @Inject 
    private ADBAssemblyManager am;
    
  
          public void predictRefs(long speciesId, long assemblyId, String source1, String source2) throws FinderException, UpdateException {
  
          .....  
                  
               ScrollableResults scr = session.createCriteria(ADBGene.class).add(Restrictions.eq("source", source1))
                                                  .createAlias("assembly", "a").add(Restrictions.eq("a.aID", (long)speciesId))
                                                  .createAlias("species", "s").add(Restrictions.eq("s.sID", (long)assemblyId)).scroll();
  
               while (scr.next()) {
         
                    ADBGene gene = (ADBGene) scr.get(0);
  
                         List<ADBGene> refs = session.createCriteria(ADBGene.class,"g").createAlias("region", "rg").add(
                                                                                    Restrictions.and(...).list();
                            if(refs.size() > 0) {
         
                                      for (ADBGene ref : refs) {
                   
                                                ADBRelatedGene relGeneG = new ADBRelatedGene(gene, ref.getSource(), ref.getGeneID(), "predicted");
                                                ADBRelatedGene relGeneR = new ADBRelatedGene(ref, gene.getSource(), gene.getGeneID(), "predicted");
                                                em.persist(relGeneG);
                                           em.persist(relGeneR);
                                      
                                             if( refsCnt++ % 50 == 0) {
                                                em.flush();
                                                em.clear()
                                             }
                                        }
                            }
         
                  }
                  System.out.println("RefCnt= " + refCnt);
         
          }
  
  
}