Hibernate Criteria object - perform a INTERSECT

I was wondering if thei开发者_JAVA百科r is an easy to perform a oracle INTERSECT using the Criteria API object.

Using Hibernate 3.2

EX. I want all of the t.value that exist in 'table' where the names are dora and diego.

SELECT t.value from table t where t.name = 'Dora'

INTERSECT

SELECT t.value from table t where t.name = 'Diego'

I am thinking I will have to use Restriction sqlRestriction or write a oracle stored procedure that is called that does it. Any other suggestions?

Solution 1:

    Session sessionFactory = sessionFactory.getCurrentSession();         
    String intersetSQLQuery = "SEE QUERY STRING BELOW";     
    SQLQuery query = sessionFactory.createSQLQuery( intersetSQLQuery );
    query.addScalar( hibernate_mapped_field_name,Hibernate.STRING);
    //Returns a list of string objects
    nameList = query.list();

---

Looks like Intersect isn't currently supported. See https://hibernate.onjira.com/browse/HHH-1050

You may be able to get away with something like

select t.value from table t 
where t.name = 'Diego' 
and t.value in 
    (select t1 from table t1 
     where t1.name = 'Dora')

---

Why not just:

select  t.value from table t where t.name in ('Diego', 'Dora' ) 

• just like that.

Never try to omit the power of sql.