一、MyBatis常见问题
介绍项目中遇见的常见问题，便于分析及快速定位问题。
1.非空判断
1.1 字符串非空判断 != 与 单引号与双引号
1.2 数字或字符串相等的判断 == 异常：（根据条件查询字段）
<choose>
<!-- 集团、区客：人均日拜访目标为3 -->
<when test="basePro.dataType=0">
3,
</when>
<when test="basePro.dataType=2">
3,
</when>
<!-- 大客：人均日拜访目标为2 -->
<when test="basePro.dataType=1">
2,
</when>
<otherwise>
2,
</otherwise>
</choose>
1.3 case when 查询条件
length(depart_id) > 0
<choose>
<when test="secondDeptName != null and secondDeptName='区客'">
AND (depart_name2 = #{secondDeptName} or depart_id='2317')
</when>
<otherwise>
AND depart_name2 = #{secondDeptName}
</otherwise>
</choose>
2. 时间段查询数据
<!-- 注意： startTime 的数据类型，如果为LocalDateTime -->
<!--LocalDataTime 时间查询 -->
<if test="startTime != null and startTime !=''"> <!-- 错误-->
AND `customer_management`.`update_time` >= #{startTime}
</if>
<if test="endTime != null"><!-- 正确-->
AND `customer_management`.`update_time` <= #{endTime}
</if>
3. 拼接查询[Like、IN]
<!--Like 拼接 -->
<if test="templateName !=null and templateName !=''">
AND `name` like '%${templateName}%'
</if>
<!--IN 拼接 -->
<!-- 注： screenCodeList !='' 将 screenCodeList变为了字符串 会出现异常！-> screenCodeList !=null 即可 -->
<if test="screenCodeList !=null and screenCodeList.size > 0">
and db_index.`id` in
(
select
distinct db_index_screen_relation.index_id
from
db_index_screen_relation
where
screen_code in
<foreach collection="screenCodeList" open="(" close=")" separator="," item="code">
#{code}
</foreach>
)
</if>
4. 批量
4.1 批量新增
<insert id="insertAuthor" useGeneratedKeys="true"
keyProperty="id">
insert into Author (username, password, email, bio) values
<foreach item="item" collection="list" separator=",">
(#{item.username}, #{item.password}, #{item.email}, #{item.bio})
</foreach>
</insert>
4.2 批量更新
<!-- 注：需要支持多语句执行 &allowMultiQueries=true -->
<update id="updateBatch">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update
workorder_lock_info
set
solve_person=#{item.solvePerson},
solve_person_num=#{item.solvePersonNum},
task_id=#{item.taskId},
order_status=#{item.orderStatus},
start_time=now()
where
order_id=#{item.orderId}
</foreach>
</update>
5. 分页、新增操作
5.1 返回主键id
<!--从实体对象中获取 -->
<insert id="add" useGeneratedKeys="true" keyProperty="id"
parameterType="com.xinchao.datacollection.dao.entity.Template">
5.2 MyBatisPlus 分页
// 简单的就用mybatis-puls查询
// 复杂的就是用 xml方式
Page page = new Page<>(pkInfoParam.getCurrPage(), pkInfoParam.getPageSize());
QueryWrapper<PkInfo> wrapper = new QueryWrapper();
if (null != pkInfoParam.getPkStatus()) {
wrapper.eq("pk_status", pkInfoParam.getPkStatus());
}
if (StringUtils.isNotBlank(pkInfoParam.getPkRange())) {
wrapper.eq("pk_rang", pkInfoParam.getPkRange());
}
if (StringUtils.isNotBlank(pkInfoParam.getCreateBy())) {
wrapper.like("create_by", pkInfoParam.getCreateBy());
}
if (StringUtils.isNotBlank(pkInfoParam.getPkName())) {
wrapper.like("pk_name", pkInfoParam.getPkName());
}
wrapper.eq("is_deleted", false);
if (StringUtils.isNotBlank(pkInfoParam.getStartCreateTime())) {
wrapper.ge("create_time", pkInfoParam.getStartCreateTime());
}
if (StringUtils.isNotBlank(pkInfoParam.getEndCreateTime())) {
wrapper.lt("create_time", pkInfoParam.getEndCreateTime());
}
5.3 级联
<resultMap id="teacherMap" type="com.czd.mybatis02.bean.Teacher">
<id property="id" column="t_id"/>
<result property="name" column="t_name"/>
<collection property="studentList" javaType="ArrayList"
column="name" select="selectStu">
</collection>
</resultMap>
<select id="selectStu" parameterType="string" resultType="com.czd.mybatis02.bean.Student">
SELECT s.id ,s.name FROM stu s
WHERE s.name = #{name}
</select>
<select id="testFindByStuName" parameterType="string" resultMap="teacherMap">
SELECT t.id t_id,t.name t_name,s.name ,s.id
FROM teacher t, stu s
WHERE t.id in
(SELECT t2.id FROM teacher t2 , stu s2 WHERE s2.name = #{name} AND t2.id =s2.t_id)
AND t.id=s.t_id;
</select>
6. 特殊场景
6.1 地图经纬度范围问题
public class MapUtils {
private static final double EARTH_RADIUS = 6371.393;
private static double rad(double d) {
return d * Math.PI / 180.0;
}
/**
* 计算两个经纬度之间的距离
*
* @param lat1
* @param lng1
* @param lat2
* @param lng2
* @return
*/
public static double getDistance(double lat1, double lng1, double lat2, double lng2) {
double radLat1 = rad(lat1);
double radLat2 = rad(lat2);
double a = radLat1 - radLat2;
double b = rad(lng1) - rad(lng2);
double s = 2 * Math.asin(Math.sqrt(
Math.pow(Math.sin(a / 2), 2) + Math.cos(radLat1) * Math.cos(radLat2) * Math.pow(Math.sin(b / 2), 2)));
s = s * EARTH_RADIUS;
s = Math.round(s * 1000);
return s;
}
/**
* 百度地图 获取给定经纬度和半径距离的经纬度范围
*
* @param lat 纬度
* @param lon 经度
* @param radius 单位:m
* @return
*/
public static Coordinates getAround(double lon, double lat, Double radius) {
double degree = (24901 * 1609) / 360.0;
double radiusMile = radius;
double dpmLat = 1 / degree;
double radiusLat = dpmLat * radiusMile;
double minLat = lat - radiusLat;
double maxLat = lat + radiusLat;
double mpdLng = Math.abs(degree * Math.cos(lat * (Math.PI / 180)));
double dpmLng = 1 / mpdLng;
double radiusLng = dpmLng * radiusMile;
double minLng = lon - radiusLng;
double maxLng = lon + radiusLng;
Coordinates coordinates = new Coordinates();
coordinates.setMinLat(dealNum(minLat));
coordinates.setMinLng(dealNum(minLng));
coordinates.setMaxLat(dealNum(maxLat));
coordinates.setMaxLng(dealNum(maxLng));
return coordinates;
}
}
<!-- 核心配置 -->
<if test="minLng !=null and maxLng !=null and minLng > 0 ">
AND (`lng` between #{minLng} and #{maxLng})
</if>
<if test="minLat !=null and maxLat !=null and minLat > 0 ">
AND (`lat` between #{minLat} and #{maxLat})
</if>
<if test="lat>0 and lng>0 and minLng > 0 and minLat > 0">
AND (
6371.393 * 2 * ASIN(
SQRT(
POW(
SIN(( RADIANS(#{lat}) - RADIANS(lat))/2),2) +
COS(RADIANS(#{lat})) * COS(RADIANS(lat)) *
POW(SIN((RADIANS(#{lng}) - RADIANS(lng)) / 2 ),2)
)
)
) < #{radius}
</if>
参考
MyBatis中文档：https://mybatis.org/mybatis-3/zh/