sql 测试JSON表 CREATE TABLE `testjson` ( `id` int NOT NULL AUTO_INCREMENT, `json_obj` json DEFAULT NULL, `json_arr` jso
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);
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文档到电脑,方便收藏和打印~
2024-05-16
2024-05-16
2024-05-16
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
2024-05-15
回答
回答
回答
回答
回答
回答
回答
回答
回答
回答
0