Mybatis常用标签记录

基础标签

select 标签

<select id="selectById" parameterType="String" resultType="User">
    select * from Userwhere id=#{id}
</select>

insert 标签

<insert id="insert" parameterType="Object">
    insert into student
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="name != null"> NAME, </if>
    </trim>
    <trim prefix="values(" suffix=")" suffixOverrides=",">
        <if test="name != null"> #{name}, </if>
     </trim>
</insert>

delete 标签

<delete id="deleteByPrimaryKey" parameterType="Object">
    delete from student where id=#{id}
</delete>

update 标签

<update id="insert" parameterType="Object">
    update student set name = #{name}
    where id = #{id}
</update>

resultMap 标签

字段表名映射

<resultMap id="BaseResultMap" type="com.online.charge.platform.student.model.Student">
    <id property="id" column="id" />
    <result column="NAME" property="name" />
    <result column="HOBBY" property="hobby" />
    <result column="MAJOR" property="major" />
    <result column="BIRTHDAY" property="birthday" />
    <result column="AGE" property="age" />
</resultMap>

sql 标签

<sql id="Base_Column_List">
    ID,MAJOR,BIRTHDAY,AGE,NAME,HOBBY
</sql>

include 标签

<select id="selectAll" resultMap="BaseResultMap">
    SELECT
    <include refid="Base_Column_List" />
    FROM student
    <include refid="Example_Where_Clause" />
</select>

动态标签

if 标签

<if test="name != null and name != ''">
    and NAME = #{name}
</if>

foreach 标签

• collection：属性分别是 list、array、map三种，分别对应的List、数组、map 集合。
• item ：表示在迭代过程中每一个元素的别名
• index ：表示迭代的位置（下标）
• open ：前缀
• close ：后缀
• separator ：分隔符，表示迭代时每个元素之间以什么分隔

<select id="selectIn" resultMap="BaseResultMap">
    select name,hobby from student where id in
    <foreach item="item" index="index" collection="list" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

choose 标签

<select id="getStudentListChoose" parameterType="Student" resultMap="BaseResultMap">
    SELECT * from STUDENT WHERE 1=1
    <where>
        <choose>
            <when test="Name!=null and student!='' ">
                AND name LIKE CONCAT(CONCAT('%', #{student}),'%')
            </when>
            <when test="hobby!= null and hobby!= '' ">
                AND hobby = #{hobby}
            </when>
            <otherwise>
                AND AGE = 15
            </otherwise>
        </choose>
    </where>
</select>

where 标签

<select id="getStudentListWhere" parameterType="Object" resultMap="BaseResultMap">
    SELECT * from STUDENT
    <where>
        <if test="name!=null and name!='' ">
            NAME LIKE CONCAT(CONCAT('%', #{name}),'%')
        </if>
        <if test="hobby!= null and hobby!= '' ">
            AND hobby = #{hobby}
        </if>
    </where>
</select>

set 标签

<update id="updateStudent" parameterType="Object">
    UPDATE STUDENT
    <set>
        <if test="name!=null and name!='' ">
            NAME = #{name},
        </if>
        <if test="hobby!=null and hobby!='' ">
            MAJOR = #{major},
        </if>
        <if test="hobby!=null and hobby!='' ">
            HOBBY = #{hobby}
        </if>
    </set>
    WHERE ID = #{id};
</update>

trim 标签

参考博客：https://blog.csdn.net/qq_39623058/article/details/88779301

<select id="selectByNameOrHobby" resultMap="BaseResultMap">
	select * from student 
	<trim prefix="WHERE" prefixOverrides="AND | OR">
		<if test="name != null and name.length()>0"> 
			AND name=#{name}
		</if>
		<if test="hobby != null and hobby.length()>0">
			AND hobby=#{hobby}
		</if>
	</trim>
</select>

association 标签

方式一：一对一嵌套结果集映射

   <resultMap type="PersonResult" id="PersonMap">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <!-- 一对一关联 -->
        <association property="idCard" column="idcard_id" javaType="IdCard">
            <id column="cid" property="id" />
            <result column="number" property="number" />
            <result column="expired_time" property="expiredTime" />
        </association>
    </resultMap>
    <select id="selectById" parameterType="Integer"
        resultMap="PersonMap">
        select p.id id, p.name name,c.id cid,c.number
        number,c.expired_time expired_time from t_person p
        inner join t_idcard
        c on p.idcard_id=c.id and p.id=#{id}
    </select>

方式二：一对一或者一对多嵌套查询

   <resultMap type="PersonResult" id="PersonMap">
        <id property="id" column="id" />
        <result property="name" column="name" />

        <!-- 一对一或者一对多关联,设置延迟加载方式 -->
        <association property="idCard" column="idcard_id"
            javaType="IdCard"
            select="com.sunwii.mybatis.mapper.IdCardMapper.selectById" fetchType="lazy"/>
    </resultMap>
    <select id="selectById" parameterType="Integer"
        resultMap="PersonMap">
        select id, name, idcard_id from t_person p where p.id=#{id}
    </select>

collection 标签

方式一：嵌套查询

• select: 对应ml中的代码片段的id,
• column: 作为select语句的参数传入,如果只传一个参数id可以简写: column=“id”

<resultMap id="BaseResultMap" type="com.xxx.modules.xxx.entity.Question" >
		<id column="id" 		 property="id"      jdbcType="VARCHAR" />
		<result column="content" property="content" jdbcType="VARCHAR" />
		<result column="type"    property="type"    jdbcType="VARCHAR" />
		<result column="sort"    property="sort"    jdbcType="INTEGER" />
		<collection property="options" javaType="java.util.ArrayList" ofType="User"
					select="selectList" column="{qid=id,sort=sort}" />
	</resultMap>

	<!-- 查询列表 -->
	<select id="selectList" resultMap="BaseResultMap">
        SELECT
        	pq.id, pq.content, pq.type, pq.sort
        FROM
        	question AS pq
    </select>

方式二：嵌套结果

<mapper namespace="com.xxx.modules.xxx.mapper.QuestionMapper">

<resultMap id="BaseResultMap" type="com.xxx.modules.xxx.entity.Question" >
		<id column="id" 		 property="id"      jdbcType="VARCHAR" />
		<result column="content" property="content" jdbcType="VARCHAR" />
		<result column="type"    property="type"    jdbcType="VARCHAR" />
		<result column="sort"    property="sort"    jdbcType="INTEGER" />
		<collection property="options" javaType="java.util.ArrayList" ofType="com.xxx.modules.data.entity.QuestionOption">
			<id column="o_id" property="id" jdbcType="VARCHAR" />
			<result column="o_content" property="content" jdbcType="VARCHAR" />
			<result column="o_sort" property="sort" jdbcType="INTEGER" />
		</collection>
		<!-- 列的别名 o_id,o_content,o_sort , 起别名是因为主子表都有这几个字段
			 这里要写 ofType, javaType还是可以不写 -->
	</resultMap>

	<!-- 查询列表 -->
	<select id="selectList" resultMap="BaseResultMap">
        SELECT
        	pq.id, pq.content, pq.type, pq.sort
        	,pqo.id AS oid ,pqo.content AS ocontent ,pqo.sort AS osort <!-- 联查子表字段,起别名 -->
        FROM
        	question AS pq
        	LEFT JOIN question_option pqo ON pq.id = pqo.qid <!-- 联查子表 -->
        <where>  
        </where>
    </select>