How to get fully materialized query from querydsl

Question

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.

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.

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

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:

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

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

Veinule

I am doing the exact same things you are doing -- (1) using getSQL(...) with projections so as to not execute the query, and (2) replacing the '?' with the bindings on a one-by-one basis. I personally do not know of a better solution. – Pori

If querydsl can build fully materialized query and support schema prefixing, it would be the perfect tool needed for dynamic query building. I am still hoping there is something that I am probably missing in the picture here. – Veinule

It does support schema prefixing -- give me a few minutes and I'll post an answer how to do that. – Pori

Answer 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

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.

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

It will make your code typesafe, compact and readable.

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

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

with

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

Visible

Thanks. The Literals now work after setUseLiterals on configuration. But the schema notation still does not show up, even after using MySQLTemplates.builder().printSchema().build() . I am using version 3.3.1. – Veinule

Schema prefixing works if you use variables with schema metadata, there is none for new PathImpl<Object>(Object.class, table), use code generation or RelationalPathBase instead – Valenba

Thanks Timo, can you please elaborate on what you mean by using code generation and RelationalPathBase. – Veinule

@TimoWestkämper - seems I am always doing things the hard way :( – Pori

@DavidFleeman No problem. Next time you write a clunky Querydsl-workaround get in touch ;) – Valenba

Thank you so much. It works perfectly. Just one small note - You are missing a parameter in your RelationalPathBase constructor call. It has 4 parameters. – Veinule

@Veinule Just fixed the parameters. – Valenba

Answer 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:

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:

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:

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!

Pori

That means I have to subclass every template class for the schema prefixing to work. – Veinule

@Veinule With my approach, yes. The trick is the setPrintSchema(true) statement -- if you can figure out how to get the print schema flag set another way, then please let me know! It is probably possible! – Pori