CODEWITHSUNDEEP
mysqlhibernate
Crabime
Crabime

Reputation: 684

how hibernate deal with like keyword in query sentence

Here met a hesitation about the way hibernate dealing with like keyword in query sentence.This's a small snippet in DaoImpl and see it first:

public List<T> findBySQL(String sql, String... params){
    SQLQuery query = getSession().createSQLQuery(sql);
    query.addEntity(clazz);
    for (int i = 0; i < params.length; i++){
        query.setParameter(i, params[i]);
    }
    List<T> list = query.list();
    return list;
}

ok! without any doubt and use it in below controller:

@RequestMapping(value = "/getFirst/{var}", produces = "application/json; charset=utf-8")
public @ResponseBody Site getFirst(@PathVariable String var){

    String fr = "select * from Food f where f.resname = ?";
    List<Site> siteList = siteService.findBySQL(fr, var);
    Site first = siteList.get(0);
    return first;
}

here is the result:{"src":"http://p0.meituan.net/350.214/deal/ac8ba922d7a6030325976fb31e51b4ce38985.jpg","resname":"哈哈派"}

but when i change such sql sentence like this using like and without amending anything else:

String fr = "select * from Food f where f.resname like '%?%'"; List<Site> siteList = siteService.findBySQL(fr, var);

redeploy and run, exception appear:

HTTP Status 500 - Request processing failed; nested exception is org.hibernate.QueryParameterException: Position beyond number of declared ordinal parameters. Remember that ordinal parameters are 1-based! Position: 1

but i don't think it's the parameter index problem, maybe something error or maybe like can not be used like such way? so i wonder how hibernate deal with 'like' keyword?

Upvotes: 0

Views: 215

Answers (1)

Rohit Gaikwad
Rohit Gaikwad

Reputation: 3914

You should add the wildchar(%) to the parameter and not the query. So modify your query as below.

String fr = "select * from Food f where f.resname like ? ";
List<Site> siteList = siteService.findBySQL(fr, "%"+var+"%");

 OR

String fr = "select * from Food f where f.resname like ? ";
List<Site> siteList = siteService.findBySQL(fr, new String[]{"%"+var+"%"});

Upvotes: 1

Related Questions