Re: native SQL withn JPA Query

[Timo Westkämper]

Hi.

You can use JPASQLQuery for native queries in Querydsl JPA http://www.querydsl.com/static/querydsl/2.8.0/apidocs/com/mysema/query/jpa/sql/JPASQLQuery.html

The usage is explained here http://www.querydsl.com/static/querydsl/2.8.0/reference/html/ch02.html#d0e343

Br,
Timo Westkämper

On Thu, Oct 18, 2012 at 8:24 AM, Querydsl on behalf of Joos <querydsl+noreply-APn2wQdJSNGgLj0...@googlegroups.com> wrote:

Hi,

in my query I'm using a special/custom type of search (chemical structure search) and for that I need to be able to add native SQL to a query. I use PostgreSQL and an according extension that does the chemical structure search.

I need to add following native SQL to my JPA query:

where chemical_structure.chemical_structure @ (:structure, :options)::bingo.sub

:structure, :options are the query parameters.

I currently have:
<code>
JPAQuery query = new JPAQuery(entityManager);
       
        QChemicalCompound compound = QChemicalCompound.chemicalCompound;
        QChemicalCompoundComposition composition = QChemicalCompoundComposition.chemicalCompoundComposition;
        QChemicalStructure structure = QChemicalStructure.chemicalStructure;
       
        ChemicalCompound result = query.from(compound)
                .innerJoin(compound.composition, composition)
                .innerJoin(composition.pk.chemicalStructure, structure)
                .where() // How to put native SQL here in where?
</code>

--
Timo Westkämper
Mysema Oy
+358 (0)40 591 2172
www.mysema.com

[Joos]

Maybe I'm dumb but I don't see the big difference to the JPA Query. I need to put a plain text string (=SQL) as part of the where clause.

eg. instead of

.where(cat.dtype.eq("Cat"), mate.dtype.eq("Cat"))

I want this:

.where("cat.dtype = 'Cat' AND mate.dtype = 'Cat'")

Why? Because the operator I need to use is custom and does not exist in querydsl.

On Thursday, October 18, 2012 4:06:08 PM UTC+2, Timo Westkämper wrote:

Hi.

You can use JPASQLQuery for native queries in Querydsl JPA http://www.querydsl.com/static/querydsl/2.8.0/apidocs/com/mysema/query/jpa/sql/JPASQLQuery.html

The usage is explained here http://www.querydsl.com/static/querydsl/2.8.0/reference/html/ch02.html#d0e343

Br,
Timo Westkämper

[Timo Westkämper]

Hi.

On Fri, Oct 19, 2012 at 8:21 AM, Querydsl on behalf of Joos <querydsl+noreply-APn2wQdJSNGgLj0...@googlegroups.com> wrote:

Maybe I'm dumb but I don't see the big difference to the JPA Query.

On the API level JPAQuery and JPASQLQuery are very similar. But JPAQuery serializes queries into JPQL whereas JPASQLQuery uses SQL.

If you are sure that you can express your custom SQL snippet also as an JPQL expression, use JPAQuery. Otherwise use JPASQLQuery.

 

I need to put a plain text string (=SQL) as part of the where clause.

eg. instead of

.where(cat.dtype.eq("Cat"), mate.dtype.eq("Cat"))

I want this:

.where("cat.dtype = 'Cat' AND mate.dtype = 'Cat'")

.where(cat.dtype.eq("Cat), mate.dtype.eq("Cat"))

is just a shorthand for

.where(cat.dtype.eq("Cat).and(mate.dtype.eq("Cat")))
 

Why? Because the operator I need to use is custom and does not exist in querydsl.

You can use BooleanTemplate for that.

e.g. query.where(BooleanTemplate.create("{0} @ ({1}, {2})", arg1, arg2, arg3)
 
Br,
Timo Westkämper

[Joos]

Hi Timo,

and what type do args have? In my case they are strings and that leads to following:

no suitable method found for create(String,String,String,String)
    method BooleanTemplate.create(Template,Expression<?>...) is not applicable
      (actual argument String cannot be converted to Template by method invocation conversion)
    method BooleanTemplate.create(String,Expression<?>...) is not applicable
      (argument type String does not conform to vararg element type Expression<?>)

On Friday, October 19, 2012 9:16:58 AM UTC+2, Timo Westkämper wrote:

Hi.

[Joos]

This compiles:

.where(BooleanTemplate.create("{0} @ ({1}, {2})\\:\\:bingo.sub",
                    structure.structureData, ConstantImpl.create("c1ccccc1"),
                    ConstantImpl.create("")))

When running the query

JPAQuery query = new JPAQuery(entityManager);

        QChemicalCompound compound = QChemicalCompound.chemicalCompound;
        QChemicalCompoundComposition composition = QChemicalCompoundComposition.chemicalCompoundComposition;
        QChemicalStructure structure = QChemicalStructure.chemicalStructure;

        ChemicalCompound result = query.from(compound)
                .innerJoin(compound.composition, composition)
                .innerJoin(composition.pk.chemicalStructure, structure)               

                .where(BooleanTemplate.create("{0} @ ({1}, {2})\\:\\:bingo.sub",
                    structure.structureData, ConstantImpl.create("c1ccccc1"),
                    ConstantImpl.create("")))
                .uniqueResult(compound);

I get NullPointerException:

java.lang.NullPointerException: null
    at com.mysema.query.types.ValidatingVisitor.visit(ValidatingVisitor.java:121) ~[querydsl-core-2.8.1.jar:na]
    at com.mysema.query.types.ValidatingVisitor.visit(ValidatingVisitor.java:55) ~[querydsl-core-2.8.1.jar:na]
    at com.mysema.query.types.ValidatingVisitor.visit(ValidatingVisitor.java:29) ~[querydsl-core-2.8.1.jar:na]
    at com.mysema.query.types.OperationImpl.accept(OperationImpl.java:81) ~[querydsl-core-2.8.1.jar:na]
    at com.mysema.query.DefaultQueryMetadata.validate(DefaultQueryMetadata.java:325) ~[querydsl-core-2.8.1.jar:na]
    at com.mysema.query.DefaultQueryMetadata.addJoin(DefaultQueryMetadata.java:120) ~[querydsl-core-2.8.1.jar:na]
    at com.mysema.query.DefaultQueryMetadata.addJoin(DefaultQueryMetadata.java:110) ~[querydsl-core-2.8.1.jar:na]
    at com.mysema.query.support.QueryMixin.innerJoin(QueryMixin.java:218) ~[querydsl-core-2.8.1.jar:na]
    at com.mysema.query.jpa.JPQLQueryBase.innerJoin(JPQLQueryBase.java:140) ~[querydsl-jpa-2.8.1.jar:na]

[Timo Westkämper]

Hi.

This exception comes from the path initialization behaviour in Querydsl http://www.querydsl.com/static/querydsl/2.8.0/reference/html/ch03s03.html#d0e1440

I just improved the exception messages for such cases.

Br,
Timo

[Joos]

Here the final solution I came up with if anyone has a similar issue. Sure not simple and if you don't know it, you are faster with plain string concatenation.

First of I have a many-to-many relationship where the link table has an additional column. The link table entity contains an @EmbeddedId composite id.
(see http://it4beginners.wordpress.com/2012/10/05/spring-3-and-hibernate-4-for-beginners/ for full details)

to avoid the NullPointerException I had to add @QueryInit("chemicalStructure") to the @EmbeddedId
[code]
@EmbeddedId
@QueryInit("chemicalStructure")
public ChemicalCompoundCompositionID getPk() {
    return pk;
}
[/code]
(This part is kind of undesirable to make you entity classes depend on querydsl)

However this lead to the next Problem: hibernate exception because @ is an invalid character.

The solution to this is completely unrelated to querydsl but I will just post it here. I had to extend the Dialect I use an register an additional function.
[code]
public class BingoPostgreSQLDialect extends PostgreSQL82Dialect {

    public BingoPostgreSQLDialect() {
         registerFunction("issubstructure", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN, "?1  @ (?2, ?3)::bingo.sub"));
    }
}
[/code]

Note that I had to write "issubstructure" all in lower case else it would not work.However in the BooleanTemplate itself you can write it in camel case and it works. A bit strange yes. I assume querydsl or hibernate make everything lower case and hence the function must be lower case. You then need to tell hibernate / JPA to use this custom dialect (in my case in springs application context file) instead fo the default one for your database.

The query then look like this:
[code]

        JPAQuery query = new JPAQuery(entityManager);

        QChemicalCompound compound = QChemicalCompound.chemicalCompound;
        QChemicalCompoundComposition composition = QChemicalCompoundComposition.chemicalCompoundComposition;
        QChemicalStructure structure = QChemicalStructure.chemicalStructure;
       

        List<ChemicalCompound> result = query.from(compound)
                .innerJoin(compound.composition, composition)
                .innerJoin(composition.pk.chemicalStructure, structure)
                .where(BooleanTemplate.create("isSubstructure({0},{1},{2}) = true",

                    structure.structureData,
                    ConstantImpl.create("c1ccccc1"),
                    ConstantImpl.create("")))

                .list(compound);
        return result;
[/code]

Mind "= true" for the isSubstructure function. That is also absolutely required!

On Monday, October 22, 2012 8:38:52 AM UTC+2, Timo Westkämper wrote:

Hi.

This exception comes from the path initialization behaviour in Querydsl http://www.querydsl.com/static/querydsl/2.8.0/reference/html/ch03s03.html#d0e1440

I just improved the exception messages for such cases.

Br,
Timo

[Timo Westkämper]

Hi.

On Mon, Oct 22, 2012 at 2:30 PM, Querydsl on behalf of Joos <querydsl+noreply-APn2wQdJSNGgLj0...@googlegroups.com> wrote:

Here the final solution I came up with if anyone has a similar issue. Sure not simple and if you don't know it, you are faster with plain string concatenation.

First of I have a many-to-many relationship where the link table has an additional column. The link table entity contains an @EmbeddedId composite id.
(see http://it4beginners.wordpress.com/2012/10/05/spring-3-and-hibernate-4-for-beginners/ for full details)

to avoid the NullPointerException I had to add @QueryInit("chemicalStructure") to the @EmbeddedId
[code]
@EmbeddedId
@QueryInit("chemicalStructure")
public ChemicalCompoundCompositionID getPk() {
    return pk;
}
[/code]
(This part is kind of undesirable to make you entity classes depend on querydsl)

Yes, but this is not the only option. You can also use accessor methods like documented in the Querydsl reference docs.

Querydsl uses final fields, so limited initialization depth can't be avoided. But maybe the default depth could be raised? That would mean more memory usage, but less special cases.
 

However this lead to the next Problem: hibernate exception because @ is an invalid character.

The solution to this is completely unrelated to querydsl but I will just post it here. I had to extend the Dialect I use an register an additional function.
[code]
public class BingoPostgreSQLDialect extends PostgreSQL82Dialect {

    public BingoPostgreSQLDialect() {
         registerFunction("issubstructure", new SQLFunctionTemplate(StandardBasicTypes.BOOLEAN, "?1  @ (?2, ?3)::bingo.sub"));
    }
}
[/code]

Note that I had to write "issubstructure" all in lower case else it would not work.However in the BooleanTemplate itself you can write it in camel case and it works. A bit strange yes. I assume querydsl or hibernate make everything lower case and hence the function must be lower case. You then need to tell hibernate / JPA to use this custom dialect (in my case in springs application context file) instead fo the default one for your database.

Yes, it is unfortunate that Querydsl can't abstract this away. Unfortunately I don't see a way how to do it.
 

The query then look like this:
[code]

        JPAQuery query = new JPAQuery(entityManager);

        QChemicalCompound compound = QChemicalCompound.chemicalCompound;
        QChemicalCompoundComposition composition = QChemicalCompoundComposition.chemicalCompoundComposition;
        QChemicalStructure structure = QChemicalStructure.chemicalStructure;
       

        List<ChemicalCompound> result = query.from(compound)
                .innerJoin(compound.composition, composition)
                .innerJoin(composition.pk.chemicalStructure, structure)
                .where(BooleanTemplate.create("isSubstructure({0},{1},{2}) = true",

                    structure.structureData,
                    ConstantImpl.create("c1ccccc1"),
                    ConstantImpl.create("")))

                .list(compound);
        return result;
[/code]

Mind "= true" for the isSubstructure function. That is also absolutely required!

I added auto conversion of constants as a feature for the 3.0 release https://github.com/mysema/querydsl/issues/178

This will simplify it to

        List<ChemicalCompound> result = query.from(compound)
                .innerJoin(compound.

composition, composition)
                .innerJoin(composition.pk.chemicalStructure, structure)
                .where(BooleanTemplate.create("isSubstructure({0},{1},{2}) = true",

                    structure.structureData, "c1ccccc1", ""))

                .list(compound);

Br,
Timo