java—数据存储过程 (54)

9/6/2015来源:Java教程人气:2904

java—数据存储过程 (54)

存储过程:PRocedure:就是一段可执行程序。这个程序运行在数据中。

Begin = {

End = }

If

Else

也可以接收参数。

1 定义一个procedure:

CREATE PROCEDURE proc1()BEGIN  ――{  SELECT * FROM users;END // ―― }DELIMITER ;CALL proc1();

2定义一个接收参数的:

DELIMITER $$CREATE PROCEDURE proc2(IN _id VARCHAR(32),IN _nm VARCHAR(30))BEGIN  INSERT INTO users(id,NAME) VALUES(_id,_nm);END $$CALL proc2('U003','Rose');

3定义一个返回类型的参数:

DELIMITER $$CREATE PROCEDURE proc3(IN _id VARCHAR(32),IN _nm VARCHAR(30),OUT _size INT)BEGIN  INSERT INTO users(id,NAME) VALUES(_id,_nm);  SELECT COUNT(1) INTO _size FROM users;  IF(_size=10) THEN     INSERT INTO users VALUES('U0000','ddd','ddddd');  END IF;END $$CALL proc3('U002882','Rose',@wj);SELECT @wj;

在Java代码中调用Procedure

package cn.hx.demo;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.Types;

import org.junit.Test;

import cn.itcast.utils.DataSourceUtils;

public class ProceDemo {

@Test

public void proc1() throws Exception{

//dbutils不提供调用存储过程的能力

Connection con = DataSourceUtils.getDatasSource().getConnection();

//获取调用过程的对象

CallableStatement cs =

con.prepareCall("{call proc1()}");

//执行

boolean boo = cs.execute();//如果返回true,指最后一句执行的是select语句

if(boo){

ResultSet rs = cs.getResultSet();

while(rs.next()){

System.err.println(rs.getString("name"));

}

}

con.close();

}

@Test

public void proc2() throws Exception{

Connection con = DataSourceUtils.getDatasSource().getConnection();

//获取调用过程的对象

CallableStatement cs =

con.prepareCall("{call proc2(?,?)}");

cs.setString(1,"UAAA");

cs.setString(2, "11");

boolean boo = cs.execute();

System.err.println(boo);

con.close();

}

@Test

public void proc3() throws Exception{

Connection con = DataSourceUtils.getDatasSource().getConnection();

//获取调用过程的对象

CallableStatement cs =

con.prepareCall("{call proc5(?,?,?)}");

cs.setString(1,"UBDDB");

cs.setString(2, "张三");

cs.registerOutParameter(3,Types.INTEGER);//--int,

boolean boo = cs.execute();

System.err.println(">>:"+boo);//true

//从call中获取返回的值

int size = cs.getInt(3);

System.err.println("行数:"+size);

if(boo){

ResultSet rs= cs.getResultSet();

rs.next();

int ss = rs.getInt(1);

System.err.println("sss:"+ss);

}

con.close();

}

@Test

public void proc6() throws Exception{

Connection con = DataSourceUtils.getDatasSource().getConnection();

//获取调用过程的对象

CallableStatement cs =

con.prepareCall("{call proc6(?,?,?,?)}");

cs.setString(1,"UBafadsB");

cs.setString(2, "张三");

cs.registerOutParameter(3,Types.INTEGER);//--int,

cs.registerOutParameter(4, Types.INTEGER);

boolean boo = cs.execute();

System.err.println(">>:"+boo);//faluse

//从call中获取返回的值

int size = cs.getInt(3);

int _s = cs.getInt(4);

System.err.println("行数:"+size+","+_s);

con.close();

}

}