How do I write hql query with cast?

Question

I need to combine 2 tables using hql, both are having common column, but table1 common column is integer and table2 common column is String

For example,

select a.id as id,a.name as name,b.address as address 
from Personal as a,Home as b 
where a.id=b.studid

Here a.id is an integer while b.stduid is a string, but Data of both columns is the same.

How can I get the result of the query using hql query?

Answer 1

Just noticed that you are using JPA, there you can not cast or convert datatpes. In the query language, only values of the same type can be compared! read in http://download.oracle.com/javaee/5/tutorial/doc/bnbuf.html#bnbvu

Answer 2

HQL supports CAST (if underlying database supports it), you can use it:

select a.id as id,a.name as name,b.address as address 
from Personal as a,Home as b
where cast(a.id as string) = b.studid 

See also:

• 16.10. Expressions

Answer 3

You really need to think why have you got a need to join two entities by properties of different types. Most likely it suggests that some of the entities need to be refactored, which could include changing data types for columns of the underlying db tables. If the model is correct there will be no need to twist Hibernate.