首页 > 技术 > DAO和增删改查通用方法
DAO和增删改查通用方法
  • 路安
  • 2022-06-14
  • 10782
  • 技术
广告
摘要:区块链技术DAO和增删改查通用方法DAO:Data Access Object访问数据信息的类和接口,包括了对数据的CRUD(Create、Retrival、Update、Delete),而不包含任何业务相关的信息作用:为了实现功能的模块化,更有利于代码的维护和升级。1.1表和JavaBean1.2
DAO和增删改查通用方法

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

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

1.1表和JavaBeanDAO和增删改查通用方法

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 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 getAll()throws Exception;Long getCount()throws Exception;List getAll(int page, int pageSize)throws Exception;Double getMaxSalary()throws Exception;Map 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{@Overridepublic 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);}@Overridepublic 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);}@Overridepublic 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);}@Overridepublic 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;}@Overridepublic List 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 list = new ArrayList();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 {private Class type;@SuppressWarnings("all")protected BasicDAOImpl() {// 为什么要在构造器中写,因为子类继承BasicDAOImpl类一定会调用父类的构造器Class clazz = this.getClass();// this代表的是正在创建的那个对象,即子类的对象// 获取clazz的带泛型父类信息Type superType = clazz.getGenericSuperclass();// Father:参数化的类型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、执行sqlint 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 clazz:用来创建实例对象,获取对象的属性,并设置属性值/*** 执行查询操作的SQL语句,SQL可以带参数(?)* @param sql String 执行查询操作的SQL语句* @param args Object... 对应的每个?设置的值,顺序要与?对应* @return ArrayList 封装了查询结果的集合* @throws Exception*/public ArrayList getList(String sql, Object... args) throws Exception {// 1、获取连接Connection conn = JDBCUtils.getConnection();//2、执行查询sqlResultSet rs = executeQuery(conn,sql, args);//3、获取结果集的元数据ResultSetMetaData metaData = rs.getMetaData();// 获取结果中总列数int count = metaData.getColumnCount();// 创建集合对象ArrayList list = new ArrayList();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、执行查询sqlResultSet 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 89002 4889 6899*/public List> getListMap(String sql,Object... args)throws Exception{//1、获取连接Connection conn = JDBCUtils.getConnection();//2、执行sqlResultSet rs = executeQuery(conn, sql, args);//获取结果集的元数据对象ResultSetMetaData metaData = rs.getMetaData();//一共有几列int count = metaData.getColumnCount();//创建ListArrayList> list = new ArrayList>();while(rs.next()){//每一行是一个Map的对象HashMap map = new HashMap();//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;}//通用的查询方法之四:查询一行多列,但一行又不是一个JavaBeanpublic Map getMap(String sql,Object... args)throws Exception{List> 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 implements DepartmentDAO{@Overridepublic 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());}@Overridepublic void deleteById(String did) throws Exception {String sql = "DELETE FROM t_department WHERE did = ?";update(sql, did);}@Overridepublic 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;}@Overridepublic List getAll() throws Exception {String sql = "SELECT did as id,dname as name,description FROM t_department";ArrayList 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 implements EmployeeDAO{@Overridepublic 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);}@Overridepublic 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);}@Overridepublic void deleteById(String eid) throws Exception {String sql = "DELETE FROM t_employee WHERE eid = ?";update(sql, eid);}@Overridepublic 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;}@Overridepublic List 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 list = getList(sql);return list;}@Overridepublic Long getCount() throws Exception {String sql = "SELECT COUNT(*) FROM t_employee";Long value = (Long) getValue(sql);return value;}@Overridepublic List 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 list = getList( sql, (page-1)*pageSize, pageSize);return list;}@Overridepublic Double getMaxSalary() throws Exception {String sql = "SELECT MAX(salary) FROM t_employee";Double value = (Double) getValue(sql);return value;}@Overridepublic Map getAvgSalaryByDid() throws Exception {String sql = "SELECT did,MAX(salary) FROM t_employee GROUP BY did";List> list = getListMap(sql);HashMap result = new HashMap<>();for (Map map : list) {Set> entrySet = map.entrySet();Integer did = null;Double salary = null;for (Entry 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();@Testpublic 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("添加失败");}}@Testpublic void getAllDepartment() throws Exception {List all = dao.getAll();for (Department department : all) {System.out.println(department);}}@Testpublic 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("修改失败");}}@Testpublic 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();@Testpublic 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("添加失败");}}@Testpublic 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("修改失败");}}@Testpublic void deleteById()throws Exception{//省略键盘输入String eid = "26";try {ed.deleteById(eid);System.out.println("删除成功");} catch (Exception e) {e.printStackTrace();System.out.println("删除失败");}}@Testpublic void getAll()throws Exception{List all = ed.getAll();for (Employee employee : all) {System.out.println(employee);}}@Testpublic 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 all = ed.getAll(page, pageSize);for (Employee employee : all) {System.out.println(employee);}}@Testpublic void getMaxSalary()throws Exception{Double maxSalary = ed.getMaxSalary();System.out.println("公司最高工资是: " + maxSalary);}@Testpublic void getAvgSalaryByDid()throws Exception{Map map = ed.getAvgSalaryByDid();Set> entrySet = map.entrySet();for (Entry entry : entrySet) {System.out.println(entry.getKey()+ ":" + entry.getValue());}}}

相关阅读:

JDBC的操作有哪些

java编程技术JDBC

JDBC API的使用与操作详解

JDBC工具类解读,让你快速入门

如何使用JDBC API操作数据库

免责声明:世链财经作为开放的信息发布平台,所有资讯仅代表作者个人观点,与世链财经无关。如文章、图片、音频或视频出现侵权、违规及其他不当言论,请提供相关材料,发送到:2785592653@qq.com。
风险提示:本站所提供的资讯不代表任何投资暗示。投资有风险,入市须谨慎。
世链粉丝群:提供最新热点新闻,空投糖果、红包等福利,微信:rtt4322。