MyBatis 学习笔记

Mybatis是用于简化jdbc开发的框架

1 Jdbc与MyBatis的对比

1.1 jdbc更新操作示例

String url = "jdbc:mysql://127.0.0.1:3306/db1";
String userName = "root";
String pwd = "1234";
Class.forName("com.mysql.jdbc.Driver");

Connection connection;
connection = DriverManager.getConnection(url, userName, pwd);
String sql = "update account set money = 2000 where id = 1";
Statement statement = connection.createStatement();
int count = statement.executeUpdate(sql);
System.out.println(count);

statement.close();
connection.close();

1.2 mybatis查询操作示例

2.1 定义查询接口

public interface UserMapper {
    List<User> selectAll();
}

2.2 定义查询的配置文件

<?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.test.UserMapper">
    <select id = "selectAll" resultType = "com.test.User">
        select *
        from tb_user;
    </select>
</mapper>

2.3 执行查询操作

@Test
public void testSelect() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    List<User> userList = userMapper.selectAll();
    
    System.out.println("userList = " + userList);
    sqlSession.close();
}

2 MyBatis环境准备

2.1 创建Maven工程

选择Maven工程

2.2 安装Mybatis插件

插件效果，可以看到对应的logo

2.3 IDEA关联MySql

点加号，选择MySql

在弹出的页面中安装MySql驱动
然后输入用户名，密码，和对应的操作的数据库

最后点测试连接，看是否能连上

2.4 添加依赖

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.example</groupId>
    <artifactId>MyBatis-Demo</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>
    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>

        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.5</version>
        </dependency>

        <!-- 添加slf4j日志api -->
        <dependency>
            <groupId>org.slf4j</groupId>
            <artifactId>slf4j-api</artifactId>
            <version>1.7.20</version>
        </dependency>

        <!-- 添加logback-classic依赖 -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-classic</artifactId>
            <version>1.2.3</version>
        </dependency>

        <!-- 添加logback-core依赖 -->
        <dependency>
            <groupId>ch.qos.logback</groupId>
            <artifactId>logback-core</artifactId>
            <version>1.2.3</version>
        </dependency>

        <!--junit 单元测试-->
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13</version>
            <scope>test</scope>
        </dependency>

    </dependencies>

</project>

修改了依赖的配置文件后点一下同步，使依赖生效

2.5 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.test"/>
    </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>
        <!--加载sql映射文件-->
        <mapper resource="com.test/UserMapper.xml"/>
    </mappers>


</configuration>

3 Mapper代理开发

3.1 创建表并添加测试数据

-- 创建表
create table tb_user(
    id int primary key auto_increment,
    username varchar(20),
    password varchar(20),
    gender char(1),
    addr varchar(30)
);
-- 添加假数据
insert into tb_user values(1, '科比', '242424', '男', '洛杉矶');
insert into tb_user values(2, '韦德', '333', '女', '迈阿密');
insert into tb_user values(3, '威少', '000', '男', '鹅城');

3.2 接口方法

package com.test;

import java.util.List;

public interface UserMapper {
    List<User> selectAll();
}

User对应的数据类型

public class User {
    private Integer id;
    private String username;
    private String gender;
    private String addr;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public String getAddr() {
        return addr;
    }

    public void setAddr(String addr) {
        this.addr = addr;
    }

    @Override
    public String toString() {
        return "User{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", gender='" + gender + '\'' +
                ", addr='" + addr + '\'' +
                '}';
    }
}

3.3 SQL语句

<?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.test.UserMapper">
    <select id = "selectAll" resultType = "com.test.User">
        select *
        from tb_user;
    </select>
</mapper>

3.4 查询

public class UserMapperTest {
    @Test
    public void testSelect() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        
        SqlSession sqlSession = sqlSessionFactory.openSession();
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = userMapper.selectAll();
        
        System.out.println("userList = " + userList);
        sqlSession.close();
    }
}

执行前先启动MySql
输出结果

userList = [
User{id=1, username='科比', gender='男', addr='洛杉矶'}, 
User{id=2, username='韦德', gender='女', addr='迈阿密'}, 
User{id=3, username='威少', gender='男', addr='鹅城'}]

4 查询

创建表并添加测试数据

drop table if exists tb_brand;
use mybatis;

create table tb_brand
(
    id int primary key auto_increment,
    brand_name varchar(20),
    company_name varchar(20),
    ordered int,
    description varchar(100),
    status int
);

-- 添加数据
insert into tb_brand(brand_name, company_name, ordered, description, status)
values ('Oppo', 'Oppo有限公司', 20, '可以听音乐的手机', 10),
       ('华为', '华为有限公司', 80, '华为致力高端科技', 10),
       ('小米', '小米有限公司', 90, '让美好发生', 40);

在mybatis-config.xml中添加配置

<mappers>
	<mapper resource="com.test/BrandMapper.xml"/>
</mappers>

数据模型类

package com.test;

public class Brand {
    private Integer id;
    private String brandName;
    private String companyName;
    private Integer ordered;
    private String description;
    private Integer status;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getBrandName() {
        return brandName;
    }

    public void setBrandName(String brandName) {
        this.brandName = brandName;
    }

    public String getCompanyName() {
        return companyName;
    }

    public void setCompanyName(String companyName) {
        this.companyName = companyName;
    }

    public Integer getOrdered() {
        return ordered;
    }

    public void setOrdered(Integer ordered) {
        this.ordered = ordered;
    }

    public String getDescription() {
        return description;
    }

    public void setDescription(String description) {
        this.description = description;
    }

    public Integer getStatus() {
        return status;
    }

    public void setStatus(Integer status) {
        this.status = status;
    }

    @Override
    public String toString() {
        return "Brand{" +
                "id=" + id +
                ", brandName='" + brandName + '\'' +
                ", companyName='" + companyName + '\'' +
                ", ordered=" + ordered +
                ", description='" + description + '\'' +
                ", status=" + status +
                '}';
    }
}

4.1 普通查询

定义Mapper类

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

    Brand selectById(int 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.test.BrandMapper">
    <select id = "selectAll" resultType = "brand">
        select *
        from tb_brand;
    </select>

    <select id="selectById" parameterType="int" resultType="com.test.Brand">
        select * from tb_brand where id = #{id};
    </select>
</mapper>

此处的parameterType属性可以省略

测试

 @Test
    public void testSelectAllBrand() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

        List<Brand> brandList = brandMapper.selectAll();
        System.out.println("brandList = " + brandList);
        Brand brand = brandMapper.selectById(3);
        System.out.println("brand = " + brand);


        sqlSession.close();
    }

结果输出

brandList = [Brand{id=1, brandName='null', companyName='null', ordered=20, description='可以听音乐的手机', status=10}, Brand{id=2, brandName='null', companyName='null', ordered=80, description='华为致力高端科技', status=10}, Brand{id=3, brandName='null', companyName='null', ordered=90, description='让美好发生', status=40}]

brand = Brand{id=3, brandName='null', companyName='null', ordered=90, description='让美好发生', status=40}

4.2 起别名

上面有一部分字段没对应上，brandName和companyName

<select id="selectAll" resultType="brand">
    select
    id, brand_name as brandName, company_name as companyName, ordered, description, status
    from tb_brand;
</select>

再测试输出结果

brandList = [
Brand{id=1, brandName='Oppo', companyName='Oppo有限公司', ordered=20, description='可以听音乐的手机', status=10}, 
Brand{id=2, brandName='华为', companyName='华为有限公司', ordered=80, description='华为致力高端科技', status=10}, 
Brand{id=3, brandName='小米', companyName='小米有限公司', ordered=90, description='让美好发生', status=40}
]

4.3 ResultMap

<resultMap id="brandResultMap" type="brand">
    <result column="brand_name" property="brandName"></result>
    <result column="company_name" property="companyName"></result>
</resultMap>

<select id="selectAll" resultMap="brandResultMap">
    select * from tb_brand;
</select>

4.4 特殊字段处理

处理办法用 &lt; 替代 “<”

<select id="selectById" parameterType="int" resultMap="brandResultMap">
    select *
    from tb_brand
    where id &lt; #{id};
</select>

输出结果

brand = Brand{id=1, brandName='Oppo', companyName='Oppo有限公司', ordered=20, description='可以听音乐的手机', status=10}

4.5 多条件查询

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

List<Brand> selectByCondition(Map map);

List<Brand> selectByCondition(Brand map);

<select id="selectByCondition" resultMap="brandResultMap">
    select *
    from tb_brand
    where status = #{status}
    and company_name like #{companyName}
    and brand_name like #{brandName}
</select>

测试代码

@Test
public void testByCondition() throws Exception {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

    String companyName = "%" + "小米" + "%" ;
    String brandName = "%" + "小米" + "%" ;

    List<Brand> brandList = brandMapper.selectByCondition(1, companyName, brandName);
    System.out.println("first way brandList = " + brandList);

    Brand brand = new Brand();
    brand.setStatus(1);
    brand.setCompanyName(companyName);
    brand.setBrandName(brandName);
    brand.setStatus(1);
    brandList = brandMapper.selectByCondition(brand);
    System.out.println("second way brandList = " + brandList);

    Map map = new HashMap<>();
    map.put("status", 1);
    map.put("companyName", companyName);
    map.put("brandName", brandName);
    brandList = brandMapper.selectByCondition(map);

    System.out.println("third way brandList = " + brandList);
    sqlSession.close();
}

结果输出

first way brandList = [Brand{id=3, brandName='小米', companyName='小米有限公司', ordered=90, description='让美好发生', status=1}]
second way brandList = [Brand{id=3, brandName='小米', companyName='小米有限公司', ordered=90, description='让美好发生', status=1}]
third way brandList = [Brand{id=3, brandName='小米', companyName='小米有限公司', ordered=90, description='让美好发生', status=1}]

5 添加

添加Mapper方法

void add(Brand brand);

添加statement配置

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

添加测试方法

@Test
public void testAdd() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

    int status = 1;
    String companyName = "上汽大众有限公司";
    String brandName = "大众";
    String description = "舒服的开车体验";
    int ordered = 40;

    Brand brand = new Brand();
    brand.setStatus(status);
    brand.setCompanyName(companyName);
    brand.setBrandName(brandName);
    brand.setDescription(description);
    brand.setOrdered(ordered);

    brandMapper.add(brand);
    sqlSession.commit();
    sqlSession.close();
}

5.1 主键返回

测试代码修改

System.out.println("id = " + brand.getId());
brandMapper.add(brand);
System.out.println("id = " + brand.getId());

stattement修改

<insert id="addOrder" useGeneratedKeys="true" keyProperty="id">
    insert into tb_order(payment, payment)
</insert>

输出结果

id = null
18:17:52.752 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Opening JDBC Connection
18:17:52.985 [main] DEBUG org.apache.ibatis.datasource.pooled.PooledDataSource - Created connection 370869802.
18:17:52.985 [main] DEBUG org.apache.ibatis.transaction.jdbc.JdbcTransaction - Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@161b062a]
18:17:52.987 [main] DEBUG com.test.BrandMapper.add - ==>  Preparing: insert into tb_brand(brand_name, company_name, ordered, description, status) values (?, ?, ?, ?, ?);
18:17:53.023 [main] DEBUG com.test.BrandMapper.add - ==> Parameters: 大众(String), 上汽大众有限公司(String), 40(Integer), 舒服的开车体验(String), 1(Integer)
18:17:53.025 [main] DEBUG com.test.BrandMapper.add - <==    Updates: 1
id = 6

6 更新

mapper代码

int update(Brand brand);

statemen xml配置

<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="description != null and description != '' ">
           description = #{description},
       </if>
       <if test="status != null">
           status = #{status},
       </if>
   </set>
   where id = #{id}
</update>

测试代码

@Test
public void testUpdate() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

    int status = 1;
    String companyName = "奥迪有限公司";
    String brandName = "Audi";
    String description = "高端商务车";
    int ordered = 50;
    int id = 5;

    Brand brand = new Brand();
    brand.setStatus(status);
    brand.setCompanyName(companyName);
    brand.setBrandName(brandName);
    brand.setDescription(description);
    brand.setOrdered(ordered);
    brand.setId(id);

    System.out.println("id = " + brand.getId());
    int count  = brandMapper.update(brand);
    System.out.println("count = " + count);
    sqlSession.commit();
    sqlSession.close();
}

7 删除

7.1 删除单条数据

删除接口

void deleteById(int id);

statement

<delete id="deleteById">
    delete from tb_brand where id = #{id};
</delete>

测试

@Test
public void testDelete() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

    int id = 6;
    brandMapper.deleteById(id);

    sqlSession.commit();
    sqlSession.close();
}

7.2 删除多条数据

接口方法

void deleteByIds(int[] ids);

statement

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

测试代码

@Test
public void testDeleteMulti() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

    int[] ids = {6, 7, 8};
    brandMapper.deleteByIds(ids);

    sqlSession.commit();
    sqlSession.close();
}

8 注解完成增删改查

创建Mapper

public interface UserMapper {
	@Select("select * from tb_user where id = #{id}")
    User selectBy(int id);
}

测试代码

@Test
public void testSelectAnnotion() throws IOException {
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession sqlSession = sqlSessionFactory.openSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User user = userMapper.selectBy(2);
    System.out.println("user = " + user);
    sqlSession.close();
}

执行结果

user = User{id=2, username='韦德', gender='女', addr='迈阿密'}

除了@Select注解 还有

@Select
@Update
@Delete
@Insert

9 动态SQL

9.1 动态Sql

根据传值内容，动态拼接sql语句

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

        <if test="brandName != null and brandName != '' ">
            and brandName like  #{brandName}
        </if>
</select>

9.2 单个条件动态Sql

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

任意一个条件满足，就执行了
测试代码

@Test
    public void testByCondition() throws Exception {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession();
        BrandMapper brandMapper = sqlSession.getMapper(BrandMapper.class);

        String companyName = "%" + "小米" + "%" ;
        String brandName = "%" + "小米" + "%" ;

        List<Brand> brandList = brandMapper.selectByCondition(1, companyName, brandName);
        System.out.println("first way brandList = " + brandList);
        sqlSession.close();
    }

结果输出

17:34:24.273 [main] DEBUG com.test.BrandMapper.selectByCondition - ==>  Preparing: select * from tb_brand WHERE status = ?
17:34:24.303 [main] DEBUG com.test.BrandMapper.selectByCondition - ==> Parameters: 1(Integer)
17:34:24.315 [main] DEBUG com.test.BrandMapper.selectByCondition - <==      Total: 2

first way brandList = [Brand{id=1, brandName='Oppo', companyName='Oppo有限公司', ordered=20, description='可以听音乐的手机', status=1}, Brand{id=3, brandName='小米', companyName='小米有限公司', ordered=90, description='让美好发生', status=1}]