栏目分类:
子分类:
返回
名师互学网用户登录
快速导航关闭
当前搜索
当前分类
子分类
实用工具
热门搜索
名师互学网 > IT > 软件开发 > 后端开发 > Java

Springboot项目使用Mysql的json类型来存取数据。

Java 更新时间: 发布时间: IT归档 最新发布 模块sitemap 名妆网 法律咨询 聚返吧 英语巴士网 伯小乐 网商动力

Springboot项目使用Mysql的json类型来存取数据。

为什么要使用json存取数据呢,个人认为可以减少数据表的字段。不过代码上可能会比较复杂,特别是层级关系很深的时候。

第一步:创建一个数据表,定义一个字段使用json来存储数据

第二步:创建实体和实体VO

package com.example.tourismmanagement.entity;

import cn.hutool.json.JSONUtil;
import com.example.tourismmanagement.entity.vo.InformationVO;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;


@Data
@NoArgsConstructor
@AllArgsConstructor
public class Information {

    // 资讯id
    private Integer id;

    //资讯信息
    private String dataInfo;

    //资讯类型
    private Integer type;

    //创建时间
    private String createTime;

    //修改时间
    private String updateTime;

    //InformationVO转Information
    public Information(InformationVO informationVO){
        this.id = informationVO.getId();
        this.dataInfo = JSONUtil.toJsonStr(informationVO.getDataInfo());
        this.type = informationVO.getType();
        this.createTime = informationVO.getCreateTime();
        this.updateTime = informationVO.getUpdateTime();
    }
}
package com.example.tourismmanagement.entity.vo;

import cn.hutool.json.JSONObject;
import lombok.Data;


@Data
public class InformationVO {

    // 资讯id
    private Integer id;

    //资讯信息
    private JSONObject dataInfo;

    //资讯类型
    private Integer type;

    //创建时间
    private String createTime;

    //修改时间
    private String updateTime;

}

第三步:创建Mapper,Mapper.xml,Service,ServiceImpl,Controller等

package com.example.tourismmanagement.mapper;

import cn.hutool.json.JSONObject;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.tourismmanagement.entity.Information;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;


@Mapper
public interface InformationMapper {


    
    List list(JSONObject jsonObject);

    
    int insert(Information information);

    
    int update(Information information);

    
    int delete(@Param("ids") List ids);

}




    
        id,
        data_info,
        type,
        create_time,
        update_time
    

    
        select
            
        from
            information
        
            
                and type = #{type}
            
            
                and data_info ->> '$.title' like concat('%', #{title}, '%')
            
            
                and data_info ->> '$.content' like concat('%', #{content}, '%')
            
            
                and create_time = #{createTime}
            
            
                and update_time = #{updateTime}
            
        

    

    
        insert into information(data_info,type)
        values (#{dataInfo},#{type})
    


    
        update information
        
            
                data_info = #{dataInfo},
            
            
                type = #{type},
            
            
                create_time = #{createTime},
            
            
                update_time = #{updateTime}
            
        
        where id = #{id}
    
    
        delete from information
        where id in 
            #{item}
        
    


    

package com.example.tourismmanagement.service;

import cn.hutool.json.JSONObject;
import com.example.tourismmanagement.entity.Information;
import com.example.tourismmanagement.entity.common.Result;
import com.example.tourismmanagement.entity.vo.InformationVO;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.concurrent.ExecutionException;


public interface InformationService {


    
    Result list(JSONObject jsonObject) throws ExecutionException, InterruptedException;

    
    int insert(InformationVO informationVO);

    
    int update(InformationVO informationVO);

    
    int delete(List ids);
}
package com.example.tourismmanagement.service.impl;

import cn.hutool.json.JSONObject;
import cn.hutool.json.JSONUtil;
import com.example.tourismmanagement.config.TreadPoolConfig;
import com.example.tourismmanagement.entity.Information;
import com.example.tourismmanagement.entity.common.Result;
import com.example.tourismmanagement.entity.vo.InformationVO;
import com.example.tourismmanagement.mapper.InformationMapper;
import com.example.tourismmanagement.service.InformationService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;
import java.util.stream.Collectors;


@Service
@Slf4j
public class InformationServiceImpl implements InformationService {

    private final InformationMapper informationMapper;

    public InformationServiceImpl(InformationMapper informationMapper) {
        this.informationMapper = informationMapper;
    }



    @Override
    public Result list(JSONObject jsonObject) throws ExecutionException, InterruptedException {
        CompletableFuture> list = CompletableFuture.supplyAsync(
                () -> informationMapper.list(jsonObject).stream().map(o -> {
                    InformationVO informationVO = new InformationVO();
                    informationVO.setId(o.getId());
                    informationVO.setDataInfo(JSONUtil.parseObj(o.getDataInfo()));
                    informationVO.setCreateTime(o.getCreateTime());
                    informationVO.setUpdateTime(o.getUpdateTime());
                    return informationVO;
                }).collect(Collectors.toList()), TreadPoolConfig.TEST_EXECUTOR_01);
        List informations = list.get();
        return Result.success(new JSONObject().putOpt("informations", informations));
    }

    @Transactional
    @Override
    public int insert(InformationVO informationVO) {
        log.info("informationVO :{}",informationVO);
        return informationMapper.insert(new Information(informationVO));
    }

    @Transactional
    @Override
    public int update(InformationVO informationVO) {
       return informationMapper.update(new Information(informationVO));
    }

    @Transactional
    @Override
    public int delete(List ids) {
       return informationMapper.delete(ids);
    }
}
package com.example.tourismmanagement.controller;

import cn.hutool.json.JSONObject;
import com.example.tourismmanagement.entity.Information;
import com.example.tourismmanagement.entity.common.Result;
import com.example.tourismmanagement.entity.vo.InformationVO;
import com.example.tourismmanagement.service.InformationService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.util.Assert;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.concurrent.ExecutionException;


@Slf4j
@RestController
@RequestMapping("/information")
public class InformationController {

    private final InformationService informationService;

    public InformationController(InformationService informationService) {
        this.informationService = informationService;
    }

    
    @RequestMapping("/list")
    public Result list(@RequestBody JSONObject jsonObject) throws ExecutionException, InterruptedException {
      return informationService.list(jsonObject);
    }

    
    @RequestMapping("/insert")
    public Result insert(@RequestBody InformationVO informationVO) throws ExecutionException, InterruptedException {
        return Result.success(informationService.insert(informationVO));
    }

    
    @RequestMapping("/update")
    public Result update(@RequestBody InformationVO informationVO) throws ExecutionException, InterruptedException {
        Assert.notNull(informationVO.getId(),"修改时id不能为空!");
        return Result.success(informationService.update(informationVO));
    }

    
    @RequestMapping("/delete")
    public Result delete(@RequestBody List ids) {
        log.info("ids:{}",ids);
        Assert.notEmpty(ids,"id不能为空!");
        return Result.success(informationService.delete(ids));
    }
}
 

最后一步:测试使用JSON格式进行插入、修改、删除和查询(使用postman)

插入

 查询

修改和删除都差不多就贴图了。

转载请注明:文章转载自 www.mshxw.com
我们一直用心在做
关于我们 文章归档 网站地图 联系我们

版权所有 (c)2021-2022 MSHXW.COM

ICP备案号:晋ICP备2021003244-6号