Just did your quick tests - its interesting. The first query (the original with all the joins) returned 48 rows as I said. If I just do a single LEFT JOIN FETCH it depends what I'm fetching that determines the rows it returns:
If I join resourceConfigurationDefinition, I get 1 row (good) If I join metricDefinitions, I get 16 rows (bad) If I join evenDefinition, I get 1 row (good) If I join operationDefinitions, I get 3 rows (bad)
Here's the 5 queries I tested with:
sqlStrings[0] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[1] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[2] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[3] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
sqlStrings[4] = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
On 10/25/2010 03:09 PM, Joseph Marques wrote:
Yes, I've seen Hibernate do this from time to time when left join fetching.
Do a little investigation for me - how many rows do you get back if you only fetch eventDefinitions versus if you only fetch operationDefinitions or resourceConfigurationDefinitions? Also, what is the generated SQL for the query (if there is a join to a collection [xxxToMany mapping] anywhere, that would account for the duplicate rows of the projected data.)
On 10/23/2010 03:08 PM, John Mazzitelli wrote:
I don't get it. I run this code, and each System.out.println shows me that the results have 48 rows - but each query should only produce 1 row.
Note: if I go to admin/test/hibernate.jsp, and try this JPQL string, it actually only produces 1 row (the correct results!). I don't know what its doing differently, but here's the code that I tested with - there are actually 4 different ways I try it (two using JPA API, two using Hibernate API - for each I try using a collection of 1 ID integer, and I try using a single parameter value of Integer type).
String sqlString = "SELECT resourcetype " + // "FROM ResourceType resourcetype " + // "LEFT JOIN FETCH resourcetype.resourceConfigurationDefinition " + // "LEFT JOIN FETCH resourcetype.metricDefinitions " + // "LEFT JOIN FETCH resourcetype.eventDefinitions " + // "LEFT JOIN FETCH resourcetype.operationDefinitions " + // "WHERE ( resourcetype.id in ( :ids ) ) ";
ArrayList<Integer> idList = new ArrayList<Integer>(); idList.add(new Integer(10014));
Query q; List<?> results;
// JPA - parameter is an ArrayList of 1 ID q = entityManager.createQuery(sqlString); q.setParameter("ids", idList); results = q.getResultList(); System.out.println("1~~~~~~~~~size=" + results.size());
// JPA - parameter is a single Integer value q = entityManager.createQuery(sqlString); q.setParameter("ids", new Integer(10014)); results = q.getResultList(); System.out.println("2~~~~~~~~~size=" + results.size());
Session session; org.hibernate.Query hq; session = (Session) entityManager.getDelegate();
// Hibernate - parameter is an ArrayList of 1 ID hq = session.createQuery(sqlString); hq.setParameterList("ids", idList); results = hq.list(); System.out.println("3~~~~~~~~~size=" + results.size());
// Hibernate - parameter is a single Integer value hq = session.createQuery(sqlString); hq.setParameter("ids", new Integer(10014)); results = hq.list(); System.out.println("4~~~~~~~~~size=" + results.size()); _______________________________________________ rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel
rhq-devel mailing list rhq-devel@lists.fedorahosted.org https://fedorahosted.org/mailman/listinfo/rhq-devel