一、一对多关系：部门管理系统
1、数据库
1
2 create table department
3 (
4 id varchar(40) primary key,
5 name varchar(40)
6 );
7
8
9 create table employee
10 (
11 id varchar(40) primary key,
12 name varchar(40),
13 salary double,
14 department_id varchar(40),
15 constraint department_id_FK foreign key(department_id) references department(id) on delete set null;
16 );
constraint department_id_FK foreign key(department_id) references department(id) on delete set null; 设置级联
当主表的记录删除后，从表的外键置为空
2、对象
Department.java：部门
1 package com.domain;
2
3 import java.util.HashSet;
4 import java.util.Set;
5
6 public class Department {
7
8 private String id;
9 private String name;
10
11 //设置一个Set集合保存所有员工
12 private Set employees = new HashSet();
13
14 public String getId() {
15 return id;
16 }
17 public void setId(String id) {
18 this.id = id;
19 }
20 public String getName() {
21 return name;
22 }
23 public void setName(String name) {
24 this.name = name;
25 }
26 public Set getEmployees() {
27 return employees;
28 }
29 public void setEmployees(Set employees) {
30 this.employees = employees;
31 }
32 }
在多对一的情况下，要注意“一”的一方是否需要维护多的一方数据的集合
Employee.java:员工
1 package com.domain;
2
3 public class Employee {
4 private String id;
5 private String name;
6 private double salary;
7 private Department department;
8
9 public String getId() {
10 return id;
11 }
12 public void setId(String id) {
13 this.id = id;
14 }
15 public String getName() {
16 return name;
17 }
18 public void setName(String name) {
19 this.name = name;
20 }
21 public double getSalary() {
22 return salary;
23 }
24 public void setSalary(double salary) {
25 this.salary = salary;
26 }
27 public Department getDepartment() {
28 return department;
29 }
30 public void setDepartment(Department department) {
31 this.department = department;
32 }
33 }
3、dao层
DepartmentDao.java
1 package com.dao;
2 import java.sql.SQLException;
3 import java.util.List;
4 import java.util.Set;
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
10 import com.domain.Department;
11 import com.domain.Employee;
12 import com.utils.JdbcUtils;
13
14
15 public class DepartmentDao {
16 //插入操作
17 public void insert(Department d) throws SQLException{
18 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
19
20 //1、把Department对象的数据插入到department表
21 String sql = "insert into department(id,name) values(?,?)";
22 Object params[] = {d.getId(),d.getName()};
23 runner.update(sql, params);
24
25 //2、把department维护的所有员工保存在employee表
26 Set<Employee> set = d.getEmployees();
27 for(Employee e : set){
28 sql = "insert into employee(id,name,salary,department_id) values(?,?,?,?)";
29
30 //3、更新员工的外键列，说明员工所在的部门
31 params = new Object[]{e.getId(),e.getName(),e.getSalary(),d.getId()};
32 runner.update(sql, params);
33 }
34 }
35
36 //查找
37 public Department find(String id) throws SQLException{
38
39 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
40
41 //1.找部门表，查出部门的基本信息
42 String sql = "select * from department where id=?";
43 Department d = (Department) runner.query(sql, id, new BeanHandler(Department.class));
44
45 //2.找员工表，找出部门下面所有员工
46 sql = "select * from employee where department_id=?";
47 List list = (List) runner.query(sql, id, new BeanListHandler(Employee.class));
48
49
50 d.getEmployees().addAll(list);
51
52 return d;
53 }
54
55 //删除
56 public void delete(String id) throws SQLException{
57 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
58 String sql = "delete from department where id=?";
59 runner.update(sql, id);
60 }
61 }
4、service层
1 package com.service;
2
3 import java.sql.SQLException;
4
5 import org.junit.Test;
6
7 import com.dao.DepartmentDao;
8 import com.domain.Department;
9 import com.domain.Employee;
10
11 public class Bservice {
12 @Test
13 public void add() throws SQLException{
14 Department d = new Department();
15 d.setId("111");
16 d.setName("开发部");
17
18 Employee e1 = new Employee();
19 e1.setId("1");
20 e1.setName("aa");
21 e1.setSalary(10000);
22
23
24 Employee e2 = new Employee();
25 e2.setId("2");
26 e2.setName("bb");
27 e2.setSalary(10000);
28
29 d.getEmployees().add(e1);
30 d.getEmployees().add(e2);
31
32 DepartmentDao dao = new DepartmentDao();
33 dao.insert(d);
34 }
35
36 @Test
37 //查找
38 public void find() throws SQLException{
39
40 DepartmentDao dao = new DepartmentDao();
41 Department d = dao.find("111");
42 System.out.println(d.getName());
43
44 }
45
46
47 @Test
48 //删除
49 public void delete() throws SQLException{
50
51 DepartmentDao dao = new DepartmentDao();
52 dao.delete("111");
53 }
54 }
5、工具类
JdbcUtils.java
1 package com.utils;
2
3 import java.io.IOException;
4 import java.io.InputStream;
5 import java.sql.Connection;
6 import java.sql.SQLException;
7 import java.util.Properties;
8
9 import javax.sql.DataSource;
10
11 import org.apache.commons.dbcp.BasicDataSourceFactory;
12
13 public class JdbcUtils {
14 private static DataSource ds;
15 static{
16 try {
17 Properties prop = new Properties();
18 InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
19 prop.load(in);
20
21 //创建数据源
22 BasicDataSourceFactory factory = new BasicDataSourceFactory();
23 ds = factory.createDataSource(prop);
24 } catch (Exception e) {
25 throw new ExceptionInInitializerError(e);
26 }
27 }
28
29 //提供数据源
30 public static DataSource getDataSource(){
31 return ds;
32 }
33
34 //获取连接
35 public static Connection getConnection() throws SQLException{
36 return ds.getConnection();
37 }
38 }
二、多对多关系：老师-----学生
1、数据库
create table teacher
(
id varchar(40) primary key,
name varchar(40),
salary double
) ;
create table student
(
id varchar(40) primary key,
name varchar(40)
);
create table teacher_student
(
teacher_id varchar(40),
student_id varchar(40),
primary key(teacher_id,student_id),
constraint teacher_id_FK foreign key(teacher_id) references teacher(id),
constraint student_id_FK foreign key(student_id) references student(id)
);
2、实体类
Teacher.java
3 import java.util.HashSet;
4 import java.util.Set;
5
6 public class Teacher {
7 private String id;
8 private String name;
9 private double salary;
10 private Set students = new HashSet();
11 public String getId() {
12 return id;
13 }
14 public void setId(String id) {
15 this.id = id;
16 }
17 public String getName() {
18 return name;
19 }
20 public void setName(String name) {
21 this.name = name;
22 }
23 public double getSalary() {
24 return salary;
25 }
26 public void setSalary(double salary) {
27 this.salary = salary;
28 }
29 public Set getStudents() {
30 return students;
31 }
32 public void setStudents(Set students) {
33 this.students = students;
34 }
35
36
37
38 }
Student.java
3 import java.util.HashSet;
4 import java.util.Set;
5
6 public class Student {
7 private String id;
8 private String name;
9 private Set teachers = new HashSet();
10 public String getId() {
11 return id;
12 }
13 public void setId(String id) {
14 this.id = id;
15 }
16 public String getName() {
17 return name;
18 }
19 public void setName(String name) {
20 this.name = name;
21 }
22 public Set getTeachers() {
23 return teachers;
24 }
25 public void setTeachers(Set teachers) {
26 this.teachers = teachers;
27 }
28
29
30 }
3、dao层
TeacherDao.java
1 import java.sql.SQLException;
2 import java.util.List;
3 import java.util.Set;
4
5 import org.apache.commons.dbutils.QueryRunner;
6 import org.apache.commons.dbutils.handlers.BeanHandler;
7 import org.apache.commons.dbutils.handlers.BeanListHandler;
8
9 import cn.itcast.domain.Student;
10 import cn.itcast.domain.Teacher;
11 import cn.itcast.utils.JdbcUtils;
12
13 public class TeacherDao {
14
15 public void add(Teacher t) throws SQLException {
16
17 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
18
19 //1`.取出老师存老师表
20 String sql = "insert into teacher(id,name,salary) values(?,?,?)";
21 Object params[] = {t.getId(),t.getName(),t.getSalary()};
22 runner.update(sql, params);
23
24
25 //2.取出老师所有学生的数据，存学生表
26 Set<Student> set = t.getStudents();
27 for(Student s : set){
28 sql = "insert into student(id,name) values(?,?)";
29 params = new Object[]{s.getId(),s.getName()};
30 runner.update(sql, params);
31
32 //3.更新中间表，说明老师和学生的关系
33 sql = "insert into teacher_student(teacher_id,student_id) values(?,?)";
34 params = new Object[]{t.getId(),s.getId()};
35 runner.update(sql, params);
36 }
37 }
38
39 public Teacher find(String id) throws SQLException{
40
41 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
42
43 //1.找老师表，找出老师的基本信息
44 String sql = "select * from teacher where id=?";
45 Teacher t = (Teacher) runner.query(sql, id, new BeanHandler(Teacher.class));
46
47 //2.找出老师的所有学生 多表查询
49 sql = "select s.* from teacher_student ts,student s where ts.teacher_id=? and ts.student_id=s.id";
50 List list = (List) runner.query(sql, id, new BeanListHandler(Student.class));
51
52
53 t.getStudents().addAll(list);
54 return t;
55 }
56
57 public void delete(String id){
58 QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
59 String sql = "delete from teacher where id=?";
60
61 }
62 }
4、测试
1 @Test
2 public void addTeacher() throws SQLException{
3
4 Teacher t = new Teacher();
5 t.setId("1");
6 t.setName("老张");
7 t.setSalary(100000);
8
9 Student s1 = new Student();
10 s1.setId("1");
11 s1.setName("aa");
12
13 Student s2 = new Student();
14 s2.setId("2");
15 s2.setName("bb");
16
17 t.getStudents().add(s1);
18 t.getStudents().add(s2);
19
20
21 TeacherDao dao = new TeacherDao();
22 dao.add(t);
23 }
24
25 @Test
26 public void findTeacher() throws SQLException{
27 TeacherDao dao = new TeacherDao();
28 Teacher t = dao.find("1");
29 System.out.println(t);
30 }