Mybatis小笔记

本文旨在记录我在 idea 中使用 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. 新增
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>

遍历操作

  1. 根据 ids 来删除:
    注:前面 ids 是个数组,故需要以此表达:String[] ids
1
2
3
4
5
6
/**
* 支持批量删除用户
* @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
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);

一步步放弃mybatis的xml

Java API

常见错误

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

请我喝杯咖啡吧~

支付宝
微信