一、简介
jdbc调用存储过程与调用一般的sql语句有些差别。jdbc调用一般的sql语句的时候,返回的是结果集,或是sql语句后是否执行成功的标记量。而存储过程返回的可以是结果集,输出参数、返回状态和受影响行数。而mysql存储过程不支持return,所以只有剩下的三种返回方式。
调用的过程一般如下:
1、创建连接 Connection conn = DriverManager.getConnection(url, user, password);
2、创建CallableStatement CallableStatement statement = conn.prepareCall(sql);
3、设置参数
statement.setInt(1, id);
statement.registerOutParameter(2, Types.VARCHAR);
statement.registerOutParameter(3, Types.INTEGER);
statement.registerOutParameter(4, Types.VARCHAR);
4、执行
statement.execute(); 或 statement.executeUpdate();
5、获取返回
int age = statement.getInt(3);或ResultSet resultSet = statement.executeQuery();
二、实例
以下存储过程表结构如下:
DROP TABLE IF EXISTS`person`;CREATE TABLE`person` (
`id`int(11) NOT NULLAUTO_INCREMENT,
`username`varchar(255) DEFAULT NULL,
`age`int(11) DEFAULT NULL,
`password`varchar(255) DEFAULT NULL,PRIMARY KEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;--------------------------------Records of person------------------------------
INSERT INTO `person` VALUES ('1', 'lisi', '21', 'li123456');INSERT INTO `person` VALUES ('2', 'zhangsan', '23', 'zhang123456');
1、增加记录(输入输出参数的调用)
存储过程如下:
DROP PROCEDURE IF EXISTSproc_person_insert;CREATE PROCEDUREproc_person_insert(IN uusername VARCHAR(255),IN uage INT(11),IN upassword VARCHAR(255),
OUT flagINT(11)
)BEGINSTARTTRANSACTION;INSERT INTO person (username, age, password) values(uusername, uage, upassword);SET flag = ROW_COUNT(); --保存sql语句操作后影响的行数
COMMIT;END;
调用代码如下:
1 public static void insert(String username, intage, String upassword) {2 String driver = "com.mysql.jdbc.Driver";3 String url = "jdbc:mysql://127.0.0.1:3306/test";4 String user = "root";5 String password = "";6 try{7 Class.forName(driver);8 Connection conn =DriverManager.getConnection(url, user, password);9 String sql = "{call proc_person_insert( ?, ?, ?, ? )}";10 CallableStatement statement =conn.prepareCall(sql);11 statement.setString(1, username); //设置输入参数username
12 statement.setInt(2, age); //设置输入参数age
13 statement.setString(3, upassword);//设置输入参数password
14 statement.registerOutParameter(4, Types.INTEGER);//设置输出参数username
15 statement.executeUpdate();16 int flag = statement.getInt(4);//获取输出参数
17 System.out.println(flag);//1添加成功,0添加失败
18 } catch(ClassNotFoundException e) {19 e.printStackTrace();20 } catch(SQLException e) {21 e.printStackTrace();22 }23 }
对于输出参数需要调用statement.registerOutParameter(4, Types.INTEGER)进行注册,执行之后通过statement.getInt()方法来获取。
2、修改记录(输入输出参数的调用)
存储过程如下:
DROP PROCEDURE IF EXISTSproc_person_update;CREATE PROCEDUREproc_person_update(IN uid INT(11),IN uusername VARCHAR(255),IN uage INT(11),IN upassword VARCHAR(255),
OUT flagINT(11)
)BEGINSTARTTRANSACTION;UPDATE person SET username = uusername, age = uage, password = upassword WHERE id =uid;SET flag = ROW_COUNT(); --保存sql语句操作后影响的行数
COMMIT;END;
调用代码如下:
1 public static void update(int id, String username, intage, String upassword) {2 String driver = "com.mysql.jdbc.Driver";3 String url = "jdbc:mysql://127.0.0.1:3306/test";4 String user = "root";5 String password = "";6 try{7 Class.forName(driver);8 Connection conn =DriverManager.getConnection(url, user, password);9 String sql = "{call proc_person_update( ?, ?, ?, ?, ? )}";10 CallableStatement statement =conn.prepareCall(sql);11 statement.setInt(1, id);12 statement.setString(2, username);13 statement.setInt(3, age);14 statement.setString(4, upassword);15 statement.registerOutParameter(5, Types.INTEGER);16 statement.executeUpdate();17 int flag = statement.getInt(5);18 System.out.println(flag);//1添加成功,0添加失败
19 } catch(ClassNotFoundException e) {
20 e.printStackTrace();21 } catch(SQLException e) {
22 e.printStackTrace();23 }24 }
3、删除记录(使用statement.getUpdateCount()获取影响行数)
存储过程如下:
DROP PROCEDURE IF EXISTSproc_person_del;CREATE PROCEDUREproc_person_del(IN pid INT(11)
)BEGIN
DELETE FROM person WHERE id =pid;END;
调用代码如下:
1 public static void delete(intid){2 String driver = "com.mysql.jdbc.Driver";3 String url = "jdbc:mysql://127.0.0.1:3306/test";4 String user = "root";5 String password = "";6 try{7 Class.forName(driver);8 Connection conn =DriverManager.getConnection(url, user, password);9 String sql = "{call proc_person_del( ?)}";10 CallableStatement statement =conn.prepareCall(sql);11 statement.setInt(1, id);12 statement.executeUpdate();13 int flag = statement.getUpdateCount();//使用该语句的时候存储过程DELETE语句外不能出现 START TRANSACTION;COMMINT;
14 System.out.println(flag);15 } catch(ClassNotFoundException e) {
16 e.printStackTrace();17 } catch(SQLException e) {
18 e.printStackTrace();19 }20 }
4、查询一条记录(输入输出)
存储过程如下:
DROP PROCEDURE IF EXISTSproc_person_find;CREATE PROCEDUREproc_person_find(IN pid INT(11),
OUT pusernameVARCHAR(255),
OUT pageINT(11),
OUT ppasswordVARCHAR(255)
)BEGIN
SELECT username, age, password INTO pusername, page, ppassword FROM person WHERE id =pid;END;
使用在SELECT语句中使用INTO给输出参数赋值
调用代码如下:
1 public static void find(intid){2 String driver = "com.mysql.jdbc.Driver";3 String url = "jdbc:mysql://127.0.0.1:3306/test";4 String user = "root";5 String password = "";6 try{7 Class.forName(driver);8 Connection conn =DriverManager.getConnection(url, user, password);9 String sql = "{call proc_person_find( ?, ?, ?, ?)}";10 CallableStatement statement =conn.prepareCall(sql);11 statement.setInt(1, id);12 statement.registerOutParameter(2, Types.VARCHAR);13 statement.registerOutParameter(3, Types.INTEGER);14 statement.registerOutParameter(4, Types.VARCHAR);15 statement.execute();16 String username = statement.getString(2);17 int age = statement.getInt(3);18 String ppassword = statement.getString(4);19 System.out.println("id:" +id);20 System.out.println("username:" +username);21 System.out.println("age:" +age);22 System.out.println("ppassword:" +ppassword);23 } catch(ClassNotFoundException e) {24 e.printStackTrace();25 } catch(SQLException e) {26 e.printStackTrace();27 }28 }
5、查询一个集合(返回一个集合)
存储过程如下:
DROP PROCEDURE IF EXISTSproc_person_findAll;CREATE PROCEDUREproc_person_findAll( )BEGIN
SELECT id, username, age, password FROMperson;END;
调用代码如下:
1 public static voidfindAll(){2 String driver = "com.mysql.jdbc.Driver";3 String url = "jdbc:mysql://127.0.0.1:3306/test";4 String user = "root";5 String password = "";6 try{7 Class.forName(driver);8 Connection conn =DriverManager.getConnection(url, user, password);9 String sql = "{call proc_person_findAll()}";10 CallableStatement statement =conn.prepareCall(sql);11 ResultSet resultSet =statement.executeQuery();12 while(resultSet.next()){13 int id = resultSet.getInt("id");14 String username =resultSet.getString("username");15 int age = resultSet.getInt("age");16 String ppassword = resultSet.getString("password");17 System.out.println(id + " " + username + " " + age + " " +ppassword);18 }19 } catch(ClassNotFoundException e) {20 e.printStackTrace();21 } catch(SQLException e) {22 e.printStackTrace();23 }24 }
通过statement.executeQuery()方法返回已经ResultSet,再对ResultSet进行遍历。
关于存储过程存在into参数的时候,java代码中在设置输入参数之后,还需要注册输出参数,调用之后通过statement.get..方法拿到输出参数的值。如下:
String sql = "{call proc_person_del( ? )}";
CallableStatement statement=conn.prepareCall(sql);
statement.setInt(1, id);
statement.registerOutParameter(1, Types.INTEGER);//注意此次注册out 的index 和上面的in 参数index 相同
statement.execute();int flag = statement.getInt(1);