We are having a situation to build a query with dynamically adding "where" clause for a search screen using three different Views .
Wondering if i can use Criteria API to built the query .
For example
Criteria criteria = session.createCriteria(DeviceSummaryView.class)
.createCriteria(DeviceContactView.class)
.createCriteria(HostNameContactView.class);

I am not sure if this works or is supported by Hibernate API as the moment i put this code snippet into Eclipse IDE it gives error saying createCriteria() Not Applicable for arguments

Please note i am able to successfully build & run the query with single pojo i.e. session.createCriteria(DeviceSummaryView.class).

Now i wanna extend it to join with other views such as
session.createCriteria(DeviceSummaryView.class)
.createCriteria(DeviceContactView.class)
.createCriteria(HostNameContactView.class);


Any pointers/suggestions will be highly appreciated

Hi bansilal,

I think you should just write your request like this:



Where you only use the Class definition on the first Criteria (for return type) and then you directly use attribute name to go forward into your data structure (to be able to set constraint on specific attributes). Then you can set constraint on HostNameContactView attribute (address attribute in this example) as follow:



Hope its help,

Regards.

Joël

Hi Joel
Thanks for quick response. The search screen in my case has fields related to more than one database views.
For example
The Device Search screen in my case has following fields
- hostName & ipAddress related to DeviceSummaryView pojo
- deviceContact related to DeviceContactView pojo
- hostContact & ipAddress related to HostNameContactView pojo

As user may search on any of these fields , i wanna use Criteria query as something like this


so that it result into following query



Currently i am able to successfully search on the fields related to single pojo i.e. ipAddress & hostName using session.createCriteria(DeviceSummaryView.class)

Any pointers/suggestions on how to extend this to search on additional fields i.e. deviceContact & hostNameContact related to other pojos will be highly appreciated

Regards
Bansi

Hi Bansi,

So it seems my answer didn't really solve your problem ? Is it because there is no link between your POJO (DeviceContactView, DeviceSummaryView and HostNameContactView) ?

But in your query, it seems you link them using the deviceId (1..1 relationship).


So at least, their ID are common. So you could define attributes in those Objects which points on other Objects (one-to-one mapping). Then, for example, you will be able to use the following on a DeviceSummaryView instance:

This will allow you to use the contactView attribute of the DeviceSummaryView to build your Criteria filter...

I hope its help a bit !

Regards,

Joël

Hi Joël
Thanks for quick response. I highly appreciate your help.
You are absolutely correct
"there is no link between the POJOs (DeviceContactView, DeviceSummaryView and HostNameContactView) "

As there is no link & the POJOs are not associated objects , hence DeviceSummaryView doesnt have attribute "contactView". So its not possible to use
DeviceSummaryView instance to get the ContactView i.e. deviceSummary.getContactView()



I apologize for the query in my earlier posting.Each of these pojos have deviceId as attribute but not sure whether i can link them thru deviceId attribute.

Basically the idea is to accomplish the following

select
<attributes of DeviceSummaryView i.e. ipAddress,hostName>,
<attributes of DeviceContactView i.e. deviceContact>,
<attributes of HostContactView i.e. hostContact>

from
DeviceSummaryView,
DeviceContactView,
HostContactView

where

ipAddress=?
AND
hostName=?
AND
deviceContact=?
AND
hostContact=?

Whenever users search on attributes like devicecontact,
it should look into its corresponding pojo & retrieve the rows

Any pointers/suggestions on how to write criteria query will be highly appreciated

Regards
bansi

Hello Bansi,



But in your previous post, in the query, you were joining tables using those ID. It should mean they are the same, no ?

If those tables aren't linked together using Hibernate Mapping, I don't really know how to extract the information you want using a single Criteria query... I would maybe do it using 3 Criteria queries (I'm not an Hibernate expert, so there is maybe another way to do it !).

Regards,

Joël

Hi Joël
Thanks for your thoughts. I tweaked the pojos so that they are linked using the ID i.e deviceId in my case.

Now i have two pojos using deviceId



As suggested by you i have added the attribute into DeviceSummaryView but not sure how to add this change into Hibernate Mapping File. I tried the following snippet but it resulted in Mapping Exception


Logically its many-to-many relationship between device & contacts. But in the case of Search screen all i do is key in the contact name & the search resultSet will display the list of devices i.e. i guess one-to-many uni-directional relationship as you key in the contact name on search screen & it will show the list of devices associated with contact. Not sure how to model this in hibernate mapping file

Any pointers/suggestions highly appreciated