56

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?

templatetypedef
  • 362,284
  • 104
  • 897
  • 1,065
ver
  • 561
  • 1
  • 4
  • 4

4 Answers4

84

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:

axtavt
  • 239,438
  • 41
  • 511
  • 482
  • 5
    For the cast I had to use the fully-qualified class name (e.g. `java.lang.String`) when using HQL inside an `@Query` annotation provided by spring-data-jpa. – Janaka Bandara Mar 26 '17 at 14:06
6

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.

01es
  • 5,362
  • 1
  • 31
  • 40
  • While casting will solve your problem in the short term i think 01es has got a point. Something seems to be wrong when you need to cast IDs. – Pascal Apr 13 '12 at 13:12
  • 4
    I see these types of non-answers all over stack overflow. On the one hand, 01es is probably right about the model being most likely broken. On the other hand, most real world systems are not refactorable at the drop of a hat. – Kramer Mar 22 '18 at 21:16
4

I had to cast it to String like so :

 @Query( value = "select new com.api.models.DResultStatus("+
            "cast(ds.demoId as java.lang.String),cast(ds.comp as java.lang.String),cast(ds.dc as java.lang.String),cast(be.buildUrl as java.lang.String)")
Alferd Nobel
  • 3,185
  • 2
  • 30
  • 35
2

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

Eduard
  • 3,176
  • 3
  • 21
  • 31
  • yes my query is wrong, but i need result for this query using hql cast... is it possible? – ver Jan 25 '11 at 08:52
  • I think its posible only in hibernate, but you are using JPA? @axtavt is giving you the link to hibernate docs which wrong, em I right? – Eduard Jan 26 '11 at 12:50