存储过程之七—java代码调用

最新推荐文章于 2022-05-21 06:45:00 发布

weixin_34071713

最新推荐文章于 2022-05-21 06:45:00 发布

阅读量89

点赞数

文章标签： 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); //设置输入参数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 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进行遍历。

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