mybatis配置及使用

简介

这是一篇关于mybatis的配置及使用的文章，目的帮你快速配置自己的mybatis。

Maven坐标

<dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.20</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.2.3</version>
        </dependency>
    </dependencies>

mybatis-config.xml

在 resources 目录下创建mybatis的配置文件

<?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>
        <package name="com.xxx.pojo"/>
    </typeAliases>

    <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:///mybatis?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>

        <environment id="test">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql:///mybatis?useSSL=false"/>
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>
    </environments>

    <mappers>
        <package name="com.xxx.mapper"/>
    </mappers>

</configuration>

创建同级目录mapper存放SQL映射文件（xml）

即在resource目录下新建directory 命名com/xxx/mapper

编写接口方法(interface)

在 com.xxx.mapper 包写创建名为 BrandMapper 的接口。并在该接口中定义 List selectAll() 方法。

public interface BrandMapper{ 
  List<Brand> selectAll(); 
}

编写SQL语句（查询）

在 reources 下创建 com/itheima/mapper 目录结构，并在该目录下创建名为 BrandMapper.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.xxx.mapper.BrandMapper">
<!--
       数据库表的字段名称  和  实体类的属性名称 不一样，则不能自动封装数据
           * resultMap：注意大小写
               1. 定义<resultMap>标签
               2. 在<select>标签中，使用resultMap属性替换 resultType属性
   -->
   <!--
       id：唯一标识
       type：映射的类型，支持别名
   -->
   <resultMap id="brandResultMap" type="Brand">
       <!--
           id：完成主键字段的映射
           result：完成一般字段的映射
               column：表的列名
               property：实体类的属性名
       -->
       <result column="brand_name" property="brandName"/>
       <result column="company_name" property="companyName"/>
   </resultMap>


   <select id="selectAll" resultMap="brandResultMap">
       select *
       from tb_brand;
   </select>
 
  若一样，不修改resultType
   <select id="selectAll" resultType="Brand">
       select *
       from tb_brand;
   </select>
   </mapper>

执行SQL语句

import com.xxx.mapper.BrandMapper;
import com.xxx.pojo.Brand;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;

public class mybatis1 {
    public static void main(String[] args) throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        //2. 获取SqlSession对象，用它来执行sql
        SqlSession sqlSession = sqlSessionFactory.openSession();
        //3. 执行sql
        //List<User> users = sqlSession.selectList("test.selectAll");
        //3.1 获取UserMapper接口的代理对象
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);
        List<Brand> users = brandMapper.selectAll();
        System.out.println(users);
        //4. 释放资源
        sqlSession.close();
    }
}

动态查询

多查询

  <!--
        动态条件查询
            * if: 条件判断
                * test：逻辑表达式
            * 问题：
                * 恒等式
                * <where> 替换 where 关键字
    -->
    <select id="selectByCondition" resultMap="brandResultMap">
        select *
        from tb_brand
        /* where 1 = 1*/
        <where>

            <if test="status != null">
                and status = #{status}
            </if>
            <if test="companyName != null and companyName != '' ">
                and company_name like #{companyName}
            </if>
            <if test="brandName != null and brandName != '' ">
                and brand_name like #{brandName}
            </if>
        </where>

    </select>

单查询

   <select id="selectByConditionSingle" resultMap="brandResultMap">
        select *
        from tb_brand
        <where>
            <choose><!--相当于switch-->
                <when test="status != null"><!--相当于case-->
                    status = #{status}
                </when>
                <when test="companyName != null and companyName != '' "><!--相当于case-->
                    company_name like #{companyName}
                </when>
                <when test="brandName != null and brandName != ''"><!--相当于case-->
                    brand_name like #{brandName}
                </when>

            </choose>
        </where>
    </select>

参数接收

 /**
     * 条件查询
     *  * 参数接收
     *      1. 散装参数：如果方法中有多个参数，需要使用@Param("SQL参数占位符名称")
     *      2. 对象参数:对象的属性名称要和参数占位符名称一致
     *      3. map集合参数
     *
     */

 1.     List<Brand> selectByCondition(@Param("status") int status, @Param("companyName") String companyName, @Param("brandName") String brandName);
 2.     List<Brand> selectByCondition(Brand brand);
 3.     List<Brand> selectByCondition(Map map);

MyBatis 参数封装

       //接收参数
        int status = 1;
        String companyName = "华为";
        String brandName = "华为";

        // 处理参数
        companyName = "%" + companyName + "%";
        brandName = "%" + brandName + "%";

        //封装对象
       /* Brand brand = new Brand();
        brand.setStatus(status);
        brand.setCompanyName(companyName);
        brand.setBrandName(brandName);*/

        Map map = new HashMap();
        // map.put("status" , status);
        map.put("companyName", companyName);
        // map.put("brandName" , brandName);

特殊字符处理

快捷:CD

select *
        from tb_brand
        where id
         <![CDATA[
            <
         ]]>
         #{id};

增删改注意事项

1.SqlSession sqlSession = sqlSessionFactory.openSession(true); //设置自动提交事务，这种情况不需要手动提交事务了
2.//手动提交事务
sqlSession.commit();

添加-主键返回

在 insert 标签上添加如下属性：
useGeneratedKeys：是够获取自动增长的主键值。true表示获取
keyProperty ：指定将获取到的主键值封装到哪儿个属性里

<insert id="add" useGeneratedKeys="true" keyProperty="id"> 
	insert into tb_brand (brand_name, company_name, ordered, description, status) 
	values (#{brandName}, #{companyName}, #{ordered}, #{description}, #{status}); 
</insert>

批量删除

接口方法，参数是一个数组，数组中存储的是多条数据的id
例：int[] ids = {1,2,3};

void deleteByIds(int[] ids);

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

工具类：SqlSessionFactoryUtils.java

util工具类

package com.itheima.util;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.InputStream;

public class SqlSessionFactoryUtils {

    private static SqlSessionFactory sqlSessionFactory;

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


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

logback.xml

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

    <logger name="com.xxx" 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>