转载自：MyBatis学习（六）MyBatis关联映射之一对多映射
他写的真详细！！！
数据库中一对多通常使用主外键关联，外键应该在多方，即多方维护关系。
补充：如果映射时存在多个参数时：
<collection property="answers" column="id=id,userid=userId" fetchType="lazy"
javaType="ArrayList" ofType="Answer"
select="findAnsByQidAndUserid">
<!-- 根据问题id和用户id找到单个答案（v_paper_answer） -->
<select id="findAnsByQidAndUserid" resultType="PaperAns2">
select *
from v_paper_answer v
where 1=1 and v.questionId = #{id} and v.userId = #{userid}
<!-- where 1=1 and v.questionId = #{id} and v.userId = #{userid} -->
</select>
<!-- 查询 paper + ques + ans(为ans链表) -->
<select id="findAnswerList" resultMap="paperQuesAns2ResultMap">
SELECT
q.id,
q.paperid,
p.title,
p.createTime,
p.createUserId,
p.description,
p.status,
p.level,
q.id as quesId,
q.qid,
q.content,
q.ismust,
q.type,
q.min_num,
q.max_num,
a.userId
FROM
db_paper p, db_question2 q
left join db_answer a on a.questionId = q.id
WHERE
q.paperId = #{id} and p.id = q.paperId and a.userId = #{userId}
AND (
content IS NOT NULL ) order by (qid+0)
</select>
<resultMap type="PaperQuesAns2" id="paperQuesAns2ResultMap">
<id property="id" column="id"/>
<result property="paperId" column="paperId"/>
<result property="title" column="title"/>
<result property="createTime" column="createTime"/>
<result property="createUserId" column="createUserId"/>
<result property="description" column="description"/>
<result property="status" column="status"/>
<result property="level" column="level"/>
<result property="quesId" column="quesId"/>
<result property="qid" column="qid"/>
<result property="content" column="content"/>
<!-- <result property="remark" column="remark"/> -->
<!-- <result property="num" column="num"/> -->
<result property="ismust" column="ismust"/>
<result property="type" column="type"/>
<result property="min_num" column="min_num"/>
<result property="max_num" column="max_num"/>
<!--property表示返回类型Question2的属性options
column表示将id作为参数进行之后的查询
fetchtype表示懒加载
javaType表示属性对应的类型
ofType表示集合当中的类型
-->
<!-- 填充选项链表 -->
<collection property="options" column="id" fetchType="lazy"
javaType="ArrayList" ofType="PaperOption2"
select="findAllOption">
<id property="id" column="id"/>
<result property="quesid" column="quesid"/>
<result property="orderid" column="orderid"/>
<result property="content" column="content"/>
<result property="remark" column="remark"/>
</collection>
<!-- 填充答案链表 -->
<collection property="answers" column="id=id,userid=userId" fetchType="lazy"
javaType="ArrayList" ofType="Answer"
select="findAnsByQidAndUserid">
<id property="id" column="id"/>
<result property="answer" column="answer"/>
<result property="subTime" column="subTime"/>
<result property="userId" column="userId"/>
</collection>
</resultMap>
下面举一个简单实例来看看MyBatis怎么处理一对多的关系。
1.创建一个项目，导入所需jar包，导入db.properties配置文件，导入log4j.properties配置文件
2.创建一个数据库，在里面创建两张表
-- Table structure for `t_clazz`
-- ----------------------------
DROP TABLE IF EXISTS `t_clazz`;
CREATE TABLE `t_clazz` (
`id` int(11) NOT NULL,
`code` varchar(18) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_clazz
-- ----------------------------
INSERT INTO `t_clazz` VALUES ('1', '一班');
INSERT INTO `t_clazz` VALUES ('2', '二班');
-- Table structure for `t_student`
-- ----------------------------
DROP TABLE IF EXISTS `t_student`;
CREATE TABLE `t_student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(18) NOT NULL,
`sex` varchar(3) NOT NULL,
`age` int(11) NOT NULL,
`cid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `cid` (`cid`),
CONSTRAINT `cid` FOREIGN KEY (`cid`) REFERENCES `t_clazz` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of t_student
-- ----------------------------
INSERT INTO `t_student` VALUES ('1', '张三', '男', '11', '1');
INSERT INTO `t_student` VALUES ('2', '李四', '男', '12', '2');
INSERT INTO `t_student` VALUES ('3', '小红', '女', '13', '1');
3.编写对应的实体类
public class Student {
private Integer id;
private String name;
private String sex;
private Integer age;
//关联的clazz对象
private Clazz clazz;
public class Clazz {
private Integer id;
private String code;
//关联的student集合
private List<Student> students;
4.编写对应的SQL映射文件
ClazzMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dj.mapper.ClazzMapper">
<select id="selectClazzById" parameterType="int" resultMap="clazzResultMap">
select * from t_clazz where id =#{id}
</select>
<resultMap type="com.dj.domain.Clazz" id="clazzResultMap">
<id property="id" column="id"/>
<resultproperty="code" column="code"/>
<!--property表示返回类型Clazz的属性students
column表示将id作为参数进行之后的查询
fetchtype表示懒加载
javaType表示属性对应的类型
ofType表示集合当中的类型
-->
<collection property="students" column="id" fetchType="lazy"
javaType="ArrayList" ofType="com.dj.domain.Student"
select="com.dj.mapper.StudentMapper.selectStudentByClazzId">
<id property="id" column="id"/>
<resultproperty="name" column="name"/>
<resultproperty="sex" column="sex"/>
<resultproperty="age" column="age"/>
</collection>
</resultMap>
</mapper>
StudentMapper.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace指用户自定义的命名空间 -->
<mapper namespace="com.dj.mapper.StudentMapper">
<select id="selectStudentByClazzId" parameterType="int" resultType="com.dj.domain.Student">
select * from t_student where cid=#{id}
</select>
</mapper>
5.编写mybatis-config.xml文件
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 引入 外部db.properties文件-->
<properties resource="db.properties"/>
<!-- 指定 MyBatis 所用日志的具体实现-->
<settings>
<setting name="logImpl" value="log4j"/>
</settings>
<!-- 环境配置 -->
<environments default="mysql">
<environment id="mysql">
<!-- 指定事务类型 -->
<transactionManager type="JDBC"/>
<!-- dataSource指数据源配置，POOLED是JDBC连接对象的数据源连接池的实现。 -->
<dataSource type="POOLED">
<property name="driver" value="${driver}"/>
<property name="url" value="${url}"/>
<property name="username" value="${username}"/>
<property name="password" value="${password}"/>
</dataSource>
</environment>
</environments>
<!-- SQL映射文件位置 -->
<mappers>
<mapper resource="com/dj/mapper/StudentMapper.xml"/>
<mapper resource="com/dj/mapper/ClazzMapper.xml"/>
</mappers>
</configuration>
6.mybatis建议通过mapper接口的代理对象访问mybatis，该对象关联了一个sqlsession对象，开发者可以通过该对象直接调用方法操作数据库。
注意: mapper接口对象的类名必须和之前的mapper.xml的namespace一致，方法名和参数名及返回类型也要与mapper.xml的配置一致。
public interface ClazzMapper {
//根据id查询班级信息
Clazz selectClazzById(int id);
}
7.测试
public class OneToManyTest {
public static void main(String[] args) throws Exception {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
//获得mapper接口的代理对象
ClazzMapper mapper = sqlSession.getMapper(ClazzMapper.class);
//调用接口中的方法
Clazz clazz = mapper.selectClazzById(1);
List<Student> students = clazz.getStudents();
for (Student student : students) {
System.out.println(student);
}
}
}
在控制台可以看到如下结果：
测试成功。