mysql call的结果集怎么合并,如何从一个CallableStatement获得多个结果集？

When I call the stored proc from command line I get the following.

CALL `events`.`get_event_by_id`(10)

+---------+----------+-------------+---------------------+---------------------+---------------------+--------+----------+

| evet_id | name | description | starttime | endtime | last_modified | active | addre_id |

+---------+----------+-------------+---------------------+---------------------+---------------------+--------+----------+

| 10 | samole 3 | sanely | 2013-11-27 17:37:00 | 2013-11-27 18:09:00 | 2013-11-27 09:37:42 | 1 | 20 |

+---------+----------+-------------+---------------------+---------------------+---------------------+--------+----------+

1 row in set (0.00 sec)

+---------+------------+---------+

| user_id | username | picture |

+---------+------------+---------+

| 1 | jamess2000 | NULL |

| 2 | yferna2012 | NULL |

+---------+------------+---------+

2 rows in set (0.00 sec)

+----------+------------------------------+---------------------+-------------+--------+

| addre_id | street | name | description | active |

+----------+------------------------------+---------------------+-------------+--------+

| 20 | Schieffelin | Manhattan Ville Loc | NULL | 1 |

+----------+------------------------------+---------------------+-------------+--------+

Here is a snippet of my Java Code

String SP_GET_EVENT_BY_ID = "CALL `events`.`get_event_by_id`(?)";

String PROC_PARAM_EVENT_ID = "evet_id";

mCallableStatement = mConnection.prepareCall(SP_GET_EVENT_BY_ID);

mCallableStatement.setInt(10, PROC_PARAM_EVENT_ID);

When I execute the statement, only the event_table results are returned.

I read the query with the following:

ResultSet reader = mCallableStatement.executeQuery();

while(reader.next())

{

//etc..... here i assign db values to properties.

}

I am trying to avoid making multiple request to the database because it's extremely slow (300 ms depending on on how many results)

Is it even possible?

解决方案

Here is the code from that article.

/**

* MySqlCallMultipleResultSet.java

* Copyright (c) 2007 by Dr. Herong Yang. All rights reserved.

*/

import java.sql.*;

public class MySqlCallMultipleResultSet {

public static void main(String [] args) {

Connection con = null;

try {

com.mysql.jdbc.jdbc2.optional.MysqlDataSource ds

= new com.mysql.jdbc.jdbc2.optional.MysqlDataSource();

ds.setServerName("localhost");

ds.setPortNumber(3306);

ds.setDatabaseName("HerongDB");

ds.setUser("Herong");

ds.setPassword("TopSecret");

con = ds.getConnection();

// Create CallableStatement

CallableStatement cs = con.prepareCall("CALL HeadTail(?)");

// Register OUT parameters

cs.registerOutParameter(1, java.sql.Types.INTEGER);

// Execute the CALL statement and expecting multiple result sets

boolean isResultSet = cs.execute();

// First ReulstSet object

if (!isResultSet) {

System.out.println("The first result is not a ResultSet.");

return;

}

// First ReulstSet object

System.out.println("Head of the table:");

ResultSet res = cs.getResultSet();

while (res.next()) {

System.out.println(" "+res.getInt("ID")

+", "+res.getString("FirstName")

+", "+res.getString("LastName")

+", "+res.getTimestamp("ModTime"));

}

res.close();

// Move to the next result

isResultSet = cs.getMoreResults();

if (!isResultSet) {

System.out.println("The next result is not a ResultSet.");

return;

}

// Second ReulstSet object

System.out.println("Tail of the table:");

res = cs.getResultSet();

while (res.next()) {

System.out.println(" "+res.getInt("ID")

+", "+res.getString("FirstName")

+", "+res.getString("LastName")

+", "+res.getTimestamp("ModTime"));

}

res.close();

// Retrieve OUT parameters

System.out.println("Total number of records: "+cs.getInt(1));

// Close resource

cs.close();

con.close();

} catch (Exception e) {

System.err.println("Exception: "+e.getMessage());

e.printStackTrace();

}

}

}