folderdao.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.sql.Types;
import java.util.ArrayList;
import java.util.List;

import com.oa.lp.database.DataBase;
import com.oa.lp.model.File;
import com.oa.lp.model.Folder;
import com.oa.lp.util.DTOPopulator;

public class FolderDAO {
	private List folderList = new ArrayList();
	private StringBuffer path = new StringBuffer();
	private Connection conn;
	public Connection getConn() {
		return conn;
	}
	public void setConn(Connection conn) {
		this.conn = conn;
	}
	/**
	 * 添加文件夹
	 * @param folder
	 * @throws SQLException 
	 */
	public void addFolder(Folder folder) throws SQLException{
		String sql = "insert into FOLDER(PARENT_FOLDER_ID,EMP_ID,FOLDER_NAME) values(?,?,?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		if(folder.getParentfolderId()==0){
			pstmt.setNull(1,Types.INTEGER);
		}else{
			pstmt.setInt(1,folder.getParentfolderId());
		}
		pstmt.setInt(2,folder.getEmpId());
		pstmt.setString(3,folder.getFolderName());
		pstmt.executeUpdate();
		pstmt.close();
	}
	/**
	 * 删除文件夹
	 * @param folderId
	 * @return
	 * @throws SQLException 
	 */
	public boolean delFolder(int folderId) throws SQLException{
		boolean flag = true; 
		//是否有下级文件夹
		String sql = "select count(*) from FOLDER where PARENT_FOLDER_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,folderId);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			if(rs.getInt(1)>0){
				//有下级不能删除
				flag = false;
			}
		}
		rs.close();
		pstmt.close();
		//是否有文件
		sql = "select count(*) from [FILE] where FOLDER_ID=?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,folderId);
		rs = pstmt.executeQuery();
		if(rs.next()){
			if(rs.getInt(1)>0){
				//有文件不能删除
				flag = false;
			}
		}
		rs.close();
		pstmt.close();
		if(flag){
			//删除
			sql = "delete from FOLDER where FOLDER_ID=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,folderId);
			pstmt.executeUpdate();
			pstmt.close();
		}
		return flag;
		
	}
	/**
	 * 通过员工的ID的起拥有的共享文件
	 * @param empId
	 * @return
	 * @throws Exception
	 */
	public List listAllFiles(int empId) throws Exception{
		List list = null;
		String sql = "select *from [FILE] where FOLDER_ID in(select FOLDER_ID from SHARE_FILE where EMP_ID=?)";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,empId);
		ResultSet rs = pstmt.executeQuery();
		//将结果集中的每一行记录封装成一个对象,再放进集合返回
		list = DTOPopulator.populate(rs, File.class);
		rs.close();
		pstmt.close();
		return list;
	}
	/**
	 * 通过员工ID得到其所拥有的文件和文件夹
	 * @throws Exception 
	 */
	public List listAllFolders(int empId) throws Exception{
		List list = null;
		String sql = "select *from FOLDER where EMP_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,empId);
		ResultSet rs = pstmt.executeQuery();
		//将结果集中的每一行记录封装成一个对象,再放进集合返回
		list = DTOPopulator.populate(rs, Folder.class);
		rs.close();
		pstmt.close();
		return list;
	}
	/**
	 * 通过上级文件夹ID返回所有下级文件夹
	 * @return
	 * @throws Exception 
	 */
	public List listAllFolder(int parentFolderId) throws Exception{
		List list = null;
		String sql = null;
		if(parentFolderId==0){
			sql = "select * from FOLDER where PARENT_FOLDER_ID is null";
		}else{
			sql = "select * from FOLDER where PARENT_FOLDER_ID="+parentFolderId;
		}
		PreparedStatement pstmt = conn.prepareStatement(sql);
		
		ResultSet rs = pstmt.executeQuery();
		//将结果集中的每一行记录封装成一个对象,再放进集合返回
		list = DTOPopulator.populate(rs, Folder.class);
		rs.close();
		pstmt.close();
		return list;
	}
	/**
	 * 通过文件夹ID 返回当前位置
	 * @param folderId
	 * @return
	 * @throws SQLException 
	 */
	public String getPathByFolderId(int folderId,String fileName) throws SQLException{
		String sql = "select * from FOLDER where FOLDER_ID=?"; 
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,folderId);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			Folder folder = new Folder();
			folder.setFolderId(rs.getInt("FOLDER_ID"));
			folder.setFolderName(rs.getString("FOLDER_NAME"));
			folderList.add(folder);
			int parentFolderId = rs.getInt("PARENT_FOLDER_ID");
			rs.close();
			pstmt.close();
			rs=null;
			pstmt=null; 
			if(parentFolderId==0){
				for(int i=(folderList.size()-1);i>=0;i--){
					Folder folderTemp = (Folder)folderList.get(i);
					path.append("<a href="+fileName+"&folderId=");
					path.append(folderTemp.getFolderId());
					path.append(">");
					path.append(folderTemp.getFolderName());
					path.append("</a>");
					if(i!=0){
						path.append(">>");
					}
				}
				return path.toString();
			}else{
				
				this.getPathByFolderId(parentFolderId,fileName);
			}
		}
		return path.toString();
	}
	
}