七:DAO及相关实现类
1. DAO介绍
- DAO:
Data Access Object
访问数据信息的类和接口,包括了对数据的CRUD
(Create、Retrival、Update、Delete
),而不包含任何业务相关的信息。有时也称作:BaseDAO
- 作用:为了实现功能的模块化,更有利于代码的维护和升级。在实际生产中,也是使用DAO的样式进行编写。
2. BaseDAO
- 把用到的通用操作写在此类中,用
作父类供子类调用
例:把之前编写的增删查改的具体方法代码放入其中,还可以添加一些其他方法
import java.lang.reflect.Field;
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.List;import com.jdbc1.util.JDBC_Utils;/**** * @author Cat God 007 封装了数据表的基本通用操作**/
public abstract class BaseDAO {// 通用的增删改操作---version2.0public int update(Connection conn, String sql, Object... args) {PreparedStatement ps = null;try {ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);// 小心参数声明错误(从1开始)}return ps.executeUpdate();} catch (Exception e) {e.printStackTrace();} finally {JDBC_Utils.closeResource(null, ps);}return 0;}// 通用的查询操作,返回一条数据----version2.0(考虑上数据库事务)public <T> T getInstance(Connection conn, Class<T> clazz, String sql, Object... args) {PreparedStatement ps = null;ResultSet rs = null;try {ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();// 获取结果集的元数据ResultSetMetaData rsmd = rs.getMetaData();// 通过ResultSetMetaData获取结果集中的列数int columnCount = rsmd.getColumnCount();if (rs.next()) {T t = clazz.newInstance();for (int i = 0; i < columnCount; i++) {// 获取列值:结果集Object columnvalue = rs.getObject(i + 1);// 获取每个列的列名:结果集元数据
// String columnName = rsmd.getColumnName(i + 1);// 获取每个列的别名(无别名就获取表的列名):结果集元数据String columnLabel = rsmd.getColumnLabel(i + 1);// 给cust指定columnName属性赋值为columnvalue,通过反射Field field = clazz.getDeclaredField(columnLabel);field.setAccessible(true);field.set(t, columnvalue);}return t;}} catch (Exception e) {e.printStackTrace();} finally {JDBC_Utils.closeResource(null, ps, rs);}return null;}// 通用的查询操作,返回多条数据----version2.0(考虑上数据库事务)public <T> List<T> getForList(Connection conn, Class<T> clazz, String sql, Object... args) {PreparedStatement ps = null;ResultSet rs = null;try {ps = conn.prepareStatement(sql);for (int i = 0; i < args.length; i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();// 获取结果集的元数据ResultSetMetaData rsmd = rs.getMetaData();// 通过ResultSetMetaData获取结果集中的列数int columnCount = rsmd.getColumnCount();// 创建集合对象ArrayList<T> list = new ArrayList<T>();while (rs.next()) {T t = clazz.newInstance();// 给t对象指定的属性赋值for (int i = 0; i < columnCount; i++) {// 获取列值:结果集Object columnvalue = rs.getObject(i + 1);// 获取每个列的列名:结果集元数据
// String columnName = rsmd.getColumnName(i + 1);// 获取每个列的别名(无别名就获取表的列名):结果集元数据String columnLabel = rsmd.getColumnLabel(i + 1);// 给cust指定columnName属性赋值为columnvalue,通过反射Field field = clazz.getDeclaredField(columnLabel);field.setAccessible(true);field.set(t, columnvalue);}list.add(t);}return list;} catch (Exception e) {e.printStackTrace();} finally {JDBC_Utils.closeResource(null, ps, rs);}return null;}//新增:用于查询特殊值的通用方法,如:查询表中的总人数等public <E> E getValue(Connection conn,String sql,Object ...args){PreparedStatement ps = null;ResultSet rs = null;try {ps = conn.prepareStatement(sql);for(int i = 0;i < args.length;i++) {ps.setObject(i + 1, args[i]);}rs = ps.executeQuery();if(rs.next()) {return (E) rs.getObject(1);}} catch (Exception e) {e.printStackTrace();}finally {JDBC_Utils.closeResource(null, ps, rs);} return null;}}
3.XxxDAO
- 定义针对某一表或数据库的功能接口
例:
在此之前需要创建Customer类,有的话就直接调用就行,不需要再创建
package com.jdbc2.bean;import java.sql.Date;/*** * @author Cat God 007* ORM编程思想(object relational mapping)对象关系映射* 一个数据表对应一个Java类* 表中的一条记录对应Java类的一个对象* 表中的一个字段对应Java类的一个属性*/
public class Customer {private int id; private String name;private String email;private Date birth;public Customer() {super();}public Customer(int id, String name, String email, Date birth) {super();this.id = id;this.name = name;this.email = email;this.birth = birth;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}public String getEmail() {return email;}public void setEmail(String email) {this.email = email;}public Date getBirth() {return birth;}public void setBirth(Date birth) {this.birth = birth;}@Overridepublic String toString() {return "Customer [id=" + id + ", name=" + name + ", email=" + email + ", birth=" + birth + "]";}}
定义针对Customer表的功能接口
import java.sql.Connection;
import java.sql.Date;
import java.util.List;import com.jdbc2.bean.Customer;//此接口用于规范针对customers表的常用操作
public interface CustomerDAO {/*** 将cust对象添加到数据库中*/void insert(Connection conn,Customer cust);/***针对指定的id,删除表中的一条记录*/void deleteById(Connection conn,int id);/** 针对内存中的cust对象,去修改数据表中指定的记录*/void update(Connection conn,Customer cust);/** 针对指定的id查询对应的Customer对象(一条记录)*/Customer getCustomerById(Connection conn,int id);/** 查询表中的所有记录构成的集合*/List<Customer> getAll(Connection conn);/** 返回数据表中的数据条目数*/Long getCount(Connection conn);/** 返回数据表中最大的生日(即最小的人的生日)*/Date getMaxBirth(Connection conn);}
4.XxxDAOImpl
- 继承
BaseDAO
,实现XxxDAO
接口
例:
CustomerDAOImpl继承BaseDAO,实现CustomerDAO接口
import java.sql.Connection;
import java.sql.Date;
import java.util.List;import com.jdbc2.bean.Customer;public class CustomerDAOImpl extends BaseDAO implements CustomerDAO{@Overridepublic void insert(Connection conn, Customer cust) {String sql = "insert into customers(name,email,birth)values(?,?,?)";update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());}@Overridepublic void deleteById(Connection conn, int id) {String sql = "delete from customers where id = ?";update(conn,sql,id);}@Overridepublic void update(Connection conn, Customer cust) {String sql = "update customers set name = ?,email = ?,birth = ? where id = ?";update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth(),cust.getId());}@Overridepublic Customer getCustomerById(Connection conn, int id) {String sql = "select id ,name,email,birth from customers where id = ?";Customer customer = getInstance(conn,Customer.class,sql,id);return customer;}@Overridepublic List<Customer> getAll(Connection conn) {String sql = "select id,name,email,birth from customers";List<Customer> list = getForList(conn,Customer.class,sql);return list;}@Overridepublic Long getCount(Connection conn) {String sql = "select count(*) from customers";return getValue(conn,sql);}@Overridepublic Date getMaxBirth(Connection conn) {String sql = "select max(birth) from customers";return getValue(conn,sql);}}
5. XxxDAOImplTest
测试
- 对具体的XxxDAOImpl实现类i,进行测试
例:
import static org.junit.Assert.*;import java.sql.Connection;
import java.sql.Date;
import java.util.List;import org.junit.Test;import com.jdbc1.util.JDBC_Utils;
import com.jdbc2.bean.Customer;
import com.jdbc4.dao.CustomerDAOImpl;public class CustomerDAOImplTest {private CustomerDAOImpl dao = new CustomerDAOImpl();@Testpublic void testInsert() {Connection conn = null;try {conn = JDBC_Utils.getConnection();Customer cust = new Customer(1,"猫神","catgod007@186.com",new Date(6323453234L));dao.insert(conn, cust);System.out.println("插入成功");} catch (Exception e) {e.printStackTrace();}finally {JDBC_Utils.closeResource(conn, null);}}@Testpublic void testDeleteById() {Connection conn = null;try {conn = JDBC_Utils.getConnection();dao.deleteById(conn, 16);System.out.println("删除成功");} catch (Exception e) {e.printStackTrace();}finally {JDBC_Utils.closeResource(conn, null);}}@Testpublic void testUpdateConnectionCustomer() {Connection conn = null;try {conn = JDBC_Utils.getConnection();Customer cust = new Customer(18,"马云","mayun@123.com",new Date(567865437L));dao.update(conn, cust); System.out.println("修改成功");} catch (Exception e) {e.printStackTrace();}finally {JDBC_Utils.closeResource(conn, null);}}@Testpublic void testGetCustomerById() {Connection conn = null;try {conn = JDBC_Utils.getConnection(); Customer customer = dao.getCustomerById(conn,10);System.out.println(customer);} catch (Exception e) {e.printStackTrace();}finally {JDBC_Utils.closeResource(conn, null);}}@Testpublic void testGetAll() {Connection conn = null;try {conn = JDBC_Utils.getConnection();List<Customer> list = dao.getAll(conn);list.forEach(System.out::println);//注意遍历对象的方法} catch (Exception e) {e.printStackTrace();}finally {JDBC_Utils.closeResource(conn, null);}}@Testpublic void testGetCount() {Connection conn = null;try {conn = JDBC_Utils.getConnection();Long count = dao.getCount(conn);System.out.println("表中的数据共有:" + count + "条");} catch (Exception e) {e.printStackTrace();}finally {JDBC_Utils.closeResource(conn, null);}}@Testpublic void testGetMaxBirth() {Connection conn = null;try {conn = JDBC_Utils.getConnection();Date maxBirth = dao.getMaxBirth(conn);System.out.println("数据表中最年轻的人的生日是: " + maxBirth);} catch (Exception e) {e.printStackTrace();}finally {JDBC_Utils.closeResource(conn, null);}}
}
6. 创建测试文件测试多个方法(可略过)
- 在要创建的包下,右键点击想要测试的java类,点击new------->Other
- 搜索
Junit test case
,如果不行就需要导入Junit 包
到处DAO就结束了,如果需要编写针对Order表的DAO层,就需要编写OrderDAO
,OrderDAOImpl
即可
其中可能会出现不少的小问题,请多多包含
感谢大家的支持,关注,评论,点赞!
参考资料:尚硅谷_宋红康_JDBC核心技术