使用Hibernate SQLQuery执行原生SQL

civilvar

　　Hibernate对原生SQL查询的支持和控制是通过SQLQuery接口实现的，这种方式弥补了HQL、Criterion查询的不足，在操作和使用上往往更加的自由和灵活，如果使用得当，数据库操作的效率还会得到不同程度的提升。
　　Hibernate对原生 SQL查询的支持和控制是通过SQLQuery接口实现的。通过Session接口，我们能够很方便的创建一个SQLQuery(SQLQuery是一个接口，在Hibernate4.2.2之前，默认返回的是SQLQuery的实现类——SQLQueryImpl对象，在下文中出现的SQLQuery如非注明，都是指该子类)对象来进行原生SQL查询：
　　session.createSQLQuery(String sql);
　　SQLQuery实现了Query接口，因此你可以使用Query接口中提供的API来获取数据。
　　最简单的示例
　　//获取所有查询结果
　　session.createSQLQuery("select * from note").list();
　　//仅获取第一条结果

　　session.createSQLQuery("select * from note where>　　使用预处理SQL
　　预处理SQL的好处自然不必多说，除了众所周知的能够防止SQL注入***外，还能够在一定程度上提高SQL的查询效率。SQLQuery提供了众多的接口来分别设置不同类型的参数，诸如setBigDecimal、setBinary、setDouble等，详参SQLQuery的JavaDoc，此处不再赘述。这里仅重点说一下通用的SQL参数设置接口setParameter。
　　如下代码示范了如何使用SQLQuery执行预处理SQL：

　　SQLQuery query = session.createSQLQuery("select * from note where>　　//设置第一个参数的值为12,即查询ID=12的note
　　query.setParameter(0, 12);
　　List list = query.list();
　　...
　　这里需要注明一点，无论是通过不同类型参数的设置接口来设置SQL参数，还是通过setParameter来设置参数，下标都是从0开始的，而不是从1开始的！
　　使用自定义的结果转换器处理查询结果
　　SQLQuery 接口预留了setResultTransformer接口以实现使用用户自定义的ResultTransformer结果集转换器处理查询结果。 ResultTransformer接口非常简单，只有两个方法，分别用来转换单行数据和所有结果数据。经过自定义ResultTransformer生成的实体，并未加入Session，因此是非受管实体。
　　如下代码，示范了如何将单行数据装入LinkedHashMap对象中:
　　query.setResultTransformer(new ResultTransformer() {
　　@Override
　　public Object transformTuple(Object[] values, String[] columns) {
　　Map map = new LinkedHashMap(1);
　　int i = 0;
　　for(String column : columns){
　　map.put(column, values[i++]);
　　}
　　return map;
　　}
　　@Override
　　public List transformList(List list) {
　　return list;
　　}
　　});
　　如果不设置自定义的ResultTransformer转换器，则Hibernate将每行返回结果的数据按照结果列的顺序装入Object数组中。
　　这里介绍一个工具类：Transformers，它提供了一些常用的转换器，能够帮助我们快速转换结果集，如Transformers.aliasToBean(Note.class)能够将查询结果依别名注入到Note实体中。
　　使用标量
　　使用SQLQuery执行原生SQL时，Hibernate会使用ResultSetMetadata来判定返回的标量值的实际顺序和类型。如果要避免过多的使用ResultSetMetadata,或者只是为了更加明确的指名返回值，可以使用addScalar()。

　　session.createSQLQuery("select * from note where>　　.addScalar("id", LongType.INSTANCE)
　　.addScalar("name", StringType.INSTANCE)
　　.addScalar("createtime", DateType.INSTANCE);
　　这个查询指定了SQL查询字符串,要返回的字段和类型.它仍然会返回Object数组,但是此时不再使用ResultSetMetdata,而是明确的将id,name和 createtime按照Long, String和Date类型从resultset中取出。同时，也指明了就算query是使用*来查询的，可能获得超过列出的这三个字段，也仅仅会返回这三个字段。
　　对全部或者部分的标量值不设置类型信息也是可以的：

　　session.createSQLQuery("select * from note where>　　.addScalar("id")
　　.addScalar("name")
　　.addScalar("createtime", DateType.INSTANCE);
　　没有被指定类型的字段将仍然使用ResultSetMetdata获取其类型。注意，字段不区分大小写，同时不能够指定不存在的字段！
　　关于从ResultSetMetaData返回的java.sql.Types是如何映射到Hibernate类型，是由方言(Dialect)控制的。假若某个指定的类型没有被映射，或者不是你所预期的类型，你可以通过Dialet的registerHibernateType调用自行定义。
　　如果仅指定了一个scalar，那么...

　　Date createTime = (Date)session.createSQLQuery("select * from note where>　　.addScalar("createtime", DateType.INSTANCE)
　　.uniqueResult();
　　如果我们的SQL语句使用了聚合函数，如count、max、min、avg等，且返回结果仅一个字段，那么Hibernate提供的这种提取标量结果的方式就非常便捷了。
　　实体查询
　　上面的查询都是返回标量值的，也就是从resultset中返回的“裸”数据。下面展示如何通过addEntity()让原生查询返回实体对象。

　　session.createSQLQuery("select * from note where>
　　session.createSQLQuery("select>　　这个查询指定SQL查询字符串,要返回的实体。假设Note被映射为拥有id,name和createtime三个字段的类，以上的两个查询都返回一个List，每个元素都是一个Note实体。
　　假若实体在映射时有一个many-to-one的关联指向另外一个实体，在查询时必须也返回那个实体，否则会导致发生一个"column not found"的数据库错误。这些附加的字段可以使用*标注来自动返回，但我们希望还是明确指明，看下面这个具有指向Dog的many-to-one的例子：

　　session.createSQLQuery("select>　　author字段即为Note实体和Author实体的关联字段，只需在查询时得到该字段的值，Hibernate即可使用该值找到对应的关联实体。如上例中，note.getAuthor()即可返回当前Note所属的Author对象。
　　处理关联和集合类
　　通过提前抓取将Author连接获得，而避免初始化proxy带来的额外开销也是可能的。这是通过addJoin()方法进行的，这个方法可以让你将关联或集合连接进来。
　　session.createSQLQuery("select {note.*}, {author.*} from note note, user author where note.author = author.id")
　　.addEntity("note", Note.class)
　　.addJoin("author", "note.author");
　　上面的例子是多对一的关联查询，反过来做一对多的关联查询也是可以的。如下的例子中，author.notes表示该用户发表的所有日记(Note)，Set集合类型：
　　session.createSQLQuery("select {author.*},{note.*} from note note, user author where author.id = ? and note.author = author.id")
　　.addEntity("author", User.class)
　　.addJoin("note", "author.notes");
　　注意：join查询会在每行返回多个实体对象，处理时需要注意。
　　别名和属性引用
　　假若SQL查询连接了多个表，同一个字段名可能在多个表中出现多次，这会导致SQL错误。不过在我们可以通过使用占位符来完美地解决这一问题。
　　其实在上例中已经用到了占位符：
　　session.createSQLQuery("select {note.*}, {author.*} from note note, user author where note.author = author.id")
　　.addEntity("note", Note.class)
　　.addJoin("author", "note.author");
　　这个查询指明SQL查询语句，其中包含占位附来让Hibernate注入字段别名,查询并返回的实体。
　　上面使用的{note.*}和{author.*}标记是作为“所有属性”的简写形式出现的，当然你也可以明确地罗列出字段名。但如下的范例代码中我们让Hibernate来为每个属性注入SQL字段别名，字段别名的占位符是表别名 + . + 属性名。
　　注意:属性名区分大小写，而且不能够在where子句中使用占位符。
　　SQLQuery query = session.createSQLQuery("select note.id as {note.id},note as {note.note},createtime as {note.createTime},author as {note.author}, {author.*} from note, user author where note.id = ? and note.author = author.id");
　　query.addEntity("note", Note.class);
　　query.addJoin("author", "note.author");
　　大多数情况下，上面的别名注入方式可以满足需要，但在使用更加复杂的映射，比如复合属性、通过标识符构造继承树，以及集合类等等情况下，则需要更加复杂的别名注入方式。
　　下表列出了使用别名注射参数的不同方式：
　　别名注入(alias injection names) 描述   语法   示例
　　简单属性   {[aliasname].[propertyname]   A_NAME as {item.name}
　　复合属性   {[aliasname].[componentname].[propertyname]}   CURRENCY as {item.amount.currency}, VALUE as {item.amount.value}
　　实体辨别器   {[aliasname].class}   DISC as {item.class}
　　实体的所有属性   {[aliasname].*}   {item.*}
　　集合键(collection key)   {[aliasname].key}   ORGID as {coll.key}
　　集合id   {[aliasname].id}   EMPID as {coll.id}
　　集合元素   {[aliasname].element}   XID as {coll.element}
　　集合元素的属性   {[aliasname].element.[propertyname]}   NAME as {coll.element.name}
　　集合元素的所有属性   {[aliasname].element.*}   {coll.element.*}
　　集合的所有属性   {[aliasname].*}   {coll.*}
　　在hbm文件中描述结果集映射信息，并在查询中使用
　　对于一些复杂的结果集映射，往往需要像MyBatis那样在文件中手动配置好，然后在程序中使用。幸运的是Hibernate也提供了类似的功能，你可以使用自己配置的结果集映射来处理返回的结果集数据：
　　SQLQuery query = session.createSQLQuery("select note.id as {note.id},note as {note.note},createtime as {note.createTime},author as {note.author}, {author.*} from note, user author where note.id = ? and note.author = author.id");
　　//使用在hbm文件中配置的自定义结果集映射
　　query.setResultSetMapping("noteAnduthor");
　　query.list();
　　执行更新操作
　　使用SQLQuery执行数据库更新操作比较容易，除了像查询时那样需要指定SQL语句（如有需要还需设置SQL参数）外，仅需调用executeUpdate()方法，即可提交更新操作。代码如下所示：
　　session.createSQLQuery("update createtime = ? from note where note.id = ?");
　　query.setDate(0, new Date());
　　query.setLong(1, 1L);
　　query.executeUpdate();
　　executeUpdate方法的返回结果为改变的数据库记录的行数。
　　转载地址： http://www.yshjava.cn/post/543.html
　　在问答里和论坛中，经常看到有人问，怎样将使用本地SQL查询出来的结果映射为值对象的问题，这里就Hibernate中提供的方法做个结论。前提，这里没有使用属性的延迟加载技术。
　　假设有个值对像，如下：
　　Java代码　　　
　　package　test;　
　　public　class　Person　{　
　　private　Long　id;　
　　private　String　name;　
　　private　Long　age;　
　　private　Long　phone;　
　　private　String　address;　
　　}
　　如果查询全部五列记录的话，那么只要
　　Java代码　　
　　List　list　=　getHibernateTemplate().loadAll(Person.class);
　　如果只需要查询id,name,age三列记录呢？那么就要新增构造方法了，
　　Java代码　　　
　　public　Person(Long　id,　String　name,　Long　age)　{　
　　this.id　=　id;　
　　this.name　=　name;　
　　this.age　=　age;　
　　}
　　然后呢，就可以通过HQL来进行查询。
　　Java代码　　
　　List　list　=　getHibernateTemplate().find("select　new　test.Person(id,name,age)　from　Person");
　　这个方法通常可以满足需要了，只是如果，只需要查询id,name,phone三列记录的话，还新增构造方法？不行了，会出现构造方法冲突了。有个办法：
　　Java代码　　
　　List　list　=　getSession().createQuery("select　id,name,phone　from　person")　
　　.addScalar("id",Hibernate.Long).addScalar("name").addScalar("phone",Hibernate.Long)　
　　.addEntity(Person.class);
　　但是，这个只适用于存在Person实体的，如果Hibernate中没有进行Person映射的呢，系统中只存在一个JavaBean。
　　Java代码　　
　　List　list　=　getSession().createSQLQuery("select　id　\"id\",name　\"name\",phone　\"phone\"　from　person")　
　　.addScalar("id",Hibernate.Long).addScalar("name").addScalar("phone",Hibernate.Long)　
　　.setResultTransformer(Transformers.aliasToBean(Person.class)));
　　那么Hibernate就会自动将查出来的三列内容组装到VO对象中去，只是代码量有点大，而且名称都需要重新定义为小写的，在Oracle中查出来的列都默认为大写的（不知道其它数据库怎么样）
　　这个办法就不依赖于构造方法了，只需要定义私有变量，设置getter/setter方法就行了。
　　不过如果更猛点的，根本就没有JavaBean对象可以填充怎么办，Hibernate可以将查出来的列组装到集合类中去。如Map。
　　Java代码　　
　　List　list　=　getSession().createSQLQuery("select　*　from　person")　
　　.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
　　除了这个Transformers还可以将列转化为List。
　　Java代码　　
　　List　list　=　getSession().createSQLQuery("select　*　from　person")　
　　.setResultTransformer(Transformers.T0_LIST);
　　到此，还可以通过继承Transformers将结果映射为其它对象，不累述了，基本功能够用了。
　　----------------------------------------------------------------------------------------------------------
　　String hql ="SELECT apply.productid \"id\",count(apply.productid) \"count\"FROM Applyservice apply GROUP BY productid";
　　Query query  = this.getSession().createSQLQuery(hql)
　　.addScalar("id", Hibernate.STRING)
　　.addScalar("count", Hibernate.INTEGER)
　　.setResultTransformer(Transformers.aliasToBean(SqlObject.class));
　　HQL尽管容易使用，但是在一些复杂的数据操作上功能有限。特别是在实现复杂的报表统计与计算，以及多表连接查询上往往无能为力，这时可以使用SQL（Native SQL）实现HQL无法完成的任务。
　　1、使用SQL查询
　　使用SQL查询可以通过两种方式来实现：
　　（1）、利用Hibernate提供的SQLQuery对象执行。即可以通过Session对象的createSQLQuery()方法获取。如：
　　String sql = "select * from product limit 0,10";
　　SQLQuery query = session.createSQLQuery(sql);
　　List = query.list();
　　注意：查询结果返回的是一个Object的数组。
　　（2）、利用ResultMetaData对象来实现执行SQL语句，但是如果过多地使用这种方法就会对系统的性能产生影响，它将降低运行效率。通过addScalar()方法设置返回数据的类型可以减少ResultSetMetaData对象的使用而提高运行效率。如：
　　String sql = "select * from product limit 0,10";
　　SQLQuery sqlQuery = session.CreateSQLQuery();
　　sqlQuery.addScalar("id",Hibernate.INTEGER);
　　sqlQuery.addScalar("name",Hibernate.STRING);
　　sqlQuery.addScalar("addre",Hibernate.STRING);
　　sqlQuery.addScalar("des",Hibernate.STRING);
　　List list = sqlQuery.list();
　　2、返回SQL查询的持久对象
　　获取SQL查询的持久对象有三种方式实现：
　　方式1：Hibernate不仅能把查询到的记录封装为包含多个Object数组的List对象返回，还可以把每一条查询到的记录封装成持久对象后返回包含这些对象的List对象。如：
　　String sql = "select * from product limit 0,10";
　　SQLQuery sqlQuery = session.CreateSQLQuery();
　　sqlQuery.addEntity(Product .class);
　　List list = sqlQuery.list();
　　方式2：使用大括号指定查询的字段，然后通过SQLQuery对象对象的addEntity()方法关联SQL中的别名和持久化类确定要返回的List对象中的每个元素类型。如：
　　String sql = "select{p.*} from Product p,Category c where p.category_id=c.id";
　　SQLQuery sqlQuery = session.CreateSQLQuery();
　　sqlQuery.addEntity("p",Product.class);
　　List list = sqlQuery.list();
　　方式3：同时使用SQLQuery对象的addScalar()方法和Transformers对象的aliasToBean()方法关联数据库的表与持久化类确定返回记录的各个字段与持久化类属性的对象对应关系。如：
　　String sql = "select p.id,p.name from product p,Category c where p.category_id = c.id";
　　SQLQuery sqlQuery = session.createSQLQuery(sql);
　　sqlQuery.addScalar("id",Hibernate.INTEGER)
　　.addScalar("name",Hibernate.STRING);
　　sqlQuery.setResultTransformer(Transformers.aliasToBean(Product.class));
　　List list = sqlQuery.list();
　　1、首先对 org.hibernate.jdbc.Work 接口封装
　　package com.cms.dao;
　　import java.sql.Connection;
　　import java.sql.ResultSet;
　　import java.sql.SQLException;
　　import java.util.HashMap;
　　import java.util.Map;
　　import org.hibernate.jdbc.Work;

　　public abstract>　　private String proSql; //存储过程语句
　　private ResultSet rs = null; //返回结果集
　　private Map pro = new HashMap(); //存储过程涉及参数
　　@Override
　　public abstract void execute(Connection conn) throws SQLException;
　　public ProWork(String proSql,Object... params){
　　this.proSql = proSql;
　　int i=1;
　　for(Object obj:params){
　　pro.put(i, obj);
　　i++;
　　}
　　}
　　public void setParams(Integer key,Object value){
　　pro.put(key, value);
　　}
　　public String getProSql() {
　　return proSql;
　　}
　　public void setProSql(String proSql) {
　　this.proSql = proSql;
　　}
　　public Object getParams(Integer key){
　　return pro.get(key);
　　}
　　public ResultSet getRs() {
　　return rs;
　　}
　　public void setRs(ResultSet rs) {
　　this.rs = rs;
　　}
　　}
　　2、在 Dao 实现类中方法
　　public ResultSet executeProRs(ProWork work){
　　Session session = sessionFactory.getCurrentSession();
　　session.doWork(work);
　　return work.getRs();
　　}
　　3、在service 层中调用 dao
　　ResultSet rs = gdi.executeProRs(new ProWork("{call ******.*********(?,?)}","2013-12-03"){
　　//新建ProWork 抽象类 ，第一个参数为存储过程调用语句，之后的参数为不定长参数。游标参数不用传入！
　　//实现ProWork 抽象方法。
　　@Override
　　public void execute(Connection conn) throws SQLException {
　　// TODO Auto-generated method stub
　　CallableStatement statement = conn.prepareCall(this.getProSql());
　　//为存储过程设置参数，用this.getParams(Index ) 获取传入参数。
　　statement.setString(1, this.getParams(1).toString());
　　statement.registerOutParameter(2, OracleTypes.CURSOR);
　　statement.execute();
　　//将存储过程返回游标结果赋值给ResultSet.
　　this.setRs((ResultSet) statement.getObject(2));
　　}
　　});

　　* Hibernate,>　　*
　　* Copyright (c) 2007-2011, Red Hat Inc. or third-party contributors as
　　* indicated by the @author tags or express copyright attribution
　　* statements applied by the authors.  All third-party contributions are
　　* distributed under license by Red Hat Inc.
　　*
　　* This copyrighted material is made available to anyone wishing to use, modify,
　　* copy, or redistribute it subject to the terms and conditions of the GNU
　　* Lesser General Public License, as published by the Free Software Foundation.
　　*
　　* This program is distributed in the hope that it will be useful,
　　* but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
　　* or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU Lesser General Public License
　　* for more details.
　　*
　　* You should have received a copy of the GNU Lesser General Public License
　　* along with this distribution; if not, write to:
　　* Free Software Foundation, Inc.
　　* 51 Franklin Street, Fifth Floor
　　* Boston, MA  02110-1301  USA
　　*/
　　package org.hibernate.test.jdbc;
　　import java.sql.Connection;
　　import java.sql.ResultSet;
　　import java.sql.SQLException;
　　import java.sql.Statement;
　　import org.hibernate.JDBCException;
　　import org.hibernate.Session;
　　import org.hibernate.jdbc.ReturningWork;
　　import org.hibernate.jdbc.Work;
　　import org.junit.Test;
　　import org.hibernate.testing.junit4.BaseCoreFunctionalTestCase;
　　import static org.junit.Assert.assertEquals;
　　import static org.junit.Assert.fail;
　　/**
　　* GeneralWorkTest implementation
　　*
　　* @author Steve Ebersole
　　*/

　　public>　　@Override
　　public String getBaseForMappings() {
　　return "org/hibernate/test/jdbc/";
　　}
　　@Override
　　public String[] getMappings() {
　　return new String[] { "Mappings.hbm.xml" };
　　}
　　@Test
　　public void testGeneralUsage() throws Throwable {
　　Session session = openSession();
　　session.beginTransaction();
　　session.doWork(
　　new Work() {
　　public void execute(Connection connection) throws SQLException {

　　// in this current form, users must handle try/catches themselves for proper resource>　　Statement statement = null;
　　try {
　　statement = connection.createStatement();
　　ResultSet resultSet = null;
　　try {
　　resultSet = statement.executeQuery( "select * from T_JDBC_PERSON" );
　　}
　　finally {
　　releaseQuietly( resultSet );
　　}
　　try {
　　resultSet = statement.executeQuery( "select * from T_JDBC_BOAT" );
　　}
　　finally {
　　releaseQuietly( resultSet );
　　}
　　}
　　finally {
　　releaseQuietly( statement );
　　}
　　}
　　}
　　);
　　session.getTransaction().commit();
　　session.close();
　　}
　　@Test
　　public void testSQLExceptionThrowing() {
　　Session session = openSession();
　　session.beginTransaction();
　　try {
　　session.doWork(
　　new Work() {
　　public void execute(Connection connection) throws SQLException {
　　Statement statement = null;
　　try {
　　statement = connection.createStatement();
　　statement.executeQuery( "select * from non_existent" );
　　}
　　finally {
　　releaseQuietly( statement );
　　}
　　}
　　}
　　);
　　fail( "expecting exception" );
　　}
　　catch ( JDBCException expected ) {
　　// expected outcome
　　}
　　session.getTransaction().commit();
　　session.close();
　　}
　　@Test
　　public void testGeneralReturningUsage() throws Throwable {
　　Session session = openSession();
　　session.beginTransaction();
　　Person p = new Person( "Abe", "Lincoln" );
　　session.save( p );
　　session.getTransaction().commit();
　　session = openSession();
　　session.beginTransaction();
　　long count = session.doReturningWork(
　　new ReturningWork() {
　　public Long execute(Connection connection) throws SQLException {

　　// in this current form, users must handle try/catches themselves for proper resource>　　Statement statement = null;
　　long personCount = 0;
　　try {
　　statement = connection.createStatement();
　　ResultSet resultSet = null;
　　try {
　　resultSet = statement.executeQuery( "select count(*) from T_JDBC_PERSON" );
　　resultSet.next();
　　personCount = resultSet.getLong( 1 );
　　assertEquals( 1L, personCount );
　　}
　　finally {
　　releaseQuietly( resultSet );
　　}
　　}
　　finally {
　　releaseQuietly( statement );
　　}
　　return personCount;
　　}
　　}
　　);
　　session.getTransaction().commit();
　　session.close();
　　assertEquals( 1L, count );
　　session = openSession();
　　session.beginTransaction();
　　session.delete( p );
　　session.getTransaction().commit();
　　session.close();
　　}

　　private void>　　if ( statement == null ) {
　　return;
　　}
　　try {
　　statement.close();
　　}
　　catch ( SQLException e ) {
　　// ignore
　　}
　　}

　　private void>　　if ( resultSet == null ) {
　　return;
　　}
　　try {
　　resultSet.close();
　　}
　　catch ( SQLException e ) {
　　// ignore
　　}
　　}
　　}