actordao.java

实现办公自动化系统

/**
 * @author Administrator
 * 角色处理类
 */
package com.oa.struts.systemManager.actorDAO;

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.oa.struts.systemManager.actor.form.ActorsForm;
import com.oa.struts.vo.ActorInfo;
import com.oa.util.DBConn;

public class ActorDAO {
	private DBConn db=new DBConn();
	private Connection con=null;
	private ResultSet rs=null;
	private PreparedStatement pstmt=null;
	private Statement stmt=null;
	public ActorDAO()
	{
		
	}
	/**
	 * 获取该数据库的总行数
	 * @return
	 */
	public int getTotulRows() 
	{   
		int i = 0;
		try {
			db=new DBConn();
			con=db.getConnection();
			con.setAutoCommit(false);
			String sql = "select count(*) totulrows from tb_actors  where 1=1 ";

			pstmt = con.prepareStatement(sql);
			
            rs = pstmt.executeQuery();
            
			if(rs.next())
			{
				i = rs.getInt("totulrows");
			}
		    con.commit();
		} catch (SQLException e) {
			
			try {
				if(con!=null)
				{
					con.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(con!=null)
				{
					con.close();
				}
				if(pstmt!=null)
				{
					pstmt.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}
	
	
	
	public List getAllList(int startRow,int endRow)
	{
		List<ActorInfo> lt=new ArrayList<ActorInfo>();
		db=new DBConn();
		try{
			con=db.getConnection();
			con.setAutoCommit(false);
			stmt=con.createStatement();
			String sql="select t.*,rownum rn from tb_actors t where 1=1 ";
			sql = "select * from ("+ sql+" and rownum<="+endRow +") t where rn>="+startRow;
			rs=stmt.executeQuery(sql);
			int i=0;
			while(rs.next())
			{
				ActorInfo actorInfo=new ActorInfo();
				actorInfo.setActorId(rs.getInt("ACTORID"));
				actorInfo.setActorName(rs.getString("ACTORNAME"));
				lt.add(i++,actorInfo);
			}
			con.commit();
		}
		catch(SQLException e)
		{
			try{
				if(con!=null)
				{
					con.rollback();
				}
			}
			catch(SQLException e1)
			{
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try{
				if(con!=null)
				{con.close();}
				if(pstmt!=null)
				{
					pstmt.close();
				}
				if(rs!=null)
				{rs.close();}
			}
			catch(SQLException e2)
			{e2.printStackTrace();}
		}
		return lt;
	}
	
	/**
	 * @param actorName
	 * @return
	 * 查询角色名是否存在,返回true表示不存在，false表示存在
	 */
	public boolean searchactor(String actorName)
	{
		boolean flag=false;
		db=new DBConn();
		try{
			db=new DBConn();
			con = db.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("select * from tb_actors where actorname=?");
			pstmt.setString(1,actorName);
			rs = pstmt.executeQuery();
			if(!rs.next())
			{
				flag=true;
			}
		    con.commit();
		}catch (SQLException e) {
			try {
				if(con!=null)
				{
					con.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(con!=null)
				{
					con.close();
				}
				if(pstmt!=null)
				{
					pstmt.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
	}
	/**
	 * 删除角色
	 * @param actorName
	 * @return
	 */
	public boolean  delactor(int actorid)
	{
		boolean flag=false;
		db=new DBConn();
		try{
			con=db.getConnection();
			con.setAutoCommit(false);
			pstmt=con.prepareStatement("delete from tb_actors where actorid=?");
			pstmt.setInt(1,actorid);
			int i = pstmt.executeUpdate();
			if(i>0)
			{
				flag=true;
			}
		    con.commit();
		} catch (SQLException e) {
			
			try {
				if(con!=null)
				{
					con.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(con!=null)
				{
					con.close();
				}
				if(pstmt!=null)
				{
					pstmt.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return flag;
		
	}
	public int updateactor(String actorName)
	{
		int i = 0;
		db=new DBConn();
		try {
			con = db.getConnection();
			con.setAutoCommit(false);
			pstmt = con.prepareStatement("update  tb_actors set actorName=? where actorID=?");
			pstmt.setString(1,actorName);
			i = pstmt.executeUpdate();
		    con.commit();
		} catch (SQLException e) {
			
			try {
				if(con!=null)
				{
					con.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(con!=null)
				{
					con.close();
				}
				if(pstmt!=null)
				{
					pstmt.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return i;
	}
	/**
	 * 增加角色
	 * @param actorName
	 * @return
	 */
	public boolean addactor(String actorName)
	{
		boolean result=false;
		try
		{
			db=new DBConn();
			con=db.getConnection();
			pstmt=con.prepareStatement("insert into tb_actors values(actor_seq.nextval,?)");
			pstmt.setString(1,actorName);
			int i=pstmt.executeUpdate();
			System.out.println("i="+i);
			if(i>0)
			{
				result=true;
			}
				
			if(pstmt!=null) pstmt.close();
			if(con!=null) con.close();		
			
			 // con.commit();
		} catch (SQLException e) {
			
			try {
				if(con!=null)
				{
					con.rollback();//事务回滚
				}
			} catch (SQLException e1) {
				
				e1.printStackTrace();
			}
			e.printStackTrace();
		}
		finally
		{
			try {
				if(con!=null)
				{
					con.close();
				}
				if(pstmt!=null)
				{
					pstmt.close();
				}
				if(rs!=null)
				{
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return result;
	}
	/**
	 * 角色列表
	 */
	public List getActorList()
	{
		List<ActorInfo> lt=new ArrayList<ActorInfo>();
		db=new DBConn();
		con=db.getConnection();
		ResultSet rs=null;
		String sql="select * from tb_actors";
		try
		{
			stmt=con.createStatement();
			rs=stmt.executeQuery(sql);
			int i=0;
			while(rs.next())
			{
				ActorInfo actorInfo=new ActorInfo();
				actorInfo.setActorId(rs.getInt("actorid"));
				actorInfo.setActorName(rs.getString("actorname"));
				lt.add(i++, actorInfo);
			}
		    con.commit();
		} catch (SQLException e) {
		
		try {
			if(con!=null)
			{
				con.rollback();//事务回滚
			}
		} catch (SQLException e1) {
			
			e1.printStackTrace();
		}
		e.printStackTrace();
	}
	finally
	{
		try {
			if(con!=null)
			{
				con.close();
			}
			if(pstmt!=null)
			{
				pstmt.close();
			}
			if(rs!=null)
			{
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
		return lt;
	}
}