dbutils组件的使用

简介

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

测试

1.导包

2.创建DBUtil工具类，用于获取连接

package com.serendipity.dbutils;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

import org.apache.commons.dbutils.DbUtils;

/**
 * @author 谢英亮
 * @date 2018年1月4日  下午8:14:34
 * @Description: 连接数据库
 */
public class DBUtil {
    private static String url;
    private static String user;
    private static String password;
    private static ThreadLocal<Connection> threadLocal;
    static {
        Properties prop = new Properties();
        threadLocal = new ThreadLocal<>();
        try {// 加载配置文件
            //prop.load(new FileInputStream("src/com/serendipity/db.properties"));
            prop.load(DBUtil.class.getResourceAsStream("db.properties"));
            //读取配置文件
            url=prop.getProperty("url");
            user=prop.getProperty("user");
            password=prop.getProperty("password");
            // 加载驱动
            Class.forName(prop.getProperty("driver"));
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接
     * 
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        Connection conn = threadLocal.get();
        if (null != conn) {
            return conn;
        }
        conn = DriverManager.getConnection(url, user, password);
        threadLocal.set(conn);
        return conn;
    }

    /**
     * 释放资源
     * 
     * @throws SQLException
     */
    public static void close() throws SQLException {
        Connection conn = threadLocal.get();
        if (null != conn && !conn.isClosed()) {
            conn.close();
            threadLocal.remove();
        }
    }
}

db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql:///test
user=root
password=admin

用户实体类，用于映射数据库中字段

package com.serendipity.dbutils;

/**
 * @author 谢英亮
 * @date 2018年1月4日  下午8:58:30
 * @Description: 用户实体类
 */
public class User {
    private int id;
    private String name;
    private int age;
    private String gendar;
    public User() {

    }
    public User(String name, int age, String gendar, int id) {
        super();
        this.name = name;
        this.age = age;
        this.gendar = gendar;
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getAge() {
        return age;
    }
    public void setAge(int age) {
        this.age = age;
    }
    public String getGendar() {
        return gendar;
    }
    public void setGendar(String gendar) {
        this.gendar = gendar;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Override
    public String toString() {
        return "User [id=" + id + ", name=" + name + ", age=" + age + ", gendar=" + gendar + "]";
    }


}

测试类

package com.serendipity.dbutils;

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

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

/**
 * @author 谢英亮
 * @date 2018年1月6日 上午8:53:57
 * @Description: 测试dbutils组件
 */
public class DbutilsTest {
    /**
     * 测试增删改
     * @throws Exception
     */
    @Test
    public void testUpdate() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = DBUtil.getConnection();
        //向数据库中增加一条数据
        String  sql="insert into t_user(name,age,gendar) values(?,?,?)";
        queryRunner.update(conn, sql,"小胖子",15,"男");

        //更新数据库中的信息
        /*String  sql="update  t_user set name = ? where id = ?";
        queryRunner.update(conn, sql,"小胖子",7);*/


    }
    /**
     * 测试查询功能
     * @throws Exception
     */
    @Test
    public void testQuery() throws Exception {
        QueryRunner queryRunner = new QueryRunner();
        Connection conn = DBUtil.getConnection();

        //查询一条数据
        /*String sql="select id,name,age,gendar from t_user where id = ?";
        User user = queryRunner.query(conn, sql, new BeanHandler<>(User.class),7);
        System.out.println(user);*/

        //查询几条数据
        String sql="select id,name,age,gendar from t_user where id < ?";
        List<User> users = queryRunner.query(conn, sql, new BeanListHandler<>(User.class),7);
        for (User user : users) {
            System.out.println(user);
        }
    }
}

小结

Dbutils组件可以减少程序员的工作量，使用起来也比较简单。如需相关的jar包，可以留言！