17.Mybites

一、快速入门
官网MyBatis

1. pom.xml:

    <dependencies>
        <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.32</version>
        </dependency>
        <!--   logback     -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.20</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.2.3</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>
    </dependencies>

2. resource下添加logback.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <!--
        CONSOLE ：表示当前的日志信息是可以输出到控制台的。
    -->
    <appender name="Console" class="ch.qos.logback.core.ConsoleAppender">
        <encoder>
            <pattern>[%level] %blue(%d{HH:mm:ss.SSS}) %cyan([%thread]) %boldGreen(%logger{15}) - %msg %n</pattern>
        </encoder>
    </appender>

    <logger name="com.itheima" level="DEBUG" additivity="false">
        <appender-ref ref="Console"/>
    </logger>


    <!--

      level:用来设置打印级别，大小写无关：TRACE, DEBUG, INFO, WARN, ERROR, ALL 和 OFF
     ， 默认debug
      <root>可以包含零个或多个<appender-ref>元素，标识这个输出位置将会被本日志级别控制。
      -->
    <root level="DEBUG">
        <appender-ref ref="Console"/>
    </root>
</configuration>

3. 再添加一个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>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <!--数据库连接信息-->
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///db02?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="123"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--加载sql的映射文件-->
        <mapper resource="AdminMapper.xml"/>
    </mappers>
<!--    <mappers>-->
<!--        &lt;!&ndash;包扫描&ndash;&gt;-->
<!--        <package name="com.ylf.mapper"/>-->
<!--    </mappers>-->
</configuration>

4. 添加一个sql映射文件AdminMapper.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="admin">
    <select id="selectAll" resultType="com.ylf.pojo.Admin">
        select * from Admin
    </select>
</mapper>

5. 最后测试：

public class Home {
  public static void main(String[] args) throws IOException {
    //1. 加载myBites的核心配置文件，获取sqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    //2. 获取sqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    //3. 执行sql
    User user = mapper.login(name, password);
    //4.释放资源
    sqlSession.close();
  }
}

二、mapper代理

1. 定义与sql映射文件同名的mapper接口，并且将mapper接口和sql映射文件放在同一个目录下：
   方法：在resources下创建于mapper接口一样的层级目录，把sql映射文件放进去，然后用maven编译就会发现在同一个目录下了
2. 设置sql映射文件的namespace属性为mapper接口全限定名

<mapper namespace="com.ylf.mapper.AdminMapper">

3. 在Mapper接口中定义方法，方法名就是sql映射文件中sql语句的id，并保持参数类型和返回值类型一致
4. 最后使用sqlSession去调用getMapper方法，传入mapper接口的类对象，获得代理，再调用对应方法

    final AdminMapper mapper = sqlSession.getMapper(AdminMapper.class);
    final List<Admin> admins = mapper.selectAll();

5. 如果使用了代理模式，在mybites配置文件中加载mapper可以使用扫描模式

    <mappers>
        <!--包扫描-->
        <package name="com.ylf.mapper"/>
    </mappers>

6. mybitesx插件，可以快速构建mybites框架

7. resultMap

<mapper namespace="com.ylf.mapper.BrandMapper">
    <resultMap id="brandResultMap" type="com.ylf.pojo.Brand">
        <result column="brand_name" property="brandName"/>
        <result column="company_name" property="companyName"/>
    </resultMap>
    <select id="selectAll" resultMap="brandResultMap">
        select *
        from tb_brand
    </select>
</mapper>

三、参数传递

1. 参数占位符 #{}
2. 多个参数如何传递
   散装参数：

  List<Brand> selectBySearch(
      @Param("status") int status,
      @Param("brandName") String brandName,
      @Param("companyName") String companyName);

实体类参数：

List<Brand> selectBySearch(Brand brand);

map集合

List<Brand> selectBySearch(Map map);

四、动态查询

1. 多条件查询

    <select id="selectBySearch" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <if test="status != null">and status = #{status}</if>
            <if test="brandName != null and brandName != ''">and brand_name like #{brandName}</if>
            <if test="companyName != null and companyName != ''">and company_name like #{companyName}</if>
        </where>
    </select>

2. 单条件查询

    <select id="selectBySingle" resultMap="brandResultMap">
        select *
        from tb_brand
        where
        <choose>
            <when test="status != null">status = #{status}</when>
            <when test="brandName != null and brandName != ''">brand_name like #{brandName}</when>
            <when test="companyName != null and companyName != ''">company_name like #{companyName}</when>
            <otherwise>1=1</otherwise>
        </choose>
    </select>

五、添加修改细节

1. 默认情况下mybites是开启了事务，openSession()是为false，不自动提交，需要执行sql后sqlSession.commit()去提交事务
2. 或者openSession()修改为openSession(true)设置成默认提交
3. 主键返回：

    <!--添加useGeneratedKeys="true" keyProperty="id"-->
    <insert id="add" useGeneratedKeys="true" keyProperty="id">
        insert into tb_brand
        values (null, #{brandName}, #{companyName}, #{ordered}, #{description}, #{status})
    </insert>

这样就能获取到keyProperty设置的值id了。

4. 动态修改

    <update id="update">
        update tb_brand
        <set>
            <if test="brandName != null and brandName != ''">brand_name = #{brandName},</if>
            <if test="companyName != null and companyName != ''">company_name = #{companyName},</if>
            <if test="ordered != null">ordered = #{ordered},</if>
            <if test="description != null and description != ''">description = #{description},</if>
            <if test="status != null">status = #{status}</if>
        </set>
        where id = #{id}
    </update>

六、批量删除

//BrandMapper
void deleteByIds(@Param("ids") int[] ids);

//BrandMapper.xml
<delete id="deleteByIds">
    delete
    from tb_brand
    where id in
    <foreach collection="ids" item="id" separator="," open="(" close=")">#{id}</foreach>
</delete>

七、注解开发

1. 注解开发只用于一些比较简单的操作，对于复杂的操作还是用配置xml开发

  @Select("select * from tb_brand where id = #{id}")
  Brand selectById(int id);

因为注解开发的话无法使用resultMap，所以注意列名不一致时需要使用as别名