0

I want to execute the below query in Hibernate?

(select count(*) from login where emailid='something') + (select count(*) from user where nameid='something')

1st part OK:

Query query = session.createQuery(
        "select count(*) from LoginClass login where login.emailid=:email);
query.setString("email", "something");
Long count = (Long)query.uniqueResult();

2nd part OK:

Query query2 = session.createQuery(
        "select count(*) from UserClass login where name.nameid=:name);
query2.setString("name", "something");
Long count2 = (Long)query2.uniqueResult();

My question is for hibernate (HQL) not SQL.

but I want use only one query. When I use session.createSQLQuery I can use + (addition) between the select count(*)

EDIT 1:

UNION do not work (do not add)

SELECT COUNT(*) FROM LoginClass return 85

SELECT COUNT(*) FROM UserClass return 12

SELECT COUNT(*) FROM LoginClass UNION SELECT COUNT(*) FROM UserClass return same response of 1st (so 85 again).

EDIT 2:

This post is not a duplicate of Hibernate count from multi tables

select DISTINCT is not a addition. The result contain [85, 12] (not a long 97)

My question is for + char in (HQL) doing a addition, not multiple count(*).

Stéphane GRILLON
  • 11,140
  • 10
  • 85
  • 154
  • Have a look at https://stackoverflow.com/questions/606234/select-count-from-multiple-tables . It has nothing to do with hibernate. You can try and adapt it to hibernate or use SqlQuery in hibernate – SirVaulterScoff Mar 28 '18 at 08:26
  • My question is for hibernate (HQL) not SQL. – Stéphane GRILLON Mar 28 '18 at 08:30
  • Not everything that can be done in SQL can be done in HQL. I'd say this is probably one of those. – Kayaman Mar 28 '18 at 08:31
  • UNION do not work (do not add)!! I use HQL not SQL!! – Stéphane GRILLON Mar 28 '18 at 08:42
  • I know it doesn't work. I'm surprised it doesn't throw an exception, as `UNION` shouldn't be supported by Hibernate (also you're using it wrong in your example). We understand you use HQL, but if you can't do it with HQL then you can't do it with HQL. No matter how many exclamation marks you put in there. – Kayaman Mar 28 '18 at 08:43
  • @Kayaman, `UNION` was a proposal made by someone other than you (this proposal is now deleted). – Stéphane GRILLON Mar 28 '18 at 08:47
  • It was my comment, I deleted it since I thought there'd be a better way than going with `UNION` anyway. – Kayaman Mar 28 '18 at 08:50
  • `select DISTINCT` is not a addition. The result contain [85, 12] (not a long 97). @Kayaman, you answer a little too quickly. At first, you say that it is not possible and now to answer a wrong answer. try on your computer before answering anything promptly please. – Stéphane GRILLON Mar 28 '18 at 09:03
  • @Kayaman, revome your note: `marked as duplicate` because this post explain multiple count (not a addition). It is not the same question and the same response. – Stéphane GRILLON Mar 28 '18 at 09:06
  • my question is to add to my HQL query (replace the `+` char). I do not have the hands on the code that follows the request. I can not do the sum after. – Stéphane GRILLON Mar 28 '18 at 09:11
  • I meant did you try modifying the code in the duplicate so it does a `(select count(*) from Tab1...) + (select count(*) from Tab2...)`? – Kayaman Mar 28 '18 at 09:16
  • `(select count(*) from Tab1...) + (select count(*) from Tab2...)` do not work in HQL (work only in SQL with `session.createSQLQuery(query)`) I want use `session.createQuery(query)` – Stéphane GRILLON Mar 28 '18 at 09:25
  • Really. I would've expected that if the syntax allows for inner selects in the "column" listing, it would allow addition there as well. I'll reopen this question, not that it will do you any good. Could you one more time clarify, do you want to use `HQL` or `SQL`? – Kayaman Mar 28 '18 at 09:33
  • I have an old code that uses SQL and I want to switch to HQL because in SQL I can inject SQL code into the data (and thus hack the entire database) – Stéphane GRILLON Mar 28 '18 at 09:42
  • Why don't you just fix the queries so you can't do SQL injection? Or are you under the impression that SQL in itself is unsafe? – Kayaman Mar 28 '18 at 09:55
  • yes, SQL is dangerous because the data is interpreted at the same time as the SQL syntax. It is always possible to pass prohibited data in the free fields (binary, escape char, ...) – Stéphane GRILLON Mar 28 '18 at 10:09
  • 1
    No it's not always possible. SQL injection is pretty hard to do if you're using anything better than concatenating strings to execute with `Statements`. As soon as you use placeholders for parameters, it's not possible to do direct injection anymore. Of course you could still manage to put executable content in the database, but it would be pretty stupid to execute things you've gotten from the db anyway. – Kayaman Mar 28 '18 at 10:39

0 Answers0