JDBC的BasicDao

一、问题

apache-dbutils+Druid 简化JDBC开发，存在那些不足？

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

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

3.在表很多，业务需求复杂，不能单靠一个Java类来完成。

二、BasicDao

1. DAO：data access object 数据库访问对象

2.专门和数据库交互的，完成对数据库的（表）的crud操作。

3.在BasicDao的基础上，实现一张表对应一个Dao，更好的完成功能。

package com.jun.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 JDBCUtilsDruid {
    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();
    }
    //关闭连接，在数据库连接池中，colse不是真正的断掉连接，是把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);
        }
    }
}

package com.jun.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 "\n Actor{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", sex='" + sex + '\'' +
                ", borndate=" + borndate +
                ", phone='" + phone + '\'' +
                '}';
    }
}

 

package com.jun.dao.dao;

import com.jun.jdbc.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;

/**
 * BasicDAO，是其他DAO的父类
 */
public class BasicDAO<T> {
    private QueryRunner queryRunner = new QueryRunner();
    //通用的dml方法，用于任意表
    public int update(String sql,Object...parameters) {
        Connection connection = null;
        try {
            connection = JDBCUtilsDruid.getConnection();
            int update = queryRunner.update(connection, sql, parameters);
            return update;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsDruid.close(null,null,connection);
        }
    }
    //返回多个对象（查询的结果是多行）用于任意表

    /**
     *
     * @param sql sql语句，可以？
     * @param 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 = JDBCUtilsDruid.getConnection();
            return queryRunner.query(connection,sql,new BeanListHandler<T>(clazz),parameters);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsDruid.close(null,null,connection);
        }
    }
    //查询单行结果的通用方法
    public T querySingle(String sql,Class<T> clazz,Object...parameters){
        Connection connection = null;
        try {
            connection = JDBCUtilsDruid.getConnection();
            return queryRunner.query(connection,sql,new BeanHandler<T>(clazz),parameters);

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            JDBCUtilsDruid.close(null,null,connection);
        }
    }
    //查询单行单列的方法，返回单值的方法
    public Object queryScalar(String sql,Object...parameter){
        Connection connection = null;
        try {
            connection = JDBCUtilsDruid.getConnection();
            return queryRunner.query(connection,sql,new ScalarHandler(),parameter);

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

package com.jun.dao.dao;

import com.jun.dao.domain.Actor;

public class ActorDAO extends BasicDAO<Actor> {
    //有BasicDAO的方法
}

 

package com.jun.dao.test;

import com.jun.dao.dao.ActorDAO;
import com.jun.dao.domain.Actor;
import org.junit.Test;

import java.util.List;

public class TestDAO {
    //测试ActorDAO对actor表crud操作
    @Test
    public void testActorDAO() {
        ActorDAO actorDAO = new ActorDAO();
        //查询
        List<Actor> actors = actorDAO.queryMulti("select * from actor where id >=?", Actor.class, 1);
        for (Actor actor : actors) {
            System.out.println(actor);
        }
        //查询单行记录
        Actor actor = actorDAO.querySingle("select * from actor where id =?", Actor.class, 1);
        System.out.println("----查询单行结果-----");
        System.out.println(actor);

        //查询单行单列
        Object o = actorDAO.queryScalar("select * from actor where id =?", 2);
        System.out.println("-----查询单行单列值------");
        System.out.println(o);

        //dml 操作
        int update = actorDAO.update("insert into actor values(null,?,?,?,?)", "宋小宝", "女", "1988-6-24", "123456");
        System.out.println(update>0?"执行成功":"执行没有影响到表");

    }


}