accessdatabase.java

自己做的一个简单的JAVA考勤系统

package access;
import java.sql.*;
import java.util.*;
import java.sql.Time;
import java.sql.Types;
import com.microsoft.jdbc.sqlserver.*;
import com.microsoft.jdbc.base.*;
public class AccessDataBase
{
	Connection conn;
	Statement state;
	PreparedStatement pst;
	ResultSet rs;
      	Time offtime;
    	Time ontime;
	int count=0;
 public	AccessDataBase()
	{
		try {
            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
		try
		{
		String constr="jdbc:odbc:driver={Microsoft Access Driver (*.mdb)};DBQ=CheckWorkTime.mdb";
		conn=DriverManager.getConnection(constr);	
		}
		catch(Exception e){
		e.printStackTrace();
	   }
	
	}
	public ArrayList getConlumnsNameFromDB(String sql)
	{
		ArrayList array=new ArrayList();
	  try
	  {
		
		 state=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
	     	 rs=state.executeQuery(sql);
		
	
		for(int i=1;i<=rs.getMetaData().getColumnCount();i++)
		{
			array.add(rs.getMetaData().getColumnName(i));
		}
		
	 }
	catch(Exception e)
	{
		try
		{
		 rs.close();
		 state.close();
		
		}
		catch(Exception ae)
		{
			ae.printStackTrace();
		}
	
		e.printStackTrace();
	}
	
		return array;
	
	}
	public ArrayList getData(String sql)
	{
		ArrayList array=new ArrayList();
		try
		{
			state=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
			rs=state.executeQuery(sql);
		
			while(rs.next())
			{
			String sd[]=new String[rs.getMetaData().getColumnCount()];
				for(int i=1;i<=rs.getMetaData().getColumnCount();i++)
				{ 
				 if(rs.getMetaData().getColumnType(i)==93)
				  {

				    sd[i-1]=rs.getTime(i).toString();					  	
				  }
				  else
				   sd[i-1]=rs.getString(i);
				 
				}
				array.add(sd);
			}
			rs.close();
			state.close();
		}catch(Exception e)
		{
			try
			{
		 	rs.close();
		 	state.close();
		 	conn.close();
			}
			catch(Exception ae)
			{
			ae.printStackTrace();
			}
			e.printStackTrace();
		}

		return array;
	
	}
	public int doing(String sql)
	{
		
		int count=0;
	try{
	    state=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);	    
	    count=state.executeUpdate(sql);	
	    }catch(Exception e){
		    try{
			rs.close();
		 	state.close();
		 	
			}catch(Exception ex)
			{
				ex.printStackTrace();
			}
			
		               }
            return count;
	}
 
	
   public Time getEmpOntime(int empid)
   {
   	 	
	
   	  try{
   	  	    pst=conn.prepareStatement("select EmpOnTime from Employee where EmpID="+String.valueOf(empid));
   	  	    //pst.setInt(1,empid);
   	  	    rs=pst.executeQuery();
   	  	   while(rs.next())
   	  	   {
   	  	   	 ontime=rs.getTime("EmpOnTime");
   	  	   }
   	  	   rs.close();
   	  	   pst.close();
		 
   	  }catch(Exception e){
   	      try{
			rs.close();
		 	pst.close();
		 	conn.close();	
			}catch(Exception ex)
			{
				ex.printStackTrace();
			}
   	  }
   	  return ontime ;
   }
   public boolean queryEmployee(int empid)
   {
   
  
   	try{
   	  	    pst=conn.prepareStatement("select EmpName from Employee where EmpID="+String.valueOf(empid));
   	  	    //pst.setInt(1,empid);
   	  	    rs=pst.executeQuery();
   	  	   
   	  	   
   	  	    while(rs.next())
   	  	    {
   	  	    	
   	  	    	count++;
   	  	    	break;
   	  	    }
   	  	    rs.close();
   	  	    pst.close();
   	  	   
   	   }catch(Exception e){
   	      
		e.printStackTrace();
			
   	  }
      
   	  if(count>0)
   	  return true;
   	  else
   	  return false;
   }
  public ArrayList getEmp_TotalData(int empid)
  {
  	ArrayList array=new ArrayList();
  	try{
  		//;
  		  pst=conn.prepareStatement("select Employee.EmpID,Employee.EmpName,Record.Type,Record.CardTime,Record.Result,Record.BeLastTime from Employee , Record where Employee.EmpID like Record.EmpID and Record.EmpID like "+String.valueOf(empid));
//  		  pst.setInt(1,empid);
  		  rs=pst.executeQuery();
  		  
  		
			
		 	
  		  while(rs.next())
  		  {
  		  	String sd[]=new String[rs.getMetaData().getColumnCount()];
  		  	for(int i=1;i<=rs.getMetaData().getColumnCount();i++)
  		  	{
  		  		sd[i-1]=rs.getString(i);
  		  	
  		  	}
  		   	array.add(sd);
  		  }	
  		 
  		  rs.close();
  		  pst.close();
  		
  		  
  	}catch(Exception e)
  	{
  		e.printStackTrace();
  	}
    return array;
  }	
    public ArrayList getEmp_TotalData2(int empid)
  {
  	ArrayList array=new ArrayList();
  	try{
  		//;
  		  pst=conn.prepareStatement("select EmpID,left(CardTime,7) as YearMonth,Result,count(*) as Times from Record where EmpID like "+String.valueOf(empid)+" group by EmpID,left(CardTime,7),Result");
//  		  pst.setInt(1,empid);
  		  rs=pst.executeQuery();
  		  
  		
			
		 	
  		  while(rs.next())
  		  {
  		  	String sd[]=new String[rs.getMetaData().getColumnCount()];
  		  	for(int i=1;i<=rs.getMetaData().getColumnCount();i++)
  		  	{
  		  		sd[i-1]=rs.getString(i);
  		  	
  		  	}
  		   	array.add(sd);
  		  }	
  		 
  		  rs.close();
  		  pst.close();
  		
  		  
  	}catch(Exception e)
  	{
  		e.printStackTrace();
  	}
    return array;
  }	
  public ArrayList getEmp_TotalName(String sql)
  {
  	  ArrayList totalname=new ArrayList();
  	try{
  		
  		state=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
  	
  		rs=state.executeQuery(sql);
  		  
  		for(int i=1;i<=rs.getMetaData().getColumnCount();i++)
		 {
		  	
		  totalname.add(rs.getMetaData().getColumnName(i));
		 }
  		
  		
  	}catch(Exception e)
  	{
  		e.printStackTrace();
  	}
  	
  	
  	 return totalname;
  }
    public ArrayList getEmp_TotalName2(String sql)
  {
  	  ArrayList totalname=new ArrayList();
  	try{
  		
  		state=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
  	
  		rs=state.executeQuery(sql);
  		  
  		for(int i=1;i<=rs.getMetaData().getColumnCount();i++)
		 {
		  	
		  totalname.add(rs.getMetaData().getColumnName(i));
		 }
  		
  		
  	}catch(Exception e)
  	{
  		e.printStackTrace();
  	}
  	
  	
  	 return totalname;
  }
  public ArrayList getAllTotalData(String sql)
  {
  	ArrayList array=new ArrayList();
  	try{
  		
  		   state=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
  		 
  		   rs=state.executeQuery(sql);
  		  
  		   
  		  while(rs.next())
  		  {
  		  	String sd[]=new String[rs.getMetaData().getColumnCount()];
  		  	for(int i=1;i<=rs.getMetaData().getColumnCount();i++)
  		  	{
  		  		sd[i-1]=rs.getString(i);
  		  	    
  		  	}
  		   	array.add(sd);
  		  }	
  		 
  		  rs.close();
  		  state.close();
  		
  		  
  	}catch(Exception e)
  	{
  		e.printStackTrace();
  	}
    return array;
  }
    public ArrayList getAllTotalData2(String sql)
  {
  	ArrayList array=new ArrayList();
  	try{
  		
  		   state=conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_UPDATABLE);
  		 
  		   rs=state.executeQuery(sql);
  		  
  		   
  		  while(rs.next())
  		  {
  		  	String sd[]=new String[rs.getMetaData().getColumnCount()];
  		  	for(int i=1;i<=rs.getMetaData().getColumnCount();i++)
  		  	{
  		  		sd[i-1]=rs.getString(i);
  		  	    
  		  	}
  		   	array.add(sd);
  		   }	
  		 
  		  rs.close();
  		  state.close();
  		
  		  
  	}catch(Exception e)
  	{
  		e.printStackTrace();
  	}
    return array;
  }
  public Time getEmpOfftime(int empid)
   {
   	 	
	
   	  try{
   	  	    pst=conn.prepareStatement("select EmpOffTime from Employee where EmpID="+String.valueOf(empid));
   	  	   // pst.setInt(1,empid);
   	  	    rs=pst.executeQuery();
   	  	   while(rs.next())
   	  	   {
   	  	   	 offtime=rs.getTime("EmpOffTime");
   	  	   }
   	  	   rs.close();
   	  	   pst.close();
		 
   	  }catch(Exception e){
   	      try{
			rs.close();
		 	pst.close();
		 	conn.close();	
			}catch(Exception ex)
			{
				ex.printStackTrace();
			}
   	  }
   	  return offtime ;
   }
}