mybatis 学习笔记（总）2018-12-02

作者: Ernest_Chou | 来源:发表于2018-12-02 03:28 被阅读0次

MyBatis

mybatis-3.4.6
jdk1.8+

一、mybatis入门

1.依赖jar包

mybatis基础包

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核心设置 -->
<configuration>
    <!-- 引入外部资源文件 -->
    <properties resource="db.properties"></properties>
    
    <settings>
        <!-- 指定mybatis所用日志的具体实现 -->
        <setting name="logImpl" value="LOG4J"/>
        <!-- 设置驼峰匹配 -->
        <setting name="mapUnderscoreToCamelCase" value="true" />
    </settings>
    <!-- 设置包扫描(别名) -->
    <typeAliases>
        <package name="com.zhougl.web.beans" />
    </typeAliases>
    <!-- 配置环境：可以配置多个环境，default：配置某一个环境的唯一标识，表示默认使用哪个环境 -->
    <environments default="development">
        <environment id="development">
        <!-- 指定事务管理类型，type="JDBC"指直接简单实用了JDBC提交和回滚设置 -->
            <transactionManager type="JDBC" />
            <!-- dataSource指配置数据源，type="POOLED"是JDBC连接对象的数据源连接池的实现 -->
            <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语句和结果集类型等 -->
    <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习惯上设置成包名+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完成数据操作代码

public class BasicMyBaitisTest {
    public static void main(String[] args) throws IOException {
        //读取mybatis-config.xml配置文件
        InputStream is = Resources.getResourceAsStream("mybatis-config.xml");
        //初始化mybatis.创建SQLSessionFactory实例
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(is);
        //创建session实例
        SqlSession session = sqlSessionFactory.openSession();
        //创建phoneUser 对象
        PhoneUser user = new PhoneUser("test", "吴总", new Date(1992, 9, 26), "54232819800130824X", "15555555858", "wuzong@163.com", "123456", new Date());
        //插入数据 
        //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());
        //提交事务
        session.commit();
        //关闭session
        session.close();
    }
}

//打印结果
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>
    <!--
        出现错误：Caused by: java.lang.ClassNotFoundException: com.mysql.jdbc.Driver
        解决办法：将本地的MAVEN仓库中的mysql驱动引入进来
    -->
    <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>
        <!-- 配置数据库连接 -->
        <jdbcConnection driverClass="${jdbc.driverClass}"
                        connectionURL="${jdbc.url}"
                        userId="${jdbc.username}"
                        password="${jdbc.password}" />
                        
        <!-- java类型处理器
            用于处理DB中的类型到Java中的类型，默认使用JavaTypeResolverDefaultImpl；
            注意一点，默认会先尝试使用Integer，Long，Short等来对应DECIMAL和 NUMERIC数据类型；
        -->
        <javaTypeResolver type="org.mybatis.generator.internal.types.JavaTypeResolverDefaultImpl">
            <!--
                true：使用BigDecimal对应DECIMAL和 NUMERIC数据类型
                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的基础上，根据数据库的schema再生成一层package，最终生成的类放在这个package下，默认为false -->
            <property name="enableSubPackages" value="true" />
            <!-- 设置是否在getter方法中，对String类型字段调用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表生成对应的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基础

1. typeAliases类型设置别名

mybatis-config.xml文件中设置：

<!-- 配置类型别名  User 可以使用在任何使用com.zhougl.web.beans.PhoneUser的地方-->
<typeAliases>
    <typeAlias alias="User" type="com.zhougl.web.beans.PhoneUser"/>
</typeAliases>
<!-- 设置包扫描(别名) 制定一个包名，每一个在该包中的java Bean ，没有注解的情况下会使用Bean的首字母小写的非限定类名作为别名，有注解则为主键值@Alias("User")-->
<typeAliases>
    <package name="com.zhougl.web.beans" />
</typeAliases>

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

2.mapper映射器

<!-- 配置持久化类映射文件：用来配置sql语句和结果集类型等 -->
    <!-- 使用类路径查找资源文件 -->
    <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");
//结果  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;
    //创建初始化SqlSessionFactory对象
    static {
        try {
            //读取mybatis-config.xml配置文件
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            
            e.printStackTrace();
        }
    }
    //获取SqlSession对象的静态方法
    public static  SqlSession getSession() {
        return sqlSessionFactory.openSession();
    }
    //获取SqlSessionFactory的静态方法
    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }
}

6. <association>一对一查询

<association>元素的解释:

column 表示当前查询数据库表的列名DEPARTMENT_ID
property 表示返回类型PhoneUserIdAndDepartment属性名department
javaType 表示该属性对于的类型名称
select 表示执行的查询语句，将查询到的数据封装到property所代表的类型对象当中

6.1 基本应用

6.1.1 java bean

WebClass.java

public class WebClass {
    private BigDecimal id;
    private String code;
    private String name;
    //班级与学生是一对多的关系
    private List<Student> students;
    
}

Student.java

public class Student {
    private BigDecimal id;

    private String name;

    private String sex;

    private Short age;
    //学生与班级是多对一的关系
    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();
    }

结果

==>  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=大乱斗指导班]]
Student [id=2, name=null, sex=女, age=24, webClass=WebClass [id=2, code=C002, name=无限流战斗班]]
Student [id=3, name=null, sex=男, age=28, webClass=WebClass [id=1, code=C001, name=大乱斗指导班]]
Student [id=4, name=null, sex=女, age=26, webClass=WebClass [id=2, code=C002, name=无限流战斗班]]

6.2 一对一映射（接口）

6.2.1 java bean

card.java

public class Card {
    private Integer id;
    private String code;
    //省略构造器和set/get方法
}

Person.java

public class Person {
    private Integer id;
    private String name;
    private String sex;
    private Short age;
    private Card card;
    //省略构造器和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" />
        <!-- 一对一关联映射 -->
        <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 {
    /**
     * 方法名与参数必须和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);
        //直接调用接口方法，查询数据
        Person person = mapper.selectPersonById(1);
        System.out.println(person);
        System.out.println(person.getCard());
        session.commit();
        session.close();
    }
}

7 <collection>一对多查询

<collection>元素的解释:

column 表示当前查询数据库表的列名DEPARTMENT_ID
property 表示返回类型PhoneUserIdAndDepartment属性名department
javaType 表示该属性对于的类型名称，本例是一个ArrayList集合
select 表示执行的查询语句，将查询到的数据封装到property所代表的类型对象当中
ofType 表示集合当中的类型

7.1 基本应用

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();

结果

==>  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=无限流战斗班]
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=大乱斗指导班]
Student [id=1, name=王一倩, sex=女, age=22]
Student [id=3, name=王二赞, sex=男, age=28]

7.2 一对多映射

7.2.1 mybatis配置

mybatis-config.xml添加如下配置

<settings>
    <!-- 延迟加载的全局开关 -->
    <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();

    }
    //测试一对多，查询班级（一）级联查询学生（多）
    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 大乱斗指导班
==>  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=大乱斗指导班]
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>
 <!-- 多表连接 -->
 <!-- 查出来的列同名时需要使用别名区分 -->
  <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 {
    //测试多对一，查询学生（多）级联查询班级（一）
    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());
    }

结果

==>  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=大乱斗指导班]

7.4 多对多映射

7.4.1 java bean

public class WebOrder {
    private BigDecimal id;
    private String code;
    private BigDecimal total;
    private BigDecimal userId;
    //订单和用户是多对一关系
    private WebUser user;
    //订单和商品是多对多关系
    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;
    
    //用户和订单是一对多关系
    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" />
    <!-- 多对一关联 -->
    <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>
    <!-- 多对多关联 -->
    <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" />
    <!-- 一对多关联 -->
    <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.动态SQL

mybatis才有ognl表达式来完成动态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子句，若最后内容是 and 或 or 开头，where元素会将他们去除

<!-- where元素之一在一个以上的if条件有至的情况下才会去插入where子句，若最后内容是 and 或 or 开头，where元素会将他们去除 -->
  <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:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串

<select id="selectEmployeeForeach"  resultType="com.zhougl.web.beans.WebEmployee">
  <!-- collection:指定输入对象中的集合属性
        item:每次遍历生成的对象
        open:开始遍历时的拼接字符串
       close:结束时拼接的字符串
       separator:遍历对象之间需要拼接的字符串
       select * from WEB_EMPLOYEE where ID in (1,2,3)
    -->
    select  * from WEB_EMPLOYEE
    where ID in
    <!-- foreach 指定一个集合，声明元素体内的集合项和索引变量，也袁旭指定开闭匹配的字符串以及迭代中间放置分隔符 -->
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")" >
        #{item}
    </foreach>
  </select>

8.6 bind

预定义变量

<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：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的and一样）

<select id="selectEmployeeTrim" resultMap="BaseResultMap">
    SELECT * FROM WEB_EMPLOYEE
    <!-- prefix：前缀　
　　      prefixoverride：去掉第一个and或者是or
        suffix：后缀　　
        suffixoverride：去掉最后一个逗号（也可以是其他的标记，就像是上面前缀中的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 元素会动态前置set关键字，同时会消除无关的逗号

<update id="updateEmployeeSet" parameterType="com.zhougl.web.beans.WebEmployee">
    update WEB_EMPLOYEE
    <!-- set 元素会动态前置set关键字，同时会消除无关的逗号 -->
    <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.事务管理及缓存机制

9.1 事务管理

9.1.1 事务特性

原子性。事务是应用中最小执行单位。
一致性。事务执行的结果，必须使数据库从一种一致状态，变到另一种一致性状态。
隔离性。事务执行互不干扰，不能互相影响。
持续性。事务一提交，对数据所做的任何改变都要保存到物理数据库中。

9.1.2 Transaction接口

Transaction接口
- JdbcTransaction实现类
- ManagedTransaction实现类
TransactionFactory接口
- JdbcTransactionFactory实现类
- ManagedTransactionFactory实现类
使用JDBC事务管理机制。利用java.sql.Connection完成对事务的提交（commit()）、回滚（rollback()）、和关闭（close()）等。
使用MANAGED事务管理机制。mybatis自身不会去实现事务管理，让容器如weblogic、JBoss等来实现对事务的管理。

9.1.3 事务的配置创建

mybatis-config.xml

<environment id="development">
        <!-- 指定事务管理类型，type="JDBC"指直接简单实用了JDBC提交和回滚设置。type="MANAGED"指让容器实现对事务的管理 -->
            <transactionManager type="JDBC" />
</environment>

9.2 缓存机制

9.2.1 一级缓存（SqlSession级别）

一级缓存的作用域是SqlSession范围的。同一个SqlSession中执行两次相同的SQL语句，第一次执行会将查询的数据写到缓存（内存）,第二次查询时会从缓存中获取数据。如果SqlSession执行DML操作（insert、update、delete），并提交到数据库，mybatis会清空SqlSession中的一级缓存，保证缓存中是最新信息，避免出现脏读现象。

<u>mybatis默认开启一级缓存。</u>

9.2.2 二级缓存（Mapper级别）

二级缓存是mapper级别的，多个SqlSession可以使用同一个mapper的SQL语句去操作数据库，得到的数据会存在二级缓存。

9.2.2.1 开启二级缓存配置

mybatis-config.xml

<!-- 开启二级缓存 -->
<setting name="cacheEnabled" value="true"/>

9.2.2.2 mapper中开启二级缓存

mapper.xml

<!-- 开启当前mapper的namespace下的二级缓存 -->
<cache eviction="LRU" flushInterval="60000" size="512" readOnly="true"/>

cashe元素用来开启当前mapper的namespace下的二级缓存，属性设置：
- flushInterval。刷新间隔。默认不设置，缓存仅仅调用语句时刷新。
- size。缓存数目。默认值1024。
- readOnly。只读。默认为false。只读的缓存会给所有调用者返回缓存对象的相同实例，这些对象不能被修改。
- eviction。收回策略。默认为LRU。
  - LRU。最近最少使用的策略。移出最长时间不被使用的对象。
  - FIFO。先进先出策略。按对象进入缓存的顺序来移除。
  - SOFT。软引用策略。移除基于垃圾回收器状态和软引用规则的对象。
  - WEAK。弱引用策略。更积极地移除基于垃圾收集器和弱引用规则的对象。
使用二级缓存时，查询结果映射的Java对象必须实现java.io.Serializable接口。

三、注解配置

select。映射查询SQL语句
selectProvider。select语句的动态SQL映射。
insert。映射插入的SQL语句
insertProvider。insert语句的动态SQL映射。
update。映射更新的SQL语句。
updateProvider。
delete。映射删除的SQL语句。
deleteProvider。
result。在列和属性直接的单独结果映射。属性包括：id、column、property、javaType、jdbcTpye、type Handler、one、many。id是一个布尔值，表示十分被用于主键映射。one属性是单独的联系，与<association>相似。many属性是对集合而言的，与<collection>相似。
results。多结果映射（result）列表。
options。提供配置选项的附加值。@Options(userGenerateKeys=true,keProperty="id")
one。复杂类型的单独属性映射。必须制定select属性。表示已映射SQL语句的完全限定名。
many。复杂类型的集合属性映射。必须制定select属性。表示已映射SQL语句的完全限定名。
param。用于映射器方法来给每个参数取一个名字。否则，多参数将会以它们的顺序位置和SQL语句中的表达式进行映射。
SelectKey。用于生产主键。
- 属性statement="select max(empNo)+1 as myNo from emp":表示定义的子查询语句
  - before=true：表示在之前执行，booler类型的,所以为true
  - keyColumn="myNo":表示查询所返回的类名
  - resultType=int.class：表示返回值得类型
  - keyProperty="empNo"：表示将该查询的属性设置到某个列中，此处设置到empNo中

1. CRUD基本注解

1.1 接口定义

WebUserDao.java

public interface WebUserDao {
     /*
     * statement="select max(empNo)+1 as myNo from emp":表示定义的子查询语句
     * before=true：表示在之前执行，booler类型的,所以为true
     * keyColumn="myNo":表示查询所返回的类名
     * resultType=int.class：表示返回值得类型
     * keyProperty="empNo" ：表示将该查询的属性设置到某个列中，此处设置到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 插入空值异常处理

解决插入空值产生的异常 Error setting null for parameter #5 with JdbcType OTHER .
说明：postgreSQL，MySQL，SQLSERVER都支持JdbcType.NULL类型，Oracle是不支持，适配的时候也因为这个问题导致mybatis报错。

解决方法：

MyBatis-config.xml 中设置当JDBC类型为空值时，要指定的值得，默认为OTHER，我们指定为NULL就好了（注意是大写的NULL）。

<!-- 设置但JDBC类型为空时,某些驱动程序 要指定值,default:OTHER -->
<setting name="jdbcTypeForNull" value="NULL"/>

在每个数据变量那里设置相应的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()));
        
    }

//结果
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注解动态SQL

3.1 @InsertProvider

3.1.1 构建动态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);        
    }

结果

==>  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 构建动态SQL类

public class EmployeeDynaSqlProvider {
    
    public String selectWhithMap(Map<String, Object> map) {
        return new SQL(){
            {
                SELECT("*");
                FROM("web_employee");
                //多个参数时
                //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()));
    }
}

结果

==>  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 构建动态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);        
    }       
}

结果

==>  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 构建动态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);        
    }       
}

结果

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

mybatis 学习笔记（总）2018-12-02

MyBatis

一、mybatis入门

1.依赖jar包

2. MyBatis 配置文件

3. Mapper映射文件

4.MyBatis完成数据操作代码

5. log4j日志文件配置

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

二、mybatis基础

1. typeAliases类型设置别名

2.mapper映射器

3.<selectKey>生成主键

4. sql代码段

5.SqlSessionFactoryUtil工厂类封装方法

6. <association>一对一查询

6.1 基本应用

6.1.1 java bean

6.1.1 映射文件

6.1.2 测试代码

6.2 一对一映射（接口）

6.2.1 java bean

6.2.2 映射文件

6.2.3 接口

6.2.4 测试类

7 <collection>一对多查询

7.1 基本应用

7.1.1 java bean

7.1.2 映射文件

7.1.3 测试代码

7.2 一对多映射

7.2.1 mybatis配置

7.2.2 java bean

7.2.3 mapper映射文件

7.2.4 mapper接口

7.2.5 测试类

7.3 多对一映射

7.3.1 java bean

7.3.3 mapper接口

7.3.4 测试类

7.4 多对多映射

7.4.1 java bean

7.4.2 mapper配置

7.4.3 mapper接口

7.4.4 测试类

8.动态SQL

8.1 if

8.2 choose

8.3 where

8.5 foreach

8.6 bind

预定义变量

8.7 trim

8.8 set

9.事务管理及缓存机制

9.1 事务管理

9.1.1 事务特性

9.1.2 Transaction接口

9.1.3 事务的配置创建

9.2 缓存机制

9.2.1 一级缓存（SqlSession级别）

9.2.2 二级缓存（Mapper级别）

9.2.2.1 开启二级缓存配置

9.2.2.2 mapper中开启二级缓存

三、注解配置

1. CRUD基本注解

1.1 接口定义

1.2 测试

1.3 插入空值异常处理

2. 一对一、一对多、多对多映射

2.1 一对一

2.1.1 java bean

2.1.2 接口

2.1.3 测试

2.2 一对多

2.2.1 java bean

2.2.2 接口

2.2.3 测试

2.3 多对多

2.3.1 java bean