在使用SQL的时候,重点就在于多表联合查询,而MyBatis中重点也是多表联合查询,并且将表与表的关系体现了出来。而MyBatis中的关系分为一对一、一对多、多对多,这里我们先解释前两种。
先看表结构
根据图中所示,张三具有两个住址。
看对象
//Customer对象
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Customer {
private int id;
private String userName;
private String jobs;
private String phone;
private List<Address> address;
}
//Address对象
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Address {
private int id;
private int customerId;
private String address;
//private Customer customer;
}
注解形式:t_customer为主表,t_address为副表
XML形式:t_address为主表 ,t_customer为副表
一、注解形式
1、注解实现一对一
public interface CustomerService {
@Select("SELECT * FROM t_customer WHERE id = #{id}") //查询语句
@Results(id = "customerMapper", value = { //用户表的字段映射
@Result(column = "id",property = "id",javaType = int.class),
@Result(column = "username",property = "userName",javaType = String.class),
//实现用户类中地址对象的类型映射,通过@One来实现,column表示的是需要传给子查询的参数,为主查询的列名
@Result(property = "address", column = "id", one = @One(select = "service.CustomerService.getAddressList"))
})
public Customer getCustomerList(Integer id);
@Select("SELECT * FROM t_address WHERE c_id = #{c_id}") //子查询语句
@Results(id = "addressMapper",value = { //地址表的字段映射
@Result(column = "c_id", property = "customerId",javaType = int.class),
@Result(column = "c_address",property = "address",javaType = String.class)
})
public Address getAddressList(Integer c_id);
}
2、注解实现一对多
public interface CustomerService {
@Select("SELECT * FROM t_customer WHERE id = #{id}") //查询语句
@Results(id = "customerMapper", value = { //用户表的字段映射
@Result(column = "id",property = "id",javaType = int.class),
@Result(column = "username",property = "userName",javaType = String.class),
//实现用户类中地址对象的类型映射,通过@One来实现,column表示的是需要传给子查询的参数,为主查询的列名
@Result(property = "address", column = "id", many= @Many(select = "service.CustomerService.getAddressList"))
})
public Customer getCustomerList(Integer id);
@Select("SELECT * FROM t_address WHERE c_id = #{c_id}") //子查询语句
@Results(id = "addressMapper",value = { //地址表的字段映射
@Result(column = "c_id", property = "customerId",javaType = int.class),
@Result(column = "c_address",property = "address",javaType = String.class)
})
public List<Address> getAddressList(Integer c_id);
}
3、注解实现的区别
二、XML形式
先将两个表查询出来的字段设置一个别名,方便后面的属性映射。
<!--用户表的字段设置别名-->
<sql id="customerTemplate">
customer.id AS customerId, customer.username AS customerName,
customer.jobs AS customerJobs, customer.phone AS customerPhone,
</sql>
<!--地址表的字段设置别名-->
<sql id="addressTemplate">
address.id AS addressId, address.c_id AS addressCustomerId,
address.c_address AS address
</sql>
1、实现一对一
查询标签
<select id="getAddress" parameterType="Integer" resultMap="addressMapper">
SELECT
<include refid="customerTemplate"/> <!--引入代码片段-->
<include refid="addressTemplate"/>
FROM t_address address INNER JOIN t_customer customer
ON customer.id = address.c_id
WHERE address.c_id = #{id};
</select>
结果映射标签
<resultMap id="addressMapper" type="address"> <!--先为address主表设置映射-->
<id column="addressId" property="id"/>
<result column="addressCustomerId" property="customerId"/>
<result column="address" property="address"/>
<!--再为t_customer副标设置映射,property为Address类中的属性名,javaType表示property的类型-->
<association property="customer" javaType="customer">
<id column="customerId" property="id"/>
<result column="customerName" property="userName"/>
<result column="customerJobs" property="jobs"/>
<result column="customerPhone" property="phone"/>
</association>
</resultMap>
2、实现一对多
查询标签(与一对一的查询一致)
<select id="getAddress" parameterType="Integer" resultMap="addressMapper">
SELECT
<include refid="customerTemplate"/>
<include refid="addressTemplate"/>
FROM t_address address INNER JOIN t_customer customer
ON customer.id = address.c_id
WHERE address.c_id = #{id};
</select>
结果集映射标签
<resultMap id="addressMapper" type="address">
<id column="addressId" property="id"/>
<result column="addressCustomerId" property="customerId"/>
<result column="address" property="address"/>
<!---进行映射-->
<collection property="customer" ofType="customer">
<id column="customerId" property="id"/>
<result column="customerName" property="userName"/>
<result column="customerJobs" property="jobs"/>
<result column="customerPhone" property="phone"/>
</collection>
</resultMap>
3、XML一对一和一对多的不同
<!--实现一对一-->
<association property="customer" javaType="customer"></association>
<!--
property、javaType都是主类中副类的类型
-->
<!--实现一对多-->
<collection property="customer" ofType="customer"></collection>
<!--
property、ofType都是主类中副类的类型
-->
三、注解形式和XML形式的区别
1、注解写法简单,XML形式写法复杂。
2、注解使用的是子查询的方式,XML形式使用的是多表联合查询的方式。
3、当数据量庞大的时候,注解形式执行的方式会慢与XML形式。
主要的区别就在写法和效率上,注解使用子查询,会执行两次SQL查询,而XML形式使用的是多表联合查询,一次查询会执行一次SQL,除了写法复杂一点其他的都会比使用注解的情况好很多。
我们可以使用Junit单元测试看得到情况:
1、注解的子查询情况:
2、XML实现多表联合查询情况:
所以使用XML完成多表联合查询实现一对一、一对多会比使用注解的效率高。