Mybatis的一对多、多对一、多对多关系详解（案例）
为什么需要关联关系？
在实际世界中，我们的单一实体不可能与其他实体毫无关系，事实上每个个体之间都有千丝万缕的联系。而在程序设计中就必须要考虑实体之间的关联，我们通过数据库表与表之间的链接来体现这种关联关系，比如外键引用等，而多对多是比较复杂的情况，也是最能体现现实业务逻辑的一种情况，数据库没办法直接体现多对多的关联关系，所以我们设计两个表的中间表来替代这种复杂的引用，这就是现今普遍的解决方案。
一对多和多对一
现在我有两个表，客户表（t_customer）和订单表（t_order），订单表保存了客户表的外键引用，通过外键可以表示一对多和多对一的映射关系，文末将会提供sql脚本代码，可以直接通过左侧索引跳转。
1） 实体类映射
package com.star.model; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import java.util.List; @Data @AllArgsConstructor @NoArgsConstructor @Builder public class Customer { private Integer customerId; private String customerName; //实体层面的一对多映射 private List<Order> orders; }
package com.star.model; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; @Data @AllArgsConstructor @NoArgsConstructor @Builder public class Order { private Integer orderId; private String orderNo; private Integer cid; //建立实体层面的映射关系 private Customer customer; }
2)mapper映射
接口代码：
package com.star.mapper; import com.star.model.Customer; import com.star.model.Order; import org.springframework.stereotype.Repository; import java.util.List; @Repository public interface CustomerMapper { int deleteByPrimaryKey(Integer customerId); int insert(Customer record); int insertSelective(Customer record); Customer selectByPrimaryKey(Integer customerId); int updateByPrimaryKeySelective(Customer record); int updateByPrimaryKey(Customer record); //通过客户id找到客户对应的订单 List<Order> queryOrderByCustomer(Customer customer); }
package com.star.mapper; import com.star.model.Customer; import com.star.model.Order; import org.springframework.stereotype.Repository; @Repository public interface OrderMapper { int deleteByPrimaryKey(Integer orderId); int insert(Order record); int insertSelective(Order record); Order selectByPrimaryKey(Integer orderId); int updateByPrimaryKeySelective(Order record); int updateByPrimaryKey(Order record); /*通过订单查询用户多对一*/ Order queryCustomerByOrder(Order order); }
xml映射：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "mybatis-3-mapper.dtd" >
<!--客户映射-->
<mapper namespace="com.star.mapper.CustomerMapper" >
<resultMap id="BaseResultMap" type="com.star.model.Customer" >
<constructor >
<idArg column="customer_id" jdbcType="INTEGER" javaType="java.lang.Integer" />
<arg column="customer_name" jdbcType="VARCHAR" javaType="java.lang.String" />
</constructor>
</resultMap>
<!-- 要返回的订单结果集(一对多) -->
<resultMap id="baseOneToMany" type="com.star.model.Customer" >
<!--定义主键-->
<id column="customer_id" property="customerId" javaType="java.lang.Integer"/>
<result column="customer_name" property="customerName" javaType="java.lang.String"/>
<!-- 一对多的关系 -->
<!-- property: 指的是集合属性的值, ofType：指的是集合中元素的类型 -->
<collection property="orders" ofType="com.star.model.Order">
<id column="order_id" property="orderId"/>
<result column="order_no" property="orderNo"/>
<result column="cid" property="cid"/>
</collection>
</resultMap>
<sql id="Base_Column_List" >
customer_id, customer_name
</sql>
<sql id="base_query_list">
select <include refid="Base_Column_List"/> from t_customer where 1=1
</sql>
<!--找到客户对应订单映射sql-->
<select id="queryOrderByCustomer" resultMap="baseOneToMany">
select * from t_customer ct left join t_order od on ct.customer_id=od.cid where 1=1
<if test="customerId!=null">
and customer_id = #{customerId}
</if>
</select>
<insert id="insert" parameterType="com.star.model.Customer" >
insert into t_customer (customer_id, customer_name)
values (#{customerId,jdbcType=INTEGER}, #{customerName,jdbcType=VARCHAR})
</insert>
<insert id="insertSelective" parameterType="com.star.model.Customer" >
insert into t_customer
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="customerId != null" >
customer_id,
</if>
<if test="customerName != null" >
customer_name,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="customerId != null" >
#{customerId,jdbcType=INTEGER},
</if>
<if test="customerName != null" >
#{customerName,jdbcType=VARCHAR},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.star.model.Customer" >
update t_customer
<set >
<if test="customerName != null" >
customer_name = #{customerName,jdbcType=VARCHAR},
</if>
</set>
where customer_id = #{customerId,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.star.model.Customer" >
update t_customer
set customer_name = #{customerName,jdbcType=VARCHAR}
where customer_id = #{customerId,jdbcType=INTEGER}
</update>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer" >
delete from t_customer
where customer_id = #{customerId,jdbcType=INTEGER}
</delete>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer" >
select
<include refid="Base_Column_List" />
from t_customer
where customer_id = #{customerId,jdbcType=INTEGER}
</select>
</mapper>
<?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.star.mapper.OrderMapper">
<resultMap id="BaseResultMap" type="com.star.model.Order">
<constructor>
<idArg column="order_id" jdbcType="INTEGER" javaType="java.lang.Integer"/>
<arg column="order_no" jdbcType="VARCHAR" javaType="java.lang.String"/>
<arg column="cid" jdbcType="INTEGER" javaType="java.lang.Integer"/>
</constructor>
</resultMap>
<!--对应关系结果集（多对一）-->
<resultMap id="baseManyToOne" type="com.star.model.Order">
<id column="order_id" property="orderId"/>
<result column="order_no" property="orderNo"/>
<result column="cid" property="cid"/>
<!-- 多对一的关系 -->
<!-- property: 指的是属性的值, javaType：指的是属性的类型-->
<association property="customer" javaType="com.star.model.Customer">
<id column="customer_id" property="customerId"/>
<result column="customer_name" property="customerName"/>
</association>
</resultMap>
<sql id="Base_Column_List">
order_id, order_no, cid
</sql>
<select id="queryCustomerByOrder" resultMap="baseManyToOne">
select * from t_order od inner join t_customer cu on cu.customer_id=od.cid where 1=1
and order_id = #{orderId}
</select>
<select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer">
select
<include refid="Base_Column_List"/>
from t_order
where order_id = #{orderId,jdbcType=INTEGER}
</select>
<delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
delete from t_order
where order_id = #{orderId,jdbcType=INTEGER}
</delete>
<insert id="insert" parameterType="com.star.model.Order">
insert into t_order (order_id, order_no, cid
)
values (#{orderId,jdbcType=INTEGER}, #{orderNo,jdbcType=VARCHAR}, #{cid,jdbcType=INTEGER}
)
</insert>
<insert id="insertSelective" parameterType="com.star.model.Order">
insert into t_order
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="orderId != null">
order_id,
</if>
<if test="orderNo != null">
order_no,
</if>
<if test="cid != null">
cid,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides=",">
<if test="orderId != null">
#{orderId,jdbcType=INTEGER},
</if>
<if test="orderNo != null">
#{orderNo,jdbcType=VARCHAR},
</if>
<if test="cid != null">
#{cid,jdbcType=INTEGER},
</if>
</trim>
</insert>
<update id="updateByPrimaryKeySelective" parameterType="com.star.model.Order">
update t_order
<set>
<if test="orderNo != null">
order_no = #{orderNo,jdbcType=VARCHAR},
</if>
<if test="cid != null">
cid = #{cid,jdbcType=INTEGER},
</if>
</set>
where order_id = #{orderId,jdbcType=INTEGER}
</update>
<update id="updateByPrimaryKey" parameterType="com.star.model.Order">
update t_order
set order_no = #{orderNo,jdbcType=VARCHAR},
cid = #{cid,jdbcType=INTEGER}
where order_id = #{orderId,jdbcType=INTEGER}
</update>
</mapper>
3)junit测试
service代码我就不贴了，注意注入mapper接口就OK。
baseTest：
package com.star.service.impl; import org.junit.runner.RunWith; import org.springframework.test.context.ContextConfiguration; import org.springframework.test.context.junit4.SpringJUnit4ClassRunner; import javax.annotation.Resource; @RunWith(SpringJUnit4ClassRunner.class) @ContextConfiguration(locations = {"classpath:applicationContext.xml"}) public class BaseTest { }
测试结果：
1）测试通过客户id查询对应的订单（一对多）
2020-11-12 10:28:26.674 [main-135] DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter. 2020-11-12 10:28:27.190 [main-109] DEBUG org.apache.ibatis.io.VFS - Class not found: org.jboss.vfs.VFS 2020-11-12 10:28:27.191 [main-142] DEBUG org.apache.ibatis.io.JBoss6VFS - JBoss 6 VFS API is not available in this environment. 2020-11-12 10:28:27.192 [main-109] DEBUG org.apache.ibatis.io.VFS - Class not found: org.jboss.vfs.VirtualFile 2020-11-12 10:28:27.193 [main-70] DEBUG org.apache.ibatis.io.VFS - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment. 2020-11-12 10:28:27.195 [main-84] DEBUG org.apache.ibatis.io.VFS - Using VFS adapter org.apache.ibatis.io.DefaultVFS 2020-11-12 10:28:29.294 [main-159] DEBUG com.star.mapper.CustomerMapper.queryOrderByCustomer - ==> Preparing: select * from t_customer ct left join t_order od on ct.customer_id=od.cid where 1=1 and customer_id = ? 2020-11-12 10:28:29.329 [main-159] DEBUG com.star.mapper.CustomerMapper.queryOrderByCustomer - ==> Parameters: 1(Integer) 2020-11-12 10:28:29.357 [main-165] TRACE com.star.mapper.CustomerMapper.queryOrderByCustomer - <== Columns: customer_id, customer_name, order_id, order_no, cid 2020-11-12 10:28:29.359 [main-165] TRACE com.star.mapper.CustomerMapper.queryOrderByCustomer - <== Row: 1, zs, 1, P01, 1 2020-11-12 10:28:29.363 [main-165] TRACE com.star.mapper.CustomerMapper.queryOrderByCustomer - <== Row: 1, zs, 2, P02, 1 2020-11-12 10:28:29.364 [main-165] TRACE com.star.mapper.CustomerMapper.queryOrderByCustomer - <== Row: 1, zs, 3, P03, 1 2020-11-12 10:28:29.365 [main-165] TRACE com.star.mapper.CustomerMapper.queryOrderByCustomer - <== Row: 1, zs, 4, P04, 1 2020-11-12 10:28:29.367 [main-165] TRACE com.star.mapper.CustomerMapper.queryOrderByCustomer - <== Row: 1, zs, 5, P05, 1 2020-11-12 10:28:29.367 [main-159] DEBUG com.star.mapper.CustomerMapper.queryOrderByCustomer - <== Total: 5 Customer(customerId=1, customerName=zs, orders=[Order(orderId=1, orderNo=P01, cid=1, customer=null), Order(orderId=2, orderNo=P02, cid=1, customer=null), Order(orderId=3, orderNo=P03, cid=1, customer=null), Order(orderId=4, orderNo=P04, cid=1, customer=null), Order(orderId=5, orderNo=P05, cid=1, customer=null)])
2）测试通过订单id查询对应的客户（多对一）
2020-11-12 10:31:38.681 [main-135] DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter. 2020-11-12 10:31:39.064 [main-109] DEBUG org.apache.ibatis.io.VFS - Class not found: org.jboss.vfs.VFS 2020-11-12 10:31:39.068 [main-142] DEBUG org.apache.ibatis.io.JBoss6VFS - JBoss 6 VFS API is not available in this environment. 2020-11-12 10:31:39.070 [main-109] DEBUG org.apache.ibatis.io.VFS - Class not found: org.jboss.vfs.VirtualFile 2020-11-12 10:31:39.072 [main-70] DEBUG org.apache.ibatis.io.VFS - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment. 2020-11-12 10:31:39.075 [main-84] DEBUG org.apache.ibatis.io.VFS - Using VFS adapter org.apache.ibatis.io.DefaultVFS 2020-11-12 10:31:41.563 [main-159] DEBUG com.star.mapper.OrderMapper.queryCustomerByOrder - ==> Preparing: select * from t_order od inner join t_customer cu on cu.customer_id=od.cid where 1=1 and order_id = ? 2020-11-12 10:31:41.601 [main-159] DEBUG com.star.mapper.OrderMapper.queryCustomerByOrder - ==> Parameters: 1(Integer) 2020-11-12 10:31:41.625 [main-165] TRACE com.star.mapper.OrderMapper.queryCustomerByOrder - <== Columns: order_id, order_no, cid, customer_id, customer_name 2020-11-12 10:31:41.625 [main-165] TRACE com.star.mapper.OrderMapper.queryCustomerByOrder - <== Row: 1, P01, 1, 1, zs 2020-11-12 10:31:41.628 [main-159] DEBUG com.star.mapper.OrderMapper.queryCustomerByOrder - <== Total: 1 Order(orderId=1, orderNo=P01, cid=1, customer=Customer(customerId=1, customerName=zs, orders=null))
多对多
表结构，新闻和类型标签之间的多对多关系（一个标签可以对应多个新闻，一个新闻可以有多个标签）：
-- 多对多 -- 主表 create table t_news ( news_id int primary key auto_increment, -- 新闻ID：主鍵 title varchar(50) not null -- 标题 ); -- 主表 create table t_category ( category_id int primary key auto_increment, -- 类别编号：主鍵 category_name varchar(50) not null -- 类别名称 ); -- 中间表 create table t_news_category ( id int primary key not null auto_increment, nid int not null, cid int not null, foreign key(nid) references t_news(news_id), foreign key(cid) references t_category(category_id) );
1）实体层
使用了lombok插件，可以快速提供无参和有参的构造函数。
package com.star.model; import lombok.*; import java.util.List; @Data @Builder @NoArgsConstructor @AllArgsConstructor public class News { private Integer newsId; private String title; private List<Category> categories; //后面会用到 public News(Integer newsId, String title) { this.newsId = newsId; this.title = title; } }
package com.star.model; import lombok.AllArgsConstructor; import lombok.Builder; import lombok.Data; import lombok.NoArgsConstructor; import java.util.List; @Data @Builder @NoArgsConstructor @AllArgsConstructor public class Category { private Integer categoryId; private String categoryName; private List<News> news; //后面会用到 public Category(Integer categoryId, String categoryName) { this.categoryId = categoryId; this.categoryName = categoryName; } }
2)mapper层
接口代码：
package com.star.mapper; import com.star.model.Category; import com.star.model.News; import org.springframework.stereotype.Repository; import java.util.List; @Repository public interface NewsMapper { int deleteByPrimaryKey(Integer newsId); int insert(News record); int insertSelective(News record); News selectByPrimaryKey(Integer newsId); int updateByPrimaryKeySelective(News record); int updateByPrimaryKey(News record); //通过新闻id查找到新闻对应的类型标签 News queryCategoryByNewId(News news); }
package com.star.mapper; import com.star.model.Category; import com.star.model.News; import org.springframework.stereotype.Repository; import java.util.List; @Repository public interface CategoryMapper { int deleteByPrimaryKey(Integer categoryId); int insert(Category record); int insertSelective(Category record); Category selectByPrimaryKey(Integer categoryId); int updateByPrimaryKeySelective(Category record); int updateByPrimaryKey(Category record); //通过类型id找到对应的新闻集合 Category queryNewsByCategoryId(Category category); }
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.star.mapper.NewsMapper"> <resultMap id="BaseResultMap" type="com.star.model.News"> <constructor> <idArg column="news_id" jdbcType="INTEGER" javaType="java.lang.Integer"/> <arg column="title" jdbcType="VARCHAR" javaType="java.lang.String"/> </constructor> </resultMap> <!--实际也可以不用单独写出来而是包含在collection标签内，但是为了好区分--> <resultMap id="base_categories" type="com.star.model.Category"> <id column="category_id" property="categoryId"/> <result column="category_name" property="categoryName"/> </resultMap> <!-- 新闻对应多个类型标签：一对多关系 --> <resultMap id="newsManyToMany" type="com.star.model.News" extends="BaseResultMap"> <!-- <id column="news_id" property="newsId"/>--> <!-- <result column="title" property="title"/>--> <collection property="categories" resultMap="base_categories"> </collection> </resultMap> <sql id="Base_Column_List"> news_id, title </sql> <!--为了方便用*代替，实际开发禁用，这里的外键引用字段都是不同的，所以可以用* 通过新闻查找类型 --> <select id="queryCategoryByNewId" resultMap="newsManyToMany"> select * from t_news nws left join t_news_category nct on nws.news_id = nct.nid left join t_category ctg on ctg.category_id = nct.cid where nws.news_id = #{newsId} </select> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer"> select <include refid="Base_Column_List"/> from t_news where news_id = #{newsId,jdbcType=INTEGER} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer"> delete from t_news where news_id = #{newsId,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.star.model.News"> insert into t_news (news_id, title) values (#{newsId,jdbcType=INTEGER}, #{title,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" parameterType="com.star.model.News"> insert into t_news <trim prefix="(" suffix=")" suffixOverrides=","> <if test="newsId != null"> news_id, </if> <if test="title != null"> title, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="newsId != null"> #{newsId,jdbcType=INTEGER}, </if> <if test="title != null"> #{title,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.star.model.News"> update t_news <set> <if test="title != null"> title = #{title,jdbcType=VARCHAR}, </if> </set> where news_id = #{newsId,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.star.model.News"> update t_news set title = #{title,jdbcType=VARCHAR} where news_id = #{newsId,jdbcType=INTEGER} </update> </mapper>
<?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.star.mapper.CategoryMapper"> <resultMap id="BaseResultMap" type="com.star.model.Category"> <constructor> <idArg column="category_id" jdbcType="INTEGER" javaType="java.lang.Integer"/> <arg column="category_name" jdbcType="VARCHAR" javaType="java.lang.String"/> </constructor> </resultMap> <resultMap id="baseNews" type="com.star.model.News"> <id column="news_id" property="newsId"/> <result column="title" property="title"/> <collection property="categories" resultMap="BaseResultMap"/> </resultMap> <!-- 新闻对应多个类型标签：一对多关系 --> <resultMap id="categoryManyToMany" type="com.star.model.Category"> <id column="category_id" property="categoryId"/> <result column="category_name" property="categoryName"/> <collection property="news" resultMap="baseNews"> </collection> </resultMap> <sql id="Base_Column_List"> category_id, category_name </sql> <select id="selectByPrimaryKey" resultMap="BaseResultMap" parameterType="java.lang.Integer"> select <include refid="Base_Column_List"/> from t_category where category_id = #{categoryId,jdbcType=INTEGER} </select> <!--要注意的是主从关系，类型应该是主表，新闻是从表--> <select id="queryNewsByCategoryId" resultMap="categoryManyToMany"> select * from t_category ctg left join t_news_category nct on ctg.category_id = nct.cid left join t_news nws on nws.news_id = nct.nid where ctg.category_id = #{categoryId} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer"> delete from t_category where category_id = #{categoryId,jdbcType=INTEGER} </delete> <insert id="insert" parameterType="com.star.model.Category"> insert into t_category (category_id, category_name) values (#{categoryId,jdbcType=INTEGER}, #{categoryName,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" parameterType="com.star.model.Category"> insert into t_category <trim prefix="(" suffix=")" suffixOverrides=","> <if test="categoryId != null"> category_id, </if> <if test="categoryName != null"> category_name, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="categoryId != null"> #{categoryId,jdbcType=INTEGER}, </if> <if test="categoryName != null"> #{categoryName,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="com.star.model.Category"> update t_category <set> <if test="categoryName != null"> category_name = #{categoryName,jdbcType=VARCHAR}, </if> </set> where category_id = #{categoryId,jdbcType=INTEGER} </update> <update id="updateByPrimaryKey" parameterType="com.star.model.Category"> update t_category set category_name = #{categoryName,jdbcType=VARCHAR} where category_id = #{categoryId,jdbcType=INTEGER} </update> </mapper>
3）测试
同样的我就不贴Service了，测试代码：
package com.star.service.impl; import com.star.model.Category; import com.star.model.News; import com.star.service.ICategoryService; import com.star.service.INewsService; import org.junit.Before; import org.junit.Test; import org.springframework.beans.factory.annotation.Autowired; import static org.junit.Assert.*; public class NewsServiceImplTest extends BaseTest{ @Autowired private INewsService newsService; @Autowired private ICategoryService categoryService; @Test public void queryCategoryByNewId() { //测试查询新闻对应的标签类型 System.out.println(newsService.queryCategoryByNewId(News.builder().newsId(4).build())); //测试标签类型对应的新闻 System.out.println(categoryService.queryNewsByCategoryId(Category.builder().categoryId(2).build())); } }
测试结果：
2020-11-12 10:51:57.725 [main-135] DEBUG org.apache.ibatis.logging.LogFactory - Logging initialized using 'class org.apache.ibatis.logging.slf4j.Slf4jImpl' adapter. 2020-11-12 10:51:58.185 [main-109] DEBUG org.apache.ibatis.io.VFS - Class not found: org.jboss.vfs.VFS 2020-11-12 10:51:58.186 [main-142] DEBUG org.apache.ibatis.io.JBoss6VFS - JBoss 6 VFS API is not available in this environment. 2020-11-12 10:51:58.189 [main-109] DEBUG org.apache.ibatis.io.VFS - Class not found: org.jboss.vfs.VirtualFile 2020-11-12 10:51:58.190 [main-70] DEBUG org.apache.ibatis.io.VFS - VFS implementation org.apache.ibatis.io.JBoss6VFS is not valid in this environment. 2020-11-12 10:51:58.192 [main-84] DEBUG org.apache.ibatis.io.VFS - Using VFS adapter org.apache.ibatis.io.DefaultVFS 2020-11-12 10:52:00.854 [main-159] DEBUG com.star.mapper.NewsMapper.queryCategoryByNewId - ==> Preparing: select * from t_news nws left join t_news_category nct on nws.news_id = nct.nid left join t_category ctg on ctg.category_id = nct.cid where nws.news_id = ? 2020-11-12 10:52:00.892 [main-159] DEBUG com.star.mapper.NewsMapper.queryCategoryByNewId - ==> Parameters: 4(Integer) 2020-11-12 10:52:00.919 [main-165] TRACE com.star.mapper.NewsMapper.queryCategoryByNewId - <== Columns: news_id, title, id, nid, cid, category_id, category_name 2020-11-12 10:52:00.921 [main-165] TRACE com.star.mapper.NewsMapper.queryCategoryByNewId - <== Row: 4, 113, 8, 4, 1, 1, 焦点 2020-11-12 10:52:00.926 [main-165] TRACE com.star.mapper.NewsMapper.queryCategoryByNewId - <== Row: 4, 113, 9, 4, 2, 2, 国际 2020-11-12 10:52:00.927 [main-165] TRACE com.star.mapper.NewsMapper.queryCategoryByNewId - <== Row: 4, 113, 10, 4, 3, 3, 社会 2020-11-12 10:52:00.927 [main-165] TRACE com.star.mapper.NewsMapper.queryCategoryByNewId - <== Row: 4, 113, 11, 4, 4, 4, 房产 2020-11-12 10:52:00.929 [main-159] DEBUG com.star.mapper.NewsMapper.queryCategoryByNewId - <== Total: 4 News(newsId=4, title=113, categories=[Category(categoryId=1, categoryName=焦点, news=null), Category(categoryId=2, categoryName=国际, news=null), Category(categoryId=3, categoryName=社会, news=null), Category(categoryId=4, categoryName=房产, news=null)]) 2020-11-12 10:52:00.938 [main-159] DEBUG com.star.mapper.CategoryMapper.queryNewsByCategoryId - ==> Preparing: select * from t_category ctg left join t_news_category nct on ctg.category_id = nct.cid left join t_news nws on nws.news_id = nct.nid where ctg.category_id = ? 2020-11-12 10:52:00.939 [main-159] DEBUG com.star.mapper.CategoryMapper.queryNewsByCategoryId - ==> Parameters: 2(Integer) 2020-11-12 10:52:00.941 [main-165] TRACE com.star.mapper.CategoryMapper.queryNewsByCategoryId - <== Columns: category_id, category_name, id, nid, cid, news_id, title 2020-11-12 10:52:00.941 [main-165] TRACE com.star.mapper.CategoryMapper.queryNewsByCategoryId - <== Row: 2, 国际, 2, 1, 2, 1, 110 2020-11-12 10:52:00.943 [main-165] TRACE com.star.mapper.CategoryMapper.queryNewsByCategoryId - <== Row: 2, 国际, 4, 2, 2, 2, 111 2020-11-12 10:52:00.944 [main-165] TRACE com.star.mapper.CategoryMapper.queryNewsByCategoryId - <== Row: 2, 国际, 6, 3, 2, 3, 112 2020-11-12 10:52:00.945 [main-165] TRACE com.star.mapper.CategoryMapper.queryNewsByCategoryId - <== Row: 2, 国际, 9, 4, 2, 4, 113 2020-11-12 10:52:00.946 [main-159] DEBUG com.star.mapper.CategoryMapper.queryNewsByCategoryId - <== Total: 4 Category(categoryId=2, categoryName=国际, news=[News(newsId=1, title=110, categories=null), News(newsId=2, title=111, categories=null), News(newsId=3, title=112, categories=null), News(newsId=4, title=113, categories=null)])
可以看到已经获取到我们想要的数据。
数据库脚本
-- 一对多 -- 客户表(主表) create table t_customer ( customer_id int primary key not null auto_increment, customer_name varchar(50) not null ); -- 多对一 -- 订单表(从表) create table t_order ( order_id int primary key not null auto_increment, order_no varchar(50) not null unique, cid int not null, foreign key(cid) references t_customer(customer_id) ); -- 与mssql不一样，自动增长是可以赋值，也可以不赋值 insert into t_customer(customer_id,customer_name) values(1, 'zs'); insert into t_customer(customer_id,customer_name) values(2, 'ls'); insert into t_customer(customer_id,customer_name) values(3, 'ww'); insert into t_customer(customer_id,customer_name) values(4, 'xm'); insert into t_order(order_no,cid) values('P01',1); insert into t_order(order_no,cid) values('P02',1); insert into t_order(order_no,cid) values('P03',1); insert into t_order(order_no,cid) values('P04',1); insert into t_order(order_no,cid) values('P05',1); insert into t_order(order_no,cid) values('P06',2); insert into t_order(order_no,cid) values('P07',2); insert into t_order(order_no,cid) values('P08',2); insert into t_order(order_no,cid) values('P09',3); insert into t_order(order_no,cid) values('P10',3); SELECT * FROM t_customer; SELECT * FROM t_order; select * from t_customer ct left join t_order od on ct.customer_id=od.cid where 1=1; -- 多对多 -- 主表 create table t_news ( news_id int primary key auto_increment, -- 新闻ID：主鍵 title varchar(50) not null -- 标题 ); -- 主表 create table t_category ( category_id int primary key auto_increment, -- 类别编号：主鍵 category_name varchar(50) not null -- 类别名称 ); -- 中间表 create table t_news_category ( id int primary key not null auto_increment, nid int not null, cid int not null, foreign key(nid) references t_news(news_id), foreign key(cid) references t_category(category_id) ); insert into t_news(news_id,title) values(1,'110'); insert into t_news(news_id,title) values(2,'111'); insert into t_news(news_id,title) values(3,'112'); insert into t_news(news_id,title) values(4,'113'); insert into t_news(news_id,title) values(5,'114'); insert into t_news(news_id,title) values(6,'115'); insert into t_news(news_id,title) values(7,'116'); insert into t_news(news_id,title) values(8,'117'); insert into t_news(news_id,title) values(9,'118'); insert into t_category(category_id,category_name) values(1,'焦点'); insert into t_category(category_id,category_name) values(2,'国际'); insert into t_category(category_id,category_name) values(3,'社会'); insert into t_category(category_id,category_name) values(4,'房产'); insert into t_category(category_id,category_name) values(5,'财经'); insert into t_category(category_id,category_name) values(6,'娱乐'); insert into t_news_category(nid,cid) values(1,1); insert into t_news_category(nid,cid) values(1,2); insert into t_news_category(nid,cid) values(2,1); insert into t_news_category(nid,cid) values(2,2); insert into t_news_category(nid,cid) values(3,1); insert into t_news_category(nid,cid) values(3,2); insert into t_news_category(nid,cid) values(3,3); insert into t_news_category(nid,cid) values(4,1); insert into t_news_category(nid,cid) values(4,2); insert into t_news_category(nid,cid) values(4,3); insert into t_news_category(nid,cid) values(4,4); SELECT * FROM t_news; SELECT * FROM t_category; SELECT * FROM t_news_category; select * FROM t_news nws LEFT JOIN t_news_category nct ON nws.news_id = nct.nid LEFT JOIN t_category ctg ON ctg.category_id = nct.cid WHERE nws.news_id = 4;
