1、DAO 和增删改查通用方法-BasicDao的封装
1.1、 分析问题
1.2、 基本说明
1.3、 BasicDAO 应用实例
JDBCUtilsByDruid.java
package 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;
/**
* 基于 druid 数据库连接池的工具类
*/
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);
}
}
}
BasicDAO.java
package dao_.dao;
import dao_.utils.JDBCUtilsByDruid;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
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;
/**
* 开发 BasicDAO , 是其他 DAO的父类, 使用到 apache-dbutils
*/
@SuppressWarnings({"all"})
public class BasicDAO<T> { // 泛型指定具体类型
private QueryRunner qr = new QueryRunner();
// 开发通用的 dml 方法, 针对任意的表
public int update(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);
}
}
// 返回多个对象(即查询的结果是多行), 针对任意表
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 qr.query(connection, sql, new BeanHandler<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);
}
}
}
ActorDAO.java
package dao_.dao;
import dao_.domain.Actor;
public class ActorDAO extends BasicDAO<Actor>{
// 1. 就有 BasicDAO 的方法
// 2. 根据业务需求, 可以编写特有的方法
}
Actor.java
package dao_.domain;
import java.util.Date;
/**
* Actor 对象和 actor 表的记录对应
*/
public class Actor { // Javabean, POJO, Domain 对象
private Integer id;
private String name;
private String sex;
private Date borndate;
private String phone;
public Actor() { // 一定要给一个无参构造器[反射需要]
}
public Actor(Integer id, String name, String sex, Date borndate, String phone) {
this.id = id;
this.name = name;
this.sex = sex;
this.borndate = borndate;
this.phone = phone;
}
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.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 Date getBorndate() {
return borndate;
}
public void setBorndate(Date borndate) {
this.borndate = borndate;
}
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
@Override
public String toString() {
return "Actor{" +
"id=" + id +
", name='" + name + '\'' +
", sex='" + sex + '\'' +
", borndate=" + borndate +
", phone='" + phone + '\'' +
'}';
}
}
TestDAO.java
package dao_.test;
import dao_.dao.ActorDAO;
import dao_.domain.Actor;
import org.junit.jupiter.api.Test;
import java.util.List;
public class TestDAO {
// 测试 ActorDAO 对 actor 表 crud 操作
@Test
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.println(actor);
}
// 2. 查询单行记录
Actor actor = actorDAO.querySingle("select * from actor where id = ?", Actor.class, 2);
System.out.println("====查询单行结果====");
System.out.println(actor);
// 3. 查询单行单列
Object o = actorDAO.queryScalar("select name from actor where id = ?", 1);
System.out.println("====查询单行单列值===");
System.out.println(o);
// 4. dml 操作 insert ,update, delete
int update = actorDAO.update("insert into actor values(null, ?, ?, ?, ?)", "张无忌", "男", "2000-11-11", "999");
System.out.println(update > 0 ? "执行成功" : "执行没有影响表");
}
}
druid.properties
#key=value
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/user_db?useUnicode=true&characterEncoding=UTF-8
username=root
password=root
#initial connection Size
initialSize=10
#min idle connecton size
minIdle=5
#max active connection size
maxActive=50
#max wait time (5000 mil seconds)
maxWait=5000
1.4、小练习
