MyBatis是属于持久层（DAO层）的框架，封装了JDBC的很多操作细节，让开发者大大简化了DAO层的代码。于2010年从iBatis改名为MyBatis

数据库事务（Database Transaction）

事务的四大特性（ACID）

JDBC的事务管理

在JDBC中，使用Connection对象来管理事务

setAutoCommit(false):开启事务
rollback(): 回滚事务
commit(): 提交事务

MyBatis -- 依赖

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.5</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>

核心配置

创建核心配置文件（一般叫mybatis-config.xml）

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

<!--     其他设置  -->
    <settings>
<!--                数据库my_first_name => myFirstMame-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

    <!-- 环境 -->
    <environments default="development">
        <!-- 开发环境（调试阶段） -->
        <environment id="development">
            <!-- 采用JDBC的事务管理方法 -->
            <transactionManager type="JDBC" />

            <!-- POOLED代表采取连接池的方式管理连接 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/sxl1"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>

        <!-- 生产环境（发布阶段） -->
        <environment id="production">
            <!-- 采用JDBC的事务管理方法 -->
            <transactionManager type="JDBC" />

            <!-- POOLED代表采取连接池的方式管理连接 -->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/sxl1"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>

    <!-- 映射文件 -->
    <mappers>
        <mapper resource="mappers/skill.xml" />
<!--        <mapper resource="mappers/experience.xml" />-->
    </mappers>



</configuration>

注意驼峰命名配置

创建Session

public class MyBatises {
    private static SqlSessionFactory sessionFactory;
    static {
        try (Reader reader = Resources.getResourceAsReader("mybatis-config.xml")) {
            sessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static SqlSession openSession(boolean autoCommit) { return  sessionFactory.openSession(autoCommit);};
    public static SqlSession openSession () { return sessionFactory.openSession();};
}

SqlSessionFactoryBuilder

这个类可以被实例化、使用和丢弃，一旦创建了 SqlSessionFactory，就不再需要它了。

SqlSessionFactory

SqlSessionFactory 一旦被创建就应该在应用的运行期间一直存在，没有任何理由丢弃它或重新创建另一个实例。

SqlSession

每个线程都应该有它自己的 SqlSession 实例。

实体映射

新建实体映射的配置文件(可以放在mappers文件夹下，比如mappers/skill.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="skill">
    <select id="list" resultType="com.scc.bean.Skill">
        SELECT * FROM skill
    </select>
</mapper>

添加实体映射文件的路径(mybatis-config.xml文件)

    <!-- 映射文件 -->
    <mappers>
        <mapper resource="mappers/skill.xml" />
    </mappers>

开启驼峰命名自动映射，比如从数据库列名my_age映射到java属性名myAge(mybatis-config.xml文件)

<!--     其他设置  -->
    <settings>
<!--                数据库my_first_name => myFirstMame-->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
    </settings>

查询

skill.xml

    <select id="list" resultType="com.scc.bean.Skill">
        SELECT * FROM skill
    </select>

try (SqlSession sqlSession = MyBatises.openSession()) {
   List<Skill> skills = sqlSession.selectList("skill.list");
    for (Skill skill: skills) {
        System.out.println(skill);
    }
}

session需要关闭。关闭session 不是关闭连接 mybatis采用数据连接池不需要我们手动关闭

字段映射

如果bean字段和数据库列名不一致，或者不在mybatis-config.xml里设置驼峰，在skill.xml里字段映射。

    <!--        一样的可以不写-->
    <resultMap id="reSkill" type="com.scc.bean.Skill">
<!--        主键可以用id标签-->
<!--        <id property="id" column="id" />-->
<!--        <result property="id" column="id" />-->
        <result property="createdTime" column="created_time" />
<!--        <result property="name" column="name" />-->
<!--        <result property="level" column="level" />-->
    </resultMap> 

    <select id="list" resultMap="reSkill">
        SELECT * FROM skill
    </select>

主键可以用id标签
一样的可以不写

传参

单个传参

<select id="get" parameterType="int" resultType="com.scc.bean.Skill">
    SELECT * FROM skill WHERE  id = #{id}
</select>

try (SqlSession sqlSession = MyBatises.openSession()) {
    Skill skill = sqlSession.selectOne("skill.get", 12);
    System.out.println(skill);
}

多个传参

<!--    parameterType可省略-->
<select id="list2" resultType="com.scc.bean.Skill" parameterType="com.scc.bean.Skill">
    SELECT * FROM skill WHERE  name LIKE #{name}  AND level > #{level}
</select>

        try (SqlSession sqlSession = MyBatises.openSession()) {
//            Map<String, Object> param = new HashMap<>();
//            param.put("name","%M%");
//            param.put("level",1);
            Skill param = new Skill();
            param.setName("%i%");
            param.setLevel(3);
            List<Skill> skills = sqlSession.selectList("skill.list2", param);
            for (Skill skill: skills) {
                System.out.println(skill);
            }

        }

打印日志，执行的sql为：

21:50:39.914 [main] DEBUG skill.list2 - ==>  Preparing: SELECT * FROM skill WHERE name LIKE ? AND level > ?

或用 $

<!--    parameterType可省略-->
<select id="list2" resultType="com.scc.bean.Skill" parameterType="com.scc.bean.Skill">
    SELECT * FROM skill WHERE  name LIKE "%${name}%"  AND level > #{level}
</select>

 try (SqlSession sqlSession = MyBatises.openSession()) {
//            Map<String, Object> param = new HashMap<>();
//            param.put("name","%M%");
//            param.put("level",1);
            Skill param = new Skill();
            param.setName("i");
            param.setLevel(3);
            List<Skill> skills = sqlSession.selectList("skill.list2", param);
            for (Skill skill: skills) {
                System.out.println(skill);
            }

        }

打印的日志，执行的sql为：

21:48:42.767 [main] DEBUG skill.list2 - ==>  Preparing: SELECT * FROM skill WHERE name LIKE "%i%" AND level > ?

二者区别

打印sql语句

多表关联查询 resultType

方法一

<resultMap id="rmExperience" type="com.scc.bean.Experience">
   <id property="id" column="id" />
   <result property="company.id" column="t2_id"/>
   <result property="company.name" column="t2_name"/>
</resultMap>

<select id="list" resultMap="rmExperience">
   SELECT
      t1.*,
      t2.id t2_id,
      t2.name t2_name
   FROM
      experience t1
         JOIN company t2 ON t1.company_id = t2.id
</select>

属性名和数据库列名一致的，可以省略不写。但是主键用id标签比result标签，效率更高。MyBatis会做优化。

@Test
public void select() throws Exception{

    try (SqlSession sqlSession = MyBatises.openSession()) {
        List<Experience> experiences = sqlSession.selectList("experience.list");
        for (Experience experience: experiences) {
            System.out.println(experience.getCompany().getName());
        }
    }
}

方法二

<select id="list" resultType="com.scc.bean.Experience">
   SELECT
      t1.*,
      t2.id `company.id`,
      t2.name `company.name`
   FROM
      experience t1
         JOIN company t2 ON t1.company_id = t2.id
</select>

注意与方法一sql的区别。

将resultType设置为Map、HashMap、LinkedHashMap
将resultType设置为JavaBean

添加

主键设置

<!--keyProperty是bean对象的属性，不是数据库的列名-->
    <insert id="insert" parameterType="com.scc.bean.Skill">
        INSERT INTO skill(name, level) VALUES(#{name}, #{level })
        <selectKey resultType="int" keyProperty="id" order="AFTER">
            SELECT LAST_INSERT_ID()
        </selectKey>
    </insert>

@Test
public void insert() {
    try (SqlSession session = MyBatises.openSession()){
        Skill skill = new Skill();
        skill.setName("JS");
        skill.setLevel(8);
        session.insert("skill.insert", skill);
        System.out.println(skill);
        session.commit();
    }
}

keyProperty是bean对象的属性，不是数据库的列名
第一种方法selectKey这种方法会执行两句sql.控制台打印：

第二种方法只执行一句sql.

更新

<update id="update" parameterType="com.scc.bean.Skill">
    UPDATE skill SET name = #{name}, level = #{level} WHERE id = #{id}
</update>

@Test
public void update() {
    try (SqlSession session = MyBatises.openSession()){
        Skill skill = new Skill("前端", 18);
        skill.setId(18);
        session.update("skill.update", skill);
        session.commit();
    }
}

删除

<delete id="delete" parameterType="int">
    DELETE FROM skill WHERE id = #{id}
</delete>

@Test
public void delete() {
    try (SqlSession session = MyBatises.openSession()){
        session.delete("skill.delete", 17);
        session.commit();
    }
}

动态SQL

@Test
public void dynamicSQL() {
    try (SqlSession session = MyBatises.openSession()) {
        Map<String, Object> param = new HashMap<>();
        param.put("id", 10);
        param.put("name", "%i%");
        param.put("level", 10);
        List<Skill> skills = session.selectList("skill.dynamicSQL2", param);
        for (Skill skill : skills) {
            System.out.println(skill);
        }
    }
}

if标签

<select id="dynamicSQL" parameterType="Map" resultType="com.scc.bean.Skill">
    SELECT * FROM skill WHERE 1 = 1
    <if test="id != null">
        AND id > #{id}
    </if>
    <if test="name != null">
        AND name LIKE #{name}
    </if>
    <if test="level != null">
        AND level &lt; #{level}
    </if>

</select>

或增加where标签

<select id="dynamicSQL2" parameterType="Map" resultType="com.scc.bean.Skill">
    SELECT * FROM skill
    <where>
        <if test="id != null">
            AND id > #{id}
        </if>
        <if test="name != null">
            AND name LIKE #{name}
        </if>
        <if test="level != null">
            AND level &lt; #{level}
        </if>
    </where>
</select>

注意：小于无法使用<，因为和标签的开始符号一样，必须用 字符实体:<, 大于号可以。

二者控制台打印的SQL

 Preparing: SELECT * FROM skill WHERE 1 = 1 AND id > ? AND name LIKE ? AND level < ?


 Preparing: SELECT * FROM skill WHERE id > ? AND name LIKE ? AND level < ?

用where标签，可以省略 WHERE 1 = 1；

更多标签

批量处理

批量添加

<insert id="batchInsert" parameterType="List" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO skill(name , level) VALUES
    <foreach collection="list" item="skill" separator=",">
        (#{skill.name}, #{skill.level})
    </foreach>
</insert>

@Test
public void batchAdd() {
    try (SqlSession session = MyBatises.openSession()){
        List<Skill> skills = new ArrayList<>();
        skills.add(new Skill("java1", 120));
        skills.add(new Skill("java2", 130));
        skills.add(new Skill("java3", 140));
        skills.add(new Skill("java4", 150));
        skills.add(new Skill("java5", 160));
        session.insert("skill.batchInsert", skills);
        for (Skill skill : skills) {
            System.out.println(skill.getId());
        }
        session.commit();
    }
}

批量删除

<delete id="batchDelete" parameterType="List">
    DELETE FROM skill WHERE id IN
    <foreach collection="list" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</delete>

或

<delete id="batchDelete" parameterType="List">
    DELETE FROM skill WHERE id IN (
    <foreach collection="list" item="id" separator=",">
        #{id}
    </foreach>
    )
</delete>

@Test
public void batchDelete() {
    try (SqlSession session = MyBatises.openSession()) {
        List<Integer> ids = new ArrayList<>();
        ids.add(24);
        ids.add(25);
        ids.add(26);
        ids.add(27);
        ids.add(28);
        session.delete("skill.batchDelete", ids);
        session.commit();
    }
}

其他标签

typeAliases

添加到mybatis-config.xml的configuration标签中
用于设置类型的别名（不区分大小写）

参考

<typeAliases>
    <!-- 一旦设置了别名，它是不区分大小写的 -->
    <typeAlias type="com.scc.bean.Skill" alias="skill" />
    <!-- 这个包下的所有类，都会起一个别名：短类名（全类名的最后一个单词） -->
    <package name="com.scc.bean"/>
</typeAliases>

package: 这个包下的所有类，都会起一个别名：短类名（全类名的最后一个单词）

之前的查询可改写为

<select id="list2" resultType="sKilL" parameterType="SKiLl">
    SELECT * FROM skill WHERE  name LIKE #{name}  AND level > #{level}
</select>

一般配置文件里type都是别名，这些都是内置的别名，比如mybatis-config.xml里的

<transactionManager type="JDBC" />

<!-- POOLED代表采取连接池的方式管理连接 -->
<dataSource type="DRUID">

这里JDBC、DRUID 大写小都可以，但是规范点还是大写。

parameterType="Map" 
parameterType="int"

大小写都可以，但是除了int等，其他的规范点要遵循大驼峰,比如String,List,Map

sql

添加到mapper标签中，用于抽取公共的SQL语句

笔记-MyBatis(一)：基础