CODEWITHSUNDEEP
javasqloracle-databasehibernatejpa
Randy
Randy

Reputation: 131

SQL query returning only values from first column in SELECT statement

If I run the query below in DBVisualizer it returns one long row of values (which is the expected and correct result), with the same columns repeated (from the same table in an Oracle 11 database). So, for instance, if 3 of the columns are ID*, **FIRST_NAME and LAST NAME it would look something like this (my apologies for the crude "diagram"):

+-----+------------+-----------+----+------------+-----------+
| ID  | FIRST_NAME | LAST_NAME | ID | FIRST_NAME | LAST_NAME |
| 2   | John       | Doe       | 3  | Jane       | Doe       |

However, if I run the same native query in a java web app (using Hibernate 3.0, JPA 2.1) with an Entity class mapped to the aforementioned Oracle 11 database, with the following code snippet:

List<ForeignParty> queryResult = null;
    String sqlQuery = "SELECT f.*, p.POLICY_1_NUM, p.TOTAL_PAYOUT_AMT, f1.*, f2.*, f3.*, f4.* " +
                      "FROM FOREIGN_PARTY f " +
                      "LEFT JOIN POLICY_PAYMENT p ON f.FOREIGN_PARTY_ID = p.FOREIGN_PARTY_ID " +
                      "LEFT JOIN FOREIGN_PARTY f1 ON f.RELATED_PARTY_ID1 = f1.FOREIGN_PARTY_ID " +
                      "LEFT JOIN FOREIGN_PARTY f2 ON f.RELATED_PARTY_ID2 = f2.FOREIGN_PARTY_ID " +
                      "LEFT JOIN FOREIGN_PARTY f3 ON f.RELATED_PARTY_ID3 = f3.FOREIGN_PARTY_ID " +
                      "LEFT JOIN FOREIGN_PARTY f4 ON f.RELATED_PARTY_ID4 = f4.FOREIGN_PARTY_ID " +
                      "WHERE " +
                      "f.PARTY_TYPE_ID = '2' " +
                      "AND f.SOURCE_SYSTEM = :sourceSystem " +
                      "AND f.SOURCE_CLIENT_ID = :sourceClientId";
    query = emFatca.createNativeQuery(sqlQuery, ForeignParty.class);
    query.setParameter("sourceSystem", sourceSystem);
    query.setParameter("sourceClientId", sourceClientId);
    try {
        queryResult = query.getResultList();
        //This statement ensures that the PolicyPayment object does not go out of session scope
        for (ForeignParty fp : queryResult) {
            fp.getPolicyPaymentCollection().size();
        } 
    } catch (NoResultException nre) {
        nre.getMessage();
    }
    return queryResult;

...it only returns the values for all columns as specified by f., the first item in the SELECT statement above. I verified that by temporarily moving the f4. item in the SELECT statement to be the first item in that statement, and then saw that the values in the output matched those for that particular alias. I am wondering if this has to do with the fact that this is returning multiple columns of the same name and same Entity in one record or row, rather than separate records?? Would I need to use something like SqlResultSetMapping to individually set aliases for each iteration of the results? I am thinking that would be a lot of work, since there would be many columns/aliases to define. Any help would be greatly appreciated. Thank you!

Upvotes: 2

Views: 2077

Answers (1)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 154140

The createNativeQuery takes an Entity class and not a DTO class. If you want to map a native query to a non-Entity result you need to use @SqlResultSetMapping with a @ConstructorResult.

Upvotes: 2

Related Questions