Java调用存储过程

gistfile1.sql

DELIMITER //
CREATE PROCEDURE insert_test(IN uname VARCHAR(50),IN uaddress VARCHAR(50))
BEGIN
  INSERT INTO t_test(username,address) VALUES(uname,uaddress);
END//
DELIMITER ;

gistfile2.java

Class.forName("com.mysql.jdbc.Driver");
   
Connection conn = DriverManager.getConnection("jdbc:mysql:///proc_db","root","root");
   
CallableStatement cs = conn.prepareCall("{CALL insert_test(?,?)}");
   
cs.setString(1, "fankai");
cs.setString(2, "hennan");
   
int rows = cs.executeUpdate();
   
System.out.println(rows);
   
cs.close();
conn.close();

gistfile3.sql

DELIMITER //
CREATE PROCEDURE find_test() 
BEGIN
  SELECT id,username,address FROM t_test;
END//
DELIMITER ;

gistfile4.java

Class.forName("com.mysql.jdbc.Driver");
   
Connection conn = DriverManager.getConnection("jdbc:mysql:///proc_db","root","root");
   
CallableStatement cs = conn.prepareCall("{CALL find_test()}");
   
ResultSet rs = cs.executeQuery();
   
while(rs.next()) {
  String name = rs.getString("username");
  String address = rs.getString("address");
  System.out.println("name:" + name + "\taddress:" + address);
}
   
rs.close();
cs.close();
conn.close();

gistfile5.sql

DELIMITER //
CREATE PROCEDURE insert_getId(OUT id INT,IN uname VARCHAR(50),IN uaddress VARCHAR(50))
BEGIN
  INSERT INTO t_test(username,address) VALUES(uname,uaddress);
  SELECT LAST_INSERT_ID() INTO id;
  SELECT id;
END//
DELIMITER ; 

gistfile6.java

Class.forName("com.mysql.jdbc.Driver");
    
Connection conn = DriverManager.getConnection("jdbc:mysql:///proc_db","root","root");
    
CallableStatement cs = conn.prepareCall("{CALL insert_getId(?,?,?)}");
    
cs.registerOutParameter(1, Types.INTEGER);
cs.setString(2, "fankai");
cs.setString(3, "hennan");
    
cs.executeUpdate();
    
int id = cs.getInt(1);
    
System.out.println("刚刚插入的Id：" + id);
    
cs.close();
conn.close();