java调用mysql存储

--命令行创建存储
DELIMITER //
create procedure test()
begin
select * from student4;
end
//

--命令行调用存储
call test();

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/**
*获取数据库连接的类
*/
class ConnectDb {

public static Connection getConnection(){
Connection conn = null;
PreparedStatement preparedstatement = null;
try {
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
String dbname = "test";
String url ="jdbc:mysql://localhost/"+dbname+"?user=root&password=root&useUnicode=true&characterEncoding=8859_1" ;
conn= DriverManager.getConnection(url);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}

}

import java.sql.*;
/**
* 调用带有输入参数的存储过程，三种情况例子
*/
public class ProcedureTest {

public static void main(String[] args) {
//callIn(111);
//callOut();
callResult();
}

/**
* 1.调用带有输入参数的存储过程
* @param in stored procedure input parameter value
*/
public static void callIn(int in){
//获取连接
Connection conn = ConnectDb.getConnection();
CallableStatement cs = null;
try {
//可以直接传入参数
//cs = conn.prepareCall("{call sp1(1)}");

//也可以用问号代替
cs = conn.prepareCall("{call sp1(?)}");
//设置第一个输入参数的值为110
cs.setInt(1, in);

cs.execute();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(cs != null){
cs.close();
}
if(conn != null){
conn.close();
}
} catch (Exception ex) {
ex.printStackTrace();
}
}

}

/**
* 2.调用带有输出参数的存储过程
*
*/
public static void callOut() {
Connection conn = ConnectDb.getConnection();
CallableStatement cs = null;
try {
cs = conn.prepareCall("{call sp2(?)}");
//第一个参数的类型为Int
cs.registerOutParameter(1, Types.INTEGER);
cs.execute();

//得到第一个值
int i = cs.getInt(1);
System.out.println(i);
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(cs != null){
cs.close();
}
if(conn != null){
conn.close();
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}

/**
* 3.调用输出结果集的存储过程
*/
public static void callResult(){
Connection conn = ConnectDb.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
cs = conn.prepareCall("{call sp6()}");
rs = cs.executeQuery();

//循环输出结果
while(rs.next()){
System.out.println(rs.getString(1));
}
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(rs != null){
rs.close();
}
if(cs != null){
cs.close();
}
if(conn != null){
conn.close();
}
} catch (Exception ex) {
ex.printStackTrace();
}
}
}

}


写好存储过程后,在JAVA里可以call了.
java.sql.CallableStatement sp=con.prepareCall("{call ss.get_hotline(?)}");
//用CallableStatement对象调用存储过程
sp.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);
//注册存储过程的输出参数,oracle.jdbc.OracleTypes.CURSOR也可以写成-10
sp.execute(); //执行存储过程
java.sql.ResultSet rs=(java.sql.ResultSet)sp.getObject(1); //获取返回的对象,再将对象转为记录集
while(rs.next()){
Syste
在oracle中,在package下的存储过程带上包名可直接执行.
在JAVA中也一样,call的时候加个包名即可获得句柄.
但在返回游标中的procedure时切记不能直接返回ResultSet,虚拟机会将返回的参数做为一个对象集合来处理,它不会将游标做为记录集来处理.只有将对象转化成记录集时才可以直接取数据.m.out.println(rs.getString(1));
}


存储过程结合游标：
1、查看某个订单的合计
DELIMITER //
drop procedure if exists ordertotal;
CREATE PROCEDURE ordertotal
(in onumber int,
in taxable boolean,
out ototal decimal(8,2)
)
begin
declare total decimal(8,2);
declare taxrate int default 6;

select sum(item_price*quantity) from orderitems where order_num=onumber
into total;

if taxable then
select total+(total/100*taxrate) into total;
end if;
select total into ototal;
end;//
2、其中创建一个表，调用1中的存储过程，把结果写入到表中。
drop procedure if exists processorders;
create procedure processorders()
begin
declare done boolean default 0;
declare o int;
declare t decimal(8,2);

declare ordernumbers cursor
for
select order_num from orders;
declare continue handler for sqlstate '02000' set done=1;
create table if not exists ordertotals(order_num int, total decimal(8,2));

open ordernumbers ;

repeat

fetch ordernumbers into o;
call ordertotal (o,1,t);
insert into ordertotals(order_num,total)
values(o,t);
until done end repeat;
close ordernumbers;
end;//

mysql> call processorders//
mysql> select * from ordertotals;//
+-----------+---------+
| order_num | total |
+-----------+---------+
| 20005 | 158.86 |
| 20009 | 40.78 |
| 20006 | 58.30 |
| 20007 | 1060.00 |
| 20008 | 132.50 |
| 20008 | 132.50 |
+-----------+---------+
6 rows in set (0.00 sec)
mysql> DELIMITER ;
存储过程综合实例二：（包含事务,参数,嵌套调用,游标,循环等）
drop procedure if exists pro_rep_shadow_rs;
delimiter |
----------------------------------
-- rep_shadow_rs
-- 用来处理信息的增加，更新和删除
-- 每次只更新上次以来没有做过的数据
-- 根据不同的标志位
-- 需要一个输出的参数，
-- 如果返回为0，则调用失败，事务回滚
-- 如果返回为1，调用成功，事务提交
--
-- 测试方法
-- call pro_rep_shadow_rs(@rtn);
-- select @rtn;
----------------------------------
create procedure pro_rep_shadow_rs(out rtn int)
begin
-- 声明变量，所有的声明必须在非声明的语句前面
declare iLast_rep_sync_id int default -1;
declare iMax_rep_sync_id int default -1;
-- 如果出现异常，或自动处理并rollback,但不再通知调用方了
-- 如果希望应用获得异常，需要将下面这一句，以及启动事务和提交事务的语句全部去掉
declare exit handler for sqlexception rollback;
-- 查找上一次的
select eid into iLast_rep_sync_id from rep_de_proc_log where tbl='rep_shadow_rs';
-- 如果不存在，则增加一行
if iLast_rep_sync_id=-1 then
insert into rep_de_proc_log(rid,eid,tbl) values(0,0,'rep_shadow_rs');
set iLast_rep_sync_id = 0;
end if;

-- 下一个数字
set iLast_rep_sync_id=iLast_rep_sync_id+1;
-- 设置默认的返回值为0：失败
set rtn=0;

-- 启动事务
start transaction;
-- 查找最大编号
select max(rep_sync_id) into iMax_rep_sync_id from rep_shadow_rs;
-- 有新数据
if iMax_rep_sync_id>=iLast_rep_sync_id then
-- 调用
call pro_rep_shadow_rs_do(iLast_rep_sync_id,iMax_rep_sync_id);
-- 更新日志
update rep_de_proc_log set rid=iLast_rep_sync_id,eid=iMax_rep_sync_id where tbl='rep_shadow_rs';
end if;

-- 运行没有异常，提交事务
commit;
-- 设置返回值为1
set rtn=1;
end;
|
delimiter ;
drop procedure if exists pro_rep_shadow_rs_do;
delimiter |
---------------------------------
-- 处理指定编号范围内的数据
-- 需要输入2个参数
-- last_rep_sync_id 是编号的最小值
-- max_rep_sync_id 是编号的最大值
-- 无返回值
---------------------------------
create procedure pro_rep_shadow_rs_do(last_rep_sync_id int, max_rep_sync_id int)
begin
declare iRep_operationtype varchar(1);
declare iRep_status varchar(1);
declare iRep_Sync_id int;
declare iId int;
-- 这个用于处理游标到达最后一行的情况
declare stop int default 0;
-- 声明游标
declare cur cursor for select id,Rep_operationtype,iRep_status,rep_sync_id from rep_shadow_rs where rep_sync_id between last_rep_sync_id and max_rep_sync_id;
-- 声明游标的异常处理，设置一个终止标记
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1;

-- 打开游标
open cur;

-- 读取一行数据到变量
fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;
-- 这个就是判断是否游标已经到达了最后
while stop <> 1 do
-- 各种判断
if iRep_operationtype='I' then
insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;
elseif iRep_operationtype='U' then
begin
if iRep_status='A' then
insert into rs0811 (id,fnbm) select id,fnbm from rep_shadow_rs where rep_sync_id=iRep_sync_id;
elseif iRep_status='B' then
delete from rs0811 where id=iId;
end if;
end;
elseif iRep_operationtype='D' then
delete from rs0811 where id=iId;
end if;

-- 读取下一行的数据
fetch cur into iId,iRep_operationtype,iRep_status,iRep_Sync_id;
end while; -- 循环结束
close cur; -- 关闭游标
end;