DAO数据访问对象

本文主要讲述DAO层，数据访问对象的含义与使用

一. 图解DAO层

vip

二. 代码结构

vip

　　1. dao

vip

　　2. domain

vip

　　3. utils

vip

　　4.test做测试

vip

三. 具体实现

　　1.1 dao中的BasicDAO

/**
 * 处理sql语句，返回结果
 */
public class BasicDAO<T> {
    private QueryRunner queryRunner = new QueryRunner();
    private Connection connection;

    /**
     *
     * @param sql sql语句
     * @param aClass aClass反射对象
     * @param params params可变参数，对?赋值
     * @return
     */
    // 查询多行结果
    // 方法中的泛型，必须是类中声明过的，没有声明的不能使用。
    public List<T> queryMultiple(String sql, Class<T> aClass, Object... params){
        List<T> result = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            result = queryRunner.query(connection,sql, new BeanListHandler<T>(aClass), params);
        } catch (SQLException e) {
            throw new RuntimeException(e); // 方法默认抛出运行时异常机制
        } finally {
            JDBCUtilsByDruid.Close(null,null,connection);
        }

        return result;
    }

    // 查询单行数据
    public T querySingle(String sql,Class<T> aClass,Object... params){
        T t = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            System.out.println("@2:" + connection);
            t = queryRunner.query(connection,sql,new BeanHandler<T>(aClass),params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtilsByDruid.Close(null,null,connection);
        }

        return t;
    }

    // 查询单行单列的数据
    public Object queryScalar(String sql,Object... params){
        Object obj = null;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            obj = queryRunner.query(connection, sql, new ScalarHandler(), params);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.Close(null,null,connection);
        }
        return obj;
    }

    // 执行DML语句
    public int update(String sql,Object...params){
        int update = 0;
        try {
            connection = JDBCUtilsByDruid.getConnection();
            update = queryRunner.update(connection,sql, params);
        } catch (SQLException e) {
            new RuntimeException(e);
        } finally {
            JDBCUtilsByDruid.Close(null,null,connection);
        }
        return update;
    }
}

　　注意：这里的泛型使用，方法中所使用的泛型，必须是在该类中实现声明的，如：

　　BasicDAO类声明泛型T

public class BasicDAO<T> {}

　　BasicDAO类的方法使用泛型T

ublic List<T> queryMultiple(String sql, Class<T> aClass, Object... params){}

　　1.2 dao中的AdminDAO

public class AdminDAO extends BasicDAO<Admin> {
    // 1.继承BasicDAO的所有方法
    // 2.可以写特有的方法
}

　　2 domain中的Admin

public class Admin {
    private Integer id;
    private String name;
    private String pwd;

    public Admin() {
    }

    public Admin(Integer id, String name, String pwd) {
        this.id = id;
        this.name = name;
        this.pwd = pwd;
    }

    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 getPwd() {
        return pwd;
    }

    public void setPwd(String pwd) {
        this.pwd = pwd;
    }

    @Override
    public String toString() {
        return "Admin{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}

　　注意：domain中的类的无参构造器不能省略【反射机制】

　　3. utils中的 JDBCUtilsByDruid

public class JDBCUtilsByDruid {
    private static DataSource druid;

    static {
        Properties properties = new Properties();
        try {
            properties.load(new FileInputStream("src\\druid.properties"));
            druid = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static Connection getConnection(){
        Connection connection = null;
        try {
            connection = druid.getConnection();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return connection;
    }

    @SuppressWarnings({"all"})
    public static boolean 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();
        }
        return true;
    }
}

　　参看 jdbc+Druid工具类 - zwGitOne - 博客园 (cnblogs.com)这篇博客

　　4. test测试

public class TestDAO {

    @Test
    public void testAdmin(){
        // 1.查询多行记录
        String all = "select * from admin ";
        AdminDAO adminDAO = new AdminDAO();
        List<Admin> adminList = adminDAO.queryMultiple(all, Admin.class, null);

        System.out.println("===显示表中所有的数据===");
        Iterator<Admin> iterator = adminList.iterator();
        while (iterator.hasNext()) {
            Admin admin = iterator.next();
            System.out.println(admin);
        }

        // 2.查询单行记录
        String single = "select * from admin where id = ?";
        Admin admin = adminDAO.querySingle(single, Admin.class, 8);
        System.out.println("===显示单行对象===");
        System.out.println(admin);

        // 3.查询单行单个对象
        String scalar = "select name from admin where id = ?";
        int id = 8;
        Object o = adminDAO.queryScalar(scalar, id);
        System.out.println("===显示单行单个对象===");
        System.out.println("id:" + id + "的name是" + o);

        // 增加
        String insert = "insert into admin values(null,?,?)";
        int row = adminDAO.update(insert, "张山峰", "123456");

        // 修改
        String update = "update admin set name = ? where id = ?";
        row = adminDAO.update(update, "张三丰", 16);

        // 删除
        String delete = "delete from admin where id = ?";
        row = adminDAO.update(delete,16);
    }
}

　　运行结果如下：

===显示表中所有的数据===
Admin{id=3, name='Tom3', pwd='123'}
Admin{id=5, name='Tom2', pwd='123'}
Admin{id=6, name='Tom3', pwd='123'}
Admin{id=8, name='Tom2', pwd='123'}
Admin{id=9, name='Tom3', pwd='123'}
Admin{id=11, name='zw', pwd='123'}
Admin{id=12, name='郑为', pwd='1234'}
Admin{id=13, name='郑威', pwd='1234'}
Admin{id=14, name='郑为', pwd='1234'}
Admin{id=15, name='郑威', pwd='1234'}
Admin{id=17, name='张山峰', pwd='123456'}
Admin{id=18, name='张山峰', pwd='123456'}
Admin{id=19, name='张山峰', pwd='123456'}
Admin{id=20, name='张山峰', pwd='123456'}
@2:com.mysql.cj.jdbc.ConnectionImpl@10aa41f2
===显示单行对象===
Admin{id=8, name='Tom2', pwd='123'}
===显示单行单个对象===
id:8的name是Tom2

Process finished with exit code 0