Java 如何从 querydsl 获取完全物化的查询

How to get fully materialized query from querydsl

提问by MickJ

I am trying to use querydsl for building dynamic queries for dynamic schemas. I am trying to get just the query instead of having to actually execute it.

我正在尝试使用 querydsl 为动态模式构建动态查询。我试图只获取查询，而不必实际执行它。

So far I have faced two issues: - The schema.table notation is absent. Instead I only get the table name. - I have been able to get the query but it separates out the variables and puts '?' instead which is understandable. But I am wondering if there is some way to get fully materialized query including the parameters.

到目前为止，我遇到了两个问题： - schema.table 符号不存在。相反，我只得到表名。- 我已经能够获得查询，但它分离出变量并放入“？” 相反，这是可以理解的。但我想知道是否有某种方法可以获得包括参数在内的完全物化查询。

Here is my current attempt and result(I am using MySQLTemplates to create the configuration):

这是我当前的尝试和结果（我使用 MySQLTemplates 创建配置）：

private SQLTemplates templates = new MySQLTemplates();
private Configuration configuration = new Configuration(templates); 

String table = "sometable"
Path<Object> userPath = new PathImpl<Object>(Object.class, table);
StringPath usernamePath = Expressions.stringPath(userPath, "username");
NumberPath<Long> idPath = Expressions.numberPath(Long.class, userPath, "id");
SQLQuery sqlQuery = new SQLQuery(connection, configuration)
  .from(userPath).where(idPath.eq(1l)).limit(10);
String query = sqlQuery.getSQL(usernamePath).getSQL();
return query;

And what I get is:

我得到的是：

select sometable.username
from sometable
where sometable.id = ?
limit ?

What I wanted to get was:

我想得到的是：

select sometable.username
from someschema.sometable
where sometable.id = ?
limit ?

Update:I came up with this sort of hack to get parameters materialized(Not ideal and would love better solution) But still could not get Schema.Table notation to work:

更新：我想出了这种使参数具体化的技巧（不理想，希望得到更好的解决方案）但仍然无法使 Schema.Table 符号起作用：

Hack follows. Please suggest cleaner QueryDsl way of doing it:

哈克紧随其后。请建议更清洁的 QueryDsl 方法：

String query = cleanQuery(sqlQuery.getSQL(usernamePath));

private String cleanQuery(SQLBindings bindings){
    String query = bindings.getSQL();
    for (Object binding : bindings.getBindings()) {
        query = query.replaceFirst("\?", binding.toString());
    }
    return query;
}

采纳答案by Timo Westk?mper

To enable schema printing use the following pattern

要启用模式打印，请使用以下模式

SQLTemplates templates = MySQLTemplates.builder()
    .printSchema()
    .build();

SQLTemplates subclasses were used before, but since some time the builder pattern is the official way to customize the templates http://www.querydsl.com/static/querydsl/3.3.1/reference/html/ch02s03.html#d0e904

之前使用过 SQLTemplates 子类，但由于一段时间以来，构建器模式是自定义模板的官方方式http://www.querydsl.com/static/querydsl/3.3.1/reference/html/ch02s03.html#d0e904

And to enable direct serialization of literals use

并启用文字的直接序列化使用

//configuration level
configuration.setUseLiterals(true);

//query level
configuration.setUseLiterals(true);

Here is a full example

这是一个完整的例子

// configuration
SQLTemplates templates = MySQLTemplates.builder()
    .printSchema()
    .build();
Configuration configuration = new Configuration(templates);

// querying
SQLQuery sqlQuery = new SQLQuery(connection, configuration)
    .from(userPath).where(idPath.eq(1l)).limit(10);
sqlQuery.setUseLiterals(true);    
String query = sqlQuery.getSQL(usernamePath).getSQL();

If you always just want the SQL query string out, move setUseLiterals from query to configuration.

如果您总是只想要 SQL 查询字符串，请将 setUseLiterals 从查询移动到配置。

Concerning the usage of Querydsl expressions the usage of code generation like documented here is advised http://www.querydsl.com/static/querydsl/3.3.1/reference/html/ch02s03.html

关于 Querydsl 表达式的使用，建议使用此处记录的代码生成http://www.querydsl.com/static/querydsl/3.3.1/reference/html/ch02s03.html

It will make your code typesafe, compact and readable.

它将使您的代码类型安全、紧凑和可读。

If you want to try Querydsl without code generation you can replace

如果你想在没有代码生成的情况下尝试 Querydsl，你可以替换

Path<Object> userPath = new PathImpl<Object>(Object.class, variable);

with

和

Path<Object> userPath = new RelationalPathBase<Object>(Object.class, variable, schema, table);

回答by David Fleeman

When working with QueryDSL, you must provide a template for the database platform to build the query for. I see you are already are doing this here:

使用 QueryDSL 时，您必须为数据库平台提供一个模板来构建查询。我看到你已经在这里这样做了：

private SQLTemplates templates = new MySQLTemplates();
private Configuration configuration = new Configuration(templates); 

To make the schema name appear in the generated query, the only way I have found to do this is (there may be an easier way) is to extend the template class and explicitly call this.setPrintSchema(true);inside the constructor. Here is a class that should work for MySql:

为了使模式名称出现在生成的查询中，我发现这样做的唯一方法是（可能有更简单的方法）是扩展模板类并this.setPrintSchema(true);在构造函数中显式调用。这是一个适用于 MySql 的类：

import com.mysema.query.sql.MySQLTemplates;

public class NewMySqlTemplates extends MySQLTemplates {

    public NewMySqlTemplates() {
        super('\', false);
    }

    public NewMySqlTemplates(boolean quote) {
        super('\', quote);
    }

    public NewMySqlTemplates(char escape, boolean quote) {
        super(escape, quote);
        this.setPrintSchema(true);
    }

}

Then simply use this NewMySqlTemplatesclass in place of the MySQLTemplatesclass like this:

然后简单地使用这个NewMySqlTemplates类代替这样的MySQLTemplates类：

private SQLTemplates templates = new NewMySQLTemplates();
private Configuration configuration = new Configuration(templates); 

I have this working using PostgresTemplates, so I may have a typo or mistake in the NewMySqlTemplates class above, but you should be able to get it to work. Good luck!

我使用 PostgresTemplates 进行了这项工作，因此我在上面的 NewMySqlTemplates 类中可能有拼写错误或错误，但您应该能够使其正常工作。祝你好运！