How to unit test Querydsl SQL generation?

[timowest]

Is there a best practice for unit testing SQL queries generated by Querydsl? I'm looking for a solution without using the EntityManager which is needed as a parameter when creating a new JPAQuery object.
But I guess I have to set up a complete test environment containing a persistence unit, getting the EntityManager using the EntityManagerFactory and all this overhead.

Unfortunately there is no chapter about testing in the Querydsl documentation. So how do you test Querydsl output?

timowest

There lots of ways to test Querydsl queries

* populating the database and asserting that the Query returns the right results
* testing only that the serialized query string matches the expectations
* injecting mock dependencies and asserting that they are called in the right way

What aspect do you want to test?

mwalter

Well first of all I would like to check the generated query string against a SQL statement (second point you listed above). I just want to make sure the query is as expected.

But the other items sound very interesting too. Is there any sample code or best practice for this?

timowest

There aren't any published guides on how to do this. For sample code the unit tests of Querydsl JPA and SQL will be a good basis.

Actually for most queries the toString() invocation will give you something to verify against.

kennywest

In com.mysema.query.sql.AbstractSQLQuery, there is a protected method called buildQueryString. Extending AbstractSQLQuery and creating a public version for buildQueryString should get you started :)

timowest

Documenting best practices for query testing is a bit out of scope, since we don't really want to favor one approach over the other and covering all of them is really lots of work.

If you really just want to check some serialization logic, then make your custom query class like Kenny suggested and use it for example like this :

> assertEquals("select ...", query().from(user).where(user.name.eq(...)).select(user));

select would be a custom method which adds the projection to the QueryMetadata and returns the serialized query.

This is something which is also done Querydsl unit tests.

mwalter

In com.mysema.query.sql.AbstractSQLQuery, there is a protected method called buildQueryString. Extending AbstractSQLQuery and creating a public version for buildQueryString should get you started :)

In 2.2.0.beta5-SNAPSHOT the method is private so I can't use it anymore. But there is a public createQuery(). Maybe I can use this method for testing?

I tried the following but this way the constants are not filled into the SQL.


public class MyQueryBuilderTest {

    private JPAQueryFactory queryFactory;

    @Before
    public void setUp() {
        final Provider<EntityManager> provider = new Provider<EntityManager>() {
            @Override
            public EntityManager get() {
                return EasyMock.createNiceMock(EntityManager.class);
            }
        };
        queryFactory = new JPAQueryFactory(provider);
    }

    @Test
    public void test() {
        final QSomeObj someObj = QSomeObj.someObj ;
        final JPAQuery query = queryFactory.query().from(someObj).where(someObj.attribute.eq(42));
        Assert.assertNotNull(query);
        Assert.assertEquals("from MyTable myTable\nwhere someObj.attribute = :a1", query.toString());
    }
}


What does the select(user) do you stated in your example? Does it print the partial SQL statement like toString() does?

timowest

Btw, do you want to see the generated JPQL or SQL? My advices were for JPQL generation.

> I tried the following but this way the constants are not filled into the SQL.

The constants are put into the constants map.

> What does the select(user) do you stated in your example? Does it print the partial SQL statement like toString() does?

Yes.

mwalter

Please can you give a code sample for the JPQL generation? I can't use the method buildQueryString() from AbstractJPASQLQuery kennywest proposed because it is private as I wrote before.

Using the following code...


assertEquals("select ...", query().from(user).where(user.name.eq(...)).select(user));


...where do I get the query() from?

timowest

You need to use JPQLQueryBase.buildQueryString(boolean). Kenny mentioned the super class for Querydsl JPA native queries.

query() is just a factory method for Query creation.

Something like this :


class MyQuery extends JPQLQueryBase<MyQuery> {

    public String select(Expression<?>... exprs) {
        getQueryMixin().addToProjection(exprs);
        return buildQueryString(false);
    }
}

mwalter

Sorry but I don't get it put together.
I use the code I showed earlier to create a JPAQueryFactory with the mocked EntityManager. If I call query() on this factory I get a JPAQuery. So far so good.

Now if I extend MyQuery with JPQLQueryBase the compiler says I have to implement a constructor and six methods from the superclass. And when I use the select() method you posted in your last post I get the compiler error: "The method select() is undefined for the JPAQuery."

timowest

The class would be


class MyQuery extends JPQLQueryBase<MyQuery> {

    public MyQuery() {
        super(new DefaultQueryMetadata(), HQLTemplates.DEFAULT);
    }

    public String select(Expression<?>... exprs) {
        getQueryMixin().addToProjection(exprs);
        return buildQueryString(false);
    }

    @Override
    public long count() {
        // TODO Auto-generated method stub
        return 0;
    }

    @Override
    public CloseableIterator<Object[]> iterate(Expression<?>[] args) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public <RT> CloseableIterator<RT> iterate(Expression<RT> projection) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public <RT> SearchResults<RT> listResults(Expression<RT> projection) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public Object[] uniqueResult(Expression<?>[] args) {
        // TODO Auto-generated method stub
        return null;
    }

    @Override
    public <RT> RT uniqueResult(Expression<RT> projection) {
        // TODO Auto-generated method stub
        return null;
    }
}


And you use it like this


String hql = new MyQuery().from(user).where(user.name.eq("John")).select(user);


mwalter

Okay, that's nice.

Your example returns "select user\nfrom User user\nwhere user.name = :a1".
Is it possible to get the query with parameters, i.e. "select user\nfrom User user\nwhere user.name = 'John'"? And can I get the SQL version of the query ("select * from...")? Do I have to extend from something like SQLQueryBase in order to get the SQL syntax?

timowest

Is it possible to get the query with parameters, i.e. "select user\nfrom User user\nwhere user.name = 'John'"?

No directly, but you can do the replacements yourself with the constants map.

And can I get the SQL version of the query ("select * from...")? Do I have to extend from something like SQLQueryBase in order to get the SQL syntax?

This is JPA specific. Maybe there is some Hibernate tool to convert JPQL to SQL. Querydsl doesn't do it.

Do you want to construct a Querydsl JPQL or SQL query and get the SQL? They are different things.