【分享】hibernate执行原生，指定返回实体类型

浮云若水 2014-06-25 04:32:51

hql相信大家都用的多，但有些时候有hql是不能代替原生SQL的，在开发过程中避免不了要应用到SQLquery执行原生sql，
但用SQLQuery执行原生sql的返回结果集，我们不好控制字段，返回结果集是object[]数组类型，取数时不是那么方便，如果返回的是list<object>,这时候取数就方便多了，直接循环取数就好，不需要字段类型转换之类的操作，其实很简单

			 StringBuffer hql = new StringBuffer( " select dt.alarm_num as alarmNum," +

				 		"dt.alarm_date as alarmDate ," +

				 		"dt.database_sign as databaseSign," +

				 		"dt.alarm_info as alarmInfo," +

				 		"dt.alarm_des as alarmDes ," +

				 		"dt.alarm_type as alarmType ");

				 hql.append("  from databasealarm dt ");

				 hql.append("  left join monitor_log lg ");

				 hql.append("  on dt.alarm_num = lg.log_id ");

				 hql.append("  left join config_alarm cfg ");

				 hql.append("  on lg.monitor_id=cfg.monitor_id ");

				 hql.append("  left join dim_monitor_type tp ");

				 hql.append("  on cfg.monitor_type_id=tp.monitor_type_id ");				 

				 hql.append(" where 1=1 ");

				 if(databasealarmVO!=null){

					 if(databasealarmVO.getStartAlarmDate()!=null){

							 System.out.println(sdf.format(databasealarmVO.getStartAlarmDate()));

						//	hql.append(" and dt.alarm_date >= DATE('").append(sdf.format(databasealarmVO.getStartAlarmDate())).append("') ");

								hql.append(" and dt.alarm_date >=to_date('").append(sdf.format(databasealarmVO.getStartAlarmDate())).append("','yyyy-mm-dd hh24:mi:ss') ");

					 }

					 if(databasealarmVO.getEndAlarmDate()!=null){

							//	hql.append(" and dt.alarm_date <=DATE('").append(sdf.format(databasealarmVO.getEndAlarmDate())).append("') ");

							hql.append(" and dt.alarm_date <=to_date('").append(sdf.format(databasealarmVO.getEndAlarmDate())).append("','yyyy-mm-dd hh24:mi:ss') ");

					 }

					 if(databasealarmVO.getMonitorTypeId()!=null){

						 hql.append(" and  tp.monitor_type_id= ").append(databasealarmVO.getMonitorTypeId()).append(" ");

					 }

					 if(databasealarmVO.getMonitorId()!=null){

						 hql.append(" and cfg.monitor_id= ").append(databasealarmVO.getMonitorId()).append(" ");

					 }

				 }

				 hql.append("order by dt.alarm_date desc ");

上面这是原生sql语句，我们只要在createSQLQuery的时候加上Transformers.aliasToBean(XXX.class)，就是将返回结果进行封装，XXX.class 就是你要封装的类，如下：

		Query query = session.createSQLQuery( hql.toString())

						.addScalar("alarmNum", Hibernate.STRING)

						.addScalar("alarmDate",Hibernate.TIMESTAMP)

						.addScalar("databaseSign", Hibernate.STRING)

						.addScalar("alarmInfo", Hibernate.STRING)

						.addScalar("alarmDes", Hibernate.STRING)

						.addScalar("alarmType", Hibernate.STRING)

						.setResultTransformer(Transformers.aliasToBean(DatabasealarmVO.class));						

				query.setFirstResult(pageList.getFirst());

				query.setMaxResults(pageList.getObjectsPerPage());

				List list = query.list();

addScalar 是显式指定返回数据的类型，这里可以指定你返回字段的类型
还有就是，在select 后面不能直接用*，得在select 后面列出你需要的列和xxx.class中的属性对应

今天在项目中无意中应用到，在这分享给大家

...全文