Idea学习----（sql语句）

<resultMap id="studentResultMap" type="com.test.Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="del" column="del"/>
    </resultMap>

<sql id="columns">
        id, name,age,del
    </sql>

单一增删查改

<delete id="delete">
        delete from t_student
        where id = #{id}
    </delete>

<update id="update">
        UPDATE t_student
        <set>
            <if test="name != null"> name = #{name},</if>
            <if test="age != null">age= #{age},</if>
            <if test="del != null">del= #{del},</if>
        </set>
        where id = #{id}
    </update>

 <insert id="insert" useGeneratedKeys="true" keyProperty="id" keyColumn="id">
        INSERT INTO t_test_student (<include refid="columns"/>)
        VALUES (#{id}, #{name}, #{age}, #{del})
    </insert>

 <select id="get" resultMap="studentResultMap">
        SELECT
        <include refid="columns"/>
        FROM t_student
        WHERE id = #{id}
    </select>

批量增删查改

<insert id="batchInsert">
        INSERT INTO t_student (<include refid="columns"/>)
        VALUES
        <foreach item="item" collection="list" separator=",">
            (#{item.id}, #{item.name},#{item.age},#{item.del})
        </foreach>
    </insert>

   <select id="list" parameterType="com.test.Student" resultMap="studentResultMap">
        SELECT
        <include refid="columns"/>
        FROM t_student
        <where>条件
            <if test="age != null">AND  age = #{age}</if>
            <if test="name != null">AND name LIKE CONCAT('%',#{name},'%')</if>
        </where>
        ORDER BY id
    </select>

 <delete id="batchDelete" parameterType="java.util.List">
        delete from t_student
        where id  in
        <foreach item="item" collection="list" open="(" separator="," close=")">
            #{item.id}
        </foreach>
    </delete>

多表查询

 <sql id="columns2">
        st.id,st.name,st.age,sc.score as score,sc.subject as subject
    </sql>

    <select id="listAllStudentScore"  resultMap="studentscoreResultMap">
        SELECT
        <include refid="columns2"/>
        FROM t_student as st
        left join t_score as sc on sc.student_id=st.id
        where del = 0
        ORDER BY id
    </select>