Mybatis

MyBatis

MyBatis is a first class persistence framework with support for custom SQL, stored procedures and advanced mappings. MyBatis eliminates almost all of the JDBC code and manual setting of parameters and retrieval of results. MyBatis can use simple XML or Annotations for configuration and map primitives, Map interfaces and Java POJOs (Plain Old Java Objects) to database records.

mybatis

mybatis doc

tools

Hello World

• mybatis.jar & mysql-connector-java.jar

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>${mybatis.version}</version>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>${mysql.version}</version>
</dependency>

• MyBatisUtil.java

public class MyBatisUtil {
    private MyBatisUtil(){}
    private static SqlSessionFactory sqlSessionFactory = null;

    static {
        try {
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(
                    Resources.getResourceAsStream("mybatis-config.xml"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }
}

• mybatis-config.xml & jdbc.properties

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

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${driver}"/>
                <property name="url" value="${url}"/>
                <property name="username" value="${user}"/>
                <property name="password" value="${password}"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <mapper resource="com/ryo/mapper/UserMapper.xml"/>
    </mappers>
</configuration>

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://127.0.0.1:3306/mybatis?useUnicode=true&characterEncoding=utf8
user=root
password=

• UserMapper.java & UserMapper.xml

public interface UserMapper {
    User selectUser(Long 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.ryo.mapper.UserMapper">

    <!--定义所有列,方便使用,一般应该避免使用*-->
    <sql id="columns"> id,username,password,createdOn</sql>

    <!--id 对应方法名称-->
    <select id="selectUser" parameterType="java.lang.Long" resultType="com.ryo.domain.User">
        SELECT
        <include refid="columns"/>
        FROM User WHERE id = #{id}
    </select>
</mapper>

• sql & domain

CREATE TABLE user (
  id        BIGINT(20) PRIMARY KEY AUTO_INCREMENT NOT NULL
  COMMENT '主键, 自增',
  username  VARCHAR(64)                           NOT NULL
  COMMENT '用户名',
  password  VARCHAR(128)                          NOT NULL
  COMMENT '密码',
  createdOn DATETIME                              NOT NULL
  COMMENT '创建时间',

  UNIQUE INDEX `username_UNIQUE` (`username`)
)
  COMMENT '用户表';

INSERT INTO `user` (username, password, createdOn) VALUES (
  'ryo', '123456', '2016-07-28 14:32:30'
);

public class User implements Serializable {
    @Id
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Long id;
    private String username;
    private String password;
    private Date createdOn;

    //getter & setter

    //toString()
}

• UserMapperTest.java & result

public class UserMapperTest {
    private SqlSessionFactory sqlSessionFactory = MyBatisUtil.getSqlSessionFactory();

    @Test
    public void testSelectUser() throws IOException {
        UserMapper userMapper = sqlSessionFactory.openSession().getMapper(UserMapper.class);
        System.out.println(userMapper.selectUser(1L));
    }
}

User{id=1, username='ryo', password='123456', createdOn=Thu Jul 28 14:32:30 CST 2016}

Process finished with exit code 0

File strut

Tips

key has list

• *key.java

public class Key {

  private String username;

  private List<Integer> ids;

  //getter & setter
}

• *mapper.xml

SELECT * FROM tableName
WHERE username = #{username}
<if test="orderStatusList != null and orderStatusList.size > 0">
  AND id in
  <foreach item="item" index="index" collection="ids" open="(" separator="," close=")">
	#{item}
  </foreach>
</if>

Tips

If we have this case:

• StudentDto.java

public class StudentDto {
    private Long id;
    private String name;
}

We want to query student that matches (id=1 and name=”001”) OR (id=2 and name=”002”)…

• *Key.java

public class Key {
    private int age;
    private List<StudentDto> studentDtoList;
}

• *Mapper.xml

<select id="query" parameterType="Key" resultMap="BaseResultMap">
    SELECT * FROM table 
    WHERE age=#{age}
    <if test="studentDtoList != null and studentDtoList.size > 0">
        AND
        <foreach item="item" index="index" collection="studentDtoList" open="(" separator=" OR " close=")">
          (
          id=#{item.id,jdbcType=INTEGER} and
          name=#{item.name,jdbcType=CHAR}
          )
        </foreach>
    </if>
    
    
</select>

result has list;

• We have a classroom & student table simple like this

classroom(
id, name
);

student(
id, classroom_id, name
);

• the model of student is:

public class Student {
    private Long id;
    private Long classroomId;
    private String name;
}

• the result we want may like this:

public class Result {
    private Long id;  //classroom id;
    private String name; //classroom name
    private List<Student> studentList;
}

• the resultMap should be:

<resultMap id="Result" type="Result">
	<id column="id" property="id" jdbcType="BIGINT"/>
	<result column="name" property="name" jdbcType="CHAR"/>

	<collection property="studentList" ofType="Student">
		<id column="student_id" property="id" jdbcType="BIGINT"/>
		<result column="classroom_id" property="classroomId" jdbcType="BIGINT"/>
		<result column="student_name" property="name" jdbcType="INTEGER"/>
	</collection>
</resultMap>

• the query sql:

SELECT c.id, c.name,
	s.id AS  student_id,
	s.classroom_id AS classroom_id,
	s.name AS student_name
FROM classroom AS c
LEFT JOIN student ON c.id = s.classroom_id

Error

return null from a method with a primitive return type

You have two ways to solve this:

• Change the result type from primitive to object;

• Use IFNULL of MySQL, like this:

SELECT IFNULL(MAX(name),0) AS name FROM user WHERE id = #{id}

diff between #{field} and ${field}

You use order by #{field}/${field}

• #{field}

If set field=”create_time DESC”, the result is:

order by create_time DESC ASC

• ${field}

If set field=”create_time DESC”, the result is:

order by create_time ASC

Error

NoClassDefFoundError

java.lang.NoClassDefFoundError: org/apache/ibatis/cursor/Cursor

• why

jar 包版本冲突

• solve

注意 mybatis-spring.jar 对应的 mybatis.jar 和 spring-*.jar 版本。

多个入参

• UserMapper.java

package com.ryo.mybatis.demo.spring.mapper;

import com.ryo.mybatis.demo.spring.model.User;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

@Repository
public interface UserMapper extends MyMapper<User> {


    List<User> selectByMultiParamOne(String username, String password);

    /**
     * 推荐使用这一种方案
     * @param username  用户名
     * @param password  密码
     * @return
     */
    List<User> selectByMultiParamTwo(@Param("username") String username, @Param("password") String password);

    /**
     * 封装成一个 map 或者封装成一个对象，类似
     * @param map
     * @return
     */
    List<User> selectByMultiParamThree(Map<String, String> map);

}

• UserMapper.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.ryo.mybatis.demo.spring.mapper.UserMapper" >
  <resultMap id="BaseResultMap" type="com.ryo.mybatis.demo.spring.model.User" >
    <!--
      WARNING - @mbggenerated
    -->
    <id column="id" property="id" jdbcType="BIGINT" />
    <result column="username" property="username" jdbcType="VARCHAR" />
    <result column="password" property="password" jdbcType="VARCHAR" />
    <result column="created_time" property="createdTime" jdbcType="TIMESTAMP" />
    <result column="updated_time" property="updatedTime" jdbcType="TIMESTAMP" />
  </resultMap>

  <select id="selectByMultiParamOne" resultMap="BaseResultMap">
    SELECT id FROM user WHERE username=#{0} and password=#{1}
  </select>

  <select id="selectByMultiParamTwo" resultMap="BaseResultMap">
    SELECT id FROM user WHERE username=#{username} and password=#{password}
  </select>

  <select id="selectByMultiParamThree" resultMap="BaseResultMap">
    SELECT id FROM user WHERE username=#{username} and password=#{password}
  </select>

</mapper>

• UserMapperTest.java

@ContextConfiguration(locations = {"classpath:applicationContext-datasource.xml"})
@RunWith(SpringJUnit4ClassRunner.class)
public class UserMapperTest {

  @Autowired
  private UserMapper userMapper;

  @Test
  public void queryTest() {
    userMapper.selectAll();
  }

  //[User(id=1, username=null, password=null, createdTime=null, updatedTime=null)]
  @Test
  public void selectByMultiParamOneTest() {
    List<User> userList = userMapper.selectByMultiParamOne("ryo", "123456");
    System.out.println(userList);
  }

  @Test
  public void selectByMultiParamTwoTest() {
    List<User> userList = userMapper.selectByMultiParamTwo("ryo", "123456");
    System.out.println(userList);
  }

  @Test
  public void selectByMultiParamThreeTest() {
    Map<String, String> map = new HashMap<>();
    map.put("username", "ryo");
    map.put("password", "123456");
    List<User> userList = userMapper.selectByMultiParamThree(map);
    System.out.println(userList);
  }

}

查询游标

• 用于存储游标的 po

public class UserPo() {

    private int id;

    private String name;
}

数据库 db

• 存储过程

SP.selectUserList(id IN NUMNER(21))

假设有一段存储过程，返回上述类型的游标。

当然，是可以做映射的。(和表字段感觉类似)

Id NUMBER(21, 0)

Name VARCHAR(32)

• service 层使用

@Override
public List<UserPo> selectUserList(Long id) {
    Map<String, Object> map = new HashMap<>();
    map.put("id", id);
    userMapper.selectUserListCursor(map);
    Object object = map.get("result");
    return (List<UserPo>) map.get("result");
}

mapper

• UserMapper.java

void selectUserListCursor(Map<String, Object> map);

• UserMapper.xml

<resultMap id="selectUserListCursorMap"
               type="com.github.houbb.po.UserPo">
    <result column="Id" property="id"/>
    <result column="Name" property="name"/>
</resultMap>

<select id="selectUserListCursor" parameterType="java.util.Map" statementType="CALLABLE">
    {#{result, mode=OUT, jdbcType=CURSOR, resultMap=selectUserListCursorMap} = call SP.selectUserList(#{id,jdbcType=INTEGER,mode=IN})}
</select>

$ & #

${}

1、 $ 将传入的数据直接显示生成在sql中。

如：order by $user_id$，如果传入的值是 111, 那么解析成 sql 时的值为order by user_id, 如果传入的值是id，则解析成的sql为order by id.

2、 $ 方式一般用于传入数据库对象，例如传入表名.

3、 MyBatis 排序时使用 order by 动态参数时需要注意，用 $ 而不是 #

#{}

1、 # 将传入的数据都当成一个字符串，会对自动传入的数据加一个双引号。

如：order by #user_id#，如果传入的值是111,那么解析成sql时的值为order by “111”, 如果传入的值是id，则解析成的sql为order by “id”.

2、 可以防止 SQL 注入

3、 尽量使用这个方式

mybatis中的#{}和${}区别

resultType & resultMap

简单的不同点

说说不同点吧,resultType 和restltMap

restulyType:

1.对应的是java对象中的属性，大小写不敏感，

2.如果放的是java.lang.Map,key是查询语句的列名，value是查询的值，大小写敏感

3.resultMap:指的是定义好了的id的，是定义好的resyltType的引用

注意：用resultType的时候，要保证结果集的列名与java对象的属性相同，而resultMap则不用，而且resultMap可以用typeHander转换

4.

type:java 对象对应的类，

id:在本文件要唯一

column :数据库的列名或别名，

property:对应java对象的属性，

jdbcType:java.sql.Types

查询语句中，resultMap

属性指向上面那个属性的标签的id，parameterType:参数类型，只能传一个参数，如果有多个参数要封装，如封装成一个类，要写包名加类名，基本数据类型则可以省略

5.一对1、一对多时，若有表的字段相同必须写别名，不然查询结果无法正常映射，出现某属性为空或者返回的结果与想象中的不同，而这往往是没有报错的。

6.若有意外中的错误，反复检查以上几点，和认真核查自己的sql语句，mapper.xml文件是否配置正确。

另外还有resultMap 元素，它是 MyBatis 中最重要最强大的元素，它能提供级联查询，缓存等功能

mybatis中resultType和resultMap使用时的区别

二级缓存

正如大多数持久层框架一样，MyBatis 同样提供了一级缓存和二级缓存的支持

1. 一级缓存: 基于 PerpetualCache 的 HashMap 本地缓存，其存储作用域为 Session，当 Session flush 或 close 之后，该Session中的所有 Cache 就将清空。

2. 二级缓存与一级缓存其机制相同，默认也是采用 PerpetualCache，HashMap存储，不同在于其存储作用域为 Mapper(Namespace)，并且可自定义存储源，如 Ehcache。

3. 对于缓存数据更新机制，当某一个作用域(一级缓存Session/二级缓存Namespaces)的进行了 C/U/D 操作后，默认该作用域下所有 select 中的缓存将被clear。

二级缓存补充说明

1. 映射语句文件中的所有select语句将会被缓存。

2. 映射语句文件中的所有insert，update和delete语句会刷新缓存。

3. 缓存会使用Least Recently Used（LRU，最近最少使用的）算法来收回。

4. 缓存会根据指定的时间间隔来刷新。

5. 缓存会存储 1024 个对象

Mapper XML Files

MyBatis缓存介绍

mapper.java 和 mapper.xml 关联

Mapper接口与mapper.xml文件绑定

Mybatis配置和接口映射原理

• MyBatis
• Hello World
• Tips
  • key has list
  • result has list;
• Error
• 多个入参
• 查询游标
  • 数据库 db
  • mapper
• $ & #
  • ${}
  • #{}
• resultType & resultMap
  • 简单的不同点
  • 二级缓存
  • 二级缓存补充说明
• mapper.java 和 mapper.xml 关联