deptdao.java

分为经理和出纳2个权限

package com.yiboit.cfss.dept;

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

import com.yiboit.cfss.db.ManageConnection;

public class DeptDAO {
	/**
	 * 更新部门信息
	 * 
	 * @param id 部门编号
	 * @param name	部门名称
	 * @param desc	部门描述
	 * @return true/false 成功/失败
	 * @throws SQLException
	 */
	public boolean updateDept(String id, String name, String desc) throws SQLException {
		boolean result = false;
		Connection conn = null;

		try {
			conn = ManageConnection.getConnection();
			String sql = "update shop_dept set dept_name = ?, dept_description = ?"
					+ " where dept_id = ? ";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, name);
			pstmt.setString(2, desc);
			pstmt.setString(3, id);

			result = pstmt.executeUpdate() == 1 ? true : false;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return result;
	}

	public boolean addDept(String deptName, String deptDescription)
			throws SQLException {
		boolean result = false;
		Connection conn = null;

		try {
			conn = ManageConnection.getConnection();
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt
					.executeQuery("select max(dept_id) + 1 as max_deptid from shop_dept");
			int deptId;

			if (rs.next()) {
				deptId = rs.getInt(1);

				String sql = "insert into shop_dept(dept_id, dept_name, dept_description)"
						+ " values(?, ?, ?)";
				PreparedStatement pstmt = conn.prepareStatement(sql);
				pstmt.setInt(1, deptId);
				pstmt.setString(2, deptName);
				pstmt.setString(3, deptDescription);

				result = pstmt.executeUpdate() == 1 ? true : false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return result;
	}

	/**
	 * 取得所有部门信息
	 * 
	 * @return
	 */
	public List getDeptList() throws SQLException {
		List<Dept> result = null;
		Connection conn = null;

		try {
			conn = ManageConnection.getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("select * from shop_dept");
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				result = new ArrayList<Dept>();
				while (rs.next()) {
					result.add(new Dept(rs.getInt("dept_id"), rs
							.getString("dept_name"), rs
							.getString("dept_description")));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
				}
			}
		}

		return result;
	}
}