userdao.java

这是jsp网站开发死酷全书的代码

package com.publish.shop.security.dao;

import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import com.publish.shop.util.dao.SerialNoDAO;
import com.publish.shop.util.db.DbManager;
import com.publish.shop.util.db.DbPool;
import com.publish.shop.taglib.Menu;
import com.publish.shop.util.javabeans.Pager;
import com.publish.shop.util.javabeans.Debug;
import com.publish.shop.util.javabeans.DateTimeUtility;
import com.publish.shop.util.javabeans.DateUtility;
import com.publish.shop.security.javabeans.UserModel;
import org.apache.struts.util.LabelValueBean;

public class UserDAO {
  public UserDAO() {
  }

  public void insert(UserModel model) throws Exception{
      String sql = "insert into UserInfo_table(loginName,userName,userPwd,userType,userStatus,createTime)"
                   +" values(?,?,?,?,?,?)";
      String sql01 = "insert into UserGroup_table(userId,groupId) values(?,?)";
      Connection con = null;
      PreparedStatement stmt = null;
      PreparedStatement pstmt = null;
      try{
        con = DbPool.getConnection();
        pstmt = con.prepareStatement(sql01);
        stmt = con.prepareStatement(sql);
        stmt.setString(1,model.getLoginName());
        stmt.setString(2,model.getUserName());
        stmt.setString(3,"12345678");
        stmt.setString(4,model.getUserTypeId());
        stmt.setString(5,"Y");
        stmt.setString(6,DateTimeUtility.getCurTimeStamp());
        System.out.println("getCurTimeStamp: "+DateTimeUtility.getCurTimeStamp()+"-");
        stmt.executeUpdate();
        UserModel model1 = queryUserByLoginName(model.getLoginName());
        String userId = model1.getUserId();
        if(model.getSelectedRoles()!=null && model.getSelectedRoles().length>0){
          for(int i=0;i<model.getSelectedRoles().length;i++){
            pstmt.setString(1, userId);
            pstmt.setString(2, model.getSelectedRoles()[i]);
            pstmt.executeUpdate();
          }
        }
        con.commit();
      }catch(Exception e){
        con.rollback();
        throw e;
      }
      finally{
        DbPool.closeStatement(pstmt);
        DbPool.closeStatement(stmt);
        DbPool.closeConnection(con);
      }
    }

    public void updatePassword(String userid,String pwd)throws Exception{
        int rtn = 0;
        String sql = "update UserInfo_table"
                   + " set userPwd='" + pwd+"'";
        sql += " where  userId='" + userid+"'";
        rtn = DbManager.executeUpdate(sql);
     }

    public void update(UserModel model) throws Exception{
      String sql = "update UserInfo_table set userName='"+model.getUserName()+"',userType='"+model.getUserTypeId()+"'"
                 +" where userId='"+model.getUserId()+"'";
      String queryPer = "select userId from UserGroup_table where userId=? and groupId=?";
      String insertPer = "insert into UserGroup_table(userId,groupId) values(?,?)";
      String deletePer = "delete from UserGroup_table where userId='"+model.getUserId()+"'";
      Connection con = null;
      Statement stmt = null;
      ResultSet rs01 = null;
      Statement stmt01 = null;
      PreparedStatement pstmt = null;
      PreparedStatement pstmt01 = null;
      try{
        con = DbPool.getConnection();
        pstmt = con.prepareStatement(insertPer);
        stmt = con.createStatement();
        stmt.executeUpdate(deletePer);
        if(model.getSelectedRoles()!=null && model.getSelectedRoles().length>0){
          for(int i=0;i<model.getSelectedRoles().length;i++){
              pstmt.setString(1,model.getUserId());
              pstmt.setString(2,model.getSelectedRoles()[i]);
              pstmt.executeUpdate();
         }
        }
        stmt.executeUpdate(sql);
        con.commit();
      }catch(Exception e){
        throw e;
      }
      finally{
        DbPool.closeResultSet(rs01);
        DbPool.closeStatement(stmt);
        DbPool.closeStatement(pstmt);
        DbPool.closeStatement(pstmt01);
        DbPool.closeConnection(con);
      }
    }

    public void delete(String userId) throws Exception{
      int rtn = 0;
        String sql = "update UserInfo_table"
                   + " set userStatus='N',deleteTime='"+DateTimeUtility.getCurTimeStamp()+"'"
                   + " where  userId='" + userId+"'";
        rtn = DbManager.executeUpdate(sql);
    }

    public ArrayList query(UserModel model) throws Exception{
        ArrayList list = new ArrayList();
        String sql = "select um.userId,um.loginName,um.userName,um.userType,cmn.codeValue,um.createtime"
                    + " from UserInfo_table um,CommonCode_table cmn"
                     + " where um.userStatus='Y' and um.userType=cmn.codename and cmn.codeType='UserType'";
        if(model.getUserId()!=null && !model.getUserId().equals("")){
          sql += " and um.userId like '%"+model.getUserId()+"%'";
        }
        if(model.getUserName()!=null && !model.getUserName().equals("")){
          sql += " and um.UserName like '%"+model.getUserName()+"%'";
        }
        if(model.getLoginName()!=null && !model.getLoginName().equals("")){
          sql += " and um.loginName like '%"+model.getLoginName()+"%'";
        }
        if(model.getUserTypeId()!=null && !model.getUserTypeId().equals("")){
          sql += " and um.UserType='"+model.getUserTypeId()+"'";
        }
        ResultSet rs = null;
         Connection con = null;
         Statement statement = null;
         try{
            con = DbPool.getConnection();
            statement = con.createStatement();
            rs = statement.executeQuery(sql);
            while(rs.next()){
              UserModel usermodel = new UserModel();
              usermodel.setUserId(rs.getString(1));
              usermodel.setLoginName(rs.getString(2));
              usermodel.setUserName(rs.getString(3));
              usermodel.setUserTypeId(rs.getString(4));
              usermodel.setUserTypeName(rs.getString(5));
              usermodel.setCreateTime(rs.getString(6));
              list.add(usermodel);
            }
        }catch(Exception e){
            throw e;
        }
        finally{
          DbPool.closeResultSet(rs);
          DbPool.closeStatement(statement);
          DbPool.closeConnection(con);
        }
        return list;
    }

    public UserModel queryUser(String userId) throws Exception{
      UserModel model = null;
      String sql = "select userId,loginName,UserName,userPwd,UserType,userSex,userPhone,userEmail,userAddress,userZip,createTime,userStatus,userLevel from UserInfo_table";
      sql += " where userId = '"+userId+"'";
      ResultSet rs = null;
      Connection con = null;
      Statement statement = null;
      try{
        con = DbPool.getConnection();
        statement = con.createStatement();
        rs = statement.executeQuery(sql);
        while(rs.next()){
          model = new UserModel();
          model.setUserId(rs.getString("userId").trim());
          model.setLoginName(rs.getString("loginName").trim());
          model.setUserName(rs.getString("UserName").trim());
          model.setUserPwd(rs.getString("userPwd").trim());
          model.setUserTypeId(rs.getString("UserType"));
          model.setUserSex(rs.getString("userSex"));
          model.setUserPhone(rs.getString("userPhone"));
          model.setUserEmail(rs.getString("userEmail"));
          model.setUserAddress(rs.getString("userAddress"));
          model.setUserZip(rs.getString("userZip"));
          model.setCreateTime(rs.getString("createTime"));
          model.setUserStatus(rs.getString("userStatus").trim());
          model.setUserLevel(rs.getString("userLevel"));
        }
      }catch(Exception e){
          throw e;
      }
      finally{
        DbPool.closeResultSet(rs);
        DbPool.closeStatement(statement);
        DbPool.closeConnection(con);
      }
      return model;
    }


    public UserModel queryUserByLoginName(String loginName) throws Exception{
        UserModel model = null;
        String sql = "select userId,loginName,userName,userPwd,userType,userStatus from UserInfo_table";
        sql += " where loginName = '"+loginName+"'";
        ResultSet rs = null;
        Connection con = null;
        Statement statement = null;
        try{
            con = DbPool.getConnection();
            statement = con.createStatement();
            rs = statement.executeQuery(sql);
            while(rs.next()){
                model = new UserModel();
                model.setUserId(rs.getString("userId").trim());
                model.setLoginName(rs.getString("loginName").trim());
                model.setUserName(rs.getString("userName").trim());
                model.setUserPwd(rs.getString("userPwd").trim());
                model.setUserTypeId(rs.getString("userType"));
                model.setUserStatus(rs.getString("userStatus").trim());
            }

        }catch(Exception e){
            throw e;
        }
        finally{
          DbPool.closeResultSet(rs);
          DbPool.closeStatement(statement);
          DbPool.closeConnection(con);
        }
        return model;
    }

    public ArrayList queryMenuByUserType(String userType)
    {
      ArrayList lList = new ArrayList();
      HashMap lMapMenuId = new HashMap();
      String sqlSecond = "select menuId,menuName,menuImg,menuSelImg,menuAction,menuLevel,parentMenuId,isUserMenu from MenuShop_table where menuLevel='2'";
      String sqlFirst = "select menuId,menuName,menuImg,menuSelImg,menuAction,menuLevel,parentMenuId,menuLine,isUserMenu from MenuShop_table where menuLevel='1' ";
      if (!userType.equals("admin")) {
        sqlSecond += " and isUserMenu = '1'";
        sqlFirst += " and isUserMenu = '1'";
      }
      sqlSecond += " order by menuLine";
      sqlFirst += " or menuId in";
      ResultSet rs = null;
      Connection con = null;
      Statement statement = null;
      Menu model = null;
      try{
          con = DbPool.getConnection();
          statement = con.createStatement();
          rs = statement.executeQuery(sqlSecond);
            Debug.println(sqlSecond);
          String parentSql = "";
          while(rs.next()){
            model = new Menu();
            model.setMenuID(rs.getString(1).trim());
            model.setMenuName(rs.getString(2).trim());
            model.setMenuImg(rs.getString(3));
            model.setMenuSelImg(rs.getString(4));
            model.setMenuLink(rs.getString(5).trim());
            model.setMenuLevel(rs.getInt(6));
            String parentId = rs.getString(7).trim();
            model.setParentMenuID(parentId);
            model.setIsUserMenu(rs.getString(8).trim());

              if(lMapMenuId.get(model.getMenuID())==null){
                lList.add(model);
                if(parentSql.indexOf(parentId)==-1)
                  parentSql += "'"+parentId+"',";
          }
        }
         Debug.println(parentSql);
         DbPool.closeResultSet(rs);
         DbPool.closeStatement(statement);
         if(parentSql.equals(""))
           return lList;
         parentSql = "("+parentSql.substring(0,parentSql.length()-1)+") order by menuLine";
         statement = con.createStatement();
         rs = statement.executeQuery(sqlFirst+parentSql);
          Debug.println(sqlFirst+parentSql);
          while(rs.next()){
            model = new Menu();
            model.setMenuID(rs.getString(1).trim());
            model.setMenuName(rs.getString(2));
            model.setMenuImg(rs.getString(3).trim());
            model.setMenuSelImg(rs.getString(4).trim());
            model.setMenuLink(rs.getString(5).trim());
            model.setMenuLevel(rs.getInt(6));
            String parentId = rs.getString(7);
            model.setParentMenuID(parentId);
            model.setIsUserMenu(rs.getString(9).trim());
           lList.add(model);
         }
      }
      catch(Exception e){
        e.printStackTrace();
//      throw e;
      }
      finally{
        DbPool.closeResultSet(rs);
        DbPool.closeStatement(statement);
        DbPool.closeConnection(con);
      }
      return lList;
    }

}