#持久化框架操作示例-Mybatis
# 环境说明
以使用Maven为例：
Java环境：Jdk8 or Jdk11
Springboot版本：2.6.2
Maven版本：3.8.6
Mybatis-plus版本：3.5.2
Maven的pom.xml文件：（用于验证整个Mybatis-plus与YashanDB的兼容性，所以涵盖很多的依赖包，如只需其中一种或者几种功能，保留相应的依赖即可。）
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter</artifactId>
</dependency>
<!-- 多数据源配置-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>dynamic-datasource-spring-boot-starter</artifactId>
<version>3.5.1</version>
</dependency>
<!-- Mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.5.2</version>
</dependency>
<!-- MySQL连接器，测试多数据源使用，YashanDB的连接驱动采用手动导入-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>
<!-- 测试Json序列化标签-->
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-core</artifactId>
<version>2.13.3</version>
</dependency>
<dependency>
<groupId>com.fasterxml.jackson.core</groupId>
<artifactId>jackson-databind</artifactId>
<version>2.13.3</version>
</dependency>
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.9.1</version>
</dependency>
</dependencies>
# 添加YashanDB驱动
打开Idea的ProjectStructure，使用Libraries添加YashanDB的连接驱动，对于多模块，只需要添加到对应的模块即可。（YashanDB驱动：在产品软件包或安装目录的Drivers文件夹中，查找yasdb-jdbc-版本号.jar文件，例如yasdb-jdbc-22.1.jar）
也可以将YashanDB驱动上传到本地的Maven私服，然后在Pom文件中引入。
# 配置数据源
# 单数据源配置（通过Hikari连接池）
本文采用Springboot最常用的方式导入数据源，即通过application.yml导入。此外，可选的数据库连接池有多种，本文采用Springboot自带的Hikari连接池。请将下例中的host_ip、dbname、username和password修改为实际值。
spring:
datasource:
url: jdbc:yasdb://host_ip:1688/dbname
driver-class-name: com.yashandb.jdbc.Driver
username: sys
password: sys
# 多数据源配置（通过Hikari连接池）
多数据源配置采用dynamic-datasource-spring-boot-starter，本文以YashanDB与MySQL两个数据源为例，进行两个数据源的切换，用户可以自行选择其他数据源。请将下例中的host_ip、dbname、username和password修改为实际值。
spring:
datasource:
dynamic:
primary: master
strict: false
datasource:
master:
url: jdbc:yasdb://host_ip:1688/dbname
driver-class-name: com.yashandb.jdbc.Driver
username: sys
password: sys
slave1:
url: jdbc:mysql://host_ip:3306/sys
driver-class-name: com.mysql.cj.jdbc.Driver
username: test
password: test
# Mybatis-plus功能验证
# 数据库脚本
--创建序列
CREATE SEQUENCE user_index;
--建表语句
CREATE TABLE user1
(
id BIGINT(20) DEFAULT user_index.NEXTVAL NOT NULL, --自增序列需要创建
name VARCHAR(30) NULL,
age INT(11) NULL ,
email VARCHAR(50) NULL ,
PRIMARY KEY (id)
);
--插入数据
INSERT INTO USER1 (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');
# 创建entity
创建User用户的entity，并使其与Table对应。
import com.baomidou.mybatisplus.annotation.*;
import lombok.Data;
import java.util.List;
@Data
@KeySequence("user_index")//自增序列，type=IdType.INPUT时，需要自定义自增序列，ps：使用自增序列之前，需要去数据库创建。
@TableName("user1")
public class User {
@TableId(type = IdType.INPUT)
private Long id;
private String name;
private Integer age;
private String email;
// @TableField(typeHandler = JacksonTypeHandler.class) 这里typeHandler采用Jackson来测试，Pom文件需要引入对应的依赖。
// private UserInfo userInfo;
}
使用type=IdType.INPUT时，自定义KeyGenerator：
import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.core.incrementer.IKeyGenerator;
public class YashanKeyGenerator implements IKeyGenerator {
@Override
public String executeSql(String incrementerName) {
return "select "+ incrementerName +".nextval from dual";
}
@Override
public DbType dbType() {
return null; //Mybatis-plus中没有YashanDB的类型，先设置为null。
}
}
import com.baomidou.mybatisplus.core.incrementer.IKeyGenerator;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
@Configuration
public class KeyGeneratorConfiguration {
@Bean
public IKeyGenerator keyGenerator(){
return new YashanKeyGenerator();
}
}
# 创建User的Mapper文件
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.samples.quickstart.entity.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper extends BaseMapper<User> {
List<User> selectUsers();
}
# 创建单元测试类
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.samples.quickstart.entity.User;
import com.baomidou.mybatisplus.samples.quickstart.mapper.UserMapper;
import com.zaxxer.hikari.HikariDataSource;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;
import javax.annotation.Resource;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
@SpringBootTest
public class QuickStartTest {
@Resource
private UserMapper userMapper;
@Test
public void testSelect() {
System.out.println(("----- selectAll method test ------"));
List<User> userList = userMapper.selectList(null);
userList.forEach(System.out::println);
}
@Test
public void testSelect2(){
User user = userMapper.selectById(1L);
System.out.println(user);
}
@Test
public void testSelect3(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
LambdaQueryWrapper<User> select = wrapper.lambda().select(User::getId, User::getAge);
List<User> users = userMapper.selectList(select);
users.forEach(System.out::println);
}
@Test
public void testSelect4(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> allEq = wrapper.allEq(new HashMap<String, Object>() {{
put("id", 1);
put("name", "Jone");
}}, true);
List<User> users = userMapper.selectList(allEq);
users.forEach(System.out::println);
}
@Test
public void testSelect5(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> eq = wrapper.eq("name", "Jone");
List<User> users = userMapper.selectList(eq);
users.forEach(System.out::println);
}
@Test
public void testSelect6(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> ne = wrapper.ne("id", 1L);
List<User> users = userMapper.selectList(ne);
users.forEach(System.out::println);
}
@Test
public void testSelect7(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> gt = wrapper.gt("id", 2L);
List<User> users = userMapper.selectList(gt);
users.forEach(System.out::println);
}
@Test
public void testSelect8(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> ge = wrapper.ge("id", 2L);
List<User> users = userMapper.selectList(ge);
users.forEach(System.out::println);
}
@Test
public void testSelect9(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> between = wrapper.between("id", 2L, 4L);
List<User> users = userMapper.selectList(between);
users.forEach(System.out::println);
}
@Test
public void testSelect10(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> between = wrapper.notBetween("id", 3L, 4L);
List<User> users = userMapper.selectList(between);
users.forEach(System.out::println);
}
@Test
public void testSelect11(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> like = wrapper.like("name", "J");
List<User> users = userMapper.selectList(like);
users.forEach(System.out::println);
}
@Test
public void testSelect12(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> like = wrapper.notLike("name", "J");
List<User> users = userMapper.selectList(like);
users.forEach(System.out::println);
}
@Test
public void testSelect13(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> like = wrapper.likeLeft("name", "J");
List<User> users = userMapper.selectList(like);
users.forEach(System.out::println);
}
@Test
public void testSelect14(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> like = wrapper.likeRight("name", "J");
List<User> users = userMapper.selectList(like);
users.forEach(System.out::println);
}
@Test
public void testSelect15(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> id = wrapper.isNotNull("id");
List<User> users = userMapper.selectList(id);
users.forEach(System.out::println);
}
@Test
public void testSelect16(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> id = wrapper.isNull("id");
List<User> users = userMapper.selectList(id);
users.forEach(System.out::println);
}
@Test
public void testSelect17(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> in = wrapper.in("id", new ArrayList<Long>(){{
add(1L);
add(2L);
}});
List<User> users = userMapper.selectList(in);
users.forEach(System.out::println);
}
@Test
public void testSelect18(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
QueryWrapper<User> inSql = wrapper.inSql("id", "select id from user1 where id < 4");
List<User> users = userMapper.selectList(inSql);
users.forEach(System.out::println);
}
@Test
public void testSelect19(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.select("age", "count(age)").groupBy("age");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
@Test
public void testSelect20(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.orderByDesc("id", "age");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
@Test
public void testSelect21(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.func(i -> {
if (false) {
i.eq("id", 1);
}else {
i.eq("id", 2);
}
});
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
@Test
public void testSelect22(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.eq("id", 1).or().likeRight("name", "J");
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
@Test
public void testSelect23(){
QueryWrapper<User> wrapper = new QueryWrapper<>();
wrapper.and(i -> i.eq("id", 1).likeRight("name", "J"));
List<User> users = userMapper.selectList(wrapper);
users.forEach(System.out::println);
}
}
下载文档
复制链接