MyBatis基本入门篇

No.1 基础

框架核心

1、 mybatis配置文件，包括Mybatis全局配置文件和Mybatis映射文件，其中全局配置文件配置了数据源、事务等信息；映射文件配置了SQL执行相关的 信息。

2、 mybatis通过读取配置文件信息（全局配置文件和映射文件），构造出SqlSessionFactory，即会话工厂。

3、 通过SqlSessionFactory，可以创建SqlSession即会话。Mybatis是通过SqlSession来操作数据库的。

4、 SqlSession本身不能直接操作数据库，它是通过底层的Executor执行器接口来操作数据库的。Executor接口有两个实现类，一个是普通执行器，一个是缓存执行器（默认）。

5、 Executor执行器要处理的SQL信息是封装到一个底层对象MappedStatement中。该对象包括：SQL语句、输入参数映射信息、输出结果集映射信息。其中输入参数和输出结果的映射类型包括HashMap集合对象、POJO对象类型。

MyBatis 入门

引入 jar

mybatis-3.2.7.jar

mysql-connector-java-5.1.7-bin.jar

创建实体

public class User {
    private int id;
    private String name;
    private String dept;
    private String phone;
    private String website;
    // 省略 get set 方法
}

创建配置

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

    <!-- 配置别名 -->
    <typeAliases>
        <typeAlias alias="User" type="com.kernel.bean.User" />
    </typeAliases>

    <!-- 配置环境 -->
    <environments default="development">
        <environment id="development">
            <!-- 事务管理类型 -->
            <transactionManager type="JDBC" />
            <!--
                数据源类型：
                    UNPOOLED：为每个数据操作创建一个连接
                    POOLED：创建一个数据库连接池，连接池中的每个连接将被用于数据库操作，一旦数据操作完成，MyBatis将连接返回给连接池
                    JNDI：向配置好的JNDI数据源获取数据库连接，生产环境有限考虑此方式
            -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver" />
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/mybatis" />
                <property name="username" value="root" />
                <property name="password" value="123456" />
            </dataSource>
        </environment>
    </environments>

    <!-- 配置映射文件 -->
    <mappers>
        <mapper resource="com/kernel/bean/User.xml" />
    </mappers>
</configuration>

创建映射

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kernel.bean">
    <select id="getUserById" parameterType="int" resultType="User">
        select * from user where id = #{id}
    </select>
</mapper>

测试

public class Main {
    private static SqlSessionFactory sqlSessionFactory;
    private static SqlSession sqlSession;
    private static Reader reader;
    static {
        try {
            reader = Resources.getResourceAsReader("config/mybatis-config.xml");
        } catch (IOException e) {
            e.printStackTrace();
        }
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void test {
        User user = sqlSession.selectOne("getUserById", 1);
        System.out.println(user);
    }
}

CURD

创建实体（同上）

创建 IUser

public interface IUser {
    public List<User> getUserList();

    public void insertUser(User user);

    public void updateUser(User user);

    public void deleteUser(int userId);

    public User getUser(int id);   
}

创建配置（同上）

创建映射

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kernel.dao.IUser">

    <select id="insertUser" parameterType="User">
        insert into user (name, dept, website, phone)values (#{name}, #{dept}, #{website}, #{phone})
    </select>
    <select id="deleteUser" parameterType="int">
        delete from user where id = #{id}
    </select>
    <select id="updateUser" parameterType="User">
        update user set name = #{name}, dept = #{dept}, website = #{website}, phone = #{phone} where id = #{id}
    </select>
    <select id="getUser" parameterType="int" resultType="User">
        select * from user where id = #{id}
    </select>
    <select id="getUserList" resultType="User">
        select * from user
    </select>
</mapper>

No.2 关联查询

一对多

创建实体

User

public class User implements Serializable {
    private int id;
    private String username;
    private String mobile;
    private List<Post> posts;
    // 省略 get 和 set
    // 重写 toString
}

Post

public class Post implements Serializable {
    private int id;
    private User user;
    private String title;
    private String content;
    // 省略 get 和 set
    // 重写 toString
}

创建配置（同上）

创建映射

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kernel.dao.IUser">
    <resultMap id="resultUserMap" type="User">
        <result property="id" column="user_id"/>
        <result property="username" column="username"/>
        <result property="mobile" column="mobile"/>
        <collection property="posts" ofType="Post" column="userid">
            <id property="id" column="post_id" javaType="int" jdbcType="INTEGER"/>
            <result property="title" column="title" javaType="string" jdbcType="VARCHAR"/>
            <result property="content" column="content" javaType="string" jdbcType="VARCHAR"/>
        </collection>

    </resultMap>
    <select id="getUser" parameterType="int" resultMap="resultUserMap">
        select u.*,p.* from user u, post p where u.id = p.userid and u.id = #{id}
    </select>
</mapper>

多对一

创建实体（同上）

创建配置（同上）

创建映射

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kernel.dao">
    <resultMap id="resultPostsMap" type="Post">
        <result property="id" column="post_id"/>
        <result property="title" column="title"/>
        <result property="content" column="content"/>
        <association property="user" javaType="User">
            <id property="id" column="userid"/>
            <result property="username" column="username"/>
            <result property="mobile" column="mobile"/>
        </association>

    </resultMap>
    <select id="getPosts" parameterType="int" resultMap="resultPostsMap">
        select u.*,p.* from user u, post p where u.id = p.userid and p.post_id = #{id}
    </select>
</mapper>

多对多

创建实体

OrderItem

public class OrderItem {
    private int id;
    private int number;
    private Order order;
    private Product product;
    // 省略 get 和 set
}

Order

public class Order {
    private int id;
    private String code;
    List<OrderItem> orderItems;
    // 省略 get 和 set
}

创建映射

OrderItem.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kernel.pojo">

    <insert id="addOrderItem" parameterType="OrderItem">
            insert into order_item_
                values(null,#{order.id},#{product.id},#{number})
        </insert>
    <insert id="deleteOrderItem" parameterType="OrderItem">
            delete from order_item_
                where oid = #{order.id} and pid = #{product.id}
        </insert>
</mapper>

Order.xml

    <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kernel.pojo">
    <resultMap type="Order" id="orderBean">
        <id column="oid" property="id" />
        <result column="code" property="code" />

        <collection property="orderItems" ofType="OrderItem">
            <id column="oiid" property="id" />
            <result column="number" property="number" />
            <association property="product" javaType="Product">
                <id column="pid" property="id"/>
                <result column="pname" property="name"/>
                <result column="price" property="price"/>
            </association>
        </collection>
    </resultMap>

    <select id="listOrder" resultMap="orderBean">
            select o.*,p.*,oi.*, o.id 'oid', p.id 'pid', oi.id 'oiid', p.name 'pname'
                from order_ o
                left join order_item_ oi    on o.id =oi.oid
                left join product_ p on p.id = oi.pid
        </select>

    <select id="getOrder" resultMap="orderBean">
            select o.*,p.*,oi.*, o.id 'oid', p.id 'pid', oi.id 'oiid', p.name 'pname'
                from order_ o
                left join order_item_ oi on o.id =oi.oid
                left join product_ p on p.id = oi.pid
            where o.id = #{id}
        </select>
</mapper>

Product.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.kernel.pojo">
    <resultMap type="Product" id="productBean">
        <id column="pid" property="id" />
        <result column="pname" property="name" />
        <result column="price" property="price" />

        <!-- 多对一的关系 -->
        <!-- property: 指的是属性名称, javaType：指的是属性的类型 -->
        <association property="category" javaType="Category">
            <id column="cid" property="id"/>
            <result column="cname" property="name"/>
        </association>
    </resultMap>

    <select id="listProduct" resultMap="productBean">
            select c.*, p.*, c.id 'cid', p.id 'pid', c.name 'cname', p.name 'pname'
                from category_ c
                left join product_ p on c.id = p.cid
        </select>
    <select id="getProduct" resultMap="productBean">
            select c.*, p.*, c.id 'cid', p.id 'pid', c.name 'cname', p.name 'pname'
                from category_ c
                left join product_ p on c.id = p.cid
            where p.id = #{id}
        </select>
</mapper>

No.3 动态 SQL

if

如果没有传参数 name，那么就查询所有，如果有 name 参数，那么就进行模糊查询。

<select id="listProduct" resultType="Product">
    select * from product_
    <if test="name!=null">
        where name like concat('%',#{name},'%')
    </if>           
</select>

where

如果任何条件都不成立，那么在 sql 中不会出现 where，如果有一个条件成立，则 sql 中会去掉 and、or。

<select id="listProduct" resultType="Product">
    select * from product
    <where>
        <if test="name!=null">
            and name like concat('%',#{name},'%')
        </if>
        <if test="price!=null and price!=0">
            and price > #{price}
        </if>
    </where>
</select>

set

效果与 where 类似，用于 update

<update id="updateProduct" parameterType="Product" >
    update product_
    <set>
        <if test="name != null">name=#{name},</if>
        <if test="price != null">price=#{price}</if>
    </set>
    where id=#{id}   
</update>

trim

自定义标签

<select id="listProduct" resultType="Product">
    select * from product_
    <trim prefix="where" prefixOverrides="and">
        <if test="name!=null">
            and name like concat('%', #{name}, '%')
        </if>
        <if test="price != null and price !=0">
            and price > #{price}
        </if>
    </trim>
</select>

choose

实现 if/else 的效果

<select id="listProduct" resultType="Product">
    select * from product_
    <where>
        <choose>
            <when test="name != null">
                and name like concat('%', #{name}, '%')
            </when>
            <when test="price != null and price >0">
                and price > #{price}
            </when>
            <otherwise>
                and id = 1
            </otherwise>
        </choose>
    </where>
</select>

foreach

<select id="listProduct" resultType="Product">
    select * from product_ where id in
    <foreach item="item" index="index" collection="list"
             open="(" separator="," close=")">
        #{item}
    </foreach>
</select>

bind

<select id="listProduct" resultType="Product">
    <bind name="likeName" value="'%' + name + '%'" />
    select * from product_ where name like #{likeName}
</select>

No.4 settings

No.5 注解

创建接口，使用 @Insert()、@Update()、@Select()、@Delete() 替代 xml 的语句

在配置文件中增加 <mapper class=“com.kernel.mapper.CategoryMapper"/>

一对多

@Results 通过 @Result 和 @Many 中调用多端方法相结合，来获取一对多关系

CategoryMapper

public interface CategoryMapper {
    @Select("select * from category_")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "products", 
                    javaType = List.class, 
                    column = "id", 
                    many = @Many(select = "com.kernel.pojo.ProductMapper.listByCategory"))
    })
    public List<Category> list();
}

ProductMapper

public interface ProductMapper {
    @Select(" select * from product_ where cid = #{cid}")
    public List<Product> listByCategory(int cid);
}

多对一

ProductMapper

@Select("select * from product_")
@Results(
    @Result(property = "category", column = "id", one = @One(select = "com.kernel.pojo.CategoryMapper.get"))
)
public List<Product> list();

CategoryMapper

@Select("select * from category_ where id = #{id}")
public Category get(int id);

多对多

ProductMapper

public interface ProductMapper {
    @Select("select * from product_ where id = #{id}")
    public Product get(int id);
}

OrderItemMapper

public interface OrderItemMapper {
    @Select("select * from order_item_ where oid = #{oid}")
    @Results({
            @Result(property = "product", column = "pid", one = @One(select = "com.kernel.pojo.ProductMapper.get"))
    })
    public List<OrderItem> listByOrder(int oid);
}

OrderMapper

public interface OrderMapper {
    @Select("select * from order_")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "orderItems", javaType = List.class, column = "id",
                    many = @Many(select = "com.kernel.pojo.OrderItemMapper.listByOrder"))
    })
    public List<Order> list();
}

动态 SQL

关于 sql 类

private String selectPersonSql() {
  return new SQL() {{
    SELECT("P.ID, P.USERNAME, P.PASSWORD, P.FULL_NAME");
    SELECT("P.LAST_NAME, P.CREATED_ON, P.UPDATED_ON");
    FROM("PERSON P");
    FROM("ACCOUNT A");
    INNER_JOIN("DEPARTMENT D on D.ID = P.DEPARTMENT_ID");
    INNER_JOIN("COMPANY C on D.COMPANY_ID = C.ID");
    WHERE("P.ID = A.ID");
    WHERE("P.FIRST_NAME like ?");
    OR();
    WHERE("P.LAST_NAME like ?");
    GROUP_BY("P.ID");
    HAVING("P.LAST_NAME like ?");
    OR();
    HAVING("P.FIRST_NAME like ?");
    ORDER_BY("P.ID");
    ORDER_BY("P.FULL_NAME");
  }}.toString();
}

使用 SQL 类的方式构建

public class CategsoryDynaSqlProvider {
    public String list() {
        return new SQL()
                .SELECT("*")
                .FROM("category_")
                .toString();

    }
    public String get() {
        return new SQL()
                .SELECT("*")
                .FROM("category_")
                .WHERE("id=#{id}")
                .toString();
    }

    public String add(){
        return new SQL()
                .INSERT_INTO("category_")
                .VALUES("name", "#{name}")
                .toString();
    }
    public String update(){
        return new SQL()
                .UPDATE("category_")
                .SET("name=#{name}")
                .WHERE("id=#{id}")
                .toString();
    }
    public String delete(){
        return new SQL()
                .DELETE_FROM("category_")
                .WHERE("id=#{id}")
                .toString();
    }
}

注解

手写sql语句
@Insert(" insert into category_ ( name ) values (#{name}) ")  
public int add(Category category);  
使用sql类
@InsertProvider(type=CategoryDynaSqlProvider.class,method="add")  
public int add(Category category);  

No.6 延迟加载

MyBatis 默认是积极加载的，开启日志，一对多查询，走起

当我查询商品分类和商品时，控制台发送的sql如下：

DEBUG [main] - ==>  Preparing: select * from category_ 
DEBUG [main] - ==> Parameters: 
TRACE [main] - <==    Columns: id, name
TRACE [main] - <==        Row: 1, category1
TRACE [main] - <==        Row: 2, category2
DEBUG [main] - <==      Total: 2
category1
DEBUG [main] - ==>  Preparing: select * from product_ where cid = ? 
DEBUG [main] - ==> Parameters: 1(Integer)
TRACE [main] - <==    Columns: id, name, price, cid
TRACE [main] - <==        Row: 1, product a, 88.88, 1
TRACE [main] - <==        Row: 2, product b, 88.88, 1
TRACE [main] - <==        Row: 3, product c, 88.88, 1
DEBUG [main] - <==      Total: 3
product a
product b
product c
category2
DEBUG [main] - ==>  Preparing: select * from product_ where cid = ? 
DEBUG [main] - ==> Parameters: 2(Integer)
TRACE [main] - <==    Columns: id, name, price, cid
TRACE [main] - <==        Row: 4, product x, 88.88, 2
TRACE [main] - <==        Row: 5, product y, 88.88, 2
TRACE [main] - <==        Row: 6, product z, 88.88, 2
DEBUG [main] - <==      Total: 3

当我只查询商品分类时，发送的 sql 还是三条，所以 MyBatis 是默认开启积极加载的

开启延迟加载

<settings>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

再次查询，当我需要使用商品时，才发送对应的 sql

No.7 缓存

一级缓存

同一个 session 中查询相同的记录，只查询一次，不同 session，发送多次，所以一级缓存是 session 级别的。

二级缓存

二级缓存是 sessionFactory 的缓存

<settings> 
    <!-- 打开延迟加载的开关 --> 
    <setting name="lazyLoadingEnabled" value="true" /> 
    <!-- 将积极加载改为消息加载即按需加载 --> 
    <setting name="aggressiveLazyLoading" value="false"/>
    <setting name="cacheEnabled" value="true"/>
</settings> 

将映射文件中 sql 包含在 cache 标签中

<mapper namespace="com.how2java.pojo">
    <cache/>
    <insert id="addCategory" parameterType="Category" >
        insert into category_ ( name ) values (#{name})   
    </insert>
    <delete id="deleteCategory" parameterType="Category" >
        delete from category_ where id= #{id}  
    </delete>

    <select id="getCategory" parameterType="_int" resultType="Category">
        select * from   category_  where id= #{id}   
    </select>

    <update id="updateCategory" parameterType="Category" >
        update category_ set name=#{name} where id=#{id}   
    </update>
    <select id="listCategory" resultType="Category">
        select * from   category_
        <if test="start!=null and count!=null">
            limit #{start},#{count}
        </if>
    </select>    
</mapper>

No.8 逆向工程

Mybatis Generator是一个用于Mybatis逆向工程的工具，通过表创建 pojo、mapper

导包 mysql-connector-java-5.0.8-bin.jar

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>
    <context id="testTables" targetRuntime="MyBatis3">

        <commentGenerator>
            <!--去掉生成日期那行注释-->
            <property name="suppressDate" value="true"/>
            <!--是否去除自动生成的注释-->
            <property name="suppressAllComments" value="false"/>
        </commentGenerator>
        <!--数据库连接信息-->
        <jdbcConnection driverClass="com.mysql.jdbc.Driver" connectionURL="jdbc:mysql:///mybatis" userId="root" password="123456"/>
        <javaTypeResolver>
            <property name="forceBigDecimals" value="false"/>
        </javaTypeResolver>
        <!--生成pojo-->
        <javaModelGenerator targetPackage="com.kernel.pojo" targetProject="src">
            <!--是否让schema作为包的后缀-->
            <property name="enableSubPackages" value="true"/>
            <!--从数据库返回的值被清理前后的空格-->
            <property name="trimStrings" value="true"/>
        </javaModelGenerator>
        <!--生成mapper-->
        <sqlMapGenerator targetPackage="com.kernel.pojo" targetProject="src">
            <property name="enableSubPackages" value="true"/>
        </sqlMapGenerator>
        <!--生成dao-->
        <javaClientGenerator type="XMLMAPPER" targetPackage="com.kernel.mapper" targetProject="src">
            <!--是否让schema作为包的后缀-->
            <property name="enableSubPackages" value="true"/>
        </javaClientGenerator>
        <!--
            enableCountByExample（默认true）：MyBatis3Simple为false，指定是否生成动态查询总条数语句（用于分页的总条数查询）
            enableUpdateByExample（默认true）：MyBatis3Simple为false，指定是否生成动态修改语句（只修改对象中不为空的属性）
            enableDeleteByExample（默认true）：MyBatis3Simple为false，指定是否生成动态删除语句；
            enableSelectByExample（默认true）：MyBatis3Simple为false，指定是否生成动态查询语句；
        -->
        <table tableName="category_" domainObjectName="Category" enableCountByExample="false" enableUpdateByExample="false" enableDeleteByExample="false" enableSelectByExample="true" selectByPrimaryKeyQueryId="false">
            <!--忽略keyColumn、keyProperty和useGeneratedKeys三个属性的生成-->
            <property name="my.isgen.usekeys" value="true"/>
            <!--使用自动增长主键-->
            <generatedKey column="id" sqlStatement="JDBC"/>
        </table>
    </context>
</generatorConfiguration>

测试

public class Test {
    public static void main(String[] args) throws IOException, XMLParserException, InvalidConfigurationException, SQLException, InterruptedException {
        List<String> warnings = new ArrayList<String>();
        boolean overwrite = true;
        InputStream is= Test.class.getClassLoader().getResource("generatorConfig.xml").openStream();
        ConfigurationParser cp = new ConfigurationParser(warnings);
        Configuration config = cp.parseConfiguration(is);
        is.close();
        DefaultShellCallback callback = new DefaultShellCallback(overwrite);
        MyBatisGenerator myBatisGenerator = new MyBatisGenerator(config, callback, warnings);
        myBatisGenerator.generate(null);
        System.out.println("生成成功");
    }
}

No.9 其他

日志

在 src 下创建 log4j.properties

# Global logging configuration
log4j.rootLogger=ERROR, stdout
# MyBatis logging configuration...
log4j.logger.com.how2java=TRACE
# Console output...
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%5p [%t] - %m%n

MyBatis 会自动打印出sql语句

分页

xml方式

<select id="listCategory" resultType="Category">
   select * from   category_
   <if test="start!=null and count!=null">
      limit #{start},#{count}
   </if>
</select>

注解方式

@Select("select * from category_ limit #{start},#{count}")
public List<Category> listByPage(@Param("start") int start, @Param("count") int count);

事务管理

将事务提交方式设置为 jdbc 后，将采用 jdbc 的方式提交事务，加入有一条记录失败，自动回滚，注意，数据表的类型必须是 INNODB

PageHelper

PageHelper 是一款 Mybatis 分页插件。

加入jar

pagehelper-5.1.0-beta2.jar、jsqlparser-1.0.jar

配置插件

<plugins>
    <plugin interceptor="com.github.pagehelper.PageInterceptor"/>
</plugins>

分页查询只需要在执行查询之前，执行

PageHelper.offsetPage(0, 5);

获取总数

PageInfo page = new PageInfo<>(list);