java 怎么调用mysql 存储过程

import java.sql.CallableStatement

import java.sql.Connection

import java.sql.DriverManager

import java.sql.Types

/*

* 对应的存储过程

DROP PROCEDURE IF EXISTS `pro_num_user`

delimiter

CREATE PROCEDURE `pro_num_user`(IN user_name varchar(10) ,OUT count_num INT)

READS SQL DATA

BEGIN

SELECT COUNT(*) INTO count_num FROM tab_user WHERE 'name'=user_name

END

delimiter

*/

public class Test {

public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver"

public static final String URL = "jdbc:mysql://127.0.0.1:3306/test"

public static final String USERNAME = "root"

public static final String PASSWORD = "acbee"

public static void main(String[] args) throws Exception {

test1()

test2()

}

public static void test1() throws Exception

{

Class.forName(DRIVER_CLASS)

Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)

String sql = "{CALL pro_num_user(?,?)}"//调用存储过程

CallableStatement cstm = connection.prepareCall(sql)//实例化对象cstm

cstm.setString(1, "myd")//存储过程输入参数

//cstm.setInt(2, 2)// 存储过程输入参数

cstm.registerOutParameter(2, Types.INTEGER)// 设置返回值类型 即返回值

cstm.execute()// 执行存储过程

System.out.println(cstm.getInt(2))

cstm.close()

connection.close()

}

public static void test2() throws Exception

{

Class.forName(DRIVER_CLASS)

Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD)

String sql = "{CALL pro_number(?,?,?)}"//调用存储过程

CallableStatement cstm = connection.prepareCall(sql)//实例化对象cstm

cstm.setInt(1, 2)// 存储过程输入参数

cstm.setInt(2, 2)// 存储过程输入参数

cstm.registerOutParameter(3, Types.INTEGER)// 设置返回值类型 即返回值

cstm.execute()// 执行存储过程

System.out.println(cstm.getInt(3))

cstm.close()

connection.close()

}

}

java代码:

Class.forName("com.mysql.jdbc.Driver")

Connection con = DriverManager

  .getConnection("jdbc:mysql://localhost:3306/test?user=root&password=111111")

String sql = "{call sel(?)}"

CallableStatement cs = (CallableStatement)con.prepareCall(sql)

cs.execute()

String name = cs.getString(1)

System.out.println(name)

创建存储过程代码:

drop procedure if exists sel

create procedure sel(out name1 varchar(225))

begin

      select name into name1 from a limit 1

end

输出结果:

在存储过程中如果有查询结果集，在java中执行存储过程后，应使用其父接口Statement的getResultSet()方法获取查询结果集，此方法就返回ResultSet，如你上面若想获取存储过程中查询结果集应如下：

ResultSet rs = cst.getResultSet()

以下遍历此rs即可... ...

---