Mybatis(Springboot整合)
Mybatis(Springboot整合)
一、入门demo
添加依赖
<!--mybatis起步依赖-->
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>
<!-- MySQL连接驱动 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
在application.properties中添加数据量的连接信息
#DB Configuration:
spring.datasource.driverClassName=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/test?
useUnicode=true&characterEncoding=utf8
spring.datasource.username=root
spring.datasource.password=root
在test数据库中创建user表
-- ----------------------------
-- Table structure for `user`
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'zhangsan', '123', '张三');
INSERT INTO `user` VALUES ('2', 'lisi', '123', '李四');
创建实体Bean
public class User {
// 主键
private Long id;
// 用户名
private String username;
// 密码
private String password;
// 姓名
private String name;
//此处省略getter和setter方法 .. ..
}
编写Mapper接口
@Mapper//可在在springboot启动类上加@MapperScan("com.kiramie.mapper")代替此注解
public interface UserMapper {
List<User> selectAll();
}
配置Mapper映射文件UserMapper.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.kiramie.mapper.UserMapper">
<select id="selectAll" resultType="user">
select * from user
</select>
</mapper>
该文件的有两种放置路径：一种资源目录resource下,例如src\main\resources\com\kiramie\mapper。另一种是放置在java目录下，与mapper接口同级。若放置路径为后者，则还需在pom.xml中添加以下代码
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
</plugin>
</plugins>
<resources>
<!--编译的时候扫描src/main/java目录以及子目录下的xml文件-->
<resource>
<directory>src/main/java</directory>
<includes>
<include>**/*.xml</include>
</includes>
</resource>
</resources>
</build>
在application.properties中添加mybatis的配置
mybatis.type-aliases-package=com.kiramie.pojo
mybatis.mapper-locations=classpath:com/kiramie/mapper/**/*.xml
编写测试Controller
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserMapper userMapper;
@GetMapping("/selectAll")
public List<User> selectAll(){
return userMapper.selectAll();
}
}
测试
二、简单CRUD
添加Mpper方法
@Mapper
public interface UserMapper {
List<User> selectAll();
User selectById(Long id);
List<User> selectList(String username,String name);//多条件
List<User> selectMap(Map map);//map传参
int insertOne(User user);//pojo传参
int deleteById(Long id);
int updatePassword(Long id,String password);
List<Account> selectAccount();//测试resultMap
}
对应的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.kiramie.mapper.UserMapper">
<resultMap id="account" type="com.kiramie.pojo.Account">
<id column="id" property="aId"/>
<result column="username" property="aUsername"/>
<result column="password" property="aPassword"/>
<result column="name" property="aName"/>
</resultMap>
<select id="selectAll" resultType="user">
select * from user
</select>
<!--parameterType属性可以不配置-->
<select id="selectById" resultType="user" parameterType="long">
select * from user where id=#{id}
</select>
<!--pojo传参-->
<select id="selectList" resultType="user">
select * from user where username=#{username} and name=#{name}
</select>
<!--map传参：map的key要包含username和name-->
<select id="selectMap" resultType="user" parameterType="map">
select * from user where username=#{username} and name=#{name}
</select>
<insert id="insertOne">
insert into user(username,password,name) values (#{username},#{password},#{name})
</insert>
<delete id="deleteById">
delete from user where id=#{id}
</delete>
<update id="updatePassword">
update user set password=#{password} where id=#{id}
</update>
<select id="selectAccount" resultMap="account">
select * from user
</select>
</mapper>
简单事务测试
- 在springboot启动类中加上@EnableTransactionManagement
- 在service层的方法上加@Transactional
- 在单元测试中开启事务默认会回滚，除了@Transactional还要加@Commit
三、映射文件深入
控制台开启日志查看sql语句
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
动态sql
if
foreach
trim（where，set）
choose（when，otherwise）
package com.kiramie.mapper;
import com.kiramie.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
/**
* @InterfaceName User2Mapper
* @Description TODO
* @Author kiramie
* @Date 2020/12/2015:10
*/
@Mapper
public interface User2Mapper {
List<User> findByCondition(User user);
List<User> findByIds(List<Long> ids);
int insertSelective(User user);
int updateById(Long id,User user);
List<User> findByUsernameOrPasswordOrName(String username,String password,String name);
}
<?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.kiramie.mapper.User2Mapper">
<!--sql片段-->
<sql id="selectUser">select * from user</sql>
<!--if标签-->
<!--<select id="findByCondition" resultType="user">
select * from user where 1=1
<if test="id!=null and id!=''">
and id=#{id}
</if>
<if test="username!=null and username!=''">
and username=#{username}
</if>
<if test="password!=null and password!=''">
and password=#{password}
</if>
</select>-->
<select id="findByCondition" resultType="user">
<!--导入sql片段-->
<include refid="selectUser"/>
<!--where标签可替换sql语句where 1=1-->
<where>
<if test="id!=null and id!=''">
and id=#{id}
</if>
<if test="username!=null and username!=''">
and username=#{username}
</if>
<if test="password!=null and password!=''">
and password=#{password}
</if>
</where>
</select>
<!--foreach标签-->
<select id="findByIds" resultType="user">
<!--select * from user where id in(1,2,10);-->
<include refid="selectUser"/>
<where>
<!--collection的值为list,array或map中的key值-->
<foreach collection="list" open="id in (" close=")" item="id" separator=",">
#{id}
</foreach>
</where>
</select>
<!--trim标签-->
<insert id="insertSelective">
insert into user
<!--
prefix：表示在trim标签内sql语句加上前缀xxx
suffix：表示在trim标签内sql语句加上后缀xxx
prefixOverrides：表示去除第一个前缀xxx
suffixOverrides：表示去除最后一个后缀xxx
xxx：表示属性引号中的值
-->
<trim prefix="(" suffix=")" suffixOverrides=",">
<if test="id!=null and id!=''">
id,
</if>
<if test="username!=null and username!=''">
username,
</if>
<if test="password!=null and password!=''">
password,
</if>
<if test="name!=null and name!=''">
name,
</if>
</trim>
<trim prefix="values(" suffix=")" prefixOverrides=",">
<if test="id!=null and id!=''">
,#{id}
</if>
<if test="username!=null and username!=''">
,#{username}
</if>
<if test="password!=null and password!=''">
,#{password}
</if>
<if test="name!=null and name!=''">
,#{name}
</if>
</trim>
</insert>
<!--set标签-->
<update id="updateById">
update user
<!--标签动态的生成set关键字，智能的处理逗号-->
<set>
<if test="user.username!=null and user.username!=''">username=#{user.username},</if>
<if test="user.password!=null and user.password!=''">password=#{user.password},</if>
<if test="user.name!=null and user.name!=''">name=#{user.name},</if>
</set>
<where>
id=#{id}
</where>
</update>
<!--choose标签-->
<select id="findByUsernameOrPasswordOrName" resultType="user">
<include refid="selectUser"/>
<where>
<!--
username不为空则根据username查；
username为空，password不为空则根据password查；
前两者都为空则根据name查。
-->
<choose>
<when test="username!=null and username!=''">
and username=#{username}
</when>
<when test="password!=null and password!=''">
and password=#{password}
</when>
<otherwise>
and name=#{name}
</otherwise>
</choose>
</where>
</select>
</mapper>
springboot中mybatis定义类型转换器
https://blog.csdn.net/weixin_42097648/article/details/81227394
集成分页助手
<!--添加pagehelper依赖-->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.3</version>
</dependency>
四、多表操作
mybatis_duobiao.sql
/*
Navicat MySQL Data Transfer
Source Server : mysql_win10_local
Source Server Version : 80013
Source Host : localhost:3306
Source Database : mybatis_duobiao
Target Server Type : MYSQL
Target Server Version : 80013
File Encoding : 65001
Date: 2020-12-21 01:35:03
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for orders
-- ----------------------------
DROP TABLE IF EXISTS `orders`;
CREATE TABLE `orders` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`ordertime` varchar(255) DEFAULT NULL,
`total` double DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `uid` (`uid`),
CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of orders
-- ----------------------------
INSERT INTO `orders` VALUES ('1', '2018-12-12', '3000', '1');
INSERT INTO `orders` VALUES ('2', '2018-12-12', '4000', '1');
INSERT INTO `orders` VALUES ('3', '2018-12-12', '5000', '2');
-- ----------------------------
-- Table structure for role
-- ----------------------------
DROP TABLE IF EXISTS `role`;
CREATE TABLE `role` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`rolename` varchar(255) DEFAULT NULL,
`roleDesc` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of role
-- ----------------------------
INSERT INTO `role` VALUES ('1', 'CTO', 'CTO');
INSERT INTO `role` VALUES ('2', 'COO', 'COO');
-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(50) DEFAULT NULL,
`password` varchar(50) DEFAULT NULL,
`birthday` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'lucy', '123', '2018-12-12');
INSERT INTO `user` VALUES ('2', 'haohao', '123', '2019-12-12');
-- ----------------------------
-- Table structure for user_role
-- ----------------------------
DROP TABLE IF EXISTS `user_role`;
CREATE TABLE `user_role` (
`user_id` int(11) NOT NULL,
`role_id` int(11) NOT NULL,
PRIMARY KEY (`user_id`,`role_id`),
KEY `user_role_ibfk_2` (`role_id`),
CONSTRAINT `user_role_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`),
CONSTRAINT `user_role_ibfk_2` FOREIGN KEY (`role_id`) REFERENCES `role` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user_role
-- ----------------------------
INSERT INTO `user_role` VALUES ('1', '1');
INSERT INTO `user_role` VALUES ('2', '1');
INSERT INTO `user_role` VALUES ('1', '2');
INSERT INTO `user_role` VALUES ('2', '2');
一对一查询
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户
对应的sql语句
select * from orders o,user u where o.uid=u.id;
查询的结果
创建Order和User实体
public class User {
private Integer id;
private String username;
private String password;
private Date birthday;
//......
}
public class Order {
private Integer id;
private Date orderTime;
private Double total;
//......
}
创建OrderMapper接口
@Mapper
public interface OrderMapper {
List<Order> findAll();
}
配置OrderMapper.xml
<resultMap id="orderMap" type="order">
<result column="uid" property="user.id"></result>
<result column="username" property="user.username"></result>
<result column="password" property="user.password"></result>
<result column="birthday" property="user.birthday"></result>
</resultMap>
<select id="findAll" resultMap="orderMap">
select * from orders o,user u where o.uid=u.id;
</select>
其中
<resultMap id="orderMap" type="order">
<result column="id" property="id"></result>
<result column="ordertime" property="orderTime"></result>
<result column="total" property="total"></result>
<association property="user" javaType="user">
<result column="uid" property="id"></result>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
测试结果
@Test
void contextLoads() {
List<Order> orders = orderMapper.findAll();
orders.forEach(order -> System.out.println(order));
}
一对多查询
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单
对应的sql语句
select *,o.id oid from user u left join orders o on u.id=o.uid;
查询的结果
修改User实体
public class User {
private Integer id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
//......
}
创建UserMapper接口
@Mapper
public interface UserMapper {
List<User> findAll();
}
配置UserMapper.xml
<mapper namespace="com.kiramie.dao.UserMapper">
<resultMap id="userMapper" type="user">
<result column="id" property="id"></result>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<collection property="orderList" ofType="order">
<result column="oid" property="id"></result>
<result column="ordertime" property="orderTime"></result>
<result column="total" property="total"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="userMapper">
select *,o.id oid from user u left join orders o on u.id=o.uid;
</select>
</mapper>
测试结果
@Test
void contextLoads1() {
List<User> users = userMapper.findAll();
for (User user : users) {
System.out.println("id="+user.getId());
System.out.println("username="+user.getUsername());
user.getOrderList().forEach(order -> System.out.println(order));
System.out.println("------------------");
}
}
多对多查询
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色
对应的sql语句
select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_id inner join role r on ur.role_id=r.id;
-- 或者
select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_id ,role r where ur.role_id=r.id;
查询的结果
创建Role实体，修改User实体
public class Role {
private Integer id;
private String roleName;
private String roleDesc;
//......
}
public class User {
private Integer id;
private String username;
private String password;
private Date birthday;
//代表当前用户具备哪些订单
private List<Order> orderList;
//代表当前用户具备哪些角色
private List<Role> roleList;
//......
}
添加UserMapper接口方法
List<User> findAllUserAndRole();
配置UserMapper.xml
<resultMap id="userRoleMap" type="user">
<result column="id" property="id"></result>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<collection property="roleList" ofType="role">
<result column="rid" property="id"></result>
<result column="rolename" property="roleName"></result>
<result column="roledesc" property="roleDesc"></result>
</collection>
</resultMap>
<select id="findAllUserAndRole" resultMap="userRoleMap">
select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_id ,role r where ur.role_id=r.id;
</select>
测试结果
@Test
void contextLoads2() {
List<User> users = userMapper.findAllUserAndRole();
for (User user : users) {
System.out.println("id="+user.getId());
System.out.println("username="+user.getUsername());
user.getRoleList().forEach(role -> System.out.println(role));
System.out.println("------------------");
}
}
