BasicDAO问题

介绍:

在apache-dbutils和Druid简化JDBC开发，还有许多的不足：

1.SQL语句是固定的，不能通过参数传入，通用性不好，需要进行改进，更方便执行增删改查操作

2.对select操作，如果有返回值，返回类型不能固定，需要使用泛型

3.将来的表很多，业务需求复杂，不可能只靠一个java类完成

BasicDAO示意图：

 BasicDAO分析

1.utils包：存放JDBCUtilsDruid 工具类

2.domain包：存放javabean(Admin02类)

3.dao包：存放XxxDAO和BasicDAO(Admin02DAO类、BasicDao类)

4.text包：存放TsetDAO 测试类

1.utils包：存放JDBCUtilsDruid 工具类：

package com.jh.dao_.test;

//基于druid数据库连接池的工具类

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;

public class JDBCUtilsDruid {

    private static DataSource ds;

    static {//静态代码块完成ds的初始化
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            //创建指定参数的连接池
            ds = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    //编写getConnection方法
    public static Connection getConnection(){
        try {
            return ds.getConnection();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    //关闭连接(在数据库连接池技术中，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);
            }
        }
    }

2.domain包：存放javabean(Admin02类)：

package com.jh.dao_.domain;

//Admin02表与admin02表的记录对应

public class Admin02 {
    private Integer id;
    private String username;
    private String password;

    public Admin02() {
    }

    public Admin02(Integer id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Override
    public String toString() {
        return id + " \t" + username + '\t' + password ;
    }
}

3.dao包：存放XxxDAO和BasicDAO(Admin02DAO类、BasicDao类)：

Admin02DAO类：

package com.jh.dao_.dao;

//

import com.jh.dao_.domain.Admin02;

public class Admin02DAO extends BasicDao{

    //可以编写特有的方法
    
}

BasicDao类：

package com.jh.dao_.dao;

//该类是其他DAO类的父类

import com.jh.datasource.JDBCUtilsDruid;
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;

public class BasicDao {//泛型指定具体的类型

    //1.
    private  QueryRunner qr = new QueryRunner();

    //开发通用的DML方法
    public int update(String sql,Object...parameters){
        Connection connection = null;

        try {
            connection = JDBCUtilsDruid.getConnection();
            int update = qr.update(connection, sql, parameters);
            return update;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsDruid.close(null,null,connection);
        }

    }

    //返回多个对象(查询的结果是多行)
    /**
     * @param sql:sql语句，可以有？
     * @param class01:传入一个类的Class对象，如Admin02.Class
     * @param parameters:传入?的具体值，可以是多个
     * @return:根据Admin02.Class 返回对应的ArrayList集合
     */
    public List queryMultiply(String sql,Classclass01,Object...parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsDruid.getConnection();
            return qr.query(connection,sql,new BeanListHandler(class01),parameters);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsDruid.close(null,null,connection);
        }
    }

    //返回单行结果的通用方法
    public T querySingle(String sql,Classclass01,Object...parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsDruid.getConnection();
            return qr.query(connection,sql,new BeanHandler(class01) ,parameters);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsDruid.close(null,null,connection);
        }

    }

    //查询单行单列的方法，返回单值的方法
    public Object queryscalar(String sql,Object...parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsDruid.getConnection();
            return qr.query(connection,sql,new ScalarHandler() ,parameters);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsDruid.close(null,null,connection);
        }
    }
}

4.text包：存放TsetDAO 测试类：

package com.jh.dao_.test;

import com.jh.dao_.dao.Admin02DAO;
import com.jh.dao_.domain.Admin02;
import org.junit.jupiter.api.Test;

import java.util.List;

public class TestDAO {

    //测试Admin02DAO对表admin02的crud操作
    @Test
    public void testAdmin02DAO(){
        Admin02DAO admin02DAO = new Admin02DAO();
        List admin02s = admin02DAO.queryMultiply(
                "select * from admin02 where id >= ?", Admin02.class, 100);
        System.out.println("查询结果...");
        for (Admin02 admin02 : admin02s){
            System.out.println(admin02);
        }
    }

    //查询单行记录
    @Test
    public void testAdmin02DAO02(){
        Admin02DAO admin02DAO = new Admin02DAO();
        Object querySingle = admin02DAO.querySingle(
                "select * from admin02 where id = ?", Admin02.class, 10);
        System.out.println("查询单行记录...");
        System.out.println(querySingle);

    }

    //查询单行单列
    @Test
    public void testAdmin02DAO03(){
        Admin02DAO admin02DAO = new Admin02DAO();
        Object queryscalar = admin02DAO.queryscalar("select username from admin02 where id = ?", 1);
        System.out.println("查询单行单列...");
        System.out.println(queryscalar);

    }

    //DML操作
    @Test
    public void testAdmin02DAO04(){
        Admin02DAO admin02DAO = new Admin02DAO();
        int update = admin02DAO.update("insert into admin02 values(null,'jett','123456')");
        System.out.println(update > 0 ? "执行成功" : "执行没有影响表");

    }

}

BasicDAO的实现

测试Admin02DAO对表admin02的crud操作：

 查询单行记录：

 查询DML操作：