Dbutils工具类的使用

一、什么是Dbutils？

　　Commons DbUtils是Apache组织提供的一个对JDBC进行简单封装的开源工具类库，使用它能够简化JDBC应用程序的开发，同时也不会影响程序的性能。来源百度百科

　　简而言之，Dbutils就是封装了jdbc的代码，简化了jdbc的dao层的操作。

二、Dbutils的使用

A：导入相关的依赖jar包

mysql-connector-java-5.1.7-bin.jar
commons-dbutils-1.7.jar

B：项目结构

StudentDao接口：对Student实体类对象操作的接口，使用接口利于维护
StudentDaoImpl：StudentDao接口的实现类，用于写具体的功能实现代码
Student类：学生实体类对象，用于封装学生对象的相关属性信息
Test类：功能测试类，用于测试功能实现的代码
JDBCTools：对于JDBC的初步封装的工具类，用于获取数据库连接以及释放资源等操作
db-config.properties文件：用于存储数据库的连接的一些信息，防止硬编码格式，方便更改

　C：使用Dbutils的具体步骤

　　a：创建QueryRunner对象　

QueryRunner queryRunner = new QueryRunner();

　　b：调用QueryRunner对象的方法执行相关操作，给QueryRunner对象传递参数：connection，sql，具体的策略对象;，条件参数

public void insert(Student student) {
        sql = "insert into student(name,clazz,grade) values(?,?,?)";
        try {
            //JDBCTools.getConnection()：数据库连接
            //sql：数据库查询sql语句
            //student.getName():需要的参数，参数与sql中的参数的数量保持一致
            queryRunner.update(JDBCTools.getConnection(),sql,student.getName(),student.getClazz(),student.getGrade());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

public Student findById(int id) {
        sql = " select * from student where id = ?";
        Student student = null;
        try {
            student = queryRunner.query(JDBCTools.getConnection(),sql,new BeanHandler<Student>(Student.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }

策略对象

BeanHandler：把单行的结果集封装成javabean对象，返回值是ResultSetHandler，该方法用于将单行结果集封装成javabean对象，对象是通过反射完成创建的

ResultSetHandler<javabean类型> rsh = new BeanHandler<javabean类型>(javabean.class);

BeanListHandler：将多行结果集封装成对象，并将对象添加到list集合中

 List<javaBean类型> list = <List<javaBean类型>> new BeanListHandler<javaBean类型>(javaBean.class);

MapHandler：将单行的结果集封装到一个map集合中，map集合中的建是表中的列名称，值对应表的列值。

Map <String,Object> map = new MapHandler();

MapListHandler：用于多行结果集的处理，把每行的结果封装成一个map，最后把所有的map都放到一个集合中，返回的是一个list集合，list集合中存放的是map集合。

  List<Map<String,Object>> listmap = new MapListHandler();

ColumnHandler：本方法用于互殴去单列，单行或者多行的数据

List<Object> nameList = new ColumnHandler();

ScalarHandler：用户处理单行、单列的数据，多用于聚合函数的查询。注意：当聚合函数涉及到数字类型的时候，要注意返回值类型的转换，若使用Integer或者Long类型的时候，容易出现数据无法存储的时候，所以使用Number（这个是所有数字数据类型的父类），对外提供了Number.intValue()和Number.longValue()等方法。

ResultSetHandler resultSetHandler = new ScalarHandler();

C：具体的实现代码

　　a：JavaBean对象

package com.dreambamboo.entity;

public class Student {
    private int id;
    private String name;
    private String grade;
    private String clazz;

    public Student(int id, String name, String grade, String clazz) {
        this.id = id;
        this.name = name;
        this.grade = grade;
        this.clazz = clazz;
    }

    public Student() {
    }

    public int getId() {
        return id;
    }

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

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getGrade() {
        return grade;
    }

    public void setGrade(String grade) {
        this.grade = grade;
    }

    public String getClazz() {
        return clazz;
    }

    public void setClazz(String clazz) {
        this.clazz = clazz;
    }

    public Student(String name, String grade, String clazz) {
        this.name = name;
        this.grade = grade;
        this.clazz = clazz;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", grade='" + grade + '\'' +
                ", clazz='" + clazz + '\'' +
                '}';
    }
}

　　b：dao接口

package com.dreambamboo.dao;

import com.dreambamboo.entity.Student;

import java.util.List;


public interface StudentDao {
    /**
     * 添加学生信息
     * @param student
     */
    public void insert(Student student);

    /**
     * 更新学生信息
     * @param student
     */
    public void update(Student student);

    /**
     * 删除学生信息
     * @param id
     */
    public void delete(int id);

    /**
     * 根据学生编号查询学生信息
     * @param id
     * @return
     */
    public Student findById(int id);

    /**
     * 查询所有学生信息
     * @return
     */
    public List<Student> findAll();

    /**
     * 查询学生总数
     * @return
     */
    public int studentCount();
}

　　c：dao接口实现类

package com.dreambamboo.dao.impl;

import com.dreambamboo.dao.StudentDao;
import com.dreambamboo.entity.Student;
import com.dreambamboo.util.JDBCTools;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.List;

public class StudentDaoImpl implements StudentDao {
    private QueryRunner queryRunner = null;//查询运行器
    public StudentDaoImpl(){
        queryRunner = new QueryRunner();
    }
    String sql = null;
    @Override
    public void insert(Student student) {
        sql = "insert into student(name,clazz,grade) values(?,?,?)";
        try {
            //JDBCTools.getConnection()：数据库连接
            //sql：数据库查询sql语句
            //student.getName():需要的参数，参数与sql中的参数的数量保持一致
            queryRunner.update(JDBCTools.getConnection(),sql,student.getName(),student.getClazz(),student.getGrade());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void update(Student student) {
        sql = " update student set name = ? ,clazz = ?,grade = ? where id = ?";
        try {
            queryRunner.update(JDBCTools.getConnection(),sql,student.getName(),student.getClazz(),student.getGrade(),student.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void delete(int id) {
        sql = "delete from student where id = ?";
        try {
            queryRunner.update(JDBCTools.getConnection(),sql,id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Student findById(int id) {
        sql = " select * from student where id = ?";
        Student student = null;
        try {
            student = queryRunner.query(JDBCTools.getConnection(),sql,new BeanHandler<Student>(Student.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return student;
    }

    @Override
    public List<Student> findAll() {
        sql = "select * from student";
        List<Student> list = null;
        try {
            list = queryRunner.query(JDBCTools.getConnection(),sql,new BeanListHandler<>(Student.class));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    @Override
    public int studentCount() {
        sql = "select count(id) from student";
        int count = 0;
        try {
            count =  queryRunner.query(JDBCTools.getConnection(),sql,new ScalarHandler<Integer>());

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
}

　　d：数据库接口连接工具类

package com.dreambamboo.util;



import java.sql.*;
import java.util.ResourceBundle;

/**
 * 数据库操作工具类
 */
public class JDBCTools {
    private static String URL;
    private static String USERNAME;
    private static String PASSWORD;
    private static String DRIVER;

    private static ResourceBundle resourceBundle = ResourceBundle.getBundle("com.dreambamboo.util.db-config");

    private JDBCTools(){

    }

    /**
     * 使用静态代码块加载驱动程序
     *      防止重复代码，使用静态代码块在类加载的时候只会执行一次
     */
    static {
        URL = resourceBundle.getString("jdbc.url");
        USERNAME = resourceBundle.getString("jdbc.username");
        PASSWORD = resourceBundle.getString("jdbc.password");
        DRIVER = resourceBundle.getString("jdbc.driver");
    }

    /**
     * 获取数据库连接
     * @return
     */
    public static Connection getConnection(){
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(URL,USERNAME,PASSWORD);
        } catch (SQLException e) {
            e.printStackTrace();
            System.out.println("获取连接失败");
        }
        return connection;
    }

    /**
     * 关闭数据库连接资源
     * @param connection    数据库连接
     * @param statement     数据库会话语句
     * @param resultSet     数据库查询结果集
     */
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        try {
            if (connection != null) {
                connection.close();
            }
            if (statement != null) {
                statement.close();
            }
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

}

　　e：数据库配置文件

jdbc.url=jdbc:mysql://localhost:3306/test
jdbc.username=root
jdbc.password=mysql
jdbc.driver=com.mysql.jdbc.Driver

　　f：测试代码

package com.dreambamboo.test;

import com.dreambamboo.dao.StudentDao;
import com.dreambamboo.dao.impl.StudentDaoImpl;
import com.dreambamboo.entity.Student;

import java.util.List;

public class Test {
    public static void main(String[] args) {
        StudentDao studentDao = new StudentDaoImpl();
        //studentDao.insert(new Student("孙悟空","高三年级","五班"));
        //studentDao.update(new Student(14,"孙悟空111","高三年级","十一班"));
        //studentDao.delete(14);
        List<Student> list = studentDao.findAll();
        for (Student st : list) {
            System.out.println(st.getId() + "===>>>" + st.getName() + "===>>>>" + st.getClazz() + "===>>> "+ st.getGrade());
        }
    }
}

三、自定义的Dbutils工具类

A：接口结构图分析

B：自定义的策略对象

QueryRuuner的封装

package com.dreambamboo.utils;
import java.sql.*;

/**
 * @className：QueryUtils
 * @description：自定义QueryRunner中的（crud）增、删、改、查的方法
 */
public class QueryUtils {
    /**
     * @method：update
     * @description：所有实体的CUD操作（Create、Update、Delete）
     *      由于所有实体的CUD操作代码基本相同，仅仅是操作执行的sql语句不同
     *      因此将实体的CUD方法统一抽取成update方法
     * @param connection        数据库连接对象
     * @param sql               执行操作的sql语句
     * @param params            执行sql语句时的参数列表
     */
    public static void update(Connection connection,String sql,Object params[]){
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1,params[i]);
            }
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseUtils.release(connection,preparedStatement,resultSet);
        }
    }

    /**
     * @method：query
     * @description：所有实体的R（Retrieve）读取查询操作
     *          实体的R操作，除了执行的sql语句不同，根据实体对象的不同，处理结果集的ResultSet的映射对象也不同
     *          因此在定义query方法时，可以将ResultSet的映射策略对象交由调用者提供，根据调用者提供的策略对象进行
     *          相对应的映射
     * @param connection        数据库连接对象
     * @param sql               执行的sql语句
     * @param params            执行sql语句时所需的参数列表
     * @param resultSetHandler  调用者提供的策略对象
     * @return                  与调用者提供的策略对象相对应的映射对象
     */
    public static Object query(Connection connection,String sql, Object params[], MyResultSetHandler resultSetHandler){
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        Object object = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i+1,params[i]);
            }
            resultSet = preparedStatement.executeQuery();
            object = resultSetHandler.handle(resultSet);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseUtils.release(connection, preparedStatement,resultSet);
        }
        return object;
    }
}

ResultSetHandler的封装

package com.dreambamboo.utils;

import java.sql.ResultSet;

/**
 * @className：MyResultSetHandler
 * @description：结果集处理器接口
 */
public interface MyResultSetHandler {
    /**
     * @method：handler
     * @description：结果集处理方法
     * @param resultSet 查询结果集
     * @return
     */
    public Object handle(ResultSet resultSet);
}

BeanHandler的封装
- 方式一

package com.dreambamboo.utils;

import org.apache.commons.dbutils.ResultSetHandler;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

public class MyBeanHandler implements ResultSetHandler {

    private Class aClass;
    public MyBeanHandler(Class aClass) {
        this.aClass = aClass;
    }


    @Override
    public Object handle(ResultSet resultSet) throws SQLException {
        //结果集的结构
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int size = resultSetMetaData.getColumnCount();
        Object object = null;
        try {
            object = aClass.getConstructor().newInstance();
            if (resultSet.next()){
                //根据结果集获取的操作
                //resultSetMetaData下标从1开始
                for (int i = 1; i <= size; i++) {
                    //mysql中的数据类型对应的javabean中的数据类型
                    String className = resultSetMetaData.getColumnClassName(i);
                    String columnName = resultSetMetaData.getColumnName(i);
                    String methodName = "set" + columnName.substring(0,1).toUpperCase() + columnName.substring(1);
                    Method method = null;
                    switch (className){
                        case "java.lang.String":
                            String valueStr = resultSet.getString(columnName);
                            method = aClass.getMethod(methodName,String.class);
                            method.invoke(object,valueStr);
                            break;
                        case "java.lang.Integer":
                            int valueInt = resultSet.getInt(columnName);
                            method = aClass.getMethod(methodName,int.class);
                            method.invoke(object,valueInt);
                            break;
                    }
                }
            }
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        }
        return object;
    }
}

- 方式二

package com.dreambamboo.utils;


import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

/**
 * @className：
 * @description：将结果集封装成javabean对象
 * @author：dreambamboo
 */
public class JavaBeanHandler implements MyResultSetHandler {
    private Class  clazz;
    public JavaBeanHandler(Class clazz){
        this.clazz = clazz;
    }
    @Override
    public Object handle(ResultSet resultSet) {
        Object bean = null;
        Object value = null;
        try {
            if (!resultSet.next()) {
                return null;
            }
            bean = clazz.newInstance();
            //获取结果集的元数据
            ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
            //获取结果集的总数
            int size = resultSetMetaData.getColumnCount();
            for (int i = 0; i < size; i++) {
                //获取元数据的列名称
                String columnName = resultSetMetaData.getColumnName(i+1);
                value = resultSet.getObject(i+1);
                //反射出类上列名对应的属性
                Field field = clazz.getDeclaredField(columnName);
                field.setAccessible(true);
                field.set(bean,value);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return bean;
    }
}

BeanListHandler的封装
- 方式一

package com.dreambamboo.utils;

import org.apache.commons.dbutils.ResultSetHandler;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MyBeanListHandler implements ResultSetHandler {
    private Class aClass;
    public MyBeanListHandler(Class aClass){
        this.aClass = aClass;
    }
    @Override
    public Object handle(ResultSet resultSet) throws SQLException {
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        List list = new ArrayList();
        int size = resultSetMetaData.getColumnCount();
        Object object = null;
        while (resultSet.next()){
            try {
                object = aClass.getConstructor().newInstance();
                for (int i = 1; i <= size; i++) {
                    String className = resultSetMetaData.getColumnClassName(i);
                    String columnName = resultSetMetaData.getColumnName(i);
                    String methodName = "set" + columnName.substring(0,1).toUpperCase() + columnName.substring(1);
                    Method method = null;
                    Object value = null;
                    switch (className){
                        case "java.lang.String":
                            value = resultSet.getString(i);
                            method = aClass.getMethod(methodName,String.class);
                            break;
                        case "java.lang.Integer":
                            value = resultSet.getInt(i);
                            method = aClass.getMethod(methodName,int.class);
                            break;
                    }
                    method.invoke(object,value);
                }
                list.add(object);
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InvocationTargetException e) {
                e.printStackTrace();
            } catch (NoSuchMethodException e) {
                e.printStackTrace();
            }

        }
        return list;
    }
}

- 方式二

package com.dreambamboo.utils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @className：ListHandler
 * @description：将结果集封装成list对象
 */
public class ListHandler implements MyResultSetHandler {
    private Class<?> clazz;
    public ListHandler(Class clazz){
        this.clazz = clazz;
    }
    @Override
    public Object handle(ResultSet resultSet) {
        List<Object> list = new ArrayList<>();
        Object bean = null;
        Field field = null;
        try {
            while (resultSet.next()){
                bean = clazz.newInstance();
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int size = resultSetMetaData.getColumnCount();
                for (int i = 0; i < size; i++) {
                    String columnName = resultSetMetaData.getColumnName(i+1);
                    Object value = resultSet.getObject(i+1);
                    field = bean.getClass().getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(bean,value);
                }
                list.add(bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        } finally {
        }
        return  list;
    }
}

BeanMapHandler的封装
- 方式一

package com.dreambamboo.utils;

import com.sun.org.apache.bcel.internal.generic.RET;
import org.apache.commons.dbutils.ResultSetHandler;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class MyMapHandler implements ResultSetHandler {
    private Class aClass;
    public MyMapHandler(Class aClass){
        this.aClass =aClass;
    }
    @Override
    public Map<Integer, Object> handle(ResultSet resultSet) throws SQLException {
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int size = resultSetMetaData.getColumnCount();
        Map<Integer,Object> map = new HashMap<>();
        Object value = null;
        Object object = null;
        String columnName = null;
        Method method  = null;
        String methodName = null;
        String className = null;
        int id = 0;
        try {
            if (resultSet.next()){
                object = aClass.getConstructor().newInstance();
                for (int i = 1; i <= size; i++) {
                    className = resultSetMetaData.getColumnClassName(i);
                    columnName = resultSetMetaData.getColumnName(i);
                    methodName = "set" + columnName.substring(0,1).toUpperCase() + columnName.substring(1);
                    if (columnName.equals("id")){
                        id = resultSet.getInt("id");
                    }
                    switch (className){
                        case "java.lang.String":
                            value = resultSet.getString(i);
                            method =aClass.getMethod(methodName,String.class);
                            break;
                        case "java.lang.Integer":
                            value = resultSet.getInt(i);
                            method = aClass.getMethod(methodName,int.class);
                            break;
                    }
                    method.invoke(object,value);
                }
            }
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        }
        map.put(id,object);
        return map;
    }

}

- 方式二

package com.dreambamboo.utils;

import java.lang.reflect.Field;
import java.security.PrivateKey;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

public class MapHandler implements MyResultSetHandler {
    private Class<?> clazz;
    public MapHandler(Class clazz){
        this.clazz = clazz;
    }
    @Override
    public Object handle(ResultSet resultSet) {
        Map<String,Object> map = new HashMap<>();
        Object bean = null;
        Field field = null;
        String idStr = null;
        try {
            while (resultSet.next()){
                bean = clazz.newInstance();
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int size = resultSetMetaData.getColumnCount();
                for (int i = 0; i < size; i++) {
                    String columnName = resultSetMetaData.getColumnName(i+1);
                    if ("id".equals(columnName)){
                        idStr = String.valueOf(resultSet.getObject(columnName));
                    }
                    Object value = resultSet.getObject(i+1);
                    field = bean.getClass().getDeclaredField(columnName);
                    field.setAccessible(true);
                    field.set(bean,value);
                }
                map.put(idStr,bean);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (NoSuchFieldException e) {
            e.printStackTrace();
        }
        return map;
    }
}

MapListHandler的封装
- 方式一

package com.dreambamboo.utils;

import org.apache.commons.dbutils.ResultSetHandler;

import javax.jws.Oneway;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MyMapListHandler implements ResultSetHandler {

    @Override
    public Object handle(ResultSet resultSet) throws SQLException {
        ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
        int size = resultSetMetaData.getColumnCount();
        List<Map<String,Object>> list = new ArrayList<>();
        Object object = null;
        Map<String,Object> map = null;
        while (resultSet.next()){
            map = new HashMap<>();
            for (int i = 1; i <= size; i++) {
                String columnClass = resultSetMetaData.getColumnClassName(i);
                String columnName = resultSetMetaData.getColumnName(i);
                switch (columnClass){
                    case "java.lang.String":
                        object = resultSet.getString(i);
                        break;
                    case "java.lang.Integer":
                        object = resultSet.getInt(i);
                        break;
                }
                map.put(columnName, object);
                list.add(map);
            }
         }
        return list;
    }
}

方式二

package com.dreambamboo.utils;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class MapListHandler implements MyResultSetHandler {
    @Override
    public Object handle(ResultSet resultSet) {
        String columnName = null;
        Object value = null;
        List<Map<String,Object>> list = new ArrayList<>();
        try {
            while (resultSet.next()){
                ResultSetMetaData resultSetMetaData = resultSet.getMetaData();
                int size = resultSetMetaData.getColumnCount();
                Map<String,Object> map = new HashMap<>();
                for (int i = 0; i < size; i++) {
                    columnName = resultSetMetaData.getColumnName(i+1);
                    value = resultSet.getObject(columnName);
                    map.put(columnName,value);
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return list;
    }
}

C：测试代码

a：BaseDao

package com.dreambamboo.dao;

import com.dreambamboo.entity.Student;

import java.util.List;

public interface BaseDao {
    public void save(Student student);
    public void delete(int id);
    public void update(Student student);
    public Student getById(int id);
    public List<Student> getAll();
}

b：BaseDaoImpl实现类

package com.dreambamboo.dao.impl;

import com.dreambamboo.dao.BaseDao;
import com.dreambamboo.entity.Student;
import com.dreambamboo.utils.JavaBeanHandler;
import com.dreambamboo.utils.ListHandler;
import com.dreambamboo.utils.QueryUtils;
import com.dreambamboo.utils.ReleaseUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class BaseDaoImpl implements BaseDao {
    private Connection connection = null;
    private String sql = null;
    private Object[] params = null;
    @Override
    public void save(Student student) {
        try {
            connection = ReleaseUtils.getConnection();
            sql = "insert into student(name,clazz,grade) values(?,?,?)";
            params = new Object[]{student.getName(),student.getClazz(),student.getGrade()};
            QueryUtils.update(connection,sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseUtils.release(connection,null,null);
        }
    }

    @Override
    public void delete(int id) {
        try {
            connection = ReleaseUtils.getConnection();
            sql = "delete from student where id = ?";
            params = new Object[]{id};
            QueryUtils.update(connection,sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseUtils.release(connection,null,null);
        }
    }

    @Override
    public void update(Student student) {
        try {
            connection = ReleaseUtils.getConnection();
            sql = "update student set name = ?,clazz = ?, grade = ? where id = ?";
            params = new Object[]{student.getName(),student.getClazz(),student.getGrade(),student.getId()};
            QueryUtils.update(connection,sql,params);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseUtils.release(connection,null,null);
        }
    }

    @Override
    public Student getById(int id) {
        Student student = null;
        try {
            connection = ReleaseUtils.getConnection();
            sql = "select * from student where id = ?";
            params = new Object[]{id};
            student = (Student) QueryUtils.query(connection,sql,params,new JavaBeanHandler(Student.class));

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseUtils.release(connection,null,null);
        }
        return student;
    }

    @Override
    public List<Student> getAll() {
        List<Student> list = null;
        try {
            connection = ReleaseUtils.getConnection();
            list = new ArrayList<>();
            sql = "select * from student";
            params = new Object[]{};
            list = (List<Student>) QueryUtils.query(connection,sql,params,new ListHandler(Student.class));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            ReleaseUtils.release(connection,null,null);
        }
        return list;
    }
}

c：测试类Test

测试添加代码

package com.dreambamboo.test;

import com.dreambamboo.dao.BaseDao;
import com.dreambamboo.dao.impl.BaseDaoImpl;
import com.dreambamboo.entity.Student;

public class Test03 {
    public static void main(String[] args) {
        BaseDao baseDao = new BaseDaoImpl();
        baseDao.save(new Student("张三","六班","高三"));
    }
}

- 运行效果

测试更新代码

package com.dreambamboo.test;

import com.dreambamboo.dao.BaseDao;
import com.dreambamboo.dao.impl.BaseDaoImpl;
import com.dreambamboo.entity.Student;

public class Test03 {
    public static void main(String[] args) {
        BaseDao baseDao = new BaseDaoImpl();
        Student student = new Student(13,"李四","gaosan","wuban");
        baseDao.update(student);
    }
}

- 运行结果

测试删除代码

package com.dreambamboo.test;

import com.dreambamboo.dao.BaseDao;
import com.dreambamboo.dao.impl.BaseDaoImpl;

public class Test03 {
    public static void main(String[] args) {
        BaseDao baseDao = new BaseDaoImpl();
       baseDao.delete(13);
    }
}

- 运行结果

测试查询代码
- 查询单条记录

package com.dreambamboo.test;

import com.dreambamboo.dao.BaseDao;
import com.dreambamboo.dao.impl.BaseDaoImpl;
import com.dreambamboo.entity.Student;


public class Test03 {
    public static void main(String[] args) {
        BaseDao baseDao = new BaseDaoImpl();
        Student student = baseDao.getById(14);
        System.out.println(student);
    }
}

- - 运行结果

- 查询所有

package com.dreambamboo.test;

import com.dreambamboo.dao.BaseDao;
import com.dreambamboo.dao.impl.BaseDaoImpl;
import com.dreambamboo.entity.Student;

import java.util.List;


public class Test03 {
    public static void main(String[] args) {
        BaseDao baseDao = new BaseDaoImpl();
        List<Student> list = baseDao.getAll();
        for (Student stu:list
             ) {
            System.out.println(stu);
        }
    }
}

- - 运行结果

JAVA学习网

分类

Dbutils工具类的使用

一、什么是Dbutils？

二、Dbutils的使用

A：导入相关的依赖jar包

B：项目结构

C：使用Dbutils的具体步骤

a：创建QueryRunner对象

b：调用QueryRunner对象的方法执行相关操作，给QueryRunner对象传递参数：connection，sql，具体的策略对象;，条件参数

策略对象

C：具体的实现代码

a：JavaBean对象

b：dao接口

c：dao接口实现类

d：数据库接口连接工具类

e：数据库配置文件

f：测试代码

三、自定义的Dbutils工具类

A：接口结构图分析

B：自定义的策略对象

C：测试代码

a：BaseDao

b：BaseDaoImpl实现类

c：测试类Test