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

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

  1 package com.dao;
  2 
  3 import java.sql.Connection;
  4 import java.sql.DriverManager;
  5 import java.sql.PreparedStatement;
  6 import java.sql.ResultSet;
  7 import java.sql.SQLException;
  8 
  9 import com.util.ConfigManager;
 10 
 11 public class BaseDao {
 12     String driver = ConfigManager.getConfigManager().getString("jdbc.driver");
 13     String url = ConfigManager.getConfigManager().getString("jdbc.url");
 14     String user = ConfigManager.getConfigManager().getString("jdbc.user");
 15     String password = ConfigManager.getConfigManager().getString("jdbc.pwd");
 16     
 17     //获取数据库连接
 18     Connection conn = null;
 19     PreparedStatement ps = null;
 20     ResultSet rs = null;
 21     public boolean getConnection() {
 22         try {
 23             Class.forName(driver);
 24             conn = DriverManager.getConnection(url,user,password);
 25             if(conn != null) {
 26                 return true;
 27             }
 28         } catch (ClassNotFoundException e) {
 29             e.printStackTrace();
 30             return false;
 31         } catch (SQLException e) {
 32             e.printStackTrace();
 33             return false;
 34         } 
 35         return false;
 36     }
 37 
 38     //增删改
 39     public boolean executeUpdate(String sql,Object[] obj) {
 40         if(getConnection()) {
 41             try {
 42                 PreparedStatement ps = conn.prepareStatement(sql);
 43                 //填充占位符
 44                 if(obj != null) {
 45                     for(int i = 0;i<obj.length;i++) {
 46                         ps.setObject(i+1,obj[i]);
 47                     }
 48                 }
 49                 int num =ps.executeUpdate();
 50                 if(num > 0) {
 51                     return true;
 52                 }
 53             } catch (SQLException e) {
 54                 e.printStackTrace();
 55             } finally {
 56                 closeResouce();
 57             }
 58         }
 59         return false;
 60     }
 61     //查
 62     public ResultSet executeQuery(String sql,Object[] obj) {
 63         if(getConnection()) {
 64             try {
 65                 ps = conn.prepareStatement(sql);
 66                 //填充占位符
 67                 if(obj != null) {
 68                     for(int i = 0;i<obj.length;i++) {
 69                         ps.setObject(i+1,obj[i]);
 70                     }
 71                 }
 72                 rs =ps.executeQuery();
 73             } catch (SQLException e) {
 74                 e.printStackTrace();
 75             }
 76         }
 77         return rs;
 78     }
 79     //释放资源
 80     public void closeResouce() {
 81         if(rs != null) {
 82             try {
 83                 rs.close();
 84             } catch (SQLException e) {
 85                 e.printStackTrace();
 86             }
 87         }
 88         if(ps != null) {
 89             try {
 90                 ps.close();
 91             } catch (SQLException e) {
 92                 e.printStackTrace();
 93             }
 94         }
 95         if(conn != null) {
 96             try {
 97                 conn.close();
 98             } catch (SQLException e) {
 99                 e.printStackTrace();
100             }
101         }
102     }
103 }

 1 package com.dao;
 2 /*
 3  * 创建DAO组件。实现： 
 4  * A. 分页查询所有的人才信息，按工作年限降序排列 
 5  * B. 查询所有的部门名称
 6  * C. 指定部门名称，分页查询人才信息，按工作年限降序排列 
 7  * D. 根据人才编号查询人才信息 
 8  * E. 添加人才信息 
 9  * F．修改人才信息 
10  * G．删除人才信息 
11  * */
12 
13 import java.util.List;
14 
15 import com.pojo.Departments;
16 import com.pojo.Talents;
17 
18 public interface TalentManageDao {
19     //A. 分页查询所有的人才信息，按工作年限降序排列 
20     public List<Talents> selectAllTalent();
21     
22     //B. 查询所有的部门名称
23     public List<Departments> selectDepartment();
24     
25     //C. 指定部门名称，分页查询人才信息，按工作年限降序排列 
26     public List<Talents> selectByDepartment(String str);
27     
28     //D. 根据人才编号查询人才信息 
29     public Talents selectTanlent(int i);
30     
31     //E. 添加人才信息 
32     public boolean insertTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool);
33     
34     //F．修改人才信息 
35     public boolean updateTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool,int id);
36     
37     //G．删除人才信息 
38     public boolean deleteTalent(int id);
39 }

  1 package com.dao;
  2 
  3 import java.sql.ResultSet;
  4 import java.sql.SQLException;
  5 import java.util.ArrayList;
  6 import java.util.List;
  7 
  8 import com.pojo.Departments;
  9 import com.pojo.Talents;
 10 
 11 public class TalentManageDaoImpl extends BaseDao implements TalentManageDao{
 12 
 13     @Override
 14     public List<Talents> selectAllTalent() {
 15         String sql = "SELECT enterprisetalent.id, enterprisetalent.`name`, workingLife, workExperience,personalProfile,department.`name`,graduateSchool from enterprisetalent,department where enterprisetalent.departmentId=department.id";
 16         Object[] obj = {};
 17         List<Talents> list = new ArrayList<Talents>();
 18         Talents tal = null;
 19         ResultSet rs = this.executeQuery(sql, obj);
 20         try {
 21             while(rs.next()) {
 22                 tal = new Talents();
 23                 tal.setId(rs.getInt("id"));
 24                 tal.setName(rs.getString("name"));
 25                 tal.setWorkingLife(rs.getInt("workingLife"));
 26                 tal.setWorkExperience(rs.getString("workExperience"));
 27                 tal.setPersonalProfile(rs.getString("personalProfile"));
 28                 tal.setDepartment(rs.getString("name"));
 29                 tal.setGraduateSchool(rs.getString("graduateSchool"));
 30                 list.add(tal);
 31             }
 32         } catch (SQLException e) {
 33             e.printStackTrace();
 34         }
 35         return list;
 36     }
 37 
 38     @Override
 39     public List<Departments> selectDepartment() {
 40         String sql = "SELECT id,`name` from department";
 41         Object[] obj = {};
 42         Departments dep = null;
 43         List<Departments> list = new ArrayList<Departments>();
 44         ResultSet rs = this.executeQuery(sql, obj);
 45         try {
 46             while(rs.next()) {
 47                 dep = new Departments();
 48                 dep.setDepartmentId(rs.getInt("id"));
 49                 dep.setDepartmentName(rs.getString("name"));
 50                 list.add(dep);
 51             }
 52         } catch (SQLException e) {
 53             // TODO Auto-generated catch block
 54             e.printStackTrace();
 55         }
 56         // TODO Auto-generated method stub
 57         return list;
 58     }
 59 
 60     @Override
 61     public List<Talents> selectByDepartment(String str) {
 62         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";
 63         Object[] obj = {str};
 64         Talents tal =null;
 65         List<Talents> list = new ArrayList<Talents>();
 66         ResultSet rs = this.executeQuery(sql, obj);
 67         try {
 68             while(rs.next()) {
 69                 tal = new Talents();
 70                 tal.setId(rs.getInt("id"));
 71                 tal.setName(rs.getString("name"));
 72                 tal.setWorkingLife(rs.getInt("workingLife"));
 73                 tal.setWorkExperience(rs.getString("workExperience"));
 74                 tal.setPersonalProfile(rs.getString("personalProfile"));
 75                 tal.setDepartment(rs.getString("name"));
 76                 tal.setGraduateSchool(rs.getString("graduateSchool"));
 77                 list.add(tal);
 78             }
 79         } catch (SQLException e) {
 80             // TODO Auto-generated catch block
 81             e.printStackTrace();
 82         }
 83         // TODO Auto-generated method stub
 84         return list;
 85     }
 86 
 87     @Override
 88     public Talents selectTanlent(int i) {
 89         String sql = "SELECT enterprisetalent.id, enterprisetalent.`name`, workingLife, workExperience,personalProfile,department.`name`,graduateSchool from enterprisetalent,department where enterprisetalent.departmentId=department.id and enterprisetalent.id=?";
 90         Object[] obj = {i};
 91         Talents tal =null;
 92         ResultSet rs = this.executeQuery(sql, obj);
 93         try {
 94             while(rs.next()) {
 95                 tal = new Talents();
 96                 tal.setId(rs.getInt("id"));
 97                 tal.setName(rs.getString("name"));
 98                 tal.setWorkingLife(rs.getInt("workingLife"));
 99                 tal.setWorkExperience(rs.getString("workExperience"));
100                 tal.setPersonalProfile(rs.getString("personalProfile"));
101                 tal.setDepartment(rs.getString("name"));
102                 tal.setGraduateSchool(rs.getString("graduateSchool"));
103             }
104         } catch (SQLException e) {
105             // TODO Auto-generated catch block
106             e.printStackTrace();
107         }
108         // TODO Auto-generated method stub
109         return tal;
110     }
111 
112     @Override
113     
114     public boolean insertTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool) {
115         String sql = "INSERT into enterprisetalent(`name`,workingLife,workExperience,personalProfile,departmentId,graduateSchool) VALUES (?,?,?,?,?,?)";
116         Object[] obj = {name,workingLife,workExperience,personalProfile,departmentId,graduateSchool};
117         boolean i = this.executeUpdate(sql, obj);
118         // TODO Auto-generated method stub
119         return i;
120     }
121 
122     @Override
123     public boolean updateTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool,int id) {
124         String sql = "UPDATE enterprisetalent SET `name`=?,workingLife=?,workExperience=?,personalProfile=?,departmentId=?,graduateSchool=? where id=?";
125         Object[] obj = {name,workingLife,workExperience,personalProfile,departmentId,graduateSchool,id};
126         boolean i = this.executeUpdate(sql, obj);
127         // TODO Auto-generated method stub
128         return i;
129     }
130 
131     @Override
132     public boolean deleteTalent(int id) {
133         String sql = "DELETE from enterprisetalent where id=?";
134         Object[] obj = {id};
135         boolean i = this.executeUpdate(sql, obj);
136         // TODO Auto-generated method stub
137         return i;
138     }
139 
140 }

 1 package com.pojo;
 2 
 3 public class Departments {
 4     private int departmentId;
 5     private String departmentName;
 6     
 7     public int getDepartmentId() {
 8         return departmentId;
 9     }
10     public void setDepartmentId(int departmentId) {
11         this.departmentId = departmentId;
12     }
13     public String getDepartmentName() {
14         return departmentName;
15     }
16     public void setDepartmentName(String departmentName) {
17         this.departmentName = departmentName;
18     }
19     @Override
20     public String toString() {
21         return "Departments [departmentId=" + departmentId + ", departmentName=" + departmentName + "]";
22     }
23     
24     
25 }

 1 package com.pojo;
 2 
 3 public class Talents {
 4     private int id;                        //人才编号
 5     private String name;                //人才姓名
 6     private int workingLife;            //工作年限
 7     private String workExperience;        //工作经历
 8     private String personalProfile;        //个人简介
 9     private String department;            //所属部门
10     private String graduateSchool;        //毕业学校
11     
12     public int getId() {
13         return id;
14     }
15     public void setId(int id) {
16         this.id = id;
17     }
18     public String getName() {
19         return name;
20     }
21     public void setName(String name) {
22         this.name = name;
23     }
24     public int getWorkingLife() {
25         return workingLife;
26     }
27     public void setWorkingLife(int workingLife) {
28         this.workingLife = workingLife;
29     }
30     public String getWorkExperience() {
31         return workExperience;
32     }
33     public void setWorkExperience(String workExperience) {
34         this.workExperience = workExperience;
35     }
36     public String getPersonalProfile() {
37         return personalProfile;
38     }
39     public void setPersonalProfile(String personalProfile) {
40         this.personalProfile = personalProfile;
41     }
42     public String getDepartment() {
43         return department;
44     }
45     public void setDepartment(String department) {
46         this.department = department;
47     }
48     public String getGraduateSchool() {
49         return graduateSchool;
50     }
51     public void setGraduateSchool(String graduateSchool) {
52         this.graduateSchool = graduateSchool;
53     }
54     @Override
55     public String toString() {
56         return "Talents [id=" + id + ", name=" + name + ", workingLife=" + workingLife + ", workExperience="
57                 + workExperience + ", personalProfile=" + personalProfile + ", department=" + department
58                 + ", graduateSchool=" + graduateSchool + "]";
59     }
60     
61     
62     
63 
64 }

 1 package com.service;
 2 /*
 3  * 创建DAO组件。实现： 
 4  * A. 分页查询所有的人才信息，按工作年限降序排列 
 5  * B. 查询所有的部门名称
 6  * C. 指定部门名称，分页查询人才信息，按工作年限降序排列 
 7  * D. 根据人才编号查询人才信息 
 8  * E. 添加人才信息 
 9  * F．修改人才信息 
10  * G．删除人才信息 
11  * */
12 
13 import java.util.List;
14 
15 import com.pojo.Departments;
16 import com.pojo.Talents;
17 
18 public interface TalentManageService {
19     //A. 分页查询所有的人才信息，按工作年限降序排列 
20     public List<Talents> selectAllTalent();
21     //B. 查询所有的部门名称
22     public List<Departments> selectDepartment();
23     //C. 指定部门名称，分页查询人才信息，按工作年限降序排列 
24     public List<Talents> selectByDepartment(String str);
25     //D. 根据人才编号查询人才信息 
26     public Talents selectTanlent(int i);
27     //E. 添加人才信息 
28     public boolean insertTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool);
29     //F．修改人才信息 
30     public boolean updateTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool,int id);
31     //G．删除人才信息 
32     public boolean deleteTalent(int id);
33 }

 1 package com.service;
 2 
 3 import java.util.List;
 4 
 5 import com.dao.TalentManageDao;
 6 import com.dao.TalentManageDaoImpl;
 7 import com.pojo.Departments;
 8 import com.pojo.Talents;
 9 
10 public class TalentManageServiceImpl implements TalentManageService{
11     TalentManageDao tmd = new TalentManageDaoImpl();
12 
13     @Override
14     public List<Talents> selectAllTalent() {
15         return tmd.selectAllTalent();
16     }
17 
18     @Override
19     public List<Departments> selectDepartment() {
20         // TODO Auto-generated method stub
21         return tmd.selectDepartment();
22     }
23 
24     @Override
25     public List<Talents> selectByDepartment(String str) {
26         // TODO Auto-generated method stub
27         return tmd.selectByDepartment(str);
28     }
29 
30     @Override
31     public Talents selectTanlent(int i) {
32         // TODO Auto-generated method stub
33         return tmd.selectTanlent(i);
34     }
35 
36     @Override
37     public boolean insertTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool) {
38         // TODO Auto-generated method stub
39         return tmd.insertTalent(name, workingLife, workExperience, personalProfile, departmentId, graduateSchool);
40     }
41 
42     @Override
43     public boolean updateTalent(String name,int workingLife,String workExperience,String personalProfile,int departmentId,String graduateSchool,int id) {
44         // TODO Auto-generated method stub
45         return tmd.updateTalent(name, workingLife, workExperience, personalProfile, departmentId, graduateSchool, id);
46     }
47 
48     @Override
49     public boolean deleteTalent(int id) {
50         // TODO Auto-generated method stub
51         return tmd.deleteTalent(id);
52     }
53 
54 }

 1 package com.test;
 2 
 3 import java.util.List;
 4 
 5 import com.pojo.Departments;
 6 import com.pojo.Talents;
 7 import com.service.TalentManageService;
 8 import com.service.TalentManageServiceImpl;
 9 
10 public class Test {
11     public static void main(String[] args) {
12         TalentManageService tms = new TalentManageServiceImpl();
13         List<Talents> a = tms.selectAllTalent();
14         for(Talents i : a) {
15             System.out.println(i);
16         }
17         System.out.println("******************************************************************************************************************");
18         List<Departments> b = tms.selectDepartment();
19         for(Departments dt : b) {
20             System.out.println(dt);
21         }
22         System.out.println("******************************************************************************************************************");
23         List<Talents> c = tms.selectByDepartment("技术部");
24         for(Talents dt : c) {
25             System.out.println(dt);
26         }
27         System.out.println("******************************************************************************************************************");
28         Talents t = tms.selectTanlent(2);
29         System.out.println(t);
30         System.out.println("******************************************************************************************************************");
31         boolean boo = tms.insertTalent("王五",5, "无", "无", 2,"杭州大学");
32         if(boo == true) {
33             System.out.println("添加成功");
34         }else {
35             System.out.println("添加失败");
36         }
37         System.out.println("******************************************************************************************************************");
38         boolean d = tms.updateTalent("李逵", 6, "无", "有", 3, "北京大学", 4);
39         if(d == true) {
40             System.out.println("修改成功");
41         }else {
42             System.out.println("修改失败");
43         }
44         System.out.println("******************************************************************************************************************");
45         boolean i = tms.deleteTalent(5);
46         if(i == true) {
47             System.out.println("删除成功");
48         }else {
49             System.out.println("删除失败");
50         }
51     }
52 }

 1 package com.util;
 2 
 3 import java.io.IOException;
 4 import java.io.InputStream;
 5 import java.util.Properties;
 6 
 7 public class ConfigManager {
 8     private static ConfigManager cm;
 9     private Properties pp;
10     
11     private ConfigManager() {
12         InputStream is = ConfigManager.class.getClassLoader().getResourceAsStream("database.properties");
13         pp = new Properties();
14         try {
15             pp.load(is);
16             is.close();
17         } catch (IOException e) {
18             e.printStackTrace();
19         }
20     }
21     
22     public static ConfigManager getConfigManager() {
23         if(cm == null) {
24             cm = new ConfigManager();
25         }
26         return cm;
27     }
28     
29     public String getString(String key) {
30         return pp.getProperty(key);
31     }
32 }