/**
  * 根據(jù)條件查詢用戶集合
  */
 List<User> selectUsers(@Param("cond")Map<String, Object> map);

<!-- 返回的是List，resultType給定的值是List里面的實體類而不是list，mybatis會自動把結(jié)果變成List -->
 <select id="selectUsers" parameterType="map" resultType="con.it.bean.User">
  select id, username, password, sex, birthday, address from user u
  <where>
   <trim suffixOverrides=",">
    <if test="cond.username != null and cond.username != ''">
     u.username = #{cond.username},
    </if>
    <if test="cond.sex != null">
     and u.sex = #{cond.sex},
    </if>
     <if test="cond.beginTime != null">
     <![CDATA[ and DATE_FORMAT(u.birthday, '%Y-%m-%d %H:%T:%s') >= DATE_FORMAT(#{beginTime}, '%Y-%m-%d %H:%T:%s')， ]]>
    </if>
    <if test="cond.endTime != null">
     <![CDATA[ and DATE_FORMAT(u.birthday, '%Y-%m-%d %H:%T:%s') <= DATE_FORMAT(#{endTime}, '%Y-%m-%d %H:%T:%s')， ]]>
    </if>
    <if test="cond.address != null and cond.address != ''">
     and u.addrerss like '%' || #{cond.address} || '%',
    </if>
   </trim>
  </where>
 </select>

 <insert id="insert" parameterType="com.it.bean.User">
 
  <!-- 使用序列插入oracle數(shù)據(jù)庫返回主鍵，MYSQL數(shù)據(jù)庫無需添加selectKey -->
  <selectKey resultType="long" order="BEFORE" keyProperty="id">
   SELECT user_seq.NEXTVAL as id from DUAL
  </selectKey>
  
  insert into User (ID, USERNAME, PASSWORD, SEX, ADRESS, CREATED_BY, CREADTED_DATE)
  values (#{id}, #{username}, #{password}, #{sex}, #{adress}, #{createdBy}, SYSDATE)
 </insert>

<sql id="selectParam">
 id, username, password, sex, birthday, address
</sql>

<select>
 select <include refid="selectParam"></include>
 from user
</select>

<if test="item != null and item != ''"></if>

<!-- 如果是Integer類型的需要把and后面去掉或是加上or-->
<if test="item != null"></if>
<if test="item != null and item != '' or item == 0"></if>

<typeAliases>
  <typeAlias type="com.it.bean.User" alias="User"/>
</typeAliases>

/**
 *問題表
 */
public class Question {
 
 private Long id; //問題id

 private String question; //問題
 
 private Integer questionType; //問題類型
 
 private List<QuestionAnswer> answerList; //問題選項集合
 
 //Getter和Setter省略
}

/**
 *問題選項表
 */
public class QuestionAnswer {
 
 private Long id; //選項id
 
 private Long questionId; //問題id

 private String answer; //選項
 
 //Getter和Setter省略
}

<!-- 具體可參考下面ResultMap -->
<collection property="answerList" javaType="java.util.List"
    ofType="com.it.bean.QuestionAnswer" column="id" 
    select="setlectQuestionAnswerByQuestionId"/>

<!-- 返回關聯(lián)查詢的問題 -->
<resultMap id="detail_result" type="com.it.bean.Question">
 <id column="id" property="id" />
 <result column="question" property="question" />
 <result column="question_type" property="questionType" />
 <collection property="answerList" javaType="java.util.List"
    ofType="com.it.bean.QuestionAnswer" column="id" 
    select="setlectQuestionAnswerByQuestionId"/>
</resultMap>

<!-- 查詢問題集 -->
<select id="selectQuestions" parameterType="map" resultMap="detail_result">
 select q.id, q.question, q.question_type 
 from question q 
 <where>
  <if test="cond.id != null">
   q.id = #{cond.id}
  </if>
  <if test="cond.idList != null and cond.idList.size() != 0">
   q.id in 
   <foreach collection="cond.idList" item="id" open="(" separator="," close=")">
    #{id}
   </foreach>
  </if>
 </where>
</select>

<!-- 查詢對應問題的答案集 -->
<select id="setlectQuestionAnswerByQuestionId" parameterType="long" resultType="com.it.bean.QuestionAnswer">
 select a.id, a.answer from question_answer a where a.question_id = #{id}
</select>

<sql id="base_column">id, question_id, answer</sql>

<!-- oracle的批量插入 -->
<insert id="insertBatchOracle" parameterType="list">
 insert into question_answer ( <include refid="base_column" /> ) 
 select question_answer_seq.NEXTVAL, A.* from (
  <foreach collection="list" item="item" separator="union all">
   select #{item.questionId}, #{item.answer} from dual
  </foreach>
 ) A 
</insert>

<!-- Mysql的批量插入，主鍵自增 -->
<insert id="insertBatchMysql" parameterType="list">
 insert into question_answer ( <include refid="base_column" /> ) 
 values 
  <foreach collection="list" item="item" open="(" separator="union all" close=")">
   #{item.id}, #{item.questionId}, #{item.answer}
  </foreach>
</insert>

 <select id="selectUserList" parameterType="com.it.bean.User" resultType="com.it.bean.User">
  <!-- 引用Sql片段 -->
  select <include refid="selectParam"> from user u
  <where>
   <!--where 可以自動去掉條件中的第一個and-->
   <if test="id != null">
    and u.id = #{id}
   </if>
   <if test="name != null and name != ''">
    and u.name = #{name}
   </if>
  </where>
 </select>

 <update id="updateUser" parameterType="com.it.bean.user">
  update user
  <set>
   <if test="username != null and username != ''">
    username = #{username},
   </if>
   <if test="sex != null and sex == 0 or sex == 1">
    sex = #{sex},
   </if>
   <if test="birthday != null "> 
    birthday = #{birthday},
   </if > 
   <if test="address != null and address != ''">
    address = #{address},
   </if>
   <if test="lastModifiedBy != null and lastModifiedBy != ''">
    last_modified_by = #{lastModifiedBy},
    last_modified_date = SYSDATE,
   </if>
  </set>
  <where>
   id = #{id}
  </where>
 </update>

<update id="test" parameterType="com.it.bean.User">
 update user
  <!-- 開頭加上set，結(jié)尾去除最后一個逗號 -->
  <trim prefix="set" suffixOverrides=",">
   <if test="username!=null and username != ''">
    name= #{username},
   </if>

   <if test="password!=null and password != ''">
    password= #{password},
   </if>

  </trim>
  <where>
   id = #{id}
  </where>
 </update>

<select id="getUserList" resultType="com.it.bean.User" parameterType="com.it.bean.User"> 
 SELECT <include refid="resultParam"></include> FROM User u 
 <where> 
  <choose> 
   <when test="username !=null and username != ''"> 
    u.username LIKE CONCAT(CONCAT('%', #{username}),'%') 
   </when > 
   <when test="sex != null"> 
    AND u.sex = #{sex} 
   </when > 
   <when test="birthday != null "> 
    AND u.birthday = #{birthday} 
   </when > 
   <otherwise> 
   </otherwise> 
  </choose> 
 </where> 
</select>