problem with ResourceTypeCriteria

Joseph Marques jmarques at redhat.com
Tue Oct 26 15:16:40 UTC 2010


  On 10/26/2010 10:07 AM, John Mazzitelli wrote:
> Just did your quick tests - its interesting. The first query (the
> original with all the joins) returned 48 rows as I said.
48 is the cross of the related rows your fetching: 1 x 16 x 1 x 3
> 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)
Yup, one resource configuration definition for every type in the system.
> If I join metricDefinitions, I get 16 rows (bad)
There are 16 metrics for the Linux platform type.
> If I join evenDefinition, I get 1 row (good)
There is, by convention, either zero or one event definition for a type.
> If I join operationDefinitions, I get 3 rows (bad)
The Linux platform type has 3 operations: discovery, viewProcessList, 
clearYumMetadataCache
> 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 at lists.fedorahosted.org
>>> https://fedorahosted.org/mailman/listinfo/rhq-devel
>> _______________________________________________
>> rhq-devel mailing list
>> rhq-devel at lists.fedorahosted.org
>> https://fedorahosted.org/mailman/listinfo/rhq-devel
> _______________________________________________
> rhq-devel mailing list
> rhq-devel at lists.fedorahosted.org
> https://fedorahosted.org/mailman/listinfo/rhq-devel



More information about the rhq-devel mailing list