尚硅谷之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; }
}
|