java操作数据库——分层开发

file:///C:/Users/jjit/AppData/Local/Temp/Rar$DIa0.776/企业人才管理系统.pdf

package com.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.util.ConfigManager;

public class BaseDao {
    String driver = ConfigManager.getConfigManager().getString("jdbc.driver");
    String url = ConfigManager.getConfigManager().getString("jdbc.url");
    String user = ConfigManager.getConfigManager().getString("jdbc.user");
    String password = ConfigManager.getConfigManager().getString("jdbc.pwd");
    
    //获取数据库连接
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public boolean getConnection() {
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url,user,password);
            if(conn != null) {
                return true;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
            return false;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        } 
        return false;
    }

    //增删改
    public boolean executeUpdate(String sql,Object[] obj) {
        if(getConnection()) {
            try {
                PreparedStatement ps = conn.prepareStatement(sql);
                //填充占位符
                if(obj != null) {
                    for(int i = 0;i<obj.length;i++) {
                        ps.setObject(i+1,obj[i]);
                    }
                }
                int num =ps.executeUpdate();
                if(num > 0) {
                    return true;
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                closeResouce();
            }
        }
        return false;
    }
    //查
    public ResultSet executeQuery(String sql,Object[] obj) {
        if(getConnection()) {
            try {
                ps = conn.prepareStatement(sql);
                //填充占位符
                if(obj != null) {
                    for(int i = 0;i<obj.length;i++) {
                        ps.setObject(i+1,obj[i]);
                    }
                }
                rs =ps.executeQuery();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rs;
    }
    //释放资源
    public void closeResouce() {
        if(rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package com.dao;
/*
 * 创建DAO组件。实现： 
 * A. 分页查询所有的人才信息，按工作年限降序排列 
 * B. 查询所有的部门名称
 * C. 指定部门名称，分页查询人才信息，按工作年限降序排列 
 * D. 根据人才编号查询人才信息 
 * E. 添加人才信息 
 * F．修改人才信息 
 * G．删除人才信息 
 * */

import java.util.List;

import com.pojo.Departments;
import com.pojo.Talents;

public interface TalentManageDao {
    //A. 分页查询所有的人才信息，按工作年限降序排列 
    public List<Talents> selectAllTalent();
    
    //B. 查询所有的部门名称
    public List<Departments> selectDepartment();
    
    //C. 指定部门名称，分页查询人才信息，按工作年限降序排列 
    public List<Talents> selectByDepartment(String str);
    
    //D. 根据人才编号查询人才信息 
    public Talents selectTanlent(int i);
    
    //E. 添加人才信息 
    public boolean insertTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool);
    
    //F．修改人才信息 
    public boolean updateTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool,int id);
    
    //G．删除人才信息 
    public boolean deleteTalent(int id);
}

package com.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.pojo.Departments;
import com.pojo.Talents;

public class TalentManageDaoImpl extends BaseDao implements TalentManageDao{

    @Override
    public List<Talents> selectAllTalent() {
        String sql = "SELECT enterprisetalent.id, enterprisetalent.`name`, workingLife, workExperience,personalProfile,department.`name`,graduateSchool from enterprisetalent,department where enterprisetalent.departmentId=department.id";
        Object[] obj = {};
        List<Talents> list = new ArrayList<Talents>();
        Talents tal = null;
        ResultSet rs = this.executeQuery(sql, obj);
        try {
            while(rs.next()) {
                tal = new Talents();
                tal.setId(rs.getInt("id"));
                tal.setName(rs.getString("name"));
                tal.setWorkingLife(rs.getInt("workingLife"));
                tal.setWorkExperience(rs.getString("workExperience"));
                tal.setPersonalProfile(rs.getString("personalProfile"));
                tal.setDepartment(rs.getString("name"));
                tal.setGraduateSchool(rs.getString("graduateSchool"));
                list.add(tal);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    @Override
    public List<Departments> selectDepartment() {
        String sql = "SELECT id,`name` from department";
        Object[] obj = {};
        Departments dep = null;
        List<Departments> list = new ArrayList<Departments>();
        ResultSet rs = this.executeQuery(sql, obj);
        try {
            while(rs.next()) {
                dep = new Departments();
                dep.setDepartmentId(rs.getInt("id"));
                dep.setDepartmentName(rs.getString("name"));
                list.add(dep);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // TODO Auto-generated method stub
        return list;
    }

    @Override
    public List<Talents> selectByDepartment(String str) {
        String sql = "SELECT enterprisetalent.id, enterprisetalent.`name`, workingLife, workExperience,personalProfile,department.`name`,graduateSchool from enterprisetalent,department where enterprisetalent.departmentId=department.id and department.`name`=? ORDER BY enterprisetalent.workingLife DESC";
        Object[] obj = {str};
        Talents tal =null;
        List<Talents> list = new ArrayList<Talents>();
        ResultSet rs = this.executeQuery(sql, obj);
        try {
            while(rs.next()) {
                tal = new Talents();
                tal.setId(rs.getInt("id"));
                tal.setName(rs.getString("name"));
                tal.setWorkingLife(rs.getInt("workingLife"));
                tal.setWorkExperience(rs.getString("workExperience"));
                tal.setPersonalProfile(rs.getString("personalProfile"));
                tal.setDepartment(rs.getString("name"));
                tal.setGraduateSchool(rs.getString("graduateSchool"));
                list.add(tal);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // TODO Auto-generated method stub
        return list;
    }

    @Override
    public Talents selectTanlent(int i) {
        String sql = "SELECT enterprisetalent.id, enterprisetalent.`name`, workingLife, workExperience,personalProfile,department.`name`,graduateSchool from enterprisetalent,department where enterprisetalent.departmentId=department.id and enterprisetalent.id=?";
        Object[] obj = {i};
        Talents tal =null;
        ResultSet rs = this.executeQuery(sql, obj);
        try {
            while(rs.next()) {
                tal = new Talents();
                tal.setId(rs.getInt("id"));
                tal.setName(rs.getString("name"));
                tal.setWorkingLife(rs.getInt("workingLife"));
                tal.setWorkExperience(rs.getString("workExperience"));
                tal.setPersonalProfile(rs.getString("personalProfile"));
                tal.setDepartment(rs.getString("name"));
                tal.setGraduateSchool(rs.getString("graduateSchool"));
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        // TODO Auto-generated method stub
        return tal;
    }

    @Override
    
    public boolean insertTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool) {
        String sql = "INSERT into enterprisetalent(`name`,workingLife,workExperience,personalProfile,departmentId,graduateSchool) VALUES (?,?,?,?,?,?)";
        Object[] obj = {name,workingLife,workExperience,personalProfile,departmentId,graduateSchool};
        boolean i = this.executeUpdate(sql, obj);
        // TODO Auto-generated method stub
        return i;
    }

    @Override
    public boolean updateTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool,int id) {
        String sql = "UPDATE enterprisetalent SET `name`=?,workingLife=?,workExperience=?,personalProfile=?,departmentId=?,graduateSchool=? where id=?";
        Object[] obj = {name,workingLife,workExperience,personalProfile,departmentId,graduateSchool,id};
        boolean i = this.executeUpdate(sql, obj);
        // TODO Auto-generated method stub
        return i;
    }

    @Override
    public boolean deleteTalent(int id) {
        String sql = "DELETE from enterprisetalent where id=?";
        Object[] obj = {id};
        boolean i = this.executeUpdate(sql, obj);
        // TODO Auto-generated method stub
        return i;
    }

}

package com.pojo;

public class Departments {
    private int departmentId;
    private String departmentName;
    
    public int getDepartmentId() {
        return departmentId;
    }
    public void setDepartmentId(int departmentId) {
        this.departmentId = departmentId;
    }
    public String getDepartmentName() {
        return departmentName;
    }
    public void setDepartmentName(String departmentName) {
        this.departmentName = departmentName;
    }
    @Override
    public String toString() {
        return "Departments [departmentId=" + departmentId + ", departmentName=" + departmentName + "]";
    }
    
    
}

package com.pojo;

public class Talents {
    private int id;                        //人才编号
    private String name;                //人才姓名
    private int workingLife;            //工作年限
    private String workExperience;        //工作经历
    private String personalProfile;        //个人简介
    private String department;            //所属部门
    private String graduateSchool;        //毕业学校
    
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getWorkingLife() {
        return workingLife;
    }
    public void setWorkingLife(int workingLife) {
        this.workingLife = workingLife;
    }
    public String getWorkExperience() {
        return workExperience;
    }
    public void setWorkExperience(String workExperience) {
        this.workExperience = workExperience;
    }
    public String getPersonalProfile() {
        return personalProfile;
    }
    public void setPersonalProfile(String personalProfile) {
        this.personalProfile = personalProfile;
    }
    public String getDepartment() {
        return department;
    }
    public void setDepartment(String department) {
        this.department = department;
    }
    public String getGraduateSchool() {
        return graduateSchool;
    }
    public void setGraduateSchool(String graduateSchool) {
        this.graduateSchool = graduateSchool;
    }
    @Override
    public String toString() {
        return "Talents [id=" + id + ", name=" + name + ", workingLife=" + workingLife + ", workExperience="
                + workExperience + ", personalProfile=" + personalProfile + ", department=" + department
                + ", graduateSchool=" + graduateSchool + "]";
    }
    
    
    

}

package com.service;
/*
 * 创建DAO组件。实现： 
 * A. 分页查询所有的人才信息，按工作年限降序排列 
 * B. 查询所有的部门名称
 * C. 指定部门名称，分页查询人才信息，按工作年限降序排列 
 * D. 根据人才编号查询人才信息 
 * E. 添加人才信息 
 * F．修改人才信息 
 * G．删除人才信息 
 * */

import java.util.List;

import com.pojo.Departments;
import com.pojo.Talents;

public interface TalentManageService {
    //A. 分页查询所有的人才信息，按工作年限降序排列 
    public List<Talents> selectAllTalent();
    //B. 查询所有的部门名称
    public List<Departments> selectDepartment();
    //C. 指定部门名称，分页查询人才信息，按工作年限降序排列 
    public List<Talents> selectByDepartment(String str);
    //D. 根据人才编号查询人才信息 
    public Talents selectTanlent(int i);
    //E. 添加人才信息 
    public boolean insertTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool);
    //F．修改人才信息 
    public boolean updateTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool,int id);
    //G．删除人才信息 
    public boolean deleteTalent(int id);
}

package com.service;

import java.util.List;

import com.dao.TalentManageDao;
import com.dao.TalentManageDaoImpl;
import com.pojo.Departments;
import com.pojo.Talents;

public class TalentManageServiceImpl implements TalentManageService{
    TalentManageDao tmd = new TalentManageDaoImpl();

    @Override
    public List<Talents> selectAllTalent() {
        return tmd.selectAllTalent();
    }

    @Override
    public List<Departments> selectDepartment() {
        // TODO Auto-generated method stub
        return tmd.selectDepartment();
    }

    @Override
    public List<Talents> selectByDepartment(String str) {
        // TODO Auto-generated method stub
        return tmd.selectByDepartment(str);
    }

    @Override
    public Talents selectTanlent(int i) {
        // TODO Auto-generated method stub
        return tmd.selectTanlent(i);
    }

    @Override
    public boolean insertTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool) {
        // TODO Auto-generated method stub
        return tmd.insertTalent(name, workingLife, workExperience, personalProfile, departmentId, graduateSchool);
    }

    @Override
    public boolean updateTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool,int id) {
        // TODO Auto-generated method stub
        return tmd.updateTalent(name, workingLife, workExperience, personalProfile, departmentId, graduateSchool, id);
    }

    @Override
    public boolean deleteTalent(int id) {
        // TODO Auto-generated method stub
        return tmd.deleteTalent(id);
    }

}

package com.test;

import java.util.List;

import com.pojo.Departments;
import com.pojo.Talents;
import com.service.TalentManageService;
import com.service.TalentManageServiceImpl;

public class Test {
    public static void main(String[] args) {
        TalentManageService tms = new TalentManageServiceImpl();
        List<Talents> a = tms.selectAllTalent();
        for(Talents i : a) {
            System.out.println(i);
        }
        System.out.println("******************************************************************************************************************");
        List<Departments> b = tms.selectDepartment();
        for(Departments dt : b) {
            System.out.println(dt);
        }
        System.out.println("******************************************************************************************************************");
        List<Talents> c = tms.selectByDepartment("技术部");
        for(Talents dt : c) {
            System.out.println(dt);
        }
        System.out.println("******************************************************************************************************************");
        Talents t = tms.selectTanlent(2);
        System.out.println(t);
        System.out.println("******************************************************************************************************************");
        boolean boo = tms.insertTalent("王五",5, "无", "无", 2,"杭州大学");
        if(boo == true) {
            System.out.println("添加成功");
        }else {
            System.out.println("添加失败");
        }
        System.out.println("******************************************************************************************************************");
        boolean d = tms.updateTalent("李逵", 6, "无", "有", 3, "北京大学", 4);
        if(d == true) {
            System.out.println("修改成功");
        }else {
            System.out.println("修改失败");
        }
        System.out.println("******************************************************************************************************************");
        boolean i = tms.deleteTalent(5);
        if(i == true) {
            System.out.println("删除成功");
        }else {
            System.out.println("删除失败");
        }
    }
}

package com.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.Properties;

public class ConfigManager {
    private static ConfigManager cm;
    private Properties pp;
    
    private ConfigManager() {
        InputStream is = ConfigManager.class.getClassLoader().getResourceAsStream("database.properties");
        pp = new Properties();
        try {
            pp.load(is);
            is.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    public static ConfigManager getConfigManager() {
        if(cm == null) {
            cm = new ConfigManager();
        }
        return cm;
    }
    
    public String getString(String key) {
        return pp.getProperty(key);
    }
}