sql - 为什么我的 "left join on"抛出 "unexpected token: on"错误？

Question

我有两个查询要运行:

2012-08-07 11:24:02,561 ERROR [org.hibernate.hql.PARSER] (http-0.0.0.0-8080-12) line 1:105: unexpected token: ON
2012-08-07 11:24:02,686 ERROR [org.jboss.aspects.tx.TxPolicy] (http-0.0.0.0-8080-12) javax.ejb.EJBTransactionRolledbackException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ON near line 1, column 105 
[SELECT count(o) from ejb.entity.News o LEFT JOIN NewsNewsDistributionServiceLink p ON o.id = p.newsId WHERE o.statusId = ?1 AND p.newsDistServiceCode is ?2]

2012-08-07 11:26:15,605 ERROR [org.hibernate.hql.PARSER] (http-0.0.0.0-8080-12) line 1:105: unexpected token: ON
2012-08-07 11:26:15,609 ERROR [org.jboss.aspects.tx.TxPolicy] (http-0.0.0.0-8080-12) javax.ejb.EJBTransactionRolledbackException: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: ON near line 1, column 105 
[SELECT count(o) from entity.News o LEFT JOIN NewsNewsDistributionServiceLink p ON o.id = p.newsId WHERE o.companyId = ?1 AND o.statusId = ?2 AND p.newsDistServiceCode is ?3]

我在 SQuirreL SQL 客户端中运行了以下命令:

select * from news n left join news_news_distribution_service_link nl
on n.id=nl.news_id
where news_distribution_service_code is null

这将返回结果，但是当我为我的 java servlet 翻译它时，它会抛出该错误。

新闻表:

id -PK
company_id
user_id
title
type_id
status_id
location
is_immediate
contents
created_date
last_modified_date
release_date
delete_date
corrected_news_id
can_distribute
was_distributed

NewsNewsDistLink 表:

id -PK 
news_id - this is equal to the id in News
news_distribution_service_code

代码:

String _query = "SELECT COUNT(o) FROM News o " + (filter.tierGroup != null ? TIER_GROUP_JOIN : "") + "WHERE ";
            WhereClauseBuilder builder = getWhereClause(filter);
            _query+= builder.where.toString();

            Query query = em.createQuery(_query);
            query.setHint("org.hibernate.cacheable", true);
            builder.bind(query);

whereClauseBuilder() 基本上在“where”之后添加参数，bind() 基本上将参数与 ?# 及其值绑定(bind)。最后，p.newsDistServiceCode 大多数时候 (>95%) 很可能为空。

Answer 1

因为查询不是SQL查询，而是HQL查询。 SQL和HQL是两种不同的语言。 SQL 适用于表和列，而 HQL 适用于 Hibernate 实体和关联。

参见 Hibernate documentation .