在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);??
修改以后就不会有问题啦
<script type="text/javascript"></script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script>
Mysql中too many connections问题的解决 | mysql的用户权限
- 16:07
- 浏览 (2899)
- 评论 (1)
- 分类: hibernate
- 收藏
- 相关推荐
评论