1 create table department_table(
 2     id int primary key,
 3     name varchar(100)
 4 );
 5 create table employee_table(
 6     id int primary key,
 7     name varchar(100),
 8     salary float(8,2),
 9     dept_id int,
10     constraint dept_id_fk foreign key(dept_id) references department(id)
11 );

 1 package cn.itcast.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.List;
 5 /*
 6 create table department(
 7     id int primary key,
 8     name varchar(100)
 9 );
10 create table employee(
11     id int primary key,
12     name varchar(100),
13     salary float(8,2),
14     dept_id int,
15     constraint dept_id_fk foreign key(dept_id) references department(id)
16 );
17  */
18 public class Employee implements Serializable {
19     Integer id;
20     String name;
21     float salary;
22     int dept_id;
23     Department dep;
24     public Employee() {
25         super();
26         // TODO Auto-generated constructor stub
27     }
28     public Integer getId() {
29         return id;
30     }
31     public void setId(Integer id) {
32         this.id = id;
33     }
34     public String getName() {
35         return name;
36     }
37     public void setName(String name) {
38         this.name = name;
39     }
40     public float getSalary() {
41         return salary;
42     }
43     public void setSalary(float salary) {
44         this.salary = salary;
45     }
46     public int getDept_id() {
47         return dept_id;
48     }
49     public void setDept_id(int dept_id) {
50         this.dept_id = dept_id;
51     }
52     @Override
53     public String toString() {
54         return "Employee [id=" + id + ", name=" + name + ", salary=" + salary
55                 + ", dept_id=" + dept_id + ", dep=" + dep + "]";
56     }
57     public Department getDep() {
58         return dep;
59     }
60     public void setDep(Department dep) {
61         this.dep = dep;
62     }
63     
64 }

 1 package cn.itcast.domain;
 2 
 3 import java.io.Serializable;
 4 import java.util.List;
 5 
 6 public class Department implements Serializable {
 7 
 8     private Integer id;
 9     private String name;
10     List<Employee> emps;
11     public List<Employee> getEmps() {
12         return emps;
13     }
14     public void setEmps(List<Employee> emps) {
15         this.emps = emps;
16     }
17     public Department() {
18         super();
19         // TODO Auto-generated constructor stub
20     }
21     public Integer getId() {
22         return id;
23     }
24     public void setId(Integer id) {
25         this.id = id;
26     }
27     public String getName() {
28         return name;
29     }
30     public void setName(String name) {
31         this.name = name;
32     }
33     @Override
34     public String toString() {
35         return "Department [id=" + id + ", name=" + name + "]";
36     }
37     
38 }

 1 package cn.itcast.dao.impl;
 2 
 3 import java.sql.SQLException;
 4 import java.util.List;
 5 
 6 import org.apache.commons.dbutils.QueryRunner;
 7 import org.apache.commons.dbutils.handlers.BeanHandler;
 8 import org.apache.commons.dbutils.handlers.BeanListHandler;
 9 import org.apache.commons.dbutils.handlers.ScalarHandler;
10 
11 import cn.itcast.domain.Department;
12 import cn.itcast.domain.Employee;
13 import cn.itcast.utils.JDBCUtil;
14 
15 public class EmployeeDaoImpl {
16     QueryRunner qr=new QueryRunner(JDBCUtil.getDataSource());
17     public void addEmployee(Employee e) throws SQLException
18     {
19         Department d=e.getDep();            //因为员工是外键依赖部门，所以先考察部门在不在然后考虑员工表
20         if(d!=null)
21         {
22             Object num=qr.query("select 1 from department_table where id= ?", new ScalarHandler(1),d.getId());
23             if(null==num)
24             {
25                 qr.update("insert into department_table (id,name) values(?,?)",d.getId(),d.getName());
26             }
27         }
28         String sql="insert into employee_table (id,name,salary,dept_id) values(?,?,?,?)";
29         qr.update(sql,e.getId(),e.getName(),e.getSalary(),e.getDept_id());
30     }
31     
32     public Department findDepartment(String id) throws SQLException
33     {
34         Department d=qr.query("select * from department_table where id =?", new BeanHandler<Department>(Department.class),id);
35         if(d!=null)
36         {
37             List<Employee> emps=qr.query("select * from employee_table where dept_id =?", new BeanListHandler<Employee>(Employee.class),d.getId());
38             d.setEmps(emps);
39         }
40         return d;
41     }
42 }

 1     EmployeeDaoImpl dao=new EmployeeDaoImpl();
 2     @Test
 3     public void addEmployee() throws SQLException
 4     {
 5         Department d=new Department();
 6         d.setId(1);
 7         d.setName("开发部");
 8 
 9         Employee e=new Employee();
10         e.setId(1);
11         e.setName("chenlongfei");
12         e.setSalary(10000);
13         e.setDept_id(1);
14         e.setDep(d);
15         
16         dao.addEmployee(e);
17         
18     }
19     @Test
20     public void findDepartment() throws SQLException
21     {
22         Department d=dao.findDepartment("1");
23         System.out.println(d.getName());
24         for(Employee e:d.getEmps())
25         {
26             System.out.println(e.getName());
27         }
28     }
29