Hibernate为MySQL生成了错误的内连接查询

Question

我正在使用JPA开发我的第一个项目，数据库使用MySQL，JPA提供程序为Hibernate 4.3.8，这是一个基于Spring 4的Web项目。

在我的Spring配置文件中，我设置了数据库和方言：

HibernateJpaVendorAdapter hjpaVA = new HibernateJpaVendorAdapter();
hjpaVA.setDatabase(Database.MYSQL);
hjpaVA.setDatabasePlatform("org.hibernate.dialect.MySQL5Dialect");

我尝试获取这个让我困扰的查询：

TypedQuery<KundeDTO> query = entityManager.createQuery("select new zdb.dto.KundeDTO(k.id, k.firma.firmenname, k.regnr, k.kategorie) 
from Kunde k where k.id = :id", KundeDTO.class);

这是Hibernate生成的SQL语句：

select kunde0_.`id` as col_0_0_, firma1_.`firmenname` as col_1_0_, kunde0_.`regnr` as col_2_0_, kunde0_.id_kategorie as col_3_0_ 
from `zdb_e`.`Kunde` kunde0_, `zdb_e`.`Firma` firma1_ 
inner join `zdb_e`.`Kategorie` kategorie2_ on kunde0_.id_kategorie=kategorie2_.`id` 
where kunde0_.id_firma=firma1_.`id` and kunde0_.`id`=1;

请注意，内连接上没有括号！

运行此语句会导致以下错误：

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 
Unknown column 'kunde0_.id_kategorie' in 'on clause'

异常的原因在这里有详细说明：mysql-unknown-column-in-on-clause。

当我给where和内连接子句添加括号并直接对数据库运行该语句时，它就可以正常工作：

select kunde0_.`id` as col_0_0_, firma1_.`firmenname` as col_1_0_, kunde0_.`regnr` as col_2_0_, kunde0_.id_kategorie as col_3_0_ 
from (`zdb_e`.`Kunde` kunde0_, `zdb_e`.`Firma` firma1_ )
inner join `zdb_e`.`Kategorie` kategorie2_ on 
( kunde0_.id_kategorie=kategorie2_.`id` ) 
where kunde0_.id_firma=firma1_.`id` and kunde0_.`id`=1;

那么，我该如何说服Hibernate生成这样的查询呢？

更新：以下是实体

客户

@Entity
public class Kunde implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private Integer id;

    private Integer regnr;

    @OneToOne(optional=false)
    @JoinColumn(name="id_firma", nullable = false)
    private Firma firma;

    @OneToOne(optional=false)
    @JoinColumn(name="id_kategorie", nullable = false)
    private Kategorie kategorie;

    @OneToOne(optional=false)
    @JoinColumn(name="id_lieferregion", nullable = false)
    private Lieferregion lieferregion;

    // getters and setters....
}

公司

@Entity
@Table(name = "Firma")
public class Firma implements Serializable {
    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue
    private Integer id;

    @Column(name="firmenname")
    private String firmenname;
    @Column(name="uid")
    private String uid;

    @OneToOne(optional=false)
    @JoinColumn(name="id_anschrift", nullable = false)
    private Anschrift anschrift;

    @OneToMany(mappedBy="id_firma", fetch=FetchType.EAGER)
    private List<Person> personen;

    public Firma() {
        personen = new ArrayList<Person>();
    }
    // getters and setters....
}

Category

@Entity
public class Kategorie implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    private Integer id;

    private Integer nummer;
    private String bezeichnung;

    public Kategorie() {
    }

    public Kategorie(int kategorieId, int kategorieNummer, String kategorieBezeichnung) {
        this.id = kategorieId;
        this.nummer = kategorieNummer;
        this.bezeichnung = kategorieBezeichnung;
    }
    // getters and setters....
}

数据库模式

CREATE TABLE kategorie 
    (id INTEGER NOT NULL AUTO_INCREMENT, 
    nummer INTEGER NOT NULL, 
    bezeichnung VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),        
    UNIQUE (nummer, bezeichnung)
    );

CREATE TABLE firma
    (ID INTEGER NOT NULL AUTO_INCREMENT, 
    firmenname VARCHAR(50) NOT NULL,
    uid VARCHAR(20) NOT NULL,
    url VARCHAR(100) NOT NULL,
    id_anschrift INTEGER NOT NULL,
    id_logo INTEGER,
    PRIMARY KEY (id),
    UNIQUE (uid),
    UNIQUE (firmenname),
    UNIQUE (id_anschrift),
    CONSTRAINT firma_fk1 FOREIGN KEY (id_anschrift) REFERENCES ANSCHRIFT (ID),
    CONSTRAINT firma_fk2 FOREIGN KEY (id_logo) REFERENCES logo (ID));

CREATE TABLE kunde 
    (id INTEGER NOT NULL AUTO_INCREMENT, 
    regnr INTEGER NOT NULL,
    id_kategorie INTEGER NOT NULL,
    id_firma INTEGER NOT NULL,
    id_benutzer INTEGER,
    id_lieferregion INTEGER NOT NULL,
    PRIMARY KEY (id), 
    UNIQUE (regnr, id_kategorie),
    UNIQUE (id_firma),
    UNIQUE (id_benutzer),
    CONSTRAINT kunde_fk1 FOREIGN KEY (id_firma) REFERENCES firma (id),
    CONSTRAINT kunde_fk2 FOREIGN KEY (id_benutzer) REFERENCES benutzer (id),
    CONSTRAINT kunde_fk3 FOREIGN KEY (id_kategorie) REFERENCES kategorie (id),
    CONSTRAINT kunde_fk4 FOREIGN KEY (id_lieferregion) REFERENCES lieferregion (id)
    );

经进一步测试

问题在于from子句缺少括号。

直接访问数据库：

select k.id, k.regnr, f.firmenname from (Kunde k, Firma f) JOIN kategorie kat on k.id_kategorie = kat.id where k.id = 1 and k.id_firma = f.id;

运作良好！

select k.id, k.regnr, f.firmenname from Kunde k, Firma f JOIN kategorie kat on k.id_kategorie = kat.id where k.id = 1 and k.id_firma = f.id;

无法工作：在“on子句”中未知列“k.id_kategorie”

为什么我甚至需要在from子句中使用括号？
如何让Hibernate自动添加它们？

- Nordic

---

错误与您链接的那个不同。在您的查询中，ON子句后面没有AND，而错误明显是关于不存在的列。请检查您的数据库架构。 - JB Nizet

@JBNizet OP说当他加上括号时语句可以运行，这是列存在的强烈暗示。 - Jens Schauder

请粘贴您的Kunde和Kategorie类。您也可以尝试像这样的语句："select new zdb.dto.KundeDTO(k.id, k.firma.firmenname, k.regnr, kat) from Kunde k JOIN k.kategorie kat where k.id = :id" - jgr

@jgr 已经尝试过了。（根据我的问题进行了编辑）问题出在FROM子句中的括号。 - Nordic

我认为在JPQL中不能使用'on'，你应该像这样按照我的方式来写：'from Kunde k JOIN k.kategorie kat'。请展示一下你的Kundle和Kategorie类以及如何定义映射。而且你确定你已经有了必要的getter和setter方法吗？ - jgr

Answer 1

我没能用JPQL让它正常工作，所以我按照评论中jgr的建议编写了自己的原生SQL查询：

String sql = "select k.id, f.firmenname, k.regnr, kat.id, kat.nummer, kat.bezeichnung from (zdb_e.Kunde k, zdb_e.Firma f) JOIN zdb_e.kategorie kat on k.id_kategorie = kat.id where k.id = :id and k.id_firma = f.id;";
Query query =  entityManager.createNativeQuery(sql, schemaName), "KundenListeRow");
query.setParameter("id", id);
return (KundeDTO) query.getSingleResult();

为了将映射到我的 Pojo KundeDTO 中，我在 Kunde 实体中添加了 @SqlResultSetMapping：

@Entity
@SqlResultSetMapping(name = "KundenListeRow",
classes = {
    @ConstructorResult(targetClass = KundeDTO.class,
            columns = {
                    @ColumnResult(name = "id"),
                    @ColumnResult(name = "firmenname"),
                    @ColumnResult(name = "regnr"),
                    @ColumnResult(name = "kat.id"),
                    @ColumnResult(name = "kat.nummer"),
                    @ColumnResult(name = "kat.bezeichnung")
                    }
            )
  })
public class Kunde implements Serializable {
     ....
}

还有在DTO中对应的构造函数：

public class KundeDTO {
    int id;
    String firmenname;
    Kategorie k;
    int regnr;

    public KundeDTO(int id, String firmenname, int regnr, int kategorieId, int kategorieNummer, String kategorieBezeichnung) {
        this.id = id;
        this.firmenname = firmenname;
        this.regnr = regnr;
        this.k = new Kategorie(kategorieId, kategorieNummer, kategorieBezeichnung);
    }
    public KundeDTO() {
    }
}

就像我说的，它可以工作。但并不理想。使用JPQL，我可以使用TypedQuery而无需处理resultSetMapping。

当然，我仍然不知道为什么我的JPQL不起作用。 :-)

- Nordic