数据库关系(一对多,多对一....)

多对一和多对一

注:分别时两个接口中对应的函数,表示两种

一个老师对应多个学生的解决方法时:在Teacher中创建一个List集合用来存放Students

多个学生对应一个老师,那么也就是每个学生都是一条单独的存储:

Student中添加一个Teacher对象用来存放老师的相关信息

首先说明前提情况,

一个老师可以教学多个学生

一个学生只能由一个老师教

—>>>也就是相当于去学一个专业技能时,你只能由一个师傅

创建表

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;
INSERT INTO `student` VALUES ('1', 'aaa', '3');
INSERT INTO `student` VALUES ('2', 'bb', '1');
INSERT INTO `student` VALUES ('3', 'cc', '3');
INSERT INTO `student` VALUES ('4', 'dd', '1');
INSERT INTO `student` VALUES ('5', 'ee', '1');
INSERT INTO `student` VALUES ('6', 'ff', '2');
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(255) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
INSERT INTO `teacher` VALUES ('1', '张三老师');
INSERT INTO `teacher` VALUES ('2', '李四老师');
INSERT INTO `teacher` VALUES ('3', '王五');

1.首先要对应数据库类型创建出两个实体类对应两张表–>>bean

package bean;

/**
 * @Author: fyw
 * @Description:
 * @Date Created in 2021-08-29 1:14
 * @Modified By:
 */
//多方
public class Student {
    private int stuId;
    private String stuName;
    private int teacherId;
//多对一:在多方创建一个储存一方数据的对象
    private Teacher teacher;

    @Override
    public String toString() {
        return "Student{" +
                "stuId=" + stuId +
                ", stuName='" + stuName + '\'' +
                ", teacherId=" + teacherId +
                ", teacher=" + teacher +
                '}';
    }

    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;
    }

    public Teacher getTeacher() {
        return teacher;
    }

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

package bean;

import java.util.List;

/**
 * @Author: fyw
 * @Description:
 * @Date Created in 2021-08-29 1:14
 * @Modified By:
 */
//一方
public class Teacher {
    private int tid;
    private String tname;
    //在一方创建存储多方数据的集合
    private List<Student> studentList;

    @Override
    public String toString() {
        return "Teacher{" +
                "tid=" + tid +
                ", tname='" + tname + '\'' +
                ", studentList=" + studentList +
                '}';
    }

    public int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

    public String getTname() {
        return tname;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }

    public List<Student> getStudentList() {
        return studentList;
    }

    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }
}

2.创建对应的dao层

package dao;

import bean.Student;
import bean.Teacher;

import java.util.List;

/**
 * @Author: fyw
 * @Description:
 * @Date Created in 2021-08-29 1:26
 * @Modified By:
 */
public interface TeacherDao {
    //通过老师id查询老师相关信息(学生信息)
    public Teacher getTeacherById(int tid);
    //查询所有的学生(包含老师的信息)
    public List<Student> getListStudentAll();
}

3.实现接口中的两个方法

package daoImpl;

import bean.Student;
import bean.Teacher;
import dao.TeacherDao;

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

/**
 * @Author: fyw
 * @Description:
 * @Date Created in 2021-08-29 1:28
 * @Modified By:
 */
public class TeacherDaoImpl implements TeacherDao {
    @Override
    public Teacher getTeacherById(int tid) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //通过反射拿到jdbc的驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //
            String url = "jdbc:mysql://localhost:3306/mode05-05?serverTimezone=UTC";
            String username = "root";
            String pwd = "123456";
            connection = DriverManager.getConnection(url, username, pwd);
            String sql = "select *from `mode05-05`.teacher t,`mode05-05`.student s\n" +
                    "where s.teacherid=t.tid and t.tid=?;";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1,tid);
            resultSet = preparedStatement.executeQuery();
            Teacher teacher = new Teacher();
            List<Student> studentList = new ArrayList<>();

            while (resultSet.next()){
                teacher.setTid(resultSet.getInt("tid"));
                teacher.setTname(resultSet.getString("tname"));

                Student student = new Student();
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                student.setTeacherId(resultSet.getInt("teacherid"));
                studentList.add(student);
            }
            teacher.setStudentList(studentList);


            return teacher;
        }

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

    @Override
    public List<Student> getListStudentAll() {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        //通过反射拿到jdbc的驱动
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            //
            String url = "jdbc:mysql://localhost:3306/mode05-05?serverTimezone=UTC";
            String username = "root";
            String pwd = "123456";
            connection = DriverManager.getConnection(url, username, pwd);
            String sql = "select *from `mode05-05`.student s,`mode05-05`.teacher t where s.teacherid=t.tid;";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            List<Student> studentList = new ArrayList<>();

            while (resultSet.next()){
                Teacher teacher = new Teacher();
                Student student = new Student();
                teacher.setTname(resultSet.getString("tname"));
                teacher.setTid(resultSet.getInt("tid"));
                student.setTeacher(teacher);
                student.setTeacherId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                studentList.add(student);
            }
            return studentList;



        }

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

5.进行测试

package test;

import bean.Student;
import bean.Teacher;
import dao.TeacherDao;
import daoImpl.TeacherDaoImpl;

import java.util.List;
import java.util.ListIterator;

/**
 * @Author: fyw
 * @Description:
 * @Date Created in 2021-08-29 1:49
 * @Modified By:
 */
public class Demo01 {
    public static void main(String[] args) {
        TeacherDao dao = new TeacherDaoImpl();
        Teacher teacher = dao.getTeacherById(1);
        System.out.println("老师姓名:"+teacher.getTname());
        List<Student> studentList = teacher.getStudentList();
        for(Student s:studentList){
            System.out.println("\t学生姓名:"+s.getStuName());
        }
        List<Student> listStudentAll = dao.getListStudentAll();
        for(Student s:listStudentAll){
            System.out.println("学生姓名"+s.getStuName()+"\t老师姓名"+s.getTeacher().getTname());
        }
    }
}

数据库连接问题jdbc:https://blog.csdn.net/weixin_45653293/article/details/119975937?spm=1001.2014.3001.5502