一、整合 MyBatisPlus

1. 导入依赖

   com.baomidou
   mybatis-plus-boot-starter
   3.5.1

2. 配置文件

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://127.0.0.1:3306/demo?useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
mybatis-plus:
  # 指定 Mapper XML 文件的位置，使用 classpath 通配符指定路径
  mapper-locations: classpath:/mapper/**/*.xml
  # 指定实体类的包路径，用于自动扫描并注册类型别名
  type-aliases-package: com.cnbai.bean
  global-config:
    db-config:
      id-type: input
      # 驼峰下划线转换（将数据库字段的下划线命名规则转换为 Java 实体类属性的驼峰命名规则）
      db-column-underline: true
      # 刷新 mapper
      refresh-mapper: true
  configuration:
    # 将 Java 实体类属性的驼峰命名规则转换为数据库字段的下划线命名规则
    map-underscore-to-camel-case: true
    # 查询结果中包含空值的列，在映射的时候，不会映射这个字段
    call-setters-on-nulls: true
    # 开启 sql 日志
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    # 关闭 sql 日志
    # log-impl: org.apache.ibatis.logging.nologging.NoLoggingImpl

3. 配置类

import com.baomidou.mybatisplus.annotation.DbType;
import com.baomidou.mybatisplus.autoconfigure.MybatisPlusProperties;
import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;
import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
@Configuration
public class MybatisPlusConfig {
    /**
     * 加载分页插件
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }
    @Primary
    @Bean
    @ConfigurationProperties("mybatis-plus")
    public MybatisPlusProperties mybatisPlusProperties() {
        return new MybatisPlusProperties();
    }
}

4. 业务类

public interface UserService extends IService {}
@Service
public class UserServiceImpl extends ServiceImpl implements UserService {}
@Mapper
public interface UserDao extends BaseMapper {}

5. 实体类

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
@TableName(value = "t_user")
public class User implements Serializable {
    @TableId(value = "id", type = IdType.INPUT)
    private Integer id;
    
    @TableField(value = "name")
    private String name;
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
}

二、查询

1. 普通查询

public List queryUserList(String name) {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>();
    wrapper.eq(User::getName, name);
    return list(wrapper);
}
public User queryUserById(String id) {
    return getBaseMapper().selectById(id);
}

2. 拼接查询

 LambdaQueryWrapper queryWrapper = new LambdaQueryWrapper<>();
 queryWrapper
        .eq(User::getName, "张三")
        .eq(User::getAge, 26);
 List list = list(queryWrapper);

3. 分页查询

// offset 默认从 1 开始，展示第一页
public Page queryPage(Integer offset, Integer limit) {
    return userDao.selectPage(PageDTO.of(offset, limit), new QueryWrapper<>());
}

4. 查询部分字段

public List queryUserList() {
    LambdaQueryWrapper wrapper = new LambdaQueryWrapper<>();
    wrapper.select(User::getName, User::getAge);
    return list(wrapper);
}

三、常用条件构造器

1. 单条件

# name = 'bai'
eq("name", "bai")

2. 拼接 AND

# name = ? AND age = ?
.eq("name", "张三").eq("age", 26);

3. 拼接 OR

# id = 1 or name = 'bai'
.eq("id",1).or().eq("name","bai")

4. 嵌套

# or ( name = '李白' and status <> '活着' )
.or ( x -> x.eq("name", "李白").ne("status", "活着") )
# and (  name = '李白' or ( name = '张三' and age = 12 )  )
.and(  x -> x.eq("name", "李白").or( y -> y.eq("name", "张三").eq("age", 12) )  )

四、注解查询

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.ResultType;
import org.apache.ibatis.annotations.Select;
import java.util.List;
@Mapper
public interface UserDao extends BaseMapper {
    @Select("select * from t_user where name = #{name}")
    @ResultType(User.class)
    List getUserListByName(@Param("name") String name);
    @Select("select * from t_user where id = #{id}")
    @ResultType(User.class)
    User getUserById(@Param("id") String id);
    
    @Select("select name from t_user where id = #{id}")
    String getUserNameById(@Param("id") String id);
}

五、xml 查询

1. 直接返回查询结果

List> queryUser();

  
    select * from user
  
  

2. 返回对象集合

List queryUser();

  
    select * from user
  
  

3. 返回单个对象

User queryUserById();

 
  
    select * from user where id = '1'
  
  

4. 返回整型

int queryUserCount();

  
  
    select count(*) from user
  
  

5. 返回字符串集合

List queryUserName();

  
    
  
  
  
    select name from user
  
  

六、处理 Json 数据

1. 存储

实体类中某个字段属性是 List，Map 之类的可以转为 Json 格式，其在 MySQL 中存储字段类型可以设置为 Json 类型，添加注解将此类型映射为 Json 存入数据库中

注：插入时可以不定义 autoResultMap = true，查询时必须定义

@TableName(value = "t_user")
public class User {
    @TableId
    private int id;
    @TableField(value = "user_info", typeHandler = JacksonTypeHandler.class)
    private JSONArray userInfo;
    @TableField(value = "info", typeHandler = JacksonTypeHandler.class)
    private JSONObject info;
}

2. 取出

当没有使用到 xml 时

@TableName(value = "t_user", autoResultMap = true)
public class User {
    @TableId
    private int id;
    @TableField(value = "user_info", typeHandler = JacksonTypeHandler.class)
    private JSONArray userInfo;
    @TableField(value = "info", typeHandler = JacksonTypeHandler.class)
    private JSONObject info;
}

当使用了 xml 时

七、循环遍历集合

/**
 * List : User(id=1, name=zhangsan, age=21)
 * List : User(id=2, name=lisi, age=22)
 *
 * Map : User(id=2, name=lisi, age=22)
 * Map : User(id=3, name=wangwu, age=23)
 */
public void queryTest() {
    userService.queryByList();
    userService.queryByMap();
    userService.updateByMap();
    userService.updateByList();
}

1. UserService

public class UserService {
    @Resource
    UserDao userDao;
    public void queryByList() {
        List list = new ArrayList<>();
        list.add(1);
        list.add(2);
        userDao.queryByList(list);
    }
    public void queryByMap() {
        Map map = new HashMap<>();
        map.put("name", username);
        userDao.queryByMap(map);
    }
    public void updateByMap() {
        Map map = new HashMap<>();
        map.put(2, 21);
        map.put(3, 31);
        userDao.updateByMap(map);
    }
    public void updateByList() {
        List list = new ArrayList<>();
        list.add(user1);
        list.add(user2);
        userDao.updateByList(list);
    }
}

2. UserDao

public interface UserDao extends BaseMapper {
    // UserMapper.xml 中 collection 的值对应 @Param 里的值
    List queryByList(@Param("list") List userList);
    // 此处不能使用 @Param , 或者不用 Map 直接传参 -> queryByMap(@Param("name") String username);
    List> queryByMap(Map map);
    void updateByMap(@Param("map") Map map);
    
    void updateByList(@Param("list") List userList);
}

3. UserMapper

  
  
    
    
    
  
  
    select * from user where
    
      
        id in
        
      		#{id}
    		
      
      
        1 = 2
      
    
  
  
    select id, name as username, age from user where
    
      name = #{name}
    
  
  
    
      update user set age = #{age} where id = #{id}
    
  
  
  
    
      update user set age = #{user.age} where id = #{user.id}
    
  

4. 增加配置参数

Mybatis 批量更新时需要在 url 后加上 &allowMultiQueries=true

application.yml

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&allowMultiQueries=true
    driver-class-name: com.mysql.jdbc.Driver

否则会报错

org.springframework.jdbc.BadSqlGrammarException: 
### Error updating database.  Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update user set age = 1 where id = 3' at line 3
### The error may involve com.bai.faker.mapper.UserMapper.updateByMapThree-Inline
### The error occurred while setting parameters
### SQL: update user set age = ? where id = ?      ;        update user set age = ? where id = ?
### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update user set age = 1 where id = 3' at line 3
; bad SQL grammar []; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'update user set age = 1 where id = 3' at line 3
... ...

八、执行 Sql 文件

1. UserService

import com.baomidou.mybatisplus.extension.service.IService;
import javax.sql.DataSource;
import java.io.File;
public interface UserService extends IService {
    void executeSqlFile(DataSource dataSource, File sqlFile);
}

2. UserServiceImpl

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import org.springframework.stereotype.Service;
import javax.sql.DataSource;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
@Service
public class UserServiceImpl extends ServiceImpl implements UserService {
    @Override
    public void executeSqlFile(DataSource dataSource, File sqlFile) {
        Connection connection = null;
        FileReader reader = null;
        try {
            connection = dataSource.getConnection();
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();
            reader = new FileReader(sqlFile);
            String line;
            BufferedReader bufferedReader = new BufferedReader(reader);
            while ((line = bufferedReader.readLine()) != null) {
                statement.addBatch(line);
            }
            statement.executeBatch();
            connection.commit();
            bufferedReader.close();
        } catch (Exception e) {
            if (connection != null) {
                try {
                    connection.rollback();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
                if (reader != null) {
                    reader.close();
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
    }
}