存储过程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 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);