MyBatis

2017/05/04 JavaEE

通过MyBatis提供的映射方式,自由灵活的生成(半自动,大部分需要程序员编写)满足需求的sql语句,可以将preparedStatement中的输入参数自动进行输入映射,将查询结果集灵活映射成java对象(输出映射)。

配置文件

主配置文件

默认SqlMapConfig.xml,配置了数据源、事务等MyBatis运行环境

<?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">
<configuration>
    <!-- 属性文件声明 -->
    <properties resource="jdbc.properties"/>
    <!--运行时配置-->
    <settings>
        <setting name="lazyLoadingEnabled" value="true"/>
    </settings>
    <!-- 别名配置 -->
    <typeAliases>
        <typeAlias type="com.xpress.model.User" alias="User"/>
    </typeAliases>
    <!-- 和spring整合后不需要配置 -->
    <environments default="development">
        <environment id="development">
            <!-- 使用jdbc事务,由mybatis控制 -->
            <transactionManager type="JDBC"/>
            <!-- 连接池由mybatis管理 -->
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClassName}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="sqlmap/Users.xml"/>
    </mappers>
</configuration>

typeAliases别名

<!-- 别名配置 -->
<typeAliases>
    <!--单独定义-->
    <typeAlias type="com.xpress.model.User" alias="User"/>
    <!--批量定义,类名就是别名,首字母大小写都可以-->
    <package name="com.xpress.model"/>
</typeAliases>

默认别名映射

别名 映射的类型 别名 映射的类型 别名 映射的类型
_byte byte byte Byte decimal BigDecimal
_long long long Long bigdecimal BigDecimal
_short short short Short object Object
_int int int Integer map Map
_integer int integer Integer hashmap HashMap
_double double double Double list List
_float float float Float arraylist ArrayList
_boolean boolean boolean Boolean collection Collection
string String date Date iterator Iterator

mappers映射文件

<mappers>
    <!-- 制定文件加载 -->
    <mapper resource="sqlmap/Users.xml"/>
    <!-- 通过mapper接口加载 -->
    <mapper class="com.xpress.dao.UserDao"/>
    <!--批量加载,制定mapper接口的包名-->
    <package name="com.xpress.dao"/>
</mappers>

通过mapper接口加载和批量加载规范

  • 在同包下放置class同名xml文件
  • 其他规范同mapper接口开发规范

typeHandlers类型处理器

类型处理器将获取的值以合适的方式转换成 Java 类型

默认typeHandlers

settings详细配置

settings详细配置

映射文件

<?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">
<!-- 命名空间用于隔离sql -->
<mapper namespace="Users">
    <resultMap id="UserResultMap" type="User">
        <result property="username" column="username"/>
        <result property="nickName" column="nickName"/>
        <result property="password" column="password"/>
    </resultMap>
    <!-- id标识映射文件中的sql -->
    <select id="selectUser" resultMap="UserResultMap" parameterType="int">
        SELECT *
        FROM users
        WHERE id = #{id}
    </select>
</mapper>

OGNL

  • #{}:占位符
    • #{id}其中的id标识一个接收输入的参数,参数名称就是id
    • 如果输入参数是简单类型,#{}中的参数名可以任意
  • ${}:拼接符
    • 拼接sql串,将接收到的参数内容不加任何修饰的拼接在sql中,不建议使用
    • 会有sql注入问题
    • ${value}接受参数,如果传入类型是简单类型,${}中只能是value

CRUD

SqlSession

String fileName = "SqlMapConfig.xml";
Reader reader = null;
SqlSession sqlSession = null;
try {
    reader = Resources.getResourceAsReader(fileName);

    SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
    SqlSessionFactory sqlSessionFactory = sqlSessionFactoryBuilder.build(reader);

    reader.close();

    sqlSession = sqlSessionFactory.openSession();
    User user = sqlSession.selectOne("Users.selectUser", 1);
    System.out.println(user);
} catch (IOException e) {
    e.printStackTrace();
} finally {
    try {
        if (reader != null) {
            reader.close();
        }
    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        if (sqlSession != null) {
            sqlSession.close();
        }
    }
}

SqlSession不是线程安全的,在SqlSession实现类中除了有接口中的方法,还有数据域属性,所以在局部使用。

select

<!-- id标识映射文件中的sql -->
<select id="selectUserById" resultMap="UserResultMap" parameterType="int">
    SELECT *
    FROM users
    WHERE id = #{id}
</select>
<!--查询多个-->
<select id="selectUserForList" resultMap="UserResultMap" parameterType="java.lang.String">
    SELECT *
    FROM users
    WHERE USERNAME LIKE '%${value}%'
</select>
User user = sqlSession.selectOne("Users.selectUserById", 1);
List<User> users = sqlSession.selectList("Users.selectUserForList", "b");

like

  • oracle
SELECT * FROM users WHERE name like CONCAT('%',#{name},'%')
SELECT * FROM users WHERE name like '%' || #{name} || '%'
  • SQLServer
SELECT * FROM users WHERE name like '%' + #{name} + '%'
  • mysql
SELECT * FROM users WHERE name like CONCAT('%',#{name},'%')
  • DB2
SELECT * FROM users WHERE name like CONCAT('%',#{name},'%')
SELECT * FROM users WHERE name like '%' || #{name} || '%'

insert

<insert id="insertUser" parameterType="User">
    <!--mysql返回自增主键-->
    <selectKey keyProperty="Id" order="AFTER" resultType="int">
        SELECT LAST_INSERT_ID()
    </selectKey>
    INSERT INTO users (USERNAME, NICKNAME, PASSWORD)
    VALUES (#{username}, #{nickName}, #{password})
</insert>
<insert id="insertUserUUID" parameterType="User">
    <!--mysql UUID主键,String类型,sql需要给出Id字段-->
    <selectKey keyProperty="Id" order="BEFORE" resultType="java.lang.String">
        SELECT uuid()
    </selectKey>
    INSERT INTO users (ID,USERNAME, NICKNAME, PASSWORD)
    VALUES (#{Id},#{username}, #{nickName}, #{password})
</insert>
<insert id="insertUserSequence" parameterType="User">
    <!--oracle sequesce主键-->
    <selectKey keyProperty="Id" order="BEFORE" resultType="int">
        SELECT userSequence.nextval()
    </selectKey>
    INSERT INTO users (ID,USERNAME, NICKNAME, PASSWORD)
    VALUES (#{Id},#{username}, #{nickName}, #{password})
</insert>
User user = new User("smith", "123", "s");
sqlSession.insert("Users.insertUser", user);
sqlSession.commit();

System.out.println(user.getId());

delete

<!--删除-->
<delete id="deleteUser" parameterType="int">
    DELETE FROM users
    WHERE ID = #{Id}
</delete>
int result = sqlSession.delete("Users.deleteUser", 27);
sqlSession.commit();

update

<!--更新-->
<update id="updateUser" parameterType="User">
    UPDATE users
    SET USERNAME = #{username}, NICKNAME = #{nickName}, PASSWORD = #{password}
    WHERE ID = #{Id}
</update>
User user = new User("smith", "123", "s");
user.setId(22);
sqlSession.update("Users.updateUser", user);
sqlSession.commit();

DAO开发

传统DAO接口方式

public class UserDaoImpl implements UserDao {
    private SqlSessionFactory sqlSessionFactory;

    @Override
    public User selectUserById(int id) {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        User user = sqlSession.selectOne("Users.selectUserById", 1);
        sqlSession.close();
        return user;
    }

    public void setSqlSessionFactory(SqlSessionFactory sqlSessionFactory) {
        this.sqlSessionFactory = sqlSessionFactory;
    }
}

mapper代理方式

与传统DAO接口方式比较

  • 传统DAO实现方法中存在大量的模板方法,这些代码增加了工作量
  • 调用SqlSession方法时statement的id硬编码
  • 调用SqlSession方法时由于泛型参数不利于编译时期发现问题

规范

  • mapper配置namespace为class的全限定名
  • 接口方法名同statement的id
  • 入参和返回值类型一致
<!-- namespace需要制定为接口的全限定名 -->
<mapper namespace="com.xpress.mybatis.UserMapper">
public interface UserMapper {
    User selectUserById(int id);
}
SqlSession sqlSession = sqlSessionFactory.openSession();
// 获取代理类
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
User user = userMapper.selectUserById(1);
sqlSession.close();

复杂类型输入映射

映射POJO

public class UserCustom extends User {
}

public class UserPO {
    private UserCustom userCustom;
    public UserCustom getUserCustom() { return userCustom; }
    public void setUserCustom(UserCustom userCustom) { this.userCustom = userCustom; }
}

public class UserVO {
    private UserCustom userCustom;
    public UserCustom getUserCustom() { return userCustom; }
    public void setUserCustom(UserCustom userCustom) { this.userCustom = userCustom; }
}
<!--输入映射复杂类型-->
<select id="selectUserListByCondition" resultType="com.xpress.model.UserCustom"
        parameterType="com.xpress.model.UserVO">
    SELECT *
    FROM users
    WHERE users.username LIKE CONCAT('%', #{userCustom.username}, '%') AND users.money > #{userCustom.money};
</select>
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
UserVO userVO = new UserVO();
UserCustom userCustom = new UserCustom();
userCustom.setUsername("s");
userCustom.setMoney(2);
userVO.setUserCustom(userCustom);
List<UserCustom> result = userMapper.selectUserListByCondition(userVO);

映射MAP

简单类型输入映射

查询结构只有一行一列时,使用简单类型进行映射

复杂类型输出映射

<!--定义resultMap映射-->
    <resultMap id="UserCustomResultMap" type="User">
        <!-- id映射主键 -->
        <id property="id" column="id"/>
        <!-- result映射普通列 -->
        <result property="username" column="name"/>
        <result property="nickName" column="nickname"/>
        <result property="password" column="pwd"/>
    </resultMap>
<select id="selectUserListForResultMap" resultMap="UserCustomResultMap"
        parameterType="com.xpress.model.UserVO">
    SELECT
        users.id,
        users.username name,
        users.nickName nickname,
        users.password pwd
    FROM users
    WHERE users.username LIKE CONCAT('%', #{userCustom.username}, '%') AND users.money > #{userCustom.money};
</select>

使用resultType

继承自字段较多的pojo,并增加继承类中不包含的查询结果字段,使用resultType映射返回。

使用resultMap

association一对一输出映射
<resultMap id="orderResultMap" type="OrderCustom">
    <id property="id" column="ID"/>
    <result property="userId" column="USER_ID"/>
    <!--定义关联实体-->
    <association property="user" javaType="User">
        <id property="id" column="UserId"/>
        <result property="username" column="USERNAME"/>
        <result property="nickName" column="NICKNAME"/>
        <result property="password" column="PASSWORD"/>
    </association>
</resultMap>

<select id="selectOrdersAndUser" resultMap="orderResultMap">
    SELECT
        orders.ID,
        USER_ID,
        users.ID UserId,
        USERNAME,
        MONEY,
        BIRTHDAY,
        AGE,
        NICKNAME,
        PASSWORD
    FROM orders, users
    WHERE orders.USER_ID = users.ID
</select>
collection一对多输出映射
<!-- 继承自UserCustomResultMap省去User的映射 -->
<resultMap id="UserOrderCustomResultMap" type="UserCustom" extends="UserCustomResultMap">
    <!--使用collection关联集合-->
    <collection property="orderList" ofType="Order">
        <id property="id" column="ORDER_ID"/>
        <result property="userId" column="USER_ID"/>
    </collection>
</resultMap>
<select id="selectUserOrders" resultMap="UserOrderCustomResultMap">
    SELECT
        users.ID,
        USERNAME,
        MONEY,
        BIRTHDAY,
        AGE,
        NICKNAME,
        PASSWORD,
        orders.ID ORDER_ID,
        USER_ID
    FROM users, orders
    WHERE orders.USER_ID = users.ID;
</select>
collection和association嵌套多对多输出映射
<resultMap id="UserOrderCustomAndDetailsResultMap" type="UserCustom" extends="UserResultMap">
    <!--关联集合-->
    <collection property="orderCustomList" ofType="OrderCustom">
        <id property="id" column="orderId"/>
        <result property="userId" column="USER_ID"/>
        <collection property="orderDetailCustomList" ofType="OrderDetailCustom">
            <id property="id" column="orderDetailId"/>
            <result property="orderId" column="ORDER_ID"/>
            <association property="item" javaType="Item">
                <id property="id" column="itemId"/>
                <result property="name" column="NAME"/>
                <result property="orderDetailId" column="orderDetailId"/>
            </association>
        </collection>
    </collection>
</resultMap>
<select id="selectUserOrdersAndDetails" resultMap="UserOrderCustomAndDetailsResultMap">
    SELECT
        users.ID,
        USERNAME,
        MONEY,
        BIRTHDAY,
        AGE,
        NICKNAME,
        PASSWORD,
        orders.ID       orderId,
        USER_ID,
        orderdetails.ID orderDetailId,
        ORDER_ID,
        items.ID        itemId,
        NAME,
        ORDER_DETAIL_ID
    FROM users, orders, orderdetails, items
    WHERE
        orders.USER_ID = users.ID
        AND orderdetails.ORDER_ID = orders.ID
        AND items.ORDER_DETAIL_ID = orderdetails.ID;
</select>

延迟加载

先从单表查询,需要时再从关联表查询,提高数据库性能。

<settings>
    <!-- 开启延迟加载 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- 关闭访问对象后自动加载对象列 -->
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>
<select id="selectOrdersByUserId" resultType="Order" parameterType="int">
    SELECT
        ID,
        USER_ID
    FROM orders
    WHERE orders.USER_ID = #{value}
</select>
<resultMap id="UserOrderCustomResultMapLazyLoad" type="UserCustom">
    <!--延迟加载,association和collectin都支持-->
    <collection property="orderCustomList"
                select="com.xpress.dao.mybatis.OrderMapper.selectOrdersByUserId" column="ID" fetchType="lazy">
    </collection>
</resultMap>

<select id="selectUserOrdersLazyLoad" resultMap="UserOrderCustomResultMapLazyLoad">
    SELECT
        ID,
        USERNAME,
        MONEY,
        BIRTHDAY,
        AGE,
        NICKNAME,
        PASSWORD
    FROM users
</select>

动态sql

DYNAMIC ELEMENTS

iBatis

  • dynamic
  • isNotNull
  • isNull
  • isNotPropertyAvailable
  • isPropertyAvailable
  • isEqual
  • isNotEqual
  • isGreaterThan
  • isGreaterEqual
  • isLessThan
  • isLessEqual
  • isEmpty
  • isNotEmpty
  • isParameterPresent
  • isNotParameterPresent
  • iterate

MyBatis

  • include
  • bind
  • sql
  • trim
  • where
  • set
  • foreach
  • choose
  • when
  • otherwise
  • if

iBatis

dynamic、isNotNull、isParameterPresent
update B2C_USER_ROLE_RELATION
<dynamic prepend="set">
  <isNotNull prepend="," property="record.userid">
    USERID = #record.userid:VARCHAR#
  </isNotNull>
  <isNotNull prepend="," property="record.roleId">
    ROLE_ID = #record.roleId:VARCHAR#
  </isNotNull>
</dynamic>
<isParameterPresent>
  <include refid="Criteria_Where_Clause" />
</isParameterPresent>
isPropertyAvailable
update B2C_ORDER
<dynamic prepend="set">
    <isPropertyAvailable property="orderFlagColor" prepend=",">
        <isNotNull property="orderFlagColor">
            <![CDATA[
               ORDER_FLAG_COLOR = #orderFlagColor:VARCHAR#
               ]]>
        </isNotNull>
        <isNull property="orderFlagColor">
            <![CDATA[
               ORDER_FLAG_COLOR = null
               ]]>
        </isNull>
    </isPropertyAvailable>
    <isNotNull prepend="," property="modifyTime">
        MODIFY_TIME = #modifyTime:TIMESTAMP#
    </isNotNull>
</dynamic>
where ORDER_ID = #orderId:CHAR#
iterate
<iterate property="oredCriteria[].criteriaWithListValue[].values"
    open="(" close=")" conjunction=",">
    #oredCriteria[].criteriaWithListValue[].values[]#
</iterate>

MyBatis

include
select
<include refid="Base_Column_List"/>
from users
where ID = #{id,jdbcType=INTEGER}
trim

在sql头、尾增加前缀或后缀,替换开头或结尾的制定sql

替换可配置多个suffixOverrides="AND|OR"

<trim prefix="(" suffix=")" suffixOverrides=",">
    <if test="id != null">
        ID,
    </if>
    <if test="username != null">
        USERNAME,
    </if>
</trim>
where

动态where并可以去除第一个and

set
<set>
    <if test="record.id != null">
        ID = #{record.id,jdbcType=INTEGER},
    </if>
    <if test="record.username != null">
        USERNAME = #{record.username,jdbcType=VARCHAR},
    </if>
</set>
if和sql片段
<select id="selectUserListForResultMap" resultMap="UserCustomResultMap"
        parameterType="com.xpress.model.UserVO">
    SELECT
    users.id,
    users.username name,
    users.nickName nickname,
    users.password pwd
    FROM users
    <!-- where标签可以自动去掉第一个and -->
    <where>
        <!--引入where语句sql片段-->
        <include refid="user_where_clause"/>
    </where>
</select>
<!--定义where语句sql片段-->
<!--一般不包含where,防止组合使用产生多个where-->
<!--基于单表定义,重用度高-->
<sql id="user_where_clause">
    <if test="userCustom!=null">
        <if test="userCustom.username!=null and userCustom.username!=''">
            and users.username LIKE CONCAT('%', #{userCustom.username}, '%')
        </if>
        <if test="userCustom.money!=null">
            AND users.money > #{userCustom.money}
        </if>
    </if>
</sql>
foreach
public class UserVO {
    private List<Integer> userIds;
    private UserCustom userCustom;
    public UserCustom getUserCustom() { return userCustom; }
    public void setUserCustom(UserCustom userCustom) {this.userCustom = userCustom; }
    public List<Integer> getUserIds() { return userIds; }
    public void setUserIds(List<Integer> userIds) { this.userIds = userIds; }
}
<sql id="user_where_clause">
    <if test="userCustom!=null">
        <!-- 使用and(or)形式 -->
        <if test="userIds!=null">
            <foreach collection="userIds" item="userId" open="AND (" close=")" separator="OR">
                id=#{userId}
            </foreach>
        </if>
        <!-- 使用in()形式 -->
        <if test="userIds!=null">
            <foreach collection="userIds" item="userId" open="IN (" close=")" separator=",">
                #{userId}
            </foreach>
        </if>
    </if>
</sql>
choose、when、otherwise
<choose>
    <when test="criterion.noValue">
        and ${criterion.condition}
    </when>
    <when test="criterion.singleValue">
        and ${criterion.condition} #{criterion.value}
    </when>
    <when test="criterion.listValue">
        and ${criterion.condition}
        <foreach close=")" collection="criterion.value" item="listItem" open="(" separator=",">
            #{listItem}
        </foreach>
    </when>
    <otherwise>
    </otherwise>
</choose>

缓存

缓存用于减轻数据库压力,提高查询性能

MyBatis提供一级缓存和二级缓存

  • 一级缓存互不影响,SqlSession级别
  • 二级缓存是共用的,Mapper(namespace)级别

一级缓存和二级缓存

一级缓存

MyBatis默认支持一级缓存

一级缓存

  • 第一次查询,先去缓存中找,如果没有,从数据库中查询,得到信息放入一级缓存中
  • 如果这个SqlSession去执行commit操作,清空一级缓存,避免脏读
  • 第二次查询,先去缓存中找,直接从缓存中获取信息

注意 集成spring(使用mybatis-spring)时:

  • 每次查询spring会重新创建SqlSession,所以一级缓存是不生效的。
  • 而当开启事务时,spring会使用同一个SqlSession做查询,所以这个情况下一级缓存是生效的
应用

一次事务中查询两次数据

二级缓存

  • 第一次查询,得到信息放入二级缓存中
  • 如果另外的SqlSession去执行commit操作,清空二级缓存
  • 第二次查询,先去二级缓存中找,直接从缓存中获取信息
  • 二级缓存按照namespace划分

二级缓存

使用二级缓存

1.主配置文件打开二级缓存

<setting name="cacheEnabled" value="true"/>

2.mapper.xml中开启本namespace二级缓存,表示此mapper开启二级缓存

<mapper namespace="com.xpress.dao.mybatis.UserMapper">
    <cache/>

cache标签参数

  • flushInterval:毫秒形式的自动刷新,不设置仅仅调用语句时刷新
  • size:内存资源,默认1024
  • readOnly:可读写的缓存会返回缓存对象的拷贝(通过序列化),这会慢一些,但是安全,因此默认是false。
  • eviction:回收策略,默认LRU
    • LRU:最近最少使用
    • FIFO:先进先出
    • SOFT
    • WEAK

3.返回的po需要实现序列化接口,二级缓存可能不止在内存中存储

4.在statement语句中使用useCache设置是否使用二级缓存,flushCache是否刷新缓存

<select id="selectUserById" resultMap="UserResultMap" parameterType="int" useCache="false">
<update id="updateUser" parameterType="User" flushCache="true">
整合Ehcache

MyBatis提供的cache接口org.apache.ibatis.cache.Cache
MyBatis默认cache实现org.apache.ibatis.cache.impl.PerpetualCache

导入jar

org.mybatis.caches:mybatis-ehcache net.sf.ehcache:ehcache-core

<dependency>
    <groupId>org.mybatis.caches</groupId>
    <artifactId>mybatis-ehcache</artifactId>
    <version>1.1.0</version>
</dependency>
<mapper namespace="com.xpress.dao.mybatis.UserMapper">
    <!-- mybatis ehcache缓存配置 -->
    <!-- 以下两个<cache>标签二选一,第一个可以输出日志,第二个不输出日志 -->
    <cache type="org.mybatis.caches.ehcache.LoggingEhcache"/>
    <cache type="org.mybatis.caches.ehcache.EhcacheCache"/>

配置ehcache.xml

<ehcache updateCheck="false">
    <diskStore path="java.io.tmpdir"/>
    <defaultCache
            maxEntriesLocalHeap="10000"
            eternal="false"
            timeToIdleSeconds="120"
            timeToLiveSeconds="120"
            overflowToDisk="true"
            maxEntriesLocalDisk="10000000"
            diskPersistent="false"
            diskExpiryThreadIntervalSeconds="120"
            memoryStoreEvictionPolicy="LRU"
    />
</ehcache>
应用

对于访问多的,访问实时性要求不高的,查询耗时的sql使用二级缓存

MyBatis二级缓存对细粒度的数据级别缓存实现不好,无法小范围控制缓存刷新

整合Spring

增加依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.1</version>
</dependency>

配置sqlSessionFactory

<!-- sqlSessionFactoryJ交给spring管理 -->
<bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
    <property name="dataSource" ref="dataSource"/>
    <property name="configLocation" value="classpath:mybatis/SqlMapConfig.xml"/>
</bean>

传统DAO形式

<bean id="userDao" class="com.xpress.dao.mybatis.UserDaoImpl">
    <property name="sqlSessionFactory" ref="sqlSessionFactory"/>
</bean>
public class UserDaoImpl extends SqlSessionDaoSupport implements UserDao {
    @Override
    public User selectUserById(int id) {
        User user = getSqlSession().selectOne("com.xpress.dao.mybatis.UserMapper.selectUserById", id);
        return user;
    }
}

Mapper接口形式

直接声明

<!--声明mapper-->
<bean id="userMapper" class="org.mybatis.spring.mapper.MapperFactoryBean">
    <property name="mapperInterface" value="com.xpress.dao.mybatis.UserMapper"/>
    <property name="sqlSessionFactory" ref="sqlSessionFactory"/>
</bean>

或者使用包扫描器

<!--配置包扫描器,自动扫描mapper*及其配置文件*,SqlMapConfig.xml中配置文件扫描可省略-->
<bean class="org.mybatis.spring.mapper.MapperScannerConfigurer">
    <!--使用bean name配置因为直接引用有加载顺序问题,数据源未加载-->
    <property name="sqlSessionFactoryBeanName" value="sqlSessionFactory"/>
    <!--扫描多个包使用逗号分隔-->
    <property name="basePackage" value="com.xpress.dao.mybatis"/>
</bean>

逆向工程

使用MyBatis Generator根据配置文件生成

<?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>

<!-- !!!! Driver Class Path !!!! -->
<classPathEntry location="C:\Users\edianzu\.m2\repository\mysql\mysql-connector-java\5.1.41\mysql-connector-java-5.1.41.jar"/>

<context id="context" targetRuntime="MyBatis3">
    <commentGenerator>
        <property name="suppressAllComments" value="false"/>
        <property name="suppressDate" value="true"/>
    </commentGenerator>

    <!-- !!!! Database Configurations !!!! -->
    <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql://localhost:3306/mydb"
                    userId="username" password="password"/>

    <javaTypeResolver>
        <property name="forceBigDecimals" value="false"/>
    </javaTypeResolver>

    <!-- !!!! Model Configurations !!!! -->
    <javaModelGenerator targetPackage="com.xpress.model" targetProject="THIS_CONFIGURATION_IS_NOT_REQUIRED">
        <property name="enableSubPackages" value="false"/>
        <property name="trimStrings" value="true"/>
    </javaModelGenerator>

    <!-- !!!! Mapper XML Configurations !!!! -->
    <sqlMapGenerator targetPackage="com.xpress.dao.mybatis" targetProject="THIS_CONFIGURATION_IS_NOT_REQUIRED">
        <property name="enableSubPackages" value="false"/>
    </sqlMapGenerator>

    <!-- !!!! Mapper Interface Configurations !!!! -->
    <javaClientGenerator targetPackage="com.xpress.dao.mybatis" targetProject="THIS_CONFIGURATION_IS_NOT_REQUIRED"
                         type="XMLMAPPER">
        <property name="enableSubPackages" value="false"/>
    </javaClientGenerator>

    <!-- !!!! Table Configurations !!!! -->
    <table tableName="items" enableCountByExample="true" enableDeleteByExample="true" enableSelectByExample="true"
           enableUpdateByExample="true"/>
    <table tableName="users" enableCountByExample="true" enableDeleteByExample="true" enableSelectByExample="true"
           enableUpdateByExample="true"/>
    <table tableName="orderdetails" enableCountByExample="true" enableDeleteByExample="true"
           enableSelectByExample="true"
           enableUpdateByExample="true"/>
    <table tableName="orders" enableCountByExample="true" enableDeleteByExample="true" enableSelectByExample="true"
           enableUpdateByExample="true"/>
</context>
</generatorConfiguration>

标准化查询

UsersExample usersExample = new UsersExample();
usersExample.createCriteria().andIdEqualTo(1);
List<Users> userCustoms = usersMapper.selectByExample(usersExample);
for (Users userCustom : userCustoms) {
    System.out.println(userCustom);
}

动态更新

  • updateByPrimaryKey 全部更新
  • updateByPrimaryKeySelective 只更新有值的字段

分页查询

iBatis

在自定义SqlMapClientFactoryBean中加载自定义sqlExecutor,重写Executor的executeQuery()

MyBatis

使用PageHelper分页

原理:使用Mybatis的interceptor对Executor的query方法进行拦截后增强selectCount和分页语句查询,并返回查询结果。

<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper</artifactId>
    <version>5.0.1</version>
</dependency>

MyBatis插件配置

<plugins>
    <!-- com.github.pagehelper为PageHelper类所在包名 -->
    <plugin  interceptor="com.github.pagehelper.PageInterceptor">
        <!-- 4.0.0以后版本可以不设置该参数 -->
        <!--<property name="dialect" value="mysql"/>-->
        <!-- 该参数默认为false -->
        <!-- 设置为true时,会将RowBounds第一个参数offset当成pageNum页码使用 -->
        <!-- 和startPage中的pageNum效果一样-->
        <property name="offsetAsPageNum" value="true"/>
        <!-- 该参数默认为false -->
        <!-- 设置为true时,使用RowBounds分页会进行count查询 -->
        <property name="rowBoundsWithCount" value="true"/>
        <!-- 设置为true时,如果pageSize=0或者RowBounds.limit = 0就会查询出全部的结果 -->
        <!-- (相当于没有执行分页查询,但是返回结果仍然是Page类型)-->
        <property name="pageSizeZero" value="true"/>
        <!-- 3.3.0版本可用 - 分页参数合理化,默认false禁用 -->
        <!-- 启用合理化时,如果pageNum<1会查询第一页,如果pageNum>pages会查询最后一页 -->
        <!-- 禁用合理化时,如果pageNum<1或pageNum>pages会返回空数据 -->
        <property name="reasonable" value="true"/>
        <!-- 3.5.0版本可用 - 为了支持startPage(Object params)方法 -->
        <!-- 增加了一个`params`参数来配置参数映射,用于从Map或ServletRequest中取值 -->
        <!-- 可以配置pageNum,pageSize,count,pageSizeZero,reasonable,orderBy,不配置映射的用默认值 -->
        <!-- 不理解该含义的前提下,不要随便复制该配置 -->
        <!--<property name="params" value="pageNum=start;pageSize=limit;"/>-->
        <!-- 支持通过Mapper接口参数来传递分页参数 -->
        <property name="supportMethodsArguments" value="true"/>
        <!-- always总是返回PageInfo类型,check检查返回类型是否为PageInfo,none返回Page -->
        <property name="returnPageInfo" value="check"/>
    </plugin>
</plugins>

推荐使用分页方式(无侵入性),其他方式官方文档

UsersExample example = new UsersExample();
example.setOrderByClause("ID ASC");
List<Users> userList = usersMapper.selectByExampleWithRowbounds(example, new RowBounds(2, 3));
for (Users users : userList) {
    System.out.println(users);
}
// 使用PageHelper提供的Page类获取分页信息
Page<Users>  page = (Page<Users>) userList;
System.out.println(page.getPages());
System.out.println(page.getTotal());
System.out.println(page.getStartRow());
System.out.println(page.getEndRow());
System.out.println(page.getPageNum());

批量操作

iBatis

使用Spring-orm提供的org.springframework.orm.ibatis.SqlMapClientTemplate的startBatch方式

MyBatis

使用SqlSession Batch
SqlSession session = sessionFactory.openSession(ExecutorType.BATCH,false);
Users user = null;
for (int i = 0; i < 999; i++) {
    user = new Users();
    user.setUsername(String.valueOf(i));
    session.insert("com.xpress.dao.mybatis.UsersMapper.insert",user);
    session.clearCache();
}
session.commit();// 提交事务时批量操作才会写入数据库
session.close();
数据库方言
  • 查询:使用mysql values或者union all语句
  • 删除和修改:使用in语句
<!-- 批量插入并返回用户id -->
<insert id="insertUsersBatch" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO users (ID, USERNAME, MONEY, BIRTHDAY, AGE, NICKNAME, PASSWORD)
    VALUES
    <foreach collection="list" item="users" index="index" separator=",">
        (
        #{users.id},
        #{users.username},
        #{users.money},
        #{users.birthday},
        #{users.age},
        #{users.nickname},
        #{users.password}
        )
    </foreach>
</insert>

Mapper接口

int insertUsersBatch(List<Users> list);
List<Users> users = new ArrayList<Users>();
Users user = new Users();
user.setUsername("a");
Users user1 = new Users();
user1.setUsername("a1");
Users user2 = new Users();
user2.setUsername("a2");
users.add(user);
users.add(user1);
users.add(user2);
int count = usersMapper.insertUsersBatch(users);
System.out.println(user.getId());
System.out.println(user1.getId());
System.out.println(user2.getId());

优缺点

与JDBC相比

  • 减少了代码量
  • 性能增强
  • SQL语句与代码分离
  • 增强了可移植性

与hibernate相比

  • hibernate
    • 是一个标准的orm框架,入门门槛高
    • 不需要写些sql
    • 对sql语句进行优化、修改比较困难
    • 适用于需求变化不多的中小型项目
  • mybatis
    • 是一个不完全的orm框架,专注sql本身
    • 需要写sql
    • 对sql语句进行优化、修改方便
    • 适用于需求变化较多的项目

源码分析

  • SqlSessionFactory
  • SqlSession
  • Executor
  • MappedStatement

以上概念总结于传智播客iBatis课程

Search

    Post Directory