java在使用registerOutParameter函数时将oracle的“OracleTypes.CURSOR”替换为mysql中的等效项

Question

亲爱的stackoverflow社区

我正在尝试将一个用JAVA开发的系统从使用oracle DB升级到mysql DB

我已经到了使用存储过程的地步，java代码使用了特定的oracle结构-OracleTypes.CURSOR。请参阅下面的代码

private int getUserID(String username) {
    int UserID = -1;
    CallableStatement cs = null;
    ResultSet rs = null;
    try {
        con = this.getConnection();
        cs = con.prepareCall("{call getUserID(?,?)}");
        cs.setString(1, username);
        cs.registerOutParameter(2, OracleTypes.CURSOR);
        cs.execute();
        rs = (ResultSet) cs.getObject(2);

        if (rs.next()) {
            UserID = rs.getInt(1);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        try {
            cs.close();
            rs.close();
        } catch (SQLException ex) {
            Logger.getLogger(DBConnector.class.getName()).log(Level.SEVERE, null, ex);
        }
        closeConnection();
        return UserID;
    }
}

我想做的是将OracleTypes.CURSOR的用法转换成mysql友好的用法。 我确实读到，在mysql的较新版本中可以这样做，所以我甚至尝试用java.sql.Types.INTEGER替换OracleTypes.CURSOR，但没有任何结果

你能帮我吗

注意：我在这里有以下关于堆栈溢出和其他地方的信息，如： http://bugs.mysql.com/bug.php?id=17898

Answer 1

不幸的是，MySql没有实现ref cursors

但是，MySql中的存储过程可以将结果集返回给客户端，请参见以下链接：http://dev.mysql.com/doc/refman/5.6/en/faqs-stored-procs.html#qandaitem-B-4-1-14

B.4.14: Can MySQL 5.6 stored routines return result sets?
Stored procedures can, but stored functions cannot. If you perform an ordinary SELECT inside a stored procedure, the result set is returned directly to the client.

只需运行一个普通SELECT，结果集就会返回给客户端

结果集的概念与Oracle中的ref cursor非常相似。最显著的区别是，在MySql中，结果集完全从服务器检索到客户端，并存储在客户端的内存中（而Oracle cursors以块的形式检索行）
但是，MySql可以模拟这种行为（有一些限制），请参见以下链接：http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html

ResultSet
By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate, and due to the design of the MySQL network protocol is easier to implement. If you are working with ResultSets that have a large number of rows or large values, and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.

它是如何工作的——一个例子

假设我们有一个要迁移到MySql的oracle过程：

create or replace procedure getUserId( p_name varchar2, p_ref OUT sys_refcursor )
is
begin
  open p_ref for
     select id, username from users
     where username = p_name;
end;
/

在MySql中，此过程可能如下所示：

delimiter %%
drop procedure if exists getUserId %%
create procedure getUserId( p_name varchar(100) )
begin
  SELECT id, username FROM users
  WHERE username = p_name;
end;%%
delimiter ;

和Java代码-基于文档中的示例：http://dev.mysql.com/doc/connector-j/en/connector-j-usagenotes-statements-callable.html#connector-j-examples-retrieving-results-params

      cs = conn.prepareCall("{call getUserID(?)}");
      cs.setString(1, "user1");
      boolean hasResultSet =  cs.execute();
      if( hasResultSet ){
            rs = cs.getResultSet();
            if (rs.next()) {
                userId = rs.getInt(1);
            }
            System.out.println( "Userid = " + userId );
      }