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
            ..."

Answer 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");