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>

SysClassMapper.xml

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': '',
'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,处理会简单一些

上一篇:Windows下同一台机器上elasticsearch集群的配置以及elasticsearch-head插件的使用


下一篇:数据库(SQL SERVER)常用知识点