springboot+mybatis环境的坑和sql语句简化技巧

1.springfox-swagger实体类无限递归

https://hacpai.com/article/1525674135818 里面有不完美的解决方案

不用动源码的解决方案也有，在swagger项目里的函数传入参数没有类互相包含就可以了

这也要求某些多对多关系的实体类的属性定义要仔细走关系图

比如User和SysClass，用在swagger传入参数的实体类不能互相包含List<SysClass>和List<User>

只能继承VO（这个VO不能作为swagger函数的传入参数），把对应的包含属性写进去，然后需要查询对应列表时返回VO就行了

 

2.有中间表的mybatis一对多查询

查看某些有明确一对多关系的实体类的mybatis xml，可以看到查询方式是主表join子表，然后在mybatis这端把主表的数据group起来作为某个对象的元素

由此可见没有直接关联的2个表对应的类也可以用这种方式查询

<?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.ass.core.mapper.SysClassMapper">

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap type="com.ass.core.entity.SysClass" id="classMap">
        <result property="id" column="cid"/>
        <result property="code" column="code"/>
        <result property="name" column="name"/>
        <result property="num" column="num"/>
        <result property="description" column="description"/>
    </resultMap>
    <resultMap type="com.ass.core.VO.ClassWithUser" id="classUser">
        <result property="id" column="cid"/>
        <result property="code" column="code"/>
        <result property="name" column="name"/>
        <result property="num" column="num"/>
        <result property="description" column="description"/>
        <collection property="teachers" ofType="User">
            <id column="id" jdbcType="INTEGER" property="id" />
            <result column="uid" jdbcType="VARCHAR" property="uid" />
            <result column="user_account" jdbcType="VARCHAR" property="userAccount" />
            <result column="password" jdbcType="VARCHAR" property="password" />
            <result column="username" jdbcType="VARCHAR" property="username" />
            <result column="user_identity" jdbcType="INTEGER" property="userIdentity" />
            <result column="user_status" jdbcType="INTEGER" property="userStatus" />
            <result column="user_type" jdbcType="INTEGER" property="userType" />
            <result column="user_email" jdbcType="VARCHAR" property="userEmail" />
            <result column="user_mobile" jdbcType="VARCHAR" property="userMobile" />
            <result column="user_remind" jdbcType="VARCHAR" property="userRemind" />
            <result column="user_create_time" jdbcType="TIMESTAMP" property="userCreateTime" />
            <result column="user_update_time" jdbcType="TIMESTAMP" property="userUpdateTime" />
            <result column="class_id" jdbcType="VARCHAR" property="classId" />
            <result column="user_col2" jdbcType="VARCHAR" property="userCol2" />
            <result column="user_col3" jdbcType="VARCHAR" property="userCol3" />
            <result column="user_token" jdbcType="VARCHAR" property="userToken" />
            <result column="role_id" jdbcType="VARCHAR" property="roleId" />
        </collection>
    </resultMap>

    <select id="selectClassListByName" resultMap="classUser">
        SELECT
            c.id as cid,
            c.code,
            c.name,
            c.num,
            c.description,
            u.*
        FROM
            sys_class c
        LEFT JOIN user_class uc ON c.id = uc.class_id
        LEFT JOIN user u ON u.uid = uc.user_id
        WHERE
            c.name like '%${name}%'
    </select>
    
</mapper>

 

3.swagger传入参数含有对象数组时的坑点

swagger前端在碰到对象数组（无论是直接传的还是实体类里面的）时，会显示xxx[0].id和xxx[0].name之类的参数，但是直接通过前端发送请求，会因为url含有非法字符被拒绝请求

如果是用其他程序提交，例如Python(先安装requests扩展包)，可以提交这种带对象数组的请求，并让服务器接收

import requests
import json

def login():
    res = requests.post('http://localhost:20005/api/user/login?userAccount=admin&userPassword=admin', verify=False)
    myj = json.loads(res.text)
    #print(myj)
    return myj['data']['token']
#
def addContent():
    token = login()
    params = {
        'resourceSteps[0].procedureName': 'ytmym', 
        'resourceSteps[0].procedureContent': 'mytm', 
        'resourceSteps[2].procedureName': 'loveZJJ', 
        'resourceSteps[2].procedureContent': 'asswecan', 
        'id': '45', 
        'resourceContentId': '45j45j', 
        'experimentAdd': '5j5j', 
        'experimentAdd1': 'grh', 
        'stepsjson': '[{"procedureName":"a","procedureContent":"b"}]', 
        'token': token, 
        'resourceManagementId': 'j54j45j', 
        'experimentalObjective': '45j45j4', 
        'experimentalContext': '45j45j', 
        'experimentalRequirements': '5j45j', 
        'prepareKnowledge': '54j45j',
        'experimentSummary': 'j54j', 
        'experimentTestId': 'h4h54'
    }
    url = 'http://localhost:20005/api/resource/add/content'
    res = requests.post(url, params=params)
    print(res.text)
#
if __name__ == '__main__':
    addContent()

但是这种实现方式不太推荐，太不直观了。建议使用@ResponseBody注解对象数组或包含此类型的实体类，然后让前端传入json，处理会简单一些