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

蔚落 2024-02-18 14:47 30阅读 0赞

## **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包和源码][jar]

先创建一个Account表

![2WWE5od.png][]

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接口 ###

![s4wFKzr.png][]

![lGjfuDI.png][]

![hIWMtGu.png][]

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.Long
    103         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层修改

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：线程局部变量。。简单理解就是一个容易，一个线程放的数据，只有该线程能取到。

![Q0zm50c.png][]

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)
    );

封装JavaBean

public class Person implements Serializable {
        private int id;
        private String name;
        private IdCard idCard;

public class IdCard implements Serializable {
        private int id;
        private String num;
        private Person person;

测试

1 public class PersonDaoImplTest {
      2     private PersonDao dao = new PersonDaoImpl();
      3     @Test
      4     public void testAddPerson() {
      5         Person p1 = new Person();
      6         p1.setId(1);
      7         p1.setName("代佩孜");
      8         
      9         IdCard ic = new IdCard();
     10         ic.setId(100);//无效
     11         ic.setNum("1101010101");
     12         
     13         p1.setIdCard(ic);
     14         dao.addPerson(p1);
     15     }
     16 
     17     @Test
     18     public void testFindPerson() {
     19         Person p = dao.findPerson(1);
     20         System.out.println(p);
     21         System.out.println(p.getIdCard());
     22     }
     23 
     24 }

Dao实现

1 public class PersonDaoImpl implements PersonDao {
      2     private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
      3     @Override
      4     public void addPerson(Person p) {
      5         try {
      6             qr.update("insert into persons(id,name) values (?,?)", p.getId(),p.getName());
      7             IdCard ic = p.getIdCard();
      8             if(ic!=null){
      9                 qr.update("insert into idcards(id,num) values (?,?)", p.getId(),ic.getNum()) ;
     10             }
     11         } catch (SQLException e) {
     12             throw new RuntimeException(e);
     13         }
     14     }
     15     //查询人的基本信息，关联的IdCard要不要查？关联的少的一方就查
     16     public Person findPerson(int personId) {
     17         try {
     18             Person p = qr.query("select * from persons where id=?", new BeanHandler<Person>(Person.class), personId);
     19             if(p!=null){
     20                 IdCard ic = qr.query("select * from idcards where id=?",new BeanHandler<IdCard>(IdCard.class), personId);
     21                 p.setIdCard(ic);
     22             }
     23             return p;
     24         } catch (SQLException e) {
     25             throw new RuntimeException(e);
     26         }
     27     }
     28 
     29 }

[jar]: http://download.csdn.net/download/onceing/9955690
[2WWE5od.png]: http://i.imgur.com/2WWE5od.png
[s4wFKzr.png]: http://i.imgur.com/s4wFKzr.png
[lGjfuDI.png]: http://i.imgur.com/lGjfuDI.png
[hIWMtGu.png]: http://i.imgur.com/hIWMtGu.png
[Q0zm50c.png]: http://i.imgur.com/Q0zm50c.png