首页 > 代码库 > 存储过程之七—java代码调用
存储过程之七—java代码调用
一、简介
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 NULL AUTO_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 EXISTS proc_person_insert;CREATE PROCEDURE proc_person_insert( IN uusername VARCHAR(255), IN uage INT(11), IN upassword VARCHAR(255), OUT flag INT(11))BEGIN START TRANSACTION; INSERT INTO person (username, age, password) values (uusername, uage, upassword); SET flag = ROW_COUNT(); -- 保存sql语句操作后影响的行数 COMMIT; END;
调用代码如下：
1 public static void insert(String username, int age, 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); //设置输入参数username12 statement.setInt(2, age); //设置输入参数age13 statement.setString(3, upassword);//设置输入参数password14 statement.registerOutParameter(4, Types.INTEGER);//设置输出参数username15 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 EXISTS proc_person_update;CREATE PROCEDURE proc_person_update( IN uid INT(11), IN uusername VARCHAR(255), IN uage INT(11), IN upassword VARCHAR(255), OUT flag INT(11))BEGIN START TRANSACTION; 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, int age, 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 EXISTS proc_person_del;CREATE PROCEDURE proc_person_del( IN pid INT(11) )BEGIN DELETE FROM person WHERE id = pid; END;
调用代码如下：
1 public static void delete(int id){ 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 EXISTS proc_person_find;CREATE PROCEDURE proc_person_find( IN pid INT(11), OUT pusername VARCHAR(255), OUT page INT(11), OUT ppassword VARCHAR(255))BEGIN SELECT username, age, password INTO pusername, page, ppassword FROM person WHERE id = pid;END;
使用在SELECT语句中使用INTO给输出参数赋值
调用代码如下：
1 public static void find(int id){ 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 EXISTS proc_person_findAll;CREATE PROCEDURE proc_person_findAll( )BEGIN SELECT id, username, age, password FROM person;END;
调用代码如下：
1 public static void findAll(){ 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进行遍历。
存储过程之七—java代码调用
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。