MyBatis

1 历史背景

1.1 背景

  • SSH, Spring + Struts1,2 + Hibernate
  • SSM, Spring + SpringMVC + MyBatis

dao, 数据库连接 

create table person (id int auto_increment primary key, name varchar(20), weixin varchar(20));
insert into person (name, weixin) values ('张学友', '110'), ('刘亦菲', '220');

jdbc:

  • 自行管理连接，语法比较繁琐
  • 容易写出不健壮的代码
  • 容易忽略业务逻辑

ORM:

  • Object-Relational Mapping
public class Person {
    private int id;
    private String name;
    private String weixin;

    // getter..setter
}

1.2 Hibernate

想法:

  1. 助手，读取数据库，获取相关的类，建立一种映射关系。session

  2. 使用助手操作数据库: 

    Book book = new Book("hhhh", 222);
session.save(book);

Book book1 = session.get(2L, Book.class);
System.out.println(book1.getName());

示例，首先，在项目中添加依赖: 

<dependency>
  <groupId>org.hibernate</groupId>
  <artifactId>hibernate-core</artifactId>
  <version>5.3.6.Final</version>
</dependency>

<dependency>
  <groupId>org.mariadb.jdbc</groupId>
  <artifactId>mariadb-java-client</artifactId>
  <version>2.3.0</version>
</dependency>

其次，创建配置文件 hibernate.cfg.xml: 

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
 "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
 "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
  <session-factory>
    <property name="hibernate.connection.driver_class">org.mariadb.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mariadb://localhost:3306/lagou</property>
    <property name="hibernate.connection.username">vip</property>
    <property name="hibernate.connection.password">vip</property>
    <property name="hibernate.dialect">org.hibernate.dialect.MariaDB10Dialect</property>

    <property name="hibernate.show_sql">true</property>

    <mapping class="com.nf147.sss.entity.Person" />
  </session-factory>
</hibernate-configuration>

然后，创建 entity: 

@Entity
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    private String weixin;

    // getter..setter
}

之后，就可以使用了: 

public static void main( String[] args )
{
    Configuration configuration = new Configuration();
    Configuration configure = configuration.configure("hibernate.cfg.xml");
    SessionFactory sessionFactory = configuration.buildSessionFactory();

    Session session = sessionFactory.openSession();
    Person person = session.get(Person.class, 1L);
    System.out.println(person.getName());

    System.out.println(session.get(Person.class, 2L).getName());

    // 新增
    Person fbb = new Person();
    fbb.setName("fanbingbing");
    fbb.setWeixin("80000000000");
    session.save(fbb);

    session.close();
    sessionFactory.close();
}

1.3 MyBatis

  • select id, name, wexin from person where ?
  • delete from person where id = ?
  • update person set..

给常用的语句，每个语句，起个名字。 

<yujus>
  <yuju name="yuju1">
    select * from person
  </yuju>

  <yuju name="yuju2">
    delete from person
  </yuju>
</juyus>

使用一个帮手，帮我们做事情: 

Helper helper; // 这个就是我们请的帮手，它能够帮我们连接、关闭数据库，能够帮我们进行数据库的查询

Person person = (Person) helper.help("yuju1");
helper.help("yuju2");

三件事:

  1. 语句
  2. 传入参数
  3. 返回值

2 核心配置文件 (mybatis-config.xml)

2.1 配置文件示例

<?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>
  <!--【定义变量，用于复用】-->
  <!-- 可以通过外部文件加载变量，也可以直接通过 property 定义 -->
  <properties resource="jdbc.properties">
    <property name="password" value="vvvvvip" />
    <property name="hello" value="world" />
  </properties>

  <!--【内部设置】-->
  <!-- 通过修改这些参数，更改 Mybatis 的内在行为 -->
  <settings>
    <setting name="multipleResultSetsEnabled" value="true"/>
    <setting name="useColumnLabel" value="true"/>
    <setting name="useGeneratedKeys" value="false"/>
    <setting name="autoMappingBehavior" value="PARTIAL"/>
    <setting name="autoMappingUnknownColumnBehavior" value="WARNING"/>
    <setting name="defaultExecutorType" value="SIMPLE"/>
    <setting name="defaultStatementTimeout" value="25"/>
    <setting name="defaultFetchSize" value="100"/>
    <setting name="safeRowBoundsEnabled" value="false"/>
    <setting name="mapUnderscoreToCamelCase" value="false"/>
    <setting name="jdbcTypeForNull" value="OTHER"/>
    <setting name="cacheEnabled" value="true"/>
    <setting name="lazyLoadingEnabled" value="true"/>
    <setting name="localCacheScope" value="SESSION"/>
    <setting name="lazyLoadTriggerMethods" value="equals,clone,hashCode,toString"/>
  </settings>

  <!--【设置别名】可以简化在 xml 中的完全限定名的使用 -->
  <!-- 通过 package 方式，可以简化写法 -->
  <typeAliases>
    <!--<typeAlias alias="Author" type="com.nf147.sss.entity.Author"/>-->
    <!--<typeAlias alias="Blog" type="com.nf147.sss.entity.Blog"/>-->
    <!--<typeAlias alias="Comment" type="com.nf147.sss.entity.Comment"/>-->
    <package name="com.nf147.sss.entity" /> <!-- 可以跟 @Alias 结合使用 -->
  </typeAliases>

  <!--【类型处理器】-->
  <!-- MyBatis 已经内置了多数的类型处理器，能应付绝大多数的类型转换 -->
  <!-- 如果我们有特定的需求，可以定义自己的处理器(实现 TypeHandler 接口或继承相关实现类) -->
  <typeHandlers>
    <!--<typeHandler handler="com.nf147.sss.handler.FakeIntegerHandler" />-->
    <package name="com.nf147.sss.handler" />
  </typeHandlers>

  <!--【实例化结果对象】-->
  <!-- 默认使用 DefaultObjectFactory 来实例化结果对象，我们也可以定义自己的，覆盖原来的 -->
  <objectFactory type="com.nf147.sss.handler.OurObjectFactory">
    <property name="someProperty" value="100"/>
  </objectFactory>

  <!--【插件】用来拦截并改变 MyBatis 的工作流 -->
  <!-- 它本质就是一个拦截器 -->
  <!-- 定义插件，需要实现 Interceptor 接口即可 -->
  <plugins>
    <plugin interceptor="com.nf147.sss.plugin.MyPlugin">
      <property name="msg" value="hello, world"/>
      <property name="dbtype" value="mysql" />
    </plugin>
  </plugins>

  <!-- 【配置连接环境】-->
  <!-- 可以配置多个，每一个需要配置两部分: 事务 + 数据源 -->
  <environments default="prod">
    <environment id="dev">
      <transactionManager type="JDBC|MANAGED" />
      <dataSource type="UNPOOLED|POOLED|JNDI|com.nf147.sss.ds.MyDataSource">
        <property name="driver" value="${driver}"/>
        <property name="url" value="${url}"/>
        <property name="username" value="${username}"/>
        <property name="password" value="${password}"/>
      </dataSource>
    </environment>
  </environments>

  <!--【多数据标识】用于在 MyBatis 中使用多数据库语句 -->
  <databaseIdProvider type="DB_VENDOR">
    <property name="MySQL" value="my"/>
    <property name="SQL Server" value="sqlserver"/>
    <property name="DB2" value="db2"/>
    <property name="Oracle" value="oracle" />
  </databaseIdProvider>

  <!--【指定映射器】-->
  <!-- 有如下四种写法 -->
  <mappers>
    <mapper resource="PersonMapper.xml" />
    <mapper url="d:/wenjian/de/weizhi/Person.xml" />
    <mapper class="com.nf147.sss.dao.CommonDAO" />
    <package name="mapper" />
  </mappers>
</configuration>

2.2 TypeHandler 示例

@MappedJdbcTypes(JdbcType.Integer)
public class FakeIntegerHandler extends IntegerTypeHandler {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Integer parameter, JdbcType jdbcType) throws SQLException {
        ps.setInt(i, parameter * parameter);
    }
}

2.3 ObjectFactory 示例

public class OurObjectFactory extends DefaultObjectFactory {
    @Override
    public <T> T create(Class<T> type) {
        System.out.println("拒绝配合");
        return null;
    }
}

2.4 DataSourceFactory 示例

public class MyDataSourceFactory extends UnpooledDataSourceFactory {
    public MyDataSourceFactory() {
        this.dataSource = new ComboPooledDataSource();
        ((ComboPooledDataSource) this.dataSource).setCheckoutTimeout(30);
    }
}

2.5 Plugin 示例

@Intercepts({@Signature(
  type= Executor.class,
  method = "update",
  args = {MappedStatement.class,Object.class})})
public class MyPlugin implements Interceptor {
      public Object intercept(Invocation invocation) throws Throwable {
          System.out.println("在这里，进行拦截、修改");
          return invocation.proceed();
      }
      public Object plugin(Object target) {
          return Plugin.wrap(target, this);
      }
      public void setProperties(Properties properties) {
          System.out.println(properties.getProperty("hello"));
      }
  }

3 映射文件 (EntityMapper.xml)

3.1 配置文件

示例 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.nf147.sss.dao.CommonDAO">
    <sql id="sql1"></sql>

    <resultMap id="rmAuthor" type="author">
        <!--<id property="id" column="id" />-->
        <!--<result property="name" column="name" />-->
        <result property="city" column="chengshi" />
    </resultMap>

    <select id="selectAll" resultMap="rmAuthor">
        select
          id,
          name,
          chengshi
        from sss_author
    </select>

    <resultMap id="rmBlog" type="blog">
        <!--基本的数据类型，使用 id/result-->

        <id property="id" column="id" />
        <result property="title" column="title" />
        <result property="content" column="content" />

        <!--
        <constructor>
            <idArg column="id" javaType="int" />
            <arg column="title" javaType="string" />
            <arg column="content" javaType="string" />
        </constructor>
        -->

        <!--association 表示的是 has One 的关系-->

        <!--第一种方式：nested select-->
        <!--<association property="author" javaType="author"-->
            <!--column="aid" select="com.nf147.sss.dao.CommonDAO.selectAuthorById" />-->

        <!--第二种方式，nested resultMap-->
        <!--
        <association property="author" javaType="author">
            <id property="id" column="author_id" />
            <result property="name" column="author_name" />
            <result property="city" column="author_city" />
        </association>
        -->

        <!--
        <association property="author" javaType="author" columnPrefix="author_">
            <id property="id" column="id" />
            <result property="name" column="name" />
            <result property="city" column="city" />
        </association>
        -->

        <association property="author" javaType="author" columnPrefix="author_" resultMap="a2" />


        <!--Collection 表示的是 Has Many 的关系-->
        <collection property="comments" ofType="comment" columnPrefix="comment_">
            <id property="id" column="id" />
            <result property="body" column="body" />
        </collection>
    </resultMap>

    <resultMap id="a2" type="author">
        <id property="id" column="id" />
        <result property="name" column="name" />
        <result property="city" column="city" />
    </resultMap>

    <!-- result type -->
    <select id="selectAuthorById" resultType="author">
        select id, name, city from sss_author where id=#{id}
    </select>
    <select id="selectCommentsOfBlog" resultType="comment">
        select id, body from sss_comment where blog_id = #{id}
    </select>

    <!-- result map -->
    <select id="selectBlogs" resultMap="rmBlog">
        select
          b.id         as id,
          b.title      as title,
          b.content    as content,
          a.id         as author_id,
          a.name       as author_name,
          a.city       as author_city,
          c.id         as comment_id,
          c.body       as comment_body
        from
          sss_blog b
          left join sss_author a on b.author_id = a.id
          left join sss_comment c on c.blog_id = b.id
    </select>

    <select id="selectBlogsNestedSelect" resultMap="rmBlogNestedSelect">
        select
          id, title, content, author_id
        from
          sss_blog
    </select>

    <resultMap id="rmBlogNestedSelect" type="blog">
        <id property="id" column="id" />
        <result property="title" column="title" />
        <result property="content" column="content" />
        <association property="author" javaType="author"
                     column="author_id" select="com.nf147.sss.dao.CommonDAO.selectAuthorById" />
        <collection property="comments" ofType="comment"
                    column="id" select="com.nf147.sss.dao.CommonDAO.selectCommentsOfBlog" />
    </resultMap>


    <!-- DML 语句 -->

    <!-- 主键策略 -->
    <insert id="insertAuthor" useGeneratedKeys="false" keyProperty="id" keyColumn="id">
        <selectKey keyProperty="id" resultType="int" order="BEFORE">
            select floor(rand()*1000)
        </selectKey>
        insert into sss_author (id, name, city) values (#{id}, #{name}, #{city})
    </insert>
    <update id="updateBlog">
        update sss_blog set content = #{content} where id = #{id}
    </update>
    <update id="updateBlog2" parameterType="blog" databaseId="oracle">
        update sss_blog set content = #{content} where id = #{id} and 1=1
    </update>
    <update id="updateBlog2" parameterType="blog" databaseId="mysql">
        update sss_blog set content = #{content} where id = #{id}
    </update>
    <update id="updateBlog3" statementType="CALLABLE">
        update sss_blog set content = #{content} where id = #{id}
    </update>
    <delete id="ddd" />
</mapper>

相关调用语句: 

CommonDAO mapper = session.getMapper(CommonDAO.class);
//        List<Author> authors = mapper.findAll(new RowBounds(1, 3));
//        authors.forEach(System.out::println);
//
//        List<Author> authors1 = mapper.findAllIdBiggerThan(3, new RowBounds(1, 2));
//        authors1.forEach(System.out::println);
//        System.out.println(mapper.findByNameAndCity("郭敬明' or 1=1 or name='xxx", "上海"));
//
//        mapper.delOne("'1' or 1=1");

//        int ret = mapper.delThree("update sss_blog set content='你不是苍蝇' ", 3);
//        session.commit();

//        Blog blog = new Blog();
//        blog.setId(3);
//        blog.setContent("我不知道苍蝇在哪里。");
//        System.out.println(mapper.updateBlog3(blog));

//        Author author = new Author();
//        author.setName("曹雪芹22");
//        author.setCity("北京222");
//        mapper.insertAuthor(author);
//
//        System.out.println(author.getId());
//
//        session.commit();

//        mapper.selectAll().forEach(System.out::println);

List<Blog> blogs = mapper.selectBlogs();
Blog blog = blogs.get(0);
Author author = blog.getAuthor();
List<Comment> comments = blog.getComments();
System.out.println(blog.getTitle());
System.out.println(author.getName());

System.out.println(comments);

3.2 sql

用于在指定位置插入代码片段，这可以达到代码片段复用的目的。

它的基本语法: 

<!-- 定义 -->
<!-- 其中可以用 ${} 语法表示传进来的值 -->
<sql id="名字">
  xxx, yyy, ${aaa}.zzz
</sql>

<!-- 使用 -->
<include refid="名字" />
<!-- 可以使用 property 表示传入的值 -->
<include refid="名字">
  <property name="aaa" value="i" />
</include>

这是一个示例: 

<!-- origin -->

<sql id="sometable">
  ${prefix}Table
</sql>

<sql id="someinclude">
  from
  <include refid="${include_target}"/>
</sql>

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

<!-- 站在 Mybatis 的角度，要解析上面的语句 -->
<!-- 首先，替换 someinclude -->

<select id="select" resultType="map">
  select
  field1, field2, field3

  <!-- <include refid="someinclude"> -->
  <!--   <property name="prefix" value="Some"/> -->
  <!--   <property name="include_target" value="sometable"/> -->
  <!-- </include> -->

  from
  <include refid="sometable"/>
</select>


<!-- 解析完的语句中，还存在 include，那么继续解析，替换 sometable -->

<select id="select" resultType="map">
  select
  field1, field2, field3

  <!-- <include refid="someinclude"> -->
  <!--   <property name="prefix" value="Some"/> -->
  <!--   <property name="include_target" value="sometable"/> -->
  <!-- </include> -->

  from
  <!-- <include refid="sometable"/ -->

  SomeTable
</select>

3.3 动态语句之 if

基本语法是: 

<if test="xxx != null">
  where id = #{xxx}
</if>

<!-- test=xxx 这里使用的是 OGNL 表达式的语法 -->
<!-- 上面的等价于 -->
<if test="_parameter.getXxx() != null">
  where id = #{xxx}
</if>

如果传入的参数是 int 等类型，使用 test= 语法会报 “getter 没有”的错误。 解决方式:

  1. 使用 Map 或 POJO 作为查询语句的入参
  2. 使用 @Param 的方式，注入参数
  3. 在 test=id 的地方，使用 test=_parameter 代替

3.4 动态语句之 trim/where/set

这些都是结合 if 语句使用，防止拼接多个语句的时候，产生多余的前缀、后缀，是一种辅助语法。

where 和 set 都可以用等价的 trim 来表示。

比如 where: 

<where>
    <!--<trim prefix="WHERE" prefixOverrides="AND |OR ">-->
    <if test="_parameter.firstName != null">
      and first_name = #{firstName}
    </if>
    <if test="city != null">
      and city = #{city}
    </if>
    <if test="address != null">
      and address like #{address}
    </if>
    <!--</trim>-->
</where>

它的逻辑是:

  1. 首先，按照条件判断，将所有符合条件的语句连接起来，比如: 

    and city = #{city} and address like #{address}

  2. 第二步，将句首(prefix)的 and 或者 or 删除掉: 

    city = #{city} and address like #{address}

  3. 最后，在语句前面，添加 where: 

    where city = #{city} and address like #{address}

于是我们看到，通过这种方式就可以动态组合成可以正常执行的 sql 语句了。

3.5 动态语句之 foreach

比如，动态组合成 in (xxx, yyy) 语句，那么这么写: 

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach
    collection="list" <!-- 表示传入的参数 -->
    item="item"       <!-- 代表集合里的每个元素 -->
    index="index"     <!-- 代表元素的序号 -->
    open="(" separator="," close=")"> <!-- 分别代表最后连接成的语句，前后用什么修饰，中间用什么拼接 -->
        #{item}
  </foreach>
</select>

对于 collection:

  • 如果定义为 selectPostIn(List<Integer> conds) 那么默认的名字为 list，如果入参类型是数组默认名字是 array，Map 类型的默认名字是 map
  • 如果定义为 selectPostIn(@Param("conds") List<Integer> conds) 那么名字为 conds
  • 如果定义为 selectPostIn(CondPojo conds) 那么 collection="xxx" 表示的是，从传入的 conds.getXxx() 的值

对照，批量插入的写法: 

<insert id="insertSome">
  insert into person (name, age) values
  <foreach collection="list" item="p" separator=",">
    (#{p.name}, #{p.age})
  </foreach>
</insert>

接口方法示例: 

int insertSome(List<Person> persons);

调用的方式: 

List<Person> persons = Arrays.asList(
    new Person("xxx", 22),
    new Person("yyy", 12),
    new Person("zzz", 18)
);

mapper.insertSome(persons);
session.commit();

可以测试下，最终生成的语句是不是我们所需要的。

4 常用 API

public static void main(String[] args) throws Exception {
    InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
    SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
    SqlSession session = factory.openSession();

    // selectOne
    // 重载有两个方法，第二个是条件参数
    Object o = session.selectOne("xxx.findById");
    Object o = session.selectOne("xxx.findById", 3);
    Object o = session.selectOne("xxx.findByName", "毛%");
    Object o = session.selectOne("xxx.findByNameAndCity", new HashMap<String, String>() {{
        put("name", "郭敬明");
        put("city", "上海");
    }});
    Object o = session.selectOne("xxx.findByNameAndCity", new Author("鲁迅", "浙江"));

    // selectList
    // 结果封装为 List
    // 重载有三个方法
    // 第三个参数，表示分页 (RowBounds)
    List<Object> lst = session.selectList("findAll");
    List<Object> lst = session.selectList("findAllLimit", 1);
    RowBounds rowBounds = new RowBounds(2, 3);
    List<Object> lst = session.selectList("findAll", null, rowBounds);
    lst.forEach(System.out::println);

    // 结果封装为 ResultMap
    Map<Object, Object> map = session.selectMap("findAll", null, "name", new RowBounds(0, 2));
    System.out.println(map);
    System.out.println(map.get("鲁迅"));

    // 结果封装为 Cursor，类似 ResultSet 遍历的语法
    Cursor<Object> cs = session.selectCursor("findAll");
    Iterator<Object> iterator = cs.iterator();
    while (iterator.hasNext()) {
        System.out.println(iterator.next());
    }

    // select，通过回调的方式，处理返回结果
    session.select("findAll", new ResultHandler() {
            @Override
            public void handleResult(ResultContext resultContext) {
                if (resultContext.getResultCount() % 2 == 0)
                    System.out.println(">>>> " + resultContext.getResultCount() + " : " + resultContext.getResultObject());
            }
        });

    // Mapper，更通用，更方便的方式
    CommonDAO mapper = session.getMapper(CommonDAO.class);
    List<Author> authors = mapper.findAll(new RowBounds(1, 3));
    authors.forEach(System.out::println);

    // 可以通过注解的方式传入多参数
    // 使用 RowBounds 类型的参数，表示分页
    List<Author> authors1 = mapper.findAllIdBiggerThan(3, new RowBounds(1, 2));
    authors1.forEach(System.out::println);
}

5 示例表

create table sss_author (
       id int auto_increment primary key,
       name varchar(30),
       city varchar(30));
create table sss_blog (
       id int auto_increment primary key,
       title varchar(30),
       content text,
       author_id int);
create table sss_comment (
       id int auto_increment primary key,
       body varchar(30),
       blog_id int,
       author_id int);

insert into sss_author (name, city) values
       ('郭敬明', '上海'),
       ('鲁迅', '浙江'),
       ('毛泽东', '湖南'),
       ('周立波', '上海'),
       ('周恩来', '浙江'),
       ('王阳明', '江西');

insert into sss_blog (title, content, author_id) values
       ('呐喊','醒醒吧，孩子', 2),
       ('朝花夕拾', '三味书屋', 2),
       ('而已集', '苍蝇就是苍蝇', 2),
       ('小时代', '悲伤尼禄看见看见框架', 1);

insert into sss_comment (body, blog_id, author_id) values
       ('这是一本好书，我是写不出来', 1, 1),
       ('很好，已阅。', 1, 2);

select * from sss_blog;
select * from sss_author;
select * from sss_comment;

6 任务指导手册 P58 答案参考

6.1 首先，在数据库中创建表，并插入测试数据:

create table customer(
    customer_id int auto_increment primary key,
    first_name varchar(30),
    last_name varchar(10),
    company varchar(30),
    address varchar(30),
    city varchar(20),
    state varchar(10),
    county varchar(10),
    postal_code varchar(30),
    phone varchar(20),
    fax varchar(20),
    email varchar(20),
    support_repId int
);
create table invoice(
    invoice_id int auto_increment primary key,
    customer_id int not null,
    invoice_date date,
    billing_address varchar(20),
    billing_city varchar(20),
    billing_state varchar(10),
    billing_country varchar(20),
    billing_postalCode varchar(20),
    total int
);

insert into customer (first_name,last_name,company,address,city,state ,county,postal_code,phone,fax,email,support_repId)
   values
   ('德华','刘','北影','001号街','香港','亚洲','中国','999077','13842344532','13842344532','4534123124@qq.com',0),
   ('富城','郭','北影','002号街','香港','亚洲','中国','999077','15072349132','15072349132','8937120154@qq.com',0),
   ('龙','成','北影','003号街','香港','亚洲','中国','999077','13389344576','13389344576','4534123124@qq.com',0);

insert into invoice (customer_id,invoice_date,billing_address,billing_city, billing_state,billing_country,billing_postalCode,total)
   values
   (1,'2018-10-13','001街','香港','亚洲','中国','999077',1);

6.2 其次，为表创建相应实体类:

  • 注意，处理好依赖关系
  • 注意命名规范
  • 要先确定好各种属性，再去生成 setters/getters

6.3 然后，创建接口。按照需求，我们要实现两个方法:

public interface InvoiceMapper {
    Invoice getInvoiceById(int id);
    Customer getCustomerById(int id);
}

6.4 为上述接口，写测试方法:

public class AppTest {
    static SqlSessionFactory factory;
    static SqlSession session;

    @Test
    public void testGetInvoiceById() {
        InvoiceMapper mapper = session.getMapper(InvoiceMapper.class);
        Invoice invoice = mapper.getInvoiceById(1);
        System.out.println(invoice);
    }

    @Test
    public void testGetCustomerById() {
        InvoiceMapper mapper = session.getMapper(InvoiceMapper.class);
        Customer customer = mapper.getCustomerById(1);
        System.out.println(customer.getInvoices());
        System.out.println(customer);
    }

    @BeforeClass
    public static void init () throws IOException {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        factory = new SqlSessionFactoryBuilder().build(in);
        session = factory.openSession();
    }

    @AfterClass
    public static void destroy() {
        session.close();
    }
}

然后，就可以写接口实现了(Mapper.xml)。

6.5 Mapper 的第一种方式，Nested ResultMap:

嵌套的 ResultMap。

优点:

  • xx
  • yy

缺点:

  • aa
  • bb
<resultMap id="rmInvoice" type="invoice" autoMapping="true">
    <association property="customer" resultMap="rmCustormer" />
    <association property="billingInfo" javaType="billingInfo" autoMapping="true" columnPrefix="billing_" />
</resultMap>

<resultMap id="rmCustormer" type="customer" autoMapping="true">
    <collection property="invoices" ofType="invoice" resultMap="rmInvoice" />
</resultMap>

<select id="getInvoiceById" resultMap="rmInvoice">
    select
      i.invoice_id,
      i.invoice_date,
      i.billing_address,
      i.billing_city,
      i.billing_state,
      i.billing_country,
      i.billing_postalCode,
      i.total,
      c.*
    from invoice i
     left join customer c on i.customer_id = c.customer_id
     where invoice_id = #{id}
</select>

<select id="getCustomerById" resultMap="rmCustormer">
    select
      c.*,
      i.invoice_id,
      i.invoice_date,
      i.billing_address,
      i.billing_city,
      i.billing_state,
      i.billing_country,
      i.billing_postalcode,
      i.total
    from customer c
    left join invoice i on i.customer_id = c.customer_id
    where c.customer_id = #{id}
</select>

6.6 Mapper 的第二种方式，Nested Select:

嵌套的 Select 查询。

优点:

  • xx
  • yy

缺点:

  • aa
  • bb
<resultMap id="rmInvoice" type="invoice" autoMapping="true">
    <association property="customer" column="customer_id" select="getCustomerById" />
    <association property="billingInfo" javaType="billingInfo" columnPrefix="billing_" autoMapping="true" />
</resultMap>

<resultMap id="rmCustomer" type="customer" autoMapping="true">
    <collection property="invoices" ofType="invoice"
                column="customer_id" select="getInvoiceByCustomerId" />
</resultMap>

<select id="getInvoiceById" resultMap="rmInvoice">
    select * from invoice
</select>

<select id="getCustomerById" resultMap="rmCustomer">
    select * from customer where customer_id = #{id}
</select>

<select id="getInvoiceByCustomerId" resultMap="rmInvoice">
    select * from invoice where customer_id = #{id}
</select>

7 缓存 (Cache)

缓存类型:

  • 一级缓存
  • 二级缓存
clip_2018-10-19_01-38-58.png

一级缓存是内置的，但是二级缓存需要手动开启。

一级缓存的范围可以通过参数设置: 

<setting name="localCacheScope" value="SESSION|STATEMENT" />

开启二级缓存，需要首先在 mbatis-config.xml 中配置: 

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

然后，在 mapper.xml 为本命名空间的语句开启缓存: 

<cache
  eviction="LRU|FIFO|SOFT|WEAK" <!-- 回收策略 -->
  flushInterval="60000" <!-- 刷新间隔 -->
  size="512"            <!-- 容量限制 -->
  readOnly="true"/>     <!-- 是否只读 -->

二级缓存也可以自己实现，Mybatis 提供了相应接口。

8 分页 (Pagination)

分页方式的分类:

  • 逻辑分页
  • 物理分页

分页的实现原理。

MyBatis-PageHelper 的使用:

配置: 

<!-- pom -->
<dependency>
  <groupId>com.github.pagehelper</groupId>
  <artifactId>pagehelper</artifactId>
  <version>5.1.7</version>
</dependency>

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

使用示例: 

// Builtin RowBounds
List<Country> list = mapper.selectAll(new RowBounds(1, 10));

// PageRowBounds
PageRowBounds pageRowBounds = new PageRowBounds(1, 10);
List<Country> list = mapper.selectAll(pageRowBounds);
assertEquals(182, pageRowBounds.total);

// PagerHelper
PageHelper.startPage(1, 10); // 1st page for 10
List<Country> list = mapper.selectIf(1); // the follow select will be pagered
assertEquals(182, ((Page) list).getTotal());

// PagerInfo
PageHelper.startPage(1, 10);
List<Country> list = mapper.selectAll();
PageInfo page = new PageInfo(list);
assertEquals(1,     page.getPageNum());
assertEquals(10,    page.getPageSize());
assertEquals(1,     page.getStartRow());
assertEquals(10,    page.getEndRow());
assertEquals(183,   page.getTotal());
assertEquals(19,    page.getPages());
assertEquals(1,     page.getFirstPage());
assertEquals(8,     page.getLastPage());
assertEquals(true,  page.isFirstPage());
assertEquals(false, page.isLastPage());
assertEquals(false, page.isHasPreviousPage());
assertEquals(true,  page.isHasNextPage());

9 MBG (MyBatis-Generator)

http://www.mybatis.org/generator/

9.1 Quick Start

首先，创建配置文件 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="xxx" targetRuntime="MyBatis3Simple">
    <commentGenerator>
      <property name="suppressDate" value="true" />
    </commentGenerator>

    <jdbcConnection driverClass="org.mariadb.jdbc.Driver"
                    connectionURL="jdbc:mariadb://localhost/test"
                    userId="vip" password="vip">
    </jdbcConnection>

    <javaModelGenerator targetPackage="test.model" targetProject="/home/vip/ddd111/src/main/java">
      <property name="trimStrings" value="true" />
    </javaModelGenerator>

    <sqlMapGenerator targetPackage="test.xml"  targetProject="/home/vip/ddd111/src/main/resources"/>

    <javaClientGenerator type="XMLMAPPER" targetPackage="test.dao"  targetProject="/home/vip/ddd111/src/main/java"/>

    <table tableName="ecs_%">
      <generatedKey column="id" sqlStatement="Mysql"/>
    </table>
  </context>
</generatorConfiguration>

然后，在 pom 中配置插件: 

<plugin>
  <groupId>org.mybatis.generator</groupId>
  <artifactId>mybatis-generator-maven-plugin</artifactId>
  <version>1.3.7</version>

  <configuration>
    <configurationFile>${basedir}/src/main/resources/generatorConfig.xml</configurationFile>
    <overwrite>true</overwrite>
  </configuration>

  <dependencies>
    <dependency>
      <groupId>org.mariadb.jdbc</groupId>
      <artifactId>mariadb-java-client</artifactId>
      <version>2.3.0</version>
    </dependency>
  </dependencies>
</plugin>

之后，执行 pom 任务即可: 

mvn mybatis-generator:generate
mvn mybatis-generator:generate -Dmybatis.generator.overwrite=true  # 可以传入参数

还可以使用其他参数:

  • configurationFile/contexts/outputDirectory
  • jdbcDriver/jdbcUrl/jdbcPassword/jdbcUserId
  • override/sqlScript/tableNames
  • verbose/includeCompileDependencies/includeAllDependencies

9.2 配置文件总览

<?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="xxx/yyy/zzz.properties" />
  <classPathEntry location="/Program Files/IBM/SQLLIB/java/db2java.jar" /> <!-- 添加到 classpath -->

  <context id="DB2Tables" introspectedColumnImpl="To Extend MBG"
           targetRuntime="MyBatis3|MyBatis3Simple|MyBatis3DynamicSql" <!-- MyBatis3Simple 没 Example 有少量动态 SQL -->
           defaultModelType="conditional|flat|hierarchical"> <!-- flat: one domain for one table -->

    <!-- 添加插件支持 -->
    <plugin type="org.mybatis.generator.plugins.SerializablePlugin"></plugin>
    <plugin type="com.nextlevel.fastlane.mybatis.plugins.SerializablePlugin"></plugin>
    <plugin type="org.mybatis.generator.plugins.RowBoundsPlugin"></plugin>
    <plugin type="org.mybatis.generator.plugins.EqualsHashCodePlugin" />

    <!-- 设置参数 -->
    <property name="suppressTypeWarnings" value="true"/>
    <property name="javaFileEncoding" value="UTF-8"/>
    <property name="javaFormatter" value="org.mybatis.generator.api.dom.DefaultJavaFormatter"/>
    <property name="xmlFormatter" value="org.mybatis.generator.api.dom.DefaultXmlFormatter"/>
    <property name="autoDelimitKeywords" value="false"/>
    <property name="beginningDelimiter" value="`"/>
    <property name="endingDelimiter" value="`"/>

    <!-- 数据库连接 -->
    <jdbcConnection  <!-- 也可以用 connectionFactory -->
                     driverClass="com.ibm.db2.jdbc.app.DB2Driver"
                     connectionURL="jdbc:db2:TEST"
                     userId="${user}"
                     password="${password}">
    </jdbcConnection>

    <!-- 类型转化规则 -->
    <javaTypeResolver type>
      <property name="forceBigDecimals" value="false" /> <!-- 是否使用 BigDecimal 类 -->
      <property name="useJSR310Types" value="true" /> <!-- 是否使用 LocalDate/LocalTime 类 -->
    </javaTypeResolver>

    <!-- 注释生成规则 -->
    <commentGenerator type>
      <property name="suppressDate" value="true" /> <!-- 取消时间戳 -->
      <property name="suppressAllComments|addRemarkComments|dateFormat" />
    </commentGenerator>

    <!-- Model生成规则 -->
    <javaModelGenerator targetPackage="test.model" targetProject="\MBGTestProject\src">
      <property name="enableSubPackages" value="true" /> <!-- true: will under test.model.schema.* -->
      <property name="trimStrings" value="true" />
      <property name="constructorBased|immutable|rootClass" />
    </javaModelGenerator>

    <!-- Mapper.xml 规则 -->
    <sqlMapGenerator targetPackage="test.xml" targetProject="\MBGTestProject\src">
      <property name="enableSubPackages" value="true" />
    </sqlMapGenerator>

    <!-- dao 规则 -->
    <javaClientGenerator type="XMLMAPPER|ANNOTATEDMAPPER" targetPackage="test.dao" targetProject="\MBGTestProject\src">
      <property name="enableSubPackages" value="true" />
    </javaClientGenerator>

    <!-- 定制要转化的 table -->
    <table schema="DB2ADMIN" tableName="ALLTYPES" domainObjectName="Customer"
           enableInsert|enableSelectByPrimaryKey|enableUpdateByPrimaryKey|enableDeleteByPrimaryKey|selectByPrimaryKeyQueryId
           enableDeleteByExample|enableCountByExample|enableUpdateByExample|selectByExampleQueryId|enableSelectByExample
           modelType|escapeWildcards|delimitIdentifiers|delimitAllColumns>

      <property name="useActualColumnNames" value="true"/> <!-- 驼峰 -->
      <property name="trimStrings|constructorBased|..." />

      <generatedKey column="ID" sqlStatement="JDBC|DB2|MySql|SqlServer" identity="true" /> <!-- 主键 -->

      <ignoreColumn column="FRED" /> <!-- 忽略 -->
      <ignoreColumnsByRegex pattern="(?i)col.*"> <!-- 按匹配忽略 -->
        <except column="col01"/>
        <except column="col13"/>
      </ignoreColumnsByRegex>

      <columnOverride column="DATE_FIELD" property="startDate" /> <!-- 定义转换 -->
      <columnOverride column="LONG_VARCHAR_FIELD" jdbcType="VARCHAR" /> <!-- 定义类型 -->
      <columnOverride column|property|javaType|jdbcType|typeHandler|delimitedColumnName|isGeneratedAlways>
        <property name="trimStrings" value="true" />
      </columnOverride>

      <columnRenamingRule searchString="^CUST_" replaceString="" /> <!-- 除去字段前缀 -->
      <domainObjectRenamingRule searchString="^Sys" replaceString="" /> <!-- 除去表前缀 -->
    </table>
  </context>
</generatorConfiguration>

Author: unname

Created: 2019-03-22 周五 01:32

Go ahead, never stop.