servlet无法正确调用sql server2000的存储过程？

[yangyanninhao]

servlet无法正确调用sql server2000的存储过程？用常量传进取可以用变量不可以了，不知道是怎么回事。


package marriage.register;

import javax.servlet.*;
import javax.servlet.http.*;

import java.io.*;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import marriage.ServletUtilities;

/**
* 类 名: MarriageRegisterValidate
* 功能描述: 验证表单填写是否完整
* @author Administrator
* 编写日期: 2006.1.16
*/
public class MarriageRegisterValidate extends HttpServlet
{

MarriageRegisterBean mrBean = new MarriageRegisterBean();
private String marriageRegisterUrl = "/Application/MarPutIn.jsp" ;

public void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException
{
//MarriageRegisterBean mrBean = new MarriageRegisterBean();

//以下是男女双方的个人资料
mrBean.setUserName(ServletUtilities.filter(request.getParameter("userName")));
mrBean.setMuserName(ServletUtilities.filter(request.getParameter("muserName")));

mrBean.setUserSex(ServletUtilities.filter(request.getParameter("userSex")));
mrBean.setMuserSex(ServletUtilities.filter(request.getParameter("muserSex")));

mrBean.setCardType(ServletUtilities.filter(request.getParameter("cardType")));
mrBean.setMcardType(ServletUtilities.filter(request.getParameter("mcardType")));

mrBean.setCardNum(ServletUtilities.filter(request.getParameter("cardNum")));
mrBean.setMcardNum(ServletUtilities.filter(request.getParameter("mcardNum")));

mrBean.setBirthday(ServletUtilities.filter(request.getParameter("birthday")));
mrBean.setMbirthday(ServletUtilities.filter(request.getParameter("mbirthday")));

mrBean.setNationality(ServletUtilities.filter(request.getParameter("nationality")));
mrBean.setMnationality(ServletUtilities.filter(request.getParameter("mnationality")));

mrBean.setFolk(ServletUtilities.filter(request.getParameter("folk")));
mrBean.setMfolk(ServletUtilities.filter(request.getParameter("mfolk")));

/*
mrBean.setMarriageState(ServletUtilities.filter(request.getParameter("marriageState")));
mrBean.setMmarriageState(ServletUtilities.filter(request.getParameter("mmarriageState")));
*/

mrBean.setKulturLevel(ServletUtilities.filter(request.getParameter("kulturLevel")));
mrBean.setMkulturLevel(ServletUtilities.filter(request.getParameter("mkulturLevel")));

mrBean.setUserWork(ServletUtilities.filter(request.getParameter("userWork")));
mrBean.setMuserWork(ServletUtilities.filter(request.getParameter("muserWork")));

mrBean.setAddressPhone(ServletUtilities.filter(request.getParameter("addressPhone")));
mrBean.setMaddressPhone(ServletUtilities.filter(request.getParameter("maddressPhone")));

mrBean.setMobelPhone(ServletUtilities.filter(request.getParameter("mobelPhone")));
mrBean.setMmobelPhone(ServletUtilities.filter(request.getParameter("mmobelPhone")));

mrBean.setAddress(ServletUtilities.filter(request.getParameter("address")));
mrBean.setMaddress(ServletUtilities.filter(request.getParameter("maddress")));

mrBean.setSertificateNo(ServletUtilities.filter(request.getParameter("sertificateNo")));
mrBean.setMsertificateNo(ServletUtilities.filter(request.getParameter("msertificateNo")));
//以上是男女双方的个人资料

//以下是男女双方的共同资料
mrBean.setSetupPersonName(ServletUtilities.filter(request.getParameter("setupPersonName")));

mrBean.setAreaName(ServletUtilities.filter(request.getParameter("areaName")));

mrBean.setOrgan(ServletUtilities.filter(request.getParameter("organ")));
//以上内容填写bean完毕
//将要判断接下来的工作是怎么样

//boolean bool = mrBean.isComplete();
if(mrBean.isComplete())
{
//如果填写了完整的信息,则进入这个环节

//未完成请继续编写
int isValidateSucceed =MarriageRegisterStateValidate.validateMarriageState(mrBean.getCardType(),mrBean.getCardNum(),mrBean.getMcardType(),mrBean.getMcardNum());

switch(isValidateSucceed)
{
case 0:

[yangyanninhao]

MarriageRegisterStateValidate.marriageRegisterInsert2(mrBean);
marriageRegisterInsert2(mrBean);

marriageRegisterUrl = "/Application/insertSucceed.jsp" ;

requestDispatcher(request,response,marriageRegisterUrl);


break;
//}
case 1:
mrBean.setMarriageRegisterMessage("男方已结婚");
marriageRegisterUrl = "/Application/insertSucceed2.jsp" ;
requestDispatcher(request,response,marriageRegisterUrl);
break;
case 2:
mrBean.setMarriageRegisterMessage("女方已结婚");
marriageRegisterUrl = "/Application/insertSucceed4.jsp" ;
requestDispatcher(request,response,marriageRegisterUrl);
break;
default:
mrBean.setMarriageRegisterMessage("男女双方都已结婚");
marriageRegisterUrl = "/Application/insertSucceed2.jsp" ;
requestDispatcher(request,response,marriageRegisterUrl);
}

}
else
{
//marriageRegisterUrl = "/Application/insertSucceed1.jsp" ;
//requestDispatcher(request,response,marriageRegisterUrl);
try
{
imperfectMackOut(request,response);
}
catch(Exception e)
{
System.err.println("123行出现错误 "+ e);
}
}



}

/**
* imperfectMackOut : 未完成的填写
* 如果填写不完整则由第一个if匹配中的else转换到这里
*
*/
private void imperfectMackOut(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException
{
//boolean bool = mrBean.isPartlyComplete();
if(mrBean.isPartlyComplete())
{
mrBean.setMarriageRegisterMessage("请您把信息填写完整");
marriageRegisterUrl = "/Application/insertSucceed1.jsp";
requestDispatcher(request,response,marriageRegisterUrl);
}
else
{
mrBean.setMarriageRegisterMessage("请您填写信息");
marriageRegisterUrl = "/Application/insertSucceed2.jsp" ;
requestDispatcher(request,response,marriageRegisterUrl);
}

//requestDispatcher(request,response,marriageRegisterUrl);
}

[yangyanninhao]

/**
* 请求转发的方法,负责转发到的页面
*
*/
private void requestDispatcher(HttpServletRequest request,HttpServletResponse response,String marriageRegisterUrl) throws ServletException,IOException
{
RequestDispatcher dispatcherPage = request.getRequestDispatcher(marriageRegisterUrl);
dispatcherPage.forward(request,response);
}

public void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException
{
doPost(request,response);
}



private void marriageRegisterInsert(String userName ,String userSex ,String cardType ,String cardNum,String birthday ,String nationality ,String folk ,String kulturLevel ,String address ,String addressPhone ,String mobelPhone ,String userWork ,String sertificateNo ,String muserName ,String muserSex ,String mcardType ,String mcardNum ,String mbirthday ,String mnationality,String mfolk ,String mkulturLevel ,String maddress ,String maddressPhone ,String mmobelPhone,String muserWork ,String msertificateNo ,String areaName ,String setupPersonName ,String organ)
{
String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String URLName = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=Marriage";
String user = "sa";
String password = "123456";

Connection connection;
//Statement statement;
//ResultSet marriageRegisterResult;
CallableStatement storedPreceduresMarriageRegister;

String storedPreceduresMarriageRegisterSql = "{call Pro_InsertUser(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";

try
{
Class.forName(driverName);
connection = DriverManager.getConnection(URLName,user,password);

storedPreceduresMarriageRegister = connection.prepareCall(storedPreceduresMarriageRegisterSql);

storedPreceduresMarriageRegister.setString(1,userName);
storedPreceduresMarriageRegister.setString(2,userSex);
storedPreceduresMarriageRegister.setString(3,cardType);
storedPreceduresMarriageRegister.setString(4,cardNum);
storedPreceduresMarriageRegister.setString(5,birthday);
storedPreceduresMarriageRegister.setString(6,nationality);
storedPreceduresMarriageRegister.setString(7,folk);
storedPreceduresMarriageRegister.setString(8,kulturLevel);
storedPreceduresMarriageRegister.setString(9,address);
storedPreceduresMarriageRegister.setString(10,addressPhone);
storedPreceduresMarriageRegister.setString(11,mobelPhone);
//storedPreceduresMarriageRegister.setString(12,marriageState);
storedPreceduresMarriageRegister.setString(12,userWork);
storedPreceduresMarriageRegister.setString(13,sertificateNo);

storedPreceduresMarriageRegister.setString(14,muserName);
storedPreceduresMarriageRegister.setString(15,muserSex);
storedPreceduresMarriageRegister.setString(16,mcardType);
storedPreceduresMarriageRegister.setString(17,mcardNum);
storedPreceduresMarriageRegister.setString(18,mbirthday);
storedPreceduresMarriageRegister.setString(19,mnationality);
storedPreceduresMarriageRegister.setString(20,mfolk);
storedPreceduresMarriageRegister.setString(21,mkulturLevel);
storedPreceduresMarriageRegister.setString(22,maddress);
storedPreceduresMarriageRegister.setString(23,maddressPhone);
storedPreceduresMarriageRegister.setString(24,mmobelPhone);
//storedPreceduresMarriageRegister.setString(25,mmarriageState);
storedPreceduresMarriageRegister.setString(25,muserWork);
storedPreceduresMarriageRegister.setString(26,msertificateNo);

storedPreceduresMarriageRegister.setString(27,areaName);
storedPreceduresMarriageRegister.setString(28,setupPersonName);
storedPreceduresMarriageRegister.setString(29,organ);

storedPreceduresMarriageRegister.execute();

storedPreceduresMarriageRegister.close();
connection.close();
}
catch(ClassNotFoundException cnfe)
{
System.err.println("读取驱动程序错误 ：" + cnfe);
}
catch(SQLException sqle)
{
System.err.println("获取Connection对象错误 ：" + sqle);
}
}

[yangyanninhao]

private void marriageRegisterInsert2(MarriageRegisterBean mrBean)
{
String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";
String URLName = "jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=Marriage";
String user = "sa";
String password = "123456";

Connection connection;
//Statement statement;
//ResultSet marriageRegisterResult;
CallableStatement storedPreceduresMarriageRegister;

String storedPreceduresMarriageRegisterSql = "{call Pro_InsertUser(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}";

try
{
Class.forName(driverName);
connection = DriverManager.getConnection(URLName,user,password);

storedPreceduresMarriageRegister = connection.prepareCall(storedPreceduresMarriageRegisterSql);


/*
* 拷贝备用的代码
MarriageRegisterStateValidate.marriageRegisterInsert(mrBean.getUserName() ,mrBean.getUserSex(),mrBean.getCardType(),mrBean.getCardNum(),mrBean.getBirthday(),mrBean.getNationality(),
mrBean.getFolk(),mrBean.getKulturLevel(),mrBean.getAddress(),mrBean.getAddressPhone(),mrBean.getMobelPhone(),mrBean.getUserWork(),mrBean.getSertificateNo() ,mrBean.getMuserName(),
mrBean.getMuserSex(),mrBean.getMcardType(),mrBean.getMcardNum(),mrBean.getMbirthday(),mrBean.getMnationality(),mrBean.getMfolk(),mrBean.getMkulturLevel(),mrBean.getMaddress(),
mrBean.getMaddressPhone(),mrBean.getMmobelPhone(),mrBean.getMuserWork(),mrBean.getMsertificateNo(),mrBean.getAreaName(),mrBean.getSetupPersonName(),mrBean.getOrgan());
*/


storedPreceduresMarriageRegister.setString(1,mrBean.getUserName());
storedPreceduresMarriageRegister.setString(2,mrBean.getUserSex());
storedPreceduresMarriageRegister.setString(3,mrBean.getCardType());
storedPreceduresMarriageRegister.setString(4,mrBean.getCardNum());
storedPreceduresMarriageRegister.setString(5,mrBean.getBirthday());
storedPreceduresMarriageRegister.setString(6,mrBean.getNationality());
storedPreceduresMarriageRegister.setString(7,mrBean.getFolk());
storedPreceduresMarriageRegister.setString(8,mrBean.getKulturLevel());
storedPreceduresMarriageRegister.setString(9,mrBean.getAddress());
storedPreceduresMarriageRegister.setString(10,mrBean.getAddressPhone());
storedPreceduresMarriageRegister.setString(11,mrBean.getMobelPhone());
//storedPreceduresMarriageRegister.setString(12,marriageState);
storedPreceduresMarriageRegister.setString(12,mrBean.getUserWork());
storedPreceduresMarriageRegister.setString(13,mrBean.getSertificateNo());

storedPreceduresMarriageRegister.setString(14,mrBean.getMuserName());
storedPreceduresMarriageRegister.setString(15,mrBean.getMuserSex());
storedPreceduresMarriageRegister.setString(16,mrBean.getMcardType());
storedPreceduresMarriageRegister.setString(17,mrBean.getMcardNum());
storedPreceduresMarriageRegister.setString(18,mrBean.getMbirthday());
storedPreceduresMarriageRegister.setString(19,mrBean.getMnationality());
storedPreceduresMarriageRegister.setString(20,mrBean.getMfolk());
storedPreceduresMarriageRegister.setString(21,mrBean.getMkulturLevel());
storedPreceduresMarriageRegister.setString(22,mrBean.getMaddress());
storedPreceduresMarriageRegister.setString(23,mrBean.getMaddressPhone());
storedPreceduresMarriageRegister.setString(24,mrBean.getMmobelPhone());
//storedPreceduresMarriageRegister.setString(25,mmarriageState);
storedPreceduresMarriageRegister.setString(25,mrBean.getMuserWork());
storedPreceduresMarriageRegister.setString(26,mrBean.getMsertificateNo());

storedPreceduresMarriageRegister.setString(27,mrBean.getAreaName());
storedPreceduresMarriageRegister.setString(28,mrBean.getSetupPersonName());
storedPreceduresMarriageRegister.setString(29,mrBean.getOrgan());

storedPreceduresMarriageRegister.execute();

storedPreceduresMarriageRegister.close();
connection.close();
}
catch(ClassNotFoundException cnfe)
{
System.err.println("读取驱动程序错误 ：" + cnfe);
}
catch(SQLException sqle)
{
System.err.println("获取Connection对象错误 ：" + sqle);
}
}

}