commons-dbutils使用介绍

commons-dbutils是Apache开源组织提供的用于操作数据库的工具包。今天为大家介绍一下该包的常用方法。
对于数据库的操作无外乎增删改查，而增删改本质上可以归为一类，操作方式相同，只是SQL语法不同而已，所以我将以修改和查询两类来介绍commons-dbutils的用法。

首先我们来创建一个测试类，使用JUnit进行测试。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;


public class DBUtilsTest
{
   private Connection conn = null;

   @Before
   public void initConnection() throws SQLException, ClassNotFoundException
   {
      printCurrentMethodName();
      Class.forName("org.h2.Driver");
      conn = DriverManager.getConnection("jdbc:h2:h2.db", "test", "123");
   }

   @Before
   public void initDatabase() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      runner.update(
            conn,
            "CREATE TABLE IF NOT EXISTS USER_INFO (userId VARCHAR(20) PRIMARY KEY, userName VARCHAR(50))");
   }

   @After
   public void destory()
   {
      printCurrentMethodName();
      DbUtils.closeQuietly(conn);
   }

   /**
    * 打印当前运行方法名称
    */
   public void printCurrentMethodName()
   {
      System.out.println(Thread.currentThread().getStackTrace()[2]
            .getMethodName());
      System.out.println("==================================================");
   }
}

在这段测试代码中，我们是用h2作为数据库进行测试以及定义了一些连接、数据库的初始化及销毁的方法。
在commons-dbutils中操作数据库的常用类为：QueryRunner。
QueryRunner的常用方法如下：

返回值	方法名	说明
int[]	batch(Connection conn, String sql, Object[][] params)	批量执行INSERT、UPDATE或DELETE
int[]	batch(String sql, Object[][] params)	批量执行INSERT、UPDATE或DELETE
T	insert(Connection conn, String sql, ResultSetHandler rsh)	执行一个插入查询语句
T	insert(Connection conn, String sql, ResultSetHandler rsh, Object… params)	执行一个插入查询语句
T	insert(String sql, ResultSetHandler rsh)	执行一个插入查询语句
T	insert(String sql, ResultSetHandler rsh, Object… params)	执行一个插入查询语句
T	insertBatch(Connection conn, String sql, ResultSetHandler rsh, Object[][] params)	批量执行插入语句
T	insertBatch(String sql, ResultSetHandler rsh, Object[][] params)	批量执行插入语句
T	query(Connection conn, String sql, ResultSetHandler rsh)	查询
T	query(Connection conn, String sql, ResultSetHandler rsh, Object… params)	查询
T	query(String sql, ResultSetHandler rsh)	查询
T	query(String sql, ResultSetHandler rsh, Object… params)	查询
int	update(Connection conn, String sql)	执行INSERT、UPDATE或DELETE
int	update(Connection conn, String sql, Object… params)	执行INSERT、UPDATE或DELETE
int	update(Connection conn, String sql, Object param)	执行INSERT、UPDATE或DELETE
int	update(String sql)	执行INSERT、UPDATE或DELETE
int	update(String sql, Object… params)	执行INSERT、UPDATE或DELETE
int	update(String sql, Object param)	执行INSERT、UPDATE或DELETE

修改

我们先来看一下如何使用QueryRunner进行修改操作，在我们的测试代码中添加测试方法：

@Test
public void update() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   String suffix = Long.toHexString(System.currentTimeMillis());
   Integer result = runner.update(conn,
         "insert into USER_INFO(userId, userName) values(?, ?)", suffix,
         "name" + suffix);
   System.out.println("受影响记录条数：" + result);
}

修改的操作相比较而言还是很简单的，在这段测试代码中，我们向数据库中添加了一条记录，在QueryRunner中也是支持动态参数的，可以很方便的绑定参数。

查询

ScalarHandler

ScalarHandler会返回一个对象，用于读取结果集中第一行指定列的数据。这里我们以查询表中总记录数为例：

@Test
public void queryByScalarHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   Number number = runner.query(conn, "select count(*) from USER_INFO",
         new ScalarHandler<Number>());
   System.out.println("总记录记录条数：" + number.intValue());
}

不知大家有没有发现，在ScalarHandler的泛型中，我使用的是Number，解释一下：之前我在使用该方法查询记录条数的时候，不同的数据库返回的数据类型可能不同，有的返回是Integer，而有的却是Long，为了代码的通用，所以在这里我使用了Number。

ArrayHandler

ArrayHandler会返回一个数组，用于将结果集第一行数据转换为数组。

@Test
public void queryByArrayHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   Object[] results = runner.query(conn, "select * from USER_INFO",
         new ArrayHandler());
   System.out.println(Arrays.asList(results));
}

ArrayListHandler

ArrayListHandler会返回一个集合，集合中的每一项对应结果集指定行中的数据转换后的数组。

@Test
public void queryByArrayListHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   List<Object[]> results = runner.query(conn, "select * from USER_INFO",
         new ArrayListHandler());
   for (Object[] object : results)
   {
      System.out.println(Arrays.asList(object));
   }
}

KeyedHandler

KeyedHandler会返回一个Map，我们可以指定某一列的值作为该Map的键，Map中的值为对应行数据转换的键值对，键为列名。

@Test
public void queryByKeyedHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   Map<String, Map<String, Object>> results = runner.query(conn,
         "select * from USER_INFO", new KeyedHandler<String>());
   System.out.println(results);
}

ColumnListHandler

ColumnListHandler会返回一个集合，集合中的数据为结果集中指定列的数据。

@Test
public void queryByColumnListHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   List<String> results = runner.query(conn, "select * from USER_INFO",
         new ColumnListHandler<String>());
   System.out.println(results);
}

MapHandler

MapHandler会将结果集中第一行数据转换为键值对，键为列名。

@Test
public void queryByMapHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   Map<String, Object> results = runner.query(conn,
         "select * from USER_INFO", new MapHandler());
   System.out.println(results);
}

MapListHandler

MapHandler会将结果集中的数据转换为一个集合，集合中的数据为对应行转换的键值对，键为列名

@Test
public void queryByMapListHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   List<Map<String, Object>> results = runner.query(conn,
         "select * from USER_INFO", new MapListHandler());
   System.out.println(results);
}

BeanHandler

BeanHandler实现了将结果集第一行数据转换为Bean对象，在实际应用中非常方便。

在编写测试代码之前，我们先来编写一个对应的Bean类：

import java.text.MessageFormat;

public class UserInfo
{
   private String userId;
   private String userName;

   public String getUserId()
   {
      return userId;
   }

   public void setUserId(String userId)
   {
      this.userId = userId;
   }

   public String getUserName()
   {
      return userName;
   }

   public void setUserName(String userName)
   {
      this.userName = userName;
   }

   @Override
   public String toString()
   {
      return MessageFormat
            .format("[userId:{0},userName:{1}]", userId, userName);
   }
}

接下来，我们来编写测试代码：

@Test
public void queryByBeanHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   UserInfo results = runner.query(conn, "select * from USER_INFO",
         new BeanHandler<UserInfo>(UserInfo.class));
   System.out.println(results);
}

BeanListHandler

BeanHandler只转换结果集的第一行，而BeanListHandler会将结果集的所有行进行转换，返回一个集合。

@Test
public void queryByBeanListHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   List<UserInfo> results = runner.query(conn, "select * from USER_INFO",
         new BeanListHandler<UserInfo>(UserInfo.class));
   System.out.println(results);
}

BeanMapHandler

BeanMapHandler也会将结果集转换为Bean对象，不过返回的是已指定列的值作为键的键值对。

@Test
public void queryByBeanMapHandler() throws SQLException
{
   printCurrentMethodName();
   QueryRunner runner = new QueryRunner();
   Map<String, UserInfo> results = runner.query(conn,
         "select * from USER_INFO", new BeanMapHandler<String, UserInfo>(
               UserInfo.class));
   System.out.println(results);
}

完整测试代码

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.BeanMapHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.KeyedHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.junit.After;
import org.junit.Before;

public class DBUtilsTest
{
   private Connection conn = null;

   @Before
   public void initConnection() throws SQLException, ClassNotFoundException
   {
      printCurrentMethodName();
      Class.forName("org.h2.Driver");
      conn = DriverManager.getConnection("jdbc:h2:h2.db", "test", "123");
   }

   @Before
   public void initDatabase() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      runner.update(
            conn,
            "CREATE TABLE IF NOT EXISTS USER_INFO (userId VARCHAR(20) PRIMARY KEY, userName VARCHAR(50))");
   }

   public void update() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      String suffix = Long.toHexString(System.currentTimeMillis());
      Integer result = runner.update(conn,
            "insert into USER_INFO(userId, userName) values(?, ?)", suffix,
            "name" + suffix);
      System.out.println("受影响记录条数：" + result);
   }

   public void queryByScalarHandler() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      Number number = runner.query(conn, "select count(*) from USER_INFO",
            new ScalarHandler<Number>());
      System.out.println("总记录记录条数：" + number.intValue());
   }

   public void queryByArrayHandler() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      Object[] results = runner.query(conn, "select * from USER_INFO",
            new ArrayHandler());
      System.out.println(Arrays.asList(results));
   }

   public void queryByArrayListHandler() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      List<Object[]> results = runner.query(conn, "select * from USER_INFO",
            new ArrayListHandler());
      for (Object[] object : results)
      {
         System.out.println(Arrays.asList(object));
      }
   }

   public void queryByKeyedHandler() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      Map<String, Map<String, Object>> results = runner.query(conn,
            "select * from USER_INFO", new KeyedHandler<String>());
      System.out.println(results);
   }

   public void queryByColumnListHandler() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      List<String> results = runner.query(conn, "select * from USER_INFO",
            new ColumnListHandler<String>());
      System.out.println(results);
   }

   public void queryByMapHandler() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      Map<String, Object> results = runner.query(conn,
            "select * from USER_INFO", new MapHandler());
      System.out.println(results);
   }

   public void queryByMapListHandler() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      List<Map<String, Object>> results = runner.query(conn,
            "select * from USER_INFO", new MapListHandler());
      System.out.println(results);
   }

   public void queryByBeanHandler() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      UserInfo results = runner.query(conn, "select * from USER_INFO",
            new BeanHandler<UserInfo>(UserInfo.class));
      System.out.println(results);
   }

   public void queryByBeanListHandler() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      List<UserInfo> results = runner.query(conn, "select * from USER_INFO",
            new BeanListHandler<UserInfo>(UserInfo.class));
      System.out.println(results);
   }

   public void queryByBeanMapHandler() throws SQLException
   {
      printCurrentMethodName();
      QueryRunner runner = new QueryRunner();
      Map<String, UserInfo> results = runner.query(conn,
            "select * from USER_INFO", new BeanMapHandler<String, UserInfo>(
                  UserInfo.class));
      System.out.println(results);
   }

   @After
   public void destory()
   {
      printCurrentMethodName();
      DbUtils.closeQuietly(conn);
   }

   /**
    * 打印当前运行方法名称
    */
   public void printCurrentMethodName()
   {
      System.out.println(Thread.currentThread().getStackTrace()[2]
            .getMethodName());
      System.out.println("==================================================");
   }
}