MyBatis

• mybatis-3.4.6
• jdk1.8+

一、mybatis入門

1.依賴jar包

mybatis基礎(chǔ)包

• pom.xml引入依賴

    <dependencies>
        <!-- mybatis依賴及需要的依賴start -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <dependency>
            <groupId>cglib</groupId>
            <artifactId>cglib</artifactId>
            <version>3.2.9</version>
        </dependency>
        <dependency>
            <groupId>commons-logging</groupId>
            <artifactId>commons-logging</artifactId>
            <version>1.2</version>
        </dependency>
        <dependency>
            <groupId>ognl</groupId>
            <artifactId>ognl</artifactId>
            <version>3.2.8</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-log4j12</artifactId>
            <version>1.7.25</version>
        </dependency>
        <!-- mybatis依賴及需要的依賴 end -->
        <dependency>
            <groupId>org.mybatis.generator</groupId>
            <artifactId>mybatis-generator-core</artifactId>
            <version>1.3.7</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6</artifactId>
            <version>11.2.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.9.1</version>
            <!-- 2.10及以上版本新增了對java9的特性支持，不兼容 -->
        </dependency>
    </dependencies>

2. MyBatis 配置文件

• mybatis-config.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
  PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!-- 配置文件包含mybatis核心設(shè)置 -->
<configuration>
    <!-- 引入外部資源文件 -->
    <properties resource="db.properties"></properties>
    
    <settings>
        <!-- 指定mybatis所用日志的具體實現(xiàn) -->
        <setting name="logImpl" value="LOG4J"/>
        <!-- 設(shè)置駝峰匹配 -->
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>
    <!-- 設(shè)置包掃描(別名) -->
    <typeAliases>
        <package name="com.zhougl.web.beans" />
    </typeAliases>
    <!-- 配置環(huán)境：可以配置多個環(huán)境，default：配置某一個環(huán)境的唯一標(biāo)識，表示默認使用哪個環(huán)境 -->
    <environments default="development">
        <environment id="development">
        <!-- 指定事務(wù)管理類型，type="JDBC"指直接簡單實用了JDBC提交和回滾設(shè)置 -->
            <transactionManager type="JDBC" />
            <!-- dataSource指配置數(shù)據(jù)源，type="POOLED"是JDBC連接對象的數(shù)據(jù)源連接池的實現(xiàn) -->
            <dataSource type="POOLED">
                <!-- 配置連接信息 -->
                <property name="driver" value="${jdbc.driverClass}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}" />
                <property name="password" value="${jdbc.password}" />
            </dataSource>
        </environment>
    </environments>
    <!-- 配置持久化類映射文件：用來配置sql語句和結(jié)果集類型等 -->
    <mappers>
        <mapper resource="com/zhougl/web/dao/mapper/PhoneUserMapper.xml" />
    </mappers>
</configuration>

3. Mapper映射文件

• PhoneUserMapper.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">
<!-- namespace習(xí)慣上設(shè)置成包名+sql映射文件（PhoneUserMapper.xml去除后綴） -->
<mapper namespace="com.zhougl.web.dao.mapper.PhoneUserMapper">
  <resultMap id="BaseResultMap" type="com.zhougl.web.beans.PhoneUser">
    <id column="ID" jdbcType="VARCHAR" property="id" />
    <result column="NAME" jdbcType="VARCHAR" property="name" />
    <result column="BIRTH_DAY" jdbcType="TIMESTAMP" property="birthDay" />
    <result column="ID_NO" jdbcType="VARCHAR" property="idNo" />
    <result column="MOBILE_TEL" jdbcType="VARCHAR" property="mobileTel" />
    <result column="E_MAIL" jdbcType="VARCHAR" property="eMail" />
    <result column="PASSWD" jdbcType="VARCHAR" property="passwd" />
    <result column="CREATE_DATE" jdbcType="TIMESTAMP" property="createDate" />
  </resultMap>
  <insert id="insertPhoneUser" parameterType="com.zhougl.web.beans.PhoneUser">
      <selectKey keyProperty="id" resultType="java.lang.String" order="BEFORE">
        select SQ_PHONE_USER_ID.nextval as id from dual
      </selectKey>
    insert into PHONE_USER (ID, NAME, BIRTH_DAY, 
      ID_NO, MOBILE_TEL, E_MAIL, 
      PASSWD, CREATE_DATE)
    values (#{id,jdbcType=VARCHAR}, #{name,jdbcType=VARCHAR}, #{birthDay,jdbcType=TIMESTAMP}, 
      #{idNo,jdbcType=VARCHAR}, #{mobileTel,jdbcType=VARCHAR}, #{eMail,jdbcType=VARCHAR}, 
      #{passwd,jdbcType=VARCHAR}, #{createDate,jdbcType=TIMESTAMP})
  </insert>
  <select id="selectPhoneUserById" parameterType="String" resultMap="BaseResultMap">
    select * from PHONE_USER where ID=#{id,jdbcType=VARCHAR}
  </select>
</mapper>

4.MyBatis完成數(shù)據(jù)操作代碼

public class BasicMyBaitisTest {
    public static void main(String[] args) throws IOException {
        //讀取mybatis-config.xml配置文件
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        //初始化mybatis.創(chuàng)建SQLSessionFactory實例
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //創(chuàng)建session實例
        SqlSession session = sqlSessionFactory.openSession();
        //創(chuàng)建phoneUser 對象
        PhoneUser user = new PhoneUser("test", "吳總", new Date(1992, 9, 26), "54232819800130824X", "15555555858", "wuzong@163.com", "123456", new Date());
        //插入數(shù)據(jù) 
        //session.insert("com.zhougl.web.dao.mapper.PhoneUserMapper.insertPhoneUser", user);
        PhoneUser userOne = session.selectOne("com.zhougl.web.dao.mapper.PhoneUserMapper.selectPhoneUserById", "1");
        System.out.println(userOne.toString());
        //提交事務(wù)
        session.commit();
        //關(guān)閉session
        session.close();
    }
}

//打印結(jié)果
PhoneUser [id=1, name=測試, birthDay=Tue Oct 23 00:00:00 CST 1984, idNo=220724198410236590, mobileTel=199123425678, eMail=ces@qq.com, passwd=1, createDate=Thu Nov 22 00:00:00 CST 2018]

5. log4j日志文件配置

#全局日志配置
#log4j.rootLogger=ERROR,stdout
log4j.rootLogger=DEBUG,Console
#MyBatis日志配置
#log4j.logger.com.zhougl.mapper.UserMapper=DEBUG
#控制臺輸出
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

6.mybatis-generator插件使用（快速生成代碼）

• generatorConfig.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE generatorConfiguration
        PUBLIC "-//mybatis.org//DTD MyBatis Generator Configuration 1.0//EN"
        "http://mybatis.org/dtd/mybatis-generator-config_1_0.dtd">

<generatorConfiguration>
    <!-- 引入外部資源文件 -->
    <properties resource="db.properties"></properties>
    <!--
        出現(xiàn)錯誤：Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
        解決辦法：將本地的MAVEN倉庫中的mysql驅(qū)動引入進來
    -->
    <classPathEntry location="D:\Oracle\ojdbc6.jar"/>

    <context id="oraclegenerator" targetRuntime="MyBatis3">
        <!-- 生成的Java文件的編碼 -->
        <property name="javaFileEncoding" value="UTF-8"/>
        <!-- 格式化java代碼 -->
        <property name="javaFormatter" value="org.mybatis.generator.api.dom.DefaultJavaFormatter"/>
        <!-- 格式化XML代碼 -->
        <property name="xmlFormatter" value="org.mybatis.generator.api.dom.DefaultXmlFormatter"/>
        <!--不生成注釋-->
        <commentGenerator>
            <property name="suppressAllComments" value="true" />
        </commentGenerator>
        <!-- 配置數(shù)據(jù)庫連接 -->
        <jdbcConnection driverClass="${jdbc.driverClass}"
                        connectionURL="${jdbc.url}"
                        userId="${jdbc.username}"
                        password="${jdbc.password}" />
                        
        <!-- java類型處理器
            用于處理DB中的類型到Java中的類型，默認使用JavaTypeResolverDefaultImpl；
            注意一點，默認會先嘗試使用Integer，Long，Short等來對應(yīng)DECIMAL和 NUMERIC數(shù)據(jù)類型；
        -->
        <javaTypeResolver type="org.mybatis.generator.internal.types.JavaTypeResolverDefaultImpl">
            <!--
                true：使用BigDecimal對應(yīng)DECIMAL和 NUMERIC數(shù)據(jù)類型
                false：默認,
                    scale>0;length>18：使用BigDecimal;
                    scale=0;length[10,18]：使用Long；
                    scale=0;length[5,9]：使用Integer；
                    scale=0;length<5：使用Short；
             -->
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>                       

        <!-- 指定javaBean生成的位置 -->
        <javaModelGenerator targetPackage="com.zhougl.web.beans" targetProject="src/main/java" >
            <!-- 在targetPackage的基礎(chǔ)上，根據(jù)數(shù)據(jù)庫的schema再生成一層package，最終生成的類放在這個package下，默認為false -->
            <property name="enableSubPackages" value="true" />
            <!-- 設(shè)置是否在getter方法中，對String類型字段調(diào)用trim()方法 -->
            <property name="trimStrings" value="true" />
        </javaModelGenerator>

        <!--指定sql映射文件生成的位置 -->
        <sqlMapGenerator targetPackage="com.zhougl.web.dao.mapper" targetProject="src/main/java" >
            <!-- enableSubPackages:是否讓schema作為包的后綴 -->
            <property name="enableSubPackages" value="false" />
        </sqlMapGenerator>
        <!-- 指定dao接口生成的位置，mapper接口 -->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.zhougl.web.dao" targetProject="src/main/java" >
            <property name="enableSubPackages" value="false" />
        </javaClientGenerator>

        <!-- table表生成對應(yīng)的DoaminObject -->
        <!-- 指定表  emp 指定schema 配合enableSubPackages="false" 會讓schema作為包的后綴
        這里不指定schema，逆向工程會查詢sysuser都有哪些schema，對每個schema生成對象-->
        <table  tableName="PHONE_USER" domainObjectName="PhoneUser"></table>
        <!-- <table schema="TJ" tableName="tbl_dept" domainObjectName="Department"></table> -->

    </context>

</generatorConfiguration>

二、mybatis基礎(chǔ)

1. typeAliases類型設(shè)置別名

mybatis-config.xml文件中設(shè)置：

<!-- 配置類型別名  User 可以使用在任何使用com.zhougl.web.beans.PhoneUser的地方-->
<typeAliases>
    <typeAlias alias="User" type="com.zhougl.web.beans.PhoneUser"/>
</typeAliases>
<!-- 設(shè)置包掃描(別名) 制定一個包名，每一個在該包中的java Bean ，沒有注解的情況下會使用Bean的首字母小寫的非限定類名作為別名，有注解則為主鍵值@Alias("User")-->
<typeAliases>
    <package name="com.zhougl.web.beans" />
</typeAliases>

@Alias("User")
public class PhoneUser {
}

2.mapper映射器

<!-- 配置持久化類映射文件：用來配置sql語句和結(jié)果集類型等 -->
    <!-- 使用類路徑查找資源文件 -->
    <mappers>
        <mapper resource="com/zhougl/web/dao/mapper/PhoneUserMapper.xml" />
    </mappers>
    <!-- 使用本地文件 -->
    <mappers>
        <mapper url="file:///D:\OxygenWorkspace\maven-mybatis\src\main\java\com\zhougl\web\dao\mapper\PhoneUserMapper.xml"/>
    </mappers>
    <!-- 使用包名 非注解模式的話xml配置文件必須也處于同一級 package 下，且與Mapper類同名-->
    <mappers>
        <package name="com.zhougl.web.dao.mapper"/>
    </mappers>

• 使用接口類

  • mybatis-config.xml

    <!-- 使用接口類 -->
    <mappers>
        <mapper class="com.zhougl.web.dao.PhoneDepartmentMapper"/>
    </mappers>
    

  • 接口與映射文件位置

    接口與映射文件

  • 映射文件命名空間namespace="com.zhougl.web.dao.PhoneDepartmentMapper"必須與接口類全限定名一直

  • 測試代碼

  PhoneDepartment phoneDepartment = session.selectOne("com.zhougl.web.dao.PhoneDepartmentMapper.selectByPrimaryKey", "1");
  //結(jié)果  PhoneDepartment [id=1, departmentName=企劃部, departmentCode=D01]
  

3.<selectKey>生成主鍵

 <insert id="insert" parameterType="com.zhougl.web.beans.PhoneDepartment">
    <selectKey keyProperty="id" resultType="String" order="BEFORE">
        select SQ_PHONE_DEPARTMENT_ID.nextval as id from dual
    </selectKey>
    insert into PHONE_DEPARTMENT (ID, DEPARTMENT_NAME, DEPARTMENT_CODE
      )
    values (#{id,jdbcType=VARCHAR}, #{departmentName,jdbcType=VARCHAR}, #{departmentCode,jdbcType=VARCHAR}
      )
  </insert>

4. sql代碼段

<!--  引用 sql 片段，如果refid 指定的不在本文件中，那么需要在前面加上 namespace -->
<!-- 屬性代碼段可以被包含在其他語句里面 -->
<sql id="sometable">${prefix}Table</sql>
<sql id="someinclude">from <include refid="${include_target}"/></sql>
<!-- 屬性值可以用于包含的refid屬性或包含的代碼段里面的屬性 -->
<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>

5.SqlSessionFactoryUtil工廠類封裝方法

public class SqlSessionFactoryUtil {
    
    private static SqlSessionFactory sqlSessionFactory = null;
    //創(chuàng)建初始化SqlSessionFactory對象
    static {
        try {
            //讀取mybatis-config.xml配置文件
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            
            e.printStackTrace();
        }
    }
    //獲取SqlSession對象的靜態(tài)方法
    public static  SqlSession getSession() {
        return sqlSessionFactory.openSession();
    }
    //獲取SqlSessionFactory的靜態(tài)方法
    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }
}

6. <association>一對一查詢

<association>元素的解釋:

• column 表示當(dāng)前查詢數(shù)據(jù)庫表的列名DEPARTMENT_ID
• property 表示返回類型PhoneUserIdAndDepartment屬性名department
• javaType 表示該屬性對于的類型名稱
• select 表示執(zhí)行的查詢語句，將查詢到的數(shù)據(jù)封裝到property所代表的類型對象當(dāng)中

6.1 基本應(yīng)用

6.1.1 java bean

• WebClass.java

public class WebClass {
    private BigDecimal id;
    private String code;
    private String name;
    //班級與學(xué)生是一對多的關(guān)系
    private List<Student> students;
    
}

• Student.java

public class Student {
    private BigDecimal id;

    private String name;

    private String sex;

    private Short age;
    //學(xué)生與班級是多對一的關(guān)系
    private WebClass webClass;
} 

6.1.1 映射文件

<mapper namespace="com.zhougl.web.dao.StudentDao">
  <resultMap id="StudentMap" type="com.zhougl.web.beans.Student">
    <id column="id" jdbcType="DECIMAL" property="id" />
    <result column="studentName" jdbcType="VARCHAR" property="name" />
    <result column="SEX" jdbcType="VARCHAR" property="sex" />
    <result column="AGE" jdbcType="DECIMAL" property="age" />
    <!-- 一對多 -->
    <association property="webClass" column="class_id"
     javaType="com.zhougl.web.beans.WebClass" 
     select="selectClassById"/>
  </resultMap>
 
  <select id="selectClassById" parameterType="int" resultType="com.zhougl.web.beans.WebClass">
    select * from WEB_CLASS  where ID = #{id,jdbcType=DECIMAL}
  </select>
  <select id="selectStudent" parameterType="int" resultMap="StudentMap">
    select * from STUDENT
  </select>
 
</mapper>

6.1.2 測試代碼

public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        List<Student> students = session.selectList("com.zhougl.web.dao.StudentDao.selectStudent");
        students.forEach(student -> System.out.println(student));
        session.commit();
        session.close();
    }

• 結(jié)果

==>  Preparing: select * from STUDENT 
==> Parameters: 
====>  Preparing: select * from WEB_CLASS where ID = ? 
====> Parameters: 1(Integer)
<====      Total: 1
====>  Preparing: select * from WEB_CLASS where ID = ? 
====> Parameters: 2(Integer)
<====      Total: 1
<==      Total: 4
Student [id=1, name=null, sex=女, age=22, webClass=WebClass [id=1, code=C001, name=大亂斗指導(dǎo)班]]
Student [id=2, name=null, sex=女, age=24, webClass=WebClass [id=2, code=C002, name=無限流戰(zhàn)斗班]]
Student [id=3, name=null, sex=男, age=28, webClass=WebClass [id=1, code=C001, name=大亂斗指導(dǎo)班]]
Student [id=4, name=null, sex=女, age=26, webClass=WebClass [id=2, code=C002, name=無限流戰(zhàn)斗班]]

6.2 一對一映射（接口）

6.2.1 java bean

• card.java

public class Card {
    private Integer id;
    private String code;
    //省略構(gòu)造器和set/get方法
}

• Person.java

public class Person {
    private Integer id;
    private String name;
    private String sex;
    private Short age;
    private Card card;
    //省略構(gòu)造器和set/get方法
}

6.2.2 映射文件

• CardMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.CardMapper">
  <sql id="Base_Column_List">
    ID, CODE
  </sql>
 
  <select id="selectCardById" parameterType="integer" resultType="com.zhougl.web.beans.Card">
    select 
    <include refid="Base_Column_List" />
    from CARD
    where ID = #{id,jdbcType=DECIMAL}
  </select>
 
</mapper>

• PersonMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.PersonMapper">
    <resultMap id="personMap" type="com.zhougl.web.beans.Person">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
        <result column="SEX" jdbcType="VARCHAR" property="sex" />
        <result column="AGE" jdbcType="DECIMAL" property="age" />
        <!-- 一對一關(guān)聯(lián)映射 -->
        <association property="card" column="CARD_ID"
            javaType="com.zhougl.web.beans.Card"
            select="com.zhougl.web.dao.mapper.CardMapper.selectCardById" />
    </resultMap>

    <sql id="Base_Column_List">
        ID, NAME, SEX, AGE, CARD_ID
    </sql>

    <select id="selectPersonById" parameterType="integer"
        resultMap="personMap">
        select
        <include refid="Base_Column_List" />
        from PERSON
        where ID = #{id,jdbcType=DECIMAL}
    </select>
</mapper>

6.2.3 接口

• PersonMapper.java,與PersonMapper.xml在同一個文件夾，接口的類名必須與映射文件namespace一致

public interface PersonMapper {
    /**
     * 方法名與參數(shù)必須和xml文件中<select...>的id屬性和parameterType屬性一致
     * @param id
     * @return Person
     */
    Person selectPersonById(Integer id);  
}

6.2.4 測試類

public class OneToOneTest {
    public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        //獲得mapper接口代理對象
        PersonMapper mapper = session.getMapper(PersonMapper.class);
        //直接調(diào)用接口方法，查詢數(shù)據(jù)
        Person person = mapper.selectPersonById(1);
        System.out.println(person);
        System.out.println(person.getCard());
        session.commit();
        session.close();
    }
}

7 <collection>一對多查詢

<collection>元素的解釋:

• column 表示當(dāng)前查詢數(shù)據(jù)庫表的列名DEPARTMENT_ID
• property 表示返回類型PhoneUserIdAndDepartment屬性名department
• javaType 表示該屬性對于的類型名稱，本例是一個ArrayList集合
• select 表示執(zhí)行的查詢語句，將查詢到的數(shù)據(jù)封裝到property所代表的類型對象當(dāng)中
• ofType 表示集合當(dāng)中的類型

7.1 基本應(yīng)用

7.1.1 java bean

• 同 6.1.1

7.1.2 映射文件

<mapper namespace="com.zhougl.web.dao.WebClassDao">
    <resultMap id="WebClassMap"
        type="com.zhougl.web.beans.WebClass">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
        <!-- 一對多 -->
        <collection property="students" column="id"
            javaType="ArrayList" ofType="com.zhougl.web.beans.Student"
            select="selectStudentByClassId"
            fetchType="lazy" />
    </resultMap>
    
    <select id="selectStudentByClassId" parameterType="int" resultType="com.zhougl.web.beans.Student">
    select * from STUDENT  where class_id = #{classId,jdbcType=DECIMAL}
  </select>
  <select id="selectClass" parameterType="int" resultMap="WebClassMap">
    select * from WEB_CLASS
  </select>

</mapper>

7.1.3 測試代碼

SqlSession session = SqlSessionFactoryUtil.getSession();
        List<WebClass> classes = session.selectList("com.zhougl.web.dao.WebClassDao.selectClass");
        classes.forEach(classe ->{
            System.out.println(classe);
            List<Student> students = classe.getStudents();
            students.forEach(student -> System.out.println(student));
        });
        session.commit();
        session.close();

• 結(jié)果

==>  Preparing: select * from WEB_CLASS 
==> Parameters: 
<==      Total: 2
==>  Preparing: select * from STUDENT where class_id = ? 
==> Parameters: 2(Integer)
<==      Total: 2
WebClass [id=2, code=C002, name=無限流戰(zhàn)斗班]
Student [id=2, name=王怡, sex=女, age=24]
Student [id=4, name=王多燕, sex=女, age=26]
==>  Preparing: select * from STUDENT where class_id = ? 
==> Parameters: 1(Integer)
<==      Total: 2
WebClass [id=1, code=C001, name=大亂斗指導(dǎo)班]
Student [id=1, name=王一倩, sex=女, age=22]
Student [id=3, name=王二贊, sex=男, age=28]

7.2 一對多映射

7.2.1 mybatis配置

• mybatis-config.xml添加如下配置

<settings>
    <!-- 延遲加載的全局開關(guān) -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- true 使帶有延遲加載的屬性對象立即加載 ，false-按需加載-->
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

7.2.2 java bean

• 同 6.1.1

7.2.3 mapper映射文件

• WebClassMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.WebClassMapper">
    <resultMap id="WebClassMap"
        type="com.zhougl.web.beans.WebClass">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
        <!-- 一對多 -->
        <collection property="students" column="id"
            javaType="ArrayList" ofType="com.zhougl.web.beans.Student"
            select="com.zhougl.web.dao.mapper.StudentMapper.selectStudentByClassId"
            fetchType="lazy" >
            <id column="ID" jdbcType="DECIMAL" property="id" />
            <result column="NAME" jdbcType="VARCHAR" property="name" />
            <result column="SEX" jdbcType="VARCHAR" property="sex" />
            <result column="AGE" jdbcType="DECIMAL" property="age" />
        </collection>
    </resultMap>

    <sql id="Base_Column_List">
        ID, CODE, NAME
    </sql>
    <select id="selectWebClassById"
        parameterType="int" resultMap="WebClassMap">
        select
        <include refid="Base_Column_List" />
        from WEB_CLASS
        where ID = #{id,jdbcType=DECIMAL}
    </select>

</mapper>

7.2.4 mapper接口

• WebClassMapper.xml

public interface WebClassMapper {
  
    WebClass selectWebClassById(int i);
}

7.2.5 測試類

public class OneToManyTest {

    public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        OneToManyTest test = new OneToManyTest();
        //測試一對多
        test.testOneToMany(session);
        
        //測試多對一
        //test.testManyToOne(session);
        session.commit();
        session.close();

    }
    //測試一對多，查詢班級（一）級聯(lián)查詢學(xué)生（多）
    public void testOneToMany(SqlSession session) {
        WebClassMapper mapper = session.getMapper(WebClassMapper.class);
        WebClass webClass = mapper.selectWebClassById(1);
        System.out.println(webClass.getId()+" "+webClass.getCode()+" "+webClass.getName());
        System.out.println(webClass.toString());
        List<Student> students = webClass.getStudents();
        for (Student student : students) {
            System.out.println(student.toString());
        }
    }

}

==>  Preparing: select ID, CODE, NAME from WEB_CLASS where ID = ? 
==> Parameters: 1(Integer)
<==      Total: 1
1 C001 大亂斗指導(dǎo)班
==>  Preparing: select ID as studentId, NAME as studentName, SEX, AGE, CLASS_ID from STUDENT where CLASS_ID = ? 
==> Parameters: 1(Integer)
<==      Total: 4
WebClass [id=1, code=C001, name=大亂斗指導(dǎo)班]
Student [id=1, name=王一倩, sex=女, age=22]
Student [id=2, name=王怡, sex=女, age=24]
Student [id=3, name=王二贊, sex=男, age=28]
Student [id=4, name=王多燕, sex=女, age=26]

7.3 多對一映射

7.3.1 java bean

• 同 6.1.1

7.3.2 mapper配置

• StudentMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.StudentMapper">
  <resultMap id="StudentMap" type="com.zhougl.web.beans.Student">
    <id column="studentId" jdbcType="DECIMAL" property="id" />
    <result column="studentName" jdbcType="VARCHAR" property="name" />
    <result column="SEX" jdbcType="VARCHAR" property="sex" />
    <result column="AGE" jdbcType="DECIMAL" property="age" />
    <!-- 多對一 -->
    <association property="webClass" javaType="com.zhougl.web.beans.WebClass">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
    </association>
  </resultMap>
 
  <sql id="Base_Column_List">
    ID as studentId, NAME as studentName, SEX, AGE, CLASS_ID
  </sql>
  <sql id="student_List">
    ${student}.ID as studentId, ${student}.NAME as studentName, ${student}.SEX, ${student}.AGE, ${student}.CLASS_ID
  </sql>
  <sql id="Web_Class_Column_List">
        ${webClass}.ID , ${webClass}.CODE, ${webClass}.NAME 
  </sql>
 <!-- 多表連接 -->
 <!-- 查出來的列同名時需要使用別名區(qū)分 -->
  <select id="selectStudentById" parameterType="int" resultMap="StudentMap">
    select 
    <include refid="student_List" >
        <property name="student" value="s"/>
    </include>,
    <include refid="Web_Class_Column_List" >
        <property name="webClass" value="c"/>
    </include>
    from STUDENT s,WEB_CLASS c
    where s.class_id=c.id and s.ID = #{id,jdbcType=DECIMAL}
  </select>
  <select id="selectStudentByClassId" parameterType="int" resultMap="StudentMap">
    select 
    <include refid="Base_Column_List" />
    from STUDENT
    where CLASS_ID = #{classId,jdbcType=DECIMAL}
  </select>
 
</mapper>

7.3.3 mapper接口

• StudentMapper.java

public interface StudentMapper {
   
    Student selectStudentById(int id);
    List<Student> selectStudentByClassId(int classId);

}

7.3.4 測試類

public class OneToManyTest {
    //測試多對一，查詢學(xué)生（多）級聯(lián)查詢班級（一）
    public void testManyToOne(SqlSession session) {
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        Student student = studentMapper.selectStudentById(1);
        System.out.println(student);
        System.out.println(student.getWebClass().toString());
    }

• 結(jié)果

==>  Preparing: select s.ID as studentId, s.NAME as studentName, s.SEX, s.AGE, s.CLASS_ID , c.ID , c.CODE, c.NAME from STUDENT s,WEB_CLASS c where s.class_id=c.id and s.ID = ? 
==> Parameters: 1(Integer)
<==      Total: 1
Student [id=1, name=王一倩, sex=女, age=22]
WebClass [id=1, code=C001, name=大亂斗指導(dǎo)班]

7.4 多對多映射

7.4.1 java bean

public class WebOrder {
    private BigDecimal id;
    private String code;
    private BigDecimal total;
    private BigDecimal userId;
    //訂單和用戶是多對一關(guān)系
    private WebUser user;
    //訂單和商品是多對多關(guān)系
    private List<WebArticle> articles;
}

public class WebUser {
    private BigDecimal id;
    private String username;
    private String loginname;
    private String password;
    private String phone;
    private String address;
    
    //用戶和訂單是一對多關(guān)系
    private List<WebOrder> orders;
}

public class WebArticle {
    private BigDecimal id;
    private String name;
    private BigDecimal price;
    private String remark;
}

7.4.2 mapper配置

• WebOrderMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.WebOrderMapper">
  <resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebOrder">
    <id column="oId" jdbcType="DECIMAL" property="id" />
    <result column="CODE" jdbcType="VARCHAR" property="code" />
    <result column="TOTAL" jdbcType="DECIMAL" property="total" />
    <!-- 多對一關(guān)聯(lián) -->
    <association property="user" javaType="com.zhougl.web.beans.WebUser">
         <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="USERNAME" jdbcType="VARCHAR" property="username" />
        <result column="LOGINNAME" jdbcType="VARCHAR" property="loginname" />
        <result column="PASSWORD" jdbcType="VARCHAR" property="password" />
        <result column="PHONE" jdbcType="VARCHAR" property="phone" />
        <result column="ADDRESS" jdbcType="VARCHAR" property="address" />
    </association>
    <!-- 多對多關(guān)聯(lián) -->
    <collection property="articles" javaType="ArrayList"
        column="oId" ofType="com.zhougl.web.beans.WebArticle"
        select="com.zhougl.web.dao.mapper.WebArticleMapper.selectArticleByOrderId"
        fetchType="lazy">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
        <result column="PRICE" jdbcType="DECIMAL" property="price" />
        <result column="REMARK" jdbcType="VARCHAR" property="remark" />
    </collection>
  </resultMap>
    <!-- 有同名列，需要使用別名 -->
  <select id="selectOrderById" parameterType="int" resultMap="BaseResultMap">
    select 
    o.ID as oId, o.CODE, o.TOTAL, u.*
    from WEB_ORDER o,WEB_USER u
    where o.user_id = u.id and o.ID = #{id,jdbcType=DECIMAL}
  </select>
  <select id="selectOrderByUserId" parameterType="int" resultType="com.zhougl.web.beans.WebOrder">
    select * from WEB_ORDER where user_id = #{userId,jdbcType=DECIMAL}
  </select>
</mapper>

<mapper namespace="com.zhougl.web.dao.mapper.WebUserMapper">
  <resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebUser">
    <id column="ID" jdbcType="DECIMAL" property="id" />
    <result column="USERNAME" jdbcType="VARCHAR" property="username" />
    <result column="LOGINNAME" jdbcType="VARCHAR" property="loginname" />
    <result column="PASSWORD" jdbcType="VARCHAR" property="password" />
    <result column="PHONE" jdbcType="VARCHAR" property="phone" />
    <result column="ADDRESS" jdbcType="VARCHAR" property="address" />
    <!-- 一對多關(guān)聯(lián) -->
    <collection property="orders" javaType="ArrayList" 
        ofType="com.zhougl.web.beans.WebOrder" 
        column="id" select="com.zhougl.web.dao.mapper.WebOrderMapper.selectOrderByUserId" 
        fetchType="lazy">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="TOTAL" jdbcType="DECIMAL" property="total" />
    </collection>
  </resultMap>
 
  <sql id="Base_Column_List">
    ID, USERNAME, LOGINNAME, PASSWORD, PHONE, ADDRESS
  </sql>
 
  <select id="selectUserById" parameterType="int" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from WEB_USER
    where ID = #{id,jdbcType=DECIMAL}
  </select>
  
</mapper>

<mapper namespace="com.zhougl.web.dao.mapper.WebArticleMapper">
  <resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebArticle">
    <id column="ID" jdbcType="DECIMAL" property="id" />
    <result column="NAME" jdbcType="VARCHAR" property="name" />
    <result column="PRICE" jdbcType="DECIMAL" property="price" />
    <result column="REMARK" jdbcType="VARCHAR" property="remark" />
  </resultMap>
  
  <sql id="Base_Column_List">
    ID, NAME, PRICE, REMARK
  </sql>
  
  <select id="selectArticleByOrderId" parameterType="int" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from WEB_ARTICLE where id in(
        select article_id from WEB_ITEM where order_id =#{orderId,jdbcType=DECIMAL}
    )
  </select>
  
</mapper>

7.4.3 mapper接口

public interface WebOrderMapper {
    List<WebOrder> selectOrderByUserId(int userId);
    WebOrder selectOrderById(int id); 
}
public interface WebUserMapper {
    WebUser selectUserById(int id);
}

7.4.4 測試類

public class ManyToManyTest {

    public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        ManyToManyTest test = new ManyToManyTest();
        //test.testOneToMany(session);
        test.testManyToMany(session);
        session.commit();
        session.close();
    }
    public void testOneToMany(SqlSession session) {
        WebUserMapper userMapper = session.getMapper(WebUserMapper.class);
        WebUser user = userMapper.selectUserById(1);
        System.out.println(user.getUsername()+" "+user.getLoginname()+" "+user.getPhone()+" "+user.getAddress());
        List<WebOrder> orders = user.getOrders();
        for (WebOrder webOrder : orders) {
            System.out.println(webOrder.toString());
        }
    }
    public void testManyToMany(SqlSession session) {
        WebOrderMapper orderMapper = session.getMapper(WebOrderMapper.class);
        WebOrder order = orderMapper.selectOrderById(1);
        System.out.println(order.getCode()+" "+order.getTotal());
        WebUser user = order.getUser();
        System.out.println(user.toString());
        List<WebArticle> articles = order.getArticles();
        for (WebArticle webArticle : articles) {
            System.out.println(webArticle.toString());
        }
    }
}

8.動態(tài)SQL

mybatis才有ognl表達式來完成動態(tài)SQL。

常用元素；

• if
• choose
  • when
  • otherwise
• where
• set
• foreach
• bind

8.1 if

• 可選條件 如果不為空則加入限制條件

  <select id="selectEmployeeByIdLike"  resultType="com.zhougl.web.beans.WebEmployee">
    select * from WEB_EMPLOYEE where state = 'active'
    <!-- 可選條件 如果不為空則加入限制條件 -->
    <if test="id != null">
    and id= #{id}
    </if>
  </select>

8.2 choose

• 選擇其中的一項

<select id="selectEmployeeChooce" parameterType="hashMap"  resultType="com.zhougl.web.beans.WebEmployee">
    select * from WEB_EMPLOYEE where state = 'active'
    <!-- 條件判斷 -->
    <choose>
        <when test="id !=null"> and id= #{id} </when>
        <when test="loginname != null and password != null"> and loginname = #{loginname} and password = #{password}</when>
        <otherwise> and sex = '男' </otherwise>
    </choose>
  </select>

8.3 where

• where元素之一在一個以上的if條件有至的情況下才會去插入where子句，若最后內(nèi)容是 and 或 or 開頭，where元素會將他們?nèi)コ?/li>

<!-- where元素之一在一個以上的if條件有至的情況下才會去插入where子句，若最后內(nèi)容是 and 或 or 開頭，where元素會將他們?nèi)コ?-->
  <select id="selectEmployeeWhere" resultType="com.zhougl.web.beans.WebEmployee">
    select * from WEB_EMPLOYEE
    <where>
        <if test="state != null"> state = #{state}</if>
        <if test="id != null"> and id = #{id}</if>
        <if test="loginname != null and password != null">
            and loginname = #{loginname} and password = #{password}
        </if>
    </where>
  </select>

8.5 foreach

• 元素用來遍歷集合
• collection:指定輸入對象中的集合屬性
• item:每次遍歷生成的對象
• open:開始遍歷時的拼接字符串
• close:結(jié)束時拼接的字符串
• separator:遍歷對象之間需要拼接的字符串

<select id="selectEmployeeForeach"  resultType="com.zhougl.web.beans.WebEmployee">
  <!-- collection:指定輸入對象中的集合屬性
        item:每次遍歷生成的對象
        open:開始遍歷時的拼接字符串
       close:結(jié)束時拼接的字符串
       separator:遍歷對象之間需要拼接的字符串
       select * from WEB_EMPLOYEE where ID in (1,2,3)
    -->
    select  * from WEB_EMPLOYEE
    where ID in
    <!-- foreach 指定一個集合，聲明元素體內(nèi)的集合項和索引變量，也袁旭指定開閉匹配的字符串以及迭代中間放置分隔符 -->
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")" >
        #{item}
    </foreach>
  </select>

8.6 bind

• 預(yù)定義變量

<select id="selectEmployeeLikeName" parameterType="int" resultMap="BaseResultMap">
    <bind name="pattern" value="'%'+ _parameter.getName() +'%'"/>
    select *    from WEB_EMPLOYEE
    where name like #{pattern}
  </select>

8.7 trim

• 替換和添加

• prefix：前綴

• prefixoverride：去掉第一個and或者是or

• suffix：后綴

• suffixoverride：去掉最后一個逗號（也可以是其他的標(biāo)記，就像是上面前綴中的and一樣）

<select id="selectEmployeeTrim" resultMap="BaseResultMap">
    SELECT * FROM WEB_EMPLOYEE
    <!-- prefix：前綴　
　　      prefixoverride：去掉第一個and或者是or
        suffix：后綴　　
        suffixoverride：去掉最后一個逗號（也可以是其他的標(biāo)記，就像是上面前綴中的and一樣）  -->
    <!--這個語句中是把AND換成where，下面的寫法基本與where是等效的-->
    <trim prefix="where" prefixOverrides="and">
        <if test="state != null">and state = #{state}</if>
        <if test="id != null"> and id = #{id}</if>
        <if test="loginname != null and password != null">
            and loginname = #{loginname} and password = #{password}
        </if>
    </trim>
  </select>

8.8 set

• set 元素會動態(tài)前置set關(guān)鍵字，同時會消除無關(guān)的逗號

<update id="updateEmployeeSet" parameterType="com.zhougl.web.beans.WebEmployee">
    update WEB_EMPLOYEE
    <!-- set 元素會動態(tài)前置set關(guān)鍵字，同時會消除無關(guān)的逗號 -->
    <set>
      <if test="loginname != null">LOGINNAME = #{loginname,jdbcType=VARCHAR},</if>
      <if test="password != null">PASSWORD = #{password,jdbcType=VARCHAR},</if>
      <if test="name != null">NAME = #{name,jdbcType=VARCHAR},</if>
      <if test="sex != null">SEX = #{sex,jdbcType=VARCHAR},</if>
      <if test="age != null">AGE = #{age,jdbcType=DECIMAL},</if>
      <if test="phone != null">PHONE = #{phone,jdbcType=VARCHAR},</if>
      <if test="sal != null">SAL = #{sal,jdbcType=DECIMAL},</if>
      <if test="state != null">STATE = #{state,jdbcType=VARCHAR}</if>
    </set>
    
    where id=#{id,jdbcType=DECIMAL}
  </update>

9.事務(wù)管理及緩存機制

9.1 事務(wù)管理

9.1.1 事務(wù)特性

• 原子性。事務(wù)是應(yīng)用中最小執(zhí)行單位。
• 一致性。事務(wù)執(zhí)行的結(jié)果，必須使數(shù)據(jù)庫從一種一致狀態(tài)，變到另一種一致性狀態(tài)。
• 隔離性。事務(wù)執(zhí)行互不干擾，不能互相影響。
• 持續(xù)性。事務(wù)一提交，對數(shù)據(jù)所做的任何改變都要保存到物理數(shù)據(jù)庫中。

9.1.2 Transaction接口

• Transaction接口

  • JdbcTransaction實現(xiàn)類
  • ManagedTransaction實現(xiàn)類

• TransactionFactory接口

  • JdbcTransactionFactory實現(xiàn)類
  • ManagedTransactionFactory實現(xiàn)類

• 使用JDBC事務(wù)管理機制。利用java.sql.Connection完成對事務(wù)的提交（commit()）、回滾（rollback()）、和關(guān)閉（close()）等。

• 使用MANAGED事務(wù)管理機制。mybatis自身不會去實現(xiàn)事務(wù)管理，讓容器如weblogic、JBoss等來實現(xiàn)對事務(wù)的管理。

9.1.3 事務(wù)的配置創(chuàng)建

• mybatis-config.xml

<environment id="development">
        <!-- 指定事務(wù)管理類型，type="JDBC"指直接簡單實用了JDBC提交和回滾設(shè)置。type="MANAGED"指讓容器實現(xiàn)對事務(wù)的管理 -->
            <transactionManager type="JDBC" />
</environment>          

9.2 緩存機制

9.2.1 一級緩存（SqlSession級別）

一級緩存的作用域是SqlSession范圍的。同一個SqlSession中執(zhí)行兩次相同的SQL語句，第一次執(zhí)行會將查詢的數(shù)據(jù)寫到緩存（內(nèi)存）,第二次查詢時會從緩存中獲取數(shù)據(jù)。如果SqlSession執(zhí)行DML操作（insert、update、delete），并提交到數(shù)據(jù)庫，mybatis會清空SqlSession中的一級緩存，保證緩存中是最新信息，避免出現(xiàn)臟讀現(xiàn)象。

<u>mybatis默認開啟一級緩存。</u>

9.2.2 二級緩存（Mapper級別）

二級緩存是mapper級別的，多個SqlSession可以使用同一個mapper的SQL語句去操作數(shù)據(jù)庫，得到的數(shù)據(jù)會存在二級緩存。

9.2.2.1 開啟二級緩存配置

• mybatis-config.xml

<!-- 開啟二級緩存 -->
<setting name="cacheEnabled" value="true"/>

9.2.2.2 mapper中開啟二級緩存

• mapper.xml

<!-- 開啟當(dāng)前mapper的namespace下的二級緩存 -->
<cache eviction="LRU" flushInterval="60000" size="512" readOnly="true"/>

• cashe元素用來開啟當(dāng)前mapper的namespace下的二級緩存，屬性設(shè)置：
  • flushInterval。刷新間隔。默認不設(shè)置，緩存僅僅調(diào)用語句時刷新。
  • size。緩存數(shù)目。默認值1024。
  • readOnly。只讀。默認為false。只讀的緩存會給所有調(diào)用者返回緩存對象的相同實例，這些對象不能被修改。
  • eviction。收回策略。默認為LRU。
    • LRU。最近最少使用的策略。移出最長時間不被使用的對象。
    • FIFO。先進先出策略。按對象進入緩存的順序來移除。
    • SOFT。軟引用策略。移除基于垃圾回收器狀態(tài)和軟引用規(guī)則的對象。
    • WEAK。弱引用策略。更積極地移除基于垃圾收集器和弱引用規(guī)則的對象。
• 使用二級緩存時，查詢結(jié)果映射的Java對象必須實現(xiàn)java.io.Serializable接口。

三、注解配置

• select。映射查詢SQL語句
• selectProvider。select語句的動態(tài)SQL映射。
• insert。映射插入的SQL語句
• insertProvider。insert語句的動態(tài)SQL映射。
• update。映射更新的SQL語句。
• updateProvider。
• delete。映射刪除的SQL語句。
• deleteProvider。
• result。在列和屬性直接的單獨結(jié)果映射。屬性包括：id、column、property、javaType、jdbcTpye、type Handler、one、many。id是一個布爾值，表示十分被用于主鍵映射。one屬性是單獨的聯(lián)系，與<association>相似。many屬性是對集合而言的，與<collection>相似。
• results。多結(jié)果映射（result）列表。
• options。提供配置選項的附加值。@Options(userGenerateKeys=true,keProperty="id")
• one。復(fù)雜類型的單獨屬性映射。必須制定select屬性。表示已映射SQL語句的完全限定名。
• many。復(fù)雜類型的集合屬性映射。必須制定select屬性。表示已映射SQL語句的完全限定名。
• param。用于映射器方法來給每個參數(shù)取一個名字。否則，多參數(shù)將會以它們的順序位置和SQL語句中的表達式進行映射。
• SelectKey。用于生產(chǎn)主鍵。
  • 屬性statement="select max(empNo)+1 as myNo from emp":表示定義的子查詢語句
    • before=true：表示在之前執(zhí)行，booler類型的,所以為true
    • keyColumn="myNo":表示查詢所返回的類名
    • resultType=int.class：表示返回值得類型
    • keyProperty="empNo"：表示將該查詢的屬性設(shè)置到某個列中，此處設(shè)置到empNo中

1. CRUD基本注解

1.1 接口定義

• WebUserDao.java

public interface WebUserDao {
     /*
     * statement="select max(empNo)+1 as myNo from emp":表示定義的子查詢語句
     * before=true：表示在之前執(zhí)行，booler類型的,所以為true
     * keyColumn="myNo":表示查詢所返回的類名
     * resultType=int.class：表示返回值得類型
     * keyProperty="empNo" ：表示將該查詢的屬性設(shè)置到某個列中，此處設(shè)置到empNo中
     */
    @Insert("insert into web_user (id, username,loginname,password, phone, address) values (#{id},#{username},#{loginname},#{password},#{phone},#{address})")
    @SelectKey(statement="select SQ_PHONE_DEPARTMENT_ID.nextval as id from dual",before=true,keyColumn="id",resultType=int.class,keyProperty="id" )
    int saveUser(WebUser user);
  
    @Select("select * from web_user where id = #{id}")
    @Results({
        @Result(column="id",property="id"),
        @Result(column="username",property="username"),
        @Result(column="loginname",property="loginname"),
        @Result(column="password",property="password"),
        @Result(column="phone",property="phone"),
        @Result(column="address",property="address"),
    })
    WebUser selectUserById(int id);
    @Update("update web_user set phone=#{phone}, password=#{password},address=#{address} where id=#{id}")
    int modifyUser(WebUser user);
    
    @Delete("delete from web_user where id=${id}")
    int deleteUser(@Param("id") int id);

}

1.2 測試

• CrudAnnotationTest.java

public class CrudAnnotationTest {

        public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        WebUserDao userDao = session.getMapper(WebUserDao.class);
        CrudAnnotationTest test = new CrudAnnotationTest();
        //test.testInsert(userDao);
        //test.testUpdate(userDao);
        //test.testDelete(userDao);
        test.testSelect(userDao);
        
        session.commit();
        session.close();
    }
    
    public void testInsert(WebUserDao userDao) {
        WebUser user = new WebUser();
        user.setUsername("楊涵");
        user.setLoginname("Yanhan");
        user.setPassword("123456");
        user.setAddress("石各莊");
        userDao.saveUser(user);
        System.out.println(user.getId());
    }
    
    public void testSelect(WebUserDao userDao) {
        WebUser user = userDao.selectUserById(8);
        System.out.println(user.toString());
    }
    public void testUpdate(WebUserDao userDao) {
        WebUser user = new WebUser();
        user.setPhone("16677778888");
        user.setPassword("666666");
        user.setAddress("石家莊");
        user.setId(7);
        int num = userDao.modifyUser(user);
        System.out.println(num);
    }
    
    public void testDelete(WebUserDao userDao) {
        int num = userDao.deleteUser(7);
        System.out.println(num);
    }


}

1.3 插入空值異常處理

解決插入空值產(chǎn)生的異常 Error setting null for parameter #5 with JdbcType OTHER .
說明：postgreSQL，MySQL，SQLSERVER都支持JdbcType.NULL類型，Oracle是不支持，適配的時候也因為這個問題導(dǎo)致mybatis報錯。

解決方法：

• MyBatis-config.xml 中設(shè)置當(dāng)JDBC類型為空值時，要指定的值得，默認為OTHER，我們指定為NULL就好了（注意是大寫的NULL）。

<!-- 設(shè)置但JDBC類型為空時,某些驅(qū)動程序 要指定值,default:OTHER -->
<setting name="jdbcTypeForNull" value="NULL"/>  

• 在每個數(shù)據(jù)變量那里設(shè)置相應(yīng)的jdbcType

<select id="selectUserById" parameterType="int" resultMap="BaseResultMap">
    select * from WEB_USER
    where ID = #{id,jdbcType=DECIMAL}
  </select>

2. 一對一、一對多、多對多映射

2.1 一對一

2.1.1 java bean

• 同（二） 6.2.1

2.1.2 接口

• PersonDao.java

public interface PersonDao {
    @Select("select * from person where id = #{id}")
    @Results({
        @Result(column="ID" ,property="id"),
        @Result(column="NAME",property="name"),
        @Result(column="SEX" ,property="sex"),
        @Result(column="AGE",property="age"),
        @Result(column="card_id",property="card",//表示person的card屬性對于表的card_id列
        one=@One(
                select="com.zhougl.web.annotation.dao.CardDao.selectCardById",
                fetchType=FetchType.EAGER))
    })
    Person selectPersonById(Integer id);

}

• CardDao.java

public interface CardDao {
    @Select("select * from Card where id = #{id}")
    Card selectCardById(Integer id);
}

2.1.3 測試

public void testOneToOne(SqlSession session) {
         PersonDao dao = session.getMapper(PersonDao.class);
         Person person =dao.selectPersonById(1);
         System.out.println(person.toString());
         System.out.println(person.getCard().toString());
    }

2.2 一對多

2.2.1 java bean

• 同（二） 6.1.1

2.2.2 接口

• WebClassDao.java

public interface WebClassDao {
    @Select("select * from WEB_CLASS where ID = #{id}")
    @Results({
        @Result(column="ID",property="id" ),
        @Result(column="CODE",property="code"),
        @Result(column="NAME",property="name"),
        @Result(column="id",property="students",
        many=@Many(
                select="com.zhougl.web.annotation.dao.StudentDao.selectStudentByClassId",
                fetchType=FetchType.LAZY))
    })
    WebClass selectWebClassById(int id);
}

• StudentDao.java

public interface StudentDao {
   
    //Student selectStudentById(int id);
    @Select("select * from STUDENT  where CLASS_ID = #{classId}")
    @Results({
        @Result(column="Id",property="id"),
        @Result(column="name",property="name"),
        @Result(column="SEX",property="sex"),
        @Result(column="AGE",property="age")
    })
    List<Student> selectStudentByClassId(int classId);

}

2.2.3 測試

public void testOneToMany(SqlSession session) {
        WebClassDao dao = session.getMapper(WebClassDao.class);
         WebClass webClass = dao.selectWebClassById(1);
         System.out.println(webClass.toString());
         webClass.getStudents().forEach(student ->System.out.println(student.toString()));
    }

2.3 多對多

2.3.1 java bean

• 同（二）7.4.1

2.3.2 接口

• WebUserDao.java

public interface WebUserDao {
WebUser selectUserById(int id);
    @Update("update web_user set phone=#{phone}, password=#{password},address=#{address} where id=#{id}")
    int modifyUser(WebUser user);
}    

• WebArticleDao.java

public interface WebArticleDao {
    @Select("select  *  from WEB_ARTICLE where id in( select article_id from WEB_ITEM where order_id =#{orderId} )")
    List<WebArticle> selectArticleByOrderId(int orderId);
}

• WebOrderDao.java

public interface WebOrderDao {
    @Select("select * from WEB_ORDER where id = #{id}")
    @Results({
        @Result(column="id",property="id"),
        @Result(column="CODE",property="code"),
        @Result(column="TOTAL",property="total"),
        @Result(column="user_id",property="user",
        one=@One(select="com.zhougl.web.annotation.dao.WebUserDao.selectUserById",fetchType=FetchType.EAGER)),
        @Result(column="id",property="articles" ,
        many=@Many(select="com.zhougl.web.annotation.dao.WebArticleDao.selectArticleByOrderId",fetchType=FetchType.LAZY)),
    })
    WebOrder selectOrderById(int id); 
}

2.3.3 測試

public void testManyToMany(SqlSession session) {
        WebOrderDao dao = session.getMapper(WebOrderDao.class);
         WebOrder order = dao.selectOrderById(1);
         System.out.println(order.toString());
         System.out.println(order.getUser().toString());
         order.getArticles().forEach(article ->System.out.println(article.toString()));
        
    }

//結(jié)果
WebOrder [id=1, code=201811300001, total=3666.65, userId=null]
WebUser [id=1, username=梁二燕, loginname=yangzi, password=123456, phone=15555556666, address=武漢]
WebArticle [id=1, name=花鳥畫, price=1999.99, remark=梁老師著作]
WebArticle [id=2, name=肖像畫, price=1666.66, remark=梁老師著作]

3. Provider注解動態(tài)SQL

3.1 @InsertProvider

3.1.1 構(gòu)建動態(tài)SQL類

public class EmployeeDynaSqlProvider {
    
    public String insertEmployee(WebEmployee employee){
        return new SQL() {
            {
                INSERT_INTO("web_employee");
                if(employee.getId()!=null) {
                    VALUES("id", "#{id}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("loginname", "#{loginname}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("password", "#{password}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("name", "#{name}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("sex", "#{sex}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("age", "#{age}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("phone", "#{phone}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("sal", "#{sal}");
                }
                if(employee.getLoginname()!=null) {
                    VALUES("state", "#{state}");
                }
            }
        }.toString();
    }
}

3.1.2 接口

public interface WebEmployeeDao {
    @InsertProvider(type=EmployeeDynaSqlProvider.class,method="insertEmployee")
    @SelectKey(statement="select max(id)+1 as id from web_employee",before=true,keyColumn="id",resultType=int.class,keyProperty="id" )
    int insertEmployee(WebEmployee employee);
}

3.1.3 測試類

public class DynamicSQLTest {
    public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        WebEmployeeDao dao = session.getMapper(WebEmployeeDao.class);
        DynamicSQLTest test = new DynamicSQLTest();
        test.testInsertEmployee(dao);
        //test.testUpdateEmployee(dao);
        //test.testDeleteEmployee(dao);
        //test.testSelectWhithMap(dao);
        session.commit();
        session.close();
    }
    
    public void testInsertEmployee(WebEmployeeDao dao) {
        WebEmployee employee = new WebEmployee();
        employee.setName("菠蘿賽東");
        employee.setLoginname("boluosaidong");
        employee.setPassword("123456");
        employee.setState("active");
        int num = dao.insertEmployee(employee);
        System.out.println(num);        
    }

• 結(jié)果

==>  Preparing: select max(id)+1 as id from web_employee 
==> Parameters: 
<==      Total: 1
==>  Preparing: INSERT INTO web_employee (id, loginname, password, name, sex, age, phone, sal, state) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) 
==> Parameters: 6(Integer), boluosaidong(String), 123456(String), 菠蘿賽東(String), null, null, null, null, active(String)
<==    Updates: 1
WebEmployee [id=6, loginname=boluosaidong, password=123456, name=菠蘿賽東, sex=null, age=null, phone=null, sal=null, state=active]

3.1 @SelectProvider

3.1.1 構(gòu)建動態(tài)SQL類

public class EmployeeDynaSqlProvider {
    
    public String selectWhithMap(Map<String, Object> map) {
        return new SQL(){
            {
                SELECT("*");
                FROM("web_employee");
                //多個參數(shù)時
                //SELECT * FROM web_employee WHERE ( id = ? AND loginname = ? and password = ? AND state = ? ) 
                if(map.get("id")!=null) {
                    WHERE(" id = #{id} ");
                }
                if(map.get("loginname")!=null && map.get("password")!=null) {
                    WHERE(" loginname = #{loginname} and password = #{password} ");
                }
                if(map.get("name")!=null) {
                    WHERE(" name = #{name} ");
                }
                if(map.get("phone")!=null) {
                    WHERE(" phone = #{phone} ");
                }
                if(map.get("state")!=null) {
                    WHERE(" state = #{state} ");
                }
            }
        }.toString();
    }
}

3.1.2 接口

public interface WebEmployeeDao {
    @SelectProvider(type=EmployeeDynaSqlProvider.class,method="selectWhithMap")
    List<WebEmployee> selectEmployeeWithMap(HashMap<String, Object> map);
}

3.1.3 測試類

public class DynamicSQLTest {
    
    public void testSelectWhithMap(WebEmployeeDao dao) {
        HashMap<String, Object> map = new HashMap<String, Object>();
        //map.put("id", 1);
        //map.put("loginname", "rose");
        //map.put("password", "123456");
        map.put("state", "active");
        //map.put("state", "inactive");
        List<WebEmployee> employees = dao.selectEmployeeWithMap(map);
        employees.forEach(WebEmployee -> System.out.println(WebEmployee.toString()));
    }
}    

• 結(jié)果

==>  Preparing: SELECT * FROM web_employee WHERE ( state = ? ) 
==> Parameters: active(String)
<==      Total: 5
WebEmployee [id=1, loginname=jack, password=123456, name=蘇文, sex=男, age=26, phone=15566665555, sal=9800, state=active]
WebEmployee [id=2, loginname=rose, password=123456, name=蘇問問, sex=女, age=24, phone=16655556666, sal=6800, state=active]
WebEmployee [id=3, loginname=tom, password=123456, name=陳大山, sex=男, age=29, phone=17766665555, sal=8800, state=active]
WebEmployee [id=4, loginname=alice, password=123456, name=蘇蘇豆豆, sex=女, age=25, phone=19955556666, sal=5800, state=active]
WebEmployee [id=6, loginname=boluosaidong, password=123456, name=菠蘿賽東, sex=null, age=null, phone=null, sal=null, state=active]

3.1 @UpdateProvider

3.1.1 構(gòu)建動態(tài)SQL類

public class EmployeeDynaSqlProvider {
    
    public String updateEmployee(WebEmployee employee) {
        return new SQL() {
            {
                UPDATE("web_employee");
                if(employee.getLoginname()!=null) {
                    SET("loginname = #{loginname}");
                }
                if(employee.getLoginname()!=null) {
                    SET("password = #{password}");
                }
                if(employee.getLoginname()!=null) {
                    SET("name = #{name}");
                }
                if(employee.getLoginname()!=null) {
                    SET("sex = #{sex}");
                }
                if(employee.getLoginname()!=null) {
                    SET("age = #{age}");
                }
                if(employee.getLoginname()!=null) {
                    SET("phone = #{phone}");
                }
                if(employee.getLoginname()!=null) {
                    SET("sal = #{sal}");
                }
                if(employee.getLoginname()!=null) {
                    SET("state = #{state}");
                }
                WHERE(" id = #{id} ");
            }
        }.toString();
    }
}

3.1.2 接口

public interface WebEmployeeDao {
    @UpdateProvider(type=EmployeeDynaSqlProvider.class,method="updateEmployee")
    int updateEmployee(WebEmployee employee);
}

3.1.3 測試類

public class DynamicSQLTest {
    public void testUpdateEmployee(WebEmployeeDao dao) {
        WebEmployee employee = new WebEmployee();
        employee.setId(6);
        employee.setName("菠蘿賽西");
        employee.setLoginname("boluosaiXi");
        employee.setPassword("789012");
        employee.setState("active");
        int num = dao.updateEmployee(employee);
        System.out.println(num);        
    }       
}

• 結(jié)果

==>  Preparing: UPDATE web_employee SET loginname = ?, password = ?, name = ?, sex = ?, age = ?, phone = ?, sal = ?, state = ? WHERE ( id = ? ) 
==> Parameters: boluosaiXi(String), 789012(String), 菠蘿賽西(String), null, null, null, null, active(String), 6(Integer)
<==    Updates: 1

WebEmployee [id=6, loginname=boluosaiXi, password=789012, name=菠蘿賽西, sex=null, age=null, phone=null, sal=null, state=active]

3.1 @DeleteProvider

3.1.1 構(gòu)建動態(tài)SQL類

public class EmployeeDynaSqlProvider {
    
    public String deleteEmployee(WebEmployee employee) {
        return new SQL() {
            {
                DELETE_FROM("web_employee");
                if(employee.getId()!=null) {
                    WHERE(" id = #{id} ");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("loginname", "#{loginname}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("password", "#{password}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("name", "#{name}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("sex", "#{sex}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("age", "#{age}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("phone", "#{phone}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("sal", "#{sal}");
                }
                if(employee.getLoginname()!=null) {
                    WHERE("state", "#{state}");
                }
            }
        }.toString();
    }
}

3.1.2 接口

public interface WebEmployeeDao {
    @DeleteProvider(type=EmployeeDynaSqlProvider.class,method="deleteEmployee")
    int deleteEmployee(WebEmployee employee);
}

3.1.3 測試類

public class DynamicSQLTest {
    public void testDeleteEmployee(WebEmployeeDao dao) {
        WebEmployee employee = new WebEmployee();
        employee.setId(6);
        int num = dao.deleteEmployee(employee);
        System.out.println(num);        
    }       
}

• 結(jié)果

==>  Preparing: DELETE FROM web_employee WHERE ( id = ? ) 
==> Parameters: 6(Integer)
<==    Updates: 1