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 存储过程名(参数)}");