1. BaseDao類
import java.sql.*;/*** 通用的工具類 ,負責連接數據， 執行增刪改查的通用方法*/
public class BaseDao {private Connection connection;private PreparedStatement pstm;private ResultSet rs;/*** 建立數據庫連接** @return*/public Boolean getConnection(){try {Class.forName("com.mysql.jdbc.Driver");connection =DriverManager.getConnection("jdbc:mysql://主機:端口/連接的數據庫名稱","根用戶名","密碼");return true;} catch (ClassNotFoundException e) {e.printStackTrace();return false;} catch (SQLException e) {e.printStackTrace();return false;}}/*** @param sql insert into xx values (?,?,?)* @param params 參數個數和占位符個數一致* new params[]{"a","b","c"}* @return*/public int update(String sql, Object[] params){try {if (this.getConnection()){pstm = connection.prepareStatement(sql);if (params!=null && params.length>0){for (int i=0; i < params.length; i++) {pstm.setObject(i+1,params[i]);}}int num = pstm.executeUpdate();return num;}} catch (SQLException e) {e.printStackTrace();}return 0;}/*** 查詢** @param sql select * from xxx where xx =? and xx=?* @param params new []{"xxx","xxx"}* @return*/public ResultSet query(String sql, Object[] params){try {if (this.getConnection()){pstm = connection.prepareStatement(sql);if (params!=null && params.length>0){for (int i=0; i < params.length; i++) {pstm.setObject(i+1,params[i]);}}rs = pstm.executeQuery();}} catch (SQLException e) {e.printStackTrace();}return rs;}/*** 關閉數據庫連接*/public void close(){try {if (rs!=null){rs.close();}if (pstm!=null){pstm.close();}if (connection!=null){connection.close();}} catch (SQLException e) {e.printStackTrace();}}
}主機，端口，根用戶名，密碼：
自選你需要連接的數據庫名：
填寫相應的數據，BaseDao就初始化完成了。
2. 指南
！食用指南前請先看文章Dao模式，更便于理解！
DAO模式-CSDN博客
接下來我將舉例來說明如何具體使用：
需求：根據雇員id添加/更新/刪除雇員信息。 根據部門id獲取部門名稱
我們需要連接到dept1來完成工作：
connection=DriverManager.getConnection("jdbc:mysql://localhost:3306/dept1","root","ok");????????表信息：
????????????????employees表：
????????????????departments表：
首先我們先創建兩個實體類：
? ? ? ? entity：
????????????????Employees
public class Employees {private Integer empId;private String empName;private Integer departmentId;private Double salary;private String empPasswd;public Employees() {}public Employees(Integer empId, String empName, Integer departmentId, Double salary, String empPasswd) {this.empId = empId;this.empName = empName;this.departmentId = departmentId;this.salary = salary;this.empPasswd = empPasswd;}public Integer getEmpId() {return empId;}public void setEmpId(Integer empId) {this.empId = empId;}public String getEmpName() {return empName;}public void setEmpName(String empName) {this.empName = empName;}public Integer getDepartmentId() {return departmentId;}public void setDepartmentId(Integer departmentId) {this.departmentId = departmentId;}public Double getSalary() {return salary;}public void setSalary(Double salary) {this.salary = salary;}public String getEmpPasswd() {return empPasswd;}public void setEmpPasswd(String empPasswd) {this.empPasswd = empPasswd;}@Overridepublic String toString() {return "Employees{" +"empId=" + empId +", empName='" + empName + '\'' +", departmentId=" + departmentId +", salary=" + salary +", empPasswd='" + empPasswd + '\'' +'}';}
}
? ? ? ? ? ? ? ? Departments
public class Departments {private Integer deptId;private String deptName;public Departments() {}public Departments(Integer deptId, String deptName) {this.deptId=deptId;this.deptName=deptName;}public Integer getDeptId() {return deptId;}public void setDeptId(Integer deptId) {this.deptId = deptId;}public String getDeptName() {return deptName;}public void setDeptName(String deptName) {this.deptName = deptName;}@Overridepublic String toString() {return "Departments{" +"deptId=" + deptId +", deptName='" + deptName + '\'' +'}';}
}
接著我們來創建兩個接口：
！注意：創建接口時可以根據需求一個一個方法的創建并實現。比如要做增刪改查四個功能，可以先在接口里寫（增加）的方法，在implement里實現以后再繼續添加后續方法！
！注意：也可以分類別添加方法，比如增刪改查是一類方法，可以add update delete list都寫進接口里，在impl里挨個實現，然后再添加減薪資之類的復雜方法！
? ? ? ? 接口：
????????????????EmployeesDao接口
import com.zb.entity.Employees;import java.util.List;public interface EmployeesDao {int addEmployees(Employees employees);int updateEmp(Employees employees);int deleteEmp(Integer id);List<Employees> listEmpByDept(Integer deptId);Double avgSalaryByDept(Integer dept);}? ? ? ? ? ? ? ?DepartmentsDao接口
import com.zb.entity.Departments;public interface DepartmentsDao {Departments findDeptById(Integer id);}接下來在接口實現類里對接口的方法進行實現：
！注意：接口實現類繼承BaseDao并實現對應接口！
? ? ? ? 接口實現類
????????????????EmployeesDaoImpl
import com.zb.dao.BaseDao;
import com.zb.dao.EmployeesDao;
import com.zb.entity.Employees;import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;public class EmployeesDaoImpl extends BaseDao implements EmployeesDao {@Overridepublic int addEmployees(Employees employees) {String sql = "insert into employees values (?,?,?,?,?)";Object[] param = {employees.getEmpId(),employees.getEmpName(),employees.getDepartmentId(),employees.getSalary(),employees.getEmpPasswd()};int num = super.update(sql,param);close();return num;}@Overridepublic int updateEmp(Employees employees) {String sql = "update employees set emp_name=? , department_id=? ,salary =? ,emp_passwd =? where emp_id=?";Object[] param = {employees.getEmpName(),employees.getDepartmentId(),employees.getSalary(),employees.getEmpPasswd(),employees.getEmpId()};int num = super.update(sql,param);close();return num;}@Overridepublic int deleteEmp(Integer id) {String sql = "delete from employees where emp_id=?";Object[] param = {id};int num = super.update(sql,param);close();return num;}@Overridepublic List<Employees> listEmpByDept(Integer deptId) {List<Employees> list = new ArrayList<>();String sql = "SELECT * FROM employees WHERE department_id=?";Object[] param = {deptId};ResultSet rs = super.query(sql,param);try {while(rs.next()){Employees employee = new Employees();employee.setEmpId(rs.getInt(1));employee.setEmpName(rs.getString(2));employee.setDepartmentId(rs.getInt(3));employee.setSalary(rs.getDouble(4));employee.setEmpPasswd(rs.getString(5));list.add(employee);}} catch (SQLException e) {e.printStackTrace();} finally {close();}return list;}@Overridepublic Double avgSalaryByDept(Integer dept) {String sql = "SELECT AVG(salary) FROM employees WHERE department_id = ?";Object[] param = {dept};ResultSet rs = super.query(sql,param);try {while (rs.next()){Double avgSalary = rs.getDouble(1);return avgSalary;}} catch (SQLException e) {e.printStackTrace();}finally {close();}return null;}}????????????????
????????????????DepartmentDaoImpl
import com.zb.dao.BaseDao;
import com.zb.dao.DepartmentsDao;
import com.zb.entity.Departments;import java.sql.ResultSet;
import java.sql.SQLException;public class DepartmentDaoImpl extends BaseDao implements DepartmentsDao {@Overridepublic Departments findDeptById(Integer id) {Departments departments = null;String sql = "SELECT * FROM departments WHERE dept_id =?";Object[] param = {id};ResultSet rs = super.query(sql, param);try {while (rs.next()) {departments = new Departments();departments.setDeptId(rs.getInt(1));departments.setDeptName(rs.getString(2));}} catch (SQLException e) {e.printStackTrace();} finally {super.close();}return departments;}
}
接著我們進行測試：
? ? ? ? Test
public class test {public static void main(String[] args) {//初始化一個新員工Employees employees=new Employees();employees.setEmpId(8);employees.setEmpName("關羽");employees.setDepartmentId(2);employees.setSalary(3000.0D);employees.setEmpPasswd("123");EmployeesDao employeesDao=new EmployeesDaoImpl();//添加新員工employeesDao.addEmployees(employees);//刪除該員工employeesDao.deleteEmp(8);//更新該員工數據employeesDao.updateEmp(employees);//查看部門編號為1的員工的姓名和薪資List<Employees> emp=employeesDao.listEmpByDept(1);for (Employees employees1 : emp) {System.out.println(employees1.getEmpName() + "\t" + employees1.getSalary());}//查看部門編號為1的名稱DepartmentsDao dept=new DepartmentDaoImpl();System.out.println(dept.findDeptById(1).getDeptName());}
}????????附加：
進階需求：查找每個部門的平均薪資，如果大于5000，則每個雇員-500元薪資
添加service文件夾，在service里對dao包里獲取的數據進行處理
????????????????接口：
? ? ? ? ? ? ? ? ? ? ? ? EmployeesService接口
public interface EmployeesService {boolean changeSalaryByDept(Integer dept,Double changeMoney);
}
? ? ? ? ? ? ? ? ? ? ? ? DepartmentsService接口
import java.util.Map;public interface DepartmentsService {Map<String, Object> searchEmpFromDept(Integer dept);}????????????????接口實現類：
????????????????????????EmpolyeesServiceImpl
import com.zb.dao.EmployeesDao;
import com.zb.dao.impl.EmployeesDaoImpl;
import com.zb.entity.Employees;
import com.zb.service.EmployeesService;import java.util.List;public class EmployeesServiceImpl implements EmployeesService {private EmployeesDao employeesDao = new EmployeesDaoImpl();@Overridepublic boolean changeSalaryByDept(Integer dept, Double reduceMoney) {double avgSalary = employeesDao.avgSalaryByDept(dept);if (avgSalary>5000){List<Employees> employees = employeesDao.listEmpByDept(dept);for (Employees employee : employees) {employee.setSalary(employee.getSalary()+reduceMoney);employeesDao.updateEmp(employee);}return true;}return false;}}
? ? ? ? ? ? ? ? ? ? ? ? ?DepartmentsImpl
import com.zb.dao.DepartmentsDao;
import com.zb.dao.EmployeesDao;
import com.zb.dao.impl.DepartmentDaoImpl;
import com.zb.dao.impl.EmployeesDaoImpl;
import com.zb.entity.Employees;
import com.zb.service.DepartmentsService;import java.util.HashMap;
import java.util.List;
import java.util.Map;public class DepartmentsServiceImpl implements DepartmentsService {private DepartmentsDao departmentsDao = new DepartmentDaoImpl();private EmployeesDao employeesDao = new EmployeesDaoImpl();@Overridepublic Map<String, Object> searchEmpFromDept(Integer dept) {Map<String, Object> deptModel = new HashMap<>();List<Employees> employees = employeesDao.listEmpByDept(dept);deptModel.put("emp",employees);deptModel.put("dept",departmentsDao.findDeptById(dept));return deptModel;}}? ? ? ? ? ? ? ? Test
import com.zb.dao.DepartmentsDao;
import com.zb.dao.EmployeesDao;
import com.zb.dao.impl.DepartmentDaoImpl;
import com.zb.dao.impl.EmployeesDaoImpl;
import com.zb.entity.Departments;
import com.zb.entity.Employees;
import com.zb.service.DepartmentsService;
import com.zb.service.EmployeesService;
import com.zb.service.impl.DepartmentsServiceImpl;
import com.zb.service.impl.EmployeesServiceImpl;import java.util.List;
import java.util.Map;public class test {public static void main(String[] args) {EmployeesDao employeesDao=new EmployeesDaoImpl();double avgSalary=employeesDao.avgSalaryByDept(1);EmployeesService employeesService=new EmployeesServiceImpl();boolean flag=employeesService.changeSalaryByDept(1, -500.0);System.out.println(avgSalary + "\t" + flag);}}附錄：
整體程序的結構概覽
