userinfodao.java

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

package com.soft.login.dao;

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.soft.vo.EmployeeInfo;
import com.soft.vo.LoginInfo;
import com.soft.vo.RoleInfo;
import com.soft.util.DBConn;
import com.soft.pagecut.PageableResultSet;
import com.soft.pagecut.pageable;

public class UserInfoDAO {
	private DBConn tj = new DBConn();
	private Connection conn = null; 
	private Statement st = null;
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	public boolean checkUser(int employeeid,String password)
	{
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement("select * from login where employeeid=? and password=?");
			ps.setInt(1,employeeid);
			
			ps.setString(2,password);
			rs = ps.executeQuery();
			if(rs.next())
			{
				return 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 false;
	}
	public boolean checkUserSQLInjection(int employeeid,String password)
	{
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st = conn.createStatement();
			rs = st.executeQuery("select * from login where employeeid='"+employeeid+"' 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 getEmpInfo()
	{
		List<EmployeeInfo> lt = new ArrayList<EmployeeInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rs=st.executeQuery("select e.employeeid,e.name from employee e ");//构造一个Pageable
			while(rs.next())
			{
				EmployeeInfo eif = new EmployeeInfo();
				int employeeid=rs.getInt("employeeid");
			
				String name=rs.getString("name");
			
				eif.setEmployeeid(employeeid);
				eif.setName(name);
			
			    lt.add(eif);
			
			}
		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();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lt;
	}
	public List getLoginUserInfo(int employeeid)
	{
		List<LoginInfo> lt = new ArrayList<LoginInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			
			st=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rs=st.executeQuery("select e.position,e.name,d.departmentname from employee e,department d where e.departmentid=d.departmentid and employeeid="+employeeid);
			while(rs.next())
			{
				LoginInfo lif = new LoginInfo();
				String departmentname=rs.getString("departmentname");
			    String position = rs.getString("position");
				String name=rs.getString("name");
			      
				lif.setDepartmentname(departmentname);
				lif.setPosition(position);
			    lif.setName(name);
			    lif.setEmployeeid(employeeid);
			    lt.add(lif);
			
			}
		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();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lt;
	}
	
	public List searchRole(int employeeid)
	{
		List<RoleInfo> lt = new ArrayList<RoleInfo>();
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			String sql = "select r.*, ro.rolename from rolepermission r,role ro ,login l where r.roleid=ro.roleid and ro.roleid=l.roleid and employeeid=?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1,employeeid);
			rs=ps.executeQuery();
			while(rs.next())
			{
				RoleInfo rd = new RoleInfo();
				
				
				 int roleid=rs.getInt("roleid");
				 String rolename=rs.getString("rolename");
				 int permissionid=rs.getInt("permissionid");
				 rd.setRoleid(roleid);
				 rd.setRolename(rolename);
				 rd.setPermissionid(permissionid);
				 lt.add(rd);
			}
			System.out.println(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();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return lt;
	}
	public int getEmployeeidByName(String name)
	{
		int i=0;
		try {
			conn=tj.getConnection();
			conn.setAutoCommit(false);
			st = conn.createStatement();
			rs = st.executeQuery("select *from employee where name='"+name+"'");
			if(rs.next())
			{
				i=rs.getInt("employeeid");
				return i;
			}
		}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;
	}
}