Mybatis关联查询
Mybatis可以执行关联查询,可以是一个sql语句也可以是多个sql语句,不管怎么样都会用到标签:
<association></association>
<collection></collection>
下面记录下单条SQL语句的写法。
涉及的实体类:
MyTest1
mport lombok.Data;
import java.util.List;
@Data
public class MyTest1 {
private Integer id;
private String name;
private List<MyTest2> myTest2List;
private MyTest3 myTest3;
}
MyTest2
import lombok.Data;
@Data
public class MyTest2 {
private Integer id;
private String name;
private Integer pid;
}
MyTest3
import lombok.Data;
@Data
public class MyTest3 {
private Integer id;
private String name;
private Integer id1;
}
Mapper类
@Mapper
public interface MyTest1Mapper {
MyTest1 get(Integer id);
MyTest1 getOne(Integer id);
}
mapper的xml文件
<!-- 如果在查询的表中是一堆多的关系,那么使用collection标签-->
<resultMap id="mytest1ResultMap" type="com.example.demo.com.example.demo.domain.MyTest1">
<id column="t1_id" property="id"/>
<result column="t1_name" property="name"/>
<collection property="myTest2List" ofType="com.example.demo.com.example.demo.domain.MyTest2">
<id column="t2_id" property="id"/>
<result column="t2_name" property="name"/>
<result column="t2_pid" property="pid"/>
</collection>
</resultMap>
<select id="get" parameterType="Integer" resultMap="mytest1ResultMap">
select
t1.id as t1_id,
t1.name as t1_name,
t2.id as t2_id,
t2.name as t2_name,
t2.pid as t2_pid
from mytest1 t1,mytest2 t2 where t1.id = t2.pid and t1.id = #{id}
</select>
<!-- 如果是一对一的关系那么使用assocation标签-->
<resultMap id="mytest1ResultMap1" type="com.example.demo.com.example.demo.domain.MyTest1">
<id column="t1_id" property="id"/>
<result column="t1_name" property="name"/>
<association property="myTest3" javaType="com.example.demo.com.example.demo.domain.MyTest3">
<id column="t2_id" property="id"/>
<result column="t2_name" property="name"/>
<result column="t2_id1" property="id1"/>
</association>
</resultMap>
<select id="getOne" parameterType="Integer" resultMap="mytest1ResultMap1">
select
t1.id as t1_id,
t1.name as t1_name,
t2.id as t2_id,
t2.name as t2_name,
t2.id1 as t2_id1
from mytest1 t1,mytest3 t2 where t1.id = t2.id1 and t1.id = #{id}
</select>
在两个标签中property属性都是在MyTest1中的对应表实体类到的属性名,如:myTest3,myTest2List
还需要注意的是指定属性类的时候,collection是通过ofType,association是通过javaType指定。