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对象如涉及到多个对象相互引用,要尽量避免使用一对多,或多对多关系,而应使用多对一描述对象之间的关系(或使用延迟加载的方式)。
郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。