7.8.1 多表关系

我们在学习数据库的时候，了解到数据库中多表之前是存在关系的，而这种关系也是固定的，分为:一对多，多对一，一对一和多对多。那么学习完JDBC,JSP,Servlet后。如何在WEB项目中操作数据库呢？这就是我们今天研究的重点:如何使用JAVA代码实现多表关系操作。

多表操作之一对多

数据表

比较经典的一对多的关系就是学生表与年级表，两张表中，学生是多方，年级是一方。因为:一个年级可以有多名学生，但反过来一名学生只属于一个年级。先创建数据表

  1. create table student (
  2. stuid int primary key,
  3. stuname varchar(5),
  4. stuage int,
  5. gid int
  6. );
  7. create table grade(
  8. gradeid int primary key ,
  9. gname varchar(5)
  10. );
  11. insert into grade values(1,'一年级');
  12. insert into grade values(2,'二年级');
  13. insert into grade values(3,'三年级');
  14. insert into student values(1,'张三',18,1);
  15. insert into student values(2,'李四',14,2);
  16. insert into student values(3,'富贵',13,3);
  17. insert into student values(4,'王芳',17,1);
  18. insert into student values(5,'甜甜',15,2);

创建实体类

  1. package bean;
  3. public class Student {
  4.     private int stuId;
  5.     private String stuName;
  6.     private int  stuAge;
  7.     private int gid;
  9.     public Grade getGrade() {
  10.         return grade;
  11.     }
  13.     public void setGrade(Grade grade) {
  14.         this.grade = grade;
  15.     }
  17.     private Grade grade;
  18.     @Override
  19.     public String toString() {
  20.         return "Student{" +
  21.                 "stuId=" + stuId +
  22.                 ", stuName='" + stuName + '\'' +
  23.                 ", stuAge=" + stuAge +
  24.                 ", gid=" + gid +
  25.                 '}';
  26.     }
  28.     public int getStuId() {
  29.         return stuId;
  30.     }
  32.     public void setStuId(int stuId) {
  33.         this.stuId = stuId;
  34.     }
  36.     public String getStuName() {
  37.         return stuName;
  38.     }
  40.     public void setStuName(String stuName) {
  41.         this.stuName = stuName;
  42.     }
  44.     public int getStuAge() {
  45.         return stuAge;
  46.     }
  48.     public void setStuAge(int stuAge) {
  49.         this.stuAge = stuAge;
  50.     }
  52.     public int getGid() {
  53.         return gid;
  54.     }
  56.     public void setGid(int gid) {
  57.         this.gid = gid;
  58.     }
  59. }

Grade:

  1. package bean;
  3. import java.util.List;
  5. public class Grade {
  6.     private int gradeId;
  7.     private String gname;
  8.     private List<Student> studentList; //数据库中使用外键列保证两表关系，实体类中使用属性保证两表关系
  10.     @Override
  11.     public String toString() {
  12.         return "Grade{" +
  13.                 "gradeId=" + gradeId +
  14.                 ", gname='" + gname + '\'' +
  15.                 ", studentList=" + studentList +
  16.                 '}';
  17.     }
  19.     public int getGradeId() {
  20.         return gradeId;
  21.     }
  23.     public void setGradeId(int gradeId) {
  24.         this.gradeId = gradeId;
  25.     }
  27.     public String getGname() {
  28.         return gname;
  29.     }
  31.     public void setGname(String gname) {
  32.         this.gname = gname;
  33.     }
  35.     public List<Student> getStudentList() {
  36.         return studentList;
  37.     }
  39.     public void setStudentList(List<Student> studentList) {
  40.         this.studentList = studentList;
  41.     }
  42. }

建立两表之间的属性关系

数据表是通过外键列来维系两表关系。实体类是通过属性来维系两表关系。在建立一对多关系时，我们分析到年级是一方，学生是多方。一对多，是以一方为主，所以我们在一方添加多方的一个属性。那这个属性是对象还是集合呢？这里记住一句话:一方存多方的集合，多方存一方的对象。所以需要在年级表中添加下列属性：
Grade新增代码:

  1. private List<Student> studentList;
  2. public List<Student> getStudentList() {
  3.   return studentList;
  4. }
  5. public void setStudentList(List<Student> studentList) {
  6.     this.studentList = studentList;
  7. }

创建Dao层接口代码和实现类，操作数据库

Dao层

  1. package dao;
  3. import bean.Grade;
  4. import bean.Student;
  6. import java.util.List;
  8. public interface GradeDao {
  9.     //查询某个年级信息(要求同时查询出学生的信息)
  10.     public Grade findById(int gid);
  11.     //查询学生的信息(包含年级信息)
  12.     public List<Student> findAll();
  13. }

实现类:在实现类中需要连接数据库，并且查询结果来自于多张表。此时如何存储数据呢？给大家一个思路:1.在不考虑两表的情况下，先存储各自表中的数据 2.结合上面步骤中添加属性的问题，考虑应该把哪个类添加到另外一个类的属性中。代码如下：

  1. package dao.impl;
  3. import bean.Grade;
  4. import bean.Student;
  5. import dao.GradeDao;
  6. import util.DruidUtil;
  8. import java.sql.Connection;
  9. import java.sql.PreparedStatement;
  10. import java.sql.ResultSet;
  11. import java.sql.SQLException;
  12. import java.util.ArrayList;
  13. import java.util.List;
  15. public class GradeDaoImpl extends DruidUtil implements GradeDao {
  17.     @Override
  18.     public Grade findById(int gid) {
  19.         Grade grade = new Grade();
  20.         ArrayList<Student> students = new ArrayList<>();
  21.         Connection connection = null;
  22.         PreparedStatement preparedStatement = null;
  23.         ResultSet resultSet = null;
  24.         try {
  25.             connection = getConnection();
  26.             preparedStatement = connection.prepareStatement("select * from student s,grade g where s.gid=g.gradeid and g.gradeid=?");
  27.             preparedStatement.setInt(1,gid);
  28.             resultSet = preparedStatement.executeQuery();
  29.             while (resultSet.next()){
  30.                 //1.将各自的数据信息进行存储
  31.                 grade.setGradeId(resultSet.getInt("gradeid"));
  32.                 grade.setGname(resultSet.getString("gname"));
  33.                 Student student = new Student();
  34.                 student.setStuName(resultSet.getString("stuname"));
  35.                 student.setStuAge(resultSet.getInt("stuage"));
  36.                 //2.将学生信息和年级中的属性进行关联
  37.                 //将学生放到一个集合中
  38.                 students.add(student);
  39.             }
  40.             //3.建立两者关系
  41.             grade.setStudentList(students);
  42.         } catch (SQLException throwables) {
  43.             throwables.printStackTrace();
  44.         } finally {
  45.             close(connection,preparedStatement,resultSet);
  46.         }
  47.         return grade;
  48.     }
  49. }

说明:这里比较难理解的是关于对象的创建以及属性赋值

测试类

  1. public class Test1 {
  2.     public static void main(String[] args) {
  3.         GradeDao gradeDao = new GradeDaoImpl();
  4.         Grade grade = gradeDao.getGradeById(1);
  5.         System.out.println(grade.getGname());
  6.         List<Student> studentList = grade.getStudentList();
  7.         for (Student student : studentList) {
  8.             System.out.println(student);
  9.         }
  10.     }
  11. }

多表操作之多对一

在上一步的基础上，完成多对一。学生是多方，秉持着“一方存多方的集合，多方存一方的对象”，那么我们就
需要在多的一方，添加一方的一个对象。此时学生类中需要添加下列代码

  1. private Grade grade;
  2. public Grade getGrade() {
  3.     return grade;
  4. }
  5. public void setGrade(Grade grade) {
  6.     this.grade = grade;
  7. }

在Dao层添加接口方法:

  1. package dao;
  3. import bean.Grade;
  4. import bean.Student;
  6. import java.util.List;
  8. public interface GradeDao {
  9.     //查询某个年级信息(要求同时查询出学生的信息)
  10.     public Grade findById(int gid);
  11.     //查询学生的信息(包含年级信息)
  12.     public List<Student> findAll();
  13. }

添加实现类:实现类中主要考虑如何建立两者关联

  1. package dao.impl;
  3. import bean.Grade;
  4. import bean.Student;
  5. import dao.GradeDao;
  6. import util.DruidUtil;
  8. import java.sql.Connection;
  9. import java.sql.PreparedStatement;
  10. import java.sql.ResultSet;
  11. import java.sql.SQLException;
  12. import java.util.ArrayList;
  13. import java.util.List;
  15. public class GradeDaoImpl extends DruidUtil implements GradeDao {
  16.     @Override
  17.     public List<Student> findAll() {
  18.         ArrayList<Student> students = new ArrayList<>();
  19.         Connection connection = null;
  20.         PreparedStatement preparedStatement = null;
  21.         ResultSet resultSet = null;
  23.         try {
  24.             connection = getConnection();
  25.             preparedStatement = connection.prepareStatement("select * from student s,grade g where s.gid=g.gradeid ");
  26.             resultSet = preparedStatement.executeQuery(); 
  28.             while(resultSet.next()){
  29.                 //1.各自存各自的数据
  30.                 Grade grade = new Grade();
  31.                 grade.setGradeId(resultSet.getInt("gradeid"));
  32.                 grade.setGname(resultSet.getString("gname"));
  34.                 Student student = new Student();
  35.                 student.setStuName(resultSet.getString("stuname"));
  36.                 student.setStuAge(resultSet.getInt("stuage"));
  37.                 //2.关联信息(将年级放在学生中，再将学生放在集合中)
  38.                 student.setGrade(grade);
  39.                 students.add(student);
  40.             }
  41.         } catch (SQLException throwables) {
  42.             throwables.printStackTrace();
  43.         } finally {
  44.             close(connection,preparedStatement,resultSet);
  45.         }
  47.         return students;
  48.     }
  49. }

多表操作之一对一

一对一在多表关系中存在场景不是很多，现在以妻子和丈夫的关系，模拟一对一的实现过程。

创建数据表

  1. create table wife(
  2. wifeid int PRIMARY key,
  3. wifename varchar(5)
  4. );
  5. create table husband(
  6. husid int PRIMARY KEY,
  7. husname varchar(5),
  8. wid int
  9. );
  10. insert into wife values(1,'黄晓明');
  11. insert into wife values(2,'邓超');
  12. insert into husband values(1,'baby',1);
  13. insert into husband values(2,'孙俪',2);

创建实体类

  1. public class Husband {
  2.     private int husId;
  3.     private String husName;
  4.     private int wid;
  5.     //setter and getter
  6. }
  1. public class Wife {
  2.     private int wifeId;
  3.     private String wifeName;
  4.     //setter and getter
  5. }

建立实体类之间的一对一关系，还是依据“一方存多方的集合，多方存一方的对象”的原则，但是现在的问题是双方
都是一方数据，此时记住原则“一方存另一方的对象”。所以代码改成：
妻子一方添加丈夫的对象

  1. public class Wife {
  2.     private int wifeId;
  3.     private String wifeName;
  4.     private Husband husband;
  5.     //setter and getter
  6. }

丈夫一方添加妻子的对象

  1. public class Husband {
  2.     private int husId;
  3.     private String husName;
  4.     private int wid;
  5.     private Wife wife;
  6.     //setter and getter
  7. }

添加Dao和实现类

Dao

  1. public interface WifeDao {
  2.     //查询妻子信息(要求包含丈夫信息)
  3.     public Wife getByWifeId(int wifeId);
  4.     //查询丈夫信息(要求包含妻子信息)
  5.     public Husband getByHusId(int husId);
  6. }

实现类:

  1. package dao.impl;
  3. import bean.Husband;
  4. import bean.Wife;
  5. import dao.WifeDao;
  6. import util.DruidUtil;
  8. import java.sql.Connection;
  9. import java.sql.PreparedStatement;
  10. import java.sql.ResultSet;
  11. import java.sql.SQLException;
  13. public class WifeDaoImpl extends DruidUtil implements WifeDao {
  14.     @Override
  15.     public Wife findByWifeId(int wid) {
  16.         Wife wife = new Wife();
  17.         Connection connection = null;
  18.         PreparedStatement preparedStatement = null;
  19.         ResultSet resultSet = null;
  20.         try {
  22.             connection = getConnection();
  23.             preparedStatement = connection.prepareStatement("select * from wife w,husband h where w.wifeid=h.wid and w.wifeid=?");
  24.             preparedStatement.setInt(1,wid);
  25.             resultSet = preparedStatement.executeQuery();
  26.             while(resultSet.next()){
  27.                 //1.存各自的信息
  28.                 wife.setWifeName(resultSet.getString("wifename"));
  29.                 Husband husband = new Husband();
  30.                 husband.setHusName(resultSet.getString("husname"));
  31.                 //2.建立两者关系（将丈夫封装到妻子的对象中）
  32.                 wife.setHusband(husband);
  33.             }
  34.         } catch (SQLException throwables) {
  35.             throwables.printStackTrace();
  36.         } finally {
  37.             close(connection,preparedStatement,resultSet);
  38.         }
  39.         return wife;
  40.     }
  42.     @Override
  43.     public Husband findByHid(int hid) {
  44.         Husband husband = new Husband();
  45.         Connection connection = null;
  46.         PreparedStatement preparedStatement = null;
  47.         ResultSet resultSet = null;
  48.         try {
  50.             connection = getConnection();
  51.             preparedStatement = connection.prepareStatement("select * from wife w,husband h where w.wifeid=h.wid and h.husid=?");
  52.             preparedStatement.setInt(1,hid);
  53.             resultSet = preparedStatement.executeQuery();
  54.             while(resultSet.next()){
  55.                 //1.存各自的信息
  56.                 Wife wife = new Wife();
  57.                 wife.setWifeName(resultSet.getString("wifename"));
  59.                 husband.setHusName(resultSet.getString("husname"));
  60.                 //2.建立两者关系（将妻子封装到丈夫的对象中）
  61.                husband.setWife(wife);
  62.             }
  63.         } catch (SQLException throwables) {
  64.             throwables.printStackTrace();
  65.         } finally {
  66.             close(connection,preparedStatement,resultSet);
  67.         }
  68.         return husband;
  69.     }
  70. }

测试类

  1. public static void main(String[] args) {
  2.     WifeDao wifeDao = new WifeDaoImpl();
  3.     Husband husband = wifeDao.getByHusId(1);
  4.     System.out.println(husband.getHusName()+","+husband.getWife().getWifeName());
  5.     Wife wife = wifeDao.getByWifeId(2);
  6.     System.out.println(wife.getWifeName()+","+wife.getHusband().getHusName());
  7. }

多表操作之多对多

多对多在现实场景中也是不很多，比较特殊的就是权限列表的三表关系。菜单表和角色表之间属于多对多。某个功能菜单可以分配给多个角色，某个角色也可以拥有多个菜单，在这个分配过程中就是典型的多对多。在多对多中，表的创建也比较有特点，必须是基于三张表来实现。

创建数据表

  1. create table menu(
  2.     menuid int primary key,
  3.     menuname varchar(10)
  4. );
  5. create table role(
  6.     roleid int primary key,
  7.     rolename varchar(10)
  8. );
  9. create table middle(
  10.     middleid int primary key,
  11.     mid int,
  12.     rid int
  13. );
  14. insert into menu values(1,'用户管理');
  15. insert into menu values(2,'菜单管理');
  16. insert into menu values(3,'角色管理');
  17. insert into role values(1,'超级管理员');
  18. insert into role values(2,'管理员');
  19. insert into role values(3,'总经理');
  20. insert into middle values(1,1,1);
  21. insert into middle values(2,2,1);
  22. insert into middle values(3,3,1);
  23. insert into middle values(4,1,2);
  24. insert into middle values(5,2,2);
  25. insert into middle values(6,1,3);

定义实体类：中间表不需要生成实体类

Menu:

  1. public class Menu {
  2.     private int menuId;
  3.     private String menuName;
  4.     //getter and setter
  5. }

Role:

  1. public class Role {
  2.     private int roleId;
  3.     private String roleName;
  4.     //getter and setter
  5. }

建立实体类之间的多对多关系，还是依据“一方存多方的集合，多方存一方的对象”的原则，但是现在的问题是双方都是多方数据，此时记住原则“多方存另一方的集合”。代码如下:
Menu:

  1. public class Menu {
  2.     private int menuId;
  3.     private String menuName;
  4.     private List<Role> roleList;
  5.     //getter and setter
  6. }

Role:

  1. public class Role {
  2.     private int roleId;
  3.     private String roleName;
  4.     private List<Menu> menuList;
  5.     //getter and setter
  6. }

定义接口和实现类

  1. public interface RoleDao {
  2.     //查询某个角色信息(要求包含角色对应的菜单列表)
  3.     public Role findByRoleId(int roleId);
  4.     //查询某个菜单信息(要求包含菜单对应的角色列表)
  5.     public Menu findByMenuId(int menuId);
  6. }
  1. package dao.impl;
  3. import bean.Menu;
  4. import bean.Role;
  5. import dao.RoleDao;
  6. import util.DruidUtil;
  8. import java.sql.Connection;
  9. import java.sql.PreparedStatement;
  10. import java.sql.ResultSet;
  11. import java.sql.SQLException;
  12. import java.util.ArrayList;
  14. public class RoleDaoImpl extends DruidUtil implements RoleDao {
  15.     @Override
  16.     public Menu findByMenuId(int mid) {
  17.         Menu menu = new Menu();
  18.         ArrayList<Role> roles = new ArrayList<>();
  19.         Connection connection = null;
  20.         PreparedStatement preparedStatement = null;
  21.         ResultSet resultSet = null;
  23.         try {
  24.             connection = getConnection();
  25.             preparedStatement = connection.prepareStatement("select * from menu m ,role r, middle where m.menuid=middle.mid and r.roleid=middle.rid and m.menuid=?");
  26.             preparedStatement.setInt(1,mid);
  27.             resultSet = preparedStatement.executeQuery();
  28.             while(resultSet.next()){
  29.                 //1.先各自存数据
  30.                 menu.setMenuName(resultSet.getString("menuname"));
  31.                 Role role = new Role();
  32.                 role.setRoleName(resultSet.getString("rolename"));
  33.                 //2.建立二者关系
  34.                 roles.add(role);
  35.             }
  36.             menu.setRoleList(roles);
  37.         } catch (SQLException throwables) {
  38.             throwables.printStackTrace();
  39.         } finally {
  40.             close(connection,preparedStatement,resultSet);
  41.         }
  42.         return menu;
  43.     }
  45.     @Override
  46.     public Role findByRoleId(int roleid) {
  48.         Role role = new Role();
  49.         ArrayList<Menu> menuArrayList = new ArrayList<Menu>();
  50.         Connection connection = null;
  51.         PreparedStatement preparedStatement = null;
  52.         ResultSet resultSet = null;
  54.         try {
  55.             connection = getConnection();
  56.             preparedStatement = connection.prepareStatement("select * from menu m ,role r, middle where m.menuid=middle.mid and r.roleid=middle.rid and r.roleid=?");
  57.             preparedStatement.setInt(1,roleid);
  58.             resultSet = preparedStatement.executeQuery();
  59.             while(resultSet.next()){
  60.                 //1.先各自存数据
  61.                 Menu menu = new Menu();
  62.                 menu.setMenuName(resultSet.getString("menuname"));
  63.                 role.setRoleName(resultSet.getString("rolename"));
  64.                 //2.建立二者关系
  65.                 menuArrayList.add(menu);
  66.             }
  67.             role.setMenuList(menuArrayList);
  68.         } catch (SQLException throwables) {
  69.             throwables.printStackTrace();
  70.         } finally {
  71.             close(connection,preparedStatement,resultSet);
  72.         }
  73.         return role;
  74.     }
  75. }

测试类

  1. public static void main(String[] args) {
  2.     RoleDao roleDao = new RoleDaoImpl();
  3.     Role role = roleDao.findByRoleId(1);
  4.     System.out.println(role.getRoleName());
  5.     List<Menu> menuList = role.getMenuList();
  6.     for (Menu menu : menuList) {
  7.         System.out.println("\t"+menu.getMenuName());
  8.     }
  9.     Menu menu = roleDao.findByMenuId(1);
  10.     System.out.println(menu.getMenuName());
  11.     List<Role> roleList = menu.getRoleList();
  12.     for (Role role1 : roleList) {
  13.         System.out.println("\t"+role1.getRoleName());
  14.     }
  15. }