以学生,课程关系来说:
对于每一个学生和课程的关系是一对多的查询,即一个学生对应多门课程
对于每一门课程和学生是一对多的关系,即一门课程对应多个学生
所以具体实现时仍然采用一对多的方式进行查询
create table t_student(
id int(6) PRIMARY key auto_increment,
name varchar(10)
);
CREATE TABLE t_course(
id int(6) PRIMARY key auto_increment,
name varchar(5)
);
#多对多关系映射表
CREATE TABLE t_student_course(
id int(6) PRIMARY key auto_increment,
sid int(6) REFERENCES t_student(id),
cid int(6) REFERENCES t_course(id)
);
学生和课程的实体类
public class Course {
private Integer id;
private String name;
private List<Student> stu;
//...set get toString
}
public class Student {
private Integer id;
private String name;
private List<Course> course;
//...set get toString
}
StudentDAO .java
public interface StudentDAO {
//查询学生信息以及选择了哪些课程
Student queryByID(Integer id);
}
学生类的配置文件 (记得配置主配置文件)
<mapper namespace="com.xxx.dao.StudentDAO">
<resultMap id="stuMap" type="com.xxx.entity.Student">
<id property="id" column="id"></id>
<result column="name" property="name"/>
<collection property="course" javaType="java.util.List" ofType= "com.xxx.entity.Course">
<id column="cid" property="id"></id>
<result column="cname" property="name"></result>
</collection>
</resultMap>
<select id="queryByID" parameterType="Int" resultMap="stuMap">
SELECT
s.id,
s.NAME,
c.NAME cname,
c.id cid
FROM
t_student s,
t_student_course sc,
t_course c
WHERE
s.id =#{id}
AND c.id = sc.cid
AND sc.sid = s.id
</select>
</mapper>
测试
public class TestStudent {
private InputStream in;
private SqlSession session;
private StudentDAO studentDAO;
@Before
public void init() throws IOException {
//1.读取配置文件
in = Resources.getResourceAsStream("SqlMapConfig.xml");
//2创建工厂
SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
SqlSessionFactory factory = builder.build(in);
session = factory.openSession();
studentDAO = session.getMapper(StudentDAO.class);
}
@After
public void destory() throws IOException {
//提交事務
session.commit();
session.close();
in.close();
}
@Test
public void TestQueryByID(){
Student stu=studentDAO.queryByID(15);
System.out.println( stu.toString());
studentDAO.queryByID(12).getCourse().forEach(course->{
System.out.println("課程信息"+course);
});
}
}