MyBatis 基础教程

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.15</version>
    </dependency>
    <dependency>
        <groupId>com.mysql</groupId>
        <artifactId>mysql-connector-j</artifactId>
        <version>8.2.0</version>
    </dependency>
</dependencies>

<?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.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/helpme"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/UserMapper.xml"/>
    </mappers>
</configuration>

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.InputStream;
import java.util.List;
import java.util.Map;

public class MyBatisDemo {

    public static void main(String[] args) throws Exception {
        InputStream in = Resources.getResourceAsStream("mybatis-config.xml");
        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
        try (SqlSession session = factory.openSession()) {
            List<Map<String, Object>> users = session.selectList("com.helpme.mapper.UserMapper.findAll");
            for (Map<String, Object> u : users) {
                System.out.println(u);
            }
        }
    }
}

public interface UserMapper {
    User findById(Long 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.helpme.mapper.UserMapper">

    <select id="findById" parameterType="long" resultType="com.helpme.entity.User">
        SELECT id, name, age FROM user WHERE id = #{id}
    </select>
</mapper>

<mapper namespace="com.helpme.mapper.UserMapper">

    <resultMap id="userMap" type="com.helpme.entity.User">
        <id column="id" property="id"/>
        <result column="user_name" property="userName"/>
        <result column="age" property="age"/>
    </resultMap>

    <select id="findById" resultMap="userMap">
        SELECT id, user_name, age FROM user WHERE id = #{id}
    </select>
</mapper>

<select id="findByCondition" resultType="com.helpme.entity.User">
    SELECT * FROM user
    WHERE 1 = 1
    <if test="name != null and name != ''">
        AND name = #{name}
    </if>
    <if test="age != null">
        AND age = #{age}
    </if>
</select>

<select id="findByCondition" resultType="com.helpme.entity.User">
    SELECT * FROM user
    <where>
        <if test="name != null and name != ''">
            AND name = #{name}
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
    </where>
</select>

<update id="updateUser">
    UPDATE user
    <set>
        <if test="name != null">name = #{name},</if>
        <if test="age != null">age = #{age},</if>
    </set>
    WHERE id = #{id}
</update>

<select id="findByIds" resultType="com.helpme.entity.User">
    SELECT * FROM user
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

import org.apache.ibatis.annotations.Param;
import java.util.List;

public interface UserMapper {
    List<User> findByNameAndAge(@Param("name") String name, @Param("age") int age);
}

import org.apache.ibatis.annotations.Param;
import java.util.Map;

public interface UserMapper {
    User findByMap(Map<String, Object> params);
}

<insert id="insertUser" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user(name, age) VALUES(#{name}, #{age})
</insert>

<resultMap id="orderMap" type="com.helpme.entity.Order">
    <id column="id" property="id"/>
    <result column="order_no" property="orderNo"/>
    <association property="user" javaType="com.helpme.entity.User">
        <id column="uid" property="id"/>
        <result column="user_name" property="name"/>
    </association>
</resultMap>

<select id="findOrderWithUser" resultMap="orderMap">
    SELECT o.id, o.order_no, u.id AS uid, u.user_name
    FROM orders o
    JOIN user u ON o.user_id = u.id
    WHERE o.id = #{id}
</select>

<resultMap id="userOrdersMap" type="com.helpme.entity.User">
    <id column="id" property="id"/>
    <result column="user_name" property="name"/>
    <collection property="orders" ofType="com.helpme.entity.Order">
        <id column="oid" property="id"/>
        <result column="order_no" property="orderNo"/>
    </collection>
</resultMap>

<select id="findUserWithOrders" resultMap="userOrdersMap">
    SELECT u.id, u.user_name, o.id AS oid, o.order_no
    FROM user u
    LEFT JOIN orders o ON o.user_id = u.id
    WHERE u.id = #{id}
</select>

<resultMap id="studentCoursesMap" type="com.helpme.entity.Student">
    <id column="id" property="id"/>
    <result column="stu_name" property="name"/>
    <collection property="courses" ofType="com.helpme.entity.Course">
        <id column="cid" property="id"/>
        <result column="course_name" property="name"/>
    </collection>
</resultMap>

<select id="findStudentWithCourses" resultMap="studentCoursesMap">
    SELECT s.id, s.stu_name, c.id AS cid, c.course_name
    FROM student s
    LEFT JOIN student_course sc ON sc.student_id = s.id
    LEFT JOIN course c ON c.id = sc.course_id
    WHERE s.id = #{id}
</select>

try (SqlSession session = factory.openSession()) {
    UserMapper mapper = session.getMapper(UserMapper.class);
    User u1 = mapper.findById(1L);
    User u2 = mapper.findById(1L);
    System.out.println("同一对象：" + (u1 == u2));
}

<mapper namespace="com.helpme.mapper.UserMapper">
    <cache eviction="LRU" flushInterval="60000" size="512" readOnly="true"/>
</mapper>

// 1. 执行增删改后缓存自动清空
// 2. 多表 JOIN 查询：某表更新时另一 namespace 缓存不失效
// 3. 分布式多实例：本地缓存无法共享，需 Redis 等外部缓存
// 4. 一级缓存与 SqlSession 生命周期绑定
public class CacheNote {
    // 以上为要点说明
}

import org.apache.ibatis.annotations.*;
import java.util.List;

public interface UserMapper {

    @Select("SELECT * FROM user WHERE id = #{id}")
    User findById(Long id);

    @Select("SELECT * FROM user")
    List<User> findAll();

    @Insert("INSERT INTO user(name, age) VALUES(#{name}, #{age})")
    int insert(User user);

    @Update("UPDATE user SET name=#{name}, age=#{age} WHERE id=#{id}")
    int update(User user);

    @Delete("DELETE FROM user WHERE id=#{id}")
    int delete(Long id);
}

import org.apache.ibatis.annotations.*;

public interface UserMapper {

    @Insert("INSERT INTO user(name, age) VALUES(#{name}, #{age})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(User user);
}

import org.apache.ibatis.annotations.SelectProvider;
import org.apache.ibatis.jdbc.SQL;
import java.util.List;

public interface UserMapper {

    @SelectProvider(type = UserSqlProvider.class, method = "findByCondition")
    List<User> findByCondition(String name, Integer age);

    class UserSqlProvider {
        public String findByCondition(String name, Integer age) {
            return new SQL() {{
                SELECT("*");
                FROM("user");
                if (name != null && !name.isEmpty()) {
                    WHERE("name = #{name}");
                }
                if (age != null) {
                    WHERE("age = #{age}");
                }
            }}.toString();
        }
    }
}

<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.5.5</version>
</dependency>

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

@Mapper
public interface UserMapper extends BaseMapper<User> {
    // 继承通用 CRUD，无需手写 SQL
}

import com.baomidou.mybatisplus.extension.service.IService;

public interface UserService extends IService<User> {
}

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import java.util.List;

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    public List<User> search(String name, Integer minAge) {
        QueryWrapper<User> wrapper = new QueryWrapper<>();
        wrapper.like("name", name)
               .gt("age", minAge)
               .orderByDesc("age");
        return list(wrapper);
    }
}

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import java.util.List;

public List<User> findByDept(String dept) {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(User::getDepartment, dept)
           .ge(User::getAge, 20);
    return list(wrapper);
}

import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;

public boolean updateAgeByName(String name, int newAge) {
    UpdateWrapper<User> wrapper = new UpdateWrapper<>();
    wrapper.eq("name", name).set("age", newAge);
    return update(wrapper);
}

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
}

import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import java.util.List;

public Page<User> pageUsers(int current, int size) {
    Page<User> page = new Page<>(current, size);
    Page<User> result = page(page, null);
    System.out.println("总数：" + result.getTotal());
    return result;
}

import com.baomidou.mybatisplus.annotation.TableLogic;

public class User {

    private Long id;
    private String name;

    @TableLogic
    private Integer deleted;
}

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;

public class User {

    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
}

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;
import java.util.List;

@Mapper
public interface UserMapper extends BaseMapper<User> {

    @Select("SELECT u.*, o.order_no FROM user u " +
            "LEFT JOIN orders o ON o.user_id = u.id WHERE u.id = #{id}")
    User selectUserWithOrder(Long id);
}

import com.baomidou.mybatisplus.generator.FastAutoGenerator;

public class GeneratorDemo {

    public static void main(String[] args) {
        FastAutoGenerator.create("jdbc:mysql://localhost:3306/helpme", "root", "123456")
                .globalConfig(builder -> builder.author("Helpme").outputDir("/tmp/gen"))
                .packageConfig(builder -> builder.parent("com.helpme"))
                .strategyConfig(builder -> builder.addInclude("user", "orders"))
                .execute();
    }
}

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;

@TableName("book")
public class Book {

    @TableId(type = IdType.AUTO)
    private Long id;
    private String title;
    private String author;
    private Double price;

    // getter / setter
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }
    public String getAuthor() { return author; }
    public void setAuthor(String author) { this.author = author; }
    public Double getPrice() { return price; }
    public void setPrice(Double price) { this.price = price; }
}

import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

public Page<Book> searchBooks(int current, int size, String keyword) {
    LambdaQueryWrapper<Book> wrapper = new LambdaQueryWrapper<>();
    wrapper.like(keyword != null && !keyword.isEmpty(), Book::getTitle, keyword)
           .orderByDesc(Book::getId);
    return bookMapper.selectPage(new Page<>(current, size), wrapper);
}

import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class BookService {

    private final BookMapper bookMapper;
    private final StockMapper stockMapper;

    public BookService(BookMapper bookMapper, StockMapper stockMapper) {
        this.bookMapper = bookMapper;
        this.stockMapper = stockMapper;
    }

    @Transactional
    public void deleteBook(Long bookId) {
        bookMapper.deleteById(bookId);
        stockMapper.deleteByBookId(bookId);
    }
}