概要
Mysql 5.7.8开始支持Json对象和Json数组,但在Mysql 8版本中使用Json性能更佳。
使用Json格式的好处:
- 无须预定义字段:字段可以无限拓展,避免了ALTER ADD COLUMN的操作,使用更加灵活。
- 处理稀疏字段:避免了稀疏字段的NULL值,避免冗余存储。
- 支持索引:相比于字符串格式的JSON,JSON类型支持索引做特定的查询优化。
整体实现流程
- 查看Mysql版本
SELECT VERSION();
2、创建mysql表
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`text` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;
3、定义实体类
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.extension.handlers.FastjsonTypeHandler;
import lombok.Data;
@Data
//开启自动映射
@TableName(value = "test",autoResultMap = true)
public class Test {
@TableId(type = IdType.AUTO)
private Integer id;
//定义Json字段handler
@TableField(typeHandler = FastjsonTypeHandler.class)
private JsonNode text;
}
import lombok.Data;
import java.io.Serializable;
@Data
public class JsonNode implements Serializable {
private Integer id;
private String name;
private Integer age;
}
4、定义Mapper、Service、ServiceImpl
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.yiyou.base.entity.Test;
public interface TestMapper extends BaseMapper<Test> {
}
import com.baomidou.mybatisplus.extension.service.IService;
import com.yiyou.base.entity.Test;
public interface TestService extends IService<Test> {
boolean insert(Test test);
}
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.yiyou.base.entity.Test;
import com.yiyou.base.mapper.TestMapper;
import com.yiyou.base.service.TestService;
import org.springframework.stereotype.Service;
@Service
public class TestServiceImpl extends ServiceImpl<TestMapper, Test> implements TestService {
@Override
public boolean insert(Test test) {
return this.saveOrUpdate(test);
}
}
5、Controller层实现
import com.baomidou.mybatisplus.extension.conditions.query.LambdaQueryChainWrapper;
import com.yiyou.base.entity.Test;
import com.yiyou.base.service.TestService;
import com.yiyou.model.R;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import java.util.List;
import java.util.Objects;
@Slf4j
@Api(tags = "test")
@RestController
@RequestMapping("/test")
public class TestController {
@Autowired
private TestService testService;
@ApiOperation("新增")
@PostMapping("/save")
public R<Boolean> save(@RequestBody Test test) {
return R.ok(testService.insert(test));
}
@ApiOperation("根据Id获取对象")
@GetMapping("/getById/{id}")
public R<Test> getById(@PathVariable("id") Integer id) {
return R.ok(testService.getById(id));
}
@ApiOperation("根据Id删除")
@DeleteMapping("/deleteById/{id}")
public R<Boolean> deleteById(@PathVariable("id") Integer id) {
return R.ok(testService.removeById(id));
}
@ApiOperation("条件查询")
@PostMapping("/findList")
public R<List<Test>> findList(@RequestBody Test test) {
LambdaQueryChainWrapper<Test> queryWrapper = testService.lambdaQuery();
queryWrapper
.eq(Objects.nonNull(test.getId()),Test::getId,test.getId())
// .apply(Objects.nonNull(test.getText()),"text -> '$.name' LIKE CONCAT('%',{0},'%')",test.getText().getName())
.apply(Objects.nonNull(test.getText()), "text -> '$.age' = {0}", test.getText().getAge());
// .like(Objects.nonNull(test.getText()),Test::getText,test.getText());
return R.ok(queryWrapper.list());
}
技术细节
- Json字段模糊查询
SELECT * FROM TEST WHERE text -> '$[*].name' like '%测%'
或 上面的"*"也可以使用下标
SELECT * FROM TEST WHERE text -> '$[1].name' like '%测%'
-
Json字段精确查询
-
使用箭头函数
SELECT * FROM TEST WHERE text -> '$.name' = '测试'
- 使用 JSON_CONTAINS
SELECT * FROM TEST WHERE JSON_CONTAINS(text,JSON_OBJECT('name', '测试'))
- 查询json中的name字段
SELECT id, text -> '$[*].name' AS name FROM TEST;
- 使用JSON_EXTRACT 函数,带双引号
SELECT id, JSON_EXTRACT( text, '$[*].name' ) AS name FROM TEST;
- 使用 JSON_UNQUOTE 函数,不带双引号
SELECT id, JSON_UNQUOTE(text, '$[*].name' ) AS name FROM TEST;
- 查询Json字段中所有的值,用 “*”
SELECT id, text -> '$[*].*' AS name FROM TEST;
Mybatis Plus使用LambdaQueryChainWrapper查询
提示:使用apply方法拼接sql片段,apply 是可以通过占位符的形式,传入多个参数。
文章来源:https://www.toymoban.com/news/detail-772079.html
例如:文章来源地址https://www.toymoban.com/news/detail-772079.html
LambdaQueryChainWrapper<Test> queryWrapper = testService.lambdaQuery();
queryWrapper
.apply(Objects.nonNull(test.getText()),"text -> '$.name' LIKE CONCAT('%',{0},'%')",test.getText().getName())//模糊查询
.apply(Objects.nonNull(test.getText()), "text -> '$.age' = {0}", test.getText().getAge());//精确查询
到了这里,关于Mybatis-Plus处理Mysql Json类型字段的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!