[导入]Hibernate 中 执行 原生 SQL 语句

(一)

开门见山.今天我要说的是不用HQL执行SAVE和DELETE方法,用hibernate的executeQuery来执行SQL

其原理如下(从SessionFactory里获得个Session，在调用session的connection方法,通过Statement来执行静态SQL,最后执行executeQuery就可以了)具体如下:

protected Session session = null; protected Transaction tr = null;

String sql = "insert into as_dept2role(roleid,dept_id)value('"+roleId+"','"+deptId+"')"; session=HibernateSessionFactory.getSession();                                                                               

   session.beginTransaction();
//获取connection,执行静态SQL
Statement state = session.connection().createStatement();
state.executeQuery(sql);
tr.commit(); session.close();

当然关于 关闭SESSION 这些方法我写的简单些,主要是为了写 执行SQL这些方法

对于删除只要写个删除语句就可以了

:Transaction tr = session.beginTransaction();

* session.connection() 方法过时 用下面来代替 *
DataSource ds= SessionFactoryUtils.getDataSource(getSessionFactory());
conn=ds.getConnection();

============================================

（二）

public Object get(Class cls, String szId) {
<wbr><wbr>Object obj = this.getHibernateTemplate().get(cls, szId);<br><wbr><wbr>return obj;<br><wbr>}<br><wbr><wbr>obj.getsessionFaction.opensession返回session</wbr></wbr></wbr></wbr></wbr></wbr></wbr>

<wbr>Session session = <wbr>dao.openSession();<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>Connection conn = session.connection();<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>List recordList = new ArrayList();<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>StringBuffer sql = new StringBuffer();<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql.append("select b.user_name, c.org_name ");<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql.append("from orgmeetinglinkman a, am_user b, organization c ");<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql.append("where a.login_name = b.login_name ");<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>sql.append("and a.org_id = c.org_id ");<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>PreparedStatement ps = conn.prepareStatement(sql.toString());<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr>ResultSet rs = ps.execu</wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>

============================================

(三)

public List findWithSQL(final String sql) {
   List list = (List) this.getHibernateTemplate().execute(
     new HibernateCallback() {
      public Object doInHibernate(Session session)
        throws SQLException, HibernateException {
       SQLQuery query = session.createSQLQuery(sql);
       query.addScalar("NX",new org.hibernate.type.StringType());
       List children = query.list();
       return children;
      }
     });
   return list;
}

/**
* 查询并返回结果集,结果集中的内容已经都转为了字符串
*/
public List<List<String>> findSql(final String sql) {
   // TODO Auto-generated method stub
   System.out.println("findSql---sql1----->"+sql);
   List<List<String>> mainObjList= (List<List<String>>) getHibernateTemplate().execute(new HibernateCallback() {

    public Object doInHibernate(Session session)
      throws HibernateException, SQLException {

    
     int n=-1;
     Connection con=null;
     PreparedStatement stmt=null;
     ResultSet rs=null;
    
     try
     {
     
      DataSource ds= SessionFactoryUtils.getDataSource(getSessionFactory());
      if(ds==null)
      {
       throw new SQLException("get dataSource is null");
      }
      con=ds.getConnection();
      System.out.println("findSql---sql2----->"+sql);
      stmt=con.prepareStatement(sql);
      rs=stmt.executeQuery();
     
     } catch (HibernateException e)
     {
      // TODO Auto-generated catch block
      e.printStackTrace();
     } catch (SQLException e)
     {
      // TODO Auto-generated catch block
      e.printStackTrace();
     }
    
     List<List<String>> list=new ArrayList<List<String>>();//每行为一个list
     try
     {
      ResultSetMetaData rsmd=rs.getMetaData();
      int colsNum=rsmd.getColumnCount();//取得列数
      int rsNum=0;
      while(rs.next())
      {
       rsNum++;
       System.out.println("rsNum==="+rsNum+" ");
       List<String> subList=new ArrayList<String> ();//每列的类型为string
       for(int i=1;i<=colsNum;i++)
       {
        System.out.println("\ti==="+i);
        //int type= rsmd.getColumnType(i);
        String columnType=getDataType(rsmd.getColumnType(i),rsmd.getScale(i));  
        String val="";
        if(columnType.equalsIgnoreCase("Date"))
        {
         Timestamp timest= rs.getTimestamp(i);
         if(timest!=null)
         {
          long times=timest.getTime();
          Date date=new Date(times);
          SimpleDateFormat df=new SimpleDateFormat("yyyy-MM-dd HH:mm:ss",Locale.CHINA);
          val=df.format(date);
         }
        }
        else if(columnType.equalsIgnoreCase("Number"))
        {
         Object obj=rs.getObject(i);
         if(obj!=null)
         {
          int m=rs.getInt(i);
          val=Integer.toString(m);
         }
        }
        else if(columnType.equalsIgnoreCase("blob"))
        {
         val="不支持blob数据的读取";
        }
        else if(columnType.equalsIgnoreCase("clob"))
        {
         val=getOracleClobField(rs, i);
        
        }
        else
        {
         val=rs.getString(i);
        }
        if(val==null)
        {
         val="";
        }
        subList.add(val);
       }
       if(subList.size()>0)
       {
        list.add(subList);
       }
      }
     }
     catch(Exception ex5)
     {
      ex5.printStackTrace();
      System.out.println("ex5.getMessage="+ex5.getMessage());
      list=null;
     }
     finally
     {
      try {
       if (rs != null) {
        rs.close();
        rs = null;
       }
      } catch (Exception e2) {
       // TODO: handle exception
       e2.printStackTrace();
       System.out.println("e2.getMessage="+e2.getMessage());
      }
      try {
       if (stmt != null) {
        stmt.close();
        stmt = null;
       }
      } catch (Exception e3) {
       // TODO: handle exception
       e3.printStackTrace();
       System.out.println("e3.getMessage="+e3.getMessage());
      }
      try {
       if (con != null) {
        con.close();
        con = null;
       }
      } catch (Exception e4) {
       // TODO: handle exception
       e4.printStackTrace();
       System.out.println("e4.getMessage="+e4.getMessage());
      }
     
     }
     return list;
    }

   });
   return mainObjList;
}

// String   columnType=getDataType(rmd.getColumnType(i),rmd.getScale(i));  
private String getOracleClobField(ResultSet rset, int index)
     throws Exception
{
     StringBuffer buffS = new StringBuffer();
     Clob clob = rset.getClob(index + 1);
     if(clob == null)
         return " ";
     Reader reader = clob.getCharacterStream();
     char buff[] = new char[1024];
     for(int len = 0; (len = reader.read(buff)) != -1;)
         buffS.append(buff, 0, len);
      return buffS.toString();
}
    
private   static   String   getDataType(int   type,int   scale)
{  
   String   dataType="";  

   switch(type){  
    case   Types.LONGVARCHAR:   //-1  
     dataType="Long";  
      break;  
    case   Types.CHAR:         //1  
     dataType="Character";  
          break;  
    case   Types.NUMERIC:   //2  
     switch(scale)
     {  
         case   0:  
             dataType="Number";  
             break;  
         case   -127:  
             dataType="Float";  
             break;  
         default:  
             dataType="Number";  
     }  
     break;  
    case   Types.VARCHAR:     //12  
     dataType="String";  
        break;  
    case   Types.DATE:     //91  
     dataType="Date";  
        break;  
    case   Types.TIMESTAMP:   //93  
     dataType="Date";  
        break;  
    case   Types.BLOB   :  
        dataType="Blob";  
        break;  
    default:  
        dataType="String";  
    }  
    return   dataType;
}

文章来源: http://blog.163.com/ccbobo_cat/blog/static/32099462200931511221288