linkmandao.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.ArrayList;
import java.util.List;

import com.oa.lp.model.LinkMan;
import com.oa.lp.model.LinkManGroup;
import com.oa.lp.util.DTOPopulator;

public class LinkManDAO {
		private Connection conn;

		public Connection getConn() {
			return conn;
		}

		public void setConn(Connection conn) {
			this.conn = conn;
		}
		
		public LinkMan getLinkManById(int linkManId) throws Exception{
			LinkMan linkMan = new LinkMan();
			List list=null;
			String sql = "select * from LINKMAN where LINKMAN_ID=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,linkManId);
			ResultSet rs = pstmt.executeQuery();
			list = DTOPopulator.populate(rs, LinkMan.class);
			if(list.size()>0){
				linkMan = (LinkMan)list.get(0);
			}
			rs.close();
			pstmt.close();
			
			return linkMan;
		}
		
		/**
		 * 通过组ID得到联系人 
		 * @param groupId
		 * @param empId
		 * @return
		 * @throws Exception
		 */
		
		public List getLinkmansByGroupId(int groupId ,int empId) throws Exception{
			List list = null;
			String sql=""; 
			if(groupId==0){
				sql="select l.* from LINKMAN as l,LINKMAN_GROUP as g where l.GROUP_ID=g.GROUP_ID and  g.EMP_ID ="+empId;
			}else{
				sql="select l.* from LINKMAN as l,LINKMAN_GROUP as g where  l.GROUP_ID=g.GROUP_ID and  g.EMP_ID ="+empId +" and g.GROUP_ID="+groupId;
			}
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			list = DTOPopulator.populate(rs,LinkMan.class);
			rs.close();
			pstmt.close();
			return list;
		}
		
		public void updateLinkMan(LinkMan linkMan) throws Exception{
			String sql = "update LINKMAN set GROUP_ID=?,LINKMAN_NAME=?,LINKMAN_EMAIL=?,LINKMAN_QQ=?,LINKMAN_MOBILE=?," +
					"LINKMAN_TEL=?,LINKMAN_BIRTHDAY=?,HOME_ADDRESS=?,COM_NAME=?,COM_ADDRESS=?,LINKMAN_STATE=? where LINKMAN_ID=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,linkMan.getGroupId());
			pstmt.setString(2,linkMan.getLinkManName());
			pstmt.setString(3,linkMan.getLinkManEmail());
			pstmt.setString(4,linkMan.getLinkManQq());
			pstmt.setString(5,linkMan.getLinkManMobile());
			pstmt.setString(6,linkMan.getLinkManTel());
			pstmt.setString(7,linkMan.getLinkManBirthday());
			pstmt.setString(8,linkMan.getHomeAddress());
			pstmt.setString(9,linkMan.getComName());
			pstmt.setString(10,linkMan.getComAddress());
			pstmt.setInt(11,linkMan.getLinkManState());
			pstmt.setInt(12, linkMan.getLinkManId());
			
			pstmt.executeUpdate();
			pstmt.close();
		}
		
		/**
		 * 列出所有联系人
		 */
		public List listAllLinkMans() throws Exception{
			List list = null; 
			String sql = "select * from LINKMAN";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			//将结果集中的每一行记录封装成一个对象,再放进集合返回
			list = DTOPopulator.populate(rs, LinkMan.class);
			pstmt.close();
			return list;
		}
		
		/**
		 * 通过联系人中的组ID得到联系人组的信息
		 * @throws Exception 
		 */
		public List getGroupIdById(int linkManId) throws SQLException{
			List list = new ArrayList();
			String sql = "select GROUP_ID from  LINKMAN where LINKMAN_ID=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,linkManId);
			ResultSet rs = pstmt.executeQuery();
			while(rs.next()){
				int powerId = rs.getInt("LINKMAN_ID");
				list.add(String.valueOf(linkManId));
			}
			
			return list;
		}
		
		/**
		 * 新增联系人
		 * @throws SQLException 
		 */
		public void addLinkMan(LinkMan linkMan) throws SQLException{
			String sql = "insert into LINKMAN(GROUP_ID,LINKMAN_NAME,LINKMAN_EMAIL,LINKMAN_QQ," +
			"LINKMAN_MOBILE,LINKMAN_TEL,LINKMAN_BIRTHDAY,HOME_ADDRESS,COM_NAME," +
			"COM_ADDRESS,LINKMAN_STATE)values(?,?,?,?,?,?,?,?,?,?,?)";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,linkMan.getGroupId());
			pstmt.setString(2,linkMan.getLinkManName());
			pstmt.setString(3,linkMan.getLinkManEmail());
			pstmt.setString(4,linkMan.getLinkManQq());
			pstmt.setString(5,linkMan.getLinkManMobile());
			pstmt.setString(6,linkMan.getLinkManTel());
			pstmt.setString(7,linkMan.getLinkManBirthday());
			pstmt.setString(8,linkMan.getHomeAddress());
			pstmt.setString(9,linkMan.getComName());
			pstmt.setString(10,linkMan.getComAddress());
			pstmt.setInt(11,linkMan.getLinkManState());
			
			pstmt.executeUpdate();
			pstmt.close();
		}
		/**
		 * 删除联系人
		 * @throws SQLException 
		 */
		public void delLinkMan(int linkManId) throws SQLException{
			String sql = "delete from LINKMAN where LINKMAN_ID=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,linkManId);
			pstmt.executeUpdate();
			pstmt.close();
		}
		
	}