I try to call createNativeQuery:

List<Identity> results = entityManager.createNativeQuery("SELECT * FROM ANDRO_IDENTITY as i where " +
" i.IDENTITY_ID NOT IN (select tk.IDENTITY_ID from ANDRO_IDENTITY_TOKEN as tk where tk.TOKEN_ID = :token_id )",Identity.class)
.setFirstResult(0)
.setParameter("token_id", tokenId)
.getResultList();

and report error: The column name TOKEN_ID is not valid.

But when I directly query in the database like:

SELECT * FROM ANDRO_IDENTITY as i where i.IDENTITY_ID NOT IN (select tk.IDENTITY_ID from ANDRO_IDENTITY_TOKEN as tk where tk.TOKEN_ID = 2)


it works well.

May I know what I miss?


Thanks

Hi quintenjiang,

it would be helpful if you'd

• provide the mappings for your entities involved with that query
• use BBCode (see the buttons below the subject line). Makes your code much easier to read.

CU
Froestel

Hi,

Identity class like:

@Entity
public class Identity {
private long ID;
private String identifier;
private String displayName;
private Token token;

}

andro_identity table design:
{
IDENTITY_ID long,
IDENTIFIER varchar,
DISPLAY_NAME varchar

}

and put token_id and joining table andro_identity_token
{
identity_id long
token_id
}

if I use native query createNativeQuery("SELECT * FROM ANDRO_IDENTITY i ", Identity.class), and try to be mapping to Identity, and exception, is lost token_id.

Question is how to design @SqlResultSetMapping(name="resultIdentity", entities=@EntityResult(entityClass=Identity.class) )

So I can query createNativeQuery("SELECT * FROM ANDRO_IDENTITY i ", "resultIdentity").


Thanks

Hi quintenjiang,

this is very confusing what you write here and I'm not sure if I get this straight. Looks like TOKEN_ID is not valid because you have token_id as the name for the table field. Database, table, and field names are (usually) case sensitive.

CU
Froestel