动态SQL 

 1     <select id="queryMessageList" resultMap="messageResult" parameterMap="messageParameter">
 2         SELECT id,command,description,content FROM message
 3         <where>
 4             <if test="command != null and !!&quot;&quot;.equals(command.trim())">
 5                 AND command = #{command}
 6             </if>
 7             <if test="description != null and !&quot;&quot;.equals(description.trim())">
 8                 AND description LIKE CONCAT('%', #{description}, '%')
 9             </if>
10         </where>
11     </select>

 

 1     <!--删除-->
 2     <delete id="deleteMessage" parameterType="int">
 3         DELETE FROM message WHERE id = #{_parameter}
 4     </delete>
 5     <delete id="deleteMessages" parameterType="java.util.List">
 6         DELETE FROM message WHERE id IN (
 7           <foreach collection="list" item="item" separator=",">
 8               #{item}
 9           </foreach>
10         )
11     </delete>

 

include的使用

 1     <sql id="messageColumns">
 2         id,command,description,content
 3     </sql>
 4 
 5 
 6     <!--查询-->
 7     <select id="queryMessageList" resultMap="messageResult" parameterMap="messageParameter">
 8         SELECT <include refid="messageColumns"/> FROM message
 9         <where>
10             <if test="command != null and !!&quot;&quot;.equals(command.trim())">
11                 AND command = #{command}
12             </if>
13             <if test="description != null and !&quot;&quot;.equals(description.trim())">
14                 AND description LIKE CONCAT('%', #{description}, '%')
15             </if>
16         </where>
17     </select>

 

 

一对多关系

package com.imooc.bean;

import java.util.List;

/**
 * 与指令表对应的实体类
 */
public class Command {
    /**
     * 主键
     */
    private String id;
    /**
     * 指令名称
     */
    private String name;
    /**
     * 描述
     */
    private String description;
    /**
     * 一条指令对应的自动回复内容列表
     */
    private List<CommandContent> contentList;
    ......
}
 1 <?xml version="1.0" encoding="UTF-8"?>
 2 <!DOCTYPE mapper
 3     PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
 4     "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 5 
 6 <mapper namespace="Command">
 7   <resultMap type="com.imooc.bean.Command" id="Command">
 8     <id column="C_ID" jdbcType="INTEGER" property="id"/>
 9     <result column="NAME" jdbcType="VARCHAR" property="name"/>
10     <result column="DESCRIPTION" jdbcType="VARCHAR" property="description"/>
11     <collection property="contentList"  resultMap="CommandContent.Content"/>
12   </resultMap>
13   
14   <select id="queryCommandList" parameterType="com.imooc.bean.Command" resultMap="Command">
15     select a.ID C_ID,a.NAME,a.DESCRIPTION,b.ID,b.CONTENT,b.COMMAND_ID
16     from COMMAND a left join COMMAND_CONTENT b
17     on a.ID=b.COMMAND_ID
18     <where>
19         <if test="name != null and !&quot;&quot;.equals(name.trim())">
20             and a.NAME=#{name}
21         </if>
22         <if test="description != null and !&quot;&quot;.equals(description.trim())">
23             and a.DESCRIPTION like '%' #{description} '%'
24         </if>
25     </where>
26   </select>
27 </mapper>
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
    PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
    "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="CommandContent">
  <resultMap type="com.imooc.bean.CommandContent" id="Content">
    <id column="ID" jdbcType="INTEGER" property="id"/>
    <result column="CONTENT" jdbcType="VARCHAR" property="content"/>
    <result column="COMMAND_ID" jdbcType="VARCHAR" property="commandId"/>
  </resultMap>
</mapper>

 

 

其他常用标签

　　定义SQL语句　　　　　　insert、delete、update、select

　　配置java与结果集映射　　resultMap

　　控制SQL拼接　　　　　　foreach、if、choose

　　格式化输出　　　　　　　where、set、trim

　　配置关联关系　　　　　　collection、association

　　定义常量　　　　　　　　sql

　　引用常量　　　　　　　　include

posted @ 2015-07-08 00:40  csnmd  阅读(515)  评论(0)  编辑  收藏  举报