dbutils是怎么使用
dbutils只是一个工具类,主要使用到QueryRunner、ResultSetHandler这两个类。
而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();
}
}
}
}