数据库dbutils
common-dbutils.jar
QueryRunner
update方法:
* int update(String sql, Object... params) --> 可执行增、删、改语句
* int update(Connection con, String sql, Object... parmas) --> 需要调用者提供Connection,这说明本方法不再管理Connection了。支持事务!
query方法:
* T query(String sql, ResultSetHandler rsh, Object... params) --> 可执行查询
> 它会先得到ResultSet,然后调用rsh的handle()把rs转换成需要的类型!
* T query(Connection con, String sql, ResultSetHadler rsh, Object... params),支持事务
ResultSetHandler接口:
* BeanHandler(单行) --> 构造器需要一个Class类型的参数,用来把一行结果转换成指定类型的javaBean对象
* BeanListHandler(多行) --> 构造器也是需要一个Class类型的参数,用来把一行结果集转换成一个javabean,那么多行就是转换成List对象,一堆javabean
* MapHandler(单行) --> 把一行结果集转换Map对象
> 一行记录:
sid sname age gender
1001 zs 99 male
> 一个Map:
{sid:1001, sname:zs, age:99, gender:male}
* MapListHandler(多行) --> 把一行记录转换成一个Map,多行就是多个Map,即List<Map>!
* ScalarHandler(单行单列) --> 通常用与select count(*) from t_stu语句!结果集是单行单列的!它返回一个Object
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
代码:
public void fun1() throws SQLException { QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource()); String sql = "insert into t_stu values(?,?,?,?)"; Object[] params = {1002, "liSi", 88, "female"}; qr.update(sql, params); } @Test public void fun2() throws SQLException { // 创建QueryRunner,需要提供数据库连接池对象 QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource()); // 给出sql模板 String sql = "select * from t_stu where sid=?"; // 给出参数 Object[] params = {1001}; // 执行query()方法,需要给出结果集处理器,即ResultSetHandler的实现类对象 // 我们给的是BeanHandler,它实现了ResultSetHandler // 它需要一个类型,然后它会把rs中的数据封装到指定类型的javabean对象中,然后返回javabean Stu stu = qr.query(sql, new BeanHandler<Stu>(Stu.class), params); System.out.println(stu); } /** * BeanListHandler的应用,它是多行处理器 * 每行对象一个Stu对象! * @throws Exception */ @Test public void fun3() throws Exception { QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource()); String sql = "select * from t_stu"; List<Stu> stuList = qr.query(sql, new BeanListHandler<Stu>(Stu.class)); System.out.println(stuList); } /** * MapHandler的应用,它是单行处理器,把一行转换成一个Map对象 * @throws SQLException */ @Test public void fun4() throws SQLException { QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource()); String sql = "select * from t_stu where sid=?"; Object[] params = {1001}; Map map = qr.query(sql, new MapHandler(), params); System.out.println(map); } /** * MapListHandler,它是多行处理器,把每行都转换成一个Map,即List<Map> * @throws SQLException */ @Test public void fun5() throws SQLException { QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource()); String sql = "select * from t_stu"; List<Map<String,Object>> mapList = qr.query(sql, new MapListHandler()); System.out.println(mapList); } /** * ScalarHandler,它是单行单列时使用,最为合适! * @throws SQLException */ @Test public void fun6() throws SQLException { QueryRunner qr = new QueryRunner(JdbcUtils.getDataSource()); String sql = "select count(*) from t_stu"; /* * Integer、Long、BigInteger */ Number cnt = (Number)qr.query(sql, new ScalarHandler()); long c = cnt.longValue(); System.out.println(c); } }
------------------------------------------------------------------------------------------------------------------------------------------------------------------
增、删、改
public void fun1() throws SQLException {
QueryRunner qr = new QueryRunner();
String sql = "insert into user values(?,?,?)";
qr.update(JdbcUtils.getConnection(), sql, "u1", "zhangSan", "123");
}
---------------------------------------------------------------------------------------------------------------------------------------------------------------
查
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "select * from tab_student";
// 把结果集转换成Bean
Student stu = qr.query(sql, new BeanHandler<Student>(Student.class));
// 把结果集转换成Bean的List
List<Student> list = qr.query(sql, new BeanListHandler<Student>(Student.class));
// 把结果集转换成Map
Map<String,Object> map = qr.query(sql, new MapHandler());
// 把结果集转换成List<Map>
List<Map<String,Object>> list = qr.query(sql, new MapListHandler() );
// 把结果集转换成一列的List
List<Object> list = qr.query(sql, new ColumnListHandler("name")) ;
// 把结果转换成单行单列的值
Number number = (Number)qr.query(sql, new ScalarHandler());
批处理
DataSource ds = JdbcUtils.getDataSource();
QueryRunner qr = new QueryRunner(ds);
String sql = "insert into tab_student values(?,?,?,?)";
Object[][] params = new Object[10][]; //表示 要插入10行记录
for(int i = 0; i < params.length; i++) {
params[i] = new Object[]{"S_300" + i, "name" + i, 30 + i, i%2==0?"男":"女"};
}
qr.batch (sql, params);
-