SQL映射有几个顶级元素(按照定义需要的顺序排列):
cache – 给定命名空间的缓存配置。cache-ref – 其他命名空间缓存配置的引用。resultMap – 是最复杂也是最强大的元素，用来描述如何从数据库结果集中来加载对象。sql – 可被其他语句引用的可重用语句块。insert – 映射插入语句update – 映射更新语句delete – 映射删除语句select – 映射查询语句简单的查询语句,此处#{id} , 是用来标记需要替换的参数:
<select id="selectPerson" parameterType="int" resultType="hashmap"> SELECT * FROM PERSON WHERE ID = #{id} </select>以下是等效语句:
// Similar JDBC code, NOT MyBatis… String selectPerson = "SELECT * FROM PERSON WHERE ID=?"; PreparedStatement ps = conn.prepareStatement(selectPerson); ps.setInt(1,id);下面是select属性详解:
<select id="selectPerson" //名称空间中的唯一标示符,可用于引用这个语句 parameterType="int" // resultType="hashmap" resultMap="personResultMap" flushCache="false" useCache="true" timeout="10000" fetchSize="256" statementType="PREPARED" resultSetType="FORWARD_ONLY">insert / update / delete 示例:
<insert id="insertAuthor"> insert into Author (id,username,password,email,bio) values (#{id},#{username},#{password},#{email},#{bio}) </insert> <update id="updateAuthor"> update Author set username = #{username}, password = #{password}, email = #{email}, bio = #{bio} where id = #{id} </update> <delete id="deleteAuthor"> delete from Author where id = #{id} </delete>主键如果设置了自动生成策略 , 那么只需要设置userGeneratedKeys=”true”, 把keyProperty设置到指定的自增目标属性上即可:
<insert id="insertAuthor" useGeneratedKeys="true" keyProperty="id"> insert into Author (username,password,email,bio) values (#{username},#{password},#{email},#{bio}) </insert>如果数据库支持多行插入,可以传入一个对象数组,并返回生成的主键
<insert id="insertAuthor" useGeneratedKeys="true" keyProperty="id"> insert into Author (username, password, email, bio) values <foreach item="item" collection="list" separator=","> (#{item.username}, #{item.password}, #{item.email}, #{item.bio}) </foreach> </insert>如果不支持自动生成主键的话,也有相应的mybatis与之对应:
<insert id="insertAuthor"> <selectKey keyProperty="id" resultType="int" order="BEFORE"> select CAST(RANDOM()*1000000 as INTEGER) a from SYSIBM.SYSDUMMY1 </selectKey> insert into Author (id, username, password, email,bio, favourite_section) values (#{id}, #{username}, #{password}, #{email}, #{bio}, #{favouriteSection,jdbcType=VARCHAR}) </insert>可以用来定义可重用的代码段,可以使用include标签包含在其它的代码段中.
<sql id="userColumns"> ${alias}.id,${alias}.username,${alias}.password </sql>以上语句能够被其它的语句引用(refid)
<select id="selectUsers" resultType="map"> select <include refid="userColumns"><property name="alias" value="t1"/></include>, <include refid="userColumns"><property name="alias" value="t2"/></include> from some_table t1 cross join some_table t2 </select>property的值可以是动态传入的(此处先定义好两个可以动态传值的sql , 然后include时候 , 使用property属性赋值. )
<sql id="sometable"> ${prefix}Table </sql> <sql id="someinclude"> from <include refid="${include_target}"/> </sql> <select id="select" resultType="map"> select field1, field2, field3 <include refid="someinclude"> <property name="prefix" value="Some"/> <property name="include_target" value="sometable"/> </include> </select>传入简单参数id
<select id="selectUsers" resultType="User"> select id, username, password from users where id = #{id} </select>传入复杂参数User(会自动在user中查找id,username,password)
<insert id="insertUser" parameterType="User"> insert into users (id, username, password) values (#{id}, #{username}, #{password}) </insert>也可以手动指定参数的映射(注意如果null作为值传递 ,jdbcType是必须的)
#{property,javaType=int,jdbcType=NUMERIC}使用#{}语法的语句,其中的值会被MyBatis安全的预处理 . 但是使用${}语法来替换值的时候,MyBatis不会修改或转义字符串.
以下两种设置都是可以的, 前一种会使用hashmap,后一种
<select id="selectUsers" resultType="com.someapp.model.User"> select id, username, hashedPassword from some_table where id = #{id} </select> <!-- In mybatis-config.xml file --> <typeAlias type="com.someapp.model.User" alias="User"/> <!-- In SQL Mapping XML file --> <select id="selectUsers" resultType="User"> select id, username, hashedPassword from some_table where id = #{id} </select>如果列名不匹配的时候, 也可使用这种映射方式:
<resultMap id="userResultMap" type="User"> <id property="id" column="user_id" /> <result property="username" column="user_name"/> <result property="password" column="hashed_password"/> </resultMap>注意此处使用的是userResultType
<select id="selectUsers" resultMap="userResultMap"> select user_id, user_name, hashed_password from some_table where id = #{id} </select>如果是多表关联的映射 , 我们不能简单的通过单表映射来完成, 看以下示例:
<!-- Very Complex Statement --> <select id="selectBlogDetails" resultMap="detailedBlogResultMap"> select B.id as blog_id, B.title as blog_title, B.author_id as blog_author_id, A.id as author_id, A.username as author_username, A.password as author_password, A.email as author_email, A.bio as author_bio, A.favourite_section as author_favourite_section, P.id as post_id, P.blog_id as post_blog_id, P.author_id as post_author_id, P.created_on as post_created_on, P.section as post_section, P.subject as post_subject, P.draft as draft, P.body as post_body, C.id as comment_id, C.post_id as comment_post_id, C.name as comment_name, C.comment as comment_text, T.id as tag_id, T.name as tag_name from Blog B left outer join Author A on B.author_id = A.id left outer join Post P on B.id = P.blog_id left outer join Comment C on P.id = C.post_id left outer join Post_Tag PT on PT.post_id = P.id left outer join Tag T on PT.tag_id = T.id where B.id = #{id} </select>