menudao.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.List;

import com.oa.lp.model.Menu;
import com.oa.lp.util.DTOPopulator;
public class MenuDAO {

	private Connection conn;
	public Connection getConn() {
		return conn;
	}

	public void setConn(Connection conn) {
		this.conn = conn;
	}
	
	/**
	 * 新增菜单
	 *
	 */
	public void addMenu(Menu menu) throws Exception{
		String sql = "insert into SYS_MENU(PARENT_MENU_ID,MENU_NAME,MENU_LINK," +
				"MENU_ORDER,MENU_STATE)values(?,?,?,?,?)";
		
		int menuOrder = this.getMenuOrderByParentId(menu.getParentMenuId());
		PreparedStatement pstmt = conn.prepareStatement(sql);
		if(menu.getParentMenuId()==0){
			//如果上级菜单编号为0,设上级菜单为null
			pstmt.setNull(1,Types.INTEGER);
		}else{
			pstmt.setInt(1,menu.getParentMenuId());
		}
		pstmt.setString(2,menu.getMenuName());
		pstmt.setString(3,menu.getMenuLink());
		pstmt.setInt(4,menuOrder);
		pstmt.setInt(5,menu.getMenuState());
		pstmt.executeUpdate();
		pstmt.close();
	}
	/**
	 * 通过菜单ID查找菜单
	 */
	public Menu getById(int menuId) throws Exception{
		Menu menu =null;
		String sql = "select * from SYS_MENU where MENU_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,menuId);
		ResultSet rs = pstmt.executeQuery();
		//将结果集中的每一行记录封装成一个对象,再放进集合返回
		List list = DTOPopulator.populate(rs, Menu.class);
		//取集合中的第一个元素返回
		if(list.size()>0){
			menu=(Menu)list.get(0);
		}
		pstmt.close();
		return menu;
	}
	/**
	 * 更新菜单
	 *
	 */
	public void updateMenu(Menu menu) throws SQLException{
		String sql = "update SYS_MENU set PARENT_MENU_ID=?,MENU_NAME=?,MENU_LINK=?," +
				"MENU_ORDER=?,MENU_STATE=? where MENU_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		if(menu.getParentMenuId()==0){
			//如果上级菜单编号为0,设上级菜单为null
			pstmt.setNull(1,Types.INTEGER);
		}else{
			pstmt.setInt(1,menu.getParentMenuId());
		}
		pstmt.setString(2,menu.getMenuName());
		pstmt.setString(3,menu.getMenuLink());
		pstmt.setInt(4,menu.getMenuOrder());
		pstmt.setInt(5,menu.getMenuState());
		pstmt.setInt(6,menu.getMenuId());
		pstmt.executeUpdate();
		pstmt.close();
	}

	/**
	 * 菜单列表
	 */
	public List listAllMenu() throws Exception{
		List list = null; 
		String sql = "select * from SYS_MENU order by PARENT_MENU_ID,MENU_ORDER";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		//将结果集中的每一行记录封装成一个对象,再放进集合返回
		list = DTOPopulator.populate(rs, Menu.class);
		pstmt.close();
		return list;
	}
	/**
	 * 通过上级菜单编号,决定菜单顺序
	 *
	 */
	public int getMenuOrderByParentId(int parentMenuId) throws Exception{
		int orderNum = 0;
		String sql = "";
		if(parentMenuId==0){
			sql = "select max(MENU_ORDER)+1 from  SYS_MENU  where PARENT_MENU_ID is null";
		}else{
			sql = "select max(MENU_ORDER)+1 from  SYS_MENU  where PARENT_MENU_ID ="+parentMenuId;
		}
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			orderNum = rs.getInt(1);
		}
		pstmt.close();
		return orderNum;
	}
	/**
	 * 通过上级菜单编号,获得下级菜单集合
	 */
	public List getMenusByParentId(int parentMenuId) throws Exception{
		String sql = ""; 
		List list = null; 
		if(parentMenuId==0){
			sql = "select *  from  SYS_MENU  where PARENT_MENU_ID is null";
		}else{
			sql = "select * from  SYS_MENU  where PARENT_MENU_ID ="+parentMenuId;
		}
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		//将结果集中的每一行记录封装成一个对象,再放进集合返回
		list = DTOPopulator.populate(rs, Menu.class);
		pstmt.close();
		return list;
		
	}
	/**
	 * 更新菜单状态
	 */
	public void updateMenuState(int menuId,int menuState) throws SQLException{
		String sql = "update SYS_MENU set MENU_STATE=? where MENU_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,menuState);
		pstmt.setInt(2,menuId); 
		pstmt.executeUpdate(); 
		pstmt.close();
	}
	
	/**
	 * 判断菜单是否是最顶层
	 * @throws SQLException 
	 */
	public boolean checkMenuOrderTop(int parentMenuId,int menuOrder) throws SQLException{
		boolean flage = false;
		String sql = "";
		if(parentMenuId==0){
			sql = "select min(MENU_ORDER)  from  SYS_MENU  where PARENT_MENU_ID is null ";
		}else{
			sql = "select min(MENU_ORDER) from  SYS_MENU  where PARENT_MENU_ID ="+parentMenuId;
		}
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			int orderNum = rs.getInt(1);
			if(orderNum==menuOrder){
				flage = true;
			}
		}
		rs.close();
		pstmt.close();
		return flage;
	}
	
	/***
	 * 菜单顺序向上移动
	 * @throws SQLException 
	 */
	public void menuOrderUp(Menu menu) throws SQLException{
		String sql = "";
		if(menu.getParentMenuId()==0){
			sql = "update SYS_MENU set MENU_ORDER=MENU_ORDER+1 where PARENT_MENU_ID is null" +
					" and MENU_ORDER="+(menu.getMenuId()-1);
		}else{
			sql = sql = "update SYS_MENU set MENU_ORDER=MENU_ORDER+1 where PARENT_MENU_ID=" + 
	 		menu.getParentMenuId()+"and MENU_ORDER="+(menu.getMenuOrder()-1);
		}
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.executeUpdate();
		pstmt.close();
		
		sql = "update SYS_MENU set MENU_ORDER=MENU_ORDER-1 where MENU_ID="+menu.getMenuId();
		pstmt = conn.prepareStatement(sql); 
		pstmt.executeUpdate();
		pstmt.close();
	}
	/**
	 * 验证菜单顺序是否为最底层
	 *
	 */
	public boolean checkMenuOrderBottom(int parentMenuId,int menuOrder) throws SQLException{
		boolean flag = false;
		String sql = "";  
		if(parentMenuId==0){
			sql = "select max(MENU_ORDER)  from  SYS_MENU  where PARENT_MENU_ID is null ";
		}else{
			sql = "select max(MENU_ORDER) from  SYS_MENU  where PARENT_MENU_ID="+parentMenuId;
		}
		
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			int orderNum = rs.getInt(1);
			if(orderNum==menuOrder){
				flag = true;
			}
		} 
		rs.close();
		pstmt.close();
		return flag;
	}
	/**
	 * 菜单顺序向下移动
	 *  
	 */
	public void menuOrderDown(Menu menu) throws SQLException{
		String sql = "";
		if(menu.getParentMenuId()==0){
			 sql = "update SYS_MENU set MENU_ORDER=MENU_ORDER-1 where PARENT_MENU_ID is null " +
			 		"and MENU_ORDER="+(menu.getMenuOrder()+1);
		}else {
			 sql = "update SYS_MENU set MENU_ORDER=MENU_ORDER-1 where PARENT_MENU_ID=" + 
			 		menu.getParentMenuId()+"and MENU_ORDER="+(menu.getMenuOrder()+1);
		}
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.executeUpdate();
		pstmt.close();
		
		sql = "update SYS_MENU set MENU_ORDER=MENU_ORDER+1 where MENU_ID="+menu.getMenuId();
		pstmt = conn.prepareStatement(sql); 
		pstmt.executeUpdate();
		pstmt.close();
		
	}
	
	/**
	 * 删除菜单
	 * @throws SQLException 
	 */
	public boolean delMenu(Menu menu) throws SQLException{
		boolean flag=true;
		//是否有下级菜单
		String sql = "select count(*) from SYS_POWER where MENU_ID=?";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1,menu.getMenuId());
		ResultSet rs = pstmt.executeQuery();
		if(rs.next()){
			if(rs.getInt(1)>0){
				//有下级不能删除
				flag = false;
			}
		}
		rs.close();
		pstmt.close();
		
		if(flag){
			//删除
			sql = "delete from SYS_MENU where MENU_ID=?";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,menu.getMenuId());
			pstmt.executeUpdate();
			pstmt.close();
			//更新菜单顺序
			if(menu.getParentMenuId()==0){
				sql = "update SYS_MENU set MENU_ORDER=MENU_ORDER-1 where " +
						"PARENT_MENU_ID is null and MENU_ORDER>?";
			}else {
				sql = "update SYS_MENU set MENU_ORDER=MENU_ORDER-1 where " +
				"PARENT_MENU_ID ="+menu.getParentMenuId()+" and MENU_ORDER>?";
			}
			pstmt = conn.prepareStatement(sql); 
			pstmt.setInt(1,menu.getMenuOrder());
			pstmt.executeUpdate();
			pstmt.close();
		}
		return flag;
	}
	
	
	
	
	
	
}