ibatis多对多实例

 

create table student
(
  stu_id int auto_increment not null,
  stu_name varchar(50) not null,
  stu_age int not null,
  primary key (stu_id)
);

create table teacher
(
  ter_id int auto_increment not null,
  ter_name varchar(50) not null,
  ter_subject varchar(50) not null,
  primary key (ter_id)
);

create table student_teacher_relation
(
  stu_id int not null,
  ter_id int not null
  primary key(stu_id,ter_id)
);

insert into student values (null,'张三',20);
insert into student values (null,'李四',21);
insert into student values (null,'王五',22);
insert into student values (null,'赵六',23);

insert into teacher values(null,'mike','数学');
insert into teacher values(null,'mary','语文');
insert into teacher values(null,'bob','外语');
insert into teacher values(null,'jack','物理');

insert into student_teacher(1,1);
insert into student_teacher(1,2);
insert into student_teacher(1,3);

insert into student_teacher(2,1);
insert into student_teacher(2,2);
insert into student_teacher(2,3);

insert into student_teacher(3,1);
insert into student_teacher(3,2);
insert into student_teacher(3,3);

　　

<sqlMapConfig>
<!--     <settings cacheModelsEnabled="false" enhancementEnabled="true"
    lazyLoadingEnabled="false" errorTracingEnabled="true" maxRequests="200"
    maxSessions="60" maxTransactions="20" useStatementNamespaces="true"
    defaultStatementTimeout="2" /> -->
<transactionManager type="JDBC">
<dataSource type="SIMPLE">
<property value="com.mysql.jdbc.Driver" name="JDBC.Driver" />
<property value="jdbc:mysql://127.0.0.1:3306/myblogdb" name="JDBC.ConnectionURL" />
<property value="root" name="JDBC.Username" />
<property value="root" name="JDBC.Password" />
</dataSource>
</transactionManager>
<sqlMap resource="sqlmapmysql/Student.xml" />
<sqlMap resource="sqlmapmysql/Teacher.xml" />
</sqlMapConfig>

 

public class Student {
private int stuId;
private String stuName;
private String stuBirthday;
private List teachers;

     生成get set方法。
}

 

public class Teacher {
private int terId;
private String  terName;
private String  terBirthday;
private List students;

     生成get set方法。
}

 

<?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 namespace="student">
<typeAlias alias="Student" type="com.zcl.blog.domain.po.Student"/>
<typeAlias alias="Teacher" type="com.zcl.blog.domain.po.Teacher"/>

<resultMap class="Student" id="studentBasicResultMap">
<result property="stuId" column="stu_id"/>
<result property="stuName" column="stu_name"/>
<result property="stuBirthday" column="stu_birthday"/>
</resultMap>

<resultMap class="Student" id="studentWithTeacherResultMap" extends="studentBasicResultMap">
<result property="teachers" column="stu_id" select="getTeachersByStudentId"/>
</resultMap>

<select id="getStudents" resultMap="studentWithTeacherResultMap">
<![CDATA[select * from student]]>
</select>

<select id="getTeachersByStudentId" resultClass="Teacher">
<![CDATA[select t.ter_id,t.ter_name terName,ter_subject terSubject from teacher t,student_teacher st where t.ter_id = st.ter_id and st.stu_id = #stuId#]]>
</select>
</sqlMap>

 

<?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 namespace="teacher">
<typeAlias alias="Teacher" type="com.zcl.blog.domain.po.Teacher"/>
<typeAlias alias="Student" type="com.zcl.blog.domain.po.Student"/>

<resultMap class="Teacher" id="terBasicResultMap">
<result property="terId" column="ter_id"/>
<result property="terName" column="ter_name"/>
<result property="terSubject" column="ter_subject"/>
</resultMap>

<resultMap class="Teacher" id="teacherWithStuResultMap" extends="teacherBasicResultMap">
<result property="students" column="ter_id" select="getStusByTeacherId"/>
</resultMap>

<select id="getTeachers" resultMap="teacherWithStuResultMap">
<![CDATA[select * from teacher]]>
</select>

<select id="getStusByTeacherId" resultClass="Stu">
<![CDATA[select s.stu_id,s.stu_name stuName,s.stu_birthday stuBirthday from student s,student_teacher st where s.stu_id = st.stu_id and st.ter_id = #terId#]]>
</select>
</sqlMap>

 

public class ManyToMany {

private static SqlMapClient sqlMapClient = null;

static {
try {
            Reader reader = Resources.getResourceAsReader("sqlmap-config.xml");
            sqlMapClient = SqlMapClientBuilder.buildSqlMapClient(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

public static void main(String[] args) {
        ManyToMany mtm = new ManyToMany();
for (int i = 0; i < stuList.size(); i++) {
            Student stu = new Student();
            stu = (Student)stuList.get(i);
//System.out.println(stu.getName());
            List tlist = stu.getTeachers();
if (tlist != null) {
for (int j = 0; j < tlist.size(); j++) {
                    Teacher teacher = new Teacher();
                    teacher = (Teacher)tlist.get(j);
                    System.out.println(teacher.getTeacherName());
                }
            }
        }

    }

public List getStudentInfo() {
        List stuList = null;
try {
            stuList = sqlMapClient.queryForList("getStudents");
        } catch (Exception e) {
            e.printStackTrace();
        }
return stuList;
    }

public List getTagInfo() {
        List tagList = null;
try {
            tagList = sqlMapClient.queryForList("getAllTag");
        } catch (Exception e) {
            e.printStackTrace();
        }
return tagList;
    }
}