Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to select if a row exists in HQL

Tags:

sql

hibernate

hql

EDIT: Specifically talking about querying against no table. Yes I can use exists, but I'd have to do

select case when exists (blah) then 1 else 0 end as conditionTrue
from ARealTableReturningMultipleRows

In T-SQL I can do:

select case when exists(blah) then 1 else 0 end as conditionTrue

In Oracle I can do:

select case when exists(blah) then 1 else 0 end as conditionTrue from DUAL

How can I achieve the same thing in HQL?

select count() seems like the second-best alternative, but I don't want to have to process every row in the table if I don't need to.

like image 241
Adam A Avatar asked Feb 03 '11 20:02

Adam A


2 Answers

Short answer: I believe it's NOT possible.

My reasoning:

According to Where can I find a list of all HQL keywords? Hibernate project doesn't publish HQL grammar on their website, it's available in the Hibernate full distribution as a .g ANTLR file though.

I don't have much experience with .g files from ANTLR, but you can find this in the file (hibernate-distribution-3.6.1.Final/project/core/src/main/antlr/hql.g):

selectFrom!
        :  (s:selectClause)? (f:fromClause)? {                                                                                                    
                // If there was no FROM clause and this is a filter query, create a from clause.  Otherwise, throw                                
                // an exception because non-filter queries must have a FROM clause.                                                               
                if (#f == null) {                                                                                                                 
                        if (filter) {                                                                                                             
                                #f = #([FROM,"{filter-implied FROM}"]);                                                                           
                        }                                                                                                                         
                        else                                                                                                                      
                                throw new SemanticException("FROM expected (non-filter queries must contain a FROM clause)");                     
                }                                                                                                                                 

which clearly states there are some HQL queries having no FROM clause, but that's acceptable if that's a filter query. Now again, I am not an expert in HQL/Hibernate, but I believe a filter query is not a full query but something you define using session.createFilter (see How do I turn item ordering HQL into a filter query?), so that makes me think there's no way to omit the FROM clause.

like image 63
Grzegorz Oledzki Avatar answered Nov 10 '22 16:11

Grzegorz Oledzki


I'm use fake table with one row for example MyDual.

    select case when exists(blah) then 1 else 0 end as conditionTrue from MyDual
like image 40
Smertokogt Avatar answered Nov 10 '22 17:11

Smertokogt