映射器
- select – 映射查询语句。
- insert – 映射插入语句。
- update – 映射更新语句。
- delete – 映射删除语句。
- cache – 该命名空间的缓存配置。
- cache-ref – 引用其它命名空间的缓存配置。
- resultMap – 描述如何从数据库结果集中加载对象,是最复杂也是最强大的元素。
- sql – 可被其它语句引用的可重用语句块。
select
<!--这个方法名为 selectPerson,接受一个 int(或 Integer)类型的参数,并返回一个 HashMap 类型的对象,其中的键是列名,值便是结果行中的对应值。-->
<select id="selectPerson" parameterType="int" resultType="hashmap">
SELECT * FROM PERSON WHERE ID = #{id}
</select>
其中如果方法只要一个参数且基本类型和String可以不用写parameterType,但是方法要多个参数时接口要使用@Parm(“参数”),例如
List<Student> findById(@Param("id") Integer id);
<select
id="selectPerson"
parameterType="int"
parameterMap="deprecated"
resultType="hashmap"
resultMap="personResultMap"
flushCache="false"
useCache="true"
timeout="10"
fetchSize="256"
statementType="PREPARED"
resultSetType="FORWARD_ONLY">
insert, update 和 delete
<!--插入成功后,数据库自动生成的主键会赋值给id->
<insert id="insertAuthor" useGeneratedKeys="true"
keyProperty="id">
insert into Author (id,username,password,email,bio)
values (null,#{username},#{password},#{email},#{bio})
</insert>
结果映射
数据库表的列名和javabean字段名不一致时:
<select id="selectUsers" resultMap="userResultMap">
select user_id, user_name, hashed_password
from some_table
where id = #{id}
</select>
<!--javabean中的字段与数据库表的列名相映射-->
<resultMap id="userResultMap" type="User">
<id property="id" column="user_id" />
<result property="username" column="user_name"/>
<result property="password" column="hashed_password"/>
</resultMap>
动态sql
-
if
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <if test="title != null"> AND title like #{title} </if> <if test="author != null and author.name != null"> AND author_name like #{author.name} </if> </select>
-
choose、when、otherwise
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG WHERE state = ‘ACTIVE’ <choose> <when test="title != null"> AND title like #{title} </when> <when test="author != null and author.name != null"> AND author_name like #{author.name} </when> <otherwise> AND featured = 1 </otherwise> </choose> </select>
-
trim、where、set
<select id="findActiveBlogLike" resultType="Blog"> SELECT * FROM BLOG <where> <if test="state != null"> state = #{state} </if> <if test="title != null"> AND title like #{title} </if> <if test="author != null and author.name != null"> AND author_name like #{author.name} </if> </where> </select>
<update id="updateAuthorIfNecessary"> update Author <set> <if test="username != null">username=#{username},</if> <if test="password != null">password=#{password},</if> <if test="email != null">email=#{email},</if> <if test="bio != null">bio=#{bio}</if> </set> where id=#{id} </update>
-
foreach
<!--collection:要遍历的集合 item:集合中的元素 index:索引 open:遍历开始 separator:分隔符 close:遍历结束 --> <select id="selectPostIn" resultType="domain.blog.Post"> SELECT * FROM POST P WHERE ID in <foreach item="item" index="index" collection="list" open="(" separator="," close=")"> #{item} </foreach> </select>