//创建存储过程
create procedure procedure_name() select * from tableName;
//调用存储过程
call procedure_name();
//创建带参数的存储过程
create procedure procedure_name(in _name varchar(255),in _sex varchar(255)) insert into stu(name,sex)values(_name,_sex);
//插入数据
call procedure_name('李小龙','女');
//查询数据库中的内容
create procedure procedure_name(in _name varchar(255),out _sex varchar(255))
begin
declare a varchar(255);
select sex into a from stu where name = _name;
set _sex = a;
end;
java代码:
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
public class TestCallableStatement {
Connection conn;
CallableStatement calls;
Statement stmt;
ResultSet rs;
String DString = "com.mysql.jdbc.Driver";
String DBUrl = "jdbc:mysql://localhost:3306/test";
String sql = "{call stu_all()}";
String sql1 = "{call _stu_insert(?,?,?)}";
String sql2 = "{call _name(?,?)}";
public static void main(String[] args) {
TestCallableStatement testcallablestatement = new TestCallableStatement();
//testcallablestatement.testCallableStatement();
//testcallablestatement.testCallableStatement2();
testcallablestatement.testCallableStatement3();
}
public void testCallableStatement() {
TestCallableStatement tcs = new TestCallableStatement();
conn = tcs.DBConnection();
try {
calls = conn.prepareCall(sql);
rs = calls.executeQuery();
while (rs.next()) {
int id = rs.getInt(1);
String strName = rs.getString(2);
String strSex = rs.getString(3);
System.out.println(id + " " + strName + " " + strSex);
}
} catch (SQLException e) {
try {
conn.close();
calls.close();
rs.close();
} catch (SQLException e1) {
}
}
}
public void testCallableStatement2() {
TestCallableStatement tcs = new TestCallableStatement();
conn = tcs.DBConnection();
try {
calls = conn.prepareCall(sql1);
calls.setInt(1, 20);
calls.setString(2, "张艺谋");
calls.setString(3, "女");
calls.executeUpdate();
} catch (SQLException e) {
try {
conn.close();
calls.close();
} catch (SQLException e1) {
}
}
}
public void testCallableStatement3() {
TestCallableStatement tcs = new TestCallableStatement();
conn = tcs.DBConnection();
try {
calls = conn.prepareCall(sql2);
calls.setString(1,"张三");
calls.registerOutParameter(2, Types.VARCHAR);
calls.execute();
String str = calls.getString(2);
System.out.println(str);
} catch (SQLException e) {
try {
conn.close();
calls.close();
} catch (SQLException e1) {
}
}
}
Connection DBConnection() {
try {
Class.forName(DString);
conn = DriverManager.getConnection(DBUrl,"root","root");
} catch (Exception e) {
System.out.println(e.getMessage());
}
return conn;
}
}
存储过程和CallableStatement
最新推荐文章于 2020-11-03 15:40:05 发布