MyBatis概述

梦里梦外; 2022-06-14 10:12 117阅读 0赞

## MyBatis概述 ##

### mytatis的介绍 ###

和jdbc比较:  
mybatis抽离出[数据库][Link 1]的连接,关闭的操作.抽离了sql语句,并且可以自动的进行参数的设置,封装结果集.

和[hibernate][]比较:  
\- 性能:mybatis较hibernate高  
\- sql灵活性:mybatis较hibernate高  
\- 配置文件:mybatis较hibernate多(维护困难)  
\- 数据库的无关性:mybatis较hibernate低

mybatis是一个封装jdbc基于sql配置的半自动化ORM框架,使用于互联网,电商项目.前身ibatis,后改名为mybatis,apache的开源项目,已经迁移到GitHub代码的托管网站.

## mabatis的配置文件 ##

### 全局配置文件:sqlmapconfig.xml ###

文件中的配置项是由顺序的,按照官方图来配.  
configuration  
\- properties  
\- settings  
\- typeAliases  
\- typeHandlers  
\- objectFactory  
\- plugins  
\- environments  
\* envioronment  
\* transactionManager  
\* dataSource  
\- databaseIdProvider  
\- mappers

例如:

<properties resource="db.properties">
            <property name="jdbc.driver" value="com.mysql.jdbc.Driver"/>
        </properties>

2. settings: 全局参数设置  
设置延迟加载:

1.  typeAliases:类型别名
    
        
        <typeAliases>
            
            
            <typeAlias type="com.aric.mybatis.po.User" alias="user"/>
            
            <package name="com.aric.mybatis.po"/>
        </typeAliases>
2.  mappers:
    
        <mappers>
            
            
            
            
            
            
            <package name="com.aric.mybatis.mapper" />
        </mappers>

### 映射文件 ###

<mapper namespace="test">
    
        
        
        
        
        
        <select id="getUserById" parameterType="int" resultType="com.aric.mybatis.po.User" >
            select * from user where id=#{idd}
        </select>
    
        
        <select id="getUserByUsername" parameterType="String" resultType="com.aric.mybatis.po.User">
            
            select * from user where username like "%${value}%"
        </select>
    
        
        
        <insert id="addUser" parameterType="user">
            
            
            
            
            
            <selectKey keyProperty="id" resultType="String" order="BEFORE">
                select uuid()
            </selectKey>
            insert into user(id,username,birthday,sex,address) values(#{id},#{username},#{birthday},#{sex},#{address});
        </insert>
    
        
        
        <insert id="addUserUUID" parameterType="userUUID">
            
            
            
            <selectKey keyProperty="id" resultType="String" order="BEFORE">
                select uuid()
            </selectKey>
            insert into userUUID(id,username,birthday,sex,address) values(#{id},#{username},#{birthday},#{sex},#{address});
        </insert>
    
        
        <delete id="deleteUserById" parameterType="int">
            delete from user where id = #{id}
        </delete>
    
        
        <update id="updateUserById" parameterType="user">
            update user set birthday=#{birthday},sex=#{sex},address=#{address} where id=#{id}
        </update>
    
    </mapper>

### 注意点 ###

`#{xxx}`表示一个占位符,jdbc中的?通过\#\{xxx\} 可以将外部传递过来映射到sql语句中,可以有效的防止sql注入.  
xxx表示一个sql串的拼接,不可以有效防止sql注入.如果是\{xxx\},输入的参数类型是简单类型,那么$\{xxx\}中的xxx必须是value.

parameterType:表示输入参数的数据类型  
resultType:输出结果的数据类型,如果查询的是列表,那么resultType只需要设置列表中的元素的数据类即可.

## 核心API ##

执行流程:  
加载sqlmapconfig.xml,通过sqlsessionfactorybuilder,构建sqlsesionfactroy对象.由它构建sqlsession提供增删改查等操作数据库的方法.

### SqlSessionFactoryBuilder ###

SqlSessionFactory build(InputStream inputStream)
    SqlSessionFactory build(InputStream inputStream,String environment)
    SqlSessionFactory build(InputStream inputStream,Properties properties)
    SqlSessionFactory build(InputStream inputStream,String env,Properties props)
    SqlSessionFactory build(Configuration config)

案例

String resource = "org/mybatis/builder/mybatis-config.xml";
    InputStream  inputStream = Resource.getResourceAsStream(resource);
    SqlSessionFactoryBuilder builder = new SqlSessionFactoryBuilder();
    SqlSessionFactory factory = builder.builder(inputStream);

### SqlSessionFactory ###

创建SqlSession的方法:

SqlSession openSession()
    SqlSession openSession(boolean autoCommit)
    SqlSession openSession(Connection connection)
    SqlSession openSession(TransactionIsolationLevel level)
    SqlSession openSession(ExecutorType execType,TransactionIsolationLevel level)
    SqlSession openSession(ExecutorType execType)
    SqlSession openSession(ExecutorType execType, boolean autoCommit)
    SqlSession openSession(ExecutorType execType, Connection connection)
    Configuration getConfiguration();

• Transaction: Do you want to use a transaction scope for the session, or use auto-commit  
(usually means no transaction with most databases and/or JDBC drivers)?  
• Connection: Do you want MyBatis to acquire a Connection from the configured DataSource for  
you, or do you want to provide your own?  
• Execution: Do you want MyBatis to reuse PreparedStatements and/or batch updates (including  
inserts and deletes)?

用openSession方法获取的SqlSession有以下特点:  
\- 开启事务,但是不会自动提交事务  
\- 将从配置文件中配置的数据源中获取连接(Connection)  
\- 事务级别默认使用驱动或数据源的  
\- 预编译不会被重用,不会批量更新

列举3个执行类型参数(ExecutorType):  
\- ExecutorType.SIMPLE 每次执行都预编译  
\- ExecutorType.REUSE 重用预编译  
\- ExecutorType.BATCH 批量预编译

### SqlSession ###

SqlSession执行增删改查以及事务操作.

<T> T selectOne(String statement, Object parameter)
        <E> List<E> selectList(String statement, Object parameter)
        <K,V> Map<K,V> selectMap(String statement, Object parameter, String mapKey)
        int insert(String statement, Object parameter)
        int update(String statement, Object parameter)
        int delete(String statement, Object parameter)

#### 分页查询 ####

<E> List<E> selectList (String statement, Object parameter, RowBounds rowBounds)
        int offset = 100;
        int limit = 25;
        RowBounds rowBounds = new RowBounds(offset, limit);
    
        void select (String statement, Object parameter, ResultHandler handler)

ResultHandler参数:可以把每行记录包装成其他数据类型,如:List,Map,Set.只需要写个接口.

package org.apache.ibatis.session;
        public interface ResultHandler {
            void handleResult(ResultContext context);
        }

#### 控制事务 ####

void commit()
        void commit(boolean force)
        void rollback()
        void rollback(boolean force)

#### 缓存 ####

一级缓存指的就是sqlsession，在sqlsession中有一个数据区域，是map结构，这个区域就是一级缓存区域。一级缓存中的key是由sql语句、条件、statement等信息组成一个唯一值。一级缓存中的value，就是查询出的结果对象。

二级缓存指的就是同一个namespace下的mapper，二级缓存中，也有一个map结构，这个区域就是一级缓存区域。一级缓存中的key是由sql语句、条件、statement等信息组成一个唯一值。一级缓存中的value，就是查询出的结果对象。

一级缓存是默认使用的。  
二级缓存需要手动开启。

二级缓存的配置  
1. 开启二级缓存中开关

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

2. 在mapper映射文件中开启二级缓存

<cache/>

3. 禁止使用缓存  
useCache=”false”  
4. 刷新缓存  
select语句默认是false,增删改语句默认是true

flushCache="true"

#### Mappers ####

Mapper是个接口,里面定义方法匹配SqlSession中方法.

public interface AuthorMapper {
         // (Author) selectOne("selectAuthor",5);
         Author selectAuthor(int id); 
         // (List<Author>) selectList(“selectAuthors”)
         List<Author> selectAuthors();
         // (Map<Integer,Author>) selectMap("selectAuthors", "id")
         @MapKey("id")
         Map<Integer, Author> selectAuthors();
         // insert("insertAuthor", author)
         int insertAuthor(Author author);
         // updateAuthor("updateAuthor", author)
         int updateAuthor(Author author);
         // delete("deleteAuthor",5)
         int deleteAuthor(int id);
        }

Mapper注解,参考Mybatis官方文档.  
示例:

@Insert("insert into table3 (id, name) values(#{nameId}, #{name})")
        @SelectKey(Statement="call next value for TestSequence",keyProperty ="nameId",before=true, resultType=int.class)
        int insertTable3(Name name);
    
        @Insert("insert into table2 (name) values(#{name})")
        @SelectKey(statement="call identity()", keyProperty="nameId", before=false, resultType=int.class)
        int insertTable2(Name name);

## 动态sql ##

MyBatis使用OGNL表达式,来支持一些常用的标签.  
\- if  
\- choose(when,otherwise)  
\- trim(where,set)  
\- foreach

案例:

### if ###

<select id="getUserByIdOrUsername" parameterType="user" resultType="user">
        select * from user 
        
        <where>
            <if test="id != null">
                and id=#{id}
            </if>
            <if test="username != null and username !=''">
                and username like '%${username}%'
            </if>
        </where>
    </select>

### choose, when, otherwise ###

<select id="findActiveBlogLike" resultType="Blog">
            SELECT * FROM BLOG WHERE state = ‘ACTIVE’
            <choose>
                <when test="title != null">
                    AND title like #{title}
                </when>
                <when test="author != null and author.name != null">
                    AND author_name like #{author.name}
                </when>
                <otherwise>
                    AND featured = 1
                </otherwise>
            </choose>
        </select>

### trim, where, set ###

<update id="updateUser" parameterType="user">
        update user
        
        <set>
            <if test="address != null and address != ''">
                address =#{address},
            </if>
            <if test="sex != null and sex != ''">
                sex =#{sex},
            </if>
            <if test="username != null and username != '' ">
                username =#{username}
            </if>
        </set>
        where id=#{id}
    </update>
    
    
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        ...
    </trim>
    
    
    <trim prefix="SET" suffixOverrides=",">
        ...
    </trim>

### foreach ###

<select id="getUserListByForEach" parameterType="QueryVo" resultType="user">
        select * from user 
        <where>
            
            
            
            
            
            <foreach collection="ids" open="id in(" close=")" item="id" separator=",">
                `#{id}`
            </foreach>
        </where>
    </select>

### bind ###

创建一个ognl表达式,用于语句中.

<select id="selectBlogsLike" resultType="Blog">
         <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
         SELECT * FROM BLOG WHERE title LIKE #{pattern}
      </select>

### Multi-db vendor support ###

<insert id="insert">
            <selectKey keyProperty="id" resultType="int" order="BEFORE">
                <if test="_databaseId == 'oracle'">
                    select seq_users.nextval from dual
                </if>
                <if test="_databaseId == 'db2'">
                     select nextval for seq_users from sysibm.sysdummy1"
                </if>
            </selectKey>
            insert into users values (#{id}, #{name})
        </insert>

### Pluggable Scripting Languages For Dynamic SQL ###

为动态sql定制可插拔脚本语句

使用步骤:  
实现LanguageDriver接口

public interface LanguageDriver {
    
             ParameterHandler createParameterHandler(MappedStatement mappedStatement, Object parameterObject, BoundSql boundSql);
    
             SqlSource createSqlSource(Configuration configuration, XNode script, Class<?> parameterType);
    
             SqlSource createSqlSource(Configuration configuration, String script, Class<?> parameterType);
    
        }

再mybatis-config.xml或sqlmapconfig.xml文件中覆盖默认别名,使用自定义

在映射文件中使用

<select id="selectBlog" lang="myLanguage">
            SELECT * FROM BLOG
        </select>

也可以在mapper中使用

public interface Mapper {
             @Lang(MyLanguageDriver.class)
             @Select("SELECT * FROM BLOG")
             List<Blog> selectBlog();
        }

### sql片段 ###

抽取sql重复代码，提供效率。

<sql id="userColumns"> id,username,password </sql>
    <select id="selectUsers" resultType="map">
     select <include refid="userColumns"/>
     from some_table
     where id = #{id}
    </select>

## Dao开发 ##

实现方式:  
1. 传统的Dao建一个dao 建立一个接口,再建实现类.  
2. mapper代理的方式,只需要写接口,不需要写实现类,实现类由mybatis框架自动创建(官方推荐)

### 传统的Dao ###

*  sqlsessionfactorybuilder当作一个工具类,一旦使用完毕,就应该销毁,最佳使用范围在方法内部.
 *  sqlsessionfactory要单例存在,一旦创建就应当在整个程序运行期使用,没必要创建一次.使用范围整个运行期.(整合spring时,可以由spring来管理)
 *  sqlsession是多例的,它线程不安全的,也不能被共享的,使用范围是在方法的内部.而且,一旦使用完成,必须要关闭,在finally中关闭.

接口:

//根据用户id查询用户
    public User getUserById(Integer id);
    
    //根据用户名称模糊查询用户列表
    public List<User> getUserListByUsername(String username)
    
    //添加用户
    public void addUser(User user);

实现类:

private SqlSessionFactory factory;//需要注入
    
        public UserDaoImpl(SqlSessionFactory factory){
            this.factory = factory;
        }
    
        public User getUserById(Integer id)throws Exception{
            SqlSession sqlSession = factory.openSession();
            List<User> list = sqlSession.selectList("test.getUserByUsername",username);
            sqlSession.close();//应当在finally中关闭
            return list;
        }
    
        public void insertUser(User user){
            SqlSession sqlSession = factory.openSession();
            sqlSession.insert("","");
            sqlSession.commit();
            sqlSession.close();
        }

优化：  
可以建立一个BaseDaoImpl 继承sqlsesiondaosupport ,在此进行sqlsessionfactory注入。

### mapper代理的方式 ###

mapper代理的方式,只需要写接口,不需要写实现类,实现类由mybatis框架自动创建.  
需要遵守规则:  
1. sql的映射文件中的namespace要和Mapper接口中的类路径(全限定名)一致  
2. sql的映射文件中的sql的id要和mapper接口中的方法的名称一致  
3. sql的映射文件中的parameterType要和mapper接口中的方法的参数类型一致  
4. sql的映射文件中的resultType要和mapper接口中的方法的返回值数据类型一致

/** * 根据用户名模糊查询用户 * @param username * @return * @throws Exception */
            List<User> getUserByUsername(String username) throws Exception;
    测试:
    
            //注入sqlsessionfactory
            //获取sqlsession
            //通过sqlsession对象获取mapper的代理对象(接口的实现类实例)
            UserMapper mapper = sqlSession.getMapper(UserMapper.class)
            mapper.getUserByUsername("张");
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
      
     
      
      
       
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14

## 多表查询 ##

### 输出结果类型 ###

1.  resultType,字段和java对象中属性一致推荐使用resultType
2.  resultMap,字段和java对象中属性不一致的情况比较多的时候使用resultMap

### ResultMap ###

resultType使用时,设置值时需要查询的列名和[Java][]对象属性名一致.如果不一致的时候,可以使用resultMap.使用时需要先定义,再使用.  
resultMap做复杂的映射配置(多表查询).

映射一对一关系

<resultMap type="com.aric.mybatis.po.Orders" id="getOrdersListUserResultMap">
        
        
        
        <id column="id" property="id" />
        <id column="id" property="id"/>
        
        
        <result column="user_id" property="userId"/>
        <result column="number" property="number"/>
        <result column="createtime" property="createtime"/>
        <result column="note" property="note"/>
        
        
        
        <association property="user" javaType="com.aric.mybatis.po.User">
            <id column="username" property="username"/>
            <result column="address" property="address"/>
        </association>
    
    </resultMap>
    
    
    <select id="getOrdersListUserResultMap" resultMap="getOrdersListUserResultMap" >
        SELECT
            o.id,
            o.user_id AS userId,
            o.number,
            o.note,
            o.createtime,
            u.username,
            u.address
        FROM
            orders o
        LEFT JOIN USER u ON u.id = o.user_id
    </select>
    
    
    <select id="getOrdersUserList" resultType="com.aric.mybatis.po.OrdersCutom">
        SELECT
            o.id,
            o.user_id AS userId,
            o.number,
            o.note,
            o.createtime,
            u.username,
            u.address
        FROM
            orders o
        LEFT JOIN USER u ON u.id = o.user_id
    </select>

映射一对多关系

<resultMap type="com.aric.mybatis.po.User" id="getUserListOrdersResultMap">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result column="birthday" property="birthday"/>
        <result column="sex" property="sex"/>
        <result column="address" property="address"/>
        
        
        
        <collection property="orders" ofType="com.aric.mybatis.po.Orders">
            
            <id column="oid" property="id"/>
            <result column="number" property="number"/>
        </collection>
    </resultMap>
    
    
    <select id="getUserListOrdersResultMap" resultMap="getUserListOrdersResultMap">
        SELECT
            u.*,
            o.number,
            o.id AS oid
        FROM
            USER u
        LEFT JOIN orders o ON u.id = o.user_id
    </select>

多对多映射

<resultMap type="user" id="resultMap_user_orders_details_items">
        <id column="user_id" property="id" />
        <result column="username" property="username"/>
        
        <collection property="orders" ofType="com.aric.mybatis.po.Orders">
            <id column="order_id" property="id" />
            <result column="number" property="number" />
            
            <collection property="orderdetail" ofType="com.aric.mybatis.po.OrderDetail">
                <id column="detail_id" property="id" />
                
                <association property="items" javaType="com.aric.mybatis.po.Items">
                    <id column="item_id" property="id" />
                    <result column="name" property="name" />
                </association>
            </collection>
        </collection>
    </resultMap>
    
    
    <select id="selectUserOrders_details_items" resultMap="resultMap_user_orders_details_items">
        SELECT
            u.id AS user_id,
            u.username,
            ord.id AS order_id,
            ord.number,
            de.id AS detail_id,
            i.id AS item_id,
            i. NAME
        FROM
            USER u
        LEFT JOIN orders ord ON u.id = ord.user_id
        LEFT JOIN orderdetail de ON de.orders_id = ord.id
        LEFT JOIN items i ON de.items_id = i.id
    </select>

## mybatis和spring的整合 ##

整合的步骤  
1. 创建工程  
2. 加入jar [spring][hibernate]的包 mybatis的包 依赖包 数据库驱动 数据连接池 整合包 日志包  
3. 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>
            
    
        </configuration>

4. spring的配置文件

<?xml version="1.0" encoding="UTF-8"?>
        <beans xmlns="http://www.springframework.org/schema/beans"
            xmlns:context="http://www.springframework.org/schema/context" xmlns:p="http://www.springframework.org/schema/p"
            xmlns:aop="http://www.springframework.org/schema/aop" xmlns:tx="http://www.springframework.org/schema/tx"
            xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
            xsi:schemaLocation="http://www.springframework.org/schema/beans 
            http://www.springframework.org/schema/beans/spring-beans-4.0.xsd
            http://www.springframework.org/schema/context 
            http://www.springframework.org/schema/context/spring-context-4.0.xsd
            http://www.springframework.org/schema/aop 
            http://www.springframework.org/schema/aop/spring-aop-4.0.xsd 
            http://www.springframework.org/schema/tx 
            http://www.springframework.org/schema/tx/spring-tx-4.0.xsd
            http://www.springframework.org/schema/util 
            http://www.springframework.org/schema/util/spring-util-4.0.xsd">
    
            
            <context:property-placeholder location="classpath:db.properties" />
    
            
            <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
                
                <property name="driverClassName" value="${jdbc.driver}" />
                <property name="url" value="${jdbc.url}" />
                <property name="username" value="${jdbc.username}" />
                <property name="password" value="${jdbc.password}" />
                <property name="maxActive" value="10" />
                <property name="maxIdle" value="5" />
            </bean>
    
            
            <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
                
                <property name="configLocation" value="classpath:mybatis/SqlMapConfig.xml"></property>
                
                <property name="dataSource" ref="dataSource"></property>
            </bean>
    
            
        
    
            
            <context:component-scan base-package="com.aric.mybatis.dao" />
    
            
    
            
            <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"> 
                
                <property name="basePackage" value="com.aric.mybatis.mapper"></property>
            </bean>
    
        </beans>

5. 数据库、日志的配置文件  
db.properties

jdbc.driver=com.mysql.jdbc.Driver
        jdbc.url=jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf-8
        jdbc.username=root
        jdbc.password=123

log4j.properties

# Global logging configuration
        log4j.rootLogger=DEBUG, stdout
        # Console output...
        log4j.appender.stdout=org.apache.log4j.ConsoleAppender
        log4j.appender.console.encoding=UTF-8
        log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
        log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

[Link 1]: http://lib.csdn.net/base/mysql
[hibernate]: http://lib.csdn.net/base/javaee
[Java]: http://lib.csdn.net/base/java