目录
1、数据库表与表之间的关系
一对一：在任意一个表中设置一个外键
一对多：
在多的表中设置外键，标记少的表中id
多对多：（学生--选课）
??????? 建立关系表
2、项目运用
一、一对一?? 多对一
查询每个学生的对应的老师
?????????
查询每个学生的对应的老师
SELECT s.id,s.Sname,t.Tname FROM student s,teacher t where s.t_id = t.id
在项目中，建立两个javaBeen，一个是Student.java，另一个是Teacher.java文件
将数据库中的字段设置为私有，并把teacher字段设置到Student.java中。设置Get()、Set()、ToString()方法。
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qcby.dao.StudentDao">
<!--
1.查询出所有老师学生的信息
2.根据查询出来的t_id进行关联
-->
<select id="getStudent" resultMap="StudentTeacher">
select * from student;
</select>
<resultMap id="StudentTeacher" type="com.qcby.enty.Student">
<id property="id" column="id"/>
<result property="Sname" column="Sname"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<result property="t_id" column="t_id"/>
<!--property: 指定的student类当中关联的字段-->
<!--column ：两个表的关联字段-->
<!--javaType： 复杂属性的类型 -->
<!--select： 调用方法 -->
<association property="teacher" column="t_id" javaType="com.qcby.enty.Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="com.qcby.enty.Teacher">
select * from teacher where id = #{t_id}
</select>
<!--第二种-->
<!-- 按照结果嵌套处理-->
<select id="getStudent1" resultMap="StudentTeacher1">
SELECT student.id,student.Sname,teacher.Tname FROM student LEFT JOIN teacher on student.t_id = teacher.id
</select>
<resultMap id="StudentTeacher1" type="com.qcby.enty.Student">
<result property="id" column="id"/>
<result property="Sname" column="Sname"/>
<result property="sex" column="sex"/>
<result property="age" column="age"/>
<result property="t_id" column="t_id"/>
<association property="teacher" javaType="com.qcby.enty.Teacher">
<result property="id" column="id"/>
<result property="Tname" column="Tname"/>
</association>
</resultMap>
</mapper>StudentTest.java
package com.qcby.test;
import com.qcby.dao.StudentDao;
import com.qcby.dao.UserDao;
import com.qcby.enty.Student;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class StudentTest {
private InputStream in = null;
private SqlSession session = null;
private StudentDao mapper = null;
@Before //前置通知, 在方法执行之前执行
public void init() throws IOException {
//加载主配置文件，目的是为了构建SqlSessionFactory对象
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//创建SqlSessionFactory对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//通过SqlSessionFactory工厂对象创建SqlSesssion对象
session = factory.openSession();
//通过Session创建UserDao接口代理对象
mapper = session.getMapper(StudentDao.class);
}
@After //@After: 后置通知, 在方法执行之后执行 。
public void destory() throws IOException {
//释放资源
session.close();
in.close();
}
@Test
public void getStudent(){
List<Student> students = mapper.getStudent();
for (Student student:students){
System.out.println(student.toString());
}
}
@Test
public void getStudent1(){
List<Student> students = mapper.getStudent();
for (Student student:students){
System.out.println(student.toString());
}
}
}
StudentDao.java
package com.qcby.dao;
import com.qcby.enty.Student;
import java.util.List;
public interface StudentDao {
List<Student> getStudent();
List<Student> getStudent1();
}
?
二、一对多
查询每个老师有多少学生
在Teacher.java中加入? private List<Student> student;并生成Get()、Set()、ToString()方法。
建立一个TeacherMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.qcby.dao.TeacherDao">
<select id="getTeacher" resultMap="TeacherStudent">
select * from teacher;
</select>
<resultMap id="TeacherStudent" type="com.qcby.enty.Teacher">
<id property="id" column="id"/>
<result property="Tname" column="Tname"/>
<!--针对集合-->
<!--property: 指定的student类当中关联的字段-->
<!--column ：该表的关联字段-->
<!--javaType： 相关字段类型 -->
<!--ofType ：泛型的具体类型-->
<!--select： 调用方法 -->
<collection property="student" column="id" javaType="ArrayList" ofType="com.qcby.enty.Student"
select="getStudentByTeacherTd"/>
</resultMap>
<select id="getStudentByTeacherTd" resultType="com.qcby.enty.Student">
select * from student where t_id = #{t_id}
</select>
</mapper>注意：这个时候要在SqlMapConfig.xml中加入关联的语句
<mapper resource="mapper/TeacherMapper.xml"></mapper>TeacherDao.java
package com.qcby.dao;
import com.qcby.enty.Teacher;
import java.util.List;
public interface TeacherDao {
List<Teacher> getTeacher();
}
TeacherTest.java
package com.qcby.test;
import com.qcby.dao.StudentDao;
import com.qcby.dao.TeacherDao;
import com.qcby.enty.Student;
import com.qcby.enty.Teacher;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
public class TeacherTest {
private InputStream in = null;
private SqlSession session = null;
private TeacherDao mapper = null;
@Before //前置通知, 在方法执行之前执行
public void init() throws IOException {
//加载主配置文件，目的是为了构建SqlSessionFactory对象
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//创建SqlSessionFactory对象
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
//通过SqlSessionFactory工厂对象创建SqlSesssion对象
session = factory.openSession();
//通过Session创建UserDao接口代理对象
mapper = session.getMapper(TeacherDao.class);
}
@After //@After: 后置通知, 在方法执行之后执行 。
public void destory() throws IOException {
//释放资源
session.close();
in.close();
}
@Test
public void getTeacher(){
List<Teacher> teachers = mapper.getTeacher();
for (Teacher teacher:teachers
) {
System.out.println(teacher.toString());
}
}
}
运行结果：
?