开发环境是Springmvc+mybatis+maven,工具是MyEclipse2015。
项目我不介绍了,自己去了解。这里我主要是要介绍下Mybatis多表连接的操作。有三张表,学生表,课程表,学生与课程关系表,这是个多对多的关系。如下图
业务需求是通过学生ID找到学生的课程,数据库语句如下
SELECT
dbo.student.id,
dbo.student.name,
dbo.student.password,
dbo.student.student_id,
dbo.student.card_id,
dbo.student.address,
dbo.student.email,
dbo.student.remark,
dbo.stu_sub.id,
dbo.stu_sub.stu_id,
dbo.stu_sub.sub_id,
dbo.subject.id,
dbo.subject.sub_name
FROM
dbo.stu_sub
JOIN dbo.student ON dbo.stu_sub.stu_id = dbo.student.id
JOIN dbo.subject ON dbo.stu_sub.sub_id = dbo.subject.id
where student.id = 1;
得到的结果是
关键性的代码如下:StudentMapping.xml
<resultMap type="com.hman.water.model.Student" id="StuAndSubResultMap" extends="BaseResultMap">
<collection property="stuSubLists" javaType="list" ofType="com.hman.water.model.StuSub">
<id property="id" column="ss_id"/>
<result property="stuId" column="ss_stu_id"/>
<result property="subId" column="ss_sub_id"/>
<association property="subject" javaType="com.hman.water.model.Subject">
<id property="id" column="sub_id"/>
<result property="subName" column="sub_name"/>
</association>
</collection>
</resultMap>
<select id="selectStuAndSubById" parameterType="java.lang.Integer" resultMap="StuAndSubResultMap">
SELECT
dbo.student.id,
dbo.student.name,
dbo.student.password,
dbo.student.student_id,
dbo.student.card_id,
dbo.student.address,
dbo.student.email,
dbo.student.remark,
dbo.stu_sub.id ss_id,
dbo.stu_sub.stu_id ss_stu_id,
dbo.stu_sub.sub_id ss_sub_id,
dbo.subject.id sub_id,
dbo.subject.sub_name sub_name
FROM
dbo.stu_sub
JOIN dbo.student ON dbo.stu_sub.stu_id = dbo.student.id
JOIN dbo.subject ON dbo.stu_sub.sub_id = dbo.subject.id
where student.id = #{id,jdbcType=INTEGER};
</select>
解释下上面的意思:
1,一对多的时候,用collection;一对一的时候,用association
2,parameterType表示输入到sql的值,可以是基本类型,也可以是对象
3,resultMap表示输出,可以是对象
4,collection中的property要与com.hman.water.model.Student中定义的成员变量一直
5,javaType可以理解为链接表的输出类型
讲过测试得到如下数据:
代码链接地址如下:
CSDN:http://download.csdn.net/my/uploads/1
github:https://github.com/fzhzx/WaterQuality
CSDN上要积分,可以去github上下载