使用addScala将SQLQuery自定义查询映射到pojo中

在平时开发中Hibernate提供的hql基本能够满足我们的日常需求。但是在有些特殊的情况下，还是需要使用原生的sql,并且希望sql查询出来的结果能够绑定到pojo上。

 

Hibernate中由SQLQuery对象提供对原生sql语句的支持，例如如下语句在查询分析器中编写好后可直接使用

 

Sql代码  

1. -- 查询采集设备及正在执行的任务 --   
2. select d.guidID as deviceId, d.strName as deviceName,    
3.     p.guidID as portId, p.strName as portName,    
4.     t.guidTaskID as taskId, t.strName as taskName, t.enumRunStatus as runStatus,    
5.     t.odtTaskStartDate as taskStartDate, t.odtTaskStopDate as taskStopDate   
6. from ULCDevice as d    
7.     inner join ULCDevicePort as p on p.guidBelongDeviceID = d.guidID   
8.     left outer join ULCTaskInfo as t on t.guidRecordPortID = p.guidID and t.enumRunStatus = 2   
9. where d.enumType = 3   
10. order by d.guidID;  

-- 查询采集设备及正在执行的任务 --select d.guidID as deviceId, d.strName as deviceName, p.guidID as portId, p.strName as portName, t.guidTaskID as taskId, t.strName as taskName, t.enumRunStatus as runStatus, t.odtTaskStartDate as taskStartDate, t.odtTaskStopDate as taskStopDatefrom ULCDevice as d inner join ULCDevicePort as p on p.guidBelongDeviceID = d.guidIDleft outer join ULCTaskInfo as t on t.guidRecordPortID = p.guidID and t.enumRunStatus = 2where d.enumType = 3order by d.guidID;

 

HibernateCallback中，使用Session创建SQLQuery对象:

 

Java代码  

1. StringBuffer b = new StringBuffer();   
2.   
3. //... 省略SQL拼接代码   
4. String sql = b.toString();   
5.                    
6. SQLQuery query = session.createSQLQuery(sql);  

StringBuffer b = new StringBuffer();//... 省略SQL拼接代码String sql = b.toString();SQLQuery query = session.createSQLQuery(sql);

 

最后的重点是如何将查询结果映射到pojo:

SQLQuery接口addScalar方法，可用于设置查询结果到Pojo的映射

 

SQLQuery addScalar(String columnAlias, Type type) 

-- 参数中的Type为Hibernate中提供的数据库类型.

 

如何使用见如下代码：

 

 

Java代码  

1. public List<WorkStations> getWorkStations() {   
2.     List<WorkStations> retList = this.getHibernateTemplate().executeFind(new HibernateCallback() {   
3.   
4.         @Override  
5.         public Object doInHibernate(Session session)   
6.                 throws HibernateException, SQLException {   
7.                
8.             StringBuffer b = new StringBuffer()   
9.                 .append("select d.guidID as deviceId, d.strName as deviceName, ")   
10.                     .append("p.guidID as portId, p.strName as portName, ")   
11.                     .append("t.guidTaskID as taskId, t.strName as taskName, t.enumRunStatus as runStatus, ")   
12.                     .append("t.odtTaskStartDate as taskStartDate, t.odtTaskStopDate as taskStopDate ")   
13.                 .append("from ULCDevice as d ")   
14.                     .append("inner join ULCDevicePort as p on p.guidBelongDeviceID = d.guidID ")   
15.                     .append("left outer join ULCTaskInfo as t on t.guidRecordPortID = p.guidID and t.enumRunStatus = 2 ")   
16.                 .append("where d.enumType = 3 ")   
17.                 .append("order by d.guidID");   
18.                
19.             String sql = b.toString();   
20.                
21.             SQLQuery query = session.createSQLQuery(sql);   
22.             query.addScalar("deviceId", Hibernate.STRING);   
23.             query.addScalar("deviceName", Hibernate.STRING);   
24.                
25.             query.addScalar("portId", Hibernate.STRING);   
26.             query.addScalar("portName", Hibernate.STRING);   
27.                
28.             query.addScalar("taskId", Hibernate.STRING);   
29.             query.addScalar("taskName", Hibernate.STRING);   
30.             query.addScalar("runStatus", Hibernate.INTEGER);   
31.                
32.             query.addScalar("taskStartDate", Hibernate.TIMESTAMP);   
33.             query.addScalar("taskStopDate", Hibernate.TIMESTAMP);   
34.                
35.             query.setResultTransformer(Transformers.aliasToBean(WorkStations.class));   
36.                
37.             List<WorkStations> list = query.list();   
38.             System.out.println(list.size());   
39.                
40.             return list;   
41.         }   
42.            
43.     });   
44.        
45.     return retList;   
46. }  

public List<WorkStations> getWorkStations() {List<WorkStations> retList = this.getHibernateTemplate().executeFind(new HibernateCallback() {@Overridepublic Object doInHibernate(Session session)throws HibernateException, SQLException {StringBuffer b = new StringBuffer().append("select d.guidID as deviceId, d.strName as deviceName, ").append("p.guidID as portId, p.strName as portName, ").append("t.guidTaskID as taskId, t.strName as taskName, t.enumRunStatus as runStatus, ").append("t.odtTaskStartDate as taskStartDate, t.odtTaskStopDate as taskStopDate ").append("from ULCDevice as d ").append("inner join ULCDevicePort as p on p.guidBelongDeviceID = d.guidID ").append("left outer join ULCTaskInfo as t on t.guidRecordPortID = p.guidID and t.enumRunStatus = 2 ").append("where d.enumType = 3 ").append("order by d.guidID");String sql = b.toString();SQLQuery query = session.createSQLQuery(sql);query.addScalar("deviceId", Hibernate.STRING);query.addScalar("deviceName", Hibernate.STRING);query.addScalar("portId", Hibernate.STRING);query.addScalar("portName", Hibernate.STRING);query.addScalar("taskId", Hibernate.STRING);query.addScalar("taskName", Hibernate.STRING);query.addScalar("runStatus", Hibernate.INTEGER);query.addScalar("taskStartDate", Hibernate.TIMESTAMP);query.addScalar("taskStopDate", Hibernate.TIMESTAMP);query.setResultTransformer(Transformers.aliasToBean(WorkStations.class));List<WorkStations> list = query.list();System.out.println(list.size());return list;}});return retList;}

 

 

WorkStations.java

 

 

Java代码  

1. public class WorkStations implements Serializable {   
2.     private String deviceId = null;   
3.     private String deviceName = null;   
4.        
5.     private String portId = null;   
6.     private String portName = null;   
7.        
8.     private String taskId = null;   
9.     private String taskName = null;   
10.     private Integer runStatus = null;   
11.        
12.     private Date taskStartDate = null;   
13.     private Date taskStopDate = null;   
14.        
15.     public WorkStations() {   
16.            
17.     }   
18.   
19.         //... 省略 getter/sette r方法   
20. }