Hibernate运用纯sql进行查询，更新

参考文章：http://jihongbin12329.iteye.com/blog/88678

?

以角色表为例子

?

数据库表Role中(Role_Id, RoleDescription, RoleName)三个字段

?

RoleDAOImpl.java

?

public List<Object[]> getRoles(){	return getHibernateTemplate().executeFind(new HibernateCallback(){		public Object doInHibernate(Session session)				throws HibernateException, SQLException {						Query q = session.createSQLQuery("select Role_Id, RoleDescription, RoleName from dbo.Role");						return q.list();		}				});}

测试：

?

//测试public static RoleDAOImpl getFromApplicationContext(ApplicationContext ctx) {	return (RoleDAOImpl) ctx.getBean("roleDAO");}public static void main(String args[]){	RoleDAOImpl dao = getFromApplicationContext(new ClassPathXmlApplicationContext("spring/applicationContext*.xml"));	List<Object[]> roles = dao.getRoles();	for(Object[] role : roles){		System.out.println(role[0]+"--"+role[1]+"--"+role[2]);	}			}

?

控制台打印结果：

Hibernate: select Role_Id, RoleDescription, RoleName from dbo.Role1--超级管理员--ROLE_ADMIN2--维修人员--ROLE_MAINTENANCE3--客服人员--ROLE_SERVICER4--车辆用户--ROLE_DRIVER

?PS：

?????? 需要注意的是：如果是insert,update delete操作的时候需要队session进行flush和clear，不然控制台打印执行语句了，但是数据库中的数据不会改变。

??????如：

????

sql = "UPDATE [dbo].[ALARM_PROCESS] SET LONGITUDE="+jws[0]+", LATITUDE="+jws[1]+", SPEED="+jws[2]+", LOCATION_DESC='"+locationDesc+"', CHULI_TAG=1 WHERE VEHICLE_NUM='"+plateNumberAndDate[0]+"' AND ALARM_DATE='"+plateNumberAndDate[1]+"'";Query q = session.createSQLQuery(sql);q.executeUpdate();if(a % 50 == 0){	session.flush();	session.clear();}

???????

?session.flush()是拿数据库与缓存中的数据同步
?当session.clear()后才会引起缓存数据变化

?

??????? 重点：1）createSQLQuery

??????????????????? 2）查询多个字段值返回List<Object[]> 或 Object[]

????????????????????????? 查询一个字段值返回List<String> 或 String

今天测试了下不行啊session.flush()和session.clear()????

?

还是用下面的可以

?

Session session = getSession();Transaction t = session.beginTransaction(); t.begin();try {	sql = "UPDATE [ALARM_PROCESS] .......";					session.createSQLQuery(sql).executeUpdate();	t.commit();} catch (HibernateException e) {	t.rollback();} finally{	session.close();}

?

?

?

public List<String> getPlateNumbersByVehicleTeamNum(final String vehicleTeamNum){		return getHibernateTemplate().executeFind(new HibernateCallback(){			@Override			public Object doInHibernate(Session session)					throws HibernateException, SQLException {				StringBuffer sb = new StringBuffer();				sb.append("SELECT VEHICLE_NUM FROM VEHICLE WHERE VEHICLETEAM_NUM='").append(vehicleTeamNum).append("' AND RECSTATUS='Y'");				Query q = session.createSQLQuery(sb.toString());				return q.list();			}});	}

public List<String> getPlateNumbersByVehicleTeamNum(final String vehicleTeamNum){		return getHibernateTemplate().executeFind(new HibernateCallback(){			@Override			public Object doInHibernate(Session session)					throws HibernateException, SQLException {				StringBuffer sb = new StringBuffer();				sb.append("SELECT VEHICLE_NUM FROM VEHICLE WHERE VEHICLETEAM_NUM='").append(vehicleTeamNum).append("' AND RECSTATUS='Y'");				Query q = session.createSQLQuery(sb.toString());				return q.list();			}});	}