时间戳

首页 > 技术文章 > J2EE之oracle、mysql存储过程调用

J2EE之oracle、mysql存储过程调用

wangyong 2013-11-08 15:12 原文

      最近几天在研究hibernate、JPA对存储过程的调用，主要是针对有返回结果集的存储过程的调用方法，个人感觉存储过程是个好东西，虽然说heibernate对数据访问封装的比较不错，再加上他的缓存机制，确实很多情况下足够了，不过存储过程还是有他的用武之地，如果能用他提高性能，为何不用....

      好吧，不讨论他有没有用处，掌握了以后备用也是不错的选择，重点看看oracle的存储过程调用，mysql的要简单的多。

      （一）、oracle存储过程调用

        先在pl/sql中建立一个存储过程，代码如下：

--创建带有游标的包
create or replace package mypackage as
  type mycursor is ref cursor;
end mypackage;

--创建存储过程
create or replace procedure searchUsers(user_cursor out mypackage.mycursor) is
begin
  open user_cursor for select * from wyuser order by id asc;
end;

建立完成以后，如果想测试，可以先看打印出来的结果测试一下，下面是我的测试代码：

--执行存储过程
declare
outcursor mypackage.mycursor;
myid number;
mydate Date;
mypassword varchar2(20);
myname varchar2(20);
begin
   dbms_output.put_line('输出');
   searchUsers(outcursor);
   loop
      fetch outcursor into myid,mydate, mypassword,myname;
      exit when outcursor%notfound;
      dbms_output.put_line(myname||' '||mypassword);
    end loop;
end;

执行后是没有问题的，然后我们重点在如何调用。
   ①hibernate中调用

     在hibernate中调用oracle的存储过程，可能会绕过heibernate，直接用jdbc操作，所以，我一直在找可有什么方法可以得到connection数据库连接，在hibernate3.0之前提供session.connection()方法可以直接获取到连接，不过我用的是hibernate4.0这个方法已经被废弃了，而是用dowork方式代替，下面就看看这种方式下的调用：

    /**
     * 调用存储过程访问数据
     */
    public List<Object[]> searchProc() {
        final List<Object[]> rtnObjs = new ArrayList<Object[]>();
        try {
            Session session = this.sessionFactory.openSession();
            session.beginTransaction();
            Work work = new Work() {
                public void execute(Connection cn) throws SQLException {
                    CallableStatement csmt = cn
                            .prepareCall("{call searchUsers(?)}");
                    csmt.registerOutParameter("user_cursor", OracleTypes.CURSOR);
                    csmt.execute();
                    ResultSet rs = (ResultSet) csmt.getObject("user_cursor");
                    while (rs.next()) {
                        //结果用数组保存，这里只是为了测试，使用定长数组****不通用
                        Object[] objects = new Object[4];
                        objects[0] = rs.getLong("ID");
                        objects[1] = rs.getTimestamp("createtime");
                        objects[2] = rs.getString("password");
                        objects[3] = rs.getString("username");

                        rtnObjs.add(objects);
                    }
                }
            };
            session.doWork(work);
            session.getTransaction().commit();

        } catch (Exception e) {
            System.out.println("DAO层访问存储过程失败");
        }

        return rtnObjs;
    }

返回的结果是一个Object数组集合，这样，就可以在业务层对这个数组进行处理，可以将其实例化为需要的对象等等.....

    ②Spring JDBC访问oracle存储过程

      本来想用JPA访问存储过程的，不过貌似对于有返回值的存储过程JPA处理起来不够强大，故，如法炮制，依然绕到绕过去，用SpringJDBC来处理。存储过程依然是上面使用的那个，下面看看JAVA的调用代码：

    /**
     * Spring JDBC方式调用ORACLE存储过程，返回结果集
     */
    @Override
    public List<Object[]> search() {
        List<Object[]> rtnObjs = new ArrayList<Object[]>();

        rtnObjs = this.jdbcTemplate.execute(new CallableStatementCreator() {

            @Override
            public CallableStatement createCallableStatement(Connection con)
                    throws SQLException {
                CallableStatement cs = con.prepareCall("{call searchUsers(?)}");
                cs.registerOutParameter("user_cursor", OracleTypes.CURSOR);
                return cs;
            }
        }, new CallableStatementCallback<List<Object[]>>() {

            @Override
            public List<Object[]> doInCallableStatement(CallableStatement cs)
                    throws SQLException, DataAccessException {

                cs.execute();
                ResultSet rs = (ResultSet) cs.getObject("user_cursor");
                List<Object[]> objs = new ArrayList<Object[]>();
                while (rs.next()) {
                    Object[] objects = new Object[4];
                    objects[0] = rs.getLong("ID");
                    objects[1] = rs.getTimestamp("createtime");
                    objects[2] = rs.getString("password");
                    objects[3] = rs.getString("username");

                    objs.add(objects);
                }
                return objs;
            }
        });

        return rtnObjs;
    }

翻看了jdbcTemplate中的方法，很多都是回调方法，而且很多大部分都可以可以返回List<Object[]>和List<Map<String,Vlaue>>这种类型，说实话这种结构在实际开发中很有用途，所以在上面的代码中也按照这种方法，返回了List<Object[]>交给业务层可以灵活处理。上面的方法首先继承CallableStatementCreator中创建CallableStatement的方法注册存储过程和定义返回类型，在CallableStatementCallback回调接口中的重写do方法，让之前注册的存储过程执行并得到起返回值，当然也可以自己写两个类分别继承CallableStatementCreator接口Callable- -StatementCallback这两个接口，实现其中的方法，然后在jdbcTemplate的execute中调用.....

 

    （二）、mysql存储过程调用

      相对于oracle的存储过程，mysql的存储过程就简单多了，这里也同样编写一个返回结果集的存储过程，然后利用jdbcTemplate对其进行调用

CREATE DEFINER=`root`@`localhost` PROCEDURE `getuser`()
BEGIN
  select * from wyuser;
END

真是巨简单，看看存储过程的调用是不是也一样简单

/**
     * 调用mysql存储过程
     * 
     * @return
     */
    @Override
    public List<Object[]> getUser() {

        List<Object[]> rtnObjs = new ArrayList<Object[]>();

        rtnObjs = this.getJdbcTemplate().execute("{call getuser()}",
                new CallableStatementCallback<List<Object[]>>() {
                    @Override
                    public List<Object[]> doInCallableStatement(
                            CallableStatement cs) throws SQLException,
                            DataAccessException {
                        List<Object[]> objects = new ArrayList<Object[]>();
                        ResultSet rs = cs.executeQuery();
                        while (rs.next()) {
                            Object[] objArr = new Object[4];
                            objArr[0] = rs.getLong("ID");
                            objArr[1] = rs.getTimestamp("createtime");
                            objArr[2] = rs.getString("password");
                            objArr[3] = rs.getString("username");

                            objects.add(objArr);
                        }
                        return objects;
                    }
                });

        return rtnObjs;

    }

好吧，呵呵，调用当然还是和调用oracle的procedure方式一样，如果是针对有参数的调用也基本上没有一致。

CREATE DEFINER=`root`@`localhost` PROCEDURE `finduser`(in uid int)
BEGIN
       select * from wyuser where id=uid;
END

调用代码：

    /**
     * 调用mysql存储过程，根据ID查找用户
     */
    @Override
    public Object[] findUser(int id) {
        final int uid = id;
        Object[] objArr = new Object[4];
        objArr = this.getJdbcTemplate().execute("{call finduser(?)}",
                new CallableStatementCallback<Object[]>() {

                    @Override
                    public Object[] doInCallableStatement(CallableStatement cs)
                            throws SQLException, DataAccessException {
                        Object[] arr = new Object[4];
                        cs.setInt("uid", uid);  //为存储过程中参数赋值
                        ResultSet rs = (ResultSet) cs.executeQuery();
                        while (rs.next()) {
                            arr[0] = rs.getLong("ID");
                            arr[1] = rs.getTimestamp("createtime");
                            arr[2] = rs.getString("password");
                            arr[3] = rs.getString("username");
                        }

                        return arr;
                    }
                });

        return objArr;
    }

 对于其他的方式，比如有返回值的，可以结合网上的一些案例自己再尝试下。

 至此，调用这两种存储过程的方式都介绍完了，说的比较简单，呵呵，有代码有真相.....

 

推荐阅读