DBUtils使用详细示例
2020-09-13 18:37:59
25次阅读
0个评论
DBUtils 使用方便,上手很快,是个不错的选择。

直接上代码:
【使用示例】
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import junit.framework.TestCase;
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.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import com.exam.db.DbManager;
import com.exam.util.BasicRowProcessorEx;

public class TestSomething extends TestCase {
public void testDBUtilSelect() {
Connection conn = DbManager.getInstance().getConnection();

QueryRunner queryRunner = new QueryRunner();

try {
// 返回单行记录,使用Map
System.out.println("使用Map处理单行记录!");
Map<String, Object> map = queryRunner.query(conn,
"select * from tab where rownum=1", new MapHandler(),
(Object[]) null);

for (Iterator<Entry<String, Object>> i = map.entrySet().iterator(); i
.hasNext();) {
Entry<String, Object> e = i.next();
System.out.println(e.getKey() + "=" + e.getValue());
}

System.out.println("处理多行记录!");
List<Map<String, Object>> list = queryRunner.query(conn,
"select * from tab where rownum<=3", new MapListHandler(),
(Object[]) null);

for (Iterator<Map<String, Object>> li = list.iterator(); li
.hasNext();) {
System.out.println("--------------");
Map<String, Object> m = li.next();
for (Iterator<Entry<String, Object>> mi = m.entrySet()
.iterator(); mi.hasNext();) {
Entry<String, Object> e = mi.next();
System.out.println(e.getKey() + "=" + e.getValue());
}
}

System.out.println("使用Bean处理单行记录!");

// com.exam.test.TestSomething.Tab
Tab tab = queryRunner.query(conn,
"select tname from tab where rownum=1",
new BeanHandler<Tab>(Tab.class));
System.out.println("tname=" + tab.getTname());
System.out.println("tabtype=" + tab.getTabtype());

System.out.println("使用Array处理单行记录!");
Object[] array = queryRunner.query(conn,
"select * from tab where rownum=1", new ArrayHandler());

for (int i = 0; i < array.length; i++) {
System.out.println(array[i]);
}

System.out.println("使用Array处理多行记录!");
List<Object[]> arraylist = queryRunner
.query(conn, "select * from tab where rownum<=3",
new ArrayListHandler());

for (Iterator<Object[]> itr = arraylist.iterator(); itr.hasNext();) {
Object[] a = itr.next();
System.out.println("--------------");
for (int i = 0; i < a.length; i++) {
System.out.println(a[i]);

}
}

System.out.println("使用ColumnListHandler处理单行记录,返回其中指定的一列!");
List<Object> colList = queryRunner.query(conn,
"select * from tab where rownum=1", new ColumnListHandler(
"tname"));
for (Iterator<Object> itr = colList.iterator(); itr.hasNext();) {
System.out.println(itr.next());
}

System.out
.println("使用ScalarHandler处理单行记录,只返回结果集第一行中的指定字段,如未指定字段,则返回第一个字段!");
Object scalar1 = queryRunner.query(conn, "select * from tab",
new ScalarHandler("tname"));
System.out.println(scalar1);
Object scalar2 = queryRunner.query(conn,
"select tname,tabtype from tab", new ScalarHandler());
System.out.println(scalar2);

// 使用自定义的行处理器
// Map中的KEY可按输入顺序输出
System.out.println("使用Map处理单行记录(使用自定义行处理器)!");
Map<String, Object> linkedmap = queryRunner
.query(
conn,
"select tabtype,tname,'wallimn' as programmer from tab where rownum=1",
new MapHandler(new BasicRowProcessorEx()),
(Object[]) null);

for (Iterator<Entry<String, Object>> i = linkedmap.entrySet()
.iterator(); i.hasNext();) {
Entry<String, Object> e = i.next();
System.out.println(e.getKey() + "=" + e.getValue());
}

// 使用自定义的行处理器
// Map中的KEY可按输入顺序输出
System.out.println("处理多行记录(使用自定义行处理器)!");
List<Map<String, Object>> listLinedMap = queryRunner
.query(
conn,
"select tabtype,tname,'wallimn' as programmer from tab where rownum<=3",
new MapListHandler(new BasicRowProcessorEx()),
(Object[]) null);

for (Iterator<Map<String, Object>> li = listLinedMap.iterator(); li
.hasNext();) {
System.out.println("--------------");
Map<String, Object> m = li.next();
for (Iterator<Entry<String, Object>> mi = m.entrySet()
.iterator(); mi.hasNext();) {
Entry<String, Object> e = mi.next();
System.out.println(e.getKey() + "=" + e.getValue());
}
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DbUtils.closeQuietly(conn);
}

public void testDBUtilInsertDeleteUpdateSelect() {
// 建一个简单的测试表,建表脚本如下
// create table T_DBUTILTEST(
// id integer,
// name varchar2(255)
// );
Connection conn = DbManager.getInstance().getConnection();

QueryRunner queryRunner = new QueryRunner(true);

try {
queryRunner.update(conn, "delete from T_DBUTILTEST");
// queryRunner.update(conn, "truncate table T_DBUTILTEST");
// 插一条
for (int i = 0; i < 10; i++) {
queryRunner.update(conn,
"insert into T_DBUTILTEST (id,name) values (?,?)", i,
"http://wallimn.iteye.com");
}

// 再插多条
queryRunner.batch(conn,
"insert into T_DBUTILTEST (id,name) values (?,?)",
new Object[][] { { 11, "batch:wallimn@sohu.com" },
{ 12, "batch:wallimn@sohu.com" } });

// 删除示例
queryRunner.update(conn, "delete from T_DBUTILTEST where id=1");
queryRunner.update(conn, "delete from T_DBUTILTEST where id=?", 2);
queryRunner.batch(conn, "delete from T_DBUTILTEST where id=?",
new Object[][] { { 3 }, { 4 } });

// 修改示例
queryRunner.update(conn,
"update T_DBUTILTEST set name = ? where id=?", "修改后的新值", 5);

System.out.println("最终结果显示结果");
List<Map<String, Object>> list = queryRunner.query(conn,
"select name,id from T_DBUTILTEST", new MapListHandler(),
(Object[]) null);

for (Iterator<Map<String, Object>> li = list.iterator(); li
.hasNext();) {
System.out.println("--------------");
Map<String, Object> m = li.next();
for (Iterator<Entry<String, Object>> mi = m.entrySet()
.iterator(); mi.hasNext();) {
Entry<String, Object> e = mi.next();
System.out.print(e.getValue());
System.out.print(",");
}
System.out.println();
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
DbUtils.closeQuietly(conn);
}
}

【自定义扩展】
如果用户想要依照存入Map的顺序显示内容,好像不能直接支持。看了看源码,自定义了一个扩展类。用法在前的代码中有示例
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

import org.apache.commons.dbutils.BasicRowProcessor;

/**
* 用于apache的dbutil类的功能改进,当需要Map中的Key能按输入的顺序输出时,使用这个类来进行处理。简单改了一下基类。<br/>
*/
public class BasicRowProcessorEx extends BasicRowProcessor {

private static class CaseInsensitiveLinkedHashMap extends HashMap<String, Object> {
private final Map<String, String> lowerCaseMap = new LinkedHashMap<String, String>();
private static final long serialVersionUID = -2848100435296897392L;

/** {@inheritDoc} */
@Override
public boolean containsKey(Object key) {
Object realKey = lowerCaseMap.get(key.toString().toLowerCase());
return super.containsKey(realKey);
}

/** {@inheritDoc} */
@Override
public Object get(Object key) {
Object realKey = lowerCaseMap.get(key.toString().toLowerCase());
return super.get(realKey);
}

/** {@inheritDoc} */
@Override
public Object put(String key, Object value) {
Object oldKey = lowerCaseMap.put(key.toLowerCase(), key);
Object oldValue = super.remove(oldKey);
super.put(key, value);
return oldValue;
}

/** {@inheritDoc} */
@Override
public void putAll(Map<? extends String, ?> m) {
for (Map.Entry<? extends String, ?> entry : m.entrySet()) {
String key = entry.getKey();
Object value = entry.getValue();
this.put(key, value);
}
}

/** {@inheritDoc} */
@Override
public Object remove(Object key) {
Object realKey = lowerCaseMap.remove(key.toString().toLowerCase());
return super.remove(realKey);
}
}

@Override
public Map<String, Object> toMap(ResultSet rs) throws SQLException {
Map<String, Object> result = new CaseInsensitiveLinkedHashMap();
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();

for (int i = 1; i <= cols; i++) {
result.put(rsmd.getColumnName(i), rs.getObject(i));
}

return result;
}

}
收藏 0 0

登录 后评论。没有帐号? 注册 一个。

小码农

  • 0 回答
  • 0 粉丝
  • 0 关注