buguserdao.java

来自「是一个Bug系统」· Java 代码 · 共 289 行

package com.runwit.bugreport.services.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.List;
import java.util.Set;

import com.runwit.bugreport.model.BugPrivModel;
import com.runwit.bugreport.model.BugStatusModel;
import com.runwit.bugreport.model.BugUserModel;
import com.runwit.bugreport.model.GroupModel;
import com.runwit.bugreport.services.IBugUserServices;
import com.runwit.common.db.DbDAO;
import com.runwit.common.util.DebugUtil;

public class BugUserDAO extends DbDAO implements IBugUserServices {
	
	private BugPrivModel[] parsePrivString(String str) {
		BugPrivModel[] models = null;
		if(str != null && str.length() > 0) {
			String[] privs = str.split(",");
			models = new BugPrivModel[privs.length];
			for(int i=0; i<privs.length; i++) {
				if(privs[i].length() > 0) { //不为""
					int privId = Integer.parseInt(privs[i]);
					models[i] = BugPrivModel.getModel(privId);
				}
			}
		}
		return models;
	}

	public Object mapRowToModel(ResultSet rs) throws SQLException {
		
		String bugPriv = rs.getString("bugpriv");
		
		
		BugUserModel model = new BugUserModel(
				rs.getLong("userid"),
				rs.getString("account"),
				rs.getString("password"),
				rs.getString("name"),
				rs.getString("sex"),
				rs.getString("dept"),
				rs.getString("duty"),
				rs.getString("phone"),
				rs.getString("mpPhone"),
				parsePrivString(bugPriv),
				rs.getString("email"),
				rs.getLong("createTime"),
				rs.getInt("lockstate")
				);
		
		return model;
	}
	
	public boolean createUser(BugUserModel model) {
		String sql = "INSERT INTO BUGUSERS(ACCOUNT,PASSWORD,NAME, " +
				"SEX, DEPT, DUTY, PHONE, MPHONE, BUGPRIV, EMAIL, CREATETIME, LOCKSTATE ) values(?,?,?,?,?,?,?,?,?,?,?,?)";
		String querySql = "select max(userid) from bugusers";
		String usergroupSql = "insert into usergroups(groupid, userid) values(?, ?)";
		
		try
		{
			openConnection();
			conn.setAutoCommit(false);
			openPreparedStatement(sql);
			pstmt.setString(1, model.getAccount());
			pstmt.setString(2, model.getPassword());
			pstmt.setString(3, model.getName());
			pstmt.setString(4, model.getSex());
			pstmt.setString(5, model.getDept());
			pstmt.setString(6, model.getDuty());
			pstmt.setString(7, model.getPhone());
			pstmt.setString(8, model.getMpPhone());
			pstmt.setString(9, BugPrivModel.convertToPrivString(model.getBugPrivs()));
			pstmt.setString(10, model.getEmail());
			pstmt.setLong(11, model.getCreateTime());
			pstmt.setInt(12, model.getLockState());
			
			int iRet = pstmt.executeUpdate();
			closePreparedStatement();
			openPreparedStatement(querySql);
			rs  = pstmt.executeQuery();
			if(rs.next()) {
				model.setUserId(rs.getInt(1));
			}
			closeResultSet();
			closePreparedStatement();
			
			if(model.getGroups().size() > 0) {
				openPreparedStatement(usergroupSql);
				Iterator gIter = model.getGroups().iterator();
				while(gIter.hasNext()) {
					GroupModel gModel = (GroupModel)gIter.next();
					pstmt.setLong(1, model.getUserId());
					pstmt.setInt(2, gModel.getGroupId());
					pstmt.addBatch();
				}
				pstmt.executeBatch();
				closePreparedStatement();
			}
			
			conn.commit();
			return true;
		}catch(SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		finally{
			closeConnection();
		}
	}
	
	public boolean setUserById(BugUserModel model) {
		String sql = "UPDATE BUGUSERS SET ACCOUNT=?, PASSWORD=?,NAME=?, " +
		"SEX=?, DEPT=?, DUTY=?, PHONE=?, MPHONE=?, BUGPRIV=?, EMAIL=?,  LOCKSTATE=? WHERE USERID=?";
		String delSql = "delete from usergroups where userid=?";
		String sql2 = "insert into usergroups(groupId, userid) values(?,?)";
		
		try
		{
			openConnection();
			conn.setAutoCommit(false);
			openPreparedStatement(sql);
			pstmt.setString(1, model.getAccount());
			pstmt.setString(2, model.getPassword());
			pstmt.setString(3, model.getName());
			pstmt.setString(4, model.getSex());
			pstmt.setString(5, model.getDept());
			pstmt.setString(6, model.getDuty());
			pstmt.setString(7, model.getPhone());
			pstmt.setString(8, model.getMpPhone());
			pstmt.setString(9, BugPrivModel.convertToPrivString(model.getBugPrivs()));
			pstmt.setString(10, model.getEmail());
			//pstmt.setLong(11, model.getCreateTime());
			pstmt.setInt(11, model.getLockState());
			pstmt.setLong(12, model.getUserId());
			int iRet = pstmt.executeUpdate();
			closePreparedStatement();
			openPreparedStatement(delSql);
			pstmt.setLong(1, model.getUserId());
			pstmt.executeUpdate();
			closePreparedStatement();
			Iterator iter = model.getGroups().iterator();
			if(model.getGroups().size() > 0) {
				openPreparedStatement(sql2);
				while(iter.hasNext()) {
					GroupModel gModel =(GroupModel)iter.next();
					pstmt.setInt(1, gModel.getGroupId());
					pstmt.setLong(2, model.getUserId());
					pstmt.addBatch();
				}
				pstmt.executeBatch();
				closePreparedStatement();
			}
			conn.commit();
			return true;
		}catch(SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		finally{
			closeConnection();
		}
	}
	
	public boolean removeUser(BugUserModel model) {
		String sql1 = "delete from usergroups where userid=" + model.getUserId();
		String sql2 = "delete from buguser where userid=" + model.getUserId();
		try
		{
			openConnection();
			openStatement();
			stmt.addBatch(sql1);
			stmt.addBatch(sql2);
			stmt.executeBatch();
			
			return true;
			
		}catch(SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		finally {
			closeStatement();
			closeConnection();
		}
	}
	
	private List listUsersBySql(String sql) {
		List groups = new ArrayList();
		try {
			openConnection();
			openStatement();
			DebugUtil.printSql(sql);
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				GroupModel groupModel = (GroupModel)mapRowToModel(rs);
				groups.add(groupModel);
				
			}
			closeResultSet();
			for(int i=0; i<groups.size(); i++) {
				try{
					rs = stmt
							.executeQuery("select bugstatus.* from bugstatus, groupstatus where bugstatus.bsid=groupstatus.bsid and groupid="
									+ ((GroupModel) groups.get(i)).getGroupId());
					while(rs.next()) {
						BugStatusModel bsModel = new BugStatusModel(rs
								.getInt("bsid"), rs.getString("name"), rs
								.getString("remark"));
						((GroupModel)groups.get(i)).getStatusList().add(bsModel);
					}
				}catch(SQLException ex) {
					ex.printStackTrace();
				}finally{
					closeResultSet();
				}
			}

		}catch(SQLException ex) {
			ex.printStackTrace();
		}
		finally {
			closeStatement();
			closeConnection();
		}
		
		return groups;

	}
	
	
	public List listUsers() {
		Set users = new LinkedHashSet();
		BugUserModel model;
		String sql = "select u.*, ug.groupid from bugusers as u left outer join usergroups ug on u.userid=ug.userid";
		try{
			openConnection();
			openStatement();
			rs = stmt.executeQuery(sql);
			while(rs.next()) {
				model = (BugUserModel)mapRowToModel(rs);
			}
			
		}catch(SQLException ex) {
			ex.printStackTrace();
		}finally{
			
		}
		return null;

	}
	
	public boolean changeLockState(int newState) {
		// TODO Auto-generated method stub
		return false;
	}
	
	
	
	public GroupModel getModel(int groupId) {
		
//		List models = listGroupsBySql("select * from groups where groupid="+groupId);
//		if(models != null && models.size() > 0) 
//			return (GroupModel)models.get(0);
		
		return null;
	}
	
	public GroupModel getModel(long userId) {
		// TODO Auto-generated method stub
		return null;
	}
	
	public boolean login(String account, String password) {
		// TODO Auto-generated method stub
		return false;
	}
	
	


}