Is there a way to write the exists query?

salesman( ssn)
sale(carvin, model , ssn, month)
employeeofMotnh(ssn,month)

Query A) select all salesman who sold Taurus (which is the model) .
I need distinct values.


s.createQuery(
from salesman man
where exists ( from sale as sold
where sold.ssn = man.ssn )


This will work. However this is very close to SQL .


My queries will need to be dynamically build . Fo example the query criteria could be Query A + the salesman should be employee of month
(If salesman is employee of month and sold a a taurus car)

Is there a better way of writing this? I need to make this easier for developers not intimately familar with SQL

thanks

Is exists supported ?
I want to use Hibernate to make things easier for Java developers without making them write SQL

thanks

thinking object and understanding HQL, i think you won't have any problem to query this.

Can you please give me an example in HQL.

Show me all departments where there is an employee whose name is Mark


Class Dept( deptName)
Class Emp(ssn,empName, deptName)


Assumptions-
I want unique departments .
LeftOuterJoin gives me duplicates.
I am on a webpage so need paging (first 5 , next 5 rectords etc)
Ordering.
(So I am reluctant to use UniqueResult() for performance reason and not sure if paging will be incorrect )



Any help or pointers will be greatly appreciated

If you need to build dynamic queries, use the criteria API:



Ah yes, and start reading the docs and the API.

Please let me know if I am doing something incorrectly here-


select name
from salesman
where exists
(select 1 from sale
where salesman.ssn= sale.ssn
and sale.model='Taurus');
Order by name;


Results in this case is
-----------------------------
Marc
Sam


Lets say Marc sold 100 taurus
sam sold 5 cars



tx = s.beginTransaction();
List list1 = s.createCriteria(salesman.class)
.createCriteria("sale", "sale")
.add( Expression.eq("model", "Tauras") )
.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)
.setMaxResults(5)



Doing the same in Hibernate HQL as suggested will give incorrect result.
Why Marc sold 100 cars , in the join between salesman and sale there are
100 records for marc and 5 for sam.
Limiting results to 5 will return the first 5 records. (Not what I want)


exists is supposed to return a unique value of salesman in the sql query.
Doing a Criteria.DISTINCT_ROOT_ENTITY will filter the results after the query is executed .
Again if I want to show the first 5 salesman then the next 5 etc the results will all be incorrect.


Is exists supported in Hibernate?
thanks

Well in that case there is no other way than the "working one" you posted.