一.什么是DbUtils組件
Commons DbUtils是Apache組織提供的一個(gè)對(duì)JDBC進(jìn)行簡(jiǎn)單封裝的開(kāi)源工具類庫(kù)柑贞，使用它能夠簡(jiǎn)化JDBC應(yīng)用程序的開(kāi)發(fā)，同時(shí)也不會(huì)影響程序的性能琳彩。
下載組件，引入jar文件：commons-dbutils-1.6.jar 密碼：tygh
maven環(huán)境配置：
<dependency>
<groupId>commons-dbutils</groupId>
<artifactId>commons-dbutils</artifactId>
<version>1.6</version>
</dependency>
二.核心類QueryRunner
帶有Connection的
Int update(Connection conn, String sql, Object param);執(zhí)行更新帶一個(gè)占位符的sql
Int update(Connection conn, String sql, Object… param);執(zhí)行更新帶多個(gè)占位符的sql
Int[] batch(Connection conn, String sql, Object[][] params) 批處理
T query(Connection conn ,String sql, ResultSetHandler<T> rsh, Object... params) 查詢方法
不帶有Connection的
Int update( String sql, Object param); 執(zhí)行更新帶一個(gè)占位符的sql
Int update( String sql, Object… param);執(zhí)行更新帶多個(gè)占位符的sql
Int[] batch( String sql, Object[][] params); 批處理
注意： 如果調(diào)用DbUtils組件的操作數(shù)據(jù)庫(kù)方法，沒(méi)有傳入連接對(duì)象甜无，那么在實(shí)例化QueryRunner對(duì)象的時(shí)候需要傳入數(shù)據(jù)源對(duì)象： QueryRunner qr = new QueryRunner(DataSource ds);
1.更新操作（包括delete 、insert哥遮、 update）
例1：
@Test
public void delete() throws Exception {
String sql = " delete from car where id =? ";
conn = ConnUtil.getConnextion();
// 創(chuàng)建DbUtils核心工具類對(duì)象
QueryRunner qr = new QueryRunner();
qr.update(conn, sql, 3);
DbUtils.close(conn);
}
結(jié)果測(cè)試
例2：
public void save(CarBean carBean)throws Exception {
String sql = "INSERT INTO car (carname,carEntity) VALUES(?,?); " ;
conn = ConnUtil.getConnextion();
QueryRunner qr = new QueryRunner();
// 批量插入
qr.batch(conn, sql, new Object[][]{ {"寶馬","2017"},{"奔馳","2017"} });
// 關(guān)閉
conn.close();
}
image.png
2.查詢操作
DbUtils提供的封裝結(jié)果的一些對(duì)象：
1）BeanHandler: 查詢返回單個(gè)對(duì)象
2） BeanListHandler: 查詢返回list集合岂丘，集合元素是指定的對(duì)象
- ArrayHandler, 查詢返回結(jié)果記錄的第一行，封裝對(duì)對(duì)象數(shù)組, 即返回：Object[]
- ArrayListHandler, 把查詢的每一行都封裝為對(duì)象數(shù)組眠饮，再添加到list集合中
- ScalarHandler 查詢返回結(jié)果記錄的第一行的第一列 (在聚合函數(shù)統(tǒng)計(jì)的時(shí)候用)
- MapHandler 查詢返回結(jié)果的第一條記錄封裝為map
- MapListHandler 查詢返回結(jié)果封裝為L(zhǎng)ist<map>
-
a.自定義結(jié)果集封裝數(shù)據(jù)
@Test
public void carFind() throws Exception {
String sql = " select * from car where id = ? ";
conn = ConnUtil.getConnextion();
QueryRunner qr = new QueryRunner();
//查詢
CarBean carBean= qr.query(conn, sql, new ResultSetHandler<CarBean>() {
//自定義 如何封裝一個(gè)CarBean對(duì)象
public CarBean handle(ResultSet resultSet) throws SQLException {
if(resultSet.next()) {
CarBean carBean = new CarBean();
carBean.setId(resultSet.getInt("id"));
carBean.setCarname(resultSet.getString("carname"));
carBean.setCarname(resultSet.getString("carEntity"));
return carBean;
}
return null;
}
},1);
System.out.println(carBean.getId()+""+carBean.getCarname()+""+carBean.getCarEntity());
}
-
b.查詢使用組件提供的BeanHandler結(jié)果集對(duì)象封裝數(shù)據(jù)
@Test
public void carFind() throws Exception {
String sql = " select * from car where id = ? ";
conn = ConnUtil.getConnextion();
QueryRunner qr = new QueryRunner();
//查詢
CarBean carBean = qr.query(conn,sql,new BeanHandler<CarBean>(CarBean.class),2);
System.out.println(carBean.getId()+" "+carBean.getCarname()+" "+carBean.getCarEntity());
conn.close();
}
-
c.查詢使用組件提供的BeanListHandler結(jié)果集對(duì)象封裝數(shù)據(jù)
@Test
public void carFind() throws Exception {
String sql = " select * from car where id = ? ";
conn = ConnUtil.getConnextion();
QueryRunner qr = new QueryRunner();
//查詢
List <CarBean> carBean = qr.query(conn,sql,new BeanListHandler<CarBean>(CarBean.class),4);
System.out.println(carBean.get(0).getId()+" "+carBean.get(0).getCarname()+" "+carBean.get(0).getCarEntity());
conn.close();
}
-
d.查詢使用組件提供的MapHandler 結(jié)果集對(duì)象封裝數(shù)據(jù)
@Test
public void carFind() throws Exception {
String sql = " select * from car";
conn = ConnUtil.getConnextion();
QueryRunner qr = new QueryRunner();
//查詢
Map<String,Object> carBean = qr.query(conn,sql,new MapHandler());
System.out.println(carBean);
conn.close();
}
image.png
-
e.查詢使用組件提供的ScalarHandler 結(jié)果集對(duì)象封裝數(shù)據(jù)(在聚合函數(shù)統(tǒng)計(jì)時(shí)候用)
@Test
public void carFind() throws Exception {
String sql = " select count(*) from car";
conn = ConnUtil.getConnextion();
QueryRunner qr = new QueryRunner();
//查詢
Long carBean = qr.query(conn,sql,new ScalarHandler<Long>());
System.out.println(carBean);
conn.close();
}
