mybatis操作mysql的geometry数据（地理位置_经纬度）

最新推荐文章于 2024-07-09 11:09:43 发布

微辣都行的小胖子

最新推荐文章于 2024-07-09 11:09:43 发布

阅读量193

点赞数 2

分类专栏： MySQL语句、MyBatis语句篇 java笔记文章标签： mybatis mysql java

本文链接：https://blog.csdn.net/weixin_49423223/article/details/139548048

版权

MySQL语句、MyBatis语句篇同时被 2 个专栏收录

7 篇文章 0 订阅

订阅专栏

java笔记

7 篇文章 0 订阅

订阅专栏

pojo

   @ApiModelProperty(value = "地址经纬度")
    @TableField("location")
    private ArrayList<String> location;

Dto

 @ApiModelProperty(value = "导航地址/地址经纬度")
    private ArrayList<String> location;

Vo

   @ApiModelProperty(value = "地址经纬度")
    private String location;
    @ApiModelProperty(value = "地址经纬度数组")
    private ArrayList<Double> locationArray;

业务层

package com.lwc.service.impl;

import cn.hutool.core.bean.BeanUtil;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.lwc.commons.utils.UserThreadLocal;
import com.lwc.controller.req.ActivityCooperationSpacesDto;
import com.lwc.controller.resp.ActivityCooperationSpacesVo;
import com.lwc.orm.entity.ActivityCooperationSpaces;
import com.lwc.orm.mapper.ActivityCooperationSpacesMapper;
import com.s.service.IActivityCooperationSpacesService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;


/**
 * <p>
 * 分会长开发的活动合作空间表 服务实现类
 * </p>
 *
 * @author LiuWenCheng
 * @since 2024-05-30
 */
@Service
@Slf4j
public class ActivityCooperationSpacesServiceImpl extends ServiceImpl<ActivityCooperationSpacesMapper, ActivityCooperationSpaces> implements IActivityCooperationSpacesService {

    @Autowired
    private ActivityCooperationSpacesMapper activityCooperationSpacesMapper;


    /**
     * 获取所有活动合作空间
     *
     * @return
     */
    @Override
    public List<ActivityCooperationSpacesVo> selectAll() {
        //获取用户id
        String userId = UserThreadLocal.getSubject();

        List<ActivityCooperationSpacesVo> vo = activityCooperationSpacesMapper.selectAll(userId);

        log.info("获取所有活动合作空间:{}", vo);

        //POINT(133.333 37.11)写一个字符串分割的方法，以中间的空格分隔，我需要分别获取133.333和37.11存入数组中
        //遍历vo
        for (ActivityCooperationSpacesVo activityCooperationSpacesVo : vo) {
            //获取location字段
            String location1 = activityCooperationSpacesVo.getLocation();
            //分割字符串
            String location = location1.substring(6, location1.length() - 1);
            String[] split = location.split(" ");
            //获取经度
            String longitude = split[0];
            //获取纬度
            String latitude = split[1];

            //设置经度//设置纬度
            activityCooperationSpacesVo.setLocationArray(new ArrayList<>());
            activityCooperationSpacesVo.getLocationArray().add(Double.parseDouble(longitude));
            activityCooperationSpacesVo.getLocationArray().add(Double.parseDouble(latitude));

        }

        return vo;


    }

    /**
     * 新增活动合作空间
     *
     * @param dto
     */
    @Override
    public void saveCooperationSpaces(ActivityCooperationSpacesDto dto) {
        if (null == dto) {
            throw new RuntimeException("新增活动合作空间为空");
        }
        ActivityCooperationSpaces pojo = BeanUtil.copyProperties(dto, ActivityCooperationSpaces.class);
        String createdBy = UserThreadLocal.subjectThreadLocal.get();
        LocalDateTime createdAt = LocalDateTime.now();
        pojo.setCreatedBy(createdBy);
        pojo.setCreatedAt(createdAt);
        //把数组pojo.getLocation()转换成字符串
        if (null == pojo.getLocation()) {
            throw new RuntimeException("新增活动合作空间位置信息不能为空");
        }
        String s1 = pojo.getLocation().get(0);
        String s2 = pojo.getLocation().get(1);
        String location = "POINT(" + s1 + " " + s2 + ")";

        log.info("location:{}", location);


        activityCooperationSpacesMapper.saveCooperationSpaces(
                pojo.getSpacesName(),
                location,
                pojo.getAddress(),
                pojo.getContact(),
                pojo.getType(),
                pojo.getPolicy(),
                pojo.getNote(),
                pojo.getCreatedBy(),
                pojo.getCreatedAt()
        );

    }

    /**
     * 修改活动合作空间
     *
     * @param dto
     */
    @Override
    public void updateCooperationSpaces(ActivityCooperationSpacesDto dto) {
        ActivityCooperationSpaces pojo = BeanUtil.copyProperties(dto, ActivityCooperationSpaces.class);
        String createdBy = UserThreadLocal.subjectThreadLocal.get();
        //根据id查询
        ActivityCooperationSpaces activityCooperationSpaces = activityCooperationSpacesMapper.selectById(pojo.getSpacesId());
        if (null == activityCooperationSpaces) {
            throw new RuntimeException("修改活动合作空间不存在");
        }
        log.info("修改活动合作空间:{}", activityCooperationSpaces);
        if (!createdBy.equals(activityCooperationSpaces.getCreatedBy())) {
            throw new RuntimeException("您的权限不足，空间创建人才可以修改空间");
        }
        //把数组pojo.getLocation()转换成字符串

        String s1 = pojo.getLocation().get(0);
        String s2 = pojo.getLocation().get(1);
        String location = "POINT(" + s1 + " " + s2 + ")";
        log.info("location:{}", location);
        LocalDateTime updateAt = LocalDateTime.now();

        activityCooperationSpacesMapper.updateCooperationSpaces(
                pojo.getSpacesId(),
                pojo.getSpacesName(),
                location,
                pojo.getAddress(),
                pojo.getContact(),
                pojo.getType(),
                pojo.getPolicy(),
                pojo.getNote(),
                updateAt
        );
    }

    @Override
    public ActivityCooperationSpacesVo selectById(Integer spacesId) {
        ActivityCooperationSpacesVo activityCooperationSpacesVo= activityCooperationSpacesMapper.byId(spacesId);

        //获取location字段
        String location1 = activityCooperationSpacesVo.getLocation();
        //分割字符串
        String location = location1.substring(6, location1.length() - 1);
        String[] split = location.split(" ");
        //获取经度
        String longitude = split[0];
        //获取纬度
        String latitude = split[1];

        //设置经度//设置纬度
        activityCooperationSpacesVo.setLocationArray(new ArrayList<>());
        activityCooperationSpacesVo.getLocationArray().add(Double.parseDouble(longitude));
        activityCooperationSpacesVo.getLocationArray().add(Double.parseDouble(latitude));
        log.info("查询活动合作空间:{}", activityCooperationSpacesVo);

        return activityCooperationSpacesVo;
    }

    /**
     * 根据id删除活动合作空间
     *
     * @param spacesId
     */
    @Override
    public void deleteById(Integer spacesId) {
        if (null == spacesId){
            throw new RuntimeException("删除活动合作空间id不能为空");
        }
        String userId = UserThreadLocal.subjectThreadLocal.get();
        ActivityCooperationSpacesVo activityCooperationSpacesVo= activityCooperationSpacesMapper.byId(spacesId);
        if (!userId.equals(activityCooperationSpacesVo.getCreatedBy())){
            throw new RuntimeException("只有活动空间创建者才能删除");
        }
        activityCooperationSpacesMapper.deleteById(spacesId);

    }
}

Mapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.lwc.orm.mapper.ActivityCooperationSpacesMapper">


    <!--      查询所有活动合作空间-->
    <select id="selectAll" resultType="com.lwc.controller.resp.ActivityCooperationSpacesVo"
            parameterType="java.lang.String">
        select spaces_id,
               spaces_name,
               ST_AsText(location) AS location,
               address,
               contact,
               `type`,
               policy,
               note,
               created_at,
               update_at,
               created_by

        from activity_cooperation_spaces
        where created_by = #{userId}

    </select>


    <!--    // 新增活动合作空间-->
    <insert id="saveCooperationSpaces">
        insert into activity_cooperation_spaces
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="spacesName != null and spacesName != ''">spaces_name,</if>
            <if test="location != null">location,</if>
            <if test="address != null">address,</if>
            <if test="contact != null and contact != ''">contact,</if>
            <if test="type != null">`type`,</if>
            <if test="policy != null">policy,</if>
            <if test="note != null">note,</if>
            <if test="createdAt != null">created_at,</if>
            <if test="createdBy != null and createdBy != ''">created_by,</if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="spacesName != null and spacesName != ''">#{spacesName},</if>
            <if test="location != null">ST_GeomFromText(#{location}),</if>
            <if test="address != null">#{address},</if>
            <if test="contact != null and contact != ''">#{contact},</if>
            <if test="type != null">#{type},</if>
            <if test="policy != null">#{policy},</if>
            <if test="note != null">#{note},</if>
            <if test="createdAt != null">#{createdAt},</if>
            <if test="createdBy != null and createdBy != ''">#{createdBy},</if>
        </trim>
    </insert>


<!--    修改活动合作空间-->
    <update id="updateCooperationSpaces">

        UPDATE activity_cooperation_spaces
        <set>
            <if test="spacesName != null">spaces_name = #{spacesName},</if>
            <if test="location != null">location = ST_GeomFromText(#{location}),</if>
            <if test="address != null">address = #{address},</if>
            <if test="contact != null">contact = #{contact},</if>
            <if test="type != null">`type` = #{type},</if>
            <if test="policy != null">policy = #{policy},</if>
            <if test="note != null">note = #{note},</if>
            <if test="updateAt != null">update_at = #{updateAt},</if>
        </set>
        WHERE spaces_id = #{spacesId}


    </update>

<!--    根据id查询活动合作空间-->
    <select id="byId" resultType="com.lwc.controller.resp.ActivityCooperationSpacesVo">

        select spaces_id,
               spaces_name,
               ST_AsText(location) AS location,
               address,
               contact,
               `type`,
               policy,
               note,
               created_at,
               update_at,
               created_by
        from activity_cooperation_spaces
        where spaces_id = #{spacesId}
    </select>

</mapper>