Criteria + record count of Subquery

Luiz Henrique Coura · **Joined:** Thu Mar 22, 2007 9:54 pm **Posts:** 1

I would like to get the record count of subquery by the following SQL:

Code:

select 
    count(*) as total 
    from (
        select 
            pf.source,
            pf.sourcePort, 
            pf.action, 
            pf.target, 
            pf.targetPort, 
            pf.protocol, 
            count(pf.source) 
        from 
            PackageFilter pf 
        where 
            pf.idServer=1 
        and (
            pf.source like '150.163.140.112%' or pf.target like '150.163.140.112%'
        ) and 
            pf.date between '2007-03-10' and '2007-03-15' 
        group by 
            pf.source, 
            pf.action, 
            pf.target, 
            pf.targetPort
    ) as mySubquery;

i know that Hibernate doesn't support subqueries in the FROM clause, but how can i do this to use Criteria?

Thanks a lot!

batmat · **Posted:** Sat Mar 24, 2007 10:17 am

I don't understand what you want: Hibernate can't do select in the from clause, you're right, so why do you say:

Quote:

but how can i do this to use Criteria?

I guess that if you really need this query, maybe first try to isolate it in an external named query using pure SQL. Your code doesn't even need to know it's using HQL, SQL, or whatever. This way, maybe you'll be able to convert it into another query when Hibernate will eventually support select in from clause (if it ever happens :)).