多表关系之多对多

最新推荐文章于 2022-03-13 07:30:00 发布

Fun灬小鱼

最新推荐文章于 2022-03-13 07:30:00 发布

阅读量95

点赞数

文章标签： mysql java 数据库 jdbc

本文链接：https://blog.csdn.net/m0_56187876/article/details/115435252

版权

该博客介绍了如何在Java中处理多表关系中的多对多映射，通过Student和Subject实体类展示了外键的使用，并提供了SubjectDao接口及其实现，用于查询学生的所有科目和根据科目查询相关学生的信息。测试方法演示了查询结果的输出。

摘要由CSDN通过智能技术生成

多表关系之多对多

这是表，学生和科目
在这里插入图片描述

学生

package bean;

import java.util.List;

public class Student {
    private int stuid;
    private String stuname;
    //外键列一般不生成属性
    private int teacherid;
    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 int getTeacherid() {
        return teacherid;
    }

    public void setTeacherid(int teacherid) {
        this.teacherid = teacherid;
    }
}

科目

package bean;

import java.util.List;

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

    public List<Student> getStulist() {
        return stulist;
    }

    public void setStulist(List<Student> stulist) {
        this.stulist = 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;
    }
}

接口

package dao;

import bean.Student;
import bean.Subject;

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

}

实现接口

package dao.impl;

import bean.Student;
import bean.Student1;
import bean.Subject;
import bean.Teacher;
import dao.SubjectDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SubjectDaoImpl implements SubjectDao {
    @Override
    public Student findById(int id) {
        //造作数据库
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取链接
            String useName = "root";
            String passWard = "123456";
            String url = "jdbc:mysql://localhost:3306/mysql01?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, useName, passWard);
            //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=?";
            preparedStatement = connection.prepareStatement(sql);
            //然后，给占位符赋值(下标，内容)，从1开始
            preparedStatement.setInt(1,id);
            //4.取出结果集信息
            resultSet = preparedStatement.executeQuery();
            Student student = new Student();
            List<Subject> Subjects = new ArrayList<Subject>();
            while (resultSet.next()){
                //1.取出信息
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));

                bean.Subject subject = new Subject();
                subject.setSubid(resultSet.getInt("subid"));
                subject.setSubname(resultSet.getString("subname"));
                //2.建立学生和老师之间的关系
                Subjects.add(subject);

            }
            student.setSubjects(Subjects);
            return student;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {//connection.notnull
                    connection.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


        return null;
    }

    @Override
    public Subject findBySubId(int subId) {
        //造作数据库
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            //1.加载驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获取链接
            String useName = "root";
            String passWard = "123456";
            String url = "jdbc:mysql://localhost:3306/mysql01?serverTimezone=UTC";
            connection = DriverManager.getConnection(url, useName, passWard);
            //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=?";
            preparedStatement = connection.prepareStatement(sql);
            //然后，给占位符赋值(下标，内容)，从1开始
            preparedStatement.setInt(1,subId);
            //4.取出结果集信息
            resultSet = preparedStatement.executeQuery();
            bean.Subject subject = new Subject();

            List<Student> students = new ArrayList<Student>();
            while (resultSet.next()){
                subject.setSubid(resultSet.getInt("subid"));
                subject.setSubname(resultSet.getString("subname"));

                //1.取出信息
                Student student = new Student();
                student.setStuid(resultSet.getInt("stuid"));
                student.setStuname(resultSet.getString("stuname"));
                students.add(student);

            }
            subject.setStulist(students);

            return subject;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            try {
                if (connection != null) {//connection.notnull
                    connection.close();
                }
                if (resultSet != null) {
                    resultSet.close();
                }
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }


        return null;
    }
}

测试方法

package test;

import bean.Student;
import bean.Subject;
import dao.impl.SubjectDaoImpl;

import java.util.List;

public class Demo03 {
    public static void main(String[] args) {
        SubjectDaoImpl subjectDao = new SubjectDaoImpl();
        Student byId = subjectDao.findById(1);
        System.out.println(byId.getStuname());
        List<Subject> subjects = byId.getSubjects();
        for (Subject subject : subjects) {
            System.out.println(subject.getSubname());
        }

        Subject bySubId = subjectDao.findBySubId(2);
        List<Student> stulist = bySubId.getStulist();
        System.out.println(bySubId.getSubname());
        for (Student student : stulist) {
            System.out.println(student.getStuname());
        }
    }
}