JavaWeb-多表关系

一对多

建立两表之间的属性关系
数据表是通过外键列来维系两表关系。实体类是通过属性来维系两表关系。一对多，是以一方为主，所以我们在一方添加多方的集合作为一个属性。
例如：年级和学生是一对多关系，那么在年级表中创建一个集合可以添加多方。
Grade.java

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

GradeDaoImp.java

public class GradeDaoImpl extends DruidUtil implements GradeDao {
@Override
	public Grade getGradeById(int id){
	//这里创建年级对象的操作要放在循环外，因为只需要创建一个年级对象即可
		Grade grade = new Grade();
		List<Student> students=new ArrayList<Student>();
		Connection connection =null;
		PreparedStatement preparedStatement =null;
		ResultSet resultSet =null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement("select * from grade g,student s
		
			where s.gid=g.gradeid and g.gradeid=?");
			preparedStatement.setInt(1,id);
			resultSet = preparedStatement.executeQuery();
			//此时结果集中包含两张表的数据，我们先分别获取各自表中的数据
			while(resultSet.next()){
				//学生信息
				Student student = new Student();
				student.setStuid(resultSet.getInt("stuid"));
				student.setStuName(resultSet.getString("stuname"));
				student.setStuAge(resultSet.getInt("stuage"));
				student.setGid(resultSet.getInt("gid"));
				//年级信息
				grade.setGname(resultSet.getString("gname"));
				grade.setGradeId(resultSet.getInt("gradeid"));
			//建立两者关系
				students.add(student);
			}
			//将学生集合封装到年级中
			grade.setStudentList(students);
		} catch (SQLException throwables) {
				throwables.printStackTrace();
		} finally {
				close(connection,preparedStatement,resultSet);
			}
		return grade;
	}
}

多对一

一方存多方的集合，多方存一方的对象。在多的一方，添加一方的一个对象作为属性
Student.java

private Grade grade;
	public Grade getGrade() {
		return grade;
}
public void setGrade(Grade grade) {
	this.grade = grade;
}

StudentDaoImp.java

public class StudentDaoImpl extends DruidUtil implements StudentDao {
@Override
	public List<Student> getAllStudent() {
		//这里创建学生集合对象，放在循环外部
		List<Student> students=new ArrayList<Student>();
		Connection connection =null;
		PreparedStatement preparedStatement =null;
		ResultSet resultSet =null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement("select * from grade g,student s
			where s.gid=g.gradeid ");
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
			//学生信息
			Student student = new Student();
			student.setStuid(resultSet.getInt("stuid"));
			student.setStuName(resultSet.getString("stuname"));
			student.setStuAge(resultSet.getInt("stuage"));
			student.setGid(resultSet.getInt("gid"));
			//年级信息
			Grade grade = new Grade();
			grade.setGname(resultSet.getString("gname"));
			grade.setGradeId(resultSet.getInt("gradeid"));
			//建立两者关系
			//将年级封装到学生中
			student.setGrade(grade);
			//将学生封装到学生集合中
			students.add(student);
			}
		} catch (SQLException throwables) {
		throwables.printStackTrace();
		} finally {
			close(connection,preparedStatement,resultSet);
		}
		return students;
	}
}

一对一

一方存另一方的对象
实现与上述同理，不同在于sql语句通过谁（A）来查找，就将另一方的对象（B）封装进A中。

多对多

多对多的关系需要建立第三张表（中间关系表）。
在一方的对象中需要创建另一方的集合作为属性。
Student.java

public class Student{
	private int studentId;
	private String studentName;
	private List<Teacher> teacherList;
	//getter and setter
}

Teacher.java

public class Teacher{
	private int teacherId;
	private String teacherName;
	private List<Student> studentList;
//getter and setter
}

MyDao.java

public interface MyDao {
//查询某个学生信息(要求包含角色对应的教师列表)
public Student findByStudentId(int studentId);
//查询某个教师信息(要求包含菜单对应的学生列表)
public Teacher findByTeacherId(int teacherId);
}

DaoImp.java

public class DaoImpl extends DruidUtil implements MyDao {
@Override
	public Role findByStudentId(int studentId) {
	//这里创建学生对象和教师集合对象
		Student student = new Student();
		List<Teacher> teachers= new ArrayList<>();
		Connection connection =null;
		PreparedStatement preparedStatement =null;
		ResultSet resultSet =null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement("select * from student s,teacher t,middle
			mid where s.studentid=mid.rid and t.teacherid= mid.mid and s.studentid=?");
			preparedStatement.setInt(1,studentId);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				//学生信息
				student.setStudentId(resultSet.getInt("studentid"));
				student.setStudentName(resultSet.getString("studentname"));
				//教师信息
				Teacher teacher = new Teacher();
				teacher.setTeacherId(resultSet.getInt("teacherid"));
				teacher.setTeacherName(resultSet.getString("teachername"));
				//建立两者关系
				//将教师添加到学生的属性中
				teachers.add(teacher);
			}
			teachers.setTeacherList(teachers);
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		} finally {
			close(connection,preparedStatement,resultSet);
		}
		return student;
	}

	@Override
	public Teacher findByTeacherId(int teacherId) {
		//创建教师对象和学生集合对象
		Teacher teacher = new Teacher();
		List<Student> students= new ArrayList<Student>();
		Connection connection =null;
		PreparedStatement preparedStatement =null;
		ResultSet resultSet =null;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement("select * from student s,teacher t,middle
			mid where s.studentid=mid.rid and t.teacherid= mid.mid and t.teacherid=?");
			preparedStatement.setInt(1,TeacherId);
			resultSet = preparedStatement.executeQuery();
			
			while(resultSet.next()){
				//学生信息
				Student student = new Role();
				student.setStudentId(resultSet.getInt("studentid"));
				student.setStudentName(resultSet.getString("studentname"));
				//菜单信息
				Teacher.setTeacherId(resultSet.getInt("teacherid"));
				Teacher.setTeacherName(resultSet.getString("teachername"));
				//建立两者关系
				//将学生添加到教师的属性中
				students.add(student);
			}
			student.setStudentList(students);
		} catch (SQLException throwables) {
			throwables.printStackTrace();
		} finally {
			close(connection,preparedStatement,resultSet);
		}
		return teacher;
	}
}