基本原理

將sql語句寫入到xml文件中，為這個(gè)xml文件提供統(tǒng)一的接口來操作數(shù)據(jù)庫

引入整合

• 引入兩個(gè)驅(qū)動(dòng)包

  
  
  Paste_Image.png
• 在bean.xml中注冊(cè)sqlSessionFactory(相當(dāng)于jdbc的connection)

<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
   <property name="dataSource" ref="dataSource"></property>
   <property name="typeAliasesPackage" value="com.gavin.exam.model"></property>
   <property name="mapperLocations" value="classpath*:com/gavin/exam/model/*Mapper.xml" />  
</bean>

其中typeAliasesPackage 為類型別名包目錄
mapperLocations為sql語句映射xml文件目錄

• 注冊(cè)sqlSessionTemplate

<bean id="sqlSessionTemplate" class="org.mybatis.spring.SqlSessionTemplate">
    <constructor-arg index="0" ref="sqlSessionFactory"></constructor-arg>
</bean>

• 注冊(cè)dao父類bean和實(shí)現(xiàn)bean

<bean id="baseDao" abstract="true">
    <property name="sqlSessionTemplate" ref="sqlSessionTemplate"></property>
</bean>

<bean id="userDao" class="com.gavin.exam.dao.mybatis.UserDaoImpl" parent="baseDao"/>

• model包下結(jié)構(gòu)如下

Paste_Image.png

• 編寫Mapper.xml映射文件

<?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="com.gavin.exam.model.User">
  <!--type 沒有寫全類名的原因是在bean.xml文件下定義了typeAliasesPackage-->
    <resultMap type="User" id="userMap">
        <!--column 為數(shù)據(jù)庫中的類表 property 為model中字段-->
        <id column="id" property="id"/>
        <result column="user_name" property="userName"/>
    </resultMap>
    
    <select id="getUserByName" parameterType="String" resultMap="userMap">
        <![CDATA[
            SELECT * FROM user WHERE user_name = LOWER(#{userName})
        ]]>
    </select>
</mapper>

其中select 中定義增刪改查的語句
屬性resultMap定義了返回map

實(shí)現(xiàn)

• DAO要extendsSqlSessionDaoSupport

public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao{

    private static final String CLASS_NAME = User.class.getName();
    private static final String SQL_ID_USER_GET_USER_BY_NAME = ".getUserByName";

    @Override
    public User getUserByName(String userName) {
        return getSqlSession().selectOne(CLASS_NAME + SQL_ID_USER_GET_USER_BY_NAME, userName);
    }
}

其中CLASS_NAME對(duì)應(yīng)mapper中的namespace
SQL_ID_USER_GET_USER_BY_NAME對(duì)應(yīng)<select id="getUserByName"中的id

各類節(jié)點(diǎn)

insert

map方法定義

<insert id="createQuestion" useGeneratedKeys="true" keyProperty="id" parameterType="Question" >
    <![CDATA[
        Insert INTO question(display_id, description, created_time, updated_time)
            VALUES(#{displayId}, #{description}, NOW(), NOW())
    ]]>
</insert>

自增長(zhǎng)的id會(huì)自動(dòng)放入傳入的對(duì)象中
DAO中實(shí)現(xiàn)

    @Override
    public void createQuestion(Question question) {
        getSqlSession().insert(QUESTION_CLASS_NAME+SQL_ID_CREATE_QUESTION, question);
    }

此時(shí)book.getId()就能獲得自增長(zhǎng)id

delete

map方法定義

 <delete id="delete" parameterType="int">
      <![CDATA[
             DELETE FROM question WHERE id = #{id}
         ]]>
 </delete>

update

map方法定義

<update id="updateQuestion" parameterType="Question">
    <![CDATA[
        UPDATE question
        SET
            description = #{description},
            updated_time = NOW()
        WHERE
            id = #{id}
    ]]>
</update>

動(dòng)態(tài)sql

動(dòng)態(tài)sql配置語句

• if
• where
• trim
• set
• choose(when, otherwise)
• foreach
• sql/include

foreach的使用

Paste_Image.png

如圖就會(huì)循環(huán)插入

實(shí)例：根據(jù)某個(gè)狀態(tài)來增加動(dòng)態(tài)增加查詢條件

    <select id="getQuestionCount" resultType="int" parameterType="String">
     <![CDATA[
            SELECT count(*) AS count FROM question WHERE 1=1
        ]]>
        <include refid=""statusSQL""/>
    </select>

動(dòng)態(tài)條件定義

    <sql id=""statusSQL"">
        <if test="status == 'deleted'">
            <![CDATA[
                AND is_deleted = 1
            ]]>
        </if>
        <if test="status == 'undeleted'">
            <![CDATA[
                AND is_deleted = 0
            ]]>
        </if>
    </sql>

其中sql節(jié)點(diǎn)用來定義公用的sql語句
同樣這段代碼可以直接寫進(jìn)getQuestionCount中

    <select id="getQuestionCount" resultType="int" parameterType="String">
     <![CDATA[
            SELECT count(*) AS count FROM question WHERE 1=1
        ]]>
                <if test="status == 'deleted'">
            <![CDATA[
                AND is_deleted = 1
            ]]>
        </if>
        <if test="status == 'undeleted'">
            <![CDATA[
                AND is_deleted = 0
            ]]>
        </if>
    </select>

使用總結(jié)

如果是在function中傳兩個(gè)以上的參數(shù)熙掺，那么可以將參數(shù)拼接成一個(gè)map傳到sql節(jié)點(diǎn)中
xml中接收參數(shù)類型為parameterType="map"
其中#會(huì)根據(jù)你參數(shù)的類型做處理
而$不會(huì)贮缅，只會(huì)將你的值替換他膳，所以當(dāng)寫入為字符串時(shí)記得加上單引號(hào)逾冬。所以盡量使用# 當(dāng)使用like時(shí)可以使用$