Search code examples
javaoracle-databasejpajpa-2.0nativequery

Paramertized Native Query returning empty resultset [parameter as comma separated string] (Using JPA)

I am trying to execute the native query using JPA 2.0.I have defined my native query in the orm.xml

 Orm.xml
    <named-native-query name="getAgencyUsersByRoles">
        <query><![CDATA[SELECT DISTINCT a1.USR_LOGIN,c.PERSON_FIRST_NAME,c.PERSON_LAST_NAME,a1.EMAIL, b.auth_role_code FROM USERS a1,PERSON_AUTH_ROLES b,PERSON c WHERE ((b.group_code = 'RCATAG' and a1.person_id=b.person_id and b.person_id=c.person_id AND b.auth_role_code IN (?)) OR (b.group_code = 'RCEMP' and a1.person_id=b.person_id and b.person_id=c.person_id AND b.auth_role_code IN (?) ) ) AND a1.email IS NOT NULL AND a1.status in ('ACTIVE','PASSWORD EXPIRED')  ORDER BY a1.usr_login]]></query>
    </named-native-query>

Code from where I am executing the query using jpa.

    Query query = getEntityManager()
            .createNamedQuery(NotificationPersistenceConstants.GET_AGENCY_USERS_BY_ROLES);
    query.setParameter(1, roles);
    query.setParameter(2, cccRoles);
    // fetch the results
    List<Object[]> list = query.getResultList();

This query doesn't throws any exception while execution I verified my appplication's jpa logs but returns me empty resultset.

When I fire the same query in my DB server I get the resultset which proves that my query has no issues and also table holds records against this query to return.

I tried to break my native query and simply execute them as separate native queries for all tables I have in the query. Below is what I tried with the DISTINCT clause to check if DISTINCT keyword could be the culprit. But they all worked good and gave result under List<Object[]> 

SELECT DISTINCT a1.USR_LOGIN FROM USERS a1 ORDER BY a1.usr_login
SELECT DISTINCT b.auth_role_code FROM PERSON_AUTH_ROLES b
SELECT DISTINCT c.PERSON_LAST_NAME FROM PERSON c

Updated the roles & cccRoles query paramters which I am passing in the query.

roles = 'teller','lender','bacth',etc... // This list is dynamically created at runtime
cccRoles = 'cccadmin','ccuser',etc // This list is dynamically created at runtime

Now I am not sure what is the issue.

Query? - Should not be as I verified running the same query directly in DB server and worked good.

EntityManager (JPA)? - Verified all configurations and also executed individual queries mentioned above and got the result back.

Joins?...

Please help me if anyone encountered the same issue using WAS 8.5, JPA 2.0.

Solution

  • Ok I was able to drill down to my problem and found the issue was with the parameters which I was setting my Query object.

    Culprit lines of code:

    String roles = 'teller','lender','bacth';// This list is dynamically created at runtime of nth elements
Strign cccRoles = 'cccadmin','ccuser';// This list is dynamically created at runtime of nth elements
Query query = getEntityManager().createNamedQuery(NotificationPersistenceConstants.GET_AGENCY_USERS_BY_ROLES);
query.setParameter(1,roles);
query.setParameter(1,cccRoles);

    Here in my above code the roles & cccRoles string is getting substituted against a single ? placeholders in my query string, so when the query was interpreted to find, the whole string (i.e. 'teller','lender','bacth') was matched against a mentioned column b.auth_role_code against each db record in the db and hence it didn't found any record against it.

    Whereas, if I would build the same query string in java by substituting directly this string parameters (roles & cccRoles) in the query and then call createNativeQuery() it works something like:

    Query query = getEntityManager().createNativeQuery("SELECT DISTINCT a1.USR_LOGIN,c.PERSON_FIRST_NAME,c.PERSON_LAST_NAME,a1.EMAIL, b.auth_role_code FROM USERS a1,PERSON_AUTH_ROLES b,PERSON c WHERE ((b.group_code = 'RCATAG' and a1.person_id=b.person_id and b.person_id=c.person_id AND b.auth_role_code IN ('teller','lender','bacth')) OR (b.group_code = 'RCEMP' and a1.person_id=b.person_id and b.person_id=c.person_id AND b.auth_role_code IN ('cccadmin','ccuser') ) ) AND a1.email IS NOT NULL AND a1.status in ('ACTIVE','PASSWORD EXPIRED')  ORDER BY a1.usr_login");

    Bottomline: Whenever a subsitition of ?(placeholder) happens in the sql query using setParameter in Query object it is mapped as a single column value even though the passed parameter string is comma separated.

    But when the same query is built in java by concatenating the parameter string directly (without using setParameter) and then executed, it works and considers it as a list of string to find with.

    Possible solutions to such situations:

    • Replace and build the complete query concatenating all such dynamic list of string params.(without using setParameter()) ans execute using createNativeQuery()
    • Use the Criteria Builder API of JPA provider and build the query and then execute. (Recommended)

    • Build required no. of ?(placeholders) based on the size of our List and then call equivalent no. of setParameters in the loop.

    • ?? (Anyone has any other better solution to such situation plz help.)

    Although I still trying to get an answer as to what is the difference between building the same query string at runtime in java and calling createNativeQuery vs when I set the query parameters using query.setParameter().