JDBC操作多张表一
一.操作一对多情况
开发步骤
1创建对象
//代码部门的对象
public class Department {
private String id;
private String name;
private Set<Employee> employees = new HashSet(); //用来保存部门中员工的集合
//代表员工的对象
}
public class Employee {
private String id;
private String name;
private double salary;
private Department department; //所属部门属性
}
2.根据对象创建表，不用考虑过多，有什么对象先创建出该对象的表
//创建部门表单
create table department
(
id varchar(40) primary key,//设置主键
name varchar(40)
);
//创建员工表单
create table employee
(
id varchar(40) primary key,
name varchar(40),
salary decimal(10,2),
department_id varchar(40),
constraint department_id_FK foreign key(department_id) references department(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; //重新设置外键，并设置外键约束，当主键删除时，外键制空
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 cascade;//重新设置外键，并设置外键约束，当主键删除时，外键删除
*/
3.编写Dao，操作数据库
public class DepartmentDao {
//对多表操作的添加方法
public void add(Department d) throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
//1.取出department对象的数据，存在department表
String sql = "insert into department(id,name) values(?,?)";
Object params[] = {d.getId(),d.getName()};
runner.update(sql, params);
//2.得到department对象中所有员工，把每一个员工的数据存到员工表
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);
}
//3.更新数据库employee表中外键列，在数据库中描述员工和部门的关系，本来应该是3步，但是这样写其实麻烦了，可以直接在保存员工信息的时候直接存到表中
}
//一对多的查询，在查询多方的数据，如果数据量一大，就千万要小心
public Department find(String id) throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
//1.查找department表，找出department对象的数据
String sql = "select * from department where id=?";
Department d = (Department) runner.query(sql, id, new BeanHandler(Department.class));
//2.查找employee ，找到部门所有员工的数据，只要不是必须显示一般不要获取，如果员工数据过大，那么很有可能导致内存溢出，在获取前一定要考虑好
//d.getEmployees().add(list); //set(list)
return d;
}
//操作多表的删除方法
public void delete(String id) throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
//删除部门
String sql = "delete from department where id=?";
runner.update(sql, id);
}
}
4.测试Dao
//把一对多的对象存到数据库
public class Demo3 {
@Test 添加
public void addDepartment() throws SQLException{
Department d = new Department();
d.setId("1");
d.setName("开发部");
Employee e1 = new Employee();
e1.setId("1");
e1.setName("aa");
e1.setSalary(1000);
Employee e2 = new Employee();
e2.setId("2");
e2.setName("bb");
e2.setSalary(1000);
d.getEmployees().add(e1);
d.getEmployees().add(e2);
//d
DepartmentDao dao = new DepartmentDao();
dao.add(d);
}
@Test 查找
public void findDepartment() throws SQLException{
DepartmentDao dao = new DepartmentDao();
Department d = dao.find("1");
System.out.println(d);
}
@Test 删除
public void delete() throws SQLException{
DepartmentDao dao = new DepartmentDao();
dao.delete("1");
}
}
二.操作多对多情况
开发步骤
1创建Bean对象类
//老师类
public class Teacher {
private String id;
private String name;
private double salary;
private Set<Student> students = new HashSet(); //保存学生的集合
//学生类
public class Student {
private String id;
private String name;
private Set<Teacher> students = new HashSet(); //保存教师的集合
2.创建对应类的表，不要考虑那么多，首先根据对象创建表，然后由于多对多情况，在创建中间表，用来记住两个表的关系
//教师表
create table teacher
(
id varchar(40) primary key,
name varchar(40),
salary decimal(10,2)
);
//学生表
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; //设置外键约束，如果主键删除，删除外键
注意这里on delete null是不可以的，因为中间表中老师和学生id是一个联合主键，必须有值，不能制空
3.开发Dao操作数据库
public class TeacherDao {
//添加方法
public void add(Teacher t) throws SQLException{
QueryRunner runner = new QueryRunner(JdbcUtils.getDataSource());
//1.取出teacher的数据存在teacher表
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.getDataSource());
//1.找老师表，找出老师的基本信息
String sql = "select * from teacher where id=?";
Teacher t= (Teacher) runner.query(sql, id, new BeanHandler(Teacher.class));
//2.找中间表，找出老师所有的学生（id）
sql = "select s.* from teacher_student ts,student s where ts.teacher_id=? and s.id=ts.student_id";
List list = (List) runner.query(sql, id, new BeanListHandler(Student.class));
t.getStudents().addAll(list);
return t;
}
4.测试Dao
//多对多对象的操作
public class Demo4 {
@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);
}
}
三.一对一操作情况（其实就是1对多的特殊情况）
开发步骤
1.创建Bean对象
//身份证对象
public class Idcard {
private String id;
private String address;
private Person person;
//人对象
public class Person {
private String id;
private String name;
private Idcard idcard;
2.创建表
//person表
create table person
(
id varchar(40) primary key,
name varchar(40)
) ;
//身份证表
create table idcard
(
id varchar(40) primary key,
address varchar(40),
constraint id_FK foreign key(id) references person(id) //将主键同时设置成外键，这样实现了，不能为空，不能重复，和关系，从而形成一对一关系
);
Tip：注意事项
不管java的对象存在何种关系，反映到关系型数据库中，都是使用外键表示纪录（即对象）的关联关系。
设计java对象如涉及到多个对象相互引用，要尽量避免使用一对多，或多对多关系，而应使用多对一描述对象之间的关系(或使用延迟加载的方式)。
