一、前言

本项目基于git项目地址：https://github.com/yangchangyong0/unitTest 点击打开链接(项目关联的其他附带东西，以提供测试，里面有initsql，执行sql 即是可以)

由于最近公司要求单元测试，达到指定覆盖率，所以最近可以写写单元测试，为了自己和部门能够达标，所以单侧很重要了哦。这一篇主要讲解跟jdbc一个很像，但是很轻量级的工具类。主要用于连接数据库，进行一下操作。

二、dbutil

2.1 引入依赖

   <!--dbutil-->
        <dependency>
            <groupId>org.hamcrest</groupId>
            <artifactId>hamcrest-all</artifactId>
            <version>1.3</version>
        </dependency>
        <dependency>
            <groupId>commons-dbutils</groupId>
            <artifactId>commons-dbutils</artifactId>
            <version>1.6</version>
        </dependency>
        <dependency>
            <groupId>org.mockito</groupId>
            <artifactId>mockito-core</artifactId>
            <version>1.9.5</version>
        </dependency>

2.2 dbutil核心

dbutil主要有两个核心类，根据官方提供资料，不必要关心其他类，因为人家只是简单封装，有兴趣点击源码查看。主要有下面两个类

ResultSetHandler：主要的请求类型
QueryRunner：主要得查询器

2.3 封装ResultSetHandler

AdDbutilHandler类，主要是对请求返回参数的封装，我只是小试牛刀用object和javabean

package test.dbutil;

import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

/**
 * Created by ycy on 16/7/30.
 */
  public class AdDbutilHandler<T> {
  private Logger logger = LoggerFactory.getLogger(this.getClass());

  /**
   * 获取obj[]类型ResultSetHandler.
   */
  public ResultSetHandler<Object[]> getObjHandel() {
    ResultSetHandler<Object[]> handel = new ResultSetHandler<Object[]>() {
      public Object[] handle(ResultSet rs) throws SQLException {
        if (!rs.next()) {
          return null;
        }

        ResultSetMetaData meta = rs.getMetaData();
        int cols = meta.getColumnCount();
        Object[] result = new Object[cols];

        for (int i = 0; i < cols; i++) {
          result[i] = rs.getObject(i + 1);
        }

        return result;
      }
    };
    return handel;
  }

  /**
   * 获取obj[]类型ResultSetHandler.
   */
  public ResultSetHandler<Object[]> getMapHandel() {
    ResultSetHandler<Object[]> handel = new ResultSetHandler<Object[]>() {
      public Object[] handle(ResultSet rs) throws SQLException {
        if (!rs.next()) {
          return null;
        }

        ResultSetMetaData meta = rs.getMetaData();
        int cols = meta.getColumnCount();
        Object[] result = new Object[cols];

        for (int i = 0; i < cols; i++) {
          result[i] = rs.getObject(i + 1);
        }

        return result;
      }
    };
    return handel;
  }

  /**
   * 获取javabean类型的ResultSetHandler.
   */
  public ResultSetHandler<List<T>> getJavaBeanHandel(Class clazz) {

    ResultSetHandler<List<T>> handelClass = null;
    try {
      handelClass = new BeanListHandler<T>((Class<T>) clazz.newInstance().getClass());
    } catch (InstantiationException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
    } catch (IllegalAccessException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
      e.getStackTrace();
    }
    return handelClass;
  }


}

2.4 封装查询接口

主要使用sql语句进行查询，也可以使用实体查询，但是想主要使用sql，方便单元测试，不是很喜欢hql。

AdDbUtil

package test.dbutil;

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.MapHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

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

import javax.sql.DataSource;

/**
 * Created by ycy on 16/7/30.
 */
public class AdDbUtil<T> {
  private Logger logger = LoggerFactory.getLogger(this.getClass());

  /**
   * 获取obj[]对象
   *
   * @param sql
   * @param params
   * @return
   */
  public Object[] queryObjs(DataSource ds, String sql, Object... params) {
    AdDbutilHandler adDbUtil = new AdDbutilHandler();
    ResultSetHandler<Object[]> objHandel = adDbUtil.getObjHandel();
    QueryRunner run = new QueryRunner(ds);
    try {
      Object[] result = run.query(sql, objHandel, params);
      return result;
    } catch (SQLException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
    }
    return null;
  }

  /**
   *获取Map<String,Object> 对象
   * @param ds
   * @param sql
   * @param params
     * @return
     */
  public Map<String,Object> queryMaps(DataSource ds, String sql, Object... params) {
    AdDbutilHandler adDbUtil = new AdDbutilHandler();
    ResultSetHandler<Object[]> objHandel = adDbUtil.getObjHandel();
    QueryRunner run = new QueryRunner(ds);
    try {
      Map<String,Object>  result = run.query(sql, new MapHandler(), params);
      return result;
    } catch (SQLException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
    }
    return null;
  }
  /**
   * 获取javabean对象List
   * 
   * @param ds
   * @param clazz
   * @param sql
   * @param params
   * @return
   */
  public List<T> queryJavaBeanList(DataSource ds, Class clazz, String sql, Object... params) {
    try {
      ResultSetHandler<List<T>> handelClass = null;
      handelClass = new BeanListHandler<T>((Class<T>) clazz.newInstance().getClass());
      QueryRunner run = new QueryRunner(ds);
      List<T> list = run.query(sql, handelClass, params);
      return list;
    } catch (InstantiationException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
    } catch (IllegalAccessException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
    } catch (SQLException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
    }
    return null;
  }


  /**
   * 获取javabean对象
   * @param ds
   * @param clazz
   * @param sql
   * @param params
     * @return
     */
  public T queryJavaBean(DataSource ds, Class clazz, String sql, Object... params) {
    try {
      ResultSetHandler<List<T>> handelClass = null;
      handelClass = new BeanListHandler<T>((Class<T>) clazz.newInstance().getClass());
      QueryRunner run = new QueryRunner(ds);
      T bean =  run.query(sql, new BeanHandler<T>((Class<T>) clazz.newInstance().getClass()), params);
      return bean;
    } catch (InstantiationException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
    } catch (IllegalAccessException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
    } catch (SQLException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
    }
    return null;
  }
  /**
   *更新数据
   * @param ds
   * @param sql
   * @param params
     * @return
     */
  public int update(DataSource ds, String sql, Object... params) {
    try {
      QueryRunner run = new QueryRunner(ds);
      int num = run.update(sql, params);
      return num;
    } catch (SQLException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
    }
    return 0;
  }

  /**
   * 插入数据
   * @param ds
   * @param sql
   * @param params
     * @return
     */
  public Object[] insert(DataSource ds, String sql, Object... params){
    try {
      AdDbutilHandler adDbUtil = new AdDbutilHandler();
      ResultSetHandler<Object[]> objHandel = adDbUtil.getObjHandel();
      QueryRunner run = new QueryRunner(ds);
      Object[] result  = run.insert(sql,objHandel ,params);
      return result;
    } catch (SQLException e) {
      StringBuilder sb = new StringBuilder();
      sb.append(e.getMessage());
      StackTraceElement[] stackTraceElements = e.getStackTrace();
      for (StackTraceElement sta : stackTraceElements) {
        sb.append(sta);
      }
      logger.error(sb.toString());
      e.getStackTrace();
    }
    return null;
  }

}

2.5 测试

对主要的几种查询与据封装测试，我们实际用的时候就这么查询

package test.dbutil;


import com.alibaba.druid.pool.DruidDataSource;
import com.ycy.center.dao.entity.YcyTable;

import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.TestExecutionListeners;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.test.context.support.DependencyInjectionTestExecutionListener;
import org.springframework.test.context.transaction.TransactionalTestExecutionListener;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * Created by ycy on 16/7/30.
 */
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(locations = "classpath:/applicationContext-test.xml")
@TestExecutionListeners({DependencyInjectionTestExecutionListener.class,
    TransactionalTestExecutionListener.class})
@Transactional
public class TestDbutilDemo {
  @Autowired
  private DruidDataSource dataSource;



  /**
   * 获取object对象
   */
  @Test
  public void testObject() {
    AdDbUtil adDbUtilManager = new AdDbUtil();
    String sql = "SELECT * FROM ycy_table WHERE name=?";
    Object[] result = adDbUtilManager.queryObjs(dataSource, sql, "0姓名");
    System.out.println(result);
  }

  /**
   * 获取javabean对象
   */
  @Test
  public void testqueryJavaBeanList() {
    AdDbUtil adDbUtilManager = new AdDbUtil();
    String sql = "SELECT * FROM ycy_table WHERE name=?";
    List<YcyTable> list =
        adDbUtilManager.queryJavaBeanList(dataSource, YcyTable.class, sql, "我是新增语句");
    System.out.println(list);
  }

  /**
   * 获取javabean对象List
   */
  @Test
  public void testqueryJavaBean() {
    AdDbUtil adDbUtilManager = new AdDbUtil();
    String sql = "SELECT * FROM ycy_table WHERE id=?";
    YcyTable ycyTable =
        (YcyTable) adDbUtilManager.queryJavaBean(dataSource, YcyTable.class, sql, 6);
    System.out.println(ycyTable);
  }

  /**
   * 执行更新语句
   */
  @Test
  public void testupdate() {
    AdDbUtil adDbUtilManager = new AdDbUtil();
    String sql = "update ycy_table set name=? where `id`=?";
    int i = adDbUtilManager.update(dataSource, sql, "我是更新后的名字", 46);
    System.out.println(i);
  }

  /**
   * 执行新增语句
   */
  @Test
  public void testInsert() {
    for (int i = 0; i < 8; i++) {
      AdDbUtil adDbUtilManager = new AdDbUtil();
      String sql = "insert into ycy_table (name,num) values (?,?)";
      Object[] objects = adDbUtilManager.insert(dataSource, sql, "我是新增语句", i);
      System.out.println(objects);
    }

  }
}

3、总结

本文主要是用一些jdbc的连接，比jdbc好用，而且实际用处还是比较好的，可能用的地方比较少，但是在项目里面作为工具类，需要测试的时候还是将就。只是为大家提供工具而已。下载项目就可以使用。

单元测试(一) dbutil篇

一、前言

二、dbutil

2.1 引入依赖

2.2 dbutil核心

2.3 封装ResultSetHandler

2.4 封装查询接口

2.5 测试

3、总结

https://github.com/yangchangyong0/unitTest点击打开链接

热门文章

最新文章

相关电子书