Mysql stored procedure instance bitsCN.com
Because mysql User-defined functions cannot output two or more results, you need to use the mysql stored procedure.
The following is an example.
SQL statement:
Drop procedure if exists 'testguocheng ';
Create procedure 'testguocheng' (IN 'sql1' varchar (100), IN 'sql2' varchar (100 ))
BEGIN
SET @ SQLStr1 = CONCAT (sql1 );
PREPARE stmt1 from @ SQLStr1;
EXECUTE stmt1;
SET @ SQLStr2 = CONCAT (sql2 );
PREPARE stmt2 from @ SQLStr2;
EXECUTE stmt2;
END;
To explain this stored procedure, two SQL statements are input and the result is two result sets.
The following are the java statements called.
Public static void getData (){
Connection conn = JdbcUtils. getConnection ();
CallableStatement stmt = null;
ResultSet rs = null;
Try {
Stmt = conn. prepareCall ("{call testguocheng ('select * from tsys_user; ', 'SELECT count (*) from tsys_user ;')}");
Boolean flag1_stmt.exe cute ();
Int I = 0;
While (flag ){
System. out. println ("number of result sets" + (++ I ));
Rs = stmt. getResultSet ();
If (I = 1 ){
While (rs. next ()){
System. out. println ("uid:" + rs. getString ("uid") + ", uname:" + rs. getString ("uname "));
}
} Else {
While (rs. next ()){
System. out. println ("Total:" + rs. getString (1 ));
}
}
Flag = stmt. getMoreResults ();
}
} Catch (Exception e ){
System. out. println (e. toString ());
} Finally {
Try {
Stmt. close ();
Conn. close ();
} Catch (Exception ex ){
System. out. println (ex. getMessage ());
}
}
}
BitsCN.com