Topic
3 replies Latest Post - ‏2011-07-27T21:18:39Z by sutter
izhd
izhd
2 Posts
ACCEPTED ANSWER

Pinned topic Java.util.Set as query parameter

‏2011-07-27T17:00:52Z |
Hello,

I'm using Oracle 11g, RAD 8.0.3, WAS 7.0.0.17 with latest OSGi & JPA 2.0 feature pack version.
There's persistence bundle, that has following code

public Set<String> getUserRoles(Set<String> authGroups ){
Query q= em.createQuery("SELECT r FROM ROLE_AUTH_GROUP r WHERE r.id.authGroupName IN (?1)");
q.getParameters().clear();
q.setParameter(1,authGroups);
@SuppressWarnings("unchecked")
List<RoleAuthGroupImpl> list = q.getResultList();
HashSet<String> roles = new HashSet<String>();
for (RoleAuthGroupImpl authGroup : list){
try {
roles.add(authGroup.getRole().getDisplayName());
log.info(authGroup.getRole().getDisplayName());
} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
return roles;
}

When I execute it first time, here's what I see in log
First time executed
7/27/11 8:52:03:476 PDT 0000002a SystemErr R 905062 ecoms.security TRACE WebContainer : 0 openjpa.Query - Executing query: http://SELECT r FROM ROLE_AUTH_GROUP r WHERE r.id.authGroupName IN (?1) with parameters: {1=TestConfidential, ORA_ICMSDB_OPER}

SELECT t0.AUTH_GROUP_NAME, t0.ROLE_ID, t1.ROLE_ID, t1.DISPLAY_NAME
FROM ICMS.ROLE_AUTH_GROUP t0, ICMS.ROLE t1
WHERE (t0.AUTH_GROUP_NAME IN (?, ?)) AND t0.ROLE_ID = t1.ROLE_ID(+)
params=?, ?

Second time, same input Set of strings
7/27/11 8:52:03:836 PDT 0000002a SystemErr R 905422 ecoms.security TRACE WebContainer : 0 openjpa.Query - Executing query: http://SELECT r FROM ROLE_AUTH_GROUP r WHERE r.id.authGroupName IN (?1) with parameters: {0=ORA_ICMSDB_OPER, 1=ORA_ICMSDB_OPER}

The second query doesn't return anything.

It looks like the issue is related to prepared statement cache, that becomes obvious when the same method is executed with list that has less parameters.
The workaround I came up with - to use q.setHint("openjpa.hint.IgnorePreparedQuery", true); for that query

Regards,
Igor
Updated on 2011-07-27T21:18:39Z at 2011-07-27T21:18:39Z by sutter
  • sutter
    sutter
    94 Posts
    ACCEPTED ANSWER

    Re: Java.util.Set as query parameter

    ‏2011-07-27T18:47:10Z  in response to izhd
    Hi Igor,
    It looks like you have found a workaround by turning off the prepared query cache. Normally, the parameter values do not affect the caching of the prepared SQL statements. But, I'm wondering whether the IN clause is causing the issue. Since we're parsing the Collection parameter for authGroupName, I'm wondering whether we're caching a specific version of the SQL based on one parameter collection, and then trying to use it with a different parameter collection. For example, the first time this query is used, maybe you pass in a set of 2 parameter values and that one prepared SQL gets cached. But, the next time you pass in a set of 1 parameter value and the cached SQL is expecting 2 parameters and the processing is getting confused...

    Can you verify if that type of processing is possible with your application? If this turns out to be the case, then this sounds like a bug. We either have to be smarter on how to cache and utilize queries with variable parameter lists, or we shouldn't be caching this type of query.

    Since you discovered the IgnorePreparedQuery hint, you have probably seen other ways of turning off the cache for other circumstances [1]. For the time being, I would suggest using one of these mechanisms to avoid caching of this query.

    Thanks,
    Kevin Sutter, JPA Architect, IBM WebSphere

    [1] http://openjpa.apache.org/builds/latest/docs/manual/manual.html#ref_guide_cache_querysql
    • izhd
      izhd
      2 Posts
      ACCEPTED ANSWER

      Re: Java.util.Set as query parameter

      ‏2011-07-27T19:58:17Z  in response to sutter
      Thank you Kevin for the prompt response.
      Actually you're right about different number of parameters : if I call method with list of 4 strings, and than list of 2 strings, it looks like it caches version with 4 and tries to use it with 2 strings, displaying some warnings - I haven't included this one in the post.
      But there's more to it. In my example I call method with the same exact list (TestConfidential, ORA_ICMSDB_OPER), and first time it is parsed correctly : {1=TestConfidential, ORA_ICMSDB_OPER} and second time as 2 parameters but repeating one item from list ; {0=ORA_ICMSDB_OPER, 1=ORA_ICMSDB_OPER}

      So I guess there're 2 related bugs
      • sutter
        sutter
        94 Posts
        ACCEPTED ANSWER

        Re: Java.util.Set as query parameter

        ‏2011-07-27T21:18:39Z  in response to izhd
        Hi Igor,
        The problems probably condense down to a single bug... :-)

        I looked a little deeper into the issue and I discovered that the problem was actually found and addressed in our WebSphere v8 development stream. The fix was integrated via this Apache OpenJPA JIRA: https://issues.apache.org/jira/browse/OPENJPA-1845

        So, we just need to move this fix back to the Feature Pack service stream. If you can open a PMR referencing this conversation, providing resolution for the Feature Pack should be relatively straight forward.

        In the mean time, I would suggest disabling the prepared query cache for this particular JPQL query.

        Thanks!
        Kevin Sutter, JPA Architect, IBM WebSphere