調用存儲過程

​

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 $$

DELIMITER ;

JSP代碼片斷:

 private  CallableStatement cStmt = null;

//調用存儲過程查詢，需要傳回查詢結果

 public ResultSet callQuery (String proc)

 {

  rs=null;

  try{

      cStmt=conn.prepareCall("CALL "+proc +";",ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

      rs=cStmt.executeQuery();

  }

  catch (SQLException ex)

  {

   System.err.println("DAO.DBBean.executeQuery() ERR :"+ex.getMessage());

  return rs;

 }

 //調用存儲過程更新，不需要傳回

 public void callUpdate (String proc)

  stmt=null;

   System.out.println("{CALL "+proc+"}");

   cStmt=conn.prepareCall("{CALL "+proc+"}");

   cStmt.execute();

   System.err.println("DAO.DBBean.callUpdate() ERR :"+ex.getMessage());

//插入資料。添加使用者，在注冊時使用

 public void addUser(UserBean user) throws SQLException {

  DBBean dbbean = new  DBBean();

  dbbean.openConn();

  try

     {

   StringBuffer sql = new StringBuffer();

       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();

   }