JDBC基于dbutils和druid

1.bean部分

部门类

public class Department {
    private int did;
    private String dname;
    private String description;

    public Department() {
    }

    public Department(String dname, String description) {
        this.dname = dname;
        this.description = description;
    }

    public Department(int did, String dname, String description) {
        this.did = did;
        this.dname = dname;
        this.description = description;
    }

    public int getDid() {
        return did;
    }

    public void setDid(int did) {
        this.did = did;
    }

    public String getDname() {
        return dname;
    }

    public void setDname(String dname) {
        this.dname = dname;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    @Override
    public String toString() {
        return "Department{" +
                "did=" + did +
                ", dname='" + dname + '\'' +
                ", description='" + description + '\'' +
                '}';
    }
}

2.DAO部分

2.1基础DAO实现

public abstract class BaseDAOImpl {
    // 获取QueryRunner的对象
    private QueryRunner queryRunner = new QueryRunner();

    /**
     * 修改功能的通用方法
     * @param sql  所需要的SQL语句
     * @param args 所需要传入的具体值 取代？
     * @return int 返回影响的条数
     */
    protected int update(String sql,Object...args){
        try {
            return  queryRunner.update(JDBCToolsFinal.getConnection(),sql,args);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询全部
     * @param clazz
     * @param sql
     * @param args
     * @return
     * @param <T>
     */
    protected <T>List<T> getAllList(Class<T> clazz, String sql, Object...args) {
        try {
            return queryRunner.query(JDBCToolsFinal.getConnection(), sql, new BeanListHandler<>(clazz), args);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 查询单个
     * @param clazz
     * @param sql
     * @param args
     * @return
     * @param <T>
     */
    protected <T> T getBean(Class<T> clazz, String sql, Object...args) {
        List<T> list = getAllList(clazz,sql,args);
        if(list != null && list.size() > 0) {
            return list.get(0);
        };
        return null;
    }

    /**
     * 查询单个值
     * @param sql
     * @param args
     * @return
     */
    protected Object getValue(String sql, Object...args) {
        try {
            return queryRunner.query(JDBCToolsFinal.getConnection(),sql,new ScalarHandler<>(),args);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }

    protected int[] batchUpdate(String sql, Object[][] args) {
        try {
            return queryRunner.batch(JDBCToolsFinal.getConnection(),sql,args);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

2.2部门接口

public interface DepartmentDAO {
    /**
     * 添加部门
     * @param department
     * @return
     */
    boolean addDepartment(Department department);

    /**
     * 更新部门
     * @param department
     * @return
     */
    boolean updateDepartment(Department department);

    /**
     * 删除部门
     * @param did
     * @return
     */
    boolean deleteDepartment(int did);

    /**
     * 查询全部部门
     * @return
     */
    List<Department> getAllDepartment();

    /**
     * 查询单个部门
     * @return
     */
    Department getDepartment(int did);

    /**
     * 查询总部门数
     * @return
     */
    long getDepartmentCount();


    int[] batchInsert(Object[][] objects);
}

2.3部门接口实现

public class DepartmentDAOImpl extends BaseDAOImpl implements DepartmentDAO {
    /**
     * 添加部门实现
     * @param department 参数为部门对象
     * @return 返回添加是否成功 结果为布尔值
     */
    @Override
    public boolean addDepartment(Department department) {
        String sql = "INSERT INTO t_department VALUES(null,?,?)";
        return update(sql,department.getDname(),department.getDescription()) > 0;
    }

    /**
     * 更新部门实现
     * @param department
     * @return
     */
    @Override
    public boolean updateDepartment(Department department) {
        String sql = "UPDATE t_department SET dname = ?,description = ? WHERE did = ?";
        return update(sql,department.getDname(),department.getDescription(),department.getDid()) > 0;
    }

    /**
     * 删除部门实现
     * @param did
     * @return
     */
    @Override
    public boolean deleteDepartment(int did) {
        String sql = "DELETE FROM t_department WHERE did = ?";
        return update(sql,did) > 0;
    }

    /**
     * 获取全部部门实现
     * @return
     */
    @Override
    public List<Department> getAllDepartment() {
        String sql = "SELECT * FROM t_department";
        return getAllList(Department.class,sql);
    }

    /**
     * 获取单个部门
     * @return
     */
    @Override
    public Department getDepartment(int did) {
        String sql = "SELECT * FROM t_department WHERE did = ?";
        return getBean(Department.class,sql,did);
    }

    @Override
    public long getDepartmentCount() {
        String sql = "SELECT count(*) FROM t_department";
        return (long) getValue(sql);
    }

    @Override
    public int[] batchInsert(Object[][] objects) {
        String sql = "INSERT INTO t_department VALUES(null,?,?)";
        return batchUpdate(sql,objects);
    }
}

3.utils工具实现

public class JDBCToolsFinal {
    private static DataSource dataSource;
    private static ThreadLocal<Connection> threadLocal = new ThreadLocal<>();

    static {
        Properties properties = new Properties();
        try {
            properties.load(JDBCToolsFinal.class.getClassLoader().getResourceAsStream("druid.properties"));
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
           e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
        Connection connection = threadLocal.get();
        if(connection == null) {
            connection = dataSource.getConnection();
            threadLocal.set(connection);
        }
        return connection;
    }


    public static void freeConnection() throws SQLException {
        Connection connection = threadLocal.get();
        connection.setAutoCommit(true);
        connection.close();
        threadLocal.remove();
    }
}

4.测试

public class Main {
    public static void main(String[] args) {
        // 测试添加部门
        DepartmentDAOImpl dao = new DepartmentDAOImpl();
        /*
        Scanner scanner = new Scanner(System.in);
        System.out.print("输入部门名称:");
        String departmentName = scanner.nextLine();
        System.out.println("输入部门描述:");
        String departmentDescription = scanner.nextLine();
        // 创建部门
        Department department = new Department(departmentName,departmentDescription);
        boolean flag = dao.addDepartment(department);
        System.out.println(flag ? "添加成功" : "添加失败");*/

        //测试更新部门
        /*Department department = new Department(4010,"游戏部","只打瓦洛兰特");
        dao.updateDepartment(department);*/

        //测试删除部门
        /*dao.deleteDepartment(4010);*/

        //测试获取全部部门
        /*List<Department> list = dao.getAllDepartment();
        for (Department department : list) {
            System.out.println(department);
        }*/

        // 测试获取单个部门
        /*Department department = dao.getDepartment(1);
        System.out.println(department);*/

        // 测试获取所有部门数量
        /*long number = dao.getDepartmentCount();
        System.out.println(number);*/

        //测试批量添加
        Object[][] objects = new Object[1000][2];
        for (int i = 0; i < 1000; i++) {
            objects[i][0] = "测试数据" + i;
            objects[i][1] = "测试描述" + i;
        }
        int[] ins = dao.batchInsert(objects);
        for (int in : ins) {
            System.out.println(in);
        }
    }
}