<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>
|