problem with ResourceTypeCriteria

John Mazzitelli mazz at redhat.com
Tue Oct 26 14:07:56 UTC 2010


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 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