类ProcedureTest
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
public class ProcedureTest {
/*
* 表和存储过程定义如下: delimiter //
*
* DROP TABLE if exists test // CREATE TABLE test( id int(11) NULL ) //
*
* drop procedure if exists sp1 //
*
* create procedure sp1(in p int) comment 'insert into a int value' begin
* declare v1 int; set v1 = p; insert into test(id) values(v1); end //
*
* drop procedure if exists sp2 // create procedure sp2(out p int) begin
* select max(id) into p from test; end //
*
* drop procedure if exists sp6 // create procedure sp6() begin select *
* from test; end//
*
*/
public static void main(String[] args) {
// callIn(111);
// callOut();
callResult();
}
/**
* 调用带有输入参数的存储过程
*
* @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();
}
}
}
/**
* 调用带有输出参数的存储过程
*
*/
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();
}
}
}
/**
* 调用输出结果集的存储过程
*/
public static void callResult() {
Connection conn = ConnectDb.getConnection();
CallableStatement cs = null;
ResultSet rs = null;
try {
cs = conn.prepareCall("{call p1()}");
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();
}
}
}
}
2.数据库连接类ConnectDb
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
class ConnectDb {
public static Connection getConnection() {
Connection conn = null;
PreparedStatement preparedstatement = null;
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();//注意,mysql驱动的版本必须要5.0以上
String dbname = "test";
String url = "jdbc:mysql://192.168.1.100/" + dbname + "?user=root&password=zhonghui&useUnicode=true&characterEncoding=8859_1";
conn = DriverManager.getConnection(url);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void main(String[] args){
System.out.println(getConnection());//测试连接
}
}