mybatis应用大全

MyBatis是一个半自动化的数据库持久层框架。
hibernate完全可以通过对象关系模型实现对数据库的操作，拥有完整的JavaBean对象与数据库的映射结构来自动生成sql。而mybatis仅有基本的字段映射，对象数据以及对象实际关系仍然需要通过手写sql来实现和管理。

configuration.xml详解：

INSERT:
自动生成主键
mysql两种方式：

<insert id="insertStudent" parameterType="Student" useGeneratedKeys="true"
keyProperty="studId">

第二种方式

    <selectKey keyProperty="studId" order="AFTER" resultType="java.lang.Integer">
      SELECT LAST_INSERT_ID()
    </selectKey>

oracle通过SEQUENCE生成的方式：

<insert id="insertStudent" parameterType="Student">
<selectKey keyProperty="studId" resultType="int" order="BEFORE">
SELECT ELEARNING.STUD_ID_SEQ.NEXTVAL FROM DUAL
</selectKey>
INSERT INTO STUDENTS(STUD_ID,NAME,EMAIL, PHONE)
VALUES(#{studId},#{name},#{email},#{phone})
</insert>

这里特别注意：order属性，如果是mysql这样可以自动赋值主键的要设置成AFTER,先插入数据然后将获取的主键赋值给对象。而通过SEQUENCE方式生成的则需要先查询下一个主键值再进行插入，因此oracle要设置为BEFORE

SELECT为重点部分：
resultType是直接表示返回类型的，而resultMap则是对外部ResultMap的引用，但是resultType跟resultMap不能同时存在。

resultType使用的一个问题，当column与property一致的时候，比如：<result column="NAME" property="name" />参数可以赋值，如果不同，比如： <id column="STUD_ID"  property="studId" />值不能赋值，
这里在写sql的时候要添加别名，与property一致

resultType="map" 例子
        SqlSession sqlSession = MyBatisFactory.getSqlSessionFactory().openSession();
        StudentsDAO studentsDAO = sqlSession.getMapper(StudentsDAO.class);
        HashMap<String,Object> map = studentsDAO.selectResultMap("学生0");
        System.out.println("STUD_ID :"+map.get("studId"));
        System.out.println("NAME :"+map.get("NAME"));
        System.out.println("EMAIL :"+map.get("EMAIL"));
        System.out.println("PHONE :"+map.get("PHONE"));

返回List

XML文件
  <select id="selectResultMap" resultType="map">
    select STUD_ID as studId, NAME, EMAIL, PHONE, DOB, ADDR_ID, BIO, PIC
    from students
  </select>
DAO文件:
    List<HashMap<String,Object>> selectResultMap();
查询:
       SqlSession sqlSession = MyBatisFactory.getSqlSessionFactory().openSession();
        StudentsDAO studentsDAO = sqlSession.getMapper(StudentsDAO.class);
        List<HashMap<String,Object>> list = studentsDAO.selectResultMap();
        for (HashMap<String,Object> map:
              list) {
                System.out.println("===========================================");
                System.out.println("STUD_ID :"+map.get("studId"));
                System.out.println("NAME :"+map.get("NAME"));
                System.out.println("EMAIL :"+map.get("EMAIL"));
                System.out.println("PHONE :"+map.get("PHONE"));
        }

一对一映射：
使用extends

<resultMap id="BaseResultMap" type="com.practice.mybatis.entity.Students">
    <!--
    @mbg.generated
    -->
    <id column="STUD_ID" jdbcType="INTEGER" property="studId" />
    <result column="NAME" jdbcType="VARCHAR" property="name" />
    <result column="EMAIL" jdbcType="VARCHAR" property="email" />
    <result column="PHONE" jdbcType="VARCHAR" property="phone" />
    <result column="DOB" jdbcType="DATE" property="dob" />
    <result column="ADDR_ID" jdbcType="INTEGER" property="addrId" />
    <result column="BIO" jdbcType="LONGVARCHAR" property="bio" />
    <result column="PIC" jdbcType="LONGVARBINARY" property="pic" />
  </resultMap>
  <resultMap id="oneTOone" type="com.practice.mybatis.entity.Students" extends="BaseResultMap">
    <result property="addresses.addrId" column="addr_id" />
    <result property="addresses.street" column="street" />
  </resultMap>
  <!--  一对一-->
  <select id="selectOneToOne" resultMap="oneTOone">
     select STUD_ID as studId, NAME, EMAIL, PHONE, DOB, s.ADDR_ID, BIO, PIC,a.ADDR_ID ,a.street
    from students s left OUTER JOIN addresses a ON s.ADDR_ID=a.ADDR_ID
    WHERE
    s.NAME=#{name}
  </select>

或者association属性

<resultMap id="addresses" type="com.practice.mybatis.entity.Addresses">
    <!--
    @mbg.generated
    -->
    <id column="ADDR_ID" jdbcType="INTEGER" property="addrId" />
    <result column="STREET" jdbcType="VARCHAR" property="street" />
  </resultMap>
  <resultMap id="oneTOone" type="com.practice.mybatis.entity.Students" extends="BaseResultMap">
    <association property="addresses" resultMap="addresses"/>
  </resultMap>

嵌套查询：

 <resultMap id="addresses" type="com.practice.mybatis.entity.Addresses">
    <!--
    @mbg.generated
    -->
    <id column="ADDR_ID" jdbcType="INTEGER" property="addrId" />
    <result column="STREET" jdbcType="VARCHAR" property="street" />
  </resultMap>
  <select id="findAddresses" parameterType="java.lang.Integer" resultMap="addresses">
    <!--
    @mbg.generated
    -->
    select ADDR_ID, STREET, CITY, STATE, ZIP, COUNTRY
    from addresses
    where ADDR_ID = #{addrId,jdbcType=INTEGER}
  </select>
  <resultMap id="oneTOone" type="com.practice.mybatis.entity.Students" extends="BaseResultMap">
    <association property="addresses" column="ADDR_ID" select="findAddresses"/>
  </resultMap>

嵌套查询是分两步完成的。

一对多映射：

<resultMap id="address" type="com.practice.mybatis.entity.Addresses">
    <!--
    @mbg.generated
    -->
    <id column="ADDR_ID" jdbcType="INTEGER" property="addrId" />
    <result column="STREET" jdbcType="VARCHAR" property="street" />
    <result column="CITY" jdbcType="VARCHAR" property="city" />
    <result column="STATE" jdbcType="VARCHAR" property="state" />
    <result column="ZIP" jdbcType="VARCHAR" property="zip" />
    <result column="COUNTRY" jdbcType="VARCHAR" property="country" />
  </resultMap>
  <resultMap id="courses" type="com.practice.mybatis.entity.Courses">
    <!--
    @mbg.generated
    -->
    <id column="COURSE_ID" jdbcType="INTEGER" property="courseId" />
    <result column="course_name" jdbcType="VARCHAR" property="name" />
    <result column="DESCRIPTION" jdbcType="VARCHAR" property="description" />
    <result column="START" jdbcType="DATE" property="start" />
    <result column="END" jdbcType="DATE" property="end" />
    <result column="TUTOR_ID" jdbcType="INTEGER" property="tutorId" />
  </resultMap>
  <!-- 查询所有的导师信息-->
  <resultMap id="all" type="com.practice.mybatis.entity.Tutors" extends="BaseResultMap">
    <association property="addresses" column="ADDR_ID" resultMap="address"></association>
    <collection property="courses"  resultMap="courses" ></collection>
  </resultMap>

  <select id="findAll" resultMap="all">
      SELECT
      t.*,a.*,c.COURSE_ID,c.NAME AS course_name,c.DESCRIPTION,c.START,c.END,c.TUTOR_ID
      FROM tutors t
      LEFT OUTER JOIN addresses a
      ON
      t.ADDR_ID = a.ADDR_ID
      LEFT OUTER JOIN courses c
      ON
      t.TUTOR_ID = c.TUTOR_ID
  </select>

当列的名称重复的时需要取别名。

动态SQL：
if

<if test="author!=null and author.name!=null">

choose, when, otherwise 从多个选项中选择一个.

<choose>
<when test=”title != null”>
AND title like #{title}
</when>
<when test=”author != null and author.name != null”>
AND title like #{author.name}
</when>
<otherwise>
AND featured = 1
</otherwise>
</choose>

where

<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 title like #{author.name}
</if>
</where>

where 元素知道插入“where”如果它包含的标签中有内容返回的话。此外，如果返回的内容
以“AND” 或者 “OR”开头，它会把“AND” 或者 “OR”去掉。

trim
overrides 属性使用了管道分隔的文本列表来覆写，而且它的空白也不能忽略的。这样的结果
是移出了指定在overrides 属性里字符，而在开头插入prefix属性中指定的字符。

***prefix 前缀，在包裹的标签前添加 例如：set或者where
suffix 后缀
prefixOverrides 前缀重写 例如： AND |OR （注意空格）
suffixOverrides 后缀重写 例如： ，
prefixOverrides 与suffixOverrides 都是去除最后一个要复写的内容，因此语句里面的需要去除的内容都要写。

---

< trim prefix ="WHERE" prefixOverrides="AND |OR ">
<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 title like #{author.name}
</if>
</ trim >

set用在动态update语句

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}

<trim prefix="SET" suffixOverrides=",">
…
</trim>
注意这种情况，我们剔除了一个后缀， 同时追加了一个前缀。

Foreach 元素，通常用在In语句中

SELECT *
FROM POST P
WHERE ID in
<foreach item="item" index="index" collection="list"
open="(" separator="," close=")">
#{item}
</foreach>

返回自定义类型：
返回map类型，将列作为key,值作为value返回：

  XML
  <select id="selectResultMap" resultType="map">
    select STUD_ID as studId, NAME, EMAIL, PHONE, DOB, ADDR_ID, BIO, PIC
    from students
  </select>
DAO
    List<HashMap<String,Object>> selectResultMap();
调用
List<HashMap<String,Object>> list = studentsDAO.selectResultMap();    

或者

XML
  <select id="selectMap" resultMap="BaseResultMap">
    select STUD_ID as studId, NAME, EMAIL, PHONE, DOB, ADDR_ID, BIO, PIC
    from students
  </select>
DAO
    Map<String,Students> selectMap();
调用
Map<String,Students> ss = sqlSession.selectMap("com.practice.mybatis.dao.StudentsDAO.selectMap","name");