一、数据库的删除操作
以下大多数内容均是参考https://blog.csdn.net/rocling/article/details/82845775,内容讲解很详细,可以多看几次。主要是对该链接中内容的学习,在里面加入了自己的理解,方便下次学习,加深印象。
首先说一下标签内部的属性,一些需要注意的小知识点
namespace="com.example.demo.mapper.DiaryMapper"
id:可以理解为对应Java文件中的方法名
parameterType:可以理解为该方法的参数类型,无参数可以不写
public Integer deleteDiary(@Param("id") Integer mId);
delete from diary where _id=#{id} 这里用@Param(“id”)标注了参数mId,因此如果要在sql中引用mId的值则可以直接用#{id}
如果参数中定义的是Diary类型的变量,不能使用@Param(“Diary”)来标注。
public Integer addDiary(Diary d); 这里插入数据的方法是Diary文章对象,默认情况下,参数是对象时在写sql语句时可以直接用对象的属性作为参数
insert into diary(title,content,pub_time,user_id) values(#{title},#{content},#{pubTime},#{userId}
增删改的结果可以返回一个int类型,一般操作成功会大于0,否则会返回0
1、根据id删除数据库里面的一条记录
<delete id="deleteCollectInfoById" parameterType="String">
delete from collect_info where collect_id = #{collectId}
</delete>
2、批量删除数据库里面的多条记录
<delete id="deleteCollectInfoByIds" parameterType="String">
delete from collect_info where collect_id in
<foreach item="collectId" collection="array" open="(" separator="," close=")">
#{collectId}
</foreach>
</delete>
二、数据库的更新操作
<update id="updateDiary" parameterType="com.example.demo.bean.Diary">
update diary set
title=#{title},content=#{content},pub_time=#{pubTime}
where _id=#{id}
</update>
也可以写成
<update id="updateCollectInfo" parameterType="CollectInfo"> update collect_info <trim prefix="SET" suffixOverrides=","> <if test="collectType != null and collectType != ''">collect_type = #{collectType},</if> <if test="objectId != null and objectId != ''">object_id = #{objectId},</if> <if test="userId != null and userId != ''">user_id = #{userId},</if> <if test="status != null ">status = #{status},</if> <if test="createTime != null ">create_time = #{createTime},</if> <if test="updateTime != null ">update_time = #{updateTime},</if> </trim> where collect_id = #{collectId} </update>
三、数据库的插入操作
<insert id="addDiary" useGeneratedKeys="true" keyProperty="id" parameterType="com.example.demo.bean.Diary"> insert into diary(title,content,pub_time,user_id)
values(#{title},#{content},#{pubTime},#{userId})
</insert>
也可以写成这样
<insert id="insertCollectInfo" parameterType="CollectInfo"> insert into collect_info <trim prefix="(" suffix=")" suffixOverrides=","> <if test="collectId != null and collectId != ''">collect_id,</if> <if test="collectType != null and collectType != ''">collect_type,</if> <if test="objectId != null and objectId != ''">object_id,</if> <if test="userId != null and userId != ''">user_id,</if> <if test="status != null ">status,</if> <if test="createTime != null ">create_time,</if> <if test="updateTime != null ">update_time,</if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="collectId != null and collectId != ''">#{collectId},</if> <if test="collectType != null and collectType != ''">#{collectType},</if> <if test="objectId != null and objectId != ''">#{objectId},</if> <if test="userId != null and userId != ''">#{userId},</if> <if test="status != null ">#{status},</if> <if test="createTime != null ">#{createTime},</if> <if test="updateTime != null ">#{updateTime},</if> </trim> </insert>
四、数据库的查询操作
1、查询一个数据库表的全部信息
<select id="selectHotDocumentInfoList" resultMap="DocumentInfoResult"> select * from document_info </select>
2、数据库的关联查询
如果现在需要对三个表user(作者表)、diary(文章表)、tags(标签表)进行关联查询,需求是:一篇文章只能由一个作者编写,而一篇文章可能对应很多个标签,现在需要查询文章信息。
<resultMap type="com.example.demo.bean.Diary" id="DiaryMap">
<id property="id" column="_id" />
<result property="title" column="title" />
<result property="content" column="content" />
<result property="pubTime" column="pub_time" javaType="java.sql.Date" />
<association property="user" column="user_id" javaType="com.example.demo.bean.User">
<id property="id" column="uId" />
<result property="name" column="name" />
<result property="sex" column="sex" />
<result property="sign" column="sign" />
<result property="photo" column="photo" />
<result property="age" column="age" />
</association>
<collection property="tags" ofType="com.example.demo.bean.Tags" column="_id" select="com.example.demo.mapper.TagMapper.getTagsByDiaryId">
</collection>
</resultMap>
第二部分代码
<resultMap type="com.example.demo.bean.Diary" id="DiaryMap2">
<id property="id" column="_id" />
<result property="title" column="title" />
<result property="content" column="content" />
<result property="pubTime" column="pub_time" javaType="java.sql.Date" />
<result property="userId" column="user_id" />
<association property="user" column="user_id" javaType="com.example.demo.bean.User" select="com.example.demo.mapper.UserMapper.getUserById">
</association>
<collection property="tags" ofType="com.example.demo.bean.Tags" column="_id" select="com.example.demo.mapper.TagMapper.getTagsByDiaryId">
</collection>
</resultMap>
3、MyBatis传入多个参数的问题,针对多个参数的情况,parameterType可以不用指定
就是说,在mapper.xml文件中对数据进行查询操作时,查询的语句有很多种不同的写法,但是需要与mapper.Java中的方法进行一一对应。比如说可以直接使用#{参数下标}的方式访问,之际在方法参数中使用@Parm声明各个参数的别名,封装List,使用Map携带多个参数四种方法来编写代码。
具体的实现例子,参考https://blog.csdn.net/rocling/article/details/82845775最后面的例子。