linkmangroupdao.java

一个jsp的oa系统,里面有很多亮点学习!

package com.oa.lp.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.oa.lp.model.CommLinkMan;
import com.oa.lp.model.Company;
import com.oa.lp.model.LinkManGroup;
import com.oa.lp.util.DTOPopulator;

public class LinkManGroupDAO {
	
	private Connection conn;

	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}
	
	/**
	 * 通过ID联系人组ID查找联系人组
	 * @param groupId
	 * @return
	 * @throws Exception
	 */
	public LinkManGroup getLinkManGroupById(int groupId) throws Exception{
		LinkManGroup linkManGroup = new LinkManGroup();
		List list=null;
		String sql = "select * from LINKMAN_GROUP where GROUP_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,groupId);
		ResultSet rs = pstmt.executeQuery();
		list = DTOPopulator.populate(rs, LinkManGroup.class);
		if(list.size()>0){
			linkManGroup = (LinkManGroup)list.get(0);
		}
		rs.close();
		pstmt.close();
		
		return linkManGroup;
	}
	
	/**
	 * 更新联系人组
	 * @param linkManGroup
	 * @throws SQLException
	 */
	public void updateGroup(LinkManGroup linkManGroup,int empId) throws SQLException{
		String sql = "update LINKMAN_GROUP set EMP_ID=?,GROUP_NAME=? where GROUP_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,empId);
		pstmt.setString(2,linkManGroup.getGroupName());
		pstmt.setInt(3,linkManGroup.getGroupId());
		pstmt.executeUpdate();
		pstmt.close();
	}
	/**
	 * 删除
	 * @throws SQLException 
	 */
	public boolean delLinkManGroupById(int groupId) throws SQLException{
		boolean flag=true;
		//是否有下级菜单
		String sql = "select count(*) from LINKMAN where GROUP_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,groupId);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			if(rs.getInt(1)>0){
				//有下级不能删除
				flag = false;
			}
		}
		rs.close();
		pstmt.close();
		
		if(flag){
			//删除
			sql = "delete from LINKMAN_GROUP where GROUP_ID=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,groupId);
			pstmt.executeUpdate();
			pstmt.close();
			
		}
		return flag;
	}
	/**
	 * 新增联系人组
	 */
	public void addGroup(LinkManGroup linkManGroup,int empId) throws SQLException{
		String sql = "insert into LINKMAN_GROUP(EMP_ID,GROUP_NAME)values(?,?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,empId);
		pstmt.setString(2,linkManGroup.getGroupName());
		pstmt.executeUpdate();
		pstmt.close();
	}
	/**
	 * 列出所有联系人组
	 */
	public List listAllGroups(int empId) throws Exception{
		List list = null; 
		String sql = "select * from LINKMAN_GROUP where EMP_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,empId);
		ResultSet rs = pstmt.executeQuery();
		//将结果集中的每一行记录封装成一个对象,再放进集合返回
		list = DTOPopulator.populate(rs, LinkManGroup.class);
		pstmt.close();
		return list;
	}
}