Crabime Crabime - 2 months ago 6
MySQL Question

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?

Answer

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+"%"});