JSP调用Mysql存储过程的方法和例子
方法:
方法一:
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("{calldbo.GetContactFormalNames}");
方法二: CallableStatement callstat = con.prepareCall("{calldbo.GetContactFormalNames}"); ResultSet rs =callstat.executeQuery();while(rs.next()){
例子
MYSQL
DELIMITER $$
DROP PROCEDURE IF EXISTS `freewap`.`add_user` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE`add_user`(
IN name TEXT,
IN pwd TEXT,
IN email TEXT,
IN qq TEXT,
IN birthday DATE,
IN sex tinyint,
IN pwdquestion TEXT,
IN pwdanswer TEXT,
IN firstpwd TEXT
)
BEGIN
INSERT INTO`tbl_user`(u_name, u_pwd, u_email, u_qq, u_birthday, u_sex,u_pwdquestion, u_pwdanswer, u_firstpwd)
VALUES(name,pwd,email,qq,birthday,sex,pwdquestion,pwdanswer,firstpwd);
END $$
CREATE DEFINER=`root`@`localhost` PROCEDURE`add_user`(
BEGIN
DELIMITER ;
JSP代码片断:
//调用存储过程查询,需要返回查询结果
//插入数据。添加用户,在注册时使用
public void addUser(UserBean user) throwsSQLException {
DBBean dbbean =new
DBBean();
dbbean.openConn();
try
{
StringBuffer sql = newStringBuffer();
sql = new StringBuffer("add_user(");
sql.append("'" + user.getU_name() + "',");
StrEncrypt strencrypt=new StrEncrypt();
sql.append("'" + strencrypt.EncryptStr(user.getU_pwd()) +"',");
sql.append("'" + user.getU_email() + "',");
sql.append("'" + user.getU_qq() + "',");
sql.append("'" + new java.sql.Date(user.getU_birthday().getTime())+ "',");
sql.append("" + user.getU_sex() + ",");
sql.append("'" + user.getU_pwdquestion() + "',");
sql.append("'" + user.getU_pwdanswer() + "',");
sql.append("'" + user.getU_pwd() + "'");
sql.append(")");
dbbean.callUpdate(sql.toString());
}
catch (Exception sqle)
{
sqle.printStackTrace();
}
finally
{
dbbean.closeCall();
}
}