第五章第5节： 多表关系之多对多

多对多：双方实体类里都有对方的集合

 

创建数据表

CREATE TABLE `middle` (
`middleid` int(11) NOT NULL AUTO_INCREMENT,
`stuid` int(11) DEFAULT NULL, 
`subid` int(11) DEFAULT NULL,
PRIMARY KEY (`middleid`) 
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8; 
-- ---------------------------- -- Records of middle -- ----------------------------
INSERT INTO `middle` VALUES ('1', '1', '1');
INSERT INTO `middle` VALUES ('2', '1', '2');
INSERT INTO `middle` VALUES ('3', '1', '3');
INSERT INTO `middle` VALUES ('4', '1', '5');
INSERT INTO `middle` VALUES ('5', '2', '2');
INSERT INTO `middle` VALUES ('6', '3', '2'); 
INSERT INTO `middle` VALUES ('7', '4', '2');
INSERT INTO `middle` VALUES ('8', '5', '2');
INSERT INTO `middle` VALUES ('9', '6', '2'); 
-- ---------------------------- -- Table structure for `student` -- ---------------------------- 
DROP TABLE IF EXISTS `student`; 
CREATE TABLE `student` ( 
`stuid` int(11) NOT NULL AUTO_INCREMENT, 
`stuname` varchar(255) DEFAULT NULL,
`teacherid` int(11) DEFAULT NULL,
PRIMARY KEY (`stuid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; 
-- ---------------------------- -- Records of student -- ---------------------------- 
INSERT INTO `student` VALUES ('1', '张三', '3');
INSERT INTO `student` VALUES ('2', '李四', '1'); 
INSERT INTO `student` VALUES ('3', '王五', '3'); 
INSERT INTO `student` VALUES ('4', '赵六', '1'); 
INSERT INTO `student` VALUES ('5', '花花', '1');
INSERT INTO `student` VALUES ('6', '潇潇', '2');
-- ---------------------------- -- Table structure for `subject` -- ---------------------------- 
DROP TABLE IF EXISTS `subject`; 
CREATE TABLE `subject` (
`subid` int(11) NOT NULL AUTO_INCREMENT,
`subname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`subid`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; 
-- ---------------------------- -- Records of subject -- ----------------------------
INSERT INTO `subject` VALUES ('1', 'java');
INSERT INTO `subject` VALUES ('2', 'ui');
INSERT INTO `subject` VALUES ('3', 'h5'); 
INSERT INTO `subject` VALUES ('4', 'c'); 
INSERT INTO `subject` VALUES ('5', 'c++');
INSERT INTO `subject` VALUES ('6', 'c#');

创建实体类

1.Subject类

public class Subject {
    private int subid;
    private String subname;
    private List stulist;

    public int getSubid() {
        return subid;
    }

    public void setSubid(int subid) {
        this.subid = subid;
    }

    public String getSubname() {
        return subname;
    }

    public void setSubname(String subname) {
        this.subname = subname;
    }

    public List getStulist() {
        return stulist;
    }

    public void setStulist(List stulist) {
        this.stulist = stulist;
    }
}

2.Student类

public class Student {
    private int stuid;
    private String stuname; //外键列一般不生成属性 
    private int teacherid;
    private Teacher teacher;
    private List<Subject> subjects;

    public List<Subject> getSubjects() {
        return subjects;
    }

    public void setSubjects(List<Subject> subjects) {
        this.subjects = subjects;
    }

    public int getStuid() {
        return stuid;
    }

    public void setStuid(int stuid) {
        this.stuid = stuid;
    }

    public String getStuname() {
        return stuname;
    }

    public void setStuname(String stuname) {
        this.stuname = stuname;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
}

创建接口

SubjectDao接口

public interface SubjectDao { 
    //查询某个学生信息（查询出所学科目） 
    public Student findById(int id); 
    //查询某个科目以及对应的学生姓名
    public Subject findBySubId(int subId);
}

创建实现类

public class SubjectDaoImpl implements SubjectDao {
    @Override
    public Student findById(int id) { //操作数据库 
        Connection connection = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        try {//1.加载驱动 
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接 
            String userName = "root";
            String passWord = "123456";
            String url = "jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord);
            //3.定义sql,创建预状态通道(进行sql语句的发送) 
            String sql = "select * from student s,subject su,middle m where s.stuid=m.stuid and su.subid=m.subid and s.stuid=?";
            pps = connection.prepareStatement(sql);
            pps.setInt(1, id); //执行sql 
            resultSet = pps.executeQuery();
            Student student = new Student();
            List<Subject> subjects = new ArrayList<>();
            while (resultSet.next()) {
                //1.取出各自的信息
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                Subject subject = new Subject();
                subject.setSubId(resultSet.getInt("subid"));
                subject.setSubName(resultSet.getString("subname"));
                subjects.add(subject);
            }//2.建立学生和科目之间的关系
            student.setSubjects(subjects);
            return student;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5.关闭资源 
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public Subject findBySubId(int subId) {
        //操作数据库 
        Connection connection = null;
        PreparedStatement pps = null;
        ResultSet resultSet = null;
        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得链接
            String userName = "root";
            String passWord = "123456";
            String url = "jdbc:mysql://localhost:3306/yhp3?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, userName, passWord); //3.定义sql,创建预状态通道(进行sql语句的发送) 
            String sql = "select * from student s,subject su,middle m where s.stuid=m.stuid and su.subid=m.subid and su.subid=?";
            pps = connection.prepareStatement(sql);
            pps.setInt(1, subId); //执行sql 
            resultSet = pps.executeQuery();
            Subject subject = new Subject();
            List<Student> studentList = new ArrayList<>();
            while (resultSet.next()) {
                //1.取出各自的信息
                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                studentList.add(student);
                subject.setSubId(resultSet.getInt("subid"));
                subject.setSubName(resultSet.getString("subname"));
            }//2.建立学生和科目之间的关系 
            subject.setStudentList(studentList);
            return subject;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            try {
                //5.关闭资源 
                if (resultSet != null) {
                    resultSet.close();
                }
                if (pps != null) {
                    pps.close();
                }
                if (connection != null) {
                    connection.close();

                }
            } catch (
                    SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}

创建测试类

public class Demo {
    public static void main(String[] args) {
        SubjectDaoImpl subjectDao = new SubjectDaoImpl();
        /* Student student = subjectDao.findById(1);
         System.out.println(student.getStuName()); 
         List<Subject> subjects = student.getSubjects();
          for (Subject subject : subjects) {
           System.out.println("\t"+subject.getSubName());
            }*/
        Subject subject = subjectDao.findBySubId(2);
        System.out.println(subject.getSubName());
        List<Student> studentList = subject.getStudentList();
        for (Student student : studentList) {
            System.out.println("\t" + student.getStuName());
        }
    }
}

结果：

结果1：注释部分

张三

    java

    ui

    h5

    c++

结果二：

java

    张三