Struts中利用连接池访问MySQL数据库

来源：互联网发布：米格23 知乎编辑：程序博客网时间：2024/05/22 07:49

Dick Winters 于 2004年9月27日晚

在“一个简单的Struts例子”（http://blog.csdn.net/dickwinters/archive/2004/09/27/118676.aspx）的基础上修改

首先，struts-config.xml中加入如下配置，注意几个主要项目的配置的顺序（http://blog.csdn.net/dickwinters/archive/2004/09/27/118685.aspx）。

<data-sources>
    <data-source key="A" type="org.apache.commons.dbcp.BasicDataSource">
      <set-property property="driverClassName" value="org.gjt.mm.mysql.Driver" />
      <set-property property="url"
          value="jdbc:mysql://localhost/dick?useUnicode=true&characterEncoding=GB18030" />
      <set-property property="username" value="root" />
      <set-property property="password" value="" />
      <set-property property="maxActive" value="10" />
      <set-property property="maxWait" value="5000" />
      <set-property property="defaultAutoCommit" value="false" />
      <set-property property="defaultReadOnly" value="false" />
    </data-source>
</data-sources>

几个主要文件改动如下：

LoginAction.java

package dick;

import java.io.IOException;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;
import javax.servlet.http.HttpServletResponse;
import org.apache.struts.action.Action;
import org.apache.struts.action.ActionError;
import org.apache.struts.action.ActionErrors;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionServlet;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

public final class LoginAction extends Action {

public ActionForward execute(ActionMapping mapping,
         ActionForm form,
         HttpServletRequest request,
         HttpServletResponse response)
         throws IOException, ServletException {
    UserInfoForm userInfoForm = (UserInfoForm) form;
    //从web层获得用户名和口令
    String username = userInfoForm.getUsername().trim();
    String password = userInfoForm.getPassword().trim();
    //声明错误集对象
    ActionErrors errors = new ActionErrors();
    //声明数据源和连接对象
    DataSource dataSource;
    Connection cnn=null;

    //校验输入
    if(username.equals("")){
      ActionError error=new ActionError("error.missing.username");
      errors.add(ActionErrors.GLOBAL_ERROR,error);
    }
    if(password.equals("")){
      ActionError error=new ActionError("error.missing.password");
      errors.add(ActionErrors.GLOBAL_ERROR,error);
    }

    //调用业务逻辑
    if(errors.size()==0){
      String validated = "";
      try{
        //取得数据库连接
        dataSource = getDataSource(request,"A");
        cnn = dataSource.getConnection();

        UserInfoBo userInfoBo=new UserInfoBo(cnn);
        validated =userInfoBo.validatePwd(username,password);
        if(validated.equals("match")){
          //一切正常就保存用户信息并转向成功的页面
          HttpSession session = request.getSession();
          session.setAttribute("userInfoForm", form);
                return mapping.findForward("success");
        }
      }

      catch(Throwable e){
        //处理可能出现的错误
        e.printStackTrace();
        ActionError error=new ActionError(e.getMessage());
        errors.add(ActionErrors.GLOBAL_ERROR,error);
      }
    }
    //如出错就转向输入页面，并显示相应的错误信息
    saveErrors(request, errors);
    return new ActionForward(mapping.getInput());
}
}

UserInfoBo.java

package dick;

import java.sql.Connection;
import java.sql.SQLException;
import java.lang.Exception;

public class UserInfoBo {
private Connection cnn=null;

public UserInfoBo(Connection cnn){
this.cnn=cnn;
}

public String validatePwd(String username,String password){

    String validateResult="";

    try{
      UserInfoDao userInfoDao = new UserInfoDao(cnn);
      validateResult=userInfoDao.validatePwd(username,password);
      if(validateResult.equals("error.logon.invalid")){
        //如果用户名与口令不匹配则报此错
        throw new RuntimeException("error.logon.invalid");
      }
      else if(validateResult.equals("error.removed.user")){
        //如果找不到用户则报此错，这样用户看到的出错信息会更详细
        throw new RuntimeException("error.removed.user");
      }
    }
    catch(Exception e){
      throw new RuntimeException(e.getMessage());
    }
    finally{
      try{
        if(cnn!=null){
          cnn.close();
        }
      }
      catch(SQLException sqle){
        sqle.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }
    return validateResult;
}
}

添加下面的文件：

UserInfoDao.java

package dick;

import java.sql.*;

public class UserInfoDao {
private Connection con;

public UserInfoDao(Connection con) {
    this.con=con;
}

public String validatePwd(String username,String password){
    PreparedStatement ps=null;
    ResultSet rs=null;
    String validated="error.logon.invalid";
    UserInfoForm userInfoForm=null;
    String sql="select * from userInfo where username=?";
    try{
      if(con.isClosed()){
        throw new IllegalStateException("error.unexpected");

      }
      ps=con.prepareStatement(sql);
      ps.setString(1,username);
      rs=ps.executeQuery();
      if(rs.next()){
        if(!rs.getString("password").trim().equals(password)){
          return validated;//口令不正确返回口令不匹配信息

        }
        else{

          validated = "match";//口令正确返回口令匹配信息
          return validated;
        }
      }else{

        validated="error.removed.user";//没有找到该用户
        return validated;

      }

    }catch(SQLException e){
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
    }finally{
      try{
        if(ps!=null)
          ps.close();
        if(rs!=null)
          rs.close();
      }catch(SQLException e){
        e.printStackTrace();
        throw new RuntimeException("error.unexpected");
      }
    }
}
}

web.xml, index.jsp, main.jsp, UserInfoForm.java代码不变