How to select multiple columns with the same name using JPA native query?

Question

I'm having some troubles while selecting some data using sql native query through JPA. That's because I have 3 columns with the same name, "descricao".

When I execute the select operation through the createNativeQuery method of the EntityManager interface the first column value found overrides the others.

(eg. the value of the first column descricao of the given record is "foo", the second "bar" and the third "foobar", when I get this result in an array of objects (because I haven't ORM mapped the entities), wherever should be filled with the given second and third values of the column descricao are filled with the value of the first one)

I'm quite sure that's because I've used JPA once selecting directly on the database return everything properly.

Environment:

MySQL5; EJB 3.0; JPA 1.0; JBoss 5.0.0GA; JDK 1.6;

SQL query:

"select p.id, p.datapedido, b.descricao, prd.descricao, s.nome,
            usuario.email, cc.chave_cupom, prd.nome,
             ca.descricao, i.produto_id, i.valoritem,
             hc.valor_utilizado, tp.datapagamento
            ..."

Solution 1

Scalar Column Mappings in Entity Bean:

@SqlResultSetMapping(
      name="DescricaoColumnAlias",
      columns={@ColumnResult(name="B_DESCRICAO"),
               @ColumnResult(name="CA_DESCRICAO"),
               @ColumnResult(name="PRD_DESCRICAO")}
)

Now using alias for the columns in the native query as specified in column mappings.

"select p.id, p.datapedido, b.descricao as B_DESCRICAO, prd.descricao as PRD_DESCRICAO, s.nome, usuario.email, cc.chave_cupom, prd.nome, ca.descricao as CA_DESCRICAO, i.produto_id, i.valoritem, hc.valor_utilizado, tp.datapagamento..."

Creating native query by specifying resultSetMapping & query.

entityManager.createNativeQuery(queryString, "DescricaoColumnAlias");

Solution 2

I think you should use SqlResultSetMapping to specify the how the columns map to the properties of the entities.

You might find this wiki page of Eclipselink project (JPA reference implementation) useful: http://en.wikibooks.org/wiki/Java_Persistence/Querying#Result_Set_Mapping

Comments

[Noam Nevo]

@Nayan from what I saw only columns you specified in the mapping return from the native query, how does your example work?

[Stijn de Witt]

Links to (very generic) docs for very specific questions are not useful imho. At least quote the relevant section(s) so we know what part of the docs we should be looking at.