Select multiple items based on multiple IDs with hibernate

Question

I am using hibernate in eclipse.

And i have 3 ONE TO MANY relation tables.

ONE [Company] --> MANY [Officer], And ONE [Officer] --> MANY [Task].

They all have unique id (companyId, officerId, taskId).

Currently i know how to find all the tasks belong to an officer and i also know how to find all the officers belong to a company.

here is the code snippet:

public static ArrayList<Officer> getOfficersByCompany(Company company){
    ArrayList<Officer> officers = new ArrayList<Officer>();
    DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Officer.class);

    detachedCriteria.add(Restrictions.eq(Key.COMPANY, company));
    detachedCriteria.add(Restrictions.eq(Key.OBJSTATUS, Value.ACTIVED));

    List<Object> list = HibernateUtil.detachedCriteriaReturnList(detachedCriteria);

    for(Object o : list){
        officers.add((Officer) o);
    }
    return officers;
}

And below is the detachedCriteriaReturnList method in HibernateUtil class.

public static List<Object> detachedCriteriaReturnList(DetachedCriteria dc){
    Session session = getSessionFactory().openSession();
    session.beginTransaction();
    Criteria criteria = dc.getExecutableCriteria(session);
    List<Object> list = criteria.list();
    session.getTransaction().commit();
    session.close();
    return list;
}

However, if i try to get all the task belongs to a company, how should i implement the code. I have tried using: detachedCriteria.add(Restrictions.allEq(officers));

public static ArrayList<Task> getTasksByOfficers(Map<String, Object> officers){
    ArrayList<Task> tasks = new ArrayList<Task>();

    DetachedCriteria detachedCriteria = DetachedCriteria.forClass(Task.class);

    detachedCriteria.add(Restrictions.allEq(officers));
    List<Object> list = HibernateUtil.detachedCriteriaReturnList(detachedCriteria);

    for(Object o : list){
        tasks.add((Task) o);
    }

    return tasks;
}

But i realise that the map only stores unique key and value pair, if i try to use the second officer's id the first one will be replaced.

Or is there any other ways to perform the selection faster and more efficient?

Answer 1

ONE [Company] --> MANY [Officer], And ONE [Officer] --> MANY [Task].

In HQL this should be pretty straight forward (I gave up using Criteria API, as it did not allow joining the same object class twice):

Obviously, I did not test the queries, but they should work...

select Task 
  from Task join Task.Officer o join o.Company c
 where c.name = 'xxx'

or

select t
  from Company c join c.Officers o join o.Tasks t
 where  c.name = 'xxx'

Note: My experience with criteria is limited, as I have said. But looking at your code, I have two comments. Maybe you should not use the Map of officers but the list officers.values() to allEq(). Second remark: If you didn't do a mistake, Hibernate obviously magically finds the reference to the property you want to be allEq with, otherwise, the allEq misses a property you want to compare to.

UPDATE: Ok, as promised, I checked the Javadocs for you: https://docs.jboss.org/hibernate/orm/3.5/api/org/hibernate/Criteria.html

A join in HQL is the equivalent of a criteria added to another criteria or an alias:

detachedCriteria = DetachedCriteria.forClass(Task.class)
                   .createCriteria("Officer") // Officer property of task
                   .createCriteria("Company") // Company property of officer
                   .add(Restriction.eq(Key.COMPANY, compKey); // the company

List<Object> list = detachedCriteria
                    .getExecutableCriteria(hibernateSession)
                    .list();

This should get you going...

Note on HQL:

To run the query, you get a query from the hibernate session with the HQL and then you call list() or executeUpdate()on it. taskHqlString is a HQL-statement as above. Replace the companyKey in the query with a named parameter :companyKey:

String taskHqlString = "select Task "  
                     + "  from Task join Task.Officer o "
                     + "            join o.Company c "
                     + " where c.name = :companyKey";

List<Task> list = (List<Task>)hibernateSession
                  .createQuery(taskHqlString)
                  .setParameter("companyKey", companyKeyValue)
                  .list();