JSP调用Mysql存储过程的方法和例子

JSP调用Mysql存储过程的方法和例子

方法：

 

方法一：          Statement stmt = con.createStatement();
          ResultSet rs = stmt.executeQuery("{calldbo.GetContactFormalNames}");

          while (rs.next()) {
                System.out.println(rs.getString("FormalName"));
          }
方法二： CallableStatement callstat = con.prepareCall("{calldbo.GetContactFormalNames}"); ResultSet rs =callstat.executeQuery();while(rs.next()){                    System.out.println(rs.getString(2)); }

 

 

 

 

 

 

例子

 

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  CallableStatementcStmt = 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)
  {
    rs=null;
    stmt=null;
    try{
      System.out.println("{CALL"+proc+"}");
      cStmt=conn.prepareCall("{CALL"+proc+"}");
      cStmt.execute();
    }
    catch (SQLException ex)
    {
      System.err.println("DAO.DBBean.callUpdate()ERR :"+ex.getMessage());
    }
  }

 

//插入数据。添加用户，在注册时使用
  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();
        }
    }