Sort on multiple columns in hql

Question

I have written a query that looks for models and his linked products. Now when I sort the data using the "order by model.name, p.number" statement, the models will be sorted correctly. But the sorting on the products is not happening. How can I fix this that the models will be sorted and that the products in the models are sorted also.

Session session = sharedData.getFactory().openSession();

        Query query = session.createQuery("select distinct model FROM Model model join fetch model.products p "
                + "join p.itemsInProducts iip " + "join iip.company iipc " + "join iip.item i "
                + "join model.company modelc " + "Join p.company pc " + "where "
                + "model.active = '1' and modelc.companyId = '" + companyId +"' " + "and p.active = '1' and pc.companyId = '" + companyId +"' "
                + "and i.id = '"+ itemID + "' " + "and iip.active = '1' and iipc.companyId = '" + companyId +"' order by model.name, p.number");

        List<Model> models = query.list();

        session.close();    

Model entity has a Set products variable that contains products.

Also when sorting only on p.number nothing happens. The order of the product objects are randomly ordered.

Answer 1

Sorted the set not in sql but with a Java comarator.

TreeSet<Product> orderList = new TreeSet<>(new Comparator<Product>() {

            @Override
            public int compare(Product prod1, Product prod2) {
                return prod1.getNumber().compareTo(prod2.getNumber());
            }
        });

        orderList.addAll(result);