commlinkmandao.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.Menu;
import com.oa.lp.model.Role;
import com.oa.lp.util.DTOPopulator;
import com.oa.lp.util.PageList;
import com.oa.lp.util.Pages;

	public class CommLinkManDAO {
		private Connection conn;
	
		public Connection getConn() {
			return conn;
		}
	
		public void setConn(Connection conn) {
			this.conn = conn;
		}
		
		
		/**
		 * 新增公共联系人
		 * @param menu
		 * @throws SQLException
		 */
		public void addComms(CommLinkMan commLinkMan) throws SQLException{
			String sql = "insert into COMM_LINKMAN(COMPANY_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,commLinkMan.getCompanyId());
			pstmt.setString(2,commLinkMan.getLinkManName());
			pstmt.setString(3,commLinkMan.getLinkManEmail());
			pstmt.setString(4,commLinkMan.getLinkManQq());
			pstmt.setString(5,commLinkMan.getLinkManMobile());
			pstmt.setString(6,commLinkMan.getLinkManTel());
			pstmt.setString(7,commLinkMan.getLinkManBirthday());
			pstmt.setString(8,commLinkMan.getHomeAddress());
			pstmt.setString(9,commLinkMan.getComName());
			pstmt.setString(10,commLinkMan.getComAddress());
			pstmt.setInt(11,commLinkMan.getLinkManState());
			pstmt.executeUpdate();
			pstmt.close();
		}
		/**
		 * 通过ID查询联系人
		 * @throws Exception 
		 */
		public CommLinkMan getCommLinkManById(int commLinkManId) throws Exception{
			CommLinkMan commLinkMan = new CommLinkMan();
			List list=null;
			String sql = "select *from COMM_LINKMAN where COMM_LINKMAN_ID=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,commLinkManId);
			ResultSet rs = pstmt.executeQuery();
			list = DTOPopulator.populate(rs, CommLinkMan.class);
			if(list.size()>0){
				commLinkMan = (CommLinkMan)list.get(0);
			}
			rs.close();
			pstmt.close();
			return commLinkMan;
		}
		
		/**
		 * 更新公共联系人
		 * @param commLinkMan
		 * @throws SQLException
		 */
		public void updatecommLinkMan(CommLinkMan commLinkMan) throws SQLException{
			String sql = "update COMM_LINKMAN set COMPANY_ID=?,LINKMAN_NAME=?,LINKMAN_EMAIL=?," +
					"LINKMAN_QQ=?,LINKMAN_MOBILE=?,LINKMAN_TEL=?,LINKMAN_BIRTHDAY=?,HOME_ADDRESS=?,COM_NAME=?," +
					"COM_ADDRESS=?,LINKMAN_STATE=? where COMM_LINKMAN_ID=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,commLinkMan.getCompanyId());
			pstmt.setString(2,commLinkMan.getLinkManName());
			pstmt.setString(3,commLinkMan.getLinkManEmail());
			pstmt.setString(4,commLinkMan.getLinkManQq());
			pstmt.setString(5,commLinkMan.getLinkManMobile());
			pstmt.setString(6,commLinkMan.getLinkManTel());
			pstmt.setString(7,commLinkMan.getLinkManBirthday());
			pstmt.setString(8,commLinkMan.getHomeAddress());
			pstmt.setString(9,commLinkMan.getComName());
			pstmt.setString(10,commLinkMan.getComAddress());
			pstmt.setInt(11,commLinkMan.getLinkManState());
			pstmt.setInt(12, commLinkMan.getCommLinkManId());
			
			pstmt.executeUpdate();
			pstmt.close();
		}
		
		/**
		 * 联系人分页列表
		 * @param page
		 * @return
		 * @throws Exception
		 */
		public PageList listAllComms(Pages page) throws Exception{
			PageList pageList = new PageList();
			//总录数
			page.setAllRecord(countAllComms());
			page.doPage();
			
			StringBuffer sql = new StringBuffer();
			sql.append("select * from (");
			sql.append("select top "+page.getPageSize()+" * from (");
			sql.append("select top "+(page.getPageSize()*page.getCPage())+" * from ");
			sql.append("COMM_LINKMAN order by COMM_LINKMAN_ID desc) t1 order by COMM_LINKMAN_ID asc) t2" +
					" order by COMM_LINKMAN_ID desc");
			
			PreparedStatement pstmt = conn.prepareStatement(sql.toString());
			ResultSet rs = pstmt.executeQuery();
			//将结果集中的每一行记录封装成一个对象,再放进集合返回
			List list = DTOPopulator.populate(rs, CommLinkMan.class);
			
			pageList.setPage(page);
			pageList.setObjectList(list);
			pstmt.close();
			return pageList;
		}
		
		public List listAllComms() throws Exception{
			List list = null; 
			String sql = "select * from COMM_LINKMAN";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			//将结果集中的每一行记录封装成一个对象,再放进集合返回
			list = DTOPopulator.populate(rs, CommLinkMan.class);
			pstmt.close();
			return list;
		}
		
		/**
		 * 联系人总记录数
		 * @return
		 * @throws SQLException 
		 */
		public int countAllComms() throws SQLException{
			int count = 0;
			String sql = "select count(*) from COMM_LINKMAN";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery(); 
			if(rs.next()){
				count = rs.getInt(1);
			}
			pstmt.close();
			return count;
		}
		/**
		 * 删除联系人
		 * @throws SQLException 
		 */
		public boolean delComms(int commLinkManId) throws SQLException{
			boolean flag = false;
			String sql="delete from COMM_LINKMAN where COMM_LINKMAN_ID=?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,commLinkManId);
			pstmt.executeUpdate();
			pstmt.close();
			flag=true;
			return flag;
		}
}