Java之JDBC之DAO 和增删改查通用方法-BasicDao的封装

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、小练习