pagecutbean.java

oa办公管理系统。一些小型的企业办公管理用的系统。一个月废寝忘食的结果。

package com.soft.pagecut;

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.cn.contactlist.vo.ContactInfo;
import com.soft.util.DBConn;
import com.soft.vo.DeviceInfo;
import com.soft.vo.LoginInfo;
import com.soft.vo.MessageInfo;
import com.soft.vo.UserInfo;

public class pagecutbean {
	
	private DBConn tj = new DBConn();
	private Connection conn = null;
	private Statement st = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private pageable res=null;
	int PageCount;
	int rowsCount;
	int PageRowsCount;
	
	int PageSize;
	int CurPage;
	public int getPageCount() {
		return PageCount;
	}

	

	public int getPageRowsCount() {
		return PageRowsCount;
	}


	public int getPageSize() {
		return PageSize;
	}

	public void setPageSize(int pageSize) {
		PageSize = pageSize;
	}

	public int getCurPage() {
		return CurPage;
	}

	public int getRowsCount() {
		return rowsCount;
	}

	public void setCurPage(int curPage) {
		CurPage = curPage;
	}
	public boolean checkUserSQLInjection(String username,String password)
	{
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st = conn.createStatement();
			rs = st.executeQuery("select * from userinfo where username='"+username+"' and password='"+password+"'");
			if(rs.next())
			{
				return true;
			}
		}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 false;
	}
	public List getAllUsers()
	{
		List<UserInfo> lt = new ArrayList<UserInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			res=new PageableResultSet(st.executeQuery("select u.*,d.deptname from userinfo u,deptment d where u.deptno = d.deptno"));//构造一个Pageable
			res.setPageSize(getPageSize());//每页5个记录
            
			System.out.println("pagetcount:"+res.getPageCount()+"rowscount:"+res.getRowsCount()+"pagesize:"+res.getPageSize());
			res.gotoPage(getCurPage());//跳转到第2页
			PageCount=res.getPageCount();
			rowsCount=res.getRowsCount();
			PageRowsCount=res.getPageRowsCount();
			
			System.out.println("current page:"+res.getCurPage());
			
			
			
			for(int i=0; i<res.getPageRowsCount(); i++)
			
			{
				LoginInfo ui = new LoginInfo();
				
				int userno=res.getInt("userno");
				
				String username=res.getString("username");
				int deptno=res.getInt("deptno");
				String deptname=res.getString("deptname");
			
			/*	ui.setUserNo(userno);
				ui.setUserName(username);
				ui.setDeptNo(deptno);
				ui.setDeptName(deptname);
				lt.add(ui);*/
				res.next();
			}
		lt.size();
		    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();
				}
				if(res!=null)
					res.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lt;
	}
	public List getAllEquipment()
	{
		List<DeviceInfo> list = new ArrayList<DeviceInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			res=new PageableResultSet(st.executeQuery("select distinct e.equipmentid,e.equipmentname,e.isdamaged,e.isrepairing,e.iscanborrow,ae.isauthorized, em.name  from  equipment e left join applyequipment ae on e.equipmentid=ae.equipmentid left join employee em on ae.applicant=em.employeeid"));//构造一个Pageable
			res.setPageSize(getPageSize());//每页5个记录
            
			//System.out.println("pagetcount:"+res.getPageCount()+"rowscount:"+res.getRowsCount()+"pagesize:"+res.getPageSize());
			res.gotoPage(getCurPage());
			PageCount=res.getPageCount();
			rowsCount=res.getRowsCount();
			PageRowsCount=res.getPageRowsCount();
			
			//System.out.println("current page:"+res.getCurPage());
			
			for(int i=0; i<res.getPageRowsCount(); i++)
			{
				//res.absolute((getCurPage() - 1) * getPageSize() + 1);
				//System.out.println("当前显示的记录数为:"+res.getPageRowsCount());
				//System.out.println("这里的ID为："+res.getInt("equipmentid"));
				DeviceInfo deviceInfo=new DeviceInfo();
				
				deviceInfo.setEquipmentID(res.getInt("equipmentid"));
				deviceInfo.setEquipmentName(res.getString("equipmentname"));
				deviceInfo.setBorrowPeople(res.getString("name")==null?"无":res.getString("name"));
				deviceInfo.setIsCanBorrow(res.getInt("iscanborrow"));
				deviceInfo.setIsDamaged(res.getInt("isdamaged"));
				deviceInfo.setIsReparing(res.getInt("isrepairing"));
				//System.out.println("此处正常");
				deviceInfo.setIsAuthorized(res.getInt("isauthorized"));
				//System.out.println("此处也正常");
				list.add(deviceInfo);
				res.next();
			}
		    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();
				}
				if(res!=null)
					res.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	public List getAllEquipment(String condition,String equipmentName)
	{
		List<DeviceInfo> list = new ArrayList<DeviceInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			String sql="select distinct e.equipmentid,e.equipmentname,e.isdamaged,e.isrepairing,e.iscanborrow,ae.isauthorized, em.name  from  equipment e left join applyequipment ae on e.equipmentid=ae.equipmentid left join employee em on ae.applicant=em.employeeid";
			if(condition!=null&&condition.equals("len"))
			{
				sql=" select *from ("+sql+") where isauthorized=1";
			}
			else if(condition!=null&&condition.equals("unlen"))
			{
				sql=" select *from ("+sql+") where isauthorized=0";
			}
			if(equipmentName!=null&&!equipmentName.equals("")&&!equipmentName.equals("null"))
			{
				sql=" select *from ("+sql+") where equipmentname like '%"+equipmentName+"%'";
			}
			res=new PageableResultSet(st.executeQuery(sql));//构造一个Pageable
			res.setPageSize(getPageSize());//每页5个记录
            
			//System.out.println("pagetcount:"+res.getPageCount()+"rowscount:"+res.getRowsCount()+"pagesize:"+res.getPageSize());
			res.gotoPage(getCurPage());
			PageCount=res.getPageCount();
			rowsCount=res.getRowsCount();
			PageRowsCount=res.getPageRowsCount();
			
			//System.out.println("current page:"+res.getCurPage());
			
			for(int i=0; i<res.getPageRowsCount(); i++)
			{
				//res.absolute((getCurPage() - 1) * getPageSize() + 1);
				//System.out.println("当前显示的记录数为:"+res.getPageRowsCount());
				//System.out.println("这里的ID为："+res.getInt("equipmentid"));
				DeviceInfo deviceInfo=new DeviceInfo();
				
				deviceInfo.setEquipmentID(res.getInt("equipmentid"));
				deviceInfo.setEquipmentName(res.getString("equipmentname"));
				deviceInfo.setBorrowPeople(res.getString("name")==null?"无":res.getString("name"));
				deviceInfo.setIsCanBorrow(res.getInt("iscanborrow"));
				deviceInfo.setIsDamaged(res.getInt("isdamaged"));
				deviceInfo.setIsReparing(res.getInt("isrepairing"));
				System.out.println("此处正常");
				deviceInfo.setIsAuthorized(res.getInt("isauthorized"));
				System.out.println("此处也正常");
				list.add(deviceInfo);
				res.next();
			}
		    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();
				}
				if(res!=null)
					res.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public List getAllContact()
	{
		List<ContactInfo> list = new ArrayList<ContactInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			res=new PageableResultSet(st.executeQuery("select cl.*,e.name,d.departmentname from contactlist cl,employee e,department d where cl.employeeid=e.employeeid and e.departmentid=d.departmentid"));//构造一个Pageable
			res.setPageSize(getPageSize());//每页5个记录
            
			//System.out.println("pagetcount:"+res.getPageCount()+"rowscount:"+res.getRowsCount()+"pagesize:"+res.getPageSize());
			res.gotoPage(getCurPage());
			PageCount=res.getPageCount();
			rowsCount=res.getRowsCount();
			PageRowsCount=res.getPageRowsCount();
			
			//System.out.println("current page:"+res.getCurPage());
			
			for(int i=0; i<res.getPageRowsCount(); i++)
			{
				//res.absolute((getCurPage() - 1) * getPageSize() + 1);
				//System.out.println("当前显示的记录数为:"+res.getPageRowsCount());
				//System.out.println("这里的ID为："+res.getInt("equipmentid"));
				ContactInfo contactInfo=new ContactInfo();
				
				contactInfo.setEmployeeId(res.getInt("employeeid"));
				contactInfo.setMobilePhone(res.getString("mobilephone"));
				contactInfo.setQQ(res.getString("QQ"));
				contactInfo.setMSN(res.getString("msn"));
				contactInfo.setEmail(res.getString("email"));
				contactInfo.setDepartmentName(res.getString("departmentname"));
				contactInfo.setEmployeeName(res.getString("name"));
				//System.out.println("此处也正常");
				list.add(contactInfo);
				res.next();
			}
		    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();
				}