一、前言
本项目基于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好用，而且实际用处还是比较好的，可能用的地方比较少，但是在项目里面作为工具类，需要测试的时候还是将就。只是为大家提供工具而已。下载项目就可以使用。
