ibatis多对多示例

最新推荐文章于 2021-05-08 17:09:57 发布

iteye_4696

最新推荐文章于 2021-05-08 17:09:57 发布

阅读量84

点赞数

分类专栏： ibatis 文章标签：数据库 java

ibatis 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

1、iBATIS的多对多映射配置方法和多对一映射配置方法差不多，不同的是，多对多映射，数据库设计上需要一个记录两个类关系的中间表，本文以学生-老师为例，在iBATIS的sqlmap中配置多对多关系。

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: