1：测试数据库表user
mysql>
desc user$$
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
|
id
|
int
(
11
) | NO | PRI | NULL | auto_increment |
| name | varchar(
10
) | YES | | NULL | |
| age |
int
(
11
) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3
rows
in
set (
0.00
sec)
2：存储过程，返回id>n_id 的与id<n_id的两个结果集
delimiter $$
create
procedure
p_get_user_list(
in
n_id
int
)
begin
select
id, name, age
from
user
where
id
>
n_id;
select
id, name, age
from
user
where
id
<
n_id;
end
$$
3：JDBC操作
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package
jdbctest;
import
java.sql.CallableStatement;
import
java.sql.Connection;
import
java.sql.DriverManager;
import
java.sql.ResultSet;
import
java.sql.SQLException;
import
java.util.logging.Level;
import
java.util.logging.Logger;
/**
*
*
@author
y
*/
public
class
Jdbctest {
/**
*
@param
args the command line arguments
*/
public
static
void
main(String[] args) {
//
TODO code application logic here
funtest();
}
public
static
void
funtest(){
Connection conn
=
null
;
CallableStatement calState
=
null
;
ResultSet rs
=
null
;
try
{
Class.forName(
"com.mysql.jdbc.Driver"
);
conn
= (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", ""
);
calState
= conn.prepareCall("{call p_get_user_list(?)}"
);
calState.setInt(
1, 3
);
boolean
oprFlag =
calState.execute();
//
使用外循环来控制结果集的个数，内循环控制每个结果集的记录
while
(oprFlag){
rs
=
calState.getResultSet();
System.out.println(
"================="
);
while
(rs.next()){
System.out.println(
"id:"+rs.getInt("id")+"\t"+
"name:"+rs.getString("name")+"\t"+
"age:" +rs.getInt("age"
));
}
oprFlag
=
calState.getMoreResults();
}
}
catch
(ClassNotFoundException |
SQLException ex) {
Logger.getLogger(Jdbctest.
class
.getName()).log(Level.SEVERE,
null
, ex);
}
finally
{
if
(
null
!=
rs ){
try
{
rs.close();
}
catch
(SQLException ex) {
Logger.getLogger(Jdbctest.
class
.getName()).log(Level.SEVERE,
null
, ex);
}
}
if
(
null
!=
calState){
try
{
calState.close();
}
catch
(SQLException ex) {
Logger.getLogger(Jdbctest.
class
.getName()).log(Level.SEVERE,
null
, ex);
}
}
if
(
null
!=
conn){
try
{
conn.close();
}
catch
(SQLException ex) {
Logger.getLogger(Jdbctest.
class
.getName()).log(Level.SEVERE,
null
, ex);
}
}
}
}
}
4：测试结果
run:
=================
id
:
4
name:test2 age:
30
id
:
5
name:test3 age:
24
=================
id
:
1
name:里斯 age:
25
id
:
2
name:王五 age:
26
成功构建 (总时间:
0
秒)
