mybatis的sql语句各种操作

1.新增：

必传字段直接插入，非必传字段用if判断

<insert id="xxx" keyProperty="id" useGeneratedKeys="true">
    insert into ss_xxx_xx
    <trim prefix="(" suffix=")" suffixOverrides=",">
        name, parent_id,level,type,create_time ,
        <if test="describe!=null">`describe`,</if>
        <if test="ancestors!=null">ancestors,</if>
    </trim>

    <trim prefix="values(" suffix=")" suffixOverrides=",">
        #{name},#{parentId},  #{level}, #{type},#{createTime},
        <if test="describe!=null">#{describe},</if>
        <if test="ancestors!=null">#{ancestors},</if>
    </trim>

</insert>

    1.2 MyBatis多参数批量插入数据返回主键id

   dao层：

Integer insertListUser( @Param("User")List<Users> User,@Param("password") String password);

xml:

    <insert id="insertListUser"   parameterType="com.ruoyi.activity.domain.Users" useGeneratedKeys="true"  keyProperty="User.id" keyColumn="id">
        insert into users
        (
           `name`,class_id,school_id,created_at,updated_at,sex,stuno,email , phone , avatar, password,man_years
            ,man_schools ,list_id ,  remember_token ,login_at , resetcode ,resetphone ,resettime,score,login_times
        )
        values
        <foreach collection="User" item="item" separator=",">
         (
            #{item.name }, #{item.classId},   #{item.schoolId}, #{item.createdAt},
            #{item.updatedAt}  ,#{item.sex},#{item.stuno},
            #{item.email},  #{item.phone}, #{item.avatar},
            #{password}, #{item.manYears}, #{item.manSchools},
             #{item.listId},    #{item.rememberToken}, #{item.loginAt},
              #{item.resetcode}, #{item.resetphone},
            #{item.resettime},  #{item.score},    #{item.loginTimes}
          )
        </foreach>


    </insert>

keyColumn对应数据库的主键，keyProperty对应类的主键

2.修改：

    <update id="updateXxx"  >
        update xx_xx_xx
        <trim prefix="set" suffixOverrides=",">
            <if test="name!=null"> name= #{name},</if>
            <if test="describe!=null"> describe= #{describe} ,</if>
            <if test="parentId!=null">parent_id= #{parentId}, </if>
        </trim>
        where id= #{id}
    </update>

3.查询：

  3.1 普通查询：

    <select id="xx" resultMap="HelpxxxMap">
           SELECT  id ,`name`,reate_time FROM xx_xx_xx;
    </select>

 3.2： 筛选条件查询 ，使用 <where>

<select id="query" resultMap="RdxxMap">
        select
          *
        <where>
    
     
            <if test="createdAt != null">
                and created_at = #{createdAt}
            </if>
            <if test="updatedAt != null">
                and updated_at = #{updatedAt}
            </if>
        </where>
        order by created_at desc
    </select>

3.3 嵌套查询：【一层嵌套】

我目前喜欢一次查询，即一次就将所有的都查询出来：

类结构：

public class TeachTaskDetaiListDo implements Serializable {


 
    private Integer Id;

    private String CreateTime;

    List<TeachWxxx> xxxList;

    List<WordTyyy> yyyList;

}

sql语句：

不写了，就是多次使用left join

主要看resultType

    <resultMap type="com.lezhi.domain.entity.TeachTaskDetaiListDo" id="taskMaps">
        <result property="wordId" column="word_id" jdbcType="INTEGER"/>
        <result property="wordName" column="word_name" jdbcType="VARCHAR"/>

        <collection property="xxxList" ofType="com.lezhi.domain.entity.TeachWxxx" javaType="java.util.ArrayList" >
            <id  property="id" column="point_id" jdbcType="INTEGER" />
            <result property="enclosure" column="point_enclosure" jdbcType="VARCHAR"/>
     jdbcType="VARCHAR"/>
        </collection>

        <collection property="yyyList"
ofType="com.lezhi.domain.entity.WordTyyy" javaType="java.util.ArrayList" >
            <id  property="id" column="tips_id" jdbcType="INTEGER" />
            <result property="image" column="tip_image" jdbcType="VARCHAR"/>
         jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

    <select id="queryTaskDetailByTaskId" resultMap="taskMaps">
        SELECT
            wd.id AS word_id,
            wd.NAME AS word_name,
            wd.image_show,
            wd.enclosure AS word_enclosure,
            wd.image_summary,
            wd.video as word_video,
            wd.create_time AS word_create_time,
            wp.id AS point_id,
            wp.enclosure AS point_enclosure,
            wp.content AS point_content,
            wp.type AS point_type,
            wp.`index` AS point_index,
            wp.is_show AS point_is_show,
            wp.create_time AS point_create_time,
            wtip.id AS tips_id,
            wtip.image AS tip_image,
            wtip.content AS tip_content,
            wtip.create_time AS tip_creat_time,
            wtip.`index` AS tips_index
        FROM
            xx_word_2_task AS wt
            INNER JOIN xx_word_point AS wp ON wt.word_id = wp.word_id
            INNER JOIN xx_word_tips AS wtip ON wtip.word_id = wt.word_id
            INNER JOIN xx_word AS wd ON wt.word_id = wd.id
        WHERE
            task_id = #{taskId}
    </select>

4.删除： 批量删除

  Dao 处：

public int deleteByList(@Param("list") List<RdHelpCenterVo> list);

    <delete id="deleteByList" >
          delete from xxxx where id in
        <foreach collection="list" item="item" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </delete>