纯纯使用 MyBatis 框架,不使用 MyBatis-Plus 和分页插件等。
1. 建表
1. 用户表 user
2. 订单表 order
3. 工作表 work
4. 角色表 role
5. 用户角色表 user_role
6. 薪资表 salary
上述6张表的建表语句及插入数据语句:
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '姓名',
`age` int(11) NULL DEFAULT NULL COMMENT '年龄',
`gender` tinyint(2) NOT NULL DEFAULT 0 COMMENT '性别,0:女 1:男',
`wid` int(11) NULL DEFAULT NULL COMMENT '工作ID',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '犬小哈', 30, 1, 1);
INSERT INTO `user` VALUES (2, '关羽', 46, 1, 2);
INSERT INTO `user` VALUES (3, '诸葛亮', 26, 1, 4);
INSERT INTO `user` VALUES (4, '张三', 34, 0, 2);
INSERT INTO `user` VALUES (5, '李四', 21, 1, 3);
INSERT INTO `user` VALUES (6, '王五', 44, 0, 8);
INSERT INTO `user` VALUES (7, '赵六', 43, 0, 2);
INSERT INTO `user` VALUES (8, '陈七', 25, 1, 3);
INSERT INTO `user` VALUES (9, '钱途', 50, 1, 6);
INSERT INTO `user` VALUES (10, '肖八', 38, 0, 9);
-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`uid` int(11) NOT NULL COMMENT '用户id',
`rid` int(11) NOT NULL COMMENT '角色id',
PRIMARY KEY (`uid`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '用户角色中间表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES (1, 2);
INSERT INTO `user_role` VALUES (2, 3);
INSERT INTO `user_role` VALUES (3, 3);
INSERT INTO `user_role` VALUES (4, 1);
INSERT INTO `user_role` VALUES (5, 2);
INSERT INTO `user_role` VALUES (6, 1);
INSERT INTO `user_role` VALUES (7, 2);
INSERT INTO `user_role` VALUES (8, 1);
INSERT INTO `user_role` VALUES (9, 3);
INSERT INTO `user_role` VALUES (10, 2);
-- ----------------------------
-- Table structure for order
-- ----------------------------
DROP TABLE IF EXISTS `order`;
CREATE TABLE `order` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`order_id` bigint(20) UNSIGNED NOT NULL COMMENT '订单ID',
`user_id` bigint(20) UNSIGNED NOT NULL COMMENT '下单用户ID',
`goods_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '商品名称',
`goods_price` decimal(10, 2) NOT NULL COMMENT '商品价格',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_order_id`(`order_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '订单表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of order
-- ----------------------------
INSERT INTO `order` VALUES (1, 805646264648356, 1, 'Switch 游戏机', 1400.00);
INSERT INTO `order` VALUES (2, 551787441310504, 1, '小米手机', 2000.00);
INSERT INTO `order` VALUES (3, 938562101633493, 2, '《三国演义》', 66.00);
INSERT INTO `order` VALUES (4, 791129917310894, 3, '华为手机', 1200.00);
INSERT INTO `order` VALUES (5, 208722395587361, 3, '《西游记》', 56.00);
-- ----------------------------
-- Table structure for work
-- ----------------------------
DROP TABLE IF EXISTS `work`;
CREATE TABLE `work` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '工作名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '工作表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of work
-- ----------------------------
INSERT INTO `work` VALUES (1, '教师');
INSERT INTO `work` VALUES (2, '程序员');
INSERT INTO `work` VALUES (3, '作家');
INSERT INTO `work` VALUES (4, '编剧');
INSERT INTO `work` VALUES (5, '演员');
INSERT INTO `work` VALUES (6, '歌手');
INSERT INTO `work` VALUES (7, '咖啡师');
INSERT INTO `work` VALUES (8, '厨师');
INSERT INTO `work` VALUES (9, '外卖员');
INSERT INTO `work` VALUES (10, '快递员');
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(11) NOT NULL COMMENT '角色ID',
`name` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '角色名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '角色表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES (1, '超级管理员');
INSERT INTO `role` VALUES (2, '管理员');
INSERT INTO `role` VALUES (3, '普通用户');
-- ----------------------------
-- Table structure for salary
-- ----------------------------
DROP TABLE IF EXISTS `salary`;
CREATE TABLE `salary` (
`uid` bigint(20) NULL DEFAULT NULL COMMENT '用户id',
`salary` decimal(10, 2) NULL DEFAULT NULL COMMENT '薪资'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '薪资表' ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of salary
-- ----------------------------
INSERT INTO `salary` VALUES (1, 3000.00);
INSERT INTO `salary` VALUES (3, 5000.00);
INSERT INTO `salary` VALUES (2, 2000.00);
INSERT INTO `salary` VALUES (9, 1000.00);
INSERT INTO `salary` VALUES (10, 10000.00);
INSERT INTO `salary` VALUES (8, 4000.00);
INSERT INTO `salary` VALUES (7, 3500.00);
INSERT INTO `salary` VALUES (4, 5500.00);
INSERT INTO `salary` VALUES (5, 6000.00);
INSERT INTO `salary` VALUES (6, 90000.00);
model层、dao层、service层、controller层代码及mapper.xml代码略,可以使用一些代码生成工具生成基本的单表增删改查功能。
2.association 和 collection 标签介绍
- association 用于一对一、多对一场景使用。
- collection 用于一对多、多对多场景使用。
- association 标签用于关联对象。
- collection 标签用于关联集合。
3.mybatis多表关联查询
3.1 一对一
user -> salary
每个人的薪资都不一样,一个人对应一个薪资。(只是个学习demo,现实当然可能多人对应一个薪资。)
1. 修改UserMapper.xml,添加 association 标签。
<resultMap id="BaseResultMap" type="cn.study.demo.model.User" >
<id column="id" property="id" jdbcType="BIGINT" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
<result column="gender" property="gender" jdbcType="TINYINT" />
<result column="wid" property="wid" jdbcType="INTEGER" />
<association property="salary" javaType="float"
column="id" select="cn.study.demo.dao.SalaryMapper.getSalaryById" />
</resultMap>
association 标签中的 property 名称可自定义,对应 resultMap 标签中的 类(即 type 值)中的自定义属性,两者名称要相同。column 为 user 表中的某个属性,用于关联 user 表 和 salary 表,javaType 为 该 select 的 返回结果类型。 select 为该 association 的查询语句。
2. 修改User.java,添加 salary 属性
public class User implements Serializable {
@ApiModelProperty("主键ID")
private Long id;
@ApiModelProperty("姓名")
private String name;
@ApiModelProperty("年龄")
private Integer age;
@ApiModelProperty("性别,0:女 1:男")
private Byte gender;
@ApiModelProperty("工作ID")
private Integer wid;
private Float salary;
private static final long serialVersionUID = 1L;
}
3. 修改SalaryMapper.xml,添加 getSalaryById 的查询语句
<select id="getSalaryById" resultType="float" parameterType="long">
select salary from salary
where uid = #{uid,jdbcType=BIGINT}
</select>
dao层、service层、controller层添加该方法的代码省略。
4. 修改UserMapper.xml,使用 limit 实现分页
<sql id="Pagination" >
<if test="page != null and page.start !=null" >
LIMIT #{page.start,jdbcType=INTEGER},#{page.pageSize,jdbcType=INTEGER}
</if>
</sql>
<select id="selectList" resultMap="BaseResultMap" parameterType="Map" >
select t.* from user t
<include refid="Pagination" />
</select>
dao层、service层、controller层实现selectList的代码省略。
3.2 多对一
与 3.1相似,没多少区别。
user -> work
一个人只能有一份工作,一个工作可以有多人来做。(也不太严谨,一个人也可以有很多副业,学习demo别太认真x)
UserMapper.xml 添加 association 标签,关联 work
<resultMap id="BaseResultMap" type="cn.study.demo.model.User" >
<id column="id" property="id" jdbcType="BIGINT" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
<result column="gender" property="gender" jdbcType="TINYINT" />
<result column="wid" property="wid" jdbcType="INTEGER" />
<association property="salary" javaType="float"
column="id" select="cn.study.demo.dao.SalaryMapper.getSalaryById" />
<association property="work" javaType="string"
column="wid" select="cn.study.demo.dao.WorkMapper.getWorkNameById" />
</resultMap>
在 User.java 中添加 work 属性
private String work;
修改WorkMapper.xml
<select id="getWorkNameById" resultType="string" parameterType="integer">
select name from work where id = #{id}
</select>
其他步骤及代码省略。
3.3 一对多
user -> order
一个用户拥有多个订单。
修改UserMapper.xml,添加 collection 标签,因为返回的是多个订单的集合
<resultMap id="BaseResultMap" type="cn.study.demo.model.User" >
<id column="id" property="id" jdbcType="BIGINT" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
<result column="gender" property="gender" jdbcType="TINYINT" />
<result column="wid" property="wid" jdbcType="INTEGER" />
<association property="salary" javaType="float"
column="id" select="cn.study.demo.dao.SalaryMapper.getSalaryById" />
<association property="work" javaType="string"
column="wid" select="cn.study.demo.dao.WorkMapper.getWorkNameById" />
<collection property="orderList" javaType="java.util.ArrayList"
column="id" ofType="cn.study.demo.model.Order"
select="cn.study.demo.dao.OrderMapper.getOrderByUserId" />
</resultMap>
修改User类,添加 orderList 属性
private List<Order> orderList;
修改OrderMapper.xml,添加getOrderByUserId
<select id="getOrderByUserId" resultMap="BaseResultMap" parameterType="long">
select * from `order`
where user_id = #{userId,jdbcType=BIGINT}
</select>
其他代码略。
3.4 多对多
user->user_role->role
一个用户对应多个角色,一个角色对应多个用户。
修改RoleMapper.xml,添加 collection 标签
<resultMap id="BaseResultMap" type="cn.study.demo.model.Role" >
<id column="id" property="id" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<collection property="userList" ofType="cn.study.demo.model.UserDTO">
<id column="user_id" property="id" jdbcType="BIGINT" />
<result column="uname" property="name" jdbcType="VARCHAR" />
<result column="age" property="age" jdbcType="INTEGER" />
<result column="gender" property="gender" jdbcType="TINYINT" />
</collection>
</resultMap>
其中,ofType为该collection的类型,collection 标签中的内容对应 UserDTO类 的属性。
UserDTO类代码如下,省略了一些User类的属性
@Data
@Builder
@NoArgsConstructor
@AllArgsConstructor
public class UserDTO implements Serializable {
@ApiModelProperty("主键ID")
private Long id;
@ApiModelProperty("姓名")
private String name;
@ApiModelProperty("年龄")
private Integer age;
@ApiModelProperty("性别,0:女 1:男")
private Byte gender;
private static final long serialVersionUID = 1L;
}
修改RoleMapper.xml,查询代码如下
<select id="selectUserRole" resultMap="BaseResultMap" parameterType="Map">
select t1.id,
t1.name,
t3.id as user_id,
t3.name as uname,
t3.age,
t3.gender
from role t1
left outer join user_role t2 on t1.id=t2.rid
left outer join user t3 on t2.uid = t3.id
<include refid="Pagination" />
</select>
<sql id="Pagination" >
<if test="page != null and page.start !=null" >
LIMIT #{page.start,jdbcType=INTEGER},#{page.pageSize,jdbcType=INTEGER}
</if>
</sql>
其他代码均省略。
该sql的查询结果如下
接口查询结果如下
分页条件为空时,查询所有,结果如下
{
"isError": false,
"data": [
{
"id": 2,
"name": "管理员",
"userList": [
{
"id": 1,
"name": "犬小哈",
"age": 30,
"gender": 1
},
{
"id": 5,
"name": "李四",
"age": 21,
"gender": 1
},
{
"id": 7,
"name": "赵六",
"age": 43,
"gender": 0
},
{
"id": 10,
"name": "肖八",
"age": 38,
"gender": 0
}
]
},
{
"id": 3,
"name": "普通用户",
"userList": [
{
"id": 2,
"name": "关羽",
"age": 46,
"gender": 1
},
{
"id": 3,
"name": "诸葛亮",
"age": 26,
"gender": 1
},
{
"id": 9,
"name": "钱途",
"age": 50,
"gender": 1
}
]
},
{
"id": 1,
"name": "超级管理员",
"userList": [
{
"id": 4,
"name": "张三",
"age": 34,
"gender": 0
},
{
"id": 6,
"name": "王五",
"age": 44,
"gender": 0
},
{
"id": 8,
"name": "陈七",
"age": 25,
"gender": 1
}
]
}
]
}