Hibernate 调用原生态Sql语句和存储过程

调用原生态Sql语句：

1.

String sqlStr = "select distinct name from mytable where isDeleted = :isDeleteFlag order by name asc";
List<T> = this.getSessionFactory().getCurrentSession().createSQLQuery(sqlStr).
                          addScalar("name", StandardBasicTypes.STRING).setParameter("isDeleteFlag", 0).list();

Double max = (Double) session.createSQLQuery("select max(cat.weight) as maxWeight from cats cat") 
                     .addScalar("maxWeight", Hibernate.DOUBLE).uniqueResult();

用 addScalar 指明输出类型，否则会报错。

调用存储过程：

1.

Connection conn = ((SessionImplementor)getSessionFactory().getCurrentSession()).connection();
String procedure = "{call test_proc(?,?,?)}";
CallableStatement cstmt = conn.prepareCall(procedure);
cstmt.setString(1, value1);
cstmt.setString(2, value2);
cstmt.registerOutParameter(3, OracleTypes.CURSOR);//这里的输出是oracle的游标
cstmt.executeUpdate();
ResultSet rs = (ResultSet)cstmt.getObject(5);

2. 用doWork

session.doWork(new Work(){
        @Override
        public void execute(Connection conn) throws SQLException {
            CallableStatement cs = conn.prepareCall("{call test_proc(?,?,?)}"); 
            cs.registerOutParameter(1, OracleTypes.NUMBER);
            cs.setInt(2, valueint);    
            cs.setDate(3, valuedate);    
            try{
                cs.executeQuery();
                int returnValue =  cs.getInt(1);
            }catch(Exception e){
                e.printStackTrace();
            }finally{
                cs.close();
            }
        }
});

3.

session.getConnetion().createQuery("call XXX");

4.
session.createSQLQuery("{Call 存储过程名(参数)}");