1、什么是DAO
DAO(Data Access Object)访问数据信息的类和接口,包括对数据的CRUD,而不包含任何业务相关的信息。
2、作用
为了实现功能的模块化,更有利于代码的维护和升级。
代码实现
下面针对customers数据表操作。
准备jdbc.properties配置文件
封装连接数据库的基本信息
user=root
password=ad
url=jdbc:mysql://localhost:3306/jdbc?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT
driverClass=com.mysql.cj.jdbc.Driver
准备JDBCUtils类
封装连接数据库、关闭的方法
package util;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
/**封装数据库连接和关闭*/
public class JDBCUtils {
public static Connection getConnection() throws Exception{
//读取配置文件基本信息
InputStream is = ClassLoader.getSystemClassLoader().getResourceAsStream("jdbc.properties");
Properties pros = new Properties();
pros.load(is);
String user = pros.getProperty("user");
String password = pros.getProperty("password");
String url = pros.getProperty("url");
String driverClass = pros.getProperty("driverClass");
//加载驱动
Class.forName(driverClass);
//获取连接
Connection conn = DriverManager.getConnection(url,user,password);
return conn;
}
public static void closeResource(Connection conn, Statement ps){
try {
if(ps != null)
ps.close();
}catch (SQLException e){
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
}catch (SQLException e){
e.printStackTrace();
}
}
public static void closeResource(Connection conn, Statement ps, ResultSet rs){
try {
if(ps != null)
ps.close();
}catch (SQLException e){
e.printStackTrace();
}
try {
if(conn != null)
conn.close();
}catch (SQLException e){
e.printStackTrace();
}
try {
if(rs!=null)
rs.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
1、创建BaseDAO类
将前面通用的增删改、查询方法封装;
此类为针对数据表的通用操作。
package dao;
import util.JDBCUtils;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
/**封装针对数据表的通用操作*/
public abstract class BaseDAO {
/**通用增删改操作(考虑事务)*/
public static void update(Connection conn, String sql, Object ...args){//args可变形参,占位符长度
PreparedStatement ps = null;
try {
//预编译
ps = conn.prepareStatement(sql);
//填充占位符
for(int i = 0;i<args.length;i++){
ps.setObject(i+1,args[i]);
}
//执行
ps.execute();
}catch (Exception e){
e.printStackTrace();
}finally {
//资源关闭
JDBCUtils.closeResource(null,ps);
}
}
/**返回一条记录*/
public static <T> T getInstance(Connection conn,Class<T> clazz,String sql,Object... args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
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();
int columnCount = rsmd.getColumnCount();
if (rs.next()) {
T t = clazz.newInstance();//创建对象,弱类型效率低,只能无参
for(int i = 0;i<columnCount;i++){
Object columValue = rs.getObject(i+1);
String columnLabel = rsmd.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columValue);
}
return t;
}
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
/**返回多条记录
* @return*/
public static <T> List<T> getInstance2(Connection conn,Class<T> clazz, String sql, Object... args){
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = JDBCUtils.getConnection();
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();
int columnCount = rsmd.getColumnCount();
//创建集合对象
ArrayList<T> list = new ArrayList<T>();
while(rs.next()) {
T t = clazz.newInstance();//创建对象,弱类型效率低,只能无参
for(int i = 0;i<columnCount;i++){
Object columValue = rs.getObject(i+1);
String columnLabel = rsmd.getColumnLabel(i+1);
Field field = clazz.getDeclaredField(columnLabel);
field.setAccessible(true);
field.set(t,columValue);
}
list.add(t);
}
return list;
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.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 (SQLException e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(null,ps,rs);
}
return null;
}
}
2、创建CustomerDAO接口
用于规范对customers表的常用操作;
只声明可能用到的方法,而不具体实现。
package dao;
import bean.Customer;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
/**用于规范针对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 getCustomerById(Connection conn,int id);
//查询表所有记录
List<Customer> getAll(Connection conn);
//返回数据表的条目数
Long getCount(Connection conn);
//返回数据表中最大生日
Date getMaxBirth(Connection conn);
}
3、创建CustomerDAOImpl类
此类用于上面接口的具体实现。
package dao;
import bean.Customer;
import java.sql.Connection;
import java.util.Date;
import java.util.List;
public class CustomerDAOImpl extends BaseDAO implements CustomerDAO{
@Override
public void insert(Connection conn, Customer cust) {
String sql = "insert into customers(name,email,birth)values(?,?,?)";
update(conn,sql,cust.getName(),cust.getEmail(),cust.getBirth());
}
@Override
public void deleteById(Connection conn, int id) {
String sql = "delete from customers where id = ?";
update(conn,sql,id);
}
@Override
public 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());
}
@Override
public 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;
}
@Override
public List<Customer> getAll(Connection conn) {
String sql = "select id,name,email,birth from customers";
List<Customer> list = getInstance2(conn,Customer.class,sql);
return list;
}
@Override
public Long getCount(Connection conn) {
String sql = "select count(*) from customers";
return getValue(conn,sql);
}
@Override
public Date getMaxBirth(Connection conn) {
String sql = "select max(birth) from customers";
return getValue(conn,sql);
}
}
4、测试
package dao;
import bean.Customer;
import util.JDBCUtils;
import java.sql.Connection;
import java.util.Date;
import static org.junit.jupiter.api.Assertions.*;
class CustomerDAOImplTest {
private CustomerDAOImpl dao = new CustomerDAOImpl();
@org.junit.jupiter.api.Test
void insert() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customer cust = new Customer(1,"张张","zhang@qq.com",new Date(4444444L));
dao.insert(conn,cust);
System.out.println("添加成功");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@org.junit.jupiter.api.Test
void deleteById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
dao.deleteById(conn,13);
System.out.println("删除成功");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@org.junit.jupiter.api.Test
void update() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customer cust = new Customer(18,"贝多芬","beiduofen@qq.com",new Date(4566465445L));
dao.update(conn,cust);
System.out.println("修改成功");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@org.junit.jupiter.api.Test
void getCustomerById() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Customer cust = dao.getCustomerById(conn,19);
System.out.println(cust);
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@org.junit.jupiter.api.Test
void getAll() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
System.out.println("添加成功");
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@org.junit.jupiter.api.Test
void getCount() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Long count = dao.getCount(conn);
System.out.println(count);
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
@org.junit.jupiter.api.Test
void getMaxBirth() {
Connection conn = null;
try {
conn = JDBCUtils.getConnection();
Date maxBirth = dao.getMaxBirth(conn);
System.out.println(maxBirth);
}catch (Exception e){
e.printStackTrace();
}finally {
JDBCUtils.closeResource(conn,null);
}
}
}