0

There is few more class variable in both class, but I removed them. I tried union query but it didn't work. Is there any other way to perform this operation in single query.

Here is my union query:

Query query = entityManager.createQuery("select vu.mobileNumber AS mn from VillageUser vu where vu.mobileNumber = :inmobile UNION select ca.mobileNumber AS mn from CustomerAccount ca where ca.mobileNumber = :inmobile");

Here is my table:

public class VillageUser extends BaseEntity{

    private static final long serialVersionUID = 1L;

    @Column(name="ASSEMBLY_USER_ID")
    private Long auAccountId;

    @Column(name="FULL_NAME")
    private String fullName;

    @Column(name="USER_TYPE")
    private String userType;

    @Column(name="MOBILE_NUMBER")
    private String mobileNumber;
}

public class AssamblyUser extends BaseEntity{

    private static final long serialVersionUID = 1L;

    @Column(name="FULL_NAME")
    private String fullName;

    @Column(name="USER_TYPE")
    private String userType;

    @Column(name="MOBILE_NUMBER")
    private String mobileNumber;
}
Erba Aitbayev
  • 4,167
  • 12
  • 46
  • 81
Abhinav
  • 1
  • 2

2 Answers2

0

Unions are not supported by HQL. See this.

However you can create a SQL using Union and use this in hibernate

session.createSQLQuery(sqlQuery).addScalar("mobileNumber",StringType.INSTANCE)
Community
  • 1
  • 1
Arpit
  • 521
  • 2
  • 8
  • 22
  • i m using entity manager to perform this operation and somehow i didn't get the session to create sql query. i tried also a native query which return cartesian product but it not work if any of table is empty. does any other way to check it? – Abhinav Sep 14 '16 at 09:08
  • i know that i can use query for each table then put both query result in a single list then check list. but i don't want to hit database two time for each table. – Abhinav Sep 14 '16 at 09:11
  • can you post native query? – Arpit Sep 14 '16 at 11:26
  • you can check this [thread](http://stackoverflow.com/questions/7232704/how-to-check-if-a-given-data-exists-in-multiple-tables-all-of-which-has-the-sam) for help in query. – Arpit Sep 14 '16 at 11:34
0

finally i got the perfect way to perform this.

use entitymanager.createNativeQuery() like below:

String mobilenumber = "1212121212";

String querystring = "select 1 from (select MOBILE_NUMBER as mobile from VILLAGE_USER union all select MOBILE_NUMBER as mobile from CUSTOMER_ACCOUNT) a where mobile ="+mobilenumber;

Query query = entityManager.createNativeQuery(querystring);

List mobile = query.getResultList();

Abhinav
  • 1
  • 2