详解DBUtils工具的使用

DBUtils的使用   

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

DBUtils简介

  DBUtils是java编程中的数据库操作实用工具，小巧简单实用，
  1.对于数据表的读操作，可以把结果转换成List，Array，Set等java集合，便于程序员操作
  2.对于数据表的写操作，也变得很简单（只需写sql语句）

DBUtils包括的主要类

DbUtils类：启动类
ResultSetHandler接口：转换类型的接口

• ArrayHandler类：实现类，把记录转化为数组
• ArrayListHandler类：把记录转化为数组，并放入集合中
• ColumnListHandler类：获取某一列的数据。封装到List中
• ScalarHandler类：适合获取一行，一列的数据
• BeanHandler类：实现类，把记录转成对象
• BeanListHandler类：实现类，把记录转化为List，使记录为JavaBean类型的对象
  QueryRunner类：执行SQL语句的类

DBUtils工具类的封装（实现对数据库的增删改查）

准备工作：
  导入jar包 工具类 配置文件
  commons-dbutils-1.6.jar
  druid-1.1.5.jar
  DruidUtils.java工具类
  druid.properties配置文件

配置文件：

#连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/myschool?useSSL=true&characterEncoding=utf8
username=root
password=root
#<!-- 初始化连接 -->
initialSize=20
#最大连接数量
maxActive=50
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=5000

创建类，把类中的属性和数据库中的属性对应

public class User implements Serializable {

    private static final long serialVersionUID = 3567498598890692746L;

    private Integer id;
    private String username;
    private String password;
    private String phone;

    public User() {
    }

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

    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;
    }

    public String getPhone() {
        return phone;
    }

    public void setPhone(String phone) {
        this.phone = phone;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", phone='" + phone + '\'' +
                '}';
    }

把每次都需要进行的连接操作抽取出来，使用连接池，每次都从连接池中取连接，返回的参数，正好是DBUtils使用的参数

public class DataSourceUtils {

    private static DruidDataSource dataSource;
    static {
        try {
            Properties properties = new Properties();
            InputStream is = DataSourceUtils.class.getClassLoader().getResourceAsStream("druid.properties");
            properties.load(is);

            dataSource = (DruidDataSource) DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static DataSource getDataSource(){
        return dataSource;
    }
}

接口：

public interface UserDao {
    
    //添加
    void add(User user);

    //修改
    void Update(User user);

    //删除
    void Delete(Integer id);
    
    //查询所有
    public List<User> findAll();

    //根据id查询某个人的数据
    User findById(Integer id);
    
    //获取数据个数
    long getCount() throws SQLException;

    //把查询的数据转换成一个数组
    Object[] findArrayUser(Integer id);

    //查询所有数据，把每条数据当成是一个对象，存入集合中
    List<Object[]> findArrayList();

    //查询一列的数据
    List<String> findNames();
}

接口的实现类：

public class UserDaoImpl implements UserDao {

    @Override
    public void add(User user) {
        //1.创建查询执行器，传入连接池，不用关闭
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        //2.执行
        Object[] params = {user.getId(), user.getUsername(), user.getPassword(), user.getPhone()};
        try {
            qr.update("insert into user(id,username,password,phone) values(?,?,?,?)", params);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("添加失败", e);
        }
    }

    @Override
    public void Update(User user) {
        //1.创建查询执行器，传入连接池
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        //2.执行
        Object[] params = {user.getUsername(), user.getPassword(), user.getPhone(),user.getId()};
        try {
            qr.update("update user set username=?, password=?, phone=? where id=?", params);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("更新失败", e);
        }
    }

    @Override
    public void Delete(Integer id) {
        //1.创建查询执行器，传入连接池
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        //2.执行
        try {
            qr.update("delete from user where id=?", id);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("删除失败", e);
        }
    }


    @Override
    public List<User> findAll() {
        //1.创建查询
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());

        try {
            //返回一个对象数组
            List<User> list = qr.query("select * from user", new BeanListHandler<User>(User.class));
            return list;
        } catch (SQLException e) {
            //e.printStackTrace();
            throw new RuntimeException("查不到啊，老弟",e );
        }
    }

    @Override
    public User findById(Integer id) {
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());

        try {
            //返回一个对象
            User user = qr.query("select * from user where id=?", new BeanHandler<User>(User.class),id);
            //User user = qr.query(sql, new BeanHandler<User>(User.class),id);
            return user;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查不到啊，老弟",e );
        }
    }

    @Override
    public long getCount() {
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        Long count = null;
        try {
            //单行单列
            count = (Long) qr.query("select count(*) from user", new ScalarHandler<>());
            return count;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    @Override
    public Object[] findArrayUser(Integer id) {
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        try {
            Object[] objects = qr.query("select * from user where id=?", new ArrayHandler(),id);
            return objects;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询失败", e);
        }
    }

    @Override
    public List<Object[]> findArrayList() {
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        try {
            List<Object[]> list = qr.query("select * from user", new ArrayListHandler());
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询失败", e);
        }
    }

    @Override
    public List<String> findNames() {
        QueryRunner qr = new QueryRunner(DataSourceUtils.getDataSource());
        try {
            List<String> username = qr.query("select * from user", new ColumnListHandler<String>("username"));
            return username;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException("查询失败", e);
        }
    }
}

测试类：

public class UserDaoImplTest {

    @Test
    public void testAdd() {
        UserDao userDao = new UserDaoImpl();
        User user = new User(4,"诸葛亮", "0000", "120");
        userDao.add(user);
        System.out.println("添加成功");
    }

    @Test
    public void testUpdate(){
        UserDao userDao = new UserDaoImpl();
        User user = new User(4,"诸葛亮", "55555", "120");
        userDao.Update(user);
        System.out.println("更新成功");
    }

    @Test
    public void testDelete(){
        UserDao userDao = new UserDaoImpl();
        userDao.Delete(4);
        System.out.println("删除成功");
    }

    @Test
    public void testFindAll(){
        UserDao userDao = new UserDaoImpl();
        List<User> list = userDao.findAll();
        for (User user : list) {
            System.out.println(user.toString());
        }
    }

    @Test
    public void testFindById() {
        UserDao userDao = new UserDaoImpl();
        User user = userDao.findById(1);
        System.out.println(user.toString());
    }

    @Test
    public void testCount(){
        UserDao userDao = new UserDaoImpl();
        try {
            long count = userDao.getCount();
            System.out.println(count);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Test
    public void testArrayList() {
        UserDao userDao = new UserDaoImpl();
        List<Object[]> arrayList = userDao.findArrayList();
        for (Object[] objects : arrayList) {
            System.out.println(Arrays.toString(objects));
        }
    }

    @Test
    public void testArrayListUser() {
        UserDao userDao = new UserDaoImpl();
        Object[] user = userDao.findArrayUser(3);
        for (Object o : user) {
            System.out.println(o.toString());
        }
    }

    @Test
    public void testFindNames() {
        UserDao userDao = new UserDaoImpl();
        List<String> names = userDao.findNames();
        for (String name : names) {
            System.out.println(name);
        }
    }
}

【总结】：

/**
 *
 *对于增、删、改来说
 *  步骤
 *      1.创建查询执行器（QueryRunner），传入连接池
 *      2.调用 查询执行器的 update()方法，传入 【相应的数据增、删、改语句】，和需要的 【参数】
 *
 * 对于查询来说
 *  步骤：
 *      1.创建查询执行器（QueryRunner），传入连接池
 *      2.调用查询执行器的 query()方法，传入 【相应数据查询语句】，和相应的参数【参数传入想要得到的数据对应的类，和参数】
 */