How to do a "select from" in more than two table using hibernate CriteriaQuery

Question

I'm a beginner at Hibernate and I would like to do a select from in more than two tables using CriteriaQuery.

I have 3 tables: Cellphone, CellphoneIamges and CellphoneRangedPrices and I would like to get the data from these three tables. One example: This query below get the data from tables:

Select cellphone.cellphoneID, cellphone.cellphoneName, cellphoneImages.cellphoneImageID, cellphoneImages.cellphoneImageInternalLink,
       cellphoneImages.cellphoneImageExternalLink, cellphoneRangedPrices.cellphoneRangedPricesID, cellphoneRangedPrices.cellphoneRangedPriceStart, cellphoneRangedPrices.cellphoneRangedPriceEnd
FROM cellphone, cellphoneImages, cellphoneRangedPrices
WHERE cellphone.cellphoneID = cellphoneImages.cellphoneID AND cellphone.cellphoneID = cellphoneRangedPrices.cellphoneID;

But I would like get it using CriteriaQuery. I tried do it.

public List getAllCellphoneData(){
    EntityManager em = mySQLDAO.getEm();
    try {
        CriteriaQuery<Object[]> criteriaQuery = em.getCriteriaBuilder().createQuery(Object[].class);
        Root<Cellphone> rootPhone = criteriaQuery.from(Cellphone.class);
        Root<CellphoneImages> rootImages = criteriaQuery.from(CellphoneImages.class);

        criteriaQuery.multiselect(rootImages,rootPhone);
        criteriaQuery.where(em.getCriteriaBuilder().equal(rootImages.get("cellphone"), rootPhone.get("cellphoneID")));

        Query query = em.createQuery(criteriaQuery);

        List<Object[]> resultList = query.getResultList();

        return null;

    } catch (Exception e) {
        logger.log(Level.SEVERE, "Error when trying get cellphone information.", e);
        throw new PersistenceException(e);
    } finally {
        em.close();
    }
}

It's working, but I don't know how to add more one class in this criteriaQuery.

I would like help to understand how to do it. Actually the method above can get data from Cellphone and CellphoneImages tables, but I don't know what I need do in code to get data from another table (CellphoneRangedPrices).

I tried search in other stackoberflow post but I don't find.

NOTE: the table CellphoneRagedPrice has foreign key from Cellphone table (Similar to CellphoneImages table)

Thank so much for your help

possible duplicate https://stackoverflow.com/questions/30639132/joining-tables-without-relation-using-jpa-criteria — Eirini Graonidou, Aug 14 '18 at 14:05
Hi Eirini, this post approaches how to do a select in two table. My post approaches how to do a select in three tables using CriteriaQuery. I think that is different scenario. thank you. — Joh, Aug 14 '18 at 14:12

score 0 · Answer 1 · answered Aug 14 '18 at 20:19

I found the solution for my question. Basically I need add into same "WHERE" clause the other .Class from third table.

criteriaQuery.multiselect(rootPhone,rootImages,rootRangedPrices);
        criteriaQuery.where(em.getCriteriaBuilder().equal(rootImages.get("cellphone"), rootPhone.get("cellphoneID")),
                            em.getCriteriaBuilder().equal(rootPhone.get("cellphoneID"), rootRangedPrices.get("cellPhone")));

Follow below the complete code:

public List<CellphoneVO> getAllCellphoneData(){
    EntityManager em = mySQLDAO.getEm();
    List<CellphoneVO> cellphoneVOList = new ArrayList<>();

    try {
        CriteriaQuery<Object[]> criteriaQuery = em.getCriteriaBuilder().createQuery(Object[].class);
        Root<Cellphone> rootPhone = criteriaQuery.from(Cellphone.class);
        Root<CellphoneImages> rootImages = criteriaQuery.from(CellphoneImages.class);
        Root<CellphoneRangedPrices> rootRangedPrices = criteriaQuery.from(CellphoneRangedPrices.class);

        criteriaQuery.multiselect(rootPhone,rootImages,rootRangedPrices);
        criteriaQuery.where(em.getCriteriaBuilder().equal(rootImages.get("cellphone"), rootPhone.get("cellphoneID")),
                            em.getCriteriaBuilder().equal(rootPhone.get("cellphoneID"), rootRangedPrices.get("cellPhone")));

        Query query = em.createQuery(criteriaQuery);

        List<Object[]> resultList = query.getResultList();

        for(Object[] objects : resultList){
            CellphoneVO cellphoneVO = new CellphoneVO();
            Cellphone cellphone = (Cellphone)objects[0];
            CellphoneImages cellphoneImages = (CellphoneImages)objects[1];
            CellphoneRangedPrices cellphoneRangedPrices = (CellphoneRangedPrices)objects[2];
            cellphoneVO.setCellphone(cellphone);
            cellphoneVO.setCellphoneImages(cellphoneImages);
            cellphoneVO.setCellphoneRangedPrices(cellphoneRangedPrices);
            cellphoneVOList.add(cellphoneVO);
        }

        return cellphoneVOList;

    } catch (Exception e) {
        logger.log(Level.SEVERE, "Error when trying get cellphones data.", e);
        throw new PersistenceException(e);
    } finally {
        em.close();
    }
}

How to do a "select from" in more than two table using hibernate CriteriaQuery

1 Answers1