一、什么是dbutils
commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库,它是对JDBC的简单封装,学习成本极低,并且使用dbutils能极大简化jdbc编码的工作量,同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。
二、dbutils核心API
1、QueryRunner类
使用QueryRunner实现增删改查
1 package com.dbutils; 2 3 import java.sql.SQLException; 4 import java.util.Date; 5 import java.util.List; 6 7 import org.apache.commons.dbutils.QueryRunner; 8 import org.apache.commons.dbutils.handlers.BeanHandler; 9 import org.apache.commons.dbutils.handlers.BeanListHandler; 10 import org.junit.Test; 11 12 import com.domain.User; 13 import com.utils.JdbcUtils; 14 15 /** 16 * 17 * 使用dbutils完成数据库的增删改查 18 * 19 */ 20 public class Demo1 { 21 // 插入 22 @Test 23 public void insert() throws SQLException { 24 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 25 String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)"; 26 Object params[] = { 1, "aa", "123", "aaa@sina.com", new Date() }; 27 runner.update(sql, params); 28 } 29 30 // 删除 31 @Test 32 public void delete() throws SQLException { 33 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 34 String sql = "delete from users where id=?"; 35 runner.update(sql, 2); 36 } 37 38 // 修改 39 @Test 40 public void update() throws SQLException { 41 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 42 String sql = "update users set email=? where id=?"; 43 Object params[] = { "ffd@sina.com", 2 }; 44 runner.update(sql, params); 45 } 46 47 // 查找单条记录 48 @Test 49 public void find() throws SQLException { 50 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 51 String sql = "select * from users where id=?"; 52 User user = (User) runner.query(sql, 1, new BeanHandler(User.class)); 53 System.out.println(user.getEmail()); 54 } 55 56 // 查找所有记录 57 @Test 58 public void getAll() throws SQLException { 59 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 60 String sql = "select * from users"; 61 List<User> list = (List) runner.query(sql, new BeanListHandler( 62 User.class)); 63 for (User user : list) { 64 System.out.println(user.getEmail()); 65 } 66 } 67 68 // 批处理 69 @Test 70 public void batch() throws SQLException { 71 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 72 String sql = "insert into users(id,name,password,email,birthday) values(?,?,?,?,?)"; 73 74 //三条sql,五个参数 75 Object params[][] = new Object[3][5]; 76 77 for(int i=0;i<params.length;i++){ 78 params[i] = new Object[]{i+1,"aaa"+i,"123","sina.com",new Date()}; 79 } 80 81 runner.batch(sql, params); 82 } 83 84 }
1 public class JdbcUtils { 2 private static DataSource ds; 3 static{ 4 try { 5 Properties prop = new Properties(); 6 InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties"); 7 prop.load(in); 8 9 //创建数据源 10 BasicDataSourceFactory factory = new BasicDataSourceFactory(); 11 ds = factory.createDataSource(prop); 12 } catch (Exception e) { 13 throw new ExceptionInInitializerError(e); 14 } 15 } 16 17 //提供数据源 18 public static DataSource getDataSource(){ 19 return ds; 20 } 21 22 //获取连接 23 public static Connection getConnection() throws SQLException{ 24 return ds.getConnection(); 25 } 26 }
2、ResultSetHandler接口的实现类
ArrayHandler:把结果集中的第一行数据转成对象数组。
ArrayListHandler:把结果集中的每一行数据都转成一个数组,再存放到List中。
BeanHandler:将结果集中的第一行数据封装到一个对应的JavaBean实例中。
BeanListHandler:将结果集中的每一行数据都封装到一个对应的JavaBean实例中,存放到List里。
ColumnListHandler:将结果集中某一列的数据存放到List中。
KeyedHandler(name):将结果集中的每一行数据都封装到一个Map里,再把这些map再存到一个map里,其key为指定的key。
MapHandler:将结果集中的第一行数据封装到一个Map里,key是列名,value就是对应的值。
MapListHandler:将结果集中的每一行数据都封装到一个Map里,然后再存放到List
1 package com.dbutils; 2 import java.sql.SQLException; 3 import java.util.Arrays; 4 import java.util.List; 5 import java.util.Map; 6 7 import javax.swing.JButton; 8 import org.apache.commons.dbutils.QueryRunner; 9 import org.apache.commons.dbutils.handlers.ArrayHandler; 10 import org.apache.commons.dbutils.handlers.ArrayListHandler; 11 import org.apache.commons.dbutils.handlers.ColumnListHandler; 12 import org.apache.commons.dbutils.handlers.KeyedHandler; 13 import org.apache.commons.dbutils.handlers.ScalarHandler; 14 import org.junit.Test; 15 16 import com.utils.JdbcUtils; 17 18 /** 19 * 20 * dbutils结果集 21 * 22 */ 23 public class Demo2 { 24 @Test 25 //把结果的第一行数据封装到数组中 26 public void test1() throws SQLException{ 27 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 28 String sql = "select * from users"; 29 //强转的时候要加[] 30 Object result[] = (Object[]) runner.query(sql, new ArrayHandler()); 31 System.out.println(result[0]); 32 System.out.println(result[1]); 33 } 34 35 @Test 36 //把结果的每行数据封装到数组中,在把数据存放在list集合中 37 public void test2() throws SQLException{ 38 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 39 String sql = "select * from users"; 40 List<Object[]> list = (List) runner.query(sql, new ArrayListHandler()); 41 System.out.println(list); 42 for(Object[] objects : list){ 43 System.out.println(Arrays.toString(objects)); 44 } 45 } 46 47 @Test 48 //把结果集的某一列封装到一个list集合中 49 public void test3() throws SQLException{ 50 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 51 String sql = "select * from users"; 52 53 List list1 = (List) runner.query(sql, new ColumnListHandler()); 54 for(Object object : list1){ 55 System.out.println(object); 56 } 57 58 List list2 = (List) runner.query(sql,new ColumnListHandler("name")); 59 for(Object object : list2){ 60 System.out.println(object); 61 } 62 63 List list3 = (List) runner.query(sql,new ColumnListHandler(4)); 64 for(Object object : list3){ 65 System.out.println(object); 66 } 67 } 68 69 //KeyedHandler(name):将结果集中的每一行数据都封装到一个Map里,再把这些map再存到一个map里,其key为指定的key 70 @Test 71 public void test4() throws SQLException{ 72 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 73 String sql = "select * from users"; 74 Map<Integer,Map<String,Object>> map = (Map) runner.query(sql, new KeyedHandler("id")); 75 for(Map.Entry<Integer, Map<String, Object>> me : map.entrySet()){ 76 int key = me.getKey(); 77 78 //拿到封装每条记录的map 79 for(Map.Entry<String, Object> entry:me.getValue().entrySet()){ 80 System.out.println(entry.getKey()+"="+entry.getValue()); 81 } 82 } 83 } 84 85 86 @Test 87 //获取记录总数 88 public void test5() throws SQLException{ 89 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource()); 90 String sql = "select count(*) from users"; 91 //Object result[] = (Object[]) runner.query(sql, new ArrayHandler()); 92 /*long totalrecord = (Long)result[0]; 93 int num = (int)totalrecord; 94 System.out.println(num); 95 int totalrecord = ((Long)result[0]).intValue(); 96 */ 97 int totalrecord = ((Long)runner.query(sql, new ScalarHandler(1))).intValue(); 98 System.out.println(totalrecord); 99 } 100 101 102 }