什么情况下才会产生多表操作呢？内存里面产生了一个对象，这个对象是一个复杂对象，有关联的对象。
一对多的关系
一对多关系
假设做一个部门管理系统，一个部门下有多个员工。
1. 建表：
create table department
(
id varchar(40) primary key,
name varchar(40)
);
在多的那一方加外键约束
create table employee
(
id varchar(40) primary key,
name varchar(40),
salary double,
department_id varchar(40),
constraint department_id_FK foreign key(department_id) references department(id)
);
2. 实体类Department和Employee写好。
**3. 具体的实现DepartmentDao **
package cn.itcast.dao;
import cn.itcast.domain.Department;
import cn.itcast.domain.Employee;
import cn.itcast.utils.JdbcUtils_dbcp;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;
import java.util.Set;
/**
* Created by yvettee on 2017/10/10.
*/
public class DepartmentDao {
public void add(Department d) {
try {
QueryRunner runner = new QueryRunner(JdbcUtils_dbcp.getDataSource());
//1.把department对象的数据插入到department表
String sql = "insert into department(id,name) values(?,?)";
Object params[] = {d.getId(), d.getName()};
runner.update(sql, params);
//2.把department对象中维护的所有员工插入到员工表
//3.更新员工表的外键列，说明员工的部门
Set<Employee> set = d.getEmployees();
for (Employee e : set) {
sql = "insert into employee(id,name,salary,department_id) values(?,?,?,?)";
params = new Object[]{e.getId(), e.getName(), e.getSalary(), d.getId()};
runner.update(sql, params);
}
} catch (SQLException e) {
throw new RuntimeException(e);
}
}
public Department find(String id) throws SQLException {
QueryRunner runner = new QueryRunner(JdbcUtils_dbcp.getDataSource());
//1.找部门表，查出部门的基本信息
String sql = "select * from department where id=?";
Department d = (Department) runner.query(sql, id, new BeanHandler(Department.class));
//2.找员工表，找出部门下面所有员工
sql = "select * from employee where department_id=?";
List list = (List) runner.query(sql, id, new BeanListHandler(Employee.class));
d.getEmployees().addAll(list);
return d;
}
//删除时有外键，采用级联删除，删除时，将外键列置为空，修改表
public void delete(String id) throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtils_dbcp.getDataSource());
String sql= "delete from department where id=?";
runner.update(sql, id);
}
}
做删除操作时，修改表：
alter table employee drop foreign key department_id_FK;
alter table employee add constraint department_id_FK foreign key(department_id) references department(id) on delete set null;
多对多的关系
教学管理系统：多个教师与多个学生
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)
);
当做删除时，修改约束条件：
alter table teacher_student drop foreign key teacher_id_FK;
alter table teacher_student add constraint teacher_id_FK foreign key(teacher_id) references teacher(id) on delete cascade;
alter table teacher_student drop foreign key student_id_FK;
alter table teacher_student add constraint student_id_FK foreign key(student_id) references student(id) on delete cascade;
2. 具体实现
package cn.itcast.dao;
import cn.itcast.domain.Student;
import cn.itcast.domain.Teacher;
import cn.itcast.utils.JdbcUtils_dbcp;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import java.sql.SQLException;
import java.util.List;
import java.util.Set;
/**
* Created by yvettee on 2017/10/10.
*/
public class TeacherDao {
public void add(Teacher t) throws SQLException {
QueryRunner runner = new QueryRunner(JdbcUtils_dbcp.getDataSource());
//1.取出老师存老师表
String sql = "insert into teacher(id,name,salary) values(?,?,?)";
Object params[] = {t.getId(), t.getName(), t.getSalary()};
runner.update(sql, params);
//2.取出老师所有学生的数据，存学生表
Set<Student> set = t.getStudents();
for (Student s : set) {
sql = "insert into student(id,name) values(?,?)";
params = new Object[]{s.getId(), s.getName()};
runner.update(sql, params);
//3.更新中间表，说明老师和学生的关系
sql = "insert into teacher_student(teacher_id,student_id) values(?,?)";
params = new Object[]{t.getId(), s.getId()};
runner.update(sql, params);
}
}
public Teacher find(String id) throws SQLException {
QueryRunner runner = new QueryRunner(JdbcUtils_dbcp.getDataSource());
//1.找老师表，找出老师的基本信息
String sql = "select * from teacher where id=?";
Teacher t = runner.query(sql, id, new BeanHandler<Teacher>(Teacher.class));
//2.找出老师所有学生
sql = "select * from teacher_student ts,student s where ts.teacher_id=? and ts.teacher_id=s.id";
List<Student> list = runner.query(sql, id, new BeanListHandler<Student>(Student.class));
t.getStudents().addAll(list);
return t;
}
}
3. 功能测试
@Test
public void addTeacher() throws SQLException {
Teacher t = new Teacher();
t.setId("1");
t.setName("陈陈");
t.setSalary(10000);
Student s1 = new Student();
s1.setId("1");
s1.setName("aaa");
Student s2 = new Student();
s2.setId("2");
s2.setName("bbb");
t.getStudents().add(s1);
t.getStudents().add(s2);
TeacherDao dao = new TeacherDao();
dao.add(t);
}
@Test
public void findTeacher() throws SQLException {
TeacherDao dao = new TeacherDao();
Teacher t = dao.find("1");
System.out.println(t.getId());
System.out.println(t.getStudents());
}
网友评论