Java调用存储过程(MySql数据库)

一、建表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `ID` int(11) NOT NULL auto_increment,
  `NAME` varchar(16) NOT NULL default '',
  `REMARK` varchar(16) NOT NULL default '',
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8;
 
二、建立存储过程
1、获取用户信息
CREATE DEFINER=`root`@`localhost` PROCEDURE `getUserList`()
BEGIN
  select * from user;
END;
2、通过传入参数创建用户
CREATE DEFINER=`root`@`localhost` PROCEDURE `insertUser`(nameVar varchar(16),remarkVar varchar(16))
BEGIN
  insert into user(name,remark) values(nameVar,remarkVar);
END;
 
三、调用
1、获取用户信息
Class.forName("org.gjt.mm.mysql.Driver").newInstance();
String url ="jdbc:mysql://localhost/temp?user=root&password=root";
Connection conn = DriverManager.getConnection(url);
String proc = "call getUserList()";
CallableStatement cs = conn.prepareCall(proc);
rs = cs.executeQuery();
while(rs.next()){
    System.out.println(rs.getString(2)+" "+rs.getString(3));
}
2、通过传入参数创建用户
Connection conn = DriverManager.getConnection(url);
String proc = "call insertUser(?,?)";
CallableStatement cs = conn.prepareCall(proc);
cs.setString(1, "LINRZ");
cs.setString(2, "REMARK");
cs.execute();