数据(库)访问对象DAO
数据(库)访问对象DAO
DAO即Data(base) Access Objects是一个面向对象的数据库接口，它位于业务逻辑和持久化数据之间，实现了对持久化数据的访问。通俗来讲，就是将数据库操作都封装起来。
DAO模式的优势
- 隔离了数据访问代码和业务逻辑代码。业务逻辑代码直接调用DAO方法即可，完全感觉不到数据库表的存在。降低了耦合性，提高了可复用性。
- 隔离了不同数据库实现。采用面向接口编程，如果底层数据库变化，只要增加DAO接口的新的实现类即可。提高了代码的扩展性和可移植性。
DAO模式的组成
- DAO抽象类，所有DAO实现类的公共父类，封装了通用的基本操作
- DAO接口
- DAO实现类
- 实体类，往往是JavaBean
- 数据库连接和关闭工具类
例：针对于Customers表的DAO
-
要交互的数据库表Customers
![]()
-
DAO抽象类(BaseDAO.java)
- 通用的增删改操作(考虑事务)：public void update(Connection connection, String sql, Object ...args)
- 通用的查询操作，返回对象：public <T> T getInstance(Connection connection, Class<T> clazz, String sql, Object ...args)
- 通用的查询操作，返回列表：public <T> ListT getForList(Connection connection, Class<T> clazz, String sql, Object ...args)
- 用于查询特殊值的通用方法：public <E> E getValue(Connection connection, String sql, Object ...args)
//BaseDAO.java
package com.czf.dao;
import com.czf.util.JDBCUtils;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* 封装了针对数据表的通用操作
*/
public abstract class BaseDAO {
//通用的增删改操作(考虑事务)
public void update(Connection connection, String sql, Object ...args){
PreparedStatement preparedStatement = null;
try {
//2.预编译sql语句，返回PreparedStatement实例
preparedStatement = connection.prepareStatement(sql);
//3.填充占位符
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
//4.执行
preparedStatement.execute();
} catch (Exception e) {
e.printStackTrace();
}finally {
//5.关闭资源
JDBCUtils.closeResource(null, preparedStatement, null);
}
}
//通用的查询操作，返回对象
public <T> T getInstance(Connection connection, Class<T> clazz, String sql, Object ...args){
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
if(resultSet.next()){
//实例化clazz
T t = clazz.getConstructor().newInstance();
//填充field
for (int i = 0; i < columnCount; i++) {
Object value = resultSet.getObject(i + 1);
Field field = clazz.getDeclaredField(metaData.getColumnLabel(i + 1));
field.setAccessible(true);
field.set(t, value);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeResource(null, preparedStatement, resultSet);
}
return null;
}
//通用的查询操作，返回列表
public <T> List<T> getForList(Connection connection, Class<T> clazz, String sql, Object ...args){
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<T> classList = new ArrayList<T>();
try {
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while(resultSet.next()){
//实例化clazz
T t = clazz.getConstructor().newInstance();
//填充field
for (int i = 0; i < columnCount; i++) {
Object value = resultSet.getObject(i + 1);
Field field = clazz.getDeclaredField(metaData.getColumnLabel(i + 1));
field.setAccessible(true);
field.set(t, value);
}
//添加进列表
classList.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeResource(null, preparedStatement, resultSet);
return classList;
}
}
//用于查询特殊值的通用方法
public <E> E getValue(Connection connection, String sql, Object ...args){
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
resultSet = preparedStatement.executeQuery();
if (resultSet.next()){
return (E)resultSet.getObject(1);
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
JDBCUtils.closeResource(null, preparedStatement, resultSet);
}
return null;
}
}
- DAO接口(CustomerDAO.java)
//CustomerDAO.java
package com.czf.dao;
import com.czf.bean.Customer;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
/**
* 此接口用于规范针对于customers表的常用操作
*/
public interface CustomerDAO {
/**
* 将customer对象添加到数据库中
* @param connection
* @param customer
*/
void insert(Connection connection, Customer customer);
/**
* 针对指定的id，删除表中的一条记录
* @param connection
* @param id
*/
void deleteById(Connection connection, int id);
/**
* 针对于内存中的customer对象，去修改数据表中指定的记录
* @param connection
* @param customer
*/
void update(Connection connection, Customer customer);
/**
* 针对指定的id查询得到对应的customer对象
* @param connection
* @param id
*/
Customer getCustomerById(Connection connection, int id);
/**
* 查询表中的所有记录，返回列表
* @param connection
* @return
*/
List<Customer> getAll(Connection connection);
/**
* 返回数据表中的数据条目数
* @param connection
* @return
*/
Long getCount(Connection connection);
/**
* 返回数据表中最大的生日
* @param connection
* @return
*/
Date getMaxBirth(Connection connection);
}
- DAO实现类
//CustomerDAOImpl.java
package com.czf.dao;
import com.czf.bean.Customer;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
public class CustomerDAOImpl extends BaseDAO implements CustomerDAO {
@Override
public void insert(Connection connection, Customer customer) {
String sql = "insert into customers(name, email, birth)values(?, ?, ?) ";
update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth());
}
@Override
public void deleteById(Connection connection, int id) {
String sql = "delete from customers where id = ?";
update(connection, sql, id);
}
@Override
public void update(Connection connection, Customer customer) {
String sql = "update customers set name = ?, email = ?, birth = ? where id = ?";
update(connection, sql, customer.getName(), customer.getEmail(), customer.getBirth(), customer.getId());
}
@Override
public Customer getCustomerById(Connection connection, int id) {
String sql = "select id, name, email, birth from customers where id = ?";
return getInstance(connection, Customer.class, sql, id);
}
@Override
public List<Customer> getAll(Connection connection) {
String sql = "select id, name, email, birth from customers";
return getForList(connection, Customer.class, sql);
}
@Override
public Long getCount(Connection connection) {
String sql = "select count(*) from customers";
return getValue(connection, sql);
}
@Override
public Date getMaxBirth(Connection connection) {
String sql = "select max(birth) from customers";
return getValue(connection, sql);
}
}
- 实体类(JavaBean)
//Customer.java
package com.czf.bean;
import java.sql.Date;
public class Customer {
private String name;
private String email;
private Date birth;
private int id;
public Customer() {
}
public Customer(String name, String email, Date birth, int id) {
this.name = name;
this.email = email;
this.birth = birth;
this.id = id;
}
@Override
public String toString() {
return "Customer{" +
"name='" + name + '\'' +
", email='" + email + '\'' +
", birth=" + birth +
", id=" + id +
'}';
}
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;
}
}
- 数据库连接和关闭工具类（JDBCUtils.java)
//JDBCUtils.java
package com.czf.util;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**
*操作数据库的工具类
*/
public class JDBCUtils {
/**
* 获取连接
* @return Connection
* @throws IOException
* @throws ClassNotFoundException
* @throws SQLException
*/
public static Connection getConnection() throws IOException, ClassNotFoundException, SQLException {
//1.读取配置文件中的基本信息
InputStream resourceAsStream = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties properties = new Properties();
properties.load(resourceAsStream);
String user = properties.getProperty("user");
String password = properties.getProperty("password");
String url = properties.getProperty("url");
String driverClass = properties.getProperty("driverClass");
//2.加载驱动
Class.forName(driverClass);
//3.获取连接
return DriverManager.getConnection(url, user, password);
}
/**
* 关闭连接和Statement的操作
* @param connection
* @param Statement
*/
public static void closeResource(Connection connection, Statement Statement, ResultSet resultSet){
try {
if (Statement != null)
Statement.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connection != null)
connection.close();
} catch (SQLException e) {
e.printStackTrace();
}
try {
if(resultSet != null)
resultSet.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
配置文件(jdbc.properties)：
#获取配置信息
user=root
password=123123
url=jdbc:mysql://localhost:3306/jdbc_learning?rewriteBatchedStatements=true
driverClass=com.mysql.cj.jdbc.Driver
利用反射和泛型优化上述DAO
问题引入
上例中DAO实现类中的如下方法
@Override
public Customer getCustomerById(Connection connection, int id) {
String sql = "select id, name, email, birth from customers where id = ?";
return getInstance(connection, Customer.class, sql, id);
}
@Override
public List<Customer> getAll(Connection connection) {
String sql = "select id, name, email, birth from customers";
return getForList(connection, Customer.class, sql);
}
调用抽象类的getInstance()方法和getForList()方法中，在知道返回的对象(列表)类型为Customer的情况下，居然还传入了参数Customer.class。这样的行为不仅不够优雅，还可能因为传入了别的参数导致一系列的错误。因此有必要对BaseDAO.java进行优化，期望能够消除Customer.class这一“无效参数”(主观上是无效的，但实际上却被依赖了)。我们可以通过反射和泛型来解决这个问题。
解决方法
- DAO抽象类(BaseDAO.java)
//BaseDAO.java
package com.czf.dao2;
import com.czf.util.JDBCUtils;
import java.lang.reflect.Field;
import java.lang.reflect.ParameterizedType;
import java.lang.reflect.Type;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**
* 封装了针对数据表的通用操作
*/
public abstract class BaseDAO<T> {
private Class<T> clazz = null;
//代码块,在实例化时子类调用,获取了父类的泛型参数
{
Type genericSuperclass = this.getClass().getGenericSuperclass();
ParameterizedType parameterizedType = (ParameterizedType) genericSuperclass;
//获取了父类的泛型参数
Type[] actualTypeArguments = parameterizedType.getActualTypeArguments();
//泛型的第一个参数
clazz = (Class<T>) actualTypeArguments[0];
}
//通用的查询操作，返回对象
public <T> T getInstance(Connection connection, String sql, Object ...args){
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
try {
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
if(resultSet.next()){
//实例化clazz
T t = (T) clazz.getConstructor().newInstance();
//填充field
for (int i = 0; i < columnCount; i++) {
Object value = resultSet.getObject(i + 1);
Field field = clazz.getDeclaredField(metaData.getColumnLabel(i + 1));
field.setAccessible(true);
field.set(t, value);
}
return t;
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeResource(null, preparedStatement, resultSet);
}
return null;
}
//通用的查询操作，返回列表
public <T> List<T> getForList(Connection connection, String sql, Object ...args){
PreparedStatement preparedStatement = null;
ResultSet resultSet = null;
List<T> classList = new ArrayList<T>();
try {
preparedStatement = connection.prepareStatement(sql);
if (args != null) {
for (int i = 0; i < args.length; i++) {
preparedStatement.setObject(i + 1, args[i]);
}
}
resultSet = preparedStatement.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
int columnCount = metaData.getColumnCount();
while(resultSet.next()){
//实例化clazz
T t = (T) clazz.getConstructor().newInstance();
//填充field
for (int i = 0; i < columnCount; i++) {
Object value = resultSet.getObject(i + 1);
Field field = clazz.getDeclaredField(metaData.getColumnLabel(i + 1));
field.setAccessible(true);
field.set(t, value);
}
//添加进列表
classList.add(t);
}
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭资源
JDBCUtils.closeResource(null, preparedStatement, resultSet);
return classList;
}
}
//其他方法
...
}
- DAO实现类(CustomerDAOImpl.java)
//CustomerDAOImpl.java
package com.czf.dao2;
import com.czf.bean.Customer;
import java.sql.Connection;
import java.sql.Date;
import java.util.List;
public class CustomerDAOImpl extends BaseDAO<Customer> implements CustomerDAO {
@Override
public Customer getCustomerById(Connection connection, int id) {
String sql = "select id, name, email, birth from customers where id = ?";
return getInstance(connection, sql, id);
}
@Override
public List<Customer> getAll(Connection connection) {
String sql = "select id, name, email, birth from customers";
return getForList(connection,sql);
}
//其他方法
...
}
浙公网安备 33010602011771号