buguserdao.java

是一个Bug系统

package com.runwit.ebookstore.services.dao;

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

import com.runwit.ebookstore.model.BugPrivModel;
import com.runwit.ebookstore.model.BugStatusModel;
import com.runwit.ebookstore.model.BugUserModel;
import com.runwit.ebookstore.model.GroupModel;
import com.runwit.ebookstore.services.IBugUserServices;
import com.runwit.ebookstore.services.IGroupServices;

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 setGroupById(GroupModel model) {
		String sql = "update groups set name=? where groupid=?";
		String delSql = "delete from groupstatus where groupid=?";
		String sql2 = "insert into groupstatus(groupId, bsid) values(?,?)";
		
		try
		{
			openConnection();
			conn.setAutoCommit(false);
			openPreparedStatement(sql);
			pstmt.setString(1, model.getName());
			pstmt.setInt(2, model.getGroupId());
			int iRet = pstmt.executeUpdate();
			closePreparedStatement();
			openPreparedStatement(delSql);
			pstmt.setInt(1, model.getGroupId());
			pstmt.executeUpdate();
			closePreparedStatement();
			List sList = model.getStatusList();
			if(iRet == 1) {
				openPreparedStatement(sql2);
				for(int i=0; i<sList.size(); i++) {
					pstmt.setInt(1, model.getGroupId());
					pstmt.setInt(2, ((BugStatusModel)(sList.get(i))).getBsid());
					pstmt.addBatch();
				}
				pstmt.executeBatch();
				closePreparedStatement();
			}
			conn.commit();
			return true;
		}catch(SQLException ex) {
			ex.printStackTrace();
			return false;
		}
		finally{
			closeConnection();
		}
	}
	
	public boolean removeGroup(GroupModel model) {
		String sql1 = "delete from groupstatus where groupid=" + model.getGroupId();
		String sql2 = "delete from groups where groupid=" + model.getGroupId();
		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 listGroupsBySql(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 listGroups() {
		
		return listGroupsBySql("select * from groups");

	}
	
	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;
	}


}