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

        一般在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();
            }
        }
    }
}

评论

4 楼 qq672076266 2013-03-29  

有没有尝试过取得结果集rs的大小

3 楼 xiangtui 2010-05-21  

太感谢了~~
刚开始没有用你的自定义类型，java执行下面的语句总是报错，
rs = (ResultSet) proc.getObject(1); 
说是索引序号不正
后来换成自定义类型后，就好了~~~

2 楼 lht200251206 2006-12-30  

对JAVA的学习得继续努力

1 楼 lht200251206 2006-12-28  

测试成功,以后多些交流