尚硅谷之JDBC

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;

    }

 

}