Java\Hibernate\ManyTone\Ordering

Question

我在Hibernate（JPA）、PostgreSQL和排序方面有一些问题

例如，我们有两个表（映射到实体）：

- Pets (id (id), PetTypes (types_id), description (description))

- PetTypes (id (id), name (name))

类型id字段不是必需的（对pet类型表的约束）

例如，我们在pets表中有10行（在1、2和3行，type_id为null）。我需要按宠物种类订购宠物桌。使用HQL或JPA标准命名（但我认为JPA不支持它）

问题：如果type_id为空，则pets表中的值未按顺序选择。 我可以尝试使用以下解决方案：

1. FROM Pets pets ORDER BY pets.petTypes.name ASC NULLS LAST
2. FROM Pets pets ORDER BY CASE WHEN pets.petTypes IS NULL THEN 0 ELSE 1 END, ORDER BY pets.petTypes.name ASC NULLS LAST
3. FROM Pets pets ORDER BY CASE WHEN pets.petTypes.name IS NULL THEN 0 ELSE 1 END, ORDER BY pets.petTypes.name ASC NULLS LAST
4. FROM Pets pets LEFT JOIN pets.petTypes ORDER BY pets.petTypes.name ASC NULLS LAST
5. FROM Pets pets ORDER BY pets.petTypes ASC NULLS LAST, pets.petTypes.names ASC
6. FROM Pets pets ORDER BY CASE WHEN pets.petTypes IS NULL OR pets.petTypes.name IS NULL THEN 0 ELSE 1 END, ORDER BY pets.petTypes.name ASC

但什么都不管用。选择后，我们有7行而不是10行。有什么想法吗？ 我不能通过HQL使用UNION语句。它从2005年开始开放

编辑

Thx到Rodrigo Menezes。此解决方案适用于：

select p from Pets p left join p.petTypes pt order by case when pt is null then 0 else 1 end, pt.name

Answer 1

可能您的HQL正在生成内部连接或交叉连接

您可以强制左连接：

select p from Pets p 
left join p.petTypes pt 
order by case when pt is null then 0 else 1 end, pt.name

我做了一个案例测试，工作如下：

public static void main(String[] args) {
    Session session = HibernateUtil.getSessionFactory().openSession();

    session.beginTransaction();

    PetTypes dog = new PetTypes();
    dog.setName("Dog");
    dog.setId(1);
    PetTypes cat = new PetTypes();
    cat.setName("Cat");
    cat.setId(2);

    session.save(dog);
    session.save(cat);

    int id = 1;
    Pets joe = new Pets();
    joe.setId(id++);
    joe.setDescription("Joe");

    Pets x = new Pets();
    x.setId(id++);
    x.setDescription("Sarah");    
    x.setPetTypes(dog);

    Pets y = new Pets();
    y.setId(id++);
    y.setDescription("Jakob");    
    y.setPetTypes(cat);  

    Pets z = new Pets();
    z.setId(id++);
    z.setDescription("Xena");    
    z.setPetTypes(cat);                

    session.save(joe);
    session.save(x);
    session.save(y);
    session.save(z);

    session.getTransaction().commit();

    Query q = session.createQuery("select p from Pets p "
            + "left join p.petTypes pt "
            + "order by case when pt is null then 0 else 1 end, pt.name ");

    List<Pets> resultList = q.list();
    System.out.println("num of employess:" + resultList.size());
    for (Pets next : resultList) {
        System.out.println("pet " + next);
    }
}

结果:

num of pets:4 
pet Pets [description=Joe, petTypes=null] 
pet Pets [description=Jakob, petTypes=PetTypes [name=Cat]] 
pet Pets [description=Xena, petTypes=PetTypes [name=Cat]] 
pet Pets [description=Sarah, petTypes=PetTypes [name=Dog]]