MyBatis 常用SQL汇总

MyBatis 常用SQL汇总

resultMap映射

<!-- resultMap映射 -->
<resultMap id="BaseResultMap" type="cn.scpro.model.UserInfo">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="name" property="name" jdbcType="VARCHAR"/>
        <result column="doccode" property="doccode" jdbcType="VARCHAR"/>
        <result column="telno" property="telno" jdbcType="VARCHAR"/>
</resultMap>

实体类查询条件

<!-- 实体类查询条件 -->
    <sql id="conditionExample">
        <where>
            <if test="example.id != null">
                AND id = #{example.id}
            </if>
            <if test="example.name != null">
                AND name = #{example.name}
            </if>
            <if test="example.doccode != null">
                AND doccode = #{example.doccode}
            </if>
            <if test="example.telno != null">
                AND telno = #{example.telno}
            </if>
        </where>z`
    </sql>

查询字段名

<!-- 查询字段名 -->
<sql id="Base_Column_List">
	id, 
	name,
	doccode,
	telno	
</sql>

主键查询

<!-- 主键查询 -->
<select id="selectByPrimaryKey" resultMap="BaseResultMap"
            parameterType="java.lang.Integer">
        select
        <include refid="Base_Column_List"/>
        from user_info
        where id = #{id}
    </select>

插入数据 返回主键1

<!-- 插入数据 返回主键1-->
<insert id="insertSelective" parameterType="cn.scpro.model.UserInfo"
            useGeneratedKeys="true" keyProperty="id">
        insert into user_info
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id != null">
                id,
            </if>
            <if test="name != null">
                name,
            </if>
            <if test="doccode != null">
                doccode,
            </if>
            <if test="telno != null">
                telno,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id != null">
                #{id},
            </if>
            <if test="name != null">
                #{name,jdbcType=VARCHAR},
            </if>
            <if test="doccode != null">
                #{doccode},
            </if>
            <if test="telno != null">
                #{telno,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>

插入数据返回主键2

<!--插入数据返回主键2 -->
 <insert id="insertTaskHistory" parameterType="cn.scpro.model.UserInfo" >
    insert into user_info
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="userinfo.name != null" >
        name,
      </if>
      <if test="userinfo.doccode != null" >
        doccode,
      </if>
      <if test="userinfo.telno != null" >
        telno,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="userinfo.name != null" >
        #{userinfo.name,jdbcType=VARCHAR},
      </if>
      <if test="userinfo.doccode != null" >
        #{userinfo.doccode,jdbcType=VARCHAR},
      </if>
      <if test="userinfo.telno != null" >
        #{userinfo.telno,jdbcType=VARCHAR},
      </if>
    </trim>
    <selectKey resultType="java.lang.Integer" order="AFTER" keyProperty="userinfo.id">
            SELECT id FROM user_info ORDER BY id DESC LIMIT 0,1 
    </selectKey>
  </insert>

更新数据

<!-- 更新数据 -->
<update id="updateByPrimaryKeySelective" parameterType="cn.scpro.model.UserInfo">
        update user_info
        <set>
            <if test="name != null">
                name = #{userinfo.name,jdbcType=VARCHAR},
            </if>
            <if test="doccode != null">
                doccode = #{userinfo.doccode},
            </if>
            <if test="telno != null">
                telno = #{userinfo.telno,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{userinfo.id}
    </update>

插入list

<!-- 插入list -->
<insert id="insertNotifylist" parameterType="java.util.List" useGeneratedKeys="true">
insert into user_info
values
    <foreach collection="userlist" item="item" index="index" separator=",">
    (#{item.name,jdbcType=VARCHAR},#{item.doccode,jdbcType=VARCHAR},
	#{item.telno,jdbcType=VARCHAR})
    </foreach>
  </insert>

更新list

<!-- 更新list-->
 <update id="UpdateById">
        update user_info  set name= null
        where id in
        <foreach collection="list" item="id" separator="," open="("
                 close=")">
            #{id}
        </foreach>
    </update>

删除list

<!-- 删除list -->
<delete id="DeleteById" parameterType="java.util.List">
        delete from user_info where id in
        <foreach collection="list" item="id" separator="," open="("
                 close=")">
            #{id}
        </foreach>
 </delete>

常用类型

<resultMap id="BaseResultMap" type="com.test.model.User">
		<id column="id" property="id" jdbcType="VARCHAR" /> 字符串
		<result column="newtime" property="newTime" jdbcType="TIMESTAMP" />	时间
		<result column="userid" property="userId" jdbcType="INTEGER" /> 整数
		<result column="money" property="money" jdbcType="DECIMAL" /> 小数
</resultMap>

继承map，可查多项个实体

<!-- 继承map，可查多项个实体 -->
<resultMap id="BaseResultMapForMore" type="com.test.model.User"
		   extends="BaseResultMap">
	<collection property="score"
				ofType="com.test.model.Score" column="userid"
				javaType="com.test.model.Score"
				select="com.test.mapper.ScoreMapper.findByuserid">
	</collection>
	<collection property="teacherlist"
				ofType="com.test.model.Teacher" column="teacherid"
				javaType="com.test.model.Teacher"
				select="com.test.model.TeacherMapper.findById">
	</collection>
	<!--<collection property="homeinfo"-->
				<!--ofType="com.test.model.Home" column="homeid"-->
				<!--javaType="com.test.model.Home"-->
				<!--select="com.test.mapper.HomeMapper.findById">-->
	<!--</collection>-->
</resultMap>