J2EE进阶之JDBC工具类DbUtils使用二十

来源：互联网发布：电脑安全防护软件编辑：程序博客网时间：2024/06/06 09:35

JDBC之DbUtils使用

O-R Mapping简介

ORM：Object Relation Mapping。

Java：一切都是对象（实体对象）。数据库：关系型数据库。

常用O-R Mapping映射工具  Hibernate     CMP JPA(Java Persistent API)  Ibatis--->MyBatis Commons DbUtils(只是对JDBC简单封装)  Spring JDBC Template

一、DBUtil框架的使用

commons-dbutils 是 Apache 组织提供的一个开源 JDBC工具类库，它是对JDBC的简单封装，学习成本极低，并且使用dbutils能极大简化jdbc编码的工作量，同时也不会影响程序的性能。因此dbutils成为很多不喜欢hibernate的公司的首选。

API介绍(查看QueryRunner的API)

org.apache.commons.dbutils.QueryRunner :DBAssist  org.apache.commons.dbutils.ResultSetHandler

工具类

org.apache.commons.dbutils.DbUtils、。

点击下载jar包和源码

先创建一个Account表

QueryRunner类

QueryRunnerDemo

  1 package jdbcdemo4.wsj.demo;  2   3 import java.io.File;  4 import java.io.FileReader;  5 import java.io.Reader;  6 import java.sql.Clob;  7 import java.sql.SQLException;  8 import javax.sql.rowset.serial.SerialClob;  9 import org.apache.commons.dbutils.QueryRunner; 10 import org.junit.Test; 11 import utils.DBCPUtil; 12 /** 13  *  14  * @author Angus 15  * 16  */ 17 public class QueryRunnerDemo { 18     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource()); 19     @Test 20     public void testAdd() throws SQLException{ 21         qr.update("insert into account (name,money) values (?,?)", "ddd",1000); 22     } 23     @Test 24     public void testUpdate() throws SQLException{ 25         qr.update("update account set money=? where id=?", 10000,4); 26     } 27     @Test 28     public void testDelete() throws SQLException{ 29         qr.update("delete from account where id=?", 4); 30     } 31      32     //插入10条数据 33     @Test 34     public void testBatch() throws SQLException{ 35         Object[][] params = new Object[10][]; 36         for (int i = 0; i < params.length; i++) { 37             params[i] = new Object[]{i+1,"aaa"+(i+1)}; 38              39         } 40         qr.batch("insert into t1(id,name) values(?,?)", params); 41     } 42      43     //了解:大文本和二进制的操作 44     /* 45      create table t2(id int,content longtext ); 46      */ 47     //注意类型：content longtext    Java：java.sql.Clob 48     @Test 49     public void testLob() throws Exception{ 50         File file = new File("src/a.txt"); 51         Reader reader = new FileReader(file); 52         char ch[] = new char[(int)file.length()]; 53         reader.read(ch); 54         reader.close(); 55          56         Clob clob = new SerialClob(ch); 57         qr.update("insert into t2 (id,content) values (?,?)", 1,clob); 58     } 59 }

二、DBUtil提供的结果处理器：ResultSetHandler接口

demo测试

  1 package jdbcdemo4.wsj.demo;  2   3 import java.sql.SQLException;  4 import java.util.List;  5 import java.util.Map;  6   7 import jdbcdemo4.wsj.domain.Account;  8   9 import org.apache.commons.dbutils.QueryRunner; 10 import org.apache.commons.dbutils.handlers.ArrayHandler; 11 import org.apache.commons.dbutils.handlers.ArrayListHandler; 12 import org.apache.commons.dbutils.handlers.BeanHandler; 13 import org.apache.commons.dbutils.handlers.BeanListHandler; 14 import org.apache.commons.dbutils.handlers.ColumnListHandler; 15 import org.apache.commons.dbutils.handlers.KeyedHandler; 16 import org.apache.commons.dbutils.handlers.MapHandler; 17 import org.apache.commons.dbutils.handlers.MapListHandler; 18 import org.apache.commons.dbutils.handlers.ScalarHandler; 19 import org.junit.Test; 20  21 import utils.DBCPUtil; 22  23 /** 24  * @author Angus 25  * 需要掌握的：BeanHandler  BeanListHandler  ScalarHandler 26  */ 27 public class QueryRunnerDemo1 { 28     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource()); 29     //适合结果只有一条，把结果封装到JavaBean中：BeanHandler 30     @Test 31     public void test1() throws SQLException{ 32         Account a = qr.query("select * from account", new BeanHandler<Account>(Account.class)); 33         System.out.println(a); 34     } 35     //适合结果有多条，把结果封装到JavaBean中List：BeanListHandler 36     @Test 37     public void test2() throws SQLException{ 38         List<Account> as = qr.query("select * from account", new BeanListHandler<Account>(Account.class)); 39         for(Account a:as) 40             System.out.println(a); 41     } 42     //ArrayHandler：把结果集中的第一行数据转成对象数组.适合结果有一条。         Object[] 43     @Test 44     public void test3() throws SQLException{ 45         Object[] objs = qr.query("select * from account", new ArrayHandler()); 46         for(Object obj:objs) 47             System.out.println(obj);//每列的值 48     } 49     //ArrayListHandler：适合结果有多条。把结果集中的每一行数据都转成一个数组，再存放到List中。      List<Object[]> 50     @Test 51     public void test4() throws SQLException{ 52         List<Object[]> list = qr.query("select * from account", new ArrayListHandler()); 53         for(Object[] objs:list){ 54             System.out.println("---------------"); 55             for(Object obj:objs){ 56                 System.out.println(obj);//每列的值 57             } 58         } 59          60     } 61     //ColumnListHandler：将结果集中某一列的数据存放到List中。  取某一列         List<Object> 62     @Test 63     public void test5() throws SQLException{ 64         List<Object> list = qr.query("select * from account", new ColumnListHandler("name")); 65         for(Object obj:list){ 66             System.out.println(obj); 67         } 68     } 69     //KeyedHandler(name)：适合多条记录。将结果集中的每一行数据都封装到一个Map<列名,列值>里，再把这些map再存到一个map里，其key为指定的key。 70     @Test 71     public void test6() throws SQLException{ 72         Map<Object,Map<String,Object>> bmap = qr.query("select * from account", new KeyedHandler("id")); 73         for(Map.Entry<Object,Map<String,Object>> bme:bmap.entrySet()){ 74             System.out.println("---------------"); 75             for(Map.Entry<String, Object> lme:bme.getValue().entrySet()){ 76                 System.out.println(lme.getKey()+"="+lme.getValue()); 77             } 78         } 79     } 80     //MapHandler：适合只有一条结果。将结果集中的第一行数据封装到一个Map里，key是列名，value就是对应的值。 81     @Test 82     public void test7() throws SQLException{ 83         Map<String,Object> map = qr.query("select * from account", new MapHandler()); 84         for(Map.Entry<String, Object> lme:map.entrySet()){ 85             System.out.println(lme.getKey()+"="+lme.getValue()); 86         } 87     } 88     //MapListHandler：将结果集中的每一行数据都封装到一个Map里，然后再存放到List 89     @Test 90     public void test8() throws SQLException{ 91         List<Map<String,Object>> list = qr.query("select * from account", new MapListHandler()); 92         for(Map<String,Object> map:list) 93         for(Map.Entry<String, Object> lme:map.entrySet()){ 94             System.out.println(lme.getKey()+"="+lme.getValue()); 95         } 96     } 97     //ScalarHandler:适合结果只有一条且只有一列的情况。  count(*) 98     @Test 99     public void test9() throws SQLException{100         Object obj = qr.query("select * from account where id=1", new ScalarHandler("name"));101         System.out.println(obj);102         obj = qr.query("select count(*) from account", new ScalarHandler(1));//java.lang.Long103         System.out.println(obj.getClass().getName());104         System.out.println(obj);105     }106     107 }

三、DBUtil做事务开发：4个版本以转账为例

第一个版本异常没有做处理，模拟

service层

public interface BusinessService {    /**     * 转账     * @param srcAccountName 源账户     * @param destAccountName 目标账户     * @param money 金额     */    void transfer(String srcAccountName,String destAccountName,float money);}

service实现层

import jdbcdemo4.wsj.dao.AccountDao;import jdbcdemo4.wsj.dao.impl.AccountDaoImpl;import jdbcdemo4.wsj.service.BusinessService;public class BusinessServiceImpl implements BusinessService {    private AccountDao dao = new AccountDaoImpl();    public void transfer(String srcAccountName, String destAccountName,            float money) {        dao.transfer(srcAccountName, destAccountName,            money);    }}

Dao层

public interface AccountDao {    void transfer(String srcAccountName, String destAccountName, float money);}

dao实现

public class AccountDaoImpl implements AccountDao {    private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());    public void transfer(String srcAccountName, String destAccountName,            float money) {        try {            qr.update("update account set money=money-? where name=?",money,srcAccountName);            int i=1/0; //这样出现了异常就造成只减不加。。            qr.update("update account set money=money+? where name=?",money,destAccountName);        } catch (SQLException e) {            e.printStackTrace();        }    }}

main方法测试

public class Main {    public static void main(String[] args) {        BusinessService s = new BusinessServiceImpl();        s.transfer("aaa", "bbb", 100);    }}

第二种情况，能解决事物问题，但是不建议这样写，事务的要求一般都是业务上的要求，不合理

dao实现修改后

  1 public class AccountDaoImpl implements AccountDao {  2     private QueryRunner qr = new QueryRunner();  3     public void transfer(String srcAccountName, String destAccountName,  4             float money) {  5         Connection conn = null;  6         try {  7             conn = DBCPUtil.getConnection();  8             conn.setAutoCommit(false);//开启事务  9             qr.update(conn,"update account set money=money-? where name=?",money,srcAccountName); 10             int i=1/0; 11             qr.update(conn,"update account set money=money+? where name=?",money,destAccountName); 12             conn.commit(); 13         } catch (SQLException e) { 14             if(conn!=null){ 15                 try { 16                     conn.rollback(); 17                 } catch (SQLException e1) { 18                     e1.printStackTrace(); 19                 } 20             } 21             e.printStackTrace(); 22         }finally{ 23             //把连接还回池中 24             if(conn!=null){ 25                 try { 26                     conn.close(); 27                 } catch (SQLException e) { 28                     e.printStackTrace(); 29                 } 30             } 31         } 32     } 33  34 }

第三种情况，也能解决，也不建议这么写

可以修改service层

  1 public class BusinessServiceImpl implements BusinessService {  2       3     public void transfer(String srcAccountName, String destAccountName,  4             float money) {  5         Connection conn = null;  6         try{  7             conn = DBCPUtil.getConnection();  8             conn.setAutoCommit(false);  9             AccountDao dao = new AccountDaoImpl(conn); 10             Account sAccount = dao.findAccount(srcAccountName); 11             Account dAccount = dao.findAccount(destAccountName); 12              13             sAccount.setMoney(sAccount.getMoney()-money); 14             dAccount.setMoney(dAccount.getMoney()+money); 15              16             dao.update(sAccount); 17 //          int i=1/0; 18             dao.update(dAccount); 19             conn.commit(); 20         }catch(Exception e){ 21             if(conn!=null){ 22                 try { 23                     conn.rollback(); 24                 } catch (SQLException e1) { 25                     e1.printStackTrace(); 26                 } 27             } 28             throw new RuntimeException(e); 29         }finally{ 30             if(conn!=null){ 31                 try { 32                     conn.close(); 33                 } catch (SQLException e) { 34                     e.printStackTrace(); 35                 } 36             } 37         } 38          39          40     } 41  42 }

Connection这样的接口应该出现在DAO层，但是出现在了业务逻辑层。。。

Dao层修改后：

  1 public class AccountDaoImpl implements AccountDao {  2     private QueryRunner qr = new QueryRunner();  3     private Connection conn;  4     public AccountDaoImpl(Connection conn){  5         this.conn = conn;  6     }  7     @Override  8     public Account findAccount(String srcAccountName) {  9         try { 10             return qr.query(conn,"select * from account where name=?", new BeanHandler<Account>(Account.class), srcAccountName); 11         } catch (SQLException e) { 12             throw new RuntimeException(e); 13         } 14     } 15     @Override 16     public void update(Account sAccount) { 17         try { 18             qr.update(conn,"update account set money=? where name=?",sAccount.getMoney(),sAccount.getName()); 19         } catch (SQLException e) { 20             throw new RuntimeException(e); 21         } 22     } 23  24 }

Dao层完事了但是service层不推荐。。

第四种情况

这里引用了ThreadLocal概念，建议先了解四、ThreadLocal：很重要，很简单第四部分模块在看线程处理情况

把service层的事物处理抽取出来到事物管理器中：

TransactionManager

  1 public class TransactionManager {  2     private static ThreadLocal<Connection> tl = new ThreadLocal<Connection>();  3       4     public static Connection getConnection(){  5         Connection conn = tl.get();//获取当前线程上的链接  6         if(conn==null){  7             conn = DBCPUtil.getConnection();  8             tl.set(conn);  9         } 10         return conn; 11     } 12     public static void startTransaction(){ 13         Connection conn = getConnection(); 14         try { 15             conn.setAutoCommit(false); 16         } catch (SQLException e) { 17             e.printStackTrace(); 18         } 19     } 20     public static void rollback(){ 21         Connection conn = getConnection(); 22         try { 23             conn.rollback(); 24         } catch (SQLException e) { 25             e.printStackTrace(); 26         } 27     } 28     public static void commit(){ 29         Connection conn = getConnection(); 30         try { 31             conn.commit(); 32         } catch (SQLException e) { 33             e.printStackTrace(); 34         } 35     } 36     public static void release(){ 37         Connection conn = getConnection(); 38         try { 39             conn.close();//链接回池 40             tl.remove();//从当前线程上把链接移除。与服务器有关：服务器都使用了线程池。 41         } catch (SQLException e) { 42             e.printStackTrace(); 43         } 44     } 45 }

service层修改

  1 public class BusinessServiceImpl implements BusinessService {  2       3     public void transfer(String srcAccountName, String destAccountName,  4             float money) {  5         Connection conn = null;  6         try{  7             conn = DBCPUtil.getConnection();  8             conn.setAutoCommit(false);  9             AccountDao dao = new AccountDaoImpl(conn); 10             Account sAccount = dao.findAccount(srcAccountName); 11             Account dAccount = dao.findAccount(destAccountName); 12              13             sAccount.setMoney(sAccount.getMoney()-money); 14             dAccount.setMoney(dAccount.getMoney()+money); 15              16             dao.update(sAccount); 17 //          int i=1/0; 18             dao.update(dAccount); 19             conn.commit(); 20         }catch(Exception e){ 21             if(conn!=null){ 22                 try { 23                     conn.rollback(); 24                 } catch (SQLException e1) { 25                     e1.printStackTrace(); 26                 } 27             } 28             throw new RuntimeException(e); 29         }finally{ 30             if(conn!=null){ 31                 try { 32                     conn.close(); 33                 } catch (SQLException e) { 34                     e.printStackTrace(); 35                 } 36             } 37         } 38          39          40     } 41  42 }

Dao层稍微改动

  1 public class AccountDaoImpl implements AccountDao {  2     private QueryRunner qr = new QueryRunner();  3     @Override  4     public Account findAccount(String srcAccountName) {  5         try {  6             return qr.query(TransactionManager.getConnection(),"select * from account where name=?", new BeanHandler<Account>(Account.class), srcAccountName);  7         } catch (SQLException e) {  8             throw new RuntimeException(e);  9         } 10     } 11     @Override 12     public void update(Account sAccount) { 13         try { 14             qr.update(TransactionManager.getConnection(),"update account set money=? where name=?",sAccount.getMoney(),sAccount.getName()); 15         } catch (SQLException e) { 16             throw new RuntimeException(e); 17         } 18     } 19  20 }

这样就基本解决了事物的问题。。

四、ThreadLocal：很重要，很简单

查看JDK中API：线程局部变量。。简单理解就是一个容易，一个线程放的数据，只有该线程能取到。

demo测试

写一个线程

public class Thread1 extends Thread {    private ThreadLocal<String> tl;    public Thread1(ThreadLocal<String> tl) {        this.tl = tl;    }    public void run() {        System.out.println(Thread.currentThread()+"取："+tl.get());    }}

测试main方法：

public class ThreadLocalDemo {    public static void main(String[] args) {        ThreadLocal<String> tl = new ThreadLocal<String>();//容器        tl.set("p");//main主线程        Thread1 tt = new Thread1(tl);        tt.start();        Object obj = tl.get();        System.out.println(Thread.currentThread().getName()+"取："+obj);    }}

输出结果只有main中能取出而Thread1中无法取到。。。

五、利用DBUtil进行多表的存和取

一对多

一个用户对应多个订单案例

创建表

create table customers(    id int primary key,    name varchar(100));create table orders(    ordernum int primary key,    money float(8,2),    customerId int,    constraint customer_id_fk foreign key (customerId) references customers(id));

封装JavaBean数据

public class Customer implements Serializable {    private int id;//    private String name;    private List<Orders> orders = new ArrayList<Orders>();    //....

。

public class Orders implements Serializable {    private int ordernum;//订单号    private float money;    private Customer customer;

测试：

public class CustomerDaoImplTest {    private CustomerDao dao = new CustomerDaoImpl();    @Test    public void testAddCustomer() {        Customer c = new Customer();        c.setId(1);        c.setName("wsj");        Orders o1 = new Orders();        o1.setOrdernum(1);        o1.setMoney(1000);        Orders o2 = new Orders();        o2.setOrdernum(2);        o2.setMoney(100);        //建立关联关系        c.getOrders().add(o1);        c.getOrders().add(o2);        dao.addCustomer(c);    }    @Test    public void testFindCustomer() {        Customer c = dao.findCustomer(1);        System.out.println(c);        for(Orders o:c.getOrders())            System.out.println(o);    }}

Dao编写

  1 public class CustomerDaoImpl implements CustomerDao {  2     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());  3     public void addCustomer(Customer c) {  4         try {  5             //保存客户的基本信息  6             qr.update("insert into customers values (?,?)", c.getId(),c.getName());  7             //判断客户有没有订单，如果有，保存订单信息  8             List<Orders> orders = c.getOrders();  9             if(orders.size()>0){ 10                 Object[][] params = new Object[orders.size()][]; 11                 for(int i=0;i<orders.size();i++){ 12                     params[i] = new Object[]{orders.get(i).getOrdernum(),orders.get(i).getMoney(),c.getId()}; 13                 } 14                 qr.batch("insert into orders (ordernum,money,customerId) values (?,?,?)", params); 15             } 16         } catch (SQLException e) { 17             throw new RuntimeException(e); 18         } 19     } 20     //一对多： 21     //客户：少的一方。订单：多的一方。 22     //在查少的一方的信息时，多的一方要不要一块查出来？ 看需求。 23     public Customer findCustomer(int customerId) { 24         try { 25             //查询客户的基本信息 26             Customer c = qr.query("select * from customers where id=?", new BeanHandler<Customer>(Customer.class), customerId); 27             //查询客户对应的订单信息，并建立关系 28             if(c!=null){ 29                 //针对查询客户有多少订单这种情况。 30                 //极懒List<Orders> os = qr.query("select count(*) from orders where customerId=?", new BeanListHandler<Orders>(Orders.class), customerId); 31                 //懒的List<Orders> os = qr.query("select ordernum from orders where customerId=?", new BeanListHandler<Orders>(Orders.class), customerId); 32                 //饿的List<Orders> os = qr.query("select * from orders where customerId=?", new BeanListHandler<Orders>(Orders.class), customerId); 33                 List<Orders> os = qr.query("select * from orders where customerId=?", new BeanListHandler<Orders>(Orders.class), customerId); 34                 c.setOrders(os); 35             } 36             return c; 37         } catch (SQLException e) { 38             throw new RuntimeException(e); 39         } 40     } 41  42 }

多对多

老师和学生的关系案例

创建表

create table teachers(    id int primary key,    name varchar(100),    salary float(8,2));create table students(    id int primary key,    name varchar(100),    grade varchar(10));create table teachers_students(    t_id int,    s_id int,    primary key(t_id,s_id),    constraint teacher_id_fk foreign key (t_id) references teachers(id),    constraint student_id_fk foreign key (s_id) references students(id));

封装JavaBean

public class Teacher implements Serializable {    private int id;    private String name;    private float salary;    private List<Student> students = new ArrayList<Student>();

。

public class Student implements Serializable {    private int id;    private String name;    private String grade;    private List<Teacher> teachers = new ArrayList<Teacher>();

测试：

  1 public class TeacherDaoImplTest {  2     private TeacherDao dao = new TeacherDaoImpl();  3     @Test  4     public void testAddTeacher() {  5         Teacher t1 = new Teacher();  6         t1.setId(1);  7         t1.setName("RT");  8         t1.setSalary(10000);  9          10         Teacher t2 = new Teacher(); 11         t2.setId(2); 12         t2.setName("WZT"); 13         t2.setSalary(10000); 14          15         Student s1 = new Student(); 16         s1.setId(1); 17         s1.setName("jsw"); 18         s1.setGrade("A"); 19          20         Student s2 = new Student(); 21         s2.setId(2); 22         s2.setName("wsj"); 23         s2.setGrade("A"); 24          25         t1.getStudents().add(s1); 26         t1.getStudents().add(s2); 27          28         t2.getStudents().add(s1); 29         t2.getStudents().add(s2); 30          31         dao.addTeacher(t1); 32         dao.addTeacher(t2); 33     } 34  35     @Test 36     public void testFindTeacher() { 37         Teacher t = dao.findTeacher(2); 38         System.out.println(t); 39         for(Student s:t.getStudents()){ 40             System.out.println(s); 41         } 42     } 43  44 }

Dao实现

  1 public class TeacherDaoImpl implements TeacherDao {  2     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());  3     @Override  4     public void addTeacher(Teacher t) {  5         try {  6             //保存老师的基本信息  7             qr.update("insert into teachers (id,name,salary) values (?,?,?)", t.getId(),t.getName(),t.getSalary());  8               9             List<Student> students = t.getStudents(); 10             if(students.size()>0){ 11                 for(Student s:students){ 12                     //看看老师有没有关联学生，保存学生信息（看看学生信息有没有？没有，才保存） 13                     Long l = (Long)qr.query("select count(*) from students where id=?", new ScalarHandler(1),s.getId()); 14                     if(l==0){//学生信息不存在 15                         qr.update("insert into students (id,name,grade) values(?,?,?)", s.getId(),s.getName(),s.getGrade()); 16                     } 17                 //保存关联信息到第三方表中 18                     qr.update("insert into teachers_students (t_id,s_id) values (?,?)", t.getId(),s.getId()); 19                 } 20             } 21         } catch (SQLException e) { 22             throw new RuntimeException(e); 23         } 24              25     } 26  27     @Override 28     public Teacher findTeacher(int teacherId) { 29         try{ 30             Teacher t = qr.query("select * from teachers where id=?", new BeanHandler<Teacher>(Teacher.class),teacherId); 31             if(t!=null){ 32                 //查学生信息 33     //          String sql = "select * from students where id in (select s_id from teachers_students where t_id=?)"; 34     //          String sql = "select s.* from students s,teachers_students ts where s.id=ts.s_id and ts.t_id=?"; 35                 String sql = "select s.* from students s inner join teachers_students ts on s.id=ts.s_id where ts.t_id=?"; 36                 List<Student> students = qr.query(sql, new BeanListHandler<Student>(Student.class), teacherId); 37                 t.setStudents(students); 38             } 39             return t; 40         } catch (SQLException e) { 41             throw new RuntimeException(e); 42         } 43     } 44  45 }

一对一

一个人对应一个身份证案例

建表

create table persons(    id int primary key,    name varchar(100));create table idcards(    id int primary key,    num varchar(18),    constraint person_id_fk foreign key (id) references persons(id));