Mybatis最全指南



Mybatis

  1. Base_Column
1
2
3
<sql id="Base_Column">
ID,USER_NAME,PASSWORD,CREATE_TIME,UPDATE_TIME,HEAD_PORTRAIT,STATUS
</sql>
  1. Base_Where
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<sql id="Base_Where">
<where>
<if test="id != null and id != ''">
AND ID=#{id}
</if>
<if test="userName != null and userName != ''">
AND USER_NAME like concat('%',#{userName},'%')
</if>
<if test="password != null and password != ''">
AND PASSWORD=md5(#{password})
</if>
<if test="status != null">
AND STATUS=#{status}
</if>
</where>
</sql>
  1. Update_Column
1
2
3
4
5
6
7
8
9
<sql id="Update_Column">
<set>
<if test="userName != null and userName!= ''">USER_NAME =#{userName},</if>
<if test="password != null and password!= ''">PASSWORD=md5(#{password}),</if>
<if test="headPortrait != null and headPortrait != ''">HEAD_PORTRAIT=#{headPortrait},</if>
<if test="updateTime != null">UPDATE_TIME=#{updateTime},</if>
<if test="status != null">STATUS=#{status},</if>
</set>
</sql>

特殊查询

  1. 返回对象为HashMap
1
2
3
4
5
List<HashMap<String, Object>> getByIds(@Param("ids") List<Integer> ids);

<select id="getByIds" resultType="java.util.Map"
...
</select>

注意这种方式,相当于以 单个 Map做对象,然后对象属性名作为key,值作为value,适合查询多条记录

  1. HashMap 或者 JSONObject 作为入参,只要key值对应上就好,可以理解为同VO对象用法
1
2
3
4
5
6
7
8
9
10
Map<String, Object> param = new HashMap<>();
param.put("id", 1);
param.put("username", "lauy");

<select id="getByIds" resultType="java.util.Map"
SELECT
<include refid="Base_Column"/>
FROM user_info
WHERE id = #{id} AND username = #{username}
</select>

单表操作

  1. 新增
1
2
3
4
5
6
<insert id="insertOneUser" parameterType="cn.blue.项目工程名.bean.UserInfo"
useGeneratedKeys="true" keyColumn="id" keyProperty="id">
INSERT INTO
user_info(ID,USER_NAME,PASSWORD,CREATE_TIME)
VALUES(#{id},#{userName},md5(#{password}),now())
</insert>
  1. 查询: 根据封装进 userInfo 对象里的条件查询,结果可以是单个对象也可以是集合
1
2
3
4
5
6
7
<select id="findOneUser" parameterType="cn.blue.项目工程名.bean.UserInfo"
resultType="cn.blue.项目工程名.bean.UserInfo">
SELECT
<include refid="Base_Column"/>
FROM user_info
<include refid="Base_Where"/>
</select>
  1. 删除:
1
2
3
4
<delete id="deleteById" parameterType="string">
DELETE FROM user_info
<include refid="Base_Where"/>
</delete>
  1. 修改:
1
2
3
4
5
<update id="deleteById" parameterType="string">
UPDATE user_info SET STATUS = 1
WHERE
id=#{id}
</update>

两表查询

  1. resultMap:
1
2
3
4
5
6
7
8
9
10
11
<resultMap type="cn.blue.项目工程名.bean.UserInfo" id="userInfoMap">
<id column="ID" property="id"/>
<result column="USER_NAME" property="userName"/>
<result column="PASSWORD" property="password"/>
<result column="CREATE_TIME" property="createTime"/>
<result column="UPDATE_TIME" property="updateTime"/>
<result column="HEAD_PORTRAIT" property="headPortrait"/>
<result column="STATUS" property="status"/>
<collection column="ID" property="roles" ofType="cn.blue.项目工程名.bean.Role"
select="cn.blue.项目工程名.mapper.RoleMapper.findRoleById"/>
</resultMap>
  1. UserInfoMapper 中的查询语句:
1
2
3
4
5
6
7
<select id="findAll" parameterType="cn.blue.项目工程名.bean.UserInfo"
resultMap="userInfoMap">
SELECT
<include refid="Base_Column"/>
FROM user_info
<include refid="Base_Where"/>
</select>
  1. RoleMapper 表中的查询
1
2
3
4
5
6
7
<select id="findById" parameterType="int"
resultMap="roleMap">
select
<include refid="Base_Column"></include>
from role
where id=#{id}
</select>

利用中间表过渡的两表查询

  1. resultMap:
1
2
3
4
5
6
7
8
9
10
11
<resultMap type="cn.blue.项目工程名.bean.UserInfo" id="userInfoMap">
<id column="ID" property="id"/>
<result column="USER_NAME" property="userName"/>
<result column="PASSWORD" property="password"/>
<result column="CREATE_TIME" property="createTime"/>
<result column="UPDATE_TIME" property="updateTime"/>
<result column="HEAD_PORTRAIT" property="headPortrait"/>
<result column="STATUS" property="status"/>
<collection column="ID" property="roles" ofType="cn.blue.项目工程名.bean.Role"
select="cn.blue.项目工程名.mapper.RoleMapper.findRoleByUserId"/>
</resultMap>
  1. UserInfoMapper 中的查询语句:
1
2
3
4
5
6
7
<select id="findAll" parameterType="cn.blue.项目工程名.bean.UserInfo"
resultMap="userInfoMap">
SELECT
<include refid="Base_Column"/>
FROM user_info
<include refid="Base_Where"/>
</select>
  1. RoleMapper 中的查询语句
1
2
3
4
5
6
7
<select id="findRoleByUserId" parameterType="int" resultType="cn.blue.项目工程名.bean.Role">
SELECT
<include refid="Base_Column"/>
FROM role r, user_role ur
WHERE r.id=ur.role_id
AND ur.user_id=#{userId}
</select>

resultMap使用Map返回数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?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="cn.lauy.customer.business.customer.dao.AskedQuestionsCategoryDao">

<!-- TODO 不使用就删除-->
<resultMap id="questionsMap" type="com.lauy.customer.business.customer.dto.AskedQuestionsCategoryDTO">
<id column="ID" property="id"/>
<result column="remark" property="remark"/>
<!--【传多个参数值到下一个SQL查询】:对应 AskedQuestionsCategoryDTO 中数据:参数=列名 -->
<collection column="{questionId=ID,questionName=REMARK}" property="AskedQuestionsList" ofType="cn.lauy.customer.business.customer.model.AskedQuestions"
select="getListQuestionsInfo"/>
</resultMap>

<select id="getListCategoryAndQuestions" resultMap="questionsMap">
SELECT c.* FROM asked_questions_category c
LEFT JOIN asked_questions q
ON c.id = q.category_id
ORDER BY c.weight
</select>

<select id="getListQuestionsInfo" parameterType="java.util.HashMap"
resultType="cn.lauy.customer.business.customer.model.AskedQuestions">
SELECT q.* FROM asked_questions q
LEFT JOIN asked_questions_info i
ON i.question_id = #{id}
<if test="questionName != '' and questionName != null">
AND i.question_name LIKE CONCAT(#{questionName}, '%')
</if>
ORDER BY q.question_weight
</select>
</mapper>

遍历操作

  1. 根据 ids 来删除:
    注:数组 或者 集合都可,如下示例 ids 是个数组,故需要以此表达:String[] ids
1
2
3
4
5
6
7
/**
* 支持批量删除用户
* @param ids 多个用户id
* @return 受影响行数
* 数组 或者 集合都可
*/
int deleteByIds(@Param("ids")String... ids);
1
2
3
4
5
6
7
8
<delete id="deleteByIds" parameterType="string">
DELETE FROM user_info
WHERE
<foreach collection="ids" open="id in (" close=")"
separator="," item="id">
#{id}
</foreach>
</delete>
  1. 根据用户 id 来在中间表中新增与之对应的角色 ids:一对一或者一对多的关系
1
2
3
4
5
6
7
/**
* 修改中间表user_role的id
* @param userId 用户id
* @param roleIds 角色id
* @return 受影响行数
*/
int addRolesByUserId(@Param("userId") String userId, @Param("roleIds") String[] roleIds);
1
2
3
4
5
6
<insert id="addRolesByUserId">
INSERT INTO user_role
<foreach collection="roleIds" open="values" item="roleId" separator=",">
(#{userId},#{roleId})
</foreach>
</insert>

注解式查询

  • @Insert
  • @Update
  • @Delete
  • @Select
  • @Results,resultMap一组结果映射,指定了对某个特定结果列,映射到某个属性或字段的方式

示例1:

1
2
3
4
5
6
7
@Results(id = "userResult", value = {
@Result(property = "id", column = "uid", id = true),
@Result(property = "firstName", column = "first_name"),
@Result(property = "lastName", column = "last_name")
})
@Select("select * from users where id = #{id}")
User getUserById(Integer id);

示例2:

1
2
3
4
5
6
7
@Select("<script>" +
"SELECT id,username,age from user WHERE id in " +
"<foreach item = 'id' index = 'index' collection = 'ids' open='(' separator=',' close=')'>" +
"#{id}" +
"</foreach>"+
"</script>")
List<User> getByIds(@Param("ids") List<Integer> ids);

等同于xml中

1
2
3
4
5
6
7
<select id="getByIds" resultType="cn.blue.项目工程名.bean.Role">
SELECT id,username,age from user
WHERE id in
<foreach item = 'id' index = 'index' collection = 'ids' open='(' separator=',' close=')'>
#{id}
</foreach>
</select>

一步步放弃mybatis的xml

Java API

大于等于小于等于的写法

1
2
3
4
5
6
7
8
9
10
11
12
13
14
第一种写法(1):

原符号 < <= > >= & ' "
替换符号 &lt; &lt;= &gt; &gt;= &amp; &apos; &quot;
例如:sql如下:
create_date_time &gt;= #{startTime} and create_date_time &lt;= #{endTime}

第二种写法(2):
大于等于
<![CDATA[ >= ]]>
小于等于
<![CDATA[ <= ]]>
例如:sql如下:
create_date_time <![CDATA[ >= ]]> #{startTime} and create_date_time <![CDATA[ <= ]]> #{endTime}

参考-mybatis中大于等于小于等于的写法

常见错误

1. Caused by: com.alibaba.druid.sql.parser.EOFParserException: EOF

可能是mybatis没有遍历的时候没有添加非空判断, 即purchaseIdList.size() > 0

1
2
3
4
5
<if test="purchaseIdList != null and purchaseIdList.size() > 0"> and purchase_id in
<foreach item="purchaseId" collection="purchaseIdList" open="(" separator="," close=")" index="index">
#{purchaseId}
</foreach>
</if>

Mybatis Plus

官方文档

UpdateWrapper用法

记一次测试库事故,一个update直接导致全表更新,事后火葬场!!!

1
2
3
4
5
6
7
8
9
UpdateWrapper<User> wrapper = new UpdateWrapper<>();
wrapper.set("username", "lauy);
// wrapper.eq("id", user.getId());
userService.update(user, wrapper);

// 同
userService.update(user, new QueryWrapper<User>().lambda()
.eq(User::getId, user.getId())
.eq(User::getUsername, user.getUsername()));

可以看出我们使用wrapper构造器时候,你传入的user对象它最后只是决定修改哪几列(user对象有值的字段对应的column),而不是修改这唯一一条。所以我们不加eq条件,那么就是走的全表!!!
修改后的sql为:

1
UPDATE acl_user SET username=?, password=?, nick_name=?, salt=?, token=?, is_deleted=?, gmt_modified=? WHERE username = ? and id = ?

条件构造器

https://blog.csdn.net/canyanruxue/article/details/79955098

打赏
  • 版权声明: 本网站所有文章除特别声明外,均采用 Apache License 2.0 许可协议。转载请注明出处!
  1. © 2020-2021 Lauy    湘ICP备20003709号-1

请我喝杯咖啡吧~

支付宝
微信