java - Replacing oracle's "OracleTypes.CURSOR" to an equivalent in mysql when using registerOutParameter function -

dear stackoverflow community

i trying upgrade system developed in java using oracle db mysql db.

i got point make use of storaged procedure java code makes use of specific oracle structure - oracletypes.cursor. please see code below.

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();     } {         try {             cs.close();             rs.close();         } catch (sqlexception ex) {             logger.getlogger(dbconnector.class.getname()).log(level.severe, null, ex);         }         closeconnection();         return userid;     } } 

what trying convert use of oracletypes.cursor mysql friendly. did read posssible in newer version of mysql tried replacing oracletypes.cursor java.sql.types.integer without result.

can me?

note: have following previous information here on stack overflow , other places like: http://bugs.mysql.com/bug.php?id=17898

mysql unfortunately doesn't implement ref cursors.

however, stored procedures in mysql can return resultset client, see link: 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, stored functions cannot. if perform ordinary select inside stored procedure, result set returned directly client.

just run ordinary select - , resultset returned client.

resulset similar concept ref cursor in oracle. significant difference in mysql resultset retrieved server client , stored in memory on client side (while oracle cursors retrieves rows in chunks).
however, mysql can simulate behaviour (with limitations), see link: http://dev.mysql.com/doc/connector-j/en/connector-j-reference-implementation-notes.html

resultset
default, resultsets retrieved , stored in memory. in cases efficient way operate, , due design of mysql network protocol easier implement. if working resultsets have large number of rows or large values, , cannot allocate heap space in jvm memory required, can tell driver stream results 1 row @ time.

to enable functionality, create statement instance in 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 forward-only, read-only result set, fetch size of integer.min_value serves signal driver stream result sets row-by-row. after this, result sets created statement retrieved row-by-row.

how works - example.

have oracle procedure want migrate mysql:

create or replace procedure getuserid( p_name varchar2, p_ref out sys_refcursor ) begin   open p_ref      select id, username users      username = p_name; end; / 

in mysql procedure might this:

delimiter %% drop procedure if exists getuserid %% create procedure getuserid( p_name varchar(100) ) begin   select id, username users   username = p_name; end;%% delimiter ; 

and code in java - based on example documentation: 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 );       }