使用JDBC调用存储过程

浏览数：27 / 时间：2015年06月12日

 1 DELIMITER $$
 2 
 3 DROP PROCEDURE IF EXISTS `jdbc`.`addUser` $$
 4 CREATE PROCEDURE `jdbc`.`addUser` (in pname varchar(45),in birthday date,in money float,out pid int)
 5 BEGIN
 6      insert into user(name,birthday,money) value(pname,birthday,money);
 7      select last_insert_id() into pid;
 8 END $$
 9 
10 DELIMITER ;

addUser

 1 package it.cast.jdbc;
 2 
 3 import java.sql.CallableStatement;
 4 import java.sql.Connection;
 5 import java.sql.PreparedStatement;
 6 import java.sql.ResultSet;
 7 import java.sql.SQLException;
 8 import java.sql.Statement;
 9 import java.sql.Types;
10 
11 public class PsTest {
12 
13     
14     public static void main(String[] args) throws SQLException {
15         create();
16     }
17     
18     static void ps(){
19         Connection conn = null;
20         CallableStatement cs = null;
21         ResultSet rs = null;
22         
23         try {
24             conn = jdbcUtils.getConnection();
25             
26             String sql = "{ call addUser(?,?,?,?)}";
27             cs = conn.prepareCall(sql);
28             cs.registerOutParameter(4, Types.INTEGER);//输出参数需要注册
29             cs.setString(1, "ps name");
30             cs.setDate(2, new java.sql.Date(System.currentTimeMillis()));
31             cs.setFloat(3, 100f);
32             
33             cs.executeUpdate();
34             int id = cs.getInt(4);
35             
36             System.out.println("id="+id);
37         } catch (SQLException e) {
38             e.printStackTrace();
39         }finally{
40             jdbcUtils.free(rs, cs, conn);
41         }
42     }
43     
44     static int create() throws SQLException{
45         Connection conn = null;
46         PreparedStatement ps = null;
47         ResultSet rs = null;
48         
49         try {
50             conn = jdbcUtils.getConnection();
51             
52             String sql = "insert into user(name,birthday,money) values(‘zero‘,‘1987-01-01‘,‘4000‘)";
53             ps = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);//返回插入数据的主键
54             ps.executeUpdate();
55             
56             rs = ps.getGeneratedKeys();
57             
58             int id = 0;
59             if(rs.next()){
60                 id = rs.getInt(1);
61             }
62             
63             return id;
64         } finally{
65             jdbcUtils.free(rs, ps, conn);
66         }
67         
68     }
69 
70 }