dbutils的基本使用方法

我这里使用到了c3p0的连接池，可以参考这个链接：c3p0的基本使用方法

dbutils只是一个工具类，如果不使用hibernate这种大型的框架，可以考虑使用.。因为他可以大大的简化我们的代码

jar包可以到官网下载，也可以在这里下载：Java连接数据库的工具类

主要使用到QueryRunner、ResultSetHandler<T>这两个类

而QueryRunner主要使用到的是

1.query方法，是用来做查询的

2.update方法，用来做增删改操作

3.batch方法，用来做批处理

而ResultSetHandley则是可以理解为接收结果的

下面直接上代码

package cn.bl.v4_DataSource.DBUtils;

import java.util.List;
import java.util.Map;

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

import cn.bl.bean.Stud;
import cn.bl.v4_DataSource.c3p0.C3P0Utils;

public class Demo1 {
	
	//查询 - 将结果封装为List<Map<String,Object>>
	@Test
	public void testQuery1() throws Exception {
		QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
		List<Map<String, Object>>list = runner.query(" select * from car ",new MapListHandler() );
		System.out.println(list);
		//[{price=23456.22, cname=奔驰, pid=1, id=1}, {price=23456.22, cname=马萨拉蒂, pid=2, id=2}, {price=23456.22, cname=法拉利, pid=3, id=3}, {price=23456.22, cname=劳斯莱斯, pid=4, id=4}, {price=23456.22, cname=三菱, pid=1, id=5}, {price=23456.22, cname=丰田, pid=2, id=6}, {price=23456.22, cname=本田, pid=2, id=7}, {price=23456.22, cname=QQ, pid=4, id=8}, {price=12.0, cname=迈巴赫, pid=3, id=11}]
	}
	
	//查询 - 封装为一个List<Student>
	@Test
	public void testQuery2() throws Exception {
		QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
		List<Stud> list = runner.query(" select * from stud", new BeanListHandler<>(Stud.class));
		System.out.println(list);
		//[Stud [id=1, name=xlh, age=12, sex=0], Stud [id=2, name=xlhh, age=22, sex=0], Stud [id=3, name=阿斯蒂芬, age=30, sex=2], Stud [id=5, name=alice, age=20, sex=0], Stud [id=4, name=雷神, age=23, sex=0], Stud [id=23, name=雷神啊, age=21, sex=1], Stud [id=14, name=电神, age=12, sex=1], Stud [id=24, name=神仙, age=23, sex=1], Stud [id=39, name=小梨花, age=17, sex=1], Stud [id=50, name=张飞, age=30, sex=1], Stud [id=49, name=刘备, age=34, sex=1], Stud [id=52, name=张飞, age=30, sex=1], Stud [id=53, name=张飞, age=30, sex=1]]
	}
}

package cn.bl.v4_DataSource.DBUtils;

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

import org.apache.commons.dbutils.QueryRunner;
import org.junit.Test;

import cn.bl.v4_DataSource.c3p0.C3P0Utils;

public class Demo2 {

	@Test 
	public void testUpdate() throws Exception {
		QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
		int i = runner.update(" insert into stud(id,name,age,sex) values(50,'张飞',30,'1')");
		System.out.println(i);
		
		i = runner.update(" insert into stud(id,name,age,sex) values(?,?,?,?)", 49,"刘备",34,"1");
		System.out.println(i);
	}
	
	@Test
	public void testBatch() throws SQLException {
		QueryRunner runner = new QueryRunner(C3P0Utils.getDataSource());
		Random r = new Random();
		for(int i = 1;i<=100;i++) {
			Object[][]objects = new Object[][]{
					{"Tom"+i,r.nextInt(20)+10},
					{"小李子"+i,r.nextInt(20)+10},
					{"Nancy"+i,r.nextInt(20)+10}
			};
			runner.batch(" insert into student(sname,age) values(?,?)",objects);
		}
	}
	
	/*
	 * 测试事务处理
	 */
	@Test
	public void testTx() {
		QueryRunner runner = new QueryRunner();
		Connection conn = C3P0Utils.getConnection();
		try {
			conn.setAutoCommit(false);
			String sql = " insert into stud(id,name,age,sex) values(52,'张飞',30,'1') ";
			runner.update(conn, sql);
			sql = " insert into stud(id,name,age,sex) values(53,'张飞',30,'1') ";
			runner.update(conn, sql);
			conn.commit();
			System.out.println("事务提交了...");
		} catch (SQLException e) {
			System.out.println("事务回滚...");
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			e.printStackTrace();
		} finally {
			try {
				conn.setAutoCommit(true);
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}