Problems with scalar values in native hibernate/jpa query using annotations

Question

I'm trying to run a simple SQL query containig a group by clause.

HistoryEntity:

@Entity
@NamedNativeQueries(value = {
    @NamedNativeQuery(name = HistoryEntity.FIND_ALL_BY_REFERENCE,
                      query = "SELECT h.id, h.reference, h.lrn "
                          + "FROM dataHistory h "
                          + "WHERE h.reference = :referenceNumber OR h.lrn = :referenceNumber",
                      resultSetMapping = HistoryEntity.FIND_ALL_BY_REFERENCE_MAPPING),
    @NamedNativeQuery(name = HistoryEntity.FIND_REFERENCE_BY_LRN,
                      query = "SELECT h.reference as reference "
                          + "FROM dataHistory h "
                          + "WHERE h.lrn = :lrn "
                          + "GROUP BY h.reference",
                      resultSetMapping = "resultMapping")                   
})
@SqlResultSetMappings(value = {
    @SqlResultSetMapping(name = HistoryEntity.FIND_ALL_BY_REFERENCE_MAPPING, entities = {
            @EntityResult(entityClass = HistoryEntity.class, fields = {
                @FieldResult(name = "id", column = "id"),
                @FieldResult(name = "reference", column = "reference"),
                @FieldResult(name = "lrn", column = "lrn")
            })
    }),
    @SqlResultSetMapping(name = "resultMapping", columns = {
            @ColumnResult(name = "reference")
    })
})
public class HistoryEntity implements Serializable {

/**
 * @param referenceNumber Referenz (LRN oder BRN)
 * @param brnList Liste von BRNs
 */
public static final String FIND_ALL_BY_REFERENCE = "HistoryEntity.findAllByReference";

public static final String FIND_ALL_BY_REFERENCE_MAPPING = "HistoryEntity.findAllByReferenceMapping";

private Integer id;

private String reference;
private String lrn;

public HistoryEntity() {}

@Id
public Integer getId() {
    return id;
}

public void setId(Integer id) {
    this.id = id;
}

public String getReference() {
    return reference;
}

public void setReference(String reference) {
    this.reference= reference;
}

public String getLrn() {
    return lrn;
}

public void setLrn(String lrn) {
    this.lrn = lrn;
}

In the service class, I execute the query as follow:

Query query = entityManager.createNamedQuery("myQuery");
query.setParameter("lrn", lrn);

List resultList = query.getResultList();

Depending on the result of the query, the list contains java.lang.Character's:

Case 1:
SQL-Result (if I'm running the sql in a sql client):
| Reference |
| 123456780678MMM |
| 123456781678MMM |
| 123456782678MMM |
| 123456783678MMM |

Java-List-Result (in Java Debug View):
[1, 1, 1, 1]

Case 2:
SQL-Result:
| Reference |
| 123456780678MMM |

Java-List-Result:
[1]

I'm looking for a way to run a simple sql query with scalar values (using hibernate/jpa) to get as result a list with the values of the result.

Is there any way to do this without using the criteria api?

If you need more information, let me know!

Many thanks in advance for your help.

Marco

How is the `reference` property in DataHistory annotated? (The answer to your question is yes, but there is something wrong at the moment. Do you want a JPA 2.0 solutions or is Hibernate propritary one ok for you?) — esej, Jun 29 '12 at 10:33
Because of DataHistory contains values from two tables, I use NamedNativeQueris and SqlResultSetMapping to fill the entity. Therefore, the property `reference` isn't annotated. Maybe I have to add a @EntityResult to @SqlResultSetMapping(name = "resultMapping"). I prefer a JPA 2.0 solution but a propritary Hibernate solution is also ok. — mbulau, Jun 29 '12 at 10:58
If reference isn't annotated, you will see this: "wrong" type on the result. Isn't DataHistory an Entity? (An entity can be spread over N tables ...) Maybe you can give us some more code/annotations? If you are not using NamedQueries / Proper Entities you are losing a lot of the benefit of using JPA, almost to where it is hard to see the point of using the technology at all. (Named)NativeQueries are just jdbc-sql-queries with some automagical mappings. — esej, Jun 29 '12 at 11:10
I updated the HistoryEntity class in the question. I hope that will helps you to understand the problem a bit more. I omitted the left join and some of the properties. I try to make a distinct query and to get a simple List with the values as result. — mbulau, Jun 29 '12 at 12:14
Working/Busy now - so I'll look at it in a couple of hours unless somebody else gives an answer. — esej, Jun 29 '12 at 12:45

score 1 · Answer 1 · edited May 23 '17 at 10:34

The Problem is a bug in hibernate, that casts the result to java.lang.Character insteat of java.lang.String. Only the first character of each row was returned in the result.

There is another question with a more detailed description of the solution:
Hibernate native query - char(3) column

If I use the cast-function in my query as follow, it works:

@NamedNativeQuery(name = HistoryEntity.FIND_REFERENCE_BY_LRN,
                  query = "SELECT cast(h.reference as VARCHAR(27)) as reference "
                      + "FROM dataHistory h "
                      + "WHERE h.lrn = :lrn "
                      + "GROUP BY h.reference",
                  resultSetMapping = "resultMapping")

PS: I found the referenced question to late. Sorry for that!

Problems with scalar values in native hibernate/jpa query using annotations

1 Answers1