iis服务器助手广告广告
返回顶部
首页 > 资讯 > 数据库 >mysql8+mybatis-plus 查询json格式数据
  • 511
分享到

mysql8+mybatis-plus 查询json格式数据

javamysqlPoweredby金山文档 2023-10-26 18:10:00 511人浏览 独家记忆
摘要

sql 测试JSON表 CREATE TABLE `testjson` ( `id` int NOT NULL AUTO_INCREMENT, `json_obj` json DEFAULT NULL, `json_arr` jso

sql 测试JSON

CREATE TABLE `testjson` (  `id` int NOT NULL AUTO_INCREMENT,  `json_obj` json DEFAULT NULL,  `json_arr` json DEFAULT NULL,  `json_str` varchar(100) DEFAULT NULL,  PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;INSERT INTO test2.testjson(id, json_obj, json_arr, json_str)VALUES(1, '{"age": "1", "sex": "123"}', '[1, 2, 4]', '12');INSERT INTO test2.testjson(id, json_obj, json_arr, json_str)VALUES(2, '{"age": "1", "sex": "123"}', '[1, 2, 4]', '12');INSERT INTO test2.testjson(id, json_obj, json_arr, json_str)VALUES(3, '{"age": "1", "sex": "123"}', '[1, 2, 4]', '12');INSERT INTO test2.testjson(id, json_obj, json_arr, json_str)VALUES(4, '{"age": "1", "sex": "123"}', '[1, 2, 4]', '12');INSERT INTO test2.testjson(id, json_obj, json_arr, json_str)VALUES(5, '{"age": "1", "sex": "123"}', '[1, 2, 4]', '12');

后台SpringBoot 文件pom.xml

    4.0.0            org.springframework.boot        spring-boot-starter-parent        2.1.6.RELEASE                 com.example    springboot-test    0.0.1-SNAPSHOT    demo    Demo project for Spring Boot            8                            org.springframework.boot            spring-boot-starter-WEB                            com.baomidou            mybatis-plus-boot-starter            3.3.0                            Mysql            mysql-connector-java            8.0.11                            org.projectlombok            lombok                                                    org.springframework.boot                spring-boot-Maven-plugin                        

application.yml

spring:    dataSource:        driver-class-name: com.mysql.cj.jdbc.Driver        passWord: root        url: jdbc:mysql://localhost:3307/test2?characterEncoding=utf8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true        username: root

实体类User.java

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.JacksonTypeHandler;import lombok.Data;import java.io.Serializable;@Data@TableName(value = "testjson", autoResultMap=true)public class User implements Serializable {    private static final long serialVersionUID = 1L;    @TableId(value = "id", type = IdType.AUTO)    private Long id;    @TableField(typeHandler = JacksonTypeHandler.class)    private JsonObj jsonObj;    private String jsonArr;    private String jsonStr;}

JsonObj,java

@Datapublic class JsonObj {    private String sex;    private Integer age;}

UserMapper.java

@Mapperpublic interface UserMapper extends BaseMapper {    User selectById(Long id);    User selectByLike(String sex);
                                                   select * from testjson where json_obj->'$.sex' like '%'+#{sex}'+%'    
import com.springboot.mapper.UserMapper;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.GetMapping;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;@RestControllerpublic class TestController {    @Autowired    UserMapper userMapper;       @GetMapping("/testJson")    public String testJson(){        return "User===:"+userMapper.selectById(1L);    }    @GetMapping("/testJsonLike")    public String testJsonLike(){        return "User===:"+userMapper.selectByLike("123");    }}

备注:常用JSON函数

//插入和更新

JSON_SET和JSON_INSERT区别:set key存在会覆盖value,insert只会插入新的key,value

UPDATE testjson SET json_obj = JSON_SET(json_obj,'$.age','localhost','$.url','www.muscleape.com') WHERE id = 2;

UPDATE testjson SET json_obj = JSON_INSERT(json_obj,'$.url','www.test.com') WHERE id = 3;

//remove元素

UPDATE testjson SET json_obj = json_remove(json_obj, '$.age') WHERE id = 5;

//模糊查询

select * from testjson where json_obj->'$.url' like '%muscleape%'

来源地址:https://blog.csdn.net/u012257776/article/details/128804552

您可能感兴趣的文档:

--结束END--

本文标题: mysql8+mybatis-plus 查询json格式数据

本文链接: https://www.lsjlt.com/news/466971.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑,方便收藏和打印~

下载Word文档
猜你喜欢
软考高级职称资格查询
编程网,编程工程师的家园,是目前国内优秀的开源技术社区之一,形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容,为IT开发者提供了一个发现、使用、并交流开源技术的平台。
  • 官方手机版

  • 微信公众号

  • 商务合作