如何从querydsl获得完全具体化的查询

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;
}

2 个解决方案

#1

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);

#2

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 NewMySqlTemplates class in place of the MySQLTemplates class 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类中有一个错字或错误，但你应该能够让它工作。祝你好运！

#1