Mybatis快速入门

目录结构

创建users表，添加数据

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL,
   `sex` varchar(2) DEFAULT NULL,
  `home_address` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
);

![](D:\Users\30638\Typora\图片\Snipaste_2024-06-04_16-41-55 - 副本.png)

导入依赖

在pom.xml文件中写入对应jar包的依赖

<dependencies>
    <!--mysql驱动-->
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
    <!--mybatis依赖-->
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.1</version>
    </dependency>

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

    <!--添加slf4j日志api-->
    <dependency>
        <groupId>org.slf4j</groupId>
        <artifactId>slf4j-api</artifactId>
        <version>1.7.30</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>
</dependencies>

导入logback.xml

导入logback.xml到resource目录下

配置打印日志

<?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.Carter_x" level="DEBUG" additivity="false">
        <appender-ref ref="Console"/>
    </logger>
</configuration>

编写核心配置文件

在resource目录下创建核心配置文件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>
    <typeAliases>
        <typeAlias type="com.zjf.utils.User" alias="User"/>//起别名,在xml文件中可简写resultType="User"
    </typeAliases>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>//对com.mysql.cj.jdbc.Driver特别说明，这是MySQL Connector/J 8.0及以后版本的驱动类名，相对于早期的com.mysql.jdbc.Driver（主要在5.x版本中使用）：
                <property name="url" value="jdbc:mysql:///batis?useSSL=false&amp;serverTimezone=UTC"/>//batis数据库名称，&amp; 代表&符号的意思
                <property name="username" value="root"/>
                <property name="password" value="1234"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
    <!--        引入接口文件-->
<!--        <mapper resource="com/zjf/mapper/UserMapper.xml"/>-->
        <package name="com.zjf.mapper"/>//两种方式，推荐第二种
    </mappers>
</configuration>

编写User实体类

package com.zjf.utils;

import java.util.Date;

public class User {
    private Integer id;
    private String username;
    private Date birthday;
    private String sex;
    private String address;

    public User() {
    }

    public User(Integer id, String username, Date birthday, String sex, String address) {
        this.id = id;
        this.username = username;
        this.birthday = birthday;
        this.sex = sex;
        this.address = address;
    }

    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 Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

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

查询所有

编写UserMapper接口

package com.zjf.mapper;

import com.zjf.utils.User;

import java.util.List;

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

}

编写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.zjf.mapper.UserMapper">

    <resultMap id="userResultMap" type="com.zjf.utils.User">
        <result column="home_address" property="address"/>
    </resultMap>//column表示数据库中的列名，property表示Java对象中的属性名。这个resultMap的作用是在查询结果集中将"home_address"列的数据映射到User对象的address属性中。

    <select id="selectAll" resultMap="userResultMap">
        select * from users
    </select>
    
</mapper>

数据库字段和实体类字段名不一致怎么处理

起别名(但是如果字段很多表很多就很复杂)

<select id="selectAll" resultType="User">
        select id,username,birthday,sex,home_address as homeAddress from users
    </select>

抽取sql片段(确定不灵活,不同的sql语句需要查询不同的字段)

<sql id="userSql">
       id,username,birthday,sex,home_address homeAddress
   </sql>
   <select id="selectAll" resultType="User">
       select
           <include refid="userSql" />
           from users
   </select>

使用resultMap

<resultMap id="userResultMap" type="user">
        <result column="home_address" property="homeAddress"/>
    </resultMap>
    
    <select id="selectAll" resultMap="userResultMap">
        select * from users
    </select>

编写测试代码

@Test
public void TestselectAll() throws IOException {
    //加载核心配置文件，获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);//通过Resources.getResourceAsStream(resource)方法加载配置文件，将其转换为输入流
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);//使用SqlSessionFactoryBuilder的build方法，传入输入流，构建SqlSessionFactory对象

    // 获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //执行sql语句
    //获取UserMapper接口的代理对象
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> users = mapper.selectAll();
    for (User user : users) {
        System.out.println(user);
    }

结果

查询单条数据

编写UserMapper接口

User selectOne(Integer id);

编写UserMapper.xml

<select id="selectOne" resultMap="userResultMap">
        select *  from users where  id = #{id};
</select>

编写测试代码

@Test
public  void TestselectOne() throws IOException {
    //加载核心配置文件，获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    // 获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //执行sql语句
    //获取UserMapper接口的代理对象
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user= mapper.selectOne(2);//id = 2的user
        System.out.println(user);

}

结果

多条件模糊查询

方法1.@Param注解实现

编写UserMapper接口

List<User> selectCondition(@Param("username") String username,@Param("address") String address);

@Param(“username”)指定了方法参数username在SQL语句中的参数名也是username。
@Param(“address”)指定了方法参数address在SQL语句中的参数名也是address

编写UserMapper.xml

<select id="selectCondition" resultMap="userResultMap">
    select * from users where username like concat("%",#{username},"%") and home_address like concat("%",#{address},"%")//模糊查询，通过concat函数将"%"+"{username}"+ "%"和"%"+"{address}"+ "%"拼接成完整的模糊匹配条件
</select>

这里的#{username}和#{address}就是SQL中的占位符，它们对应的值是Java方法调用时传入的@Param注解的参数。MyBatis会将这些参数值绑定到对应的占位符上，而实际的数据库字段名是username和home_address。

编写测试代码

@Test
public  void TestselectCondition() throws IOException {
    //加载核心配置文件，获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    // 获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //执行sql语句
    //获取UserMapper接口的代理对象
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    List<User> users= mapper.selectCondition("张", "重庆");
    for (User user : users) {
        System.out.println(user);
    }
}

方法2.创建对象实现

编写UserMapper接口

List<User> selectCondition(User user);

编写测试代码

@Test
public  void TestselectCondition() throws IOException {
    //加载核心配置文件，获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    // 获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //执行sql语句
    //获取UserMapper接口的代理对象
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    //创建User对象
    User user1 = new User();
    user1.setUsername("张");
    user1.setAddress("重庆");
    List<User> users= mapper.selectCondition(user1);
    for (User user : users) {
        System.out.println(user);
    }
}

方法3.map方式实现

编写UserMapper接口

List<User> selectCondition(Map map);

编写测试代码

@Test
public  void TestselectCondition() throws IOException {
    //加载核心配置文件，获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    // 获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();

    //执行sql语句
    //获取UserMapper接口的代理对象
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);

    Map map = new HashMap();
    map.put("username","张");
    map.put("address","重庆");
    List<User> users= mapper.selectCondition(map);
    for (User user : users) {
        System.out.println(user);
    }
}

结果

动态条件查询

在上面的条件查询中,存在一个问题,如果用户没有传入条件参数,获取传入了某一个几个条件参数,该怎么写sql语句,我们需要让映射文件中的sql语句根据条件参数是否传入生成

编写UserMapper.xml

<select id="selectCondition" resultMap="userResultMap">
    select * from users
    <where>
     <if test="username!=null and username!=''" >
    username like concat("%",#{username},"%")
     </if>
     <if test="address!=null and address!=''" >
     and home_address like concat("%",#{address},"%")
     </if>
    </where>
</select>

添加

编写UserMapper接口

Integer add(User user);

编写UserMapper.xml

<insert id="add" >
    insert into users(username,birthday,sex,home_address) values (#{username},#{birthday},#{sex},#{address})
</insert>

编写测试代码

@Test
public  void Testadd() throws IOException {
    //加载核心配置文件，获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    // 获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession();
    //      SqlSession sqlSession = sqlSessionFactory.openSession(true);设为true代表自动提交事务，则可省略sqlSession.commit()
  
    //获取UserMapper接口的代理对象
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user1 = new User();
    user1.setUsername("夏洛");
    user1.setAddress("上海");
    Integer num = mapper.add(user1);
        System.out.println(num);//num值代表数据库发生修改的行数
        sqlSession.commit();//注意提交事务
        sqlSession.close();
}

获取对象id

当您使用 <insert> 标签定义一个插入操作时，可以通过设置 useGeneratedKeys="true" 和 keyProperty="id" 来自动获取数据库自动生成的键值，通常是自增主键的id

<insert id="add" useGeneratedKeys="true" keyProperty="id" >
     insert into users(username,birthday,sex,home_address) values (#{username},#{birthday},#{sex},#{address})
 </insert>

添加测试代码

System.out.println(user1.getId());

结果

静态/动态修改

静态修改

编写UserMapper接口

Integer update(User user);

编写UserMapper.xml

<update id="update">
        update users set username=#{username},birthday=#{birthday},sex=#{sex},home_address=#{address} where id=#{id}
    </update>

编写测试代码

@Test
public  void Testupdate() throws IOException {
    //加载核心配置文件，获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    // 获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession(true);//这里设置为true，自动提交事务

    //执行sql语句
    //获取UserMapper接口的代理对象
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    User user1 = new User();
    user1.setId(1);
    user1.setUsername("袁华");
    Integer num = mapper.update(user1);
    System.out.println(num);
    sqlSession.close();
}

结果

对象中设置的属性username成功修改，未设置的属性为null

动态修改

编写UserMapper.xml

<update id="update">
 update Users
 <set>
 <if test="username!=null and username!=''">
        username=#{username}
  </if>
     <if test="birthday!=null and birthday!=''">
        birthday=#{birthday}
     </if>
 <if test="sex!=null and sex!='' ">
       sex = #{sex},
 </if>
 <if test="address!=null and address!='' ">
        home_address = #{address}
 </if>
 </set>
     where id = #{id};
    </update>

结果

只针对对象设置的属性进行修改，未设置的属性不会改变

删除/批量删除

删除

编写UserMapper接口

Integer delete(Integer id);

编写UserMapper.xml

<delete id="delete">
    delete from users where id=#{id}
</delete>

编写测试代码

@Test
public  void Testdelete() throws IOException {
    //加载核心配置文件，获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    // 获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    //执行sql语句
    //获取UserMapper接口的代理对象
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    Integer num = mapper.delete(2);
    System.out.println(num);
    sqlSession.close();
}

结果

批量删除

编写UserMapper接口

Integer deletes(@Param("ids") int[] ids);//@Param("ids")为方法参数名,SQL语句中应该使用的名字是",与UserMapper.xmlz中collection保持一致，若不添加@param注解默认参数名为array，添加可增加可读性

编写UserMapper.xml

<delete id="deletes">
        delete from users
        where id in
        <foreach collection="ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
</delete>

foreach----动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候），foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符

collection=“ids”：指定要遍历的集合，这里命名为ids。
item=“id”：指定遍历时每个元素的变量名，这里命名为id。
separator=“,”：指定遍历完成后各个元素之间的分隔符，这里使用逗号（,）作为分隔符。
open=“(”：指定遍历结果的起始符号，这里使用左括号（(）作为起始符号。
close=“)”：指定遍历结果的结束符号，这里使用右括号（)）作为结束符号。

编写测试代码

@Test
public  void Testdeletes() throws IOException {
    //加载核心配置文件，获取SqlSessionFactory
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

    // 获取SqlSession对象
    SqlSession sqlSession = sqlSessionFactory.openSession(true);

    //执行sql语句
    //获取UserMapper接口的代理对象
    UserMapper mapper = sqlSession.getMapper(UserMapper.class);
    int ids[] = {1,3};
    Integer num = mapper.deletes(ids);
    System.out.println(num);
    sqlSession.close();
}