相关依赖的导入
官方文档地址:https://baomidou.com/pages/bab2db/#release
<!--mybatisplus Springboot快速整合模块-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.0.5</version>
</dependency>
<!--lombok-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
</dependency>
<!--mysql 連接工具-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
除了官方文档里的组件这里建议再使用lombok并整合mysql连接工具
配置文件的编写
值得注意的是driver这个属性的编写,mysql5.0用的驱动是com.mysql.jdbc.Driver
mysql8.0之后用的commysql.cj.jdbc.Driver
mysql8.0的驱动是可以兼容5.0的。但是需要在URL之后增加时区的配置否则会报错
ServerTimeZone=GMT%2B8
userSSL连接,win系统下这个配置不会有什么影响,但是mac环境下使用true会报错userSSL=false
数据库的设计及相关数据的创建
CREATE TABLE `order_sheet` (
`orderId` varchar(64) NOT NULL,
`creat_time` datetime NOT NULL,
`pay_time` datetime DEFAULT NULL,
`user_id` int(11) NOT NULL,
`back_record` varchar(255) DEFAULT NULL,
`delete_int` int(1) DEFAULT NULL,
`update_by` varchar(255) DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`orderId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;
-- ----------------------------
-- Records of order_sheet
-- ----------------------------
INSERT INTO `order_sheet` VALUES ('1', '2022-02-17 14:31:15', null, '7', null, '0', 'SYSTEM', '2022-02-17 14:31:25');
INSERT INTO `order_sheet` VALUES ('2', '2022-02-17 14:39:08', null, '4', null, '0', 'SYSTEM', '2022-02-17 14:39:17');
INSERT INTO `order_sheet` VALUES ('3', '2022-02-17 14:39:22', null, '2', null, '0', 'SYSTEM', '2022-02-17 14:39:35');
INSERT INTO `order_sheet` VALUES ('4', '2022-02-17 14:39:40', null, '3', null, '0', 'SYSTEM', '2022-02-17 14:39:54');
INSERT INTO `order_sheet` VALUES ('5', '2022-02-17 14:39:59', null, '7', null, '0', 'SYSTEM', '2022-02-17 14:40:11');
INSERT INTO `order_sheet` VALUES ('6', '2022-02-17 14:40:19', null, '1', null, '0', 'SYSTEM', '2022-02-17 14:40:29');
INSERT INTO `order_sheet` VALUES ('7', '2022-02-17 14:40:34', null, '5', null, '0', 'SYSTEM', '2022-02-17 14:40:44');
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`name` varchar(30) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', 'tom', '7', '123@qq.com');
INSERT INTO `user` VALUES ('2', 'air', '6', '123@qq.com');
INSERT INTO `user` VALUES ('3', '娇娇', '18', 'xxx@163.com');
INSERT INTO `user` VALUES ('4', '娇娇', '19', 'xxx@163.com');
INSERT INTO `user` VALUES ('5', '凉凉', '19', null);
INSERT INTO `user` VALUES ('6', '呜呜', '26', null);
INSERT INTO `user` VALUES ('7', '王帅', '85', '4568@qq.com');
创建相应的实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {
public int id;
public String name;
public int age;
public String email;
}
创建相应的mapper映射
@Repository
public interface UserMapper extends BaseMapper<User> {
@Select("SELECT * from `user` right JOIN order_sheet ON user_id = id ")
List<User> selectUserListPage(Page<User> pagination);
}
完成所有的工作就可以创建测试类进行测试了
@SpringBootTest
class DemoApplicationTests {
@Autowired
private UserMapper userMapper ;
@Autowired
private MybatisServiceImpl mybatisService;
@Test
void contextLoads() {
//使用baseMapping中的方法直接查询
List<User> users = userMapper.selectList(null);
users.forEach(user -> System.out.println(user));
//使用条件构造器查询
Map<String, Object> stringStringHashMap = new HashMap<>();
stringStringHashMap.put("email",null);
Map<String, Object> stringStringHashMap2 = new HashMap<>();
stringStringHashMap2.put("age",30);
QueryWrapper<User> queryWrapper = new QueryWrapper();
queryWrapper.allEq(stringStringHashMap, true);
queryWrapper.le("age",30);
// userMapper.selectList(queryWrapper).forEach(System.out::println);
// Page<User> page = new Page<User>(1, 5);
// page.setRecords(userMapper.selectUserListPage(page));
//使用分页插件进行分页查询 userMapper.selectList(queryWrapper).forEach(System.out::println);
Page<User> page = new Page<User>(1, 5);
page.setRecords(userMapper.selectUserListPage(page));
//
// System.out.println(page.getRecords());
}
}
mybatisplus内置的两个插件
这里重点介绍两种插件,第一种是分页插件,配置的方式也很简单,只需要打开一个配置类进行配置即可
@EnableTransactionManagement
@Configuration
@MapperScan("com.test.demo.mapper")
public class MybatisConfig {
/**
* mybatis-plus SQL执行效率插件【生产环境可以关闭】
*/
@Bean
public PerformanceInterceptor performanceInterceptor() {
return new PerformanceInterceptor();
}
/*
* 分页插件,自动识别数据库类型 多租户,请参考官网【插件扩展】
*/
@Bean
public PaginationInterceptor paginationInterceptor() {
return new PaginationInterceptor();
}
}
另外mybatisplus也提供了sql查询工具,只需要在properties配置文件中加入mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl即可
User(id=5, name=凉凉, age=19, email=null)
User(id=6, name=呜呜, age=26, email=null)
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@59696551] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1686965869 wrapping com.mysql.cj.jdbc.ConnectionImpl@12e0f1cb] will not be managed by Spring
JsqlParserCountOptimize sql=SELECT * from `user` right JOIN order_sheet ON user_id = id
==> Preparing: SELECT COUNT(1) FROM `user` RIGHT JOIN order_sheet ON user_id = id
==> Parameters:
<== Columns: COUNT(1)
<== Row: 7
==> Preparing: SELECT * from `user` right JOIN order_sheet ON user_id = id LIMIT 0,5
==> Parameters:
<== Columns: id, name, age, email, orderId, creat_time, pay_time, user_id, back_record, delete_int, update_by, update_time
<== Row: 7, 王帅, 85, 4568@qq.com, 1, 2022-02-17 14:31:15, null, 7, null, 0, SYSTEM, 2022-02-17 14:31:25
<== Row: 4, 娇娇, 19, xxx@163.com, 2, 2022-02-17 14:39:08, null, 4, null, 0, SYSTEM, 2022-02-17 14:39:17
<== Row: 2, air, 6, 123@qq.com, 3, 2022-02-17 14:39:22, null, 2, null, 0, SYSTEM, 2022-02-17 14:39:35
<== Row: 3, 娇娇, 18, xxx@163.com, 4, 2022-02-17 14:39:40, null, 3, null, 0, SYSTEM, 2022-02-17 14:39:54
<== Row: 7, 王帅, 85, 4568@qq.com, 5, 2022-02-17 14:39:59, null, 7, null, 0, SYSTEM, 2022-02-17 14:40:11
<== Total: 5
Time:32 ms - ID:com.test.demo.mapper.UserMapper.selectUserListPage
Execute SQL:SELECT * from `user` right JOIN order_sheet ON user_id = id LIMIT 0,5