为什么JPA TypedQuery抛出异常:参数值[...]与预期的类型[java.lang.Character]不匹配? [英] Why does JPA TypedQuery throw Exception: Parameter value [...] did not match expected type [java.lang.Character]?

问题描述

我搜索了类似的问题，但发现对我的这个特别奇怪的问题没有帮助.异常消息似乎是可以理解的，但是对它的原因以及在何处进行适当更正的明智的引用仍然使我不胜其烦...我就像 WHY是预期的类型java.lang.Character ???

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???

我有一个名为params的HashMap<String, Object>，其中保存了我需要用来过滤对MySQL数据库的查询结果的所有搜索参数，因此这就是我如何使用JPA Criteria API构建查询的方法.

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.

目的是在所有指定的dbColumns中搜索作为参数search

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();

但是我一直收到此错误. 参数值[％tunji％]与此行TypedQuery<Client> query = em.createQuery(cq);

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

请参见下面的StackTrace代码段；

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]

这实际上使我感到困惑，因为我在任何时候都没有引用java.lang.Character.此外，除dateJoined以外的所有MySQL列的类型均为VARCHAR，应映射为Java的 String 类型.

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.

dbColumn dateJoined的MySQL类型为DATETIME，因此为什么要使用entity.get(column).as(String.class)以便将日期的String表示形式与search String进行比较.

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.

响应@Ish ...这是Client实体的样子

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
}

推荐答案

在对各种策略进行了很好的试验之后，这就是我最终完成的工作.

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

感谢这篇帖子的潜在顾客此处让我想起了JPA Tuple 接口本质上是一个可以返回多个结果类型的对象.因此，执行我的like比较，并且由于Date不能简单地转换为String，因此以下步骤；

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. 我将该列作为Tuple
2. 检查元组对象是否可以从日期分配
3. 如果是，则获取Date-Format表达式并将其传递给like表达式.

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();

值得一经的注意事项-

1. 我知道无论从哪里开始搜索，我的所有日​​期都以07/10/2015 10:25:09 PM格式显示，因此我知道如何将要比较的日期格式设置为"'%d/%m/%Y %r'"的形式.
2. 这只是适用于Dates的一个步骤.大多数其他类型，例如int，long，char ...等都可以直接转换为String，并且随着我探索更多的数据类型，对于任何其他不能直接转换为String的Type，我肯定也会做同样的事情.

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!

这篇关于为什么JPA TypedQuery抛出异常:参数值[...]与预期的类型[java.lang.Character]不匹配?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！