databasemanager.java

编写的人事管理系统!其中还要写功能没有实现,代码仅供参考.

/** 
 * access MSSQl database through JDBC Driver 
 **/
package dataBase;
import java.sql.*;
import java.util.Date;
import java.util.HashMap;
import javaBean.Worker;
//import javax.sql.*;
//import java.util.ArrayList;
//import java.util.Collection;
//import java.util.Collections;
/**
 *  @author SDH
 */
public class DataBaseManager {
    /**
     * 配置属性............................................................................/properties
     */
    private String dbUrl =  "jdbc:jtds:sqlserver://localhost:1433/RSDB";
    private String dbUser="sa";
    private String dbPwd="dh678504";
    Connection con=null;
    Statement stmt=null;
    PreparedStatement prepStmt =null; 
    ResultSet rs =null;
    
    public DataBaseManager() throws Exception{
    	Class.forName("net.sourceforge.jtds.jdbc.Driver");
    }
    
    //事务方法.........................................................................................../Method
    
    /**
     * 验证登录,并返回id,姓名;
     */
    public String[] validata(String user,String pw){
    	Connection con = null;
    	Statement stmt = null;
    	ResultSet rs = null;
    	String[] re = new String[3];
    	
    	try{
    		String sql = "select id,name,power from employee where username = '"+user+"' and  pw = '"+pw+"'";
    		rs = getResultReadOnly(sql);
    		
    		if(rs.next()){
    			
    			re[0] = rs.getString("name");
    			re[1] = String.valueOf(rs.getInt("id"));
    			re[2] = rs.getString("power");
    			closeResultSet(rs);
	    		closeStmt(stmt);
	    		closeConnection(con);
	    		
    			return re;
    			
    		}else{
    			closeResultSet(rs);
	    		closeStmt(stmt);
	    		closeConnection(con);
    			return null;
    		}
    	    	}catch(SQLException sqle){
    	    		System.out.println(sqle.toString());
    	    		return null;
    	    	}
    	    	
    	  }
    /**
     * 更改已经登录管理员的用户名和密码
     * @param_in id
     * @param_in newuser 用户名
     * @param_in newpw 新密码
     * @return boolean
     */
    public boolean monifyBase(int id,String newuser,String newpw){
    	
    	boolean right = false;
    	String sql = "update employee set username = '"+newuser+"' ,pw = '"+newpw+"' where id = "+id+" ";
    	right = updateSql(sql);
    	return right;
    	  	
    }
    /**
     * 添加部门
     * @throws SQLException 
     */
    public boolean addDepartment(String name) throws SQLException{
    	ResultSet rs = null;
    	rs = getResultReadOnly("select * from department where dname = '"+name+"'");
    	if(rs.next()){
    		return false;
    	}else{
    		String sql = "insert into department(dname) values('"+name+"')";
    		return updateSql(sql);
    	}
    }
    
    /**
     * 返回包含部门表的HashMap
     * @throws SQLException 
     */
    @SuppressWarnings("unchecked")
	public HashMap reDepartment() throws SQLException{
       	rs = getResultReadOnly("select * from department");
       	HashMap hm = new HashMap();
       	hm.clear();
       	while(rs.next()){
       		hm.put(rs.getInt("id"),rs.getString("dname"));
       	}
		return hm;
    	
    }
    
    /**
     * 修改部门表
     */
    public boolean monifyDepartment(int id,String dname){
    	String sql = "update department set dname = '"+dname+"' where id = "+id+"";
    	return updateSql(sql);
    	
    }
    
    /**
     * 判断某部门是否还有员工
     * @throws SQLException 
     */
    public boolean checkEmploy(int id) throws SQLException{
    	rs = null;
    	rs = getResultReadOnly("select * from employee where department = "+id+"");
    	if(rs.next()){
    		return false;
    	}else{
    		return true;
    	}
    }
    /**
     * 删除部门表
     */
    public boolean deleteDepartment(int id){
    	String sql = "delete department where id = "+id+"";
    	return updateSql(sql);
    }
    
    /**
     * 判断员工表中是否有重复的员工信息。
     * @throws SQLException 
     */
    public boolean judgeUser(String user) throws SQLException{
    	rs = null;
    	rs = getResultReadOnly("select username from employee " +
    			" where username = '"+user+"' ");
    	if(rs.next()){
    		closeResultSet(rs);
    		return true;
    	}else{
    		closeResultSet(rs);
    		return false;
    	}
    	
    	
    }
    
    public boolean judgeName(String card) throws SQLException{
    	rs = null;
    	rs = getResultReadOnly("select idcard from employee " +
    			" where idcard = '"+card+"' ");
    	if(rs.next()){
    		closeResultSet(rs);
    		return true;
    	}else{
    		closeResultSet(rs);
    		return false;
    	}
    	
    	
    }
    
    public boolean judgeName2(String card,int id) throws SQLException{
    	rs = null;
    	rs = getResultReadOnly("select idcard from employee " +
    			" where idcard = '"+card+"' and id != "+id+" ");
    	if(rs.next()){
    		closeResultSet(rs);
    		return true;
    	}else{
    		closeResultSet(rs);
    		return false;
    	}
    	
    	
    }
    
    /**
     * 向员工表插入员工信息
     */
    public boolean addEmployee(String name,String sex,String dateBirth,int depart,String job, 
    		float pay,String dateEmploy,String card,String pic,String info,String user,String pw,String power){
    	closePrepStmt(prepStmt);
    	closeConnection(con);
    	String sql = "insert into employee(username,pw,name,sex,birth,department,job,pay,employdate,idcard,pic,info,power) " +
    			" values('"+user+"','"+pw+"','"+name+"','"+sex+"','"+dateBirth+"',"+depart+",'"+job+"',"+pay+", " +
    			" '"+dateEmploy+"','"+card+"','"+pic+"','"+info+"','"+power+"') ";
    	System.out.println(sql);
    	if(updateSql(sql)){
    		return true;
    	}else{
    		return false;
    	}
    	
    }
    
    /**
     * 返回搜索信息  rs.
     */
    //只有一个部门编号字段  模糊查询
    public ResultSet reSearch(int id){
    	rs = null;
    	rs = getResultSCROLL("select employee.id,name,sex,job,idcard,dname from employee,department " +
    			" where employee.department =department.id and employee.department = "+id+"" );
    	
    	return rs;
    	
    }
    //参数分别是：部门编号，员工表字段，搜索输入的信息。。
    public ResultSet reSearch(int id,String ziduan,String in){
    	rs = null;
    	rs = getResultSCROLL("select employee.id,name,sex,job,idcard,dname from employee,department " +
    			" where employee.department =department.id and employee.department = "+id+" and "+ziduan+" like '%"+in+"%' " );
    	
    	return rs;
    }
    
    /**
     * 删除员工信息
     */
    public boolean deleteEmployee(int id){
    	String sql = "delete employee where id = "+id+"";
    	return updateSql(sql);
    }
    
    /**
     * 根据id编号号得到所有的员工字段信息.
     * @throws SQLException 
     */
    public Worker getAllE(int id) throws SQLException{
    	rs = null;
    	rs = getResultReadOnly("select * from employee where id = "+id+" ");
    	rs.next();
		return  new Worker(rs.getInt("id"),rs.getString("name"),rs.getString("sex"),
				 rs.getDate("birth"),rs.getString("department"),rs.getString("job"), 
				 rs.getFloat("pay"),rs.getDate("employdate"),rs.getString("idcard"), 
				 rs.getString("pic"),rs.getString("info"),rs.getString("power"));
    	
    }
    
    /**
     * 修改员工信息。。。。。。
     */
    public boolean modifyE(int id,String name,String sex,String birth,int depart,String job, 
    		float pay,String employ,String card,String pic,String info){
    	
    	String sql = "update employee set name = '"+name+"',sex = '"+sex+"',birth = '"+birth+"',  " +
    			" department = "+depart+",job = '"+job+"', pay = "+pay+",employdate = '"+employ+"', " +
    			" idcard = '"+card+"', pic = '"+pic+"',info = '"+info+"' where id = "+id+" ";
    	System.out.println(sql);
    	return updateSql(sql);
    }
    /**
     * 利用获得的参数,备份数据库.
     */
    public boolean backup(String path){
    	String sql = "use master " +
    			"if exists(select * from sysdevices  where name = 'RSBACKUP') "+
    			"exec sp_dropdevice \'RSBACKUP\' " +
    			"use RSDB " +
    			"exec sp_addumpdevice \'disk\',\'RSBACKUP\','"+path+"'  " +
    			"backup database RSDB to RSBACKUP ";
    	if(updateSql(sql)){
    		return true;
    	}else{
    		return false;
    	}
    	
    }
    /**
     * 一些常用方法集合......................................................................../Method
     */
    
    public Connection getConnection()throws Exception{
        return java.sql.DriverManager.getConnection(dbUrl,dbUser,dbPwd);
    }
    
    public void closeConnection(Connection con){
        try{
            if(con!=null) con.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public void closePrepStmt(PreparedStatement prepStmt){
        try{
            if(prepStmt!=null) prepStmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public void closeStmt(Statement stmt){
        try{
            if(stmt!=null) stmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public void closeResultSet(ResultSet rs){
        try{
            if(rs!=null) rs.close();
        }catch(Exception e){
            e.printStackTrace();
        }
    }
    
    public ResultSet getResultReadOnly(String strSQL){
        try {
            con=getConnection();
            stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
            rs = stmt.executeQuery(strSQL);
            return rs; 
       }catch(SQLException sqle) {
            System.out.println(sqle.toString());
            return null;
        }catch(Exception lange){
            System.out.println(lange.toString());
            return null;
        }
        
    }
    
    public ResultSet getResultSCROLL(String strSQL){
        try {
            con=getConnection();
            prepStmt = con.prepareStatement(strSQL,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE );
            rs = prepStmt.executeQuery();
            return rs; 
       }catch(SQLException sqle) {
            System.out.println(sqle.toString());
            return null;
        }catch(Exception lange){
            System.out.println(lange.toString());
            return null;
        }
    }
    
    public boolean updateSql(String strSQL) {
        Connection con=null;
        PreparedStatement prepStmt=null;
        try {
            con=getConnection();
            prepStmt = con.prepareStatement(strSQL);
            prepStmt.executeUpdate();
            //con.commit();
            return true;  
        }catch(SQLException sqle) {
            System.out.println(sqle.toString());
            return false;
        }catch(Exception lange){
            System.out.println(lange.toString());
            return false;
        }
    }
    

}