Home » Support » Forum » find repeated records by JPQL

find repeated records by JPQL

#1

Hi, I know a single command in MySQL can find repeated records:

Select * from TTT t where id in (select id from TTT s group id having count(id)>1)

I dont know how to do this using JPQL, but I use query from 430,000 records and put them into hashmap, finally it cost one hour and failed:

Exception in thread "main" [ObjectDB 2.2.9_03] javax.persistence.PersistenceException
Failed to read the value of field field image.URI.baURI using reflection (error 363)
at com.objectdb.jpa.JpaQuery.getResultList(JpaQuery.java:636)
at image.cleanDB.main(cleanDB.java:19)
Caused by: com.objectdb.o.UserException: Failed to read the value of field field image.URI.baURI using reflection
at com.objectdb.o.MSG.d(MSG.java:74)
at com.objectdb.o.UMR.M(UMR.java:863)
at com.objectdb.o.UMR.z(UMR.java:575)
at com.objectdb.o.UML.v(UML.java:528)
at com.objectdb.o.MMM.ag(MMM.java:1042)
at com.objectdb.o.UTY.aI(UTY.java:1253)
at com.objectdb.o.UTY.aH(UTY.java:1225)
at com.objectdb.o.ENH.b(ENH.java:99)
at com.objectdb.o.LDR.J(LDR.java:440)
at com.objectdb.o.LDR.E(LDR.java:159)
at com.objectdb.o.OBC.aK(OBC.java:1027)
at com.objectdb.o.OBC.aI(OBC.java:930)
at com.objectdb.o.OBC.UG(OBC.java:802)
at com.objectdb.o.SRB.l(SRB.java:149)
at com.objectdb.o.QRR.m(QRR.java:516)
at com.objectdb.o.QRR.b(QRR.java:197)
at com.objectdb.jpa.JpaQuery.getResultList(JpaQuery.java:627)
... 1 more
Caused by: java.lang.OutOfMemoryError: Java heap space
at com.objectdb.o.SBT.readArray(SBT.java:156)
at com.objectdb.o.ART.readStrictly(ART.java:170)
at com.objectdb.o.MUT.readAndAdjust(MUT.java:498)
at com.objectdb.o.UMR$P.A(UMR.java:945)
at com.objectdb.o.UMR.z(UMR.java:569)
... 15 more
Error opening zip file or JAR manifest missing: /E:/projects/objectdb/bin/objectdb.jar

Is there an elegant way as MySQL?

TIA

Institute of Computer Technologies
#2

Subqueries are not support by ObjectDB yet - see this feature request.

Getting an OutOfMemoryError while trying to load all the entity objects into a memory data structure is expected.

You may try splitting the query into two queries - first execute the inner query and then use the result collection as a parameter argument for the second outer query.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#3

>>You may try splitting the query into two queries - first execute the inner query and then use the result collection as a parameter argument for the second outer query.

Your suggestion is not feasible. I want to get a list of extinct names. so I do this:

Query q2 = em.createQuery("select e.sUrl, e.id from URI e"); //  where id<1793
  Iterator itS = q2.getResultList().iterator();

With 480,026 records, this code cost me 10 minutes and memory crash!

So is there existing solutions to clean the repeated records?

Institute of Computer Technologies
#4

I cannot see the connection between the queries in #1 and #3.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#5

#1 is the question

#2 is your suggestion

#3 is the implementing you suggestion!

>>You may try splitting the query into two queries - first execute the inner query and then use the result collection as a parameter argument for the second outer query.

Institute of Computer Technologies
#6

The inner query in #1 contains GROUP BY / HAVING.

The outer query in #1 contains the IN operator.

The query in #3 is different.

Your clarification in #5 really doesn't help - please try harder.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
#7

You do not get the point.

I ask a way to subquery in #1.

You suggest splitting it into two query in #2.

I follow your suggestion, implement the first query; it will not succeed with 480,026 records.(#3)

Then you doubt the relation between #1 and #3 !!!!!!

 

Institute of Computer Technologies
#8

Indeed I don't get the point since in #3 there is no GROUP BY / HAVING.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)

Post Reply

To post a reply and/or subscribe to update notifications - please login