目录
- 1. 准备
- 2. 一对多关联查询
- 2.1 创建Model
- 2.2 创建Mapper层
- 2.3 创建service层
- 2.4 创建测试类进行测试
- 3. 多对一关联关系
- 4. 多对多
1. 准备
通过脚本创建相关的数据表:一对多(db1-n.txt)
2. 一对多关联查询
2.1 创建Model
创建Customer及Order表对应的model
在model中建立一对多关联关系在Customer中声明
private List<TOrderEntity> orders = new ArrayList<>();
注意:生成对应的getter/setter方法
2.2 创建Mapper层
1)定义接口
package com.zking.mybatisdemo.mapper;
import com.zking.mybatisdemo.model.Customer;
import java.util.List;
public interface CustomerMapper {
List<Customer> listCustomer(Customer customer);
}
1)编写关联查询的sql语句
select t1.customer_id,t1.customer_name, t2.order_id, t2.order_nofrom t_customer t1 inner join t_order t2 on t1.customer_id =
t2.cid
2)创建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.zking.mybatisdemo.mapper.CustomerMapper">
<resultMap id="customerMap" type="com.zking.mybatisdemo.model.Customer">
<id column="customer_id" javaType="java.lang.Integer" property="customerId"/>
<result column="customer_name" javaType="java.lang.String" property="customerName"/>
<collection property="orders" ofType="com.zking.mybatisdemo.model.Order">
<id column="order_id" javaType="java.lang.Integer" property="orderId"/>
<result column="order_no" javaType="java.lang.String" property="orderNo"/>
<result column="cid" javaType="java.lang.Integer" property="cid"/>
</collection>
</resultMap>
<select id="listCustomer" resultMap="customerMap">
SELECT t1.customer_id,t1.customer_name,t2.cid,t2.order_id,t2.order_no
from t_customer t1
inner join t_order t2 on t1.customer_id=t2.cid
<where>
<if test="customerName !=null and customerName !=''">
and t1.customer_name like concat(#(customerName),'%')
</if>
</where>
</select>
</mapper>
2.3 创建service层
创建service层接口及其实现类,实现类的定义如下
package com.zking.mybatisdemo.service;
import com.zking.mybatisdemo.mapper.CustomerMapper;
import com.zking.mybatisdemo.model.Customer;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class CustomerService implements ICustomerService {
@Autowired
private CustomerMapper customerMapper;
@Override
public List<Customer> listCustomer(Customer customer){
return customerMapper.listCustomer(customer);
}
}
2.4 创建测试类进行测试
package com.zking.mybatisdemo.service;
import com.zking.mybatisdemo.model.Customer;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;
import static org.junit.Assert.*;
@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath*:applicationContext*.xml")
public class CustomerServiceTest {
@Autowired
private ICustomerService customerService;
@Test
public void listCustomer() {
Customer customer=new Customer();
List<Customer> customers = customerService.listCustomer(customer);
customers.forEach(t-> System.out.println(t));
}
}
测试通过一对多的关联关系就完成了。
3. 多对一关联关系
1)mapper接口
package com.zking.mybatisdemo.mapper;
import com.zking.mybatisdemo.model.Order;
import java.util.List;
public interface OrderMapper {
List<Order> listOrder(Order order);
}
2)mapperXml
<?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.zking.mybatisdemo.mapper.OrderMapper">
<resultMap id="orderMap" type="com.zking.mybatisdemo.model.Order">
<id column="order_id" javaType="java.lang.Integer" property="orderId"/>
<result column="order_no" javaType="java.lang.String" property="orderNo"/>
<result column="cid" javaType="java.lang.Integer" property="cid"/>
<association property="customer" javaType="com.zking.mybatisdemo.model.Customer">
<id column="customer_id" javaType="java.lang.Integer" property="customerId"/>
<result column="customer_name" javaType="java.lang.String" property="customerName"/>
</association>
</resultMap>
<select id="listOrder" resultMap="orderMap">
SELECT t1.customer_id,t1.customer_name,t2.cid,t2.order_id,t2.order_no
from t_customer t1
inner join t_order t2 on t1.customer_id=t2.cid
<where>
<if test="orderNo !=null and orderNo !=''">
and order_no like concat(#{orderNo})
</if>
</where>
</select>
</mapper>
3)service层
package com.zking.mybatisdemo.service;
import com.zking.mybatisdemo.mapper.OrderMapper;
import com.zking.mybatisdemo.model.Order;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class OrderService implements IOrderService {
@Autowired
private OrderMapper orderMapper;
@Override
public List<Order> listOrder(Order order) {
return orderMapper.listOrder(order);
}
}
4)测试
package com.zking.mybatisdemo.service;
import com.zking.mybatisdemo.model.Order;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import java.util.List;
import static org.junit.Assert.*;
/*@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration("classpath*:applicationContext*.xml")*/
public class OrderServiceTest extends TestBase{
@Autowired
private IOrderService orderService;
@Test
public void listOrder() {
Order order=new Order();
List<Order> orders = orderService.listOrder(order);
orders.forEach(t-> System.out.println(t));
}
}
4. 多对多
两个一对多
1)创建需要的表结构
脚本dbn-n.txt,包括表结构及测试数据
/*
SQLyog Ultimate v12.09 (64 bit)
MySQL - 5.7.23 : Database - test
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
USE `test`;
/*Table structure for table `t_category` */
DROP TABLE IF EXISTS `t_category`;
CREATE TABLE `t_category` (
`category_id` int(11) NOT NULL AUTO_INCREMENT,
`category_name` varchar(50) NOT NULL,
PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;
/*Data for the table `t_category` */
insert into `t_category`(`category_id`,`category_name`) values (1,'焦点'),(2,'国际'),(3,'社会'),(4,'房产'),(5,'财经'),(6,'娱乐');
/*Table structure for table `t_customer` */
DROP TABLE IF EXISTS `t_customer`;
CREATE TABLE `t_customer` (
`customer_id` int(11) NOT NULL AUTO_INCREMENT,
`customer_name` varchar(50) NOT NULL,
PRIMARY KEY (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
/*Data for the table `t_customer` */
insert into `t_customer`(`customer_id`,`customer_name`) values (1,'zs'),(2,'ls'),(3,'ww'),(4,'xm');
/*Table structure for table `t_news` */
DROP TABLE IF EXISTS `t_news`;
CREATE TABLE `t_news` (
`news_id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(50) NOT NULL,
PRIMARY KEY (`news_id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4;
/*Data for the table `t_news` */
insert into `t_news`(`news_id`,`title`) values (1,'110'),(2,'111'),(3,'112'),(4,'113'),(5,'114'),(6,'115'),(7,'116'),(8,'117'),(9,'118');
/*Table structure for table `t_news_category` */
DROP TABLE IF EXISTS `t_news_category`;
CREATE TABLE `t_news_category` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`nid` int(11) NOT NULL,
`cid` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `nid` (`nid`),
KEY `cid` (`cid`),
CONSTRAINT `t_news_category_ibfk_1` FOREIGN KEY (`nid`) REFERENCES `t_news` (`news_id`),
CONSTRAINT `t_news_category_ibfk_2` FOREIGN KEY (`cid`) REFERENCES `t_category` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4;
/*Data for the table `t_news_category` */
insert into `t_news_category`(`id`,`nid`,`cid`) values (1,1,1),(2,1,2),(3,2,1),(4,2,2),(5,3,1),(6,3,2),(7,3,3),(8,4,1),(9,4,2),(10,4,3),(11,4,4);
/*Table structure for table `t_order` */
DROP TABLE IF EXISTS `t_order`;
CREATE TABLE `t_order` (
`order_id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` varchar(50) NOT NULL,
`cid` int(11) NOT NULL,
PRIMARY KEY (`order_id`),
UNIQUE KEY `order_no` (`order_no`),
KEY `cid` (`cid`),
CONSTRAINT `t_order_ibfk_1` FOREIGN KEY (`cid`) REFERENCES `t_customer` (`customer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4;
/*Data for the table `t_order` */
insert into `t_order`(`order_id`,`order_no`,`cid`) values (1,'P01',1),(2,'P02',1),(3,'P03',1),(4,'P04',1),(5,'P05',1),(6,'P06',2),(7,'P07',2),(8,'P08',2),(9,'P09',3),(10,'P10',3);
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
2) model
package com.zking.mybatisdemo.model;
import lombok.Data;
import java.util.List;
@Data
public class News {
private Integer newsId;
private String title;
private List<Category> categories;
}
package com.zking.mybatisdemo.model;
import lombok.Data;
import java.util.List;
@Data
public class Category {
private Integer categoryId;
private String categoryName;
private List<News> news;
}
3)mapper
package com.zking.mybatisdemo.mapper;
import com.zking.mybatisdemo.model.News;
import java.util.List;
public interface NewsMapper {
List<News> listNews(News news);
}
4)service
package com.zking.mybatisdemo.service;
import com.zking.mybatisdemo.mapper.NewsMapper;
import com.zking.mybatisdemo.model.News;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class NewsService implements INewsService {
@Autowired
private NewsMapper newsMapper;
@Override
public List<News> listNews(News news) {
return newsMapper.listNews(news);
}
}
5)测试
package com.zking.mybatisdemo.service;
import com.zking.mybatisdemo.model.Category;
import com.zking.mybatisdemo.model.News;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import java.util.List;
import static org.junit.Assert.*;
public class NewsServiceTest extends TestBase{
@Autowired
private NewsService newsService;
@Test
public void listNews() {
News news=new News();
List<News> news1 = newsService.listNews(news);
for (News nn:news1) {
System.out.println(nn);
for (Category c:nn.getCategories()){
System.out.println(c);
}
}
}
}
6)mapperXml
<?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.zking.mybatisdemo.mapper.NewsMapper">
<resultMap id="NerwsMap" type="com.zking.mybatisdemo.model.News">
<id column="news_id" javaType="java.lang.Integer" property="newsId"/>
<result column="title" javaType="java.lang.String" property="title"/>
<collection property="categories" ofType="com.zking.mybatisdemo.model.Category">
<id column="category_id" property="categoryId" javaType="java.lang.Integer"/>
<result column="category_name" javaType="java.lang.String" property="categoryName"/>
</collection>
</resultMap>
<select id="listNews" resultMap="NerwsMap">
SELECT t1.news_id,t1.title,t3.category_id,t3.category_name FROM t_news t1
INNER JOIN t_news_category t2 ON t1.news_id=t2.nid
INNER JOIN t_category t3 ON t2.cid =t3.category_id
</select>
</mapper>
注:以上为news到category的一对多关联, 从category到new是的一对多关联的方式是一样的,只是sql及映射部分的具体配置不同。
注意:在测试的时候记得打@注释否则会空指针异常