getusermanagedao.java

公司自己开发的工作流引擎

package cn.com.iaspec.workflow.manage.dao;

import java.sql.*;
import java.util.*;
import cn.com.iaspec.workflow.db.*;
import cn.com.iaspec.workflow.manage.*;
import cn.com.iaspec.workflow.util.*;
import cn.com.iaspec.workflow.vo.workflow.*;

/**
 * <p>Title:用户信息操作类 </p>
 *
 * <p>Description: 深圳市劳动仲裁信息管理系统</p>
 *
 * <p>Copyright: Copyright (c) 2005</p>
 *
 * <p>Company: 永泰软件工程有限公司</p>
 *
 * @author syj
 * @version 1.0
 */
public class GetUserManageDAO{
  /**
   * 添加人员信息
   * @param userinfo UserInfo
   */
  public int insertUserInfo(UserInfo userinfo)
      throws PurviewManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    int i=0;
    String sql=
        "insert into wf_user (user_id,user_name,user_state,user_position,password,"+" login_id,description,rela_phone,e_mail,user_order) values (?,?,?,?,?,?,?,?,?,?)";

    try{
      if(!queryUserinfo(userinfo.getLoginName().trim())){
        String userId=userinfo.getUserId();
        if(userId==null||userId.equals("")){
          userId=GetTableSequenceId.getTableId("wf_user");
        }
        conn=WorkflowDBConnectionManager.getInstance().getConnection();
        prep=conn.prepareStatement(sql);
        prep.setString(1,userId);
        prep.setString(2,userinfo.getUserName());
        prep.setString(3,userinfo.getState());
        prep.setString(4,userinfo.getUserPosition());
        prep.setString(5,userinfo.getPassword());
        prep.setString(6,userinfo.getLoginName());
        prep.setString(7,userinfo.getDescription());
        prep.setString(8,userinfo.getRela_phone());
        prep.setString(9,userinfo.getE_mail());
        prep.setInt(10,userinfo.getUser_order());
        i=prep.executeUpdate();

      }
      else{
        i=-2;
      }
    }
    catch(Exception e){
      e.printStackTrace();
      throw new PurviewManageException("添加人员异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);

    }
    return i;

  }

  /**
   * 判断人员是否存在
   * @param longinname String
   * @return boolean
   * @throws PurviewManageException
   */
  public boolean queryUserinfo(String longinname)
      throws PurviewManageException{
    boolean flag=false;
    String sql="select * from wf_user where login_id=?";
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,longinname);
      rs=prep.executeQuery();
      while(rs.next()){
        flag=true;
      }

    }
    catch(Exception e){
      e.printStackTrace();
      throw new PurviewManageException("判断人员是否存在异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
    return flag;
  }

  /**
   * 更新人员信息
   * @param userinfo UserInfo
   * @return int
   */
  public int updateUserInfo(UserInfo userinfo){
    int i=0;
    PreparedStatement prep=null;
    Connection conn=null;
    String sql=
        "update wf_user set user_name=?,user_state=?,user_position=?,password=?,"+
        " login_id=?,description=?,rela_phone=?,e_mail=?,user_order=? where user_id=? ";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,userinfo.getUserName());
      prep.setString(2,userinfo.getState());
      prep.setString(3,userinfo.getUserPosition());
      prep.setString(4,userinfo.getPassword());
      prep.setString(5,userinfo.getLoginName());
      prep.setString(6,userinfo.getDescription());
      prep.setString(7,userinfo.getRela_phone());
      prep.setString(8,userinfo.getE_mail());
      prep.setInt(9,userinfo.getUser_order());
      prep.setString(10,userinfo.getUserId());
      i=prep.executeUpdate();

    }
    catch(Exception e){
      e.printStackTrace();
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);

    }

    return i;

  }

  /**
   * 删除用户信息
   * @param user_id String
   * @return int
   */
  public int delete(String user_id)
      throws PurviewManageException{
    int i=0;
    PreparedStatement prep=null;
    Connection conn=null;
    String sql="delete wf_user  where user_id in("+user_id+")";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      i=prep.executeUpdate();
      deleteLa_org_user(conn,user_id); //删除用户的关联信息
    }
    catch(Exception e){
      throw new PurviewManageException("删除用户异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);

    }

    return i;
  }

  /**
   * 删除该人员的所有关联信息
   * @param user_id String
   */
  public int deleteLa_org_user(Connection conn,String user_id)
      throws PurviewManageException{
    PreparedStatement prep=null;
    String sql="delete wf_org_user_role  where user_id in("+user_id+")";
    int i=0;
    try{
      prep=conn.prepareStatement(sql);
      i=prep.executeUpdate();
    }
    catch(Exception e){
      throw new PurviewManageException("删除用户所有的关联信息异常");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
    }
    return i;
  }

  /**
   * 删除关联信息
   * @param conn Connection
   * @param user_id String
   * @return int
   */
  public int deleteLa_org_user(String org_id,String user_id)
      throws PurviewManageException{
    int i=0;
    PreparedStatement prep=null;
    Connection conn=null;
    String sql="delete wf_org_user_role   where user_id=? and org_id=?";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,user_id);
      prep.setString(2,org_id);
      i=prep.executeUpdate();
    }
    catch(Exception e){
      e.printStackTrace();
      throw new PurviewManageException("--删除用户和组织的关系异常--");
    }
    return i;
  }

  /**
   * 查寻用户信息
   * @param user_id String
   * @return UserInfo
   */
  public UserInfo queryUserInfo(String user_id){
    UserInfo userinfo=new UserInfo();
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    String sql="select * from wf_user where user_id=?";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,user_id);
      rs=prep.executeQuery();
      while(rs.next()){
        userinfo.setUserId(rs.getString("user_id"));
        userinfo.setLoginName(rs.getString("login_id"));
        userinfo.setUserName(rs.getString("user_name"));
        userinfo.setPassword(rs.getString("password"));
        userinfo.setDescription(rs.getString("description"));
        userinfo.setE_mail(rs.getString("e_mail"));
        userinfo.setState(rs.getString("user_state"));
        userinfo.setRela_phone(rs.getString("rela_phone"));
        userinfo.setUser_order(rs.getInt("user_order"));
        userinfo.setUserPosition(rs.getString("user_position"));
      }

    }
    catch(Exception e){
      e.printStackTrace();
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
    return userinfo;
  }

  /**
   * 添加用户角色信息关联表
   * @param la_org_user La_Org_User_Role
   * @return int
   */
  public int inserRelatingOrganiztion(WfOrgUserRole la_org_user)
      throws PurviewManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    int i=0;
    String sql=
        "insert into wf_org_user_role(user_id,org_id,role_id) values(?,?,?)";
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      if(!checkorg_user(conn,la_org_user.getOrg_id(),la_org_user.getUser_id())){
        prep=conn.prepareStatement(sql);
        prep.setString(1,la_org_user.getUser_id());
        prep.setString(2,la_org_user.getOrg_id());
        prep.setString(3,la_org_user.getRole());
        i=prep.executeUpdate();
      }
      else{
        i=1;
      }
    }
    catch(Exception e){
      throw new PurviewManageException("-添加用户角色信息关联表异常--");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }
    return i;
  }

  /**
   * 根据区号查询用户信息
   * @param area_id String
   * @return ArrayList
   * @throws PurviewManageException
   */
  public ArrayList queryUserInfo_areaid(String area_id)
      throws PurviewManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    ArrayList list=new ArrayList();
    String sql=
        "select * from wf_user where user_id in(select distinct b.user_id"+
        "  from wf_organization a,wf_org_user_role b where b.org_id=a.org_id "+
        " and a.area_id=?) order by user_id desc";
    int i=0;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      prep.setString(1,area_id);
      rs=prep.executeQuery();
      while(rs.next()){
        i++;
        UserInfo userinfo=new UserInfo();
        userinfo.setId(i);
        userinfo.setUserId(rs.getString("user_id"));
        userinfo.setUserName(rs.getString("user_name"));
        userinfo.setDescription(rs.getString("description"));
        userinfo.setUserPosition(rs.getString("user_position"));
        userinfo.setRela_phone(rs.getString("rela_phone"));
        list.add(userinfo);
      }
    }
    catch(Exception e){
      e.printStackTrace();
      throw new PurviewManageException("---按行政区号查询用户信息异常--");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return list;
  }

  /**
   * 查寻用户信息
   * @return ArrayList
   * @throws PurviewManageException
   */
  public ArrayList queryUserInfo()
      throws PurviewManageException{
    PreparedStatement prep=null;
    Connection conn=null;
    ResultSet rs=null;
    ArrayList list=new ArrayList();
    String sql="select * from wf_user ";
    int i=0;
    ;
    try{
      conn=WorkflowDBConnectionManager.getInstance().getConnection();
      prep=conn.prepareStatement(sql);
      rs=prep.executeQuery();
      while(rs.next()){
        i++;
        UserInfo userinfo=new UserInfo();
        userinfo.setId(i);
        userinfo.setUserId(rs.getString("user_id"));
        userinfo.setUserName(rs.getString("user_name"));
        userinfo.setDescription(rs.getString("description"));
        userinfo.setUserPosition(rs.getString("user_position"));
        userinfo.setRela_phone(rs.getString("rela_phone"));
        list.add(userinfo);
      }

    }
    catch(Exception e){
      throw new PurviewManageException("---查询用户信息异常--");
    }
    finally{
      WorkflowDBConnectionManager.getInstance().close(rs);
      WorkflowDBConnectionManager.getInstance().close(prep,false);
      WorkflowDBConnectionManager.getInstance().close(conn);
    }

    return list;

  }

  /**
   * 判断组织和用户的关系是否存在
   * @param conn Connection
   * @param org_id String
   * @param user_id String
   * @return boolean
   */
  public boolean checkorg_user(Connection conn,String org_id,String user_id){
    boolean flag=false;
    PreparedStatement prep=null;
    ResultSet rs=null;
    String sql="select * from wf_org_user_role where user_id=? and org_id=?";
    try{
      prep=conn.prepareStatement(sql);
      prep.setString(1,user_id);
      prep.setString(2,org_id);
      rs=prep.executeQuery();
      while(rs.next()){
        flag=true;
      }
    }
    catch(Exception e){
      e.printStackTrace();
    }

    return flag;
  }

  public static void main(String[] args)
      throws PurviewManageException{
    GetUserManageDAO GetUserManageDAO=new GetUserManageDAO();
    UserInfo userinfo=new UserInfo();
    //('1000000018','陈文雅','c','开发人员','123','cwy','sdhjasds','212121','[e-mail]',5)";
    userinfo.setUserId("1000000018");
    userinfo.setLoginName("syj");
    userinfo.setUserName("chengwu");
    userinfo.setDescription("更新数据测试");
    System.out.println(GetUserManageDAO.insertUserInfo(userinfo));
    //GetUserManageDAO.updateUserInfo(userinfo);
    //GetUserManageDAO.deleteUserInfo(userinfo.getUserId());
    UserInfo user=GetUserManageDAO.queryUserInfo(userinfo.getUserId());
    System.out.println("getUserId"+user.getUserId());
    System.out.println("getLoginName"+user.getLoginName());
    System.out.println("getUserName"+user.getUserName());
    System.out.println("getDescription"+user.getDescription());
  }
}