尚硅谷之JDBC

第6章DAO和增删改查通用方法

DAO：Data Access Object访问数据信息的类和接口，包括了对数据的CRUD（Create、Retrival、Update、Delete），而不包含任何业务相关的信息

作用：为了实现功能的模块化，更有利于代码的维护和升级。

1、练习

1.1表和JavaBean

public class Department {          private Integer id;          private String name;          private String description; .... }

int,double等在Java中都用包装类，因为mysql中的所有类型都可能是NULL，而Java只有引用数据类型才有NULL值

public class Employee {          private Integer eid;          private String ename;          private String tel;          private String gender;//mysql中用char,在Java中也要用String          private Double salary;          private Double commissionPct;          private Date birthday;//此处用String或Date          private Date hiredate;          private Integer jobId;          private String email;          private Integer mid;          private String address;          private String nativePlace;          private Integer did; ... }

 

 

1.2 DAO接口

package com.atguigu.dao;   import java.util.List;   import com.atguigu.bean.Department;   public interface DepartmentDAO {     void addDepartment(Department department)throws Exception;     void updateDepartment(Department department)throws Exception;     void deleteById(String did)throws Exception;     Department getById(String did)throws Exception;     List<Department> getAll()throws Exception; }

package com.atguigu.dao;   import java.util.List; import java.util.Map;   import com.atguigu.bean.Employee;   public interface EmployeeDAO {     void addEmployee(Employee emp)throws Exception;     void updateEmployee(Employee emp)throws Exception;     void deleteById(String eid)throws Exception;     Employee getById(String eid)throws Exception;     List<Employee> getAll()throws Exception;     Long getCount()throws Exception;     List<Employee> getAll(int page, int pageSize)throws Exception;     Double getMaxSalary()throws Exception;     Map<Integer,Double> getAvgSalaryByDid()throws Exception; }

1.3 DAO实现类

（1）原生版

package com.atguigu.dao.impl.original;   import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.ArrayList; import java.util.List;   import com.atguigu.bean.Department; import com.atguigu.dao.DepartmentDAO; import com.atguigu.utils.JDBCUtils;   public class DepartmentDAOImpl implements DepartmentDAO{       @Override     public void addDepartment(Department department) throws Exception {         Connection conn = JDBCUtils.getConnection();                 String sql = "INSERT INTO t_department(did,dname,description) VALUES(NULL,?,?)";         PreparedStatement pst = conn.prepareStatement(sql);         pst.setString(1, department.getName());         pst.setString(2, department.getDescription());         pst.executeUpdate();                 JDBCUtils.closeQuietly(pst, conn);     }       @Override     public void updateDepartment(Department department) throws Exception {         Connection conn = JDBCUtils.getConnection();                 String sql = "UPDATE t_department SET dname = ?,description = ? WHERE did = ?";         PreparedStatement pst = conn.prepareStatement(sql);         pst.setString(1, department.getName());         pst.setString(2, department.getDescription());         pst.setInt(3, department.getId());         pst.executeUpdate();                 JDBCUtils.closeQuietly(pst, conn);     }       @Override     public void deleteById(String did) throws Exception {         Connection conn = JDBCUtils.getConnection();                 String sql = "DELETE FROM t_department WHERE did = ?";         PreparedStatement pst = conn.prepareStatement(sql);         pst.setString(1, did);         pst.executeUpdate();                 JDBCUtils.closeQuietly(pst, conn);     }       @Override     public Department getById(String did) throws Exception {                 Connection conn = JDBCUtils.getConnection();                 String sql = "SELECT did,dname,description FROM t_department WHERE did = ?";         PreparedStatement pst = conn.prepareStatement(sql);         pst.setString(1, did);                 ResultSet rs = pst.executeQuery();         Department dept = null;         if(rs.next()){             dept = new Department();             dept.setId(rs.getInt("did"));             dept.setName(rs.getString("dname"));             dept.setDescription(rs.getString("description"));         }                 JDBCUtils.closeQuietly(rs, pst, conn);                 return dept;     }       @Override     public List<Department> getAll() throws Exception {         Connection conn = JDBCUtils.getConnection();                 String sql = "SELECT did,dname,description FROM t_department";         PreparedStatement pst = conn.prepareStatement(sql);                 ResultSet rs = pst.executeQuery();         ArrayList<Department> list = new ArrayList<Department>();         while(rs.next()){             Department dept = new Department();             dept.setId(rs.getInt("did"));             dept.setName(rs.getString("dname"));             dept.setDescription(rs.getString("description"));             list.add(dept);         }                 JDBCUtils.closeQuietly(rs, pst, conn);                 return list;     }   }

 

 

本教程由尚硅谷教育大数据研究院出品，如需转载请注明来源。