Mybatis实现--多对多数据库关系

Mybatis实现–多对多数据库关系

多对多关系在数据库中十分常见，比如一个购物系统中，一个用户可以有多个订单，这个是一对多的关系；一个订单可以有多个商品，一个商品又可以存在于多个订单，这就是多对多的关系。对于数据库中多对多的关系建议使用一个中间表来维护关系，中间表中的订单id作为外键参照订单表的id，商品id作为外键参照商品表的id

建表造数据

• TB_USERS ( 用户表 )
• TB_ARTICLE （商品表）
• TB_ORDER （订单表）
• TB_ITEM （中间表）

TB_USERS

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_users
-- ----------------------------
DROP TABLE IF EXISTS `tb_users`;
CREATE TABLE `tb_users` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`USERNAME` varchar(18) DEFAULT NULL,
`LOGINNAME` varchar(18) DEFAULT NULL,
`PASSWORD` varchar(18) DEFAULT NULL,
`PHONE` varchar(18) DEFAULT NULL,
`ADDRESS` varchar(18) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_users
-- ----------------------------
INSERT INTO `tb_users` VALUES ('1', '杰克', 'jack', '123456', '13765435673', '广州');
INSERT INTO `tb_users` VALUES ('2', '玛丽', 'mary', '123456', '13765435673', '北京');

TB_ORDER

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_order
-- ----------------------------
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`CODE` varchar(32) DEFAULT NULL,
`TOTAL` double DEFAULT NULL,
`USER_ID` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
KEY `USER_ID` (`USER_ID`),
CONSTRAINT `USER_ID` FOREIGN KEY (`USER_ID`) REFERENCES `tb_users` (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_order
-- ----------------------------
INSERT INTO `tb_order` VALUES ('1', '546457657658ygyufyu', '566', '1');
INSERT INTO `tb_order` VALUES ('2', '3534terteyeyew', '467', '1');

TB_ARTICLE

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_article
-- ----------------------------
DROP TABLE IF EXISTS `tb_article`;
CREATE TABLE `tb_article` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`NAME` varchar(18) DEFAULT NULL,
`PRICE` double(10,2) DEFAULT NULL,
`REMARK` varchar(18) DEFAULT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_article
-- ----------------------------
INSERT INTO `tb_article` VALUES ('1', '疯狂java讲义', '11.00', '疯狂java讲义');
INSERT INTO `tb_article` VALUES ('2', '疯狂android讲义', '22.00', '疯狂android讲义');
INSERT INTO `tb_article` VALUES ('3', '疯狂ios讲义', '33.00', '疯狂ios讲义');
INSERT INTO `tb_article` VALUES ('4', 'spring企业级开发', '44.00', 'spring企业级开发');

TB_ITEM

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for tb_item
-- ----------------------------
DROP TABLE IF EXISTS `tb_item`;
CREATE TABLE `tb_item` (
`ORDER_ID` int(11) NOT NULL,
`ARTICLE_ID` int(11) NOT NULL,
`AMOUNT` int(11) DEFAULT NULL,
PRIMARY KEY (`ORDER_ID`,`ARTICLE_ID`),
KEY `ARTICLE_ID` (`ARTICLE_ID`),
CONSTRAINT `ORDER_ID` FOREIGN KEY (`ORDER_ID`) REFERENCES `tb_order` (`ID`),
CONSTRAINT `ARTICLE_ID` FOREIGN KEY (`ARTICLE_ID`) REFERENCES `tb_article` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tb_item
-- ----------------------------
INSERT INTO `tb_item` VALUES ('1', '1', '1');
INSERT INTO `tb_item` VALUES ('1', '2', '1');
INSERT INTO `tb_item` VALUES ('1', '3', '2');
INSERT INTO `tb_item` VALUES ('2', '1', '1');
INSERT INTO `tb_item` VALUES ('2', '4', '2');

创建与数据库表对应的实体类

• User.java
• Order.java
• Article.java

User.java

public class User implements Serializable{
private Integer id;
private String username;
private String loginname;
private String password;
private String phone;
private String address;
//用户和订单是一对多的关系
private List<Order> orders;
//set get方法省略
}

Order.java

public class Order implements Serializable{
private Integer id;
private String code;
private Double total;

//订单和用户是多对一的关系
private User user;

//订单和商品是多对多的关系
private List<Article> articles;
//set get方法省略
}

Article.java

public class Article {
private Integer id;
private String name;
private Double price;
private String remark;
//商品和订单应该是多对多的关系
private List<Order> orders;
}

创建mapper映射xml文件

• UserMapper.xml
• OrderMapper.xml
• ArticleMapper.xml

UserMapper.xml

selectById
根据用户ID查询用户信息

<?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.liusl.mapper.UserMapper">
<select id="selectById" resultMap="userResultMap">
        SELECT * FROM TB_USERS WHERE ID = #{id}
</select>
<resultMap id="userResultMap" type="User">
<id property="id" column="ID" />
<result property="username" column="USERNAME" />
<result property="loginname" column="LOGINNAME" />
<result property="password" column="PASSWORD" />
<result property="phone" column="PHONE" />
<result property="address" column="ADDRESS" />
<!--用户和订单是一对多的关系使用collection标签-->
<collection property="orders" javaType="ArrayList" column="id" fetchType="lazy" ofType="com.liusl.model.Order" select="com.liusl.mapper.OrderMapper.selectByUserId">
<id property="id" column="ID" />
<result property="code" column="CODE" />
<result property="total" column="TOTAL" />
</collection>
</resultMap>
</mapper>

因为查询用户的时候会查询该用户所有订单的详情，用户与订单是一对多的关系，使用collection标签；collection标签中有几个属性。

• property：对应的是User.java中的属性名称“orders”
• column: 列名id，传给select属性中方法的参数
• fetchType：数据加载方式，lazy代表懒加载，查询的时候可能不会访问订单信息，启用lazy还需要配置

mybatis:
  configuration:
    lazy-loading-enabled: true
    aggressive-lazy-loading: false

• ofType: select返回的数据类型
• select：调用其他的方法来返回该collection的结果，在这个地方会调用com.liusl.mapper.OrderMapper.selectByUserId

OrderMapper.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.liusl.mapper.OrderMapper">
<!--根据Order Id查询订单信息-->
<select id="selectByOrderId" resultMap="orderResultMap">
        SELECT u.*,o.ID AS OID,o.CODE,o.TOTAL
        FROM TB_USERS u,TB_ORDER o
        WHERE u.ID = o.USER_ID AND o.ID = #{id}
</select>
<!--根据用户ID 查询订单信息-->
<select id="selectByUserId" resultType="com.liusl.model.Order">
        SELECT * FROM TB_ORDER WHERE USER_ID = #{id}
</select>
<resultMap id="orderResultMap" type="Order">
<id property="id" column="ID" />
<result property="code" column="CODE" />
<result property="total" column="TOTAL" />
<!--多对一关联，多个订单有一个用户-->
<association property="user2" javaType="User2">
<id property="id" column="ID" />
<result property="username" column="USERNAME" />
<result property="loginname" column="LOGINNAME" />
<result property="password" column="PASSWORD" />
<result property="phone" column="PHONE" />
<result property="address" column="ADDRESS" />
</association>
<!--多对多关联collection-->
<collection property="articles" javaType="ArrayList" ofType="com.liusl.model.Article" fetchType="lazy" column="OID" select="com.liusl.mapper.ArticleMapper.selectArticleByOrderId">
<id property="id" column="ID" />
<result property="name" column="NAME" />
<result property="price" column="PRICE" />
<result property="remark" column="REMARK" />
</collection>
</resultMap>
</mapper>

ArticleMapper.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.liusl.mapper.ArticleMapper">
<!--根据Order Id查询商品信息-->
<!--传过来的值是ORDER_ID,首先通过中间表查询出该OID中的所有商品ID，在通过ARTICLE表查询出商品信息-->
<select id="selectArticleByOrderId" resultType="com.liusl.model.Article">
        SELECT *
        FROM TB_ARTICLE
        WHERE id IN (
          SELECT ARTICLE_ID FROM TB_ITEM WHERE ORDER_ID = #{id}
        )
</select>
</mapper>

创建mapper.java文件

• UserMapper.java
• OrderMapper.java

UserMapper.java

@Mapper
public interface UserMapper {
public User selectById(Integer id);
}

OrderMapper.java

@Mapper
public interface OrderMapper {
public Order selectByOrderId(Integer id);
}

测试

创建测试类

@RunWith(SpringRunner.class)
@SpringBootTest
public class UserMapperTest {
@Autowired
private UserMapper userMapper;
@Autowired
private OrderMapper orderMapper;
@Test
public void selectById() throws Exception {
        User user = userMapper.selectById(1);
        System.out.println(JSON.toJSONString(user,true));
    }
@Test
public void selectByOrderId() throws Exception{
        Order order = orderMapper.selectByOrderId(1);
        System.out.println(JSON.toJSONString(order,true));
    }

}

返回结果分别是：

{
    "address":"广州",
    "id":1,
    "loginname":"jack",
    "orders":[
 {
 "code":"546457657658ygyufyu",
 "id":1,
 "total":566.0
},
 {
 "code":"3534terteyeyew",
 "id":2,
 "total":467.0
}
 ],
    "password":"123456",
    "phone":"13765435673",
    "username":"杰克"
}

{
    "articles":[
 {
 "id":1,
 "name":"疯狂java讲义",
 "price":11.0,
 "remark":"疯狂java讲义"
},
 {
 "id":2,
 "name":"疯狂android讲义",
 "price":22.0,
 "remark":"疯狂android讲义"
},
 {
 "id":3,
 "name":"疯狂ios讲义",
 "price":33.0,
 "remark":"疯狂ios讲义"
}
 ],
    "code":"546457657658ygyufyu",
    "id":1,
    "total":566.0,
    "user2":{
 "address":"广州",
 "id":1,
 "loginname":"jack",
 "password":"123456",
 "phone":"13765435673",
 "username":"杰克"
}
}

大家要是想买书的话，可以加我的微信15313876221，或者qq1010893356，或者qq群376448087，比正常的要便宜，程序员你懂得，买个书动不动就100多。^-^