Assuming my entity class looks something like this snippet:
Assuming I want to create a DAO method getUserCaseDetails(long userId, long caseId), what does the syntax look like? The variables userId and caseId correspond to particular row Ids in the User and Case tables. The criteria I've constructed in the past usually refer to Objects directly but in this case, all I have are the ids .
This creates a "sub-criteria" based on the association in the main class, in your case User. You can then add restrictions to this sub-criteria. You can actually do the whole thing in one line by just adding dot to the end of each add() method, but I've shown it separately for readability.
Hibenate critieria is a great way to query on data.
As long as your ids are mapped, you can create a query based on them. Either an Example or Restriction class can be used pretty easily and effectively. And you can do more than just look up based on id. Here's an example that looks for users with an id greater than 5:
If you have millions of rows of data to query the most important thing is to make sure that you (or your DBA) has optimised the queries (whether via HQL or Criteria) that are going to access this. You can see the queries by setting the show_sql attribute in the Hibernate configuration to true and checking your logs. You might even consider doing all the "heavy" access via Native SQL. We have a Sales Decision Support System product that queries over millions of rows of data for a particular table and all of the access on this table is optimised SQL via Hibernate native SQL queries.
Then it's just down to the basics ... trying to minimise the amount of data that you have to read or process (e.g. sorting, ordering) when performing your queries.
I'm a big fan of using the Criteria API, because it works for me, and I can do amazing things with the Hibernate Criteria API that I could never do with HQL. But, thenagain, my SQL/HQL skills aren't nearly as good as my Java skills.
However, it should be said that a few other Hibernate experts in this forum, who are more experienced than me, look down upon my reliance upon the Criteria API. If you know HQL, and you're a bit better looking than me, you can probably write more efficient queries, especially complex ones, if you can use HQL.