1、iBATIS的多对多映射配置方法和多对一映射配置方法差不多,不同的是,多对多映射,数据库设计上需要一个记录两个类关系的中间表,本文以学生-老师为例,在iBATIS的sqlmap中配置多对多关系。
2、构建数据库表如下,student表,teacher表和student_teacher表:(使用mysql数据库)
student表:
teacher表:
student_teacher表:
3、生成相应的JavaBean
Student.java
Teacher.java
4、ibatis的配置文件的配置
SqlMapConfig.xml
配置连接数据库的资源文件:
jdbc.properties
studnet.xml配置文件:
teacher.xml配置文件:
5、测试类的编写
IbatisMany2Many.java
6、执行结果如下:
2、构建数据库表如下,student表,teacher表和student_teacher表:(使用mysql数据库)
student表:
- create table student(
- id int(10) not null auto_increment,
- name varchar(20) ,
- birthday varchar(20),
- primary key(id)
- );
- insert into student(name,birthday) values('张三','1982-01-01');
- insert into student(name,birthday) values('李四','1983-02-02');
- insert into student(name,birthday) values('王五','1984-03-03');
- insert into student(name,birthday) values('赵六','1985-04-04');
teacher表:
- create table teacher(
- id int(10) not null auto_increment,
- name varchar(20),
- subject varchar(20),
- primary key(id)
- );
- insert into teacher(name,subject) values('Jerry','语文');
- insert into teacher(name,subject) values('Tom','数学');
- insert into teacher(name,subject) values('Steven','英语');
student_teacher表:
- create table student_teacher(
- studentid int(10) not null,
- teacherid int(10) not null,
- primary key(studentid,teacherid)
- );
- insert into student_teacher(studentid,teacherid) values(1,1);
- insert into student_teacher(studentid,teacherid) values(1,2);
- insert into student_teacher(studentid,teacherid) values(2,1);
- insert into student_teacher(studentid,teacherid) values(3,2);
3、生成相应的JavaBean
Student.java
- package com.tonyj.pojo;
- import java.util.List;
- public class Student {
- private int id;
- private String name;
- private String birthday;
- private List<Teacher> teachers;//表示一个学生可以有多个老师教
- //相应的getter和setter 方法,构造方法
- }
Teacher.java
- package com.tonyj.pojo;
- import java.util.List;
- public class Teacher {
- private int id;
- private String name;
- private String subject;
- private List<Student> students; //表示一个老师可以教多个学生
- //相应的getter和setter方法,构造方法
- }
4、ibatis的配置文件的配置
SqlMapConfig.xml
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE sqlMapConfig
- PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
- "http://ibatis.apache.org/dtd/sql-map-config-2.dtd">
- <sqlMapConfig>
- <properties resource="jdbc.properties"/>
- <settings
- cacheModelsEnabled="true"
- enhancementEnabled="true"
- lazyLoadingEnabled="true"
- maxRequests="32"
- maxSessions="10"
- maxTransactions="5"
- useStatementNamespaces="true"/>
- <transactionManager type="JDBC">
- <dataSource type="SIMPLE">
- <property name="JDBC.Driver" value="${jdbc.driverClassName}"/>
- <property name="JDBC.ConnectionURL" value="${jdbc.url}"/>
- <property name="JDBC.Username" value="${jdbc.userName}"/>
- <property name="JDBC.Password" value="${jdbc.password}"/>
- </dataSource>
- </transactionManager>
- <sqlMap resource="teacher.xml"/>
- <sqlMap resource="student.xml"/>
- </sqlMapConfig>
配置连接数据库的资源文件:
jdbc.properties
- jdbc.driverClassName=com.mysql.jdbc.Driver
- jdbc.url=jdbc:mysql://localhost:3306/vin
- jdbc.userName=root
- jdbc.password=sa
studnet.xml配置文件:
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE sqlMap
- PUBLIC "-//ibatis.apache.org//DTD SQL MAP 2.0//EN"
- "http://ibatis.apache.org/dtd/sql-map-2.dtd">
- <sqlMap >
- <typeAlias alias="Teacher" type="com.tonyj.pojo.Teacher"/>
- <typeAlias alias="Student" type="com.tonyj.pojo.Student"/>
- <resultMap class="Student" id="studentBasicResultMap">
- <result property="id" column="id"/>
- <result property="name" column="name"/>
- <result property="birthday" column="birthday"/>
- </resultMap>
- <resultMap class="Student" id="studentWithTeacherResultMap"
- extends="studentBasicResultMap">
- <result property="teachers" column="id" select="getTeachersByStudentId"/>
- </resultMap>
- <select id="getStudents" resultMap="studentWithTeacherResultMap">
- <![CDATA[
- select * from student
- ]]>
- </select>
- <select id="getTeachersByStudentId" resultClass="Teacher" parameterClass="int">
- <![CDATA[
- select t.*
- from teacher t,student_teacher st
- where t.id=st.teacherid and st.studentid=#value#
- ]]>
- </select>
- </sqlMap>
teacher.xml配置文件:
- <?xml version="1.0" encoding="UTF-8"?>
- <!DOCTYPE sqlMap
- PUBLIC "-//ibatis.apache.org//DTD SQL MAP 2.0//EN"
- "http://ibatis.apache.org/dtd/sql-map-2.dtd">
- <sqlMap>
- <typeAlias alias="Teacher" type="com.tonyj.pojo.Teacher"/>
- <typeAlias alias="Student" type="com.tonyj.pojo.Student"/>
- <resultMap class="Teacher" id="teacherBasicResultMap">
- <result property="id" column="id"/>
- <result property="name" column="name"/>
- <result property="subject" column="subject"/>
- </resultMap>
- <!-- 下面这个resultMap中有个students属性,这个结果映射继承自上面的结果映射
- 由于有了继承,结果映射可以任意扩展 -->
- <resultMap class="Teacher" id="teacherWithTeacherResultMap"
- extends="teacherBasicResultMap">
- <result property="students" column="id" select="getStudentsByTeacherId"/>
- </resultMap>
- <!--这个查询中使用到了上面定义的结果映射,从而决定了查询出来的Teacher中关联出相关的students,
- 在student.xml中配置相似,不再注释。 -->
- <select id="getTeachers" resultMap="teacherWithTeacherResultMap">
- <![CDATA[
- select * from teacher
- ]]>
- </select>
- <select id="getStudentsByTeacherId" resultClass="Student" parameterClass="int">
- <![CDATA[
- select s.*
- from student s,student_teacher st
- where s.id=st.studentid and st.teacherid=#value#
- ]]>
- </select>
- </sqlMap>
5、测试类的编写
IbatisMany2Many.java
- package com.tonyj.test;
- import java.io.Reader;
- import java.util.List;
- import com.ibatis.common.resources.Resources;
- import com.ibatis.sqlmap.client.SqlMapClient;
- import com.ibatis.sqlmap.client.SqlMapClientBuilder;
- import com.tonyj.pojo.Student;
- import com.tonyj.pojo.Teacher;
- public class IbatisMany2Many {
- public static void main(String[] args) throws Exception {
- Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
- SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
- List<Student> studentList=sqlMap.queryForList("getStudents");
- Student stu=null;
- for(int i=0;i<studentList.size();i++){
- stu=studentList.get(i);
- System.out.println("name:"+stu.getName() + "\t" + "birthday:"
- +stu.getBirthday());
- List<Teacher> tList=stu.getTeachers();
- if(tList!=null){
- System.out.println("his teachers as follows:");
- Teacher t=null;
- for(int j=0;j<tList.size();j++){
- t=tList.get(j);
- System.out.println("teacher name:"+t.getName());
- }
- }
- }
- System.out.println("============================================");
- List<Teacher> tcherList=sqlMap.queryForList("getTeachers");
- Teacher tcher=null;
- for(int k=0;k<tcherList.size();k++){
- tcher=tcherList.get(k);
- System.out.println("name:"+tcher.getName()+"\t subject:"
- +tcher.getSubject());
- List<Student> studsList=tcher.getStudents();
- if(studsList!=null){
- System.out.println("his students as follows:");
- Student s=null;
- for(int m=0;m<studsList.size();m++){
- s=studsList.get(m);
- System.out.println("student name:"+s.getName());
- }
- }
- }
- }
- }
6、执行结果如下:
- log4j:WARN No appenders could be found for logger (com.ibatis.common.jdbc.SimpleDataSource).
- log4j:WARN Please initialize the log4j system properly.
- name:张三 birthday:1982-01-01
- his teachers as follows:
- teacher name:Jerry
- teacher name:Tom
- name:李四 birthday:1983-02-02
- his teachers as follows:
- teacher name:Jerry
- name:王五 birthday:1984-03-03
- his teachers as follows:
- teacher name:Tom
- name:赵六 birthday:1985-04-04
- his teachers as follows:
- ============================================
- name:Jerry subject:语文
- his students as follows:
- student name:张三
- student name:李四
- name:Tom subject:数学
- his students as follows:
- student name:张三
- student name:王五
- name:Steven subject:英语
- his students as follows: