前段时间从网上下载了一个简单的SSH的web项目,项目发布和部署后,测试过程中遇到错误。即在通过查询条件进行查询时,碰到unexpected token的错误。
在查找相关资料后,该问题得到解决,以下是相关代码:
报错的代码:
public PageModel findAllItem(final String queryString, final int pageNo, final int pageSize) {
List itemList = new ArrayList();
if (queryString != null && !"".equals(queryString)) {
itemList = getHibernateTemplate().executeFind(new HibernateCallback(){
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
System.out.println("queryString:"+queryString +"pageNo:" + pageNo +"pageSize:"+pageSize);
return session.createQuery("from Item i where i.itemNo like ? i.itemName like ? order by i.itemNo")
.setParameter(0, queryString + "%")
.setParameter(1, queryString + "%")
.setFirstResult((pageNo-1) * pageSize)
.setMaxResults(pageSize)
.list();
}
});
}else {
itemList = getHibernateTemplate().executeFind(new HibernateCallback(){
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
return session.createQuery("from Item i join fetch i.itemCategory join fetch i.itemUnit order by i.itemNo")
.setFirstResult((pageNo-1) * pageSize)
.setMaxResults(pageSize)
.list();
}
});
}
PageModel pageModel = new PageModel();
pageModel.setPageNo(pageNo);
pageModel.setPageSize(pageSize);
pageModel.setList(itemList);
pageModel.setTotalRecords(getTotalRecords(queryString));
return pageModel;
}
public int getTotalRecords(String queryString) {
int count = 0;
if (queryString != null && !"".equals(queryString)) {
count = ((Long)getSession().createQuery("select count(*) from Item i where i.itemNo like ? i.itemName like ?")
.setParameter(0, queryString + "%")
.setParameter(1, queryString + "%")
.uniqueResult()).intValue();
}else {
count = ((Long)getSession().createQuery("select count(*) from Item i")
.uniqueResult()).intValue();
}
return count;
}
报错信息1:
20:36:06,060 ERROR PARSER:33 - line 1:57: unexpected token: i
20:36:06,061 ERROR PARSER:33 - line 1:68: unexpected token: like
org.springframework.orm.hibernate3.HibernateQueryException: unexpected token: i near line 1, column 57 [from com.jialin.drp.domain.Item i where i.itemNo like
? i.itemName like ? order by i.itemNo]; nested exception is org.hibernate.hql.ast.QuerySyntaxException: unexpected token: i near line 1, column 57 [from
com.jialin.drp.domain.Item i where i.itemNo like ? i.itemName like ? order by i.itemNo]
Caused by: org.hibernate.hql.ast.QuerySyntaxException: unexpected token: i near line 1, column 57 [from com.jialin.drp.domain.Item i where i.itemNo like ?
i.itemName like ? order by i.itemNo]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at org.springframework.orm.hibernate3.HibernateTemplate$CloseSuppressingInvocationHandler.invoke(HibernateTemplate.java:1192)
at $Proxy8.createQuery(Unknown Source)
at com.jialin.drp.dao.imp.ItemDaoImp$1.doInHibernate(ItemDaoImp.java:38)
at org.springframework.orm.hibernate3.HibernateTemplate.execute(HibernateTemplate.java:362)
at org.springframework.orm.hibernate3.HibernateTemplate.executeFind(HibernateTemplate.java:332)
at com.jialin.drp.dao.imp.ItemDaoImp.findAllItem(ItemDaoImp.java:34)
at com.jialin.drp.service.imp.ItemServiceImp.findAllItem(ItemServiceImp.java:46)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
报错信息2:
21:53:04,963 ERROR PARSER:33 - line 1:73: unexpected token: i
21:53:04,997 ERROR PARSER:33 - line 1:84: unexpected token: like
org.hibernate.hql.ast.QuerySyntaxException: unexpected token: i near line 1, column 73 [select count(*) from com.jialin.drp.domain.Item i where i.itemNo like
? i.itemName like ?]
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:31)
at org.hibernate.hql.ast.QuerySyntaxException.convert(QuerySyntaxException.java:24)
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:59)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:258)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:157)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:111)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:77)
at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:56)
at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:72)
at org.hibernate.impl.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:133)
at org.hibernate.impl.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:112)
at org.hibernate.impl.SessionImpl.createQuery(SessionImpl.java:1623)
at com.jialin.drp.dao.imp.ItemDaoImp.getTotalRecords(ItemDaoImp.java:102)
at com.jialin.drp.dao.imp.ItemDaoImp.findAllItem(ItemDaoImp.java:78)
修改后的代码:
public PageModel findAllItem(final String queryString, final int pageNo, final int pageSize) {
List itemList = new ArrayList();
if (queryString != null && !"".equals(queryString)) {
itemList = getHibernateTemplate().executeFind(new HibernateCallback(){
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
System.out.println("queryString:"+queryString +"pageNo:" + pageNo +"pageSize:"+pageSize);
return session.createQuery("from Item i where i.itemNo like :itemNo or i.itemName like :itemName order by i.itemNo")
.setParameter("itemNo", queryString + "%")
.setParameter("itemName", queryString + "%")
.setFirstResult((pageNo-1) * pageSize)
.setMaxResults(pageSize)
.list();
}
});
}else {
itemList = getHibernateTemplate().executeFind(new HibernateCallback(){
public Object doInHibernate(Session session)
throws HibernateException, SQLException {
return session.createQuery("from Item i join fetch i.itemCategory join fetch i.itemUnit order by i.itemNo")
.setFirstResult((pageNo-1) * pageSize)
.setMaxResults(pageSize)
.list();
}
});
}
PageModel pageModel = new PageModel();
pageModel.setPageNo(pageNo);
pageModel.setPageSize(pageSize);
pageModel.setList(itemList);
pageModel.setTotalRecords(getTotalRecords(queryString));
return pageModel;
}
public int getTotalRecords(String queryString) {
int count = 0;
if (queryString != null && !"".equals(queryString)) {
count = ((Long)getSession().createQuery("select count(*) from Item i where i.itemNo like :itemNo or i.itemName like :itemName")
.setParameter("itemNo", queryString + "%")
.setParameter("itemName", queryString + "%")
.uniqueResult()).intValue();
}else {
count = ((Long)getSession().createQuery("select count(*) from Item i")
.uniqueResult()).intValue();
}
return count;
}
修改正确后,后台打印日志:
Hibernate: select item0_.itemNo as itemNo1_, item0_.itemName as itemName1_, item0_.spec as spec1_, item0_.pattern as pattern1_, item0_.itemCategory as itemCate5_1_, item0_.itemUnit as itemUnit1_, item0_.uploadFileName as uploadFi7_1_ from t_items item0_ where item0_.itemNo like ? or item0_.itemName like ? order by item0_.itemNo limit ?
Hibernate: select count(*) as col_0_0_ from t_items item0_ where item0_.itemNo like ? or item0_.itemName like ?
Hibernate: select itemcatego0_.id as id0_0_, itemcatego0_.name as name0_0_ from t_data_dict itemcatego0_ where itemcatego0_.id=? and itemcatego0_.category='A'
Hibernate: select itemunit0_.id as id0_0_, itemunit0_.name as name0_0_ from t_data_dict itemunit0_ where itemunit0_.id=? and itemunit0_.category='B'