多表的增删改查
注: 使用了DBUtil框架来操作数据库,使用数据库的可视化工具来观看结果
不管java的对象存在何种关系,反映到关系型数据库中,都是使用外键表示纪录(即对象)的关联关系。
设计java对象如涉及到多个对象相互引用,要尽量避免使用一对多,
或多对多关系,而应使用多对一描述对象之间的关系(或使用延迟加载的方式)。
1、一对多 (部门与员工)
********************************************************************************************************
------------------------------------------------------------------------------------------------------------
1.1 数据库
use day17;
create table department(
id int primary key,
name varchar(100)
);
create table employee(
id int primary key,
name varchar(100),
salary float(8,2),
dept_id int,
constraint dept_id_fk foreign key(dept_id) references department(id)
);
------------------------------------------------------------------------------------------------------------
1.2 javaBean
public class Department implements Serializable {
// 定义为Integer类型,默认为Null 则新建对象时,执行保存操作
// 若为int类型,默认值为0,则新建对象执行修改操作
private Integer id;
private String name;
private List<Employee> emps = new ArrayList<Employee>(); // 一个部门对应多个员工
// 此处省略setter和getter方法
}
public class Employee implements Serializable {
private Integer id;
private String name;
private float salary;
private Department dept; // 多个员工对应一个部门
// 此处省略setter和getter方法
}
------------------------------------------------------------------------------------------------------------
1.3、 dao层实现添加和查询操作
public class DepartmentDaoImpl {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void addDepartment(Department d) throws Exception{
// 保存部门的基本信息到department表中
qr.update("insert into department (id,name) values(?,?)",d.getId(),d.getName());
// 判断d中有没有员工信息
List<Employee> emps = d.getEmps();
if(emps!=null&&emps.size()>0){
// 如果有,保存员工信息到employee表中
Object params[][] = new Object[emps.size()][];
for(int i=0;i<params.length;i++){
Employee e = emps.get(i);
params[i] = new Object[]{e.getId(),e.getName(),e.getSalary(),d.getId()};
}
qr.batch("insert into employee (id,name,salary,dept_id) values(?,?,?,?)", params);
}
}
public Department findDepartment(Integer id) throws Exception{
// 查询部门的基本信息
Department d = qr.query("select * from department where id=?",
new BeanHandler<Department>(Department.class), id);
// 部门关联的员工信息要不要查出来,多的一方要不要查询视需求而定
if(d!=null){
List<Employee> emps = qr.query("select * from employee where dept_id=?",
new BeanListHandler<Employee>(Employee.class), id);
d.setEmps(emps);
}
return d;
}
}
------------------------------------------------------------------------------------------------------------
1.4、 dao层测试
public class DepartmentDaoImplTest {
private DepartmentDaoImpl dao = new DepartmentDaoImpl();
@Test
public void testAddDepartment() throws Exception {
Department d = new Department();
d.setId(1);
d.setName("开发部");
Employee e1 = new Employee();
e1.setId(1);
e1.setName("葛付以");
e1.setSalary(8000);
Employee e2 = new Employee();
e2.setId(2);
e2.setName("王祥云");
e2.setSalary(8000);
// 建立联系
d.getEmps().add(e1);
d.getEmps().add(e2);
dao.addDepartment(d);
}
@Test
public void testFindDepartment() throws Exception {
Department d = dao.findDepartment(1);
System.out.println(d.getName());
List<Employee> emps = d.getEmps();
for(Employee e : emps){
System.out.println(e.getName());
}
}
}
------------------------------------------------------------------------------------------------------------
********************************************************************************************************
2、多对多 (老师与学生)
********************************************************************************************************
------------------------------------------------------------------------------------------------------------
2.1 数据库
create table teacher(
id int primary key,
name varchar(100),
money float(8,2)
);
create table student(
id int primary key,
name varchar(100),
grade varchar(10)
);
create table teacher_student(
t_id int,
s_id int,
primary key(t_id,s_id), // 联合主键
constraint t_id_fk foreign key(t_id) references teacher(id),
constraint s_id_fk foreign key(s_id) references student(id)
);
------------------------------------------------------------------------------------------------------------
2.2 javaBean
public class Teacher implements Serializable {
private Integer id;
private String name;
private float money;
private List<Student> stus = new ArrayList<Student>();
// 此处省略setter和getter方法
}
public class Student implements Serializable {
private Integer id;
private String name;
private String grade;
private List<Teacher> ts = new ArrayList<Teacher>();
// 此处省略setter和getter方法
}
------------------------------------------------------------------------------------------------------------
2.3、 dao层实现添加和查询操作
public class TeacherDaoImpl {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void addTeacher(Teacher t) throws Exception{
// 保存老师的基本信息
qr.update("insert into teacher (id,name,money) values(?,?,?)",
t.getId(),t.getName(),t.getMoney());
// 判断t中是否有学生
List<Student> stus = t.getStus();
// 有学生: 判断该学生是否在student表中: 在,不添加; 不在,添加进去
if(stus!=null&&stus.size()>0){
for(Student s : stus){
// 判断学生是否已在数据库中,若不在,则返回null
Object num = qr.query("select 1 from student where id=?", new ScalarHandler(1),s.getId());
if(num==null){
qr.update("insert into student (id,name,grade) values(?,?,?)", s.getId(),s.getName(),s.getGrade());
}
// 在第三张表中建立老师和学生的关系
qr.update("insert into teacher_student (t_id,s_id) values(?,?)",t.getId(),s.getId());
}
}
}
public Teacher findTeacher(Integer id) throws SQLException{
// 查询老师的基本信息
Teacher t = qr.query("select * from teacher where id=?", new BeanHandler<Teacher>(Teacher.class), id);
if(t!=null){
// 根据老师的id查询学生的基本信息 方式有三种: 子查询/隐式内连接/显示内连接
String sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";
// String sql = "select s.* from student s,teacher_student ts where s.id=ts.s_id and ts.t_id=?";
// String sql = "select s.* from student s inner join teacher_student ts on s.id=ts.s_id where ts.t_id=?";
List<Student> stus = qr.query(sql, new BeanListHandler<Student>(Student.class), id);
t.setStus(stus);
}
return t;
}
}
------------------------------------------------------------------------------------------------------------
2.4、 dao层测试
public class TeacherDaoImplTest {
private TeacherDaoImpl dao = new TeacherDaoImpl();
@Test
public void testAddTeacher() throws Exception {
Teacher t1 = new Teacher();
t1.setId(1);
t1.setName("bxd");
t1.setMoney(20000);
Teacher t2 = new Teacher();
t2.setId(2);
t2.setName("wzt");
t2.setMoney(15000);
Student s1 = new Student();
s1.setId(1);
s1.setName("gfy");
s1.setGrade("A");
Student s2 = new Student();
s2.setId(2);
s2.setName("wxy");
s2.setGrade("A");
t1.getStus().add(s1);
t1.getStus().add(s2);
t2.getStus().add(s1);
t2.getStus().add(s2);
dao.addTeacher(t1);
dao.addTeacher(t2);
}
@Test
public void testFindTeacher() throws Exception {
Teacher t = dao.findTeacher(2);
System.out.println(t.getName());
List<Student> stus = t.getStus();
for(Student s : stus){
System.out.println(s.getName());
}
}
}
------------------------------------------------------------------------------------------------------------
********************************************************************************************************
3、一对一
********************************************************************************************************
------------------------------------------------------------------------------------------------------------
3.1 数据库
create table person(
id int primary key,
name varchar(100)
);
create table idcard(
id int primary key,
num varchar(20),
constraint person_id_fk foreign key(id) references person(id)
);
------------------------------------------------------------------------------------------------------------
3.2 javaBean
public class Person implements Serializable {
private Integer id;
private String name;
private IdCard idcard;
// 此处省略setter和getter方法
}
public class IdCard implements Serializable {
private Integer id;
private String num;
private Person person;
// 此处省略setter和getter方法
}
------------------------------------------------------------------------------------------------------------
3.3 dao层实现添加和查询操作
public class PersonDaoImpl {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void addPerson(Person p) throws SQLException{
// 保存人的基本信息
qr.update("insert into person (id,name) values(?,?)",p.getId(),p.getName());
// 如果有身份证,保存身份证的基本信息
IdCard card = p.getIdcard();
if(card!=null){
qr.update("insert into idcard (id,num) values(?,?)", p.getId(),card.getNum());
}
}
// 注: 要不要查IdCard的内容: 都要求查,因为IdCard是少的一方,内存占据很小
public Person findPerson(Integer id) throws SQLException{
Person p = qr.query("select * from person where id=?", new BeanHandler<Person>(Person.class), id);
if(p!=null){
IdCard idcard = qr.query("select * from idcard where id=?", new BeanHandler<IdCard>(IdCard.class), id);
p.setIdcard(idcard);
}
return p;
}
}
------------------------------------------------------------------------------------------------------------
3.4 dao层进行测试
public class PersonDaoImplTest {
private PersonDaoImpl dao = new PersonDaoImpl();
@Test
public void testAddPerson() throws SQLException {
Person p = new Person();
p.setId(1);
p.setName("zqy");
IdCard idcard = new IdCard();
idcard.setNum("110");
p.setIdcard(idcard);
dao.addPerson(p);
}
@Test
public void testFindPerson() throws SQLException{
Person p = dao.findPerson(1);
System.out.println(p.getName());
IdCard idcard = p.getIdcard();
System.out.println(idcard.getNum());
}
}
------------------------------------------------------------------------------------------------------------
********************************************************************************************************
注: 使用了DBUtil框架来操作数据库,使用数据库的可视化工具来观看结果
不管java的对象存在何种关系,反映到关系型数据库中,都是使用外键表示纪录(即对象)的关联关系。
设计java对象如涉及到多个对象相互引用,要尽量避免使用一对多,
或多对多关系,而应使用多对一描述对象之间的关系(或使用延迟加载的方式)。
1、一对多 (部门与员工)
********************************************************************************************************
------------------------------------------------------------------------------------------------------------
1.1 数据库
use day17;
create table department(
id int primary key,
name varchar(100)
);
create table employee(
id int primary key,
name varchar(100),
salary float(8,2),
dept_id int,
constraint dept_id_fk foreign key(dept_id) references department(id)
);
------------------------------------------------------------------------------------------------------------
1.2 javaBean
public class Department implements Serializable {
// 定义为Integer类型,默认为Null 则新建对象时,执行保存操作
// 若为int类型,默认值为0,则新建对象执行修改操作
private Integer id;
private String name;
private List<Employee> emps = new ArrayList<Employee>(); // 一个部门对应多个员工
// 此处省略setter和getter方法
}
public class Employee implements Serializable {
private Integer id;
private String name;
private float salary;
private Department dept; // 多个员工对应一个部门
// 此处省略setter和getter方法
}
------------------------------------------------------------------------------------------------------------
1.3、 dao层实现添加和查询操作
public class DepartmentDaoImpl {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void addDepartment(Department d) throws Exception{
// 保存部门的基本信息到department表中
qr.update("insert into department (id,name) values(?,?)",d.getId(),d.getName());
// 判断d中有没有员工信息
List<Employee> emps = d.getEmps();
if(emps!=null&&emps.size()>0){
// 如果有,保存员工信息到employee表中
Object params[][] = new Object[emps.size()][];
for(int i=0;i<params.length;i++){
Employee e = emps.get(i);
params[i] = new Object[]{e.getId(),e.getName(),e.getSalary(),d.getId()};
}
qr.batch("insert into employee (id,name,salary,dept_id) values(?,?,?,?)", params);
}
}
public Department findDepartment(Integer id) throws Exception{
// 查询部门的基本信息
Department d = qr.query("select * from department where id=?",
new BeanHandler<Department>(Department.class), id);
// 部门关联的员工信息要不要查出来,多的一方要不要查询视需求而定
if(d!=null){
List<Employee> emps = qr.query("select * from employee where dept_id=?",
new BeanListHandler<Employee>(Employee.class), id);
d.setEmps(emps);
}
return d;
}
}
------------------------------------------------------------------------------------------------------------
1.4、 dao层测试
public class DepartmentDaoImplTest {
private DepartmentDaoImpl dao = new DepartmentDaoImpl();
@Test
public void testAddDepartment() throws Exception {
Department d = new Department();
d.setId(1);
d.setName("开发部");
Employee e1 = new Employee();
e1.setId(1);
e1.setName("葛付以");
e1.setSalary(8000);
Employee e2 = new Employee();
e2.setId(2);
e2.setName("王祥云");
e2.setSalary(8000);
// 建立联系
d.getEmps().add(e1);
d.getEmps().add(e2);
dao.addDepartment(d);
}
@Test
public void testFindDepartment() throws Exception {
Department d = dao.findDepartment(1);
System.out.println(d.getName());
List<Employee> emps = d.getEmps();
for(Employee e : emps){
System.out.println(e.getName());
}
}
}
------------------------------------------------------------------------------------------------------------
********************************************************************************************************
2、多对多 (老师与学生)
********************************************************************************************************
------------------------------------------------------------------------------------------------------------
2.1 数据库
create table teacher(
id int primary key,
name varchar(100),
money float(8,2)
);
create table student(
id int primary key,
name varchar(100),
grade varchar(10)
);
create table teacher_student(
t_id int,
s_id int,
primary key(t_id,s_id), // 联合主键
constraint t_id_fk foreign key(t_id) references teacher(id),
constraint s_id_fk foreign key(s_id) references student(id)
);
------------------------------------------------------------------------------------------------------------
2.2 javaBean
public class Teacher implements Serializable {
private Integer id;
private String name;
private float money;
private List<Student> stus = new ArrayList<Student>();
// 此处省略setter和getter方法
}
public class Student implements Serializable {
private Integer id;
private String name;
private String grade;
private List<Teacher> ts = new ArrayList<Teacher>();
// 此处省略setter和getter方法
}
------------------------------------------------------------------------------------------------------------
2.3、 dao层实现添加和查询操作
public class TeacherDaoImpl {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void addTeacher(Teacher t) throws Exception{
// 保存老师的基本信息
qr.update("insert into teacher (id,name,money) values(?,?,?)",
t.getId(),t.getName(),t.getMoney());
// 判断t中是否有学生
List<Student> stus = t.getStus();
// 有学生: 判断该学生是否在student表中: 在,不添加; 不在,添加进去
if(stus!=null&&stus.size()>0){
for(Student s : stus){
// 判断学生是否已在数据库中,若不在,则返回null
Object num = qr.query("select 1 from student where id=?", new ScalarHandler(1),s.getId());
if(num==null){
qr.update("insert into student (id,name,grade) values(?,?,?)", s.getId(),s.getName(),s.getGrade());
}
// 在第三张表中建立老师和学生的关系
qr.update("insert into teacher_student (t_id,s_id) values(?,?)",t.getId(),s.getId());
}
}
}
public Teacher findTeacher(Integer id) throws SQLException{
// 查询老师的基本信息
Teacher t = qr.query("select * from teacher where id=?", new BeanHandler<Teacher>(Teacher.class), id);
if(t!=null){
// 根据老师的id查询学生的基本信息 方式有三种: 子查询/隐式内连接/显示内连接
String sql = "select * from student where id in (select s_id from teacher_student where t_id=?)";
// String sql = "select s.* from student s,teacher_student ts where s.id=ts.s_id and ts.t_id=?";
// String sql = "select s.* from student s inner join teacher_student ts on s.id=ts.s_id where ts.t_id=?";
List<Student> stus = qr.query(sql, new BeanListHandler<Student>(Student.class), id);
t.setStus(stus);
}
return t;
}
}
------------------------------------------------------------------------------------------------------------
2.4、 dao层测试
public class TeacherDaoImplTest {
private TeacherDaoImpl dao = new TeacherDaoImpl();
@Test
public void testAddTeacher() throws Exception {
Teacher t1 = new Teacher();
t1.setId(1);
t1.setName("bxd");
t1.setMoney(20000);
Teacher t2 = new Teacher();
t2.setId(2);
t2.setName("wzt");
t2.setMoney(15000);
Student s1 = new Student();
s1.setId(1);
s1.setName("gfy");
s1.setGrade("A");
Student s2 = new Student();
s2.setId(2);
s2.setName("wxy");
s2.setGrade("A");
t1.getStus().add(s1);
t1.getStus().add(s2);
t2.getStus().add(s1);
t2.getStus().add(s2);
dao.addTeacher(t1);
dao.addTeacher(t2);
}
@Test
public void testFindTeacher() throws Exception {
Teacher t = dao.findTeacher(2);
System.out.println(t.getName());
List<Student> stus = t.getStus();
for(Student s : stus){
System.out.println(s.getName());
}
}
}
------------------------------------------------------------------------------------------------------------
********************************************************************************************************
3、一对一
********************************************************************************************************
------------------------------------------------------------------------------------------------------------
3.1 数据库
create table person(
id int primary key,
name varchar(100)
);
create table idcard(
id int primary key,
num varchar(20),
constraint person_id_fk foreign key(id) references person(id)
);
------------------------------------------------------------------------------------------------------------
3.2 javaBean
public class Person implements Serializable {
private Integer id;
private String name;
private IdCard idcard;
// 此处省略setter和getter方法
}
public class IdCard implements Serializable {
private Integer id;
private String num;
private Person person;
// 此处省略setter和getter方法
}
------------------------------------------------------------------------------------------------------------
3.3 dao层实现添加和查询操作
public class PersonDaoImpl {
private QueryRunner qr = new QueryRunner(DBCPUtil.getDataSource());
public void addPerson(Person p) throws SQLException{
// 保存人的基本信息
qr.update("insert into person (id,name) values(?,?)",p.getId(),p.getName());
// 如果有身份证,保存身份证的基本信息
IdCard card = p.getIdcard();
if(card!=null){
qr.update("insert into idcard (id,num) values(?,?)", p.getId(),card.getNum());
}
}
// 注: 要不要查IdCard的内容: 都要求查,因为IdCard是少的一方,内存占据很小
public Person findPerson(Integer id) throws SQLException{
Person p = qr.query("select * from person where id=?", new BeanHandler<Person>(Person.class), id);
if(p!=null){
IdCard idcard = qr.query("select * from idcard where id=?", new BeanHandler<IdCard>(IdCard.class), id);
p.setIdcard(idcard);
}
return p;
}
}
------------------------------------------------------------------------------------------------------------
3.4 dao层进行测试
public class PersonDaoImplTest {
private PersonDaoImpl dao = new PersonDaoImpl();
@Test
public void testAddPerson() throws SQLException {
Person p = new Person();
p.setId(1);
p.setName("zqy");
IdCard idcard = new IdCard();
idcard.setNum("110");
p.setIdcard(idcard);
dao.addPerson(p);
}
@Test
public void testFindPerson() throws SQLException{
Person p = dao.findPerson(1);
System.out.println(p.getName());
IdCard idcard = p.getIdcard();
System.out.println(idcard.getNum());
}
}
------------------------------------------------------------------------------------------------------------
********************************************************************************************************