iBATIS的多对多 数据库设计及实现
iBATIS的多对多映射配置方法和多对一映射配置方法差不多，不同的是，多对多映射，数据库设计上需要一个记录两个类关系的中间表，本文以学生-老师为例，在iBATIS的sqlmap中配置多对多关系。
iBATIS的多对多映射配置1，建表。数据库中三个表，分别为：
1 CREATE TABLE [student] ( 2 [id] [int] IDENTITY (1, 1) NOT NULL , 3 [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , 4 [birthday] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , 5 CONSTRAINT [PK_student] PRIMARY KEY CLUSTERED 6 ( 7 [id] 8 ) ON [PRIMARY] 9 ) ON [PRIMARY] 10 GO 11 -------------------------------------------------- 12 CREATE TABLE [teacher] ( 13 [id] [int] IDENTITY (1, 1) NOT NULL , 14 [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , 15 [subject] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , 16 CONSTRAINT [PK_teacher] PRIMARY KEY CLUSTERED 17 ( 18 [id] 19 ) ON [PRIMARY] 20 ) ON [PRIMARY] 21 GO 22 --------------------------------------------------- 23 CREATE TABLE [student_teacher] ( 24 [studentid] [int] NOT NULL , 25 [teacherid] [int] NOT NULL , 26 CONSTRAINT [PK_student_teacher] PRIMARY KEY CLUSTERED 27 ( 28 [studentid], 29 [teacherid] 30 ) ON [PRIMARY] 31 ) ON [PRIMARY] 32 GO
iBATIS的多对多映射配置2，准备数据
1 insert into student(name,birthday) values('张三','1982-01-01') 2 insert into student(name,birthday) values('李四','1983-02-02') 3 insert into student(name,birthday) values('王五','1984-03-03') 4 5 insert into student(name,birthday) values('赵六','1985-04-04') 6 insert into teacher(name,subject) values('Jerry','语文') 7 insert into teacher(name,subject) values('Tom','数学') 8 9 insert into teacher(name,subject) values('Steven','英语') 10 insert into student_teacher(studentid,teacherid) values(1,1) 11 insert into student_teacher(studentid,teacherid) values(1,2) 12 insert into student_teacher(studentid,teacherid) values(2,1) 13 insert into student_teacher(studentid,teacherid) values(3,2)
iBATIS的多对多映射配置3，properties文件内容如下：
1 driver=com.microsoft.jdbc.sqlserver.SQLServerDriver 2 url=jdbc:microsoft:sqlserver://localhost:1433;SelectMethod=cursor;DatabaseName=ibatistest 3 username=sa 4 password=000
iBATIS的多对多映射配置4，总配置文件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="com/lsm/cfg/jdbc.properties" /﹥ ﹤transactionManager type="JDBC"﹥ ﹤dataSource type="SIMPLE"﹥ ﹤property value="${driver}" name="JDBC.Driver" /﹥ ﹤property value="${url}" name="JDBC.ConnectionURL" /﹥ ﹤property value="${username}" name="JDBC.Username" /﹥ ﹤property value="${password}" name="JDBC.Password" /﹥ ﹤/dataSource﹥ ﹤/transactionManager﹥ ﹤sqlMap resource="com/lsm/domain/Student.xml" /﹥ ﹤sqlMap resource="com/lsm/domain/Teacher.xml" /﹥ ﹤/sqlMapConfig﹥ iBATIS的多对多映射配置5，domain对象两个，Student 和 Teacher，如下： Teacher.java package com.lsm.domain; import java.util.List; public class Teacher { private int id; private String name; private String subject; private List students; //注意这里有个List类型的students，表示一个老师对应多个学生 public List getStudents() { return students; } public void setStudents(List students) { this.students = students; } //省略掉其他的getter and setter } //Student.java package com.lsm.domain; import java.util.List; public class Student { private int id; private String name; private String birthday; private List teachers; //这里有一个list类型的teachers，表示一个学生有多个老师 public List getTeachers() { return teachers; } public void setTeachers(List teachers) { this.teachers = teachers; } //省略掉其他的getter and setter }
iBATIS的多对多映射配置6，sqlmap配置文件
Teacher.xml
1 ﹤?xml version="1.0" encoding="UTF-8" ?﹥ 2 !DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" 3 "http://ibatis.apache.org/dtd/sql-map-2.dtd"﹥ 4 5 ﹤sqlMap namespace="teacher"﹥ 6 ﹤typeAlias alias="Teacher" type="com.lsm.domain.Teacher" /﹥ 7 ﹤typeAlias alias="Student" type="com.lsm.domain.Student" /﹥ 8 9 ﹤resultMap class="Teacher" id="teacherBasicResultMap"﹥ 10 ﹤result property="id" column="id"/﹥ 11 ﹤result property="name" column="name"/﹥ 12 ﹤result property="subject" column="subject"/﹥ 13 ﹤/resultMap﹥ 14 ﹤!-- 下面这个resultMap中有个students属性，这个结果映射继承自上面的结果映射 15 由于有了继承，结果映射可以任意扩展--﹥ 16 ﹤resultMap class="Teacher" id="teacherWithTeacherResultMap" extends="teacherBasicResultMap"﹥ 17 ﹤result property="students" column="id" select="getStudentsByTeacherId"/﹥ 18 ﹤/resultMap﹥ 19 ﹤!-- 这个查询中使用到了上面定义的结果映射，从而决定了查询出来的Teacher中关联出相关的students，在student.xml中配置相似，不再注释。--﹥ 20 ﹤select id="getTeachers" resultMap="teacherWithTeacherResultMap"﹥ 21 ﹤!--[CDATA[ 22 select * from teacher 23 ]]﹥ 24 ﹤/select﹥ 25 26 ﹤select id="getStudentsByTeacherId" resultClass="Student"﹥ 27 ﹤![CDATA[ 28 select s.* from student s,student_teacher st where s.id=st.studentid and st.teacherid=#value# ]]--﹥ 29 ﹤/select﹥ 30 31 ﹤/sqlMap﹥ 32 33 tudent.xml 34 35 ﹤?xml version="1.0" encoding="UTF-8" ?﹥ 36 !DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" 37 "http://ibatis.apache.org/dtd/sql-map-2.dtd"﹥ 38 39 ﹤sqlMap namespace="student"﹥ 40 ﹤typeAlias alias="Student" type="com.lsm.domain.Student" /﹥ 41 ﹤typeAlias alias="Teacher" type="com.lsm.domain.Teacher" /﹥ 42 43 ﹤resultMap class="Student" id="studentBasicResultMap"﹥ 44 ﹤result property="id" column="id"/﹥ 45 ﹤result property="name" column="name"/﹥ 46 ﹤result property="birthday" column="birthday"/﹥ 47 ﹤/resultMap﹥ 48 49 ﹤resultMap class="Student" id="studentWithTeacherResultMap" extends="studentBasicResultMap"﹥ 50 ﹤result property="teachers" column="id" select="getTeachersByStudentId"/﹥ 51 ﹤/resultMap﹥ 52 53 ﹤select id="getStudents" resultMap="studentWithTeacherResultMap"﹥ 54 ﹤!--[CDATA[ 55 select * from student 56 ]]﹥ 57 ﹤/select﹥ 58 59 ﹤select id="getTeachersByStudentId" resultClass="Teacher"﹥ 60 ﹤![CDATA[ 61 select t.* from teacher t,student_teacher st where t.id=st.teacherid and st.studentid=#value# ]]--﹥ 62 ﹤/select﹥ 63 64 ﹤/sqlMap﹥ 65 66 iBATIS的多对多映射配置7，测试 67 68 package com.lsm.test; 69 70 import java.io.Reader; 71 import java.sql.SQLException; 72 import java.util.List; 73 import com.ibatis.common.resources.Resources; 74 import com.ibatis.sqlmap.client.SqlMapClient; 75 import com.ibatis.sqlmap.client.SqlMapClientBuilder; 76 import com.lsm.domain.Student; 77 import com.lsm.domain.Teacher; 78 79 public class Many2Many 80 { 81 82 private static SqlMapClient sqlMapClient = null; 83 static 84 { 85 try 86 { 87 Reader reader = Resources.getResourceAsReader("com/lsm/cfg/SqlMapConfig.xml"); 88 sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader); 89 } 90 catch(Exception e) 91 { 92 e.printStackTrace(); 93 } 94 } 95 /** 96 * @param args 97 */ 98 public static void main(String[] args) 99 { 100 Many2Many m2m = new Many2Many(); 101 List studentlist = null; 102 studentlist = m2m.getStudentInfo(); 103 for(int i=0;i﹤studentlist.size();i++) 104 { 105 Student s = new Student(); 106 s = (Student) studentlist.get(i); 107 System.out.println("name:"+s.getName() + "\t" + "birthday:"+s.getBirthday()); 108 List tlist = s.getTeachers(); 109 if(tlist!=null) 110 { 111 System.out.println("his teachers as follows:"); 112 { 113 for(int ti=0;ti﹤tlist.size();ti++) 114 { 115 Teacher t = new Teacher(); 116 t = (Teacher) tlist.get(ti); 117 System.out.println("teacher name:" + t.getName()); 118 } 119 } 120 } 121 } 122 123 List teacherlist = null; 124 teacherlist = m2m.getTeacherInfo(); 125 for(int i=0;i﹤teacherlist.size();i++) 126 { 127 Teacher t = new Teacher(); 128 t = (Teacher) teacherlist.get(i); 129 System.out.println("name:"+t.getName() + "\t" + "subject:" + t.getSubject()); 130 List slist = t.getStudents(); 131 if(slist!=null) 132 { 133 System.out.println("his students as follows:"); 134 for(int si=0;si﹤slist.size();si++) 135 { 136 Student s = new Student(); 137 s = (Student) slist.get(si); 138 System.out.println("student name:"+s.getName()); 139 } 140 } 141 } 142 } 143 144 // 获取学生信息 145 public List getStudentInfo() 146 { 147 List studentList = null; 148 try 149 { 150 System.out.println("学生信息如下："); 151 studentList = sqlMapClient.queryForList("getStudents"); 152 } 153 catch (SQLException e) 154 { 155 e.printStackTrace(); 156 } 157 return studentList; 158 } 159 160 // 获取老师信息 161 // 获取学生信息 162 public List getTeacherInfo() 163 { 164 List studentList = null; 165 try 166 { 167 System.out.println("老师信息如下："); 168 studentList = sqlMapClient.queryForList("getTeachers"); 169 } 170 catch (SQLException e) 171 { 172 e.printStackTrace(); 173 } 174 return studentList; 175 } 176 177 } 178
8，输出
- 学生信息如下：
- 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:
查询学生时带出老师信息，查询老师时带出学生信息，说明多对多映射成功。
iBATIS的多对多映射配置的情况就向你介绍到这里，希望对你有所帮助。