0

I have searched through similar questions and found none helpful for this particularly weird problem of mine. The Exception message seems understandable, but a sensible reference to it's cause and where to make the appropriate corrections still beats me... I'm like WHY is the expected Type java.lang.Character???

I have a HashMap<String, Object> called params that holds all the search parameters that i need to use to filter results of a query to a MySQL Database, so here's how I build my query using JPA Criteria API.

The aim is to search all the specified dbColumns for a String passed as the argument called search

List<String> dbColumns = Arrays.asList("firstname", "lastname", "username", "email", "mobilenumber", "dateJoined");

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Client> cq = cb.createQuery(Client.class);
Root<Client> entity = cq.from(Client.class);
cq.select(entity);

List<Predicate> predicates = new ArrayList<Predicate>();
if(params.containsKey("search") && StringUtils.isNotBlank(search)){
    String search = String.valueOf(params.get("search"));
    for(String column : dbColumns){
        predicates.add(cb.like(cb.lower(entity.get(column).as(String.class)), "%"+search.toLowerCase()+"%")); 
    }
}

cq.where(predicates.toArray(new Predicate[]{}));
TypedQuery<Client> query = em.createQuery(cq); //<--- Error gets thrown here
return query.getResultList();

However I keep getting this error. Parameter value [%tunji%] did not match expected type [java.lang.Character] at this line TypedQuery<Client> query = em.createQuery(cq);

Please see StackTrace snippet below;

Caused by: java.lang.IllegalArgumentException: Parameter value [%tunji%] did not match expected type [java.lang.Character]
    at org.hibernate.ejb.AbstractQueryImpl.validateParameterBinding(AbstractQueryImpl.java:370) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractQueryImpl.registerParameterBinding(AbstractQueryImpl.java:343) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.QueryImpl.setParameter(QueryImpl.java:370) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.criteria.CriteriaQueryCompiler$1$1.bind(CriteriaQueryCompiler.java:194) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.criteria.CriteriaQueryCompiler.compile(CriteriaQueryCompiler.java:247) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:622) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.jboss.as.jpa.container.AbstractEntityManager.createQuery(AbstractEntityManager.java:96) [jboss-as-jpa-7.1.1.Final.jar:7.1.1.Final]

This actually confuses me because i'm NOT making any reference to java.lang.Character at any point. Also all the MySQL columns except dateJoined are of type VARCHAR which should map to java's String Type.

The dbColumn dateJoined is of MySQL type DATETIME hence why I used entity.get(column).as(String.class) so that the String representation of the date will be compared with the search String.

So i'm wondering what may be the cause of this error and how best to go about solving this problem. Any help would be highly appreciated.

EDIT:

In response to @Ish... Here's what the Client entity looks like

@Entity
@Table(name="client")
@NamedQueries({
    @NamedQuery(name="Client.findAll", query="SELECT c FROM Client c")
})
public class Client implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    @Column(unique=true, nullable=false)
    private int id;

    @Column(length=255)
    private String email;

    @Column(length=50)
    private String firstname;

    @Column(length=50)
    private String lastname;

    @Column(length=20)
    private String mobilenumber;

    @Column(unique=true, length=255)
    private String username;

    @Column(nullable=false, name="datejoined")
    private Date dateJoined;

    //... getters ... and ... setters
}
SourceVisor
  • 1,868
  • 1
  • 27
  • 43
  • it might help if you also post you code on the Client entity showing its mappings – Ish Oct 15 '15 at 13:50

2 Answers2

1

Date cannot be cast to a String. See this. Your understanding about Expression.as() method is not what's happening in reality.

Franck
  • 1,754
  • 1
  • 13
  • 14
  • @Frank, do you have an idea how to also search the the `dateJoined` column for the `search` String still using the JPA criteria API? – SourceVisor Oct 15 '15 at 15:25
  • I think what you're trying to achieve is using a SQL function on the RDB side. So I would do something like this: cb.function("TO_CHAR", String.class, root.get(column),cb.literal("'%d/%m/%Y'"). . – Franck Oct 15 '15 at 15:38
  • errr @Franck Thanks, very useful comment. But remember I pointed that my database was MySQL?... I'm sure what you actually meant was "DATE_FORMAT"... cos if my memory hasn't failed me, "TO_CHAR" is an Oracle function. – SourceVisor Oct 15 '15 at 18:35
  • 1
    you're absolutely right...but now you see how using this kind of specific db function defeats the whole purpose of the abstraction offered by jpa...If you don't have any other choice then you might use it as long as you remember what you're doing if you have to migrate to another rdb. – Franck Oct 15 '15 at 18:48
  • You have my upvote for that JPA abstraction comment @Frank!... I'll do well to appropriately document those areas! – SourceVisor Oct 15 '15 at 18:58
  • I would have prefered an upvote on the answer to get some points! – Franck Oct 15 '15 at 20:15
  • Ok, actually you very well deserve it... Yours was the first pointer that set me in the right direction. So there you have it. – SourceVisor Oct 16 '15 at 07:07
0

After some good experimenting with various strategies, here's what I did that finally worked.

Thanks to leads from this post here that made me remember the JPA Tuple Interface which essentially is an Object that can return multiple result Type(s). So to perform my like comparison, and since Date cannot be simply cast to a String here are the steps;

  1. I get the column as a Tuple
  2. do a check on The Tuple Object to see if it's assignable from Date
  3. if it is, then get the Date-Format expression and pass it to the like expression.

Now, this is what I have replaced my former code with and works beautifully;

List<String> dbColumns = Arrays.asList("firstname", "lastname", "username", "email", "mobilenumber", "dateJoined");

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Client> cq = cb.createQuery(Client.class);
Root<Client> entity = cq.from(Client.class);
cq.select(entity);

List<Predicate> predicates = new ArrayList<Predicate>();
if(params.containsKey("search") && StringUtils.isNotBlank(search)){
    String search = String.valueOf(params.get("search"));
    List<Predicate> ORPredicates = new ArrayList<Predicate>();
    for(String column : dbColumns){
        Path<Tuple> tuple = entity.<Tuple>get(column);
        if(tuple.getJavaType().isAssignableFrom(Date.class)){
            Expression<String> dateStringExpr = cb.function("DATE_FORMAT", String.class, entity.get(column), cb.literal("'%d/%m/%Y %r'"));
            ORPredicates.add(cb.like(cb.lower(dateStringExpr), "%"+search.toLowerCase()+"%"));
        }else{
            ORPredicates.add(cb.like(cb.lower(entity.get(column).as(String.class)), "%"+search.toLowerCase()+"%")); 
        }
    }
    predicates.add(cb.or(ORPredicates.toArray(new Predicate[]{})));
}

cq.where(predicates.toArray(new Predicate[]{}));
TypedQuery<Client> query = em.createQuery(cq); //<--- Error gets thrown here
return query.getResultList();

NOTE-WORTHY CONSIDERATIONS -

  1. I am aware that from wherever the search would be initiated, all my Dates are presented in this form 07/10/2015 10:25:09 PM hence my ability to know how to format the Date for the comparison in my like expression as "'%d/%m/%Y %r'".
  2. This is just one step that works for Dates. Most other Types e.g int, long, char ...etc... can all be directly Cast to String and as I explore more Types of data, I'll definitely do the same for any other Type that cannot be directly Cast to String.

Though this works perfectly for me, I'll still keep it open for comments by anyone that may have any reservations about my strategy and I'll only mark it as the correct answer after I've subjected it to many more extensive tests.

And if this helps you out in any way... Cheers mate!

Community
  • 1
  • 1
SourceVisor
  • 1,868
  • 1
  • 27
  • 43