Servlet调用SQL Server2000存储过程实例

 

数据库中的存储过程：

CREATE PROC getuser1

@name         VARCHAR(50),

@pwd           VARCHAR(50),

@count        int OUTPUT             -- 要输出的参数

--@myname VARCHAR(50) OUTPUT,

--@mypwd    VARCHAR(50) OUTPUT

AS

BEGIN

      SELECT @count =count(*) FROM admin WHERE name=@name and pwd=@pwd

   --   SELECT @myname =name , @mypwd=pwd FROM admin WHERE --name=@name and pwd=@pwd

END

GO

 本存储过程模拟用户登陆,输入用户名,密码,返回用户数,用户存在返回1,否则返回0;

 

服务器类：

import java.io.IOException;

import java.io.PrintWriter;

import javax.servlet.ServletException;

import javax.servlet.http.HttpServlet;

import javax.servlet.http.HttpServletRequest;

import javax.servlet.http.HttpServletResponse;

import java.sql.*;

 

 

public class CallableStatementExample extends HttpServlet {

 

       /**

        * The doGet method of the servlet. <br>

        *

        * This method is called when a form has its tag value method equals to get.

        *

        * @param request the request send by the client to the server

        * @param response the response send by the server to the client

        * @throws ServletException if an error occurred

        * @throws IOException if an error occurred

        */

       public void doGet(HttpServletRequest request, HttpServletResponse response)

                     throws ServletException, IOException {

                     doPost(request,response);

       }

 

       /**

        * The doPost method of the servlet. <br>

        *

        * This method is called when a form has its tag value method equals to post.

        *

        * @param request the request send by the client to the server

        * @param response the response send by the server to the client

        * @throws ServletException if an error occurred

        * @throws IOException if an error occurred

        */

       public void doPost(HttpServletRequest request, HttpServletResponse response)

                     throws ServletException, IOException {

 

              Connection conn=null;

              Statement stm = null;

              ResultSet rs = null;

              String name="",pwd="";

              String myname="",mypwd="";

              int count=0,i=0;

             

              name = request.getParameter("name");

              pwd = request.getParameter("pwd");

              /*Enumeration para = request.getParameterNames();

              while(para.hasMoreElements()){

                     String nextpara = (String)para.nextElement();

                     temp[i++] = request.getParameter(nextpara);

              }

              */

              try{

                     conn = new conn().connect();

                     CallableStatement cstmt = conn.prepareCall("{ call getuser1(?,?,?) }");

                     cstmt.setString(1,name);//设置第一个参数（输入）

                     cstmt.setString(2,pwd);//设置第二个参数（输出）

                     cstmt.registerOutParameter(3,Types.INTEGER);//设置第三个参数（输出）

                     //cstmt.registerOutParameter(4,Types.VARCHAR);

                     //cstmt.registerOutParameter(5,Types.VARCHAR);

                     cstmt.execute();

                     count = cstmt.getInt(3);

              //     myname = cstmt.getString(4);

                     //mypwd = cstmt.getString(5);

              /*    stm = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY);

                     String sql = "select * from admin where name='"+name+"' and pwd='"+pwd+"'";

                     rs = stm.executeQuery(sql);

                     while(rs.next()){

                            name = rs.getString("name");

                            pwd =rs.getString("pwd");

                     }

                     rs.last();

                     count = rs.getRow();*/

              }catch(Exception e){}

              finally{

                     try{

                     rs.close();

                     stm.close();

                     conn.close();

                     }catch(Exception e){}

                    

              }

              if(count>0){

                     javax.servlet.http.HttpSession session = request.getSession(false);

                     if(session == null){

                            session = request.getSession(true);

                            session.setAttribute("name",name);

                            session.setAttribute("pwd",pwd);

                     }

                     else{

                            session.setAttribute("name",name);

                            session.setAttribute("pwd",pwd);

                     }

                     //response.sendRedirect("/test/second.jsp?name="+name+"&pwd="+pwd);

                     response.sendRedirect("/test/second.jsp");

                     return;

              }

              else {

                     response.sendRedirect("/test/error.jsp");

                     return;

                     }

              }

       }