Java 寻找 HQL 构建器（Hibernate Query Language）

Looking for an HQL builder (Hibernate Query Language)

提问by Sébastien RoccaSerra

I'm looking for a builder for HQLin Java. I want to get rid of things like:

我正在为Java 中的HQL寻找构建器。我想摆脱这样的事情：

StringBuilder builder = new StringBuilder()
    .append("select stock from ")
    .append( Stock.class.getName() )
    .append( " as stock where stock.id = ")
    .append( id );

I'd rather have something like:

我宁愿有类似的东西：

HqlBuilder builder = new HqlBuilder()
    .select( "stock" )
    .from( Stock.class.getName() ).as( "stock" )
    .where( "stock.id" ).equals( id );

I googled a bit, and I couldn't find one.

我用谷歌搜索了一下，没有找到。

I wrote a quick & dumb HqlBuilderthat suits my needs for now, but I'd love to find one that has more users and tests than me alone.

我写了一个HqlBuilder适合我现在需要的快速和愚蠢的，但我很想找到一个比我一个人拥有更多用户和测试的人。

Note: I'd like to be able to do things like this and more, which I failed to do with the Criteria API:

注意：我希望能够做这样的事情以及更多，但我没有使用 Criteria API：

select stock
from com.something.Stock as stock, com.something.Bonus as bonus
where stock.someValue = bonus.id

ie. select all stocks whose property someValuepoints to anybonus from the Bonus table.

IE。从奖金表中选择所有属性someValue指向任何奖金的股票。

Thanks!

谢谢！

采纳答案by sblundy

@Sébastien Rocca-Serra
Now we're getting somewhere concrete. The sort of join you're trying to do isn't really possible through the Criteria API, but a sub-query should accomplish the same thing. First you create a DetachedCriteriafor the bonus table, then use the INoperator for someValue.

@ Sébastien Rocca-Serra
现在我们正在某个地方具体。您尝试进行的那种连接实际上无法通过 Criteria API 实现，但是子查询应该可以完成相同的操作。首先DetachedCriteria为奖金表创建 a ，然后为 使用IN运算符someValue。

DetachedCriteria bonuses = DetachedCriteria.forClass(Bonus.class);
List stocks = session.createCriteria(Stock.class)
    .add(Property.forName("someValue").in(bonuses)).list();

This is equivalent to

这相当于

select stock
from com.something.Stock as stock
where stock.someValue in (select bonus.id from com.something.Bonus as bonus)

The only downside would be if you have references to different tables in someValueand your ID's are not unique across all tables. But your query would suffer from the same flaw.

唯一的缺点是如果您引用了不同的表，someValue并且您的 ID 在所有表中都不是唯一的。但是您的查询会遇到同样的缺陷。

回答by sblundy

Doesn't the Criteria APIdo it for you? It looks almost exactly like what you're asking for.

未在标准API为你做？它看起来几乎与您要求的完全一样。

回答by Alex Argo

It looks like you want to use the Criteria query API built into Hibernate. To do your above query it would look like this:

看起来您想使用 Hibernate 中内置的 Criteria 查询 API。要执行上述查询，它看起来像这样：

List<Stock> stocks = session.createCriteria(Stock.class)
    .add(Property.forName("id").eq(id))
    .list();

If you don't have access to the Hibernate Session yet, you can used 'DetachedCriteria' like so:

如果您还没有访问 Hibernate Session 的权限，您可以像这样使用“DetachedCriteria”：

DetachedCriteria criteria = DetachedCriteria.forClass(Stock.class) 
    .add(Property.forName("id").eq(id));

If you wanted to get all Stock that have a Bonus with a specific ID you could do the following:

如果您想获得具有特定 ID 的奖金的所有股票，您可以执行以下操作：

DetachedCriteria criteria = DetachedCriteria.forClass(Stock.class)
     .createCriteria("Stock")
          .add(Property.forName("id").eq(id)));

For more infromation check out Criteria Queriesfrom the Hibernate docs

有关更多信息，请查看Hibernate 文档中的Criteria Queries

回答by sblundy

@Sébastien Rocca-Serra

@塞巴斯蒂安罗卡-塞拉

select stock
from com.something.Stock as stock, com.something.Bonus as bonus
where stock.bonus.id = bonus.id

That's just a join. Hibernate does it automatically, if and only if you've got the mapping between Stockand Bonussetup and if bonusis a property of Stock. Criteria.list()will return Stockobjects and you just call stock.getBonus().

那只是一个连接。Hibernate 会自动执行此操作，当且仅当您已获得Stock和Bonussetup之间的映射并且 ifbonus是Stock. Criteria.list()将返回Stock对象，您只需调用stock.getBonus().

Note, if you want to do anything like

请注意，如果您想做任何类似的事情

select stock
from com.something.Stock as stock
where stock.bonus.value > 1000000

You need to use Criteria.createAlias(). It'd be something like

您需要使用Criteria.createAlias(). 它会是这样的

session.createCriteria(Stock.class).createAlias("bonus", "b")
   .add(Restrictions.gt("b.value", 1000000)).list()

回答by Marcio Aguiar

Criteria API does not provide all functionality avaiable in HQL. For example, you cannot do more than one join over the same column.

Criteria API 不提供 HQL 中可用的所有功能。例如，您不能对同一列执行多个连接。

Why don't you use NAMED QUERIES? The look much more clean:

为什么不使用NAMED QUERIES？看起来更干净：

Person person = session.getNamedQuery("Person.findByName")
                             .setString(0, "Marcio")
                             .list();

回答by Josh

I wrote a GPL'd solution for OMERO which you could easily build suited to your situation.

我为 OMERO 编写了一个 GPL 的解决方案，您可以轻松地构建适合您的情况。

• Source: QueryBuilder.java
• Test: QueryBuilderMockTest

• 来源：QueryBuilder.java
• 测试：QueryBuilderMockTest

Usage:

用法：

QueryBuilder qb = new QueryBuilder();
qb.select("img");
qb.from("Image", "img");
qb.join("img.pixels", "pix", true, false);

// Can't join anymore after this
qb.where(); // First
qb.append("(");
qb.and("pt.details.creationTime > :time");
qb.param("time", new Date());
qb.append(")");
qb.and("img.id in (:ids)");
qb.paramList("ids", new HashSet());
qb.order("img.id", true);
qb.order("this.details.creationEvent.time", false);

It functions as a state machine "select->from->join->where->order", etc. and keeps up with optional parameters. There were several queries which the Criteria API could not perform (see HHH-879), so in the end it was simpler to write this small class to wrap StringBuilder. (Note: there is a ticket HHH-2407describing a Hibernate branch which should unify the two. After that, it would probably make sense to re-visit the Criteria API)

它充当状态机“select->from->join->where->order”等，并跟上可选参数。有几个 Criteria API 无法执行的查询（请参阅HHH-879），因此最终编写这个小类来包装 StringBuilder 会更简单。（注意：有一张票HHH-2407描述了一个 Hibernate 分支，它应该统一两者。之后，重新访问 Criteria API 可能是有意义的）

回答by Chuck Deal

Take a look at the search package available from the hibernate-generic-daoproject. This is a pretty decent HQL Builder implementation.

查看hibernate-generic-dao项目中提供的搜索包。这是一个相当不错的 HQL Builder 实现。

回答by Guillaume

I know this thread is pretty old, but I also was looking for a HqlBuilder And I found this "screensaver" project
It is NOT a Windows screensaver, it's a "Lab Information Management System (LIMS) for high-throughput screening (HTS) facilities that perform small molecule and RNAi screens."

我知道这个线程很旧，但我也在寻找一个 HqlBuilder 我发现这个“屏幕保护程序”项目
它不是 Windows 屏幕保护程序，它是一个“用于高通量筛选 (HTS) 设施的实验室信息管理系统 (LIMS)进行小分子和 RNAi 筛选。”

It contains an HQLBuilder that is looking quite good.
Here is a sample list of available methods:

它包含一个看起来相当不错的 HQLBuilder。
以下是可用方法的示例列表：

...
HqlBuilder select(String alias);
HqlBuilder select(String alias, String property);
HqlBuilder from(Class<?> entityClass, String alias);
HqlBuilder fromFetch(String joinAlias, String joinRelationship, String alias);
HqlBuilder where(String alias, String property, Operator operator, Object value);
HqlBuilder where(String alias, Operator operator, Object value);
HqlBuilder where(String alias1, Operator operator, String alias2);
HqlBuilder whereIn(String alias, String property, Set<?> values);
HqlBuilder whereIn(String alias, Set<?> values);
HqlBuilder where(Clause clause);
HqlBuilder orderBy(String alias, String property);
HqlBuilder orderBy(String alias, SortDirection sortDirection);
HqlBuilder orderBy(String alias, String property, SortDirection sortDirection);
String toHql();
...

回答by Timo Westk?mper

For a type-safe approach to your problem, consider Querydsl.

对于您的问题的类型安全方法，请考虑Querydsl。

The example query becomes

示例查询变为

HQLQuery query = new HibernateQuery(session);
List<Stock> s = query.from(stock, bonus)
  .where(stock.someValue.eq(bonus.id))
  .list(stock);

Querydsl uses APT for code generation like JPA2 and supports JPA/Hibernate, JDO, SQL and Java collections.

Querydsl 使用 APT 进行代码生成，如 JPA2，并支持 JPA/Hibernate、JDO、SQL 和 Java 集合。

I am the maintainer of Querydsl, so this answer is biased.

我是 Querydsl 的维护者，所以这个答案是有偏见的。

回答by ebelanger

For another type-safe query dsl, I recommend http://www.torpedoquery.org. The library is still young but it provides type safety by directly using your entity's classes. This means early compiler errors when the query no longer applies before of refactoring or redesign.

对于另一个类型安全的查询 dsl，我推荐http://www.torpedoquery.org。该库还很年轻，但它通过直接使用实体的类来提供类型安全。这意味着在重构或重新设计之前查询不再适用时，会出现早期编译器错误。

I also provided you with an example. I think from your posts that you where trying to do a subquery restriction, so I based the exemple on that:

我还提供了一个例子。我认为从你的帖子中你试图做一个子查询限制，所以我基于这个例子：

import static org.torpedoquery.jpa.Torpedo.*;

Bonus bonus = from(Bonus.class);
Query subQuery = select(bonus.getId());

Stock stock = from(Stock.class);
where(stock.getSomeValue()).in(subQuery);

List<Stock> stocks = select(stock).list(entityManager);