Querydsl: Queries and custom data types

[sinuhepop]

Some months ago I wrote this question in StackOverflow with no answer: http://stackoverflow.com/questions/19017907/custom-data-types-using-eclipselink-and-querydsl

I've created a custom data type for storing valid and normalized email addresses:

public class Email implements Serializable {

    private final String value;

    public Email(String emailAddress) {
        this.value = normalize(emailAddress);
        if (!isValid(value)) {
            throw new IllegalArgumentException("Email address format is not valid: " + value);
        }
    }
    ...
}

And its correspondent JPA 2.1 converter for automatically storing to and retrieving from database:

@Converter(autoApply = true)
public class EmailConverter implements AttributeConverter<Email, String> {

    @Override
    public String convertToDatabaseColumn(Email email) {
        return email == null ? null : email.toString();
    }

    @Override
    public Email convertToEntityAttribute(String email) {
        return email == null ? null : new Email(email);
    }

}

Now, I can use it as a property of a person entity:

public class Person extends BaseEntity {

    private String name;

    private LocalDate birthDate;

    @QueryType(PropertyType.STRING)
    private Email email;

    ...
}

And this works perfectly. But I have a problem when I try to find all persons by the starting of their email address using Querydsl. I've annotated email property with @QueryType as String. This way, Querydsl metamodel is created such ay I'm able to make a query (using .startsWith()) like this:

private static final QPerson person = QPerson.person;

public List<Person> getEmailStartWith(String pattern) {
    pattern = Email.normalize(pattern);
    return new JPAQuery(getEntityManager())
        .from(person)
        .where(person.email.startsWith(pattern))
        .orderBy(person.email.asc())
        .list(person);
}

But I get this exception when I run it:

java.lang.IllegalArgumentException: You have attempted to set a value of type class java.lang.String for parameter 1 with expected type of class xxx.Email from query string select person
from Person person
where person.email like ?1 escape '!'
order by person.email asc.
at org.eclipse.persistence.internal.jpa.QueryImpl.setParameterInternal(QueryImpl.java:932) ~[eclipselink-2.5.0.jar:2.5.0.v20130507-3faac2b]
at org.eclipse.persistence.internal.jpa.QueryImpl.setParameterInternal(QueryImpl.java:906) ~[eclipselink-2.5.0.jar:2.5.0.v20130507-3faac2b]
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.setParameter(EJBQueryImpl.java:469) ~[eclipselink-2.5.0.jar:2.5.0.v20130507-3faac2b]
at org.eclipse.persistence.internal.jpa.EJBQueryImpl.setParameter(EJBQueryImpl.java:1) ~[eclipselink-2.5.0.jar:2.5.0.v20130507-3faac2b]
at com.mysema.query.jpa.impl.JPAUtil.setConstants(JPAUtil.java:44) ~[querydsl-jpa-3.2.1.jar:na]
at com.mysema.query.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:130) ~[querydsl-jpa-3.2.1.jar:na]
at com.mysema.query.jpa.impl.AbstractJPAQuery.createQuery(AbstractJPAQuery.java:97) ~[querydsl-jpa-3.2.1.jar:na]
at com.mysema.query.jpa.impl.AbstractJPAQuery.list(AbstractJPAQuery.java:240) ~[querydsl-jpa-3.2.1.jar:na]
...

I can get the correct result using JPQL (and an inelegant trick, concat):

public List<Person> getEmailStartWith(String pattern) {
    pattern = Email.normalize(pattern);
    return getEntityManager()
        .createQuery("select p from Person p where p.email like concat(?1, '%')", Person.class)
        .setParameter(1, pattern)
        .getResultList();
}

But of course I prefer Querydsl's type safety. Is possible to create this query using this library?

[timowest]

I remember this question on SO, but didn't come up with an answer when it was posted.

Something else you could try is to try the conversion functionality of EclipseLink itself. Maybe there is a conversion declaration that works better for this case.

[johnjaylward]

Timo, is it possible to handle custom data types like this similarly to how Enums are handled? Generally QueryDSL would expect your enum parameters to match, but you can do something like this:

someTable.enumCol.eq(enumOfTheSameType)
or
someTable.enumCol.asString().startsWith("someValue")
or
someTable.enumCol.asNumber().eq(5)

If the "asString()" syntax was made available for custom types, I think it would solve his issue. Of course I have no idea how much work that would entail.

[timowest]

@johnjaylward QueryType annotation usage is the way to go here. asString() and asNumber() look more like conversion functionality and might be confusing.

What do they do? Do they just provide the same path with a different type? The issue here is that EclipseLink expect for certain operations that both arguments have the same type. So your suggestion wouldn't help.

[sinuhepop]

I'll try with last Hibernate version, maybe it handles the conversion differently.

As a lesser evil, is possible to create custom predicates which get translated to JPQL? Something like this:

return new JPAQuery(getEntityManager())
    .from(person)
    .where(new MyCustomPredicateStartsWith(person.email, pattern))
    .orderBy(person.email.asc())
    .list(person);

[sinuhepop]

I changed Email type a little, implementing Comparable<Email>. Now I can do:

return new JPAQuery(getEntityManager())
  .from(person)
  .where(person.email.stringValue().startsWith(pattern))
  .orderBy(person.email.asc())
  .list(person);

@QueryType(PropertyType.STRING) is no longer needed. IMHO this is the best option.

Anyway, I don't understand the relation between Comparable and stringValue().

[timowest]

As a lesser evil, is possible to create custom predicates which get translated to JPQL? Something like this:

Expressions.booleanTemplate("{0} like concat({1}, '%')", arg1, arg2)

Anyway, I don't understand the relation between Comparable and stringValue().

Using Comparable changes the supertype of QEmail. stringValue() will add a SQL level string conversion of the email column and EclipseLink doesn't require any more that the right hand side of the like operation is Email, because the left hand side changed to String.

You could also create a delegate method for Email like %. Delegate methods are documented here http://www.querydsl.com/static/querydsl/3.2.4/reference/html/ch03s03.html#d0e2038

But these are only workarounds. The real problem seems to be either a bug in EclipseLink or a missing configuration aspect.

[sinuhepop]

I understand that QEmail has methods like goe(), asc() et al when Email implements Comparable, but I don't understand why occurs with stringValue() too. In the end, it just makes a CAST in the database, nothing related to Comparable.

Probably a delegate method is the most elegant solution, I'll give it a try.

You're right. Sadly, these are just workarounds.

[timowest]

@sinuhepop ComparableExpressionBase is also (mis)used as a super type for literal types. Maybe for literal types another type could be used in between for the stringValue() method.

[timowest]

Closing, since this is more a EclipseLink than a Querydsl issue.

[hohwille]

For the record: If your custom type implements Comparable then you will get a ComparablePath. However, ComparablePath does not offer stringValue() (maybe it did in earlier versions of QueryDSL). IMHO this is still an QueryDSL issue and not related to EclipseLink.
BTW: I am using hibernate + queryDSL (without generation of Q-classes) and would also love to find a reasonable solution. Problem also is if there is no "converter function" (that does nothing but a cast from custom type to String in JPQL so it can be treated as String/VARCHAR in SQL) I can not find a complete solution as I have to supply an instance of my custom type using Like expression what might be invalid for the custom datatype (e.g. I might not be able to instantiate something like new Email("foo@%") because the Email class will do validation). Hence a conversion like stringValue() would actually be required here...

[mkacunha]

I am in 2020 and a have the same problem. How I can to resolve this?