Two tables: Account & Site. Site contains a field (URL) I am interested in.
Account has a FK to a Site. The FK field is not the primary key of Account.
I am trying to get the distinct URLs for all Accounts.
The following HSQL query retrieves the values I want.
Code:
String queryString = "SELECT distinct url FROM account a INNER JOIN site s ON a.site_id=s.site_id";
// This iterator will contain the URLs
Iterator<?> iter = session.createSQLQuery(queryString).list().iterator();
To avoid hard-coding SQL, I want to use Criteria.
The following solution gets the values I want.
Code:
session = HibernateUtil.getSessionFactory().getCurrentSession();
session.beginTransaction();
// Create criteria to retrieve the list of distinct Sites referenced by the Account table
DetachedCriteria distinctSites = DetachedCriteria.forClass(Account.class);
distinctSites.setProjection(Projections.distinct(Projections.property("siteId")));
// Use the DetachedCriteria to narrow the results
Criteria criteria = session.createCriteria(Site.class);
criteria.add(Subqueries.propertyIn("siteId", distinctSites));
// This iterator will contain the Site objects
Iterator<?> iter = criteria.list().iterator();
I am wondering if there is a better way to do this.
Perhaps it can be done using a JOIN?
Login
FAQ
Search