http://zjsoft.iteye.com/blog/143431
使用SQLQuery
对原生SQL查询执行的控制是通过SQLQuery接口进行的,通过执行Session.createSQLQuery()获取这个接口。最简单的情况下,我们可以采用以下形式:
List cats
=
sess.createSQLQuery(
"
select * from cats
"
).addEntity(Cat.
class
).list();
这个查询指定了:
SQL查询字符串
查询返回的实体
这里,结果集字段名被假设为与映射文件中指明的字段名相同。对于连接了多个表的查询,这就可能造成问题,因为可能在多个表中出现同样名字的字段。下面的方法就可以避免字段名重复的问题:
List cats
=
sess.createSQLQuery(
"
select {cat.*} from cats cat
"
).addEntity(
"
cat
"
, Cat.
class
).list();
这个查询指定了:
SQL查询语句,它带一个占位符,可以让Hibernate使用字段的别名.
查询返回的实体,和它的SQL表的别名.
addEntity()方法将SQL表的别名和实体类联系起来,并且确定查询结果集的形态。
addJoin()方法可以被用于载入其他的实体和集合的关联.
List cats
=
sess.createSQLQuery(
"
select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id
"
)
.addEntity(
"
cat
"
, Cat.
class
)
.addJoin(
"
kitten
"
,
"
cat.kittens
"
)
.list();
原生的SQL查询可能返回一个简单的标量值或者一个标量和实体的结合体。
Double max
=
(Double) sess.createSQLQuery(
"
select max(cat.weight) as maxWeight from cats cat
"
)
.addScalar(
"
maxWeight
"
, Hibernate.DOUBLE);
.uniqueResult();
除此之外,你还可以在你的hbm文件中描述结果集映射信息,在查询中使用。
List cats
=
sess.createSQLQuery(
"
select {cat.*}, {kitten.*} from cats cat, cats kitten where kitten.mother = cat.id
"
)
.setResultSetMapping(
"
catAndKitten
"
)
.list();
命名SQL查询
可以在映射文档中定义查询的名字,然后就可以象调用一个命名的HQL查询一样直接调用命名SQL查询.在这种情况下,我们不 需要调用addEntity()方法.
<
sql
-
query name
=
"
persons
"
>
<
return
alias
=
"
person
"
class
=
"
eg.Person
"
/>
Select person.NAME AS {person.name},person.AGE AS {person.age},person.SEX AS {person.sex} FROM PERSON person Where person.NAME LIKE :namePattern
</
sql
-
query
>
List people
=
sess.getNamedQuery(
"
persons
"
).setString(
"
namePattern
"
, namePattern)
.setMaxResults(
50
)
.list();
http://fishyych.iteye.com/blog/123328
在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);
修改以后就不会有问题啦