• 让天下没有难学的技术
    多数学员都来自推荐,这就是口碑的力量

DAO和增删改查通用方法

DAO和增删改查通用方法

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

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

1.1表和JavaBean

DAO和增删改查通用方法

 

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; } }

1.4 抽取BasicDAO

package com.atguigu.dao.impl; /* * 这个类的作用是:对DAOImpl再次抽象,把共同的部分再次抽取 */ import java.lang.reflect.Field; import java.lang.reflect.ParameterizedType; import java.lang.reflect.Type; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.SQLException; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import com.atguigu.utils.JDBCUtils; //泛型类 public abstract class BasicDAOImpl<T> { private Class<T> type; @SuppressWarnings(“all”) protected BasicDAOImpl() { // 为什么要在构造器中写,因为子类继承BasicDAOImpl类一定会调用父类的构造器 Class clazz = this.getClass();// this代表的是正在创建的那个对象,即子类的对象 // 获取clazz的带泛型父类信息 Type superType = clazz.getGenericSuperclass(); // Father<String>:参数化的类型 ParameterizedType p = (ParameterizedType) superType; // 获取泛型实参 Type[] ts = p.getActualTypeArguments(); // 因为当前类只有一个泛型形参,即子类中只有一个泛型实参 type = (Class) ts[0]; } protected int update(String sql, Object… params) throws SQLException { //1、获取连接 Connection conn = JDBCUtils.getConnection(); //2、执行更新数据库语句 int len = executeUpdate(conn, sql, params); //3、关闭连接 JDBCUtils.closeQuietly(conn); return len; } // 如果有需要在一个事务中完成更新,可以调用这个带conn的方法 protected int update(Connection conn, String sql, Object… params) throws SQLException { //执行更新数据库语句 int len = executeUpdate(conn, sql, params); return len; } private int executeUpdate(Connection conn, String sql, Object… params) throws SQLException { //1、sql预编译 PreparedStatement pst = conn.prepareStatement(sql); //2、设置sql中的? if (params != null && params.length > 0) { // 数组的下标是从0开始,?的编号是1开始 for (int i = 0; i < params.length; i++) { pst.setObject(i + 1, params[i]); } } //3、执行sql int len = pst.executeUpdate(); //4、释放资源 JDBCUtils.closeQuietly(pst); return len; } //通用的查询方法之一:查询一行,即一个对象 /** * 执行查询操作的SQL语句,SQL可以带参数(?) * @param sql String 执行查询操作的SQL语句 * @param args Object… 对应的每个?设置的值,顺序要与?对应 * @return T 封装了查询结果的实体 * @throws Exception */ protected T get(String sql, Object… params) throws Exception { //1、获取连接 Connection conn = JDBCUtils.getConnection(); //2、执行查询语句 ResultSet rs = executeQuery( conn,sql, params); //3、处理查询结果 //(1)获取查询的结果集的元数据信息 ResultSetMetaData rsmd = rs.getMetaData(); //(2)这是查询的结果集中,一共有几列 int count = rsmd.getColumnCount(); //(3)创建实例对象 T t = type.newInstance();// 要求这个Javabean类型必须有无参构造 //(4)遍历结果集 while (rs.next()) { /* * 问题? (1)sql语句中查询了几列,每一列是什么属性 (2)怎么把这个值设置到Javabean的属性中 */ // (5)循环每一行有几列 for (int i = 0; i < count; i++) { // (6)获取第几列的名称 // String columnName = rsmd.getColumnName(i+1); // 如果sql中没有取别名,那么就是列名,如果有别名,返回的是别名 String fieldName = rsmd.getColumnLabel(i + 1); // (7)获取该列的值 // Object value = rs.getObject(columnName); Object value = rs.getObject(fieldName); // (8)设置obj对象的某个属性中 Field field = type.getDeclaredField(fieldName);// JavaBean的属性名 field.setAccessible(true); field.set(t, value); } } //4、释放资源 JDBCUtils.closeQuietly(rs); JDBCUtils.closeQuietly(conn); return t; } private static ResultSet executeQuery(Connection conn, String sql, Object… params) throws SQLException { //1、sql预编译 PreparedStatement pst = conn.prepareStatement(sql); //2、设置? if (params != null && params.length > 0) { // 数组的下标是从0开始,?的编号是1开始 for (int i = 0; i < params.length; i++) { pst.setObject(i + 1, params[i]); } } //3、查询 ResultSet rs = pst.executeQuery(); return rs; } // 通用的查询方法之二:查询多行,即多个对象 // Class<T> clazz:用来创建实例对象,获取对象的属性,并设置属性值 /** * 执行查询操作的SQL语句,SQL可以带参数(?) * @param sql String 执行查询操作的SQL语句 * @param args Object… 对应的每个?设置的值,顺序要与?对应 * @return ArrayList<T> 封装了查询结果的集合 * @throws Exception */ public ArrayList<T> getList(String sql, Object… args) throws Exception { // 1、获取连接 Connection conn = JDBCUtils.getConnection(); //2、执行查询sql ResultSet rs = executeQuery(conn,sql, args); //3、获取结果集的元数据 ResultSetMetaData metaData = rs.getMetaData(); // 获取结果中总列数 int count = metaData.getColumnCount(); // 创建集合对象 ArrayList<T> list = new ArrayList<T>(); while (rs.next()) {// 遍历的行 // 1、每一行是一个对象 T obj = type.newInstance(); // 2、每一行有很多列 // for的作用是为obj对象的每一个属性设置值 for (int i = 0; i < count; i++) { // (1)每一列的名称 String fieldName = metaData.getColumnLabel(i + 1);// 获取第几列的名称,如果有别名获取别名,如果没有别名获取列名 // (2)每一列的值 Object value = rs.getObject(i + 1);// 获取第几列的值 // (3)获取属性对象 Field field = type.getDeclaredField(fieldName); // (4)设置可见性 field.setAccessible(true); // (5)设置属性值 field.set(obj, value); } // 3、把obj对象放到集合中 list.add(obj); } // 6、释放资源 JDBCUtils.closeQuietly(rs); JDBCUtils.closeQuietly(conn); // 7、返回结果 return list; } //通用的查询方法之三:查询单个值 //单值:select max(salary) from employee; 一行一列 //select count(*) from t_goods; 一共几件商品 public Object getValue(String sql,Object… args)throws Exception{ //1、获取连接 Connection conn = JDBCUtils.getConnection(); //2、执行查询sql ResultSet rs = executeQuery(conn, sql, args); Object value = null; if(rs.next()){//一行 value = rs.getObject(1);//一列 } //3、释放资源 JDBCUtils.closeQuietly(rs); JDBCUtils.closeQuietly(conn); return value; } //通用的查询方法之四:查询多行多列,但每一行又不是一个JavaBean /* * SELECT did,AVG(salary),MAX(Salary) FROM t_employee GROUP BY did; * did avg(salary) max(salary) 1 1990.90 8900 2 4889 6899 */ public List<Map<String,Object>> getListMap(String sql,Object… args)throws Exception{ //1、获取连接 Connection conn = JDBCUtils.getConnection(); //2、执行sql ResultSet rs = executeQuery(conn, sql, args); //获取结果集的元数据对象 ResultSetMetaData metaData = rs.getMetaData(); //一共有几列 int count = metaData.getColumnCount(); //创建List ArrayList<Map<String,Object>> list = new ArrayList<Map<String,Object>>(); while(rs.next()){ //每一行是一个Map的对象 HashMap<String,Object> map = new HashMap<String,Object>(); //map的key是列名 for (int i = 0; i < count; i++) { //(1)获取列名或别名 String columnName = metaData.getColumnLabel(i+1); //(2)获取对应的值 Object value = rs.getObject(i+1); //(3)把这对值放到map中 map.put(columnName, value); } //把map放到List中 list.add(map); } //6、释放资源 JDBCUtils.closeQuietly(rs); JDBCUtils.closeQuietly(conn); return list; } //通用的查询方法之四:查询一行多列,但一行又不是一个JavaBean public Map<String,Object> getMap(String sql,Object… args)throws Exception{ List<Map<String, Object>> listMap = getListMap(sql,args); if(listMap.size()>0){ return listMap.get(0); } return null; } }

1.5 继承BasicDAO的后的DAO实现类

DepartmentDAO实现类

package com.atguigu.dao.impl.basic; import java.util.ArrayList; import java.util.List; import com.atguigu.bean.Department; import com.atguigu.dao.DepartmentDAO; import com.atguigu.dao.impl.BasicDAOImpl; public class DepartmentDAOImplBasic extends BasicDAOImpl<Department> implements DepartmentDAO{ @Override public void addDepartment(Department department) throws Exception { String sql = “INSERT INTO t_department(did,dname,description) VALUES(NULL,?,?)”; update(sql, department.getName(),department.getDescription()); } @Override

public void updateDepartment(Department department) throws Exception { String sql = “UPDATE t_department SET dname = ?,description = ? WHERE did = ?”; update(sql, department.getName(),department.getDescription(),department.getId()); } @Override public void deleteById(String did) throws Exception { String sql = “DELETE FROM t_department WHERE did = ?”; update(sql, did); } @Override public Department getById(String did) throws Exception { String sql = “SELECT did as id,dname as name,description FROM t_department WHERE did = ?”; Department department = get(sql, did); return department; } @Override public List<Department> getAll() throws Exception { String sql = “SELECT did as id,dname as name,description FROM t_department”; ArrayList<Department> list = getList(sql); return list; } }

EmployeeDAO实现类

package com.atguigu.dao.impl.basic; import java.util.ArrayList; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Set; import com.atguigu.bean.Employee; import com.atguigu.dao.EmployeeDAO; import com.atguigu.dao.impl.BasicDAOImpl; public class EmployeeDAOImpl extends BasicDAOImpl<Employee> implements EmployeeDAO{ @Override public void addEmployee(Employee emp) throws Exception { String sql = “INSERT INTO t_employee ” + “(eid,ename,tel,gender,salary,commission_pct,birthday,hiredate,job_id,email,mid,address,native_place,did)” + “VALUES(NULL,?,?,?,?,?,?,?,?,?,?,?,?,?)”; Object[] args = new Object[13]; args[0] = emp.getEname(); args[1] = emp.getTel(); args[2] = emp.getGender(); args[3] = emp.getSalary(); args[4] = emp.getCommissionPct(); args[5] = emp.getBirthday(); args[6] = emp.getHiredate(); args[7] = emp.getJobId(); args[8] = emp.getEmail(); args[9] = emp.getMid(); args[10] = emp.getAddress(); args[11] = emp.getNativePlace(); args[12] = emp.getDid(); update(sql, args); } @Override public void updateEmployee(Employee emp) throws Exception { String sql = “UPDATE t_employee SET ” + “ename=?,tel=?,gender=?,salary=?,commission_pct=?,birthday=?,hiredate=?,job_id=?,email=?,MID=?,address=?,native_place=?,did=?” +” WHERE eid = ?”; Object[] args = new Object[14]; args[0] = emp.getEname(); args[1] = emp.getTel(); args[2] = emp.getGender(); args[3] = emp.getSalary(); args[4] = emp.getCommissionPct(); args[5] = emp.getBirthday(); args[6] = emp.getHiredate(); args[7] = emp.getJobId(); args[8] = emp.getEmail(); args[9] = emp.getMid(); args[10] = emp.getAddress(); args[11] = emp.getNativePlace(); args[12] = emp.getDid(); args[13] = emp.getEid(); update(sql, args); } @Override public void deleteById(String eid) throws Exception { String sql = “DELETE FROM t_employee WHERE eid = ?”; update(sql, eid); } @Override public Employee getById(String eid) throws Exception { String sql = “SELECT eid,ename,tel,gender,salary,commission_pct as ‘commissionPct’,birthday,hiredate,” + “job_id as ‘jobId’,email,mid,address,native_place as ‘nativePlace’ ,did FROM t_employee WHERE eid = ?”; Employee employee = get(sql, eid); return employee; } @Override public List<Employee> getAll() throws Exception { String sql = “SELECT eid,ename,tel,gender,salary,commission_pct as ‘commissionPct’,birthday,hiredate,” + “job_id as ‘jobId’,email,mid,address,native_place as ‘nativePlace’ ,did FROM t_employee”; ArrayList<Employee> list = getList(sql); return list; } @Override public Long getCount() throws Exception { String sql = “SELECT COUNT(*) FROM t_employee”; Long value = (Long) getValue(sql); return value; } @Override public List<Employee> getAll(int page, int pageSize) throws Exception { String sql = “SELECT eid,ename,tel,gender,salary,commission_pct as ‘commissionPct’,birthday,hiredate,” + “job_id as ‘jobId’,email,mid,address,native_place as ‘nativePlace’ ,did FROM t_employee LIMIT ?,?”; ArrayList<Employee> list = getList( sql, (page-1)*pageSize, pageSize); return list; } @Override public Double getMaxSalary() throws Exception { String sql = “SELECT MAX(salary) FROM t_employee”; Double value = (Double) getValue(sql); return value; } @Override public Map<Integer, Double> getAvgSalaryByDid() throws Exception { String sql = “SELECT did,MAX(salary) FROM t_employee GROUP BY did”; List<Map<String, Object>> list = getListMap(sql); HashMap<Integer, Double> result = new HashMap<>(); for (Map<String, Object> map : list) { Set<Entry<String, Object>> entrySet = map.entrySet(); Integer did = null; Double salary = null; for (Entry<String, Object> entry : entrySet) { String key = entry.getKey(); if(“did”.equals(key)){ did = (Integer) entry.getValue(); }else{ salary = (Double) entry.getValue(); } } result.put(did, salary); } return result; } }

1.6 测试类

package com.atguigu.dao.test; import java.util.List; import java.util.Scanner; import org.junit.Test; import com.atguigu.bean.Department; import com.atguigu.dao.DepartmentDAO; import com.atguigu.dao.impl.basic.DepartmentDAOImplBasic; import com.atguigu.dao.impl.original.DepartmentDAOImpl; import com.atguigu.utils.CMUtility; public class TestDepartmentDAO { // DepartmentDAO dao = new DepartmentDAOImpl(); DepartmentDAO dao = new DepartmentDAOImplBasic(); @Test public void addDepartment() { Scanner input = new Scanner(System.in); System.out.println(“请输入部门名称:”); String name = input.nextLine(); System.out.println(“请输入部门简介:”); String description = input.nextLine(); Department department = new Department(name, description); try { dao.addDepartment(department); System.out.println(“添加成功”); } catch (Exception e) { e.printStackTrace(); System.out.println(“添加失败”); } } @Test public void getAllDepartment() throws Exception { List<Department> all = dao.getAll(); for (Department department : all) { System.out.println(department); } } @Test public void updateDepartment() { try { getAllDepartment(); Scanner input = new Scanner(System.in); System.out.println(“请选择要修改的部门编号:”); String did = input.nextLine(); Department dept = dao.getById(did); System.out.println(“请输入部门名称(“+dept.getName()+”):”); String name = CMUtility.readString(dept.getName()); System.out.println(“请输入部门简介(“+dept.getDescription()+”):”); String description = CMUtility.readString(dept.getDescription()); Department department = new Department(dept.getId(),name, description); dao.updateDepartment(department); System.out.println(“修改成功”); } catch (Exception e) { e.printStackTrace(); System.out.println(“修改失败”); } } @Test public void deleteDepartment() { try { getAllDepartment(); Scanner input = new Scanner(System.in); System.out.println(“请选择要删除的部门编号:”); String did = input.nextLine(); dao.deleteById(did); System.out.println(“删除成功”); } catch (Exception e) { e.printStackTrace(); System.out.println(“删除失败”); } } }

package com.atguigu.dao.test; import java.util.Date; import java.util.List; import java.util.Map; import java.util.Map.Entry; import java.util.Set; import org.junit.Test; import com.atguigu.bean.Employee; import com.atguigu.dao.EmployeeDAO; import com.atguigu.dao.impl.basic.EmployeeDAOImpl; public class TestEmployeeDAO { EmployeeDAO ed = new EmployeeDAOImpl(); @Test public void addEmployee()throws Exception{ //省略键盘输入 String ename = “张三”; String tel = “10080”; String gender = “男”; double salary = 10000;

double commissionPct = 0.3; Date birthday = new Date(); Date hiredate = new Date();; int jobId = 2; String email=”zhangsan@lina.com”; int mid = 1; String address = “xx”; String nativePlace = “xxx”; int did = 2; Employee emp = new Employee(ename, tel, gender, salary, commissionPct, birthday, hiredate, jobId, email, mid, address, nativePlace, did); try { ed.addEmployee(emp); System.out.println(“添加成功”); } catch (Exception e) { e.printStackTrace();

System.out.println(“添加失败”); } } @Test public void updateEmployee()throws Exception{ //省略键盘输入 String eid = “1”; Employee emp = ed.getById(eid); //这里只演示修改一下,可以修改除了eid以外的所有项目 emp.setSalary(emp.getSalary() + 1000); try { ed.updateEmployee(emp); System.out.println(“修改成功”); } catch (Exception e) { e.printStackTrace(); System.out.println(“修改失败”); } } @Test public void deleteById()throws Exception{ //省略键盘输入 String eid = “26”; try { ed.deleteById(eid); System.out.println(“删除成功”); } catch (Exception e) { e.printStackTrace(); System.out.println(“删除失败”); } } @Test public void getAll()throws Exception{ List<Employee> all = ed.getAll(); for (Employee employee : all) { System.out.println(employee); } } @Test public void getAllPage()throws Exception{ Long count = ed.getCount(); System.out.println(“总记录数:” + count); int pageSize = 5; System.out.println(“每页显示5条”); int page = 2; System.out.println(“用户选择第” + page + “页”); List<Employee> all = ed.getAll(page, pageSize); for (Employee employee : all) { System.out.println(employee); } } @Test public void getMaxSalary()throws Exception{ Double maxSalary = ed.getMaxSalary(); System.out.println(“公司最高工资是: ” + maxSalary); } @Test public void getAvgSalaryByDid()throws Exception{ Map<Integer, Double> map = ed.getAvgSalaryByDid(); Set<Entry<Integer, Double>> entrySet = map.entrySet(); for (Entry<Integer, Double> entry : entrySet) { System.out.println(entry.getKey()+ “:” + entry.getValue()); } } } 想要了解跟多关于java培训课程内容欢迎关注尚硅谷java培训,尚硅谷除了这些技术文章外还有免费的高质量大 java培训课程视频供广大学员下载学习。

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注