deptinfodao.java

实现办公自动化系统

/**
 * @author gjq
 * 部门信息操作类
 */

package com.oa.struts.depmgr.dao;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

import com.oa.util.*;
import com.oa.struts.vo.*;
public class DeptInfoDAO
{
	private Connection conn = null; 
	private PreparedStatement ps = null;
	private Statement stmt=null;
	private ResultSet rs = null;
	public DBConn dbcon;
	
	public DeptInfoDAO()
	{
		
	}
	
	public int getTotulRows() 
	{   
		int i = 0;
		try {
			dbcon=new DBConn();
			conn=dbcon.getConnection();
			conn.setAutoCommit(false);
			String sql = "select count(*) totulrows from tb_department where 1=1 ";

			ps = conn.prepareStatement(sql);
			
            rs = ps.executeQuery();
            
			if(rs.next())
			{
				i = rs.getInt("totulrows");
			}
		    conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}
	
	public boolean insertDept(DeptInfo deptInfo)
	{
		int i;
		boolean flag=false;
		dbcon=new DBConn();
		try {
			conn = dbcon.getConnection();
			conn.setAutoCommit(false);
			String sqlInfo="insert into tb_department values(dept_seq.nextval,?,?,?)";
			ps = conn.prepareStatement(sqlInfo);
			ps.setInt(1,deptInfo.getDeptID());
			ps.setString(2,deptInfo.getDeptName());
			ps.setString(3,deptInfo.getEXPLAIN());
			
				
			i = ps.executeUpdate();
			if(i>0)
			{
				flag=true;
			}
		    conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try 
			{
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		return flag;

		}
	
	
	
	public boolean updateDept(DeptInfo deptInfo)
	{
		int i;
		boolean flag=false;
		dbcon=new DBConn();
		try {
			conn = dbcon.getConnection();
			conn.setAutoCommit(false);
			String sqlInfo="update tb_department set DeptName=? , EXPLAIN=? where DeptID=?";
			ps = conn.prepareStatement(sqlInfo);
		
			ps.setString(1,deptInfo.getDeptName());
			ps.setString(2,deptInfo.getEXPLAIN());
			ps.setInt(3,deptInfo.getDeptID());
				
			i = ps.executeUpdate();
			if(i>0)
			{
				flag=true;
			}
		    conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try 
			{
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} 
			catch (SQLException e) 
			{
				e.printStackTrace();
			}
		}
		return flag;

		}
	
	
	
	
	public List getDeptList(int startRow,int endRow)
	{
		List<DeptInfo> lt=new ArrayList<DeptInfo>();
		dbcon=new DBConn();
		try
		{
			conn=dbcon.getConnection();
			conn.setAutoCommit(false);
			String sql="select t.*,rownum rn from tb_department t where 1=1";		
			sql = "select * from ("+ sql+" and rownum<="+endRow +") t where rn>="+startRow;
			stmt=conn.createStatement();
			rs=stmt.executeQuery(sql);
			while(rs.next())
			{
				DeptInfo deptInfo=new DeptInfo();
				deptInfo.setDeptID(rs.getInt("DEPTID"));
				deptInfo.setDeptName(rs.getString("DEPTNAME"));
				deptInfo.setEXPLAIN(rs.getString("EXPLAIN"));
				lt.add(deptInfo);
			}
			
			conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lt;
	}

	public DeptInfo getDeptList(int deptId)
	{
		//List<DeptInfo> lt=new ArrayList<DeptInfo>();
		DeptInfo deptInfo=new DeptInfo();
		dbcon=new DBConn();
		try
		{
			conn=dbcon.getConnection();
			conn.setAutoCommit(false);
			String sql="select * from tb_department where deptid=?";
			ps=conn.prepareStatement(sql);
			ps.setInt(1, deptId);
			rs=ps.executeQuery();    //pstmt.executeQuery()；括号中不带参数
			while(rs.next())
			{
				deptInfo.setDeptID(rs.getInt("deptid"));
				deptInfo.setDeptName(rs.getString("DEPTNAME"));
				deptInfo.setEXPLAIN(rs.getString("EXPLAIN"));
				//lt.add(deptInfo);
				System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@");
			}
			
			conn.commit();
		} catch (SQLException e) {
			
			try {
				if(conn!=null)
				{
					conn.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(conn!=null)
				{
					conn.close();
				}
				if(ps!=null)
				{
					ps.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return deptInfo;
	}
	
	public boolean deleteDept(int DeptID)
    {
    	boolean flag=false;
    	dbcon=new DBConn();
    	conn=dbcon.getConnection();
    	try
    	{
    		System.out.println("break1");
    		//conn.setAutoCommit(false);
    		System.out.println("break2");
    		String sql="delete from tb_department where deptid="+DeptID;
    		//pstmt=conn.prepareStatement(sql);
    		System.out.println("break3");
    		//pstmt.setInt(1, userId);
    		System.out.println("sql="+sql);
    		//int i=pstmt.executeUpdate();
    		stmt=conn.createStatement();
    		int i=stmt.executeUpdate(sql);
    		System.out.println("break4");
    		if(i>0)
    		{
    			flag=true;
    		}
    		
    	}
    	catch(Exception e)
    	{
    		e.printStackTrace();
    	}
    	
    	return flag;
    }
 
}