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，输出

  1. 学生信息如下：  
  2. name:张三 birthday:1982-01-01  
  3. his teachers as follows:  
  4. teacher name:Jerry  
  5. teacher name:Tom  
  6. name:李四 birthday:1983-02-02  
  7. his teachers as follows:  
  8. teacher name:Jerry  
  9. name:王五 birthday:1984-03-03  
  10. his teachers as follows:  
  11. teacher name:Tom  
  12. name:赵六 birthday:1985-04-04  
  13. his teachers as follows:  
  14. 老师信息如下：  
  15. name:Jerry subject:语文  
  16. his students as follows:  
  17. student name:张三  
  18. student name:李四  
  19. name:Tom subject:数学  
  20. his students as follows:  
  21. student name:张三  
  22. student name:王五  
  23. name:Steven subject:英语  
  24. his students as follows: 

查询学生时带出老师信息，查询老师时带出学生信息，说明多对多映射成功。

iBATIS的多对多映射配置的情况就向你介绍到这里，希望对你有所帮助。

posted @ 2015-04-04 09:43  Tim&Blog  阅读(347)  评论(0)  编辑  收藏  举报