DAO和增删改查通用方法-BasicDao
分析问题
- apache-dbutils + Druid 简化了JDBC开发,但还有不足:
- SQL语句是固定,不能通过参数传入,通用性不好,需要进行改进,更方便执行增删改查
- 对于select操作,如果有返回值,返回值类型不能固定,需要使用泛型
- 将来的表很多,也无需求复杂,不可能只靠一个Java类完成
- 引出 => BasicDAO图形分析
图形介绍
基本说明
-
DAO:data access object数据访问对象(访问数据的对象)
-
这样的通用类,称为BasicDao,是专门和数据库交互的,即完成对数据库(表)的crud操作
-
在BaiscDao的基础上,实现一张表对应一个Dao,更好的完成功能,比如Customer表Customer.java类(javabean)-CustomerDao.java
应用实例
完成一个简单设计
- com.taotao.dao_.utils//工具类
- com.taotao.dao_.domain//javabean
- com.taotao.dao_.dao//存放XxxDAO 和 BasicDAO
- com.taotao.dao_.test//写测试类
utils包
package com.taotao.dao_.utils;
import com.alibaba.druid.pool.DruidDataSourceFactory;
import javax.sql.DataSource;
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
/**
* Create By 刘鸿涛
* 2022/3/5 13:24
* 基于 druid 数据库池的工具类
*/
@SuppressWarnings({"all"})
public class JDBCUtilsByDruid {
private static DataSource ds;
//在静态代码块完成 ds初始化
static {
Properties properties = new Properties();
try {
properties.load(new FileInputStream("src\\druid.properties"));
ds = DruidDataSourceFactory.createDataSource(properties);
}catch(Exception e){
e.printStackTrace();
}
}
//编写getConnection方法
public static Connection getConnection() throws SQLException {
return ds.getConnection();
}
//关闭连接,再次强调,在数据库连接池技术中心,close不是真的断掉连接
//而是把使用的Connection对象放回连接池
public static void close(ResultSet resultSet, Statement statement, Connection connection){
try {
if (resultSet != null){
resultSet.close();
}
if (statement != null){
statement.close();
}
if (connection != null){
connection.close();
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
}
domain包
package com.taotao.dao_.domain;
import java.time.LocalDateTime;
import java.util.Date;
/**
* Create By 刘鸿涛
* 2022/3/5 16:00
* Actor 对象和 actor 表的记录对应
*/
@SuppressWarnings({"all"})
public class Actor {
private Integer Id;
private String name;
private String sex;
private LocalDateTime borndate;
private String phone;
public Actor() { //一定要给一个无参构造器【反射需要】
}
public Actor(Integer id, String name, String sex, LocalDateTime borndate, String phone) {
Id = id;
this.name = name;
this.sex = sex;
this.borndate = borndate;
this.phone = phone;
}
public Integer getId() {
return Id;
}
public void setId(Integer id) {
Id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
public LocalDateTime getBorndate() {
return borndate;
}
public void setBorndate(LocalDateTime borndate) {
this.borndate = borndate;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "\nActor{" +
"Id=" + Id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", borndate=" + borndate +
", phone='" + phone + '\'' +
'}';
}
}
DAO包
package com.taotao.dao_.dao;
import com.taotao.jdbc.datasource.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
/**
* Create By 刘鸿涛
* 2022/3/6 16:05
* 开发BasicDAO,是其他DAO的父类
*/
@SuppressWarnings({"all"})
public class BasicDAO<T> { //泛型指定具体类型
private QueryRunner qr = new QueryRunner();
//开发通用的dml方法,针对任意的表
public int dml(String sql,Object...parameters) {
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
int update = qr.update(connection, sql, parameters);
return update;
} catch (SQLException e) {
//编译异常转为运行异常
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
/**
*
* @param sql sql 语句,可以有?
* @param clazz clazz 传入一个类的Class对象 比如 Actor.class
* @param parameters 传入 ?的具体的值,可以是多个
* @return 根据Actor.class 返回对应的ArrayList 集合
*/
//返回多个对象(即查询的结果是多行),针对任意表
public List<T> queryMulti(String sql,Class<T> clazz,Object...parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection,sql,new BeanListHandler<T>(clazz),parameters);
} catch (SQLException e) {
//编译异常转为运行异常
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
//查询单行结果的通用方法
public T querySingle(String sql, Class<T> clazz,Object...parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return (T) qr.query(connection, sql, new BeanListHandler<T>(clazz), parameters);
} catch (SQLException e) {
//编译异常转为运行异常
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
//查询单行单列的方法,即返回单值的方法
public Object queryScalar(String sql,Object...parameters){
Connection connection = null;
try {
connection = JDBCUtilsByDruid.getConnection();
return qr.query(connection,sql,new ScalarHandler(),parameters);
} catch (SQLException e) {
//编译异常转为运行异常
throw new RuntimeException(e); //将编译异常 -> 运行异常,抛出
} finally {
JDBCUtilsByDruid.close(null,null,connection);
}
}
}
package com.taotao.dao_.dao;
/**
* Create By 刘鸿涛
* 2022/3/6 16:44
*/
@SuppressWarnings({"all"})
public class ActorDAO extends BasicDAO{
//1.就有BasicDAO 的方法
//2.根据业务需求,可以编写特有的方法
}
test包
package com.taotao.dao_.test;
import com.taotao.dao_.dao.ActorDAO;
import com.taotao.dao_.dao.GoodsDAO;
import com.taotao.dao_.domain.Actor;
import org.junit.Test;
import java.util.List;
/**
* Create By 刘鸿涛
* 2022/3/6 16:45
*/
@SuppressWarnings({"all"})
public class TestDAO {
@Test
//测试ActorDAO 对actor表crud操作
public void testActorDAO() {
ActorDAO actorDAO = new ActorDAO();
//1.查询
List<Actor> actors = actorDAO.queryMulti("select * from actor where Id >= ?", Actor.class, 1);
System.out.println("===查询结果===");
for(Actor actor:actors){
System.out.print(actor);
}
// //2.查询单行记录
// Actor actor = (Actor) actorDAO.querySingle("select * from actor where id = ?", Actor.class, 1);
// System.out.println("====查询单行结果====");
// System.out.println(actor);
//3.查询单行单列
Object o = actorDAO.queryScalar("select name from actor where id = ?", 1);
System.out.println("\n===查询单行单列值===");
System.out.println(o);
//4.dml操作 insert,update,delete
//返回受影响的行数
System.out.println("===开始执行添加语句===");
int dml = actorDAO.dml("insert into actor values(3,?,?,?,?)", "涛涛", "男","1984-10-15", "12454454");
System.out.println(dml > 0?"执行成功":"执行没有结果");
}
}
案例练习
- 建立goods表
- domain中建Goods类
- DAO中建GoodsDAO继承BasicDAO
- testDao类中测试
mysql中建立表
表结构为
DAO包
直接继承BasicDAO包即可
package com.taotao.dao_.dao;
/**
* Create By 刘鸿涛
* 2022/3/6 17:47
*/
public class GoodsDAO extends BasicDAO{
}
domain包
package com.taotao.dao_.domain;
/**
* Create By 刘鸿涛
* 2022/3/6 17:49
*/
public class Goods {
private Integer id;
private String goods_name;
private Integer price;
public Goods() {
}
public Goods(Integer id, String goods_name, Integer price) {
this.id = id;
this.goods_name = goods_name;
this.price = price;
}
public int getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getGoods_name() {
return goods_name;
}
public void setGoods_name(String goods_name) {
this.goods_name = goods_name;
}
public int getPrice() {
return price;
}
public void setPrice(Integer price) {
this.price = price;
}
@Override
public String toString() {
return "Goods{" +
"id=" + id +
", goods_name='" + goods_name + '\'' +
", price=" + price +
'}';
}
}
test包
package com.taotao.dao_.test;
import com.taotao.dao_.dao.ActorDAO;
import com.taotao.dao_.dao.GoodsDAO;
import com.taotao.dao_.domain.Actor;
import org.junit.Test;
import java.util.List;
/**
* Create By 刘鸿涛
* 2022/3/6 16:45
*/
@SuppressWarnings({"all"})
public class TestDAO {
@Test
public void testGoodsDAO(){
GoodsDAO goodsDAO = new GoodsDAO();
int dml = goodsDAO.dml("insert into goods values(?,?,?)", null, "华为手机", 3000);
System.out.println(dml >0 ?"执行成功":"执行失败");
}
}