先建一张表:
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20),
math INT
)
再建一个存储过程:
带输入参数的存储过程
DELIMITER $
CREATE PROCEDURE findById(IN n INT)
BEGIN
SELECT *FROM student WHERE id=n;
END$
这里提供一个抽取的JdbcUtils来拿到连接和关闭了连接
JdbcUtils package jdbcTest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class JdbcUtils {
private static String url = "jdbc:mysql://localhost:3306/sqltest";
private static String user = "root";
private static String password = "root";
static {
try {
Class.forName("com.mysql.jdbc.Driver");
} catch (ClassNotFoundException e) {
e.printStackTrace();
System.out.println("数据库驱动加载失败!");
}
}
// 连接数据库
public static Connection getConnection() {
Connection conn = null;
try {// 连接数据库
conn = DriverManager.getConnection(url, user, password);
} catch (SQLException e) {
e.printStackTrace();
System.out.println("连接数据库出错!");
}
return conn;
}
// 关闭资源
public static void CloseConnection(Connection conn, Statement sm,ResultSet rs) {
if (rs != null)
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("关闭连接出错!");
}
if (sm != null)
try {
sm.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("关闭连接出错!");
}
if (conn != null)
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
System.out.println("关闭连接出错!");
}
}
}
调用一个带输入参数的存储过程:
这里写代码片package jdbcTest;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
/**
* 调用存储过程
*
* @author Administrator
*
*/
public class JdbcCallable {
@Test
public void callTest() {
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
// 获取连接
conn = JdbcUtils.getConnection();
// 准备要调用的存储过程的sql
String sql = "call findById(?)";
// 执行预编译
try {
cs = conn.prepareCall(sql);
// 设置输入参数(查询id等于2的学生)
cs.setInt(1, 2);
// 发送参数,执行
rs = cs.executeQuery();//所有调用存储过程的sql都是通过executeQuery()执行
while(rs!=null&&rs.next()){
System.out.print(rs.getInt(1)+" ");
System.out.print(rs.getString(2)+" ");
System.out.println(rs.getInt(3));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JdbcUtils.CloseConnection(conn, cs, rs);
}
}
}
创建一个带返回参数的存储过程
`
DELIMITER $
CREATE PROCEDURE findNameById(IN ids INT, OUT rname VARCHAR(20))
BEGIN
SELECT NAME INTO rname FROM student WHERE id=ids;
END$
调用一个带有返回参数的存储过程
@Test
public void callTest2() {
Connection conn = null;
CallableStatement cs = null;
ResultSet rs = null;
// 获取连接
conn = JdbcUtils.getConnection();
// 准备要调用的存储过程的sql
String sql = "call findNameById(?,?)";
//预编译
try {
cs=conn.prepareCall(sql);
//设置输入参数(查询id=2的学生姓名)
cs.setInt(1, 1);
//设置输出参数(参数位置,参数类型)
cs.registerOutParameter(2, java.sql.Types.VARCHAR);
//发送参数,执行通过getXX()方法获得输出参数
cs.executeQuery();
//输出参数位置为2的输出参数结果
System.out.println(cs.getString(2));
} catch (SQLException e) {
e.printStackTrace();
}finally{
JdbcUtils.CloseConnection(conn, cs, null);
}
}