在hibernate执行标准的sql,一开始写了如下的代码:
java 代码
- public List querySQL(final String sql) {
- HibernateTemplate ht = getHibernateTemplate();
- return (List) ht.execute(new HibernateCallback() {
- public Object doInHibernate(Session session) throws HibernateException {
- try {
- SQLQuery query = session.createSQLQuery(sql);
- return query.list();
- } catch (RuntimeException e) {
- log.error("query sql catch exception: " , e);
- throw e;
- }
- }
- });
- }
执行一般的查询还没有问题,但是对于某些复杂的sql语句,比如如下这句:
sql="select fact.inetip as item1,count(distinct(fact.mac)) as item2 from report_fact_t fact where " +
"not exists (select * from netbar_outsideips_t ips where fact.inetip=ips.ip) " +
"and terminallog_time=str_to_date('2007-09-04','%Y-%m-%d') group by inetip";
就会报出如下的异常:
java 代码
- [appfuse] ERROR [main] LookupDaoHibernate.doInHibernate(56) | query sql catch exception:
- org.hibernate.exception.SQLGrammarException: could not execute query
- at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
- at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
- 。。。
- Caused by: java.sql.SQLException: Column 'inetip' not found.
- at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:910)
- at com.mysql.jdbc.ResultSet.findColumn(ResultSet.java:955)
- at com.mysql.jdbc.ResultSet.getString(ResultSet.java:5447)
很是奇怪的报错!查看了它的文档,也没有看到有啥特别的说明。最后通过试验发现对于复杂的对象类型,必须用addScalar说明数据的类型,才能正常工作。修改后的代码如下:
- public List querySQL(final String sql, final Map<String,Type> paramMap) {
- HibernateTemplate ht = getHibernateTemplate();
- return (List) ht.execute(new HibernateCallback() {
- public Object doInHibernate(Session session) throws HibernateException {
- try {
- SQLQuery query = session.createSQLQuery(sql);
- Set entrySet=paramMap.entrySet();
- for (Iterator it=entrySet.iterator();it.hasNext();) {
- Entry entry=(Entry)it.next();
- query=query.addScalar((String)entry.getKey(),(Type)entry.getValue());
- }
- return query.list();
- } catch (RuntimeException e) {
- log.error("query sql catch exception: " , e);
- throw e;
- }
- }
- });
- }
外部调用的时候,要传递数据的类型:
java 代码
- sql="select fact.inetip as item1,count(distinct(fact.mac)) as item2 from report_fact_t fact where " +
- "not exists (select * from netbar_outsideips_t ips where fact.inetip=ips.ip) " +
- "and terminallog_time=str_to_date('2007-09-04','%Y-%m-%d') group by inetip";
- Map paramMap=new HashMap();
- paramMap.put("item1", Hibernate.STRING);
- paramMap.put("item2",Hibernate.INTEGER);
- List results = dao.querySQL(sql,paramMap);
修改以后就不会有问题啦