departmentdao.java

财务管理系统

/************
 * author:dengwen
 * beginTime:2008-11-30
 */
package org.fms.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 org.fms.model.DepartmentInfo;

public class DepartmentDao {
	private PreparedStatement ps;
	private ResultSet rs;
	private Connection conn;
	public Connection getConn() {
		return conn;
	}
	public void setConn(Connection conn) {
		this.conn = conn;
	}
	
	/***********
	 * 关闭ps与rs
	 *
	 */
	public void closePSandRS(){
		if(ps!=null){
			try {
				ps.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	/************
	 * 获得所有部门个数
	 * @return
	 */
	public int getDepartmentCount(){
		int count=0;
		String sql="select count(*) from department";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()){
				count = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return count;
	}
	/****************
	 * 获取部门的信息
	 * @return
	 */
	public List getDepartment(int currentPage,int pageSize){
		List list = new ArrayList();
		String sql="select top "+pageSize+" * from department where  departmentID not in (select top "+pageSize*(currentPage-1)+" departmentID from department)";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			DepartmentInfo dinfo = null;
			while(rs.next()){
				dinfo = new DepartmentInfo();
				dinfo.setDepartmentID(rs.getInt("departmentID"));
				dinfo.setDepartmentCode(rs.getString("departmentCode"));
				dinfo.setDepartmentName(rs.getString("departmentName"));
				dinfo.setOperater(rs.getInt("operater"));
				dinfo.setRemark(rs.getString("remark"));
				dinfo.setIsDelete(rs.getInt("isDelete"));
				list.add(dinfo);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return list;
	}
	/*************
	 *根据部门ID查找部门
	 * @param id
	 * @return
	 */
	public DepartmentInfo getDepartmentbyDepartmentID(int id){
		PreparedStatement ps=null;
		ResultSet rs = null;
		DepartmentInfo dinfo=null;
		String sql="select * from department where departmentID=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()){
				dinfo = new DepartmentInfo();
				dinfo.setDepartmentID(rs.getInt("departmentID"));
				dinfo.setDepartmentCode(rs.getString("departmentCode"));
				dinfo.setDepartmentName(rs.getString("departmentName"));
				dinfo.setOperater(rs.getInt("operater"));
				dinfo.setRemark(rs.getString("remark"));
				dinfo.setIsDelete(rs.getInt("isDelete"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return dinfo;
	}
	/*****************
	 * 获取部门编号
	 * @return
	 */
	public String getDepartmentCode(){
		PreparedStatement ps=null;
		ResultSet rs = null;
		String departmentCode=null;
		String sql="select top 1 departmentCode from department order by departmentID desc";
		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			if(rs.next()){
				departmentCode = rs.getString("departmentCode");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return departmentCode;
	}
	/************
	 * 修改部门
	 * @param dinfo
	 * @return
	 */
	public boolean updateDepartmentbyID(DepartmentInfo dinfo){
		boolean flag = false;
		String sql = "update department set departmentName=?,remark=?,isDelete=?,operater=? where departmentID=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, dinfo.getDepartmentName());
			ps.setString(2, dinfo.getRemark());
			ps.setInt(3, dinfo.getIsDelete());
			ps.setInt(4, dinfo.getOperater());
			ps.setInt(5, dinfo.getDepartmentID());
			int count = ps.executeUpdate();
			if(count>0){
				flag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	/*************
	 * 删除部门
	 * @param id
	 * @param operater
	 * @return
	 */
	public boolean deleteDepartmentbyID(int id,int operater){
		boolean flag = false;
		String sql = "delete from department where departmentID=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			int count = ps.executeUpdate();
			if(count>0){
				flag = true;
			}
		} catch (SQLException e) {
			flag=false;
		}
		return flag;
	}
	/*************
	 * 强制删除部门
	 * @param id
	 * @param operater
	 * @return
	 */
	public boolean deleteDepartmentbyID2(int id,int operater){
		boolean flag = false;
		String sql = "update department set isDelete=0,operater=? where departmentID=?";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, operater);
			ps.setInt(2, id);
			int count = ps.executeUpdate();
			if(count>0){
				flag = true;
			}
		} catch (SQLException e) {
			flag=false;
		}
		return flag;
	}
}