java 通过调用存储过程获取结果集

转自 http://wilent.iteye.com/blog/40704

        一般在java中,数据查询是通过Statement, PreparedStatement获取结果集,今天向大家介绍通过CallableStatement调用存储过程,从而获取结果集.
       本文是所用的数据库为oracle.
      一.  测试数据库表:

sql 代码

create
 
table
 wilent_user(
                id number(5) primary
 
key
,
                name
 varchar2(100),
                sex varchar2(1),    --Y为男,F为女;
                group_id number(5),
                teach varchar2(50)    --学历;
                );

                create
 
table
 wilent_group(
                    id number(5) primary
 
key
,
                    name
 varchar2(100)
                 );

                        insert
 
into
 wilent_group 
values
(1,'组1');
        insert
 
into
 wilent_group 
values
(2,'组2');
        insert
 
into
 wilent_group 
values
(3,'组3');
        insert
 
into
 wilent_group 
values
(4,'组4');
        insert
 
into
 wilent_group 
values
(5,'组5');

        insert
 
into
 wilent_user 
values
(1,'吴','Y',1,'大专');
        insert
 
into
 wilent_user 
values
(2,'李','Y',1,'大专');
        insert
 
into
 wilent_user 
values
(3,'赵','N',2,'本科');
        insert
 
into
 wilent_user 
values
(4,'金','Y',2,'高中');
        insert
 
into
 wilent_user 
values
(5,'钱','N',2,'大专');
        insert
 
into
 wilent_user 
values
(6,'孙','N',1,'大专');
        insert
 
into
 wilent_user 
values
(7,'高','Y',3,'本科');
        insert
 
into
 wilent_user 
values
(8,'宋','N',3,'高中');
        insert
 
into
 wilent_user 
values
(9,'伍','Y',3,'大专');
        insert
 
into
 wilent_user 
values
(10,'欧','Y',4,'本科');
        insert
 
into
 wilent_user 
values
(11,'庄','N',4,'硕士');
        insert
 
into
 wilent_user 
values
(12,'纪','Y',4,'本科');
        insert
 
into
 wilent_user 
values
(13,'陈','Y',5,'大专');
        insert
 
into
 wilent_user 
values
(14,'龙','N',5,'大专');
        insert
 
into
 wilent_user 
values
(15,'袁','Y',5,'高中');
        insert
 
into
 wilent_user 
values
(16,'杨','Y',1,'本科');
        insert
 
into
 wilent_user 
values
(17,'江','N',1,'大专');
        insert
 
into
 wilent_user 
values
(18,'刘','Y',1,'硕士');
        insert
 
into
 wilent_user 
values
(19,'郭','N',3,'硕士');
        insert
 
into
 wilent_user 
values
(20,'张','Y',3,'大专');
        insert
 
into
 wilent_user 
values
(21,'文','N',3,'硕士');
        insert
 
into
 wilent_user 
values
(22,'李','N',4,'大专');
        insert
 
into
 wilent_user 
values
(23,'梅','Y',4,'本科');
        insert
 
into
 wilent_user 
values
(24,'王','N',4,'大专');
        insert
 
into
 wilent_user 
values
(25,'吕','N',5,'高中');
        insert
 
into
 wilent_user 
values
(26,'范','Y',5,'本科');
        insert
 
into
 wilent_user 
values
(27,'许','N',1,'大专');
        insert
 
into
 wilent_user 
values
(28,'墨','Y',1,'高中');
        insert
 
into
 wilent_user 
values
(29,'孔','N',1,'本科');
        insert
 
into
 wilent_user 
values
(30,'蔡','Y',1,'大专');

       二.  oracle 存储过程

            

sql 代码

--自定义类型;
Create
 
Or
 
Replace
 Type wilent_row_table 
As
 Object
(
       group_name Varchar2(100),
       group_count Number(4),
       male_count Number(4),
       woman_count Number(4),
       da_count Number(4),
       ben_count Number(4)
);
/

--定义一个嵌套表类型;
Create
 
Or
 
Replace
 Type wilent_tab_type 
Is
 
Table
 
Of
 wilent_row_table;
/
--返回一个游标类型;
Create
 
Or
 
Replace
 Package wilent_types 
As
       Type cursor_type Is
 Ref 
Cursor
;
End
 wilent_types;
/
Create
 
Or
 
Replace
 
Procedure
 wilent_group_count(recordSet 
Out
 wilent_types.cursor_type)
As
  v_tab wilent_tab_type := wilent_tab_type();
  index_max Number(4);                         --wilent_group最大的id;
  index_min Number(4);                         --wilent_group最小的id;
  index_for Number(4);

  group_name Varchar2(100);
  user_count Number(4);
  male_count Number(4);
  woman_count Number(4);
  da_count Number(4);
  ben_count Number(4);
Begin
     dbms_output.put_line('as
');
     Select
 
Max
(g.Id) 
Into
 index_max 
From
 wilent_group g;
     --dbms_output.put_line(index_max);
     Select
 
Min
(g.Id) 
Into
 index_min 
From
 wilent_group g;
     --dbms_output.put_line(index_min);
     For
 index_for 
In
 Index_min..index_max Loop
         --添加新记录;
         v_tab.Extend;
         Select
 
Name
 
Into
 group_name 
From
 wilent_group 
Where
 Id=index_for;
         Select
 
Count
(*) 
Into
 user_count 
From
 wilent_user u, wilent_group g 
Where
 u.group_id=g.Id 
And
 g.Id=index_for;
         Select
 
Count
(*) 
Into
 male_count 
From
 wilent_user u, wilent_group g 
Where
 u.group_id=g.Id 
And
 g.Id=index_for 
And
 sex='Y';
         Select
 
Count
(*) 
Into
 woman_count 
From
 wilent_user u, wilent_group g 
Where
 u.group_id=g.Id 
And
 g.Id=index_for 
And
 sex='N';
         Select
 
Count
(*) 
Into
 da_count 
From
 wilent_user u, wilent_group g 
Where
 u.group_id=g.Id 
And
 g.Id=index_for 
And
 teach='大专';
         Select
 
Count
(*) 
Into
 ben_count 
From
 wilent_user u, wilent_group g 
Where
 u.group_id=g.Id 
And
 g.Id=index_for 
And
 teach='本科';
         --把记录写入;
         v_tab(v_tab.Last
) := wilent_row_table(group_name,user_count,male_count,woman_count,da_count,ben_count);
     End
 Loop;

     --把记录放在游标里;
     Open
 recordset 
For
      --Table(Cast(v_tab As wilent_tab_type))目的是把v_tab强转为wilent_tab_type表
          Select
 group_name,group_count ,male_count ,woman_count ,da_count ,ben_count  
From
 
Table
(
Cast
(v_tab 
As
 wilent_tab_type)) 
Order
 
By
 group_name;
End
 wilent_group_count;
/

--测试wilent_group_count();
declare
  recordset wilent_types.cursor_type;
Begin
    wilent_group_count(recordset);
End
;

     
          三. java代码:

          

java 代码

package
 com.wilent.oracle;

import
 java.sql.CallableStatement;
import
 java.sql.Connection;
import
 java.sql.ResultSet;
import
 java.sql.SQLException;

import
 oracle.jdbc.driver.OracleTypes;

import
 com.wilent.db.ConnectionManager;

public
 
class
 TestProcedure {
    public
 
static
 
void
 main(String[] args) {
        //获得conn连接,读者可以自行写;
        Connection conn = ConnectionManager.getConnection();
        ResultSet rs = null
;
        try
 {
            CallableStatement proc = conn.prepareCall("{call wilent_group_count(?)}"
);
            proc.registerOutParameter(1
, OracleTypes.CURSOR);
            proc.execute();

            rs = (ResultSet) proc.getObject(1
);
            System.out.println("组名\t总计\t男性\t女性\t大专\t本科"
);
            while
(rs.next())
            {
                StringBuffer buffer = new
 StringBuffer();
                buffer.append(rs.getString("group_name"
));
                buffer.append("\t"
);
                buffer.append(rs.getInt("group_count"
));
                buffer.append("\t"
);
                buffer.append(rs.getInt("male_count"
));
                buffer.append("\t"
);
                buffer.append(rs.getInt("woman_count"
));
                buffer.append("\t"
);
                buffer.append(rs.getInt("da_count"
));
                buffer.append("\t"
);
                buffer.append(rs.getInt("ben_count"
));
                System.out.println(buffer.toString());
            }
        } catch
 (Exception e) {
            e.printStackTrace();
        }
        finally
{
            try
 {
                conn.close();
            } catch
 (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}