一般在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 存儲過程
--自定義類型;
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;
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(rs.getint("male_count"));
buffer.append(rs.getint("woman_count"));
buffer.append(rs.getint("da_count"));
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();
}
}
四. 運作結果
組名 總計 男性 女性 大專 大學 組1 10 6 4 6 2 組2 3 1 2 1 1 組3 6 3 3 2 1 組4 6 3 3 2 3 組5 5 3 2 2 1
![Java String.format方法的簡單使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)