Mapper详解

Mapper和Query是fastmybatis两个核心类，结合两者能满足大部分的增删改查。

增

保存全部字段

int i = mapper.save(user); // 返回影响行数
int id = user.getId() // 拿到自增id

保存忽略null字段

int i = mapper.saveIgnoreNull(user); // 返回影响行数
int id = user.getId() // 拿到自增id

批量保存

INSERT INTO table(col1, col2, ...) VALUES (val1, val3,...),(val1, val3,...),(val1, val3,...)

List<TUser> users = ..
int i = mapper.saveBatch(users);  // 返回影响行数
        
// 自增id        
List<Integer> idList = users.stream()
    .map(TUser::getId)
    .collect(Collectors.toList());

批量保存不为null的数据

INSERT INTO table(col1, col2, ...) VALUES (val1, val3,...),(val1, val3,...),(val1, val3,...)

List<TUser> users = ..
int i = mapper.saveBatchIgnoreNull(users);  // 返回影响行数
// 分批处理，每次处理200条
int i = mapper.saveBatchIgnoreNull(users, 200);  // 返回影响行数        
// 自增id        
List<Integer> idList = users.stream()
    .map(TUser::getId)
    .collect(Collectors.toList());

改

修改全部字段

// UPDATE SET col1=?, col2=? ... WHERE id=?
int i = mapper.update(user) // 返回影响行数

修改不为null的字段

// UPDATE SET col1=?, col2=? ... WHERE id=?
int i = mapper.updateIgnoreNull(user) // 返回影响行数

根据主键id值更新部分字段

ColumnValue<TUser> columnValue = ColumnValue.create(TUser.class)
    .set(TUser::getUsername, "李四")
    .set(TUser::getRemark, "123");
int i = mapper.updateByMap(columnValue, 1);
 
 
 对应SQL: UPDATE table SET username='李四', remark='123' WHERE id = 1;

根据某个字段值更新部分字段

ColumnValue<TUser> columnValue = ColumnValue.create(TUser.class)
    .set(TUser::getRemark, "123");
 int i = mapper.updateByMap(columnValue, TUser::getUsername, "李四");
 
 对应SQL: UPDATE table SET remark='123' WHERE username='李四';

根据条件更新部分字段

ColumnValue<TUser> columnValue = ColumnValue.create(TUser.class)
    .set(TUser::getUsername, "李四2")
    .set(TUser::getRemark, "123");
Query query = Query.query(TUser.class)
        .eq(TUser::getId, 1);
 int i = mapper.updateByMap(columnValue, query);

根据条件更新(更新字段为null)

// 更新null值
  
 TUser user = Entitys.of(TUser.class);
 user.setUsername(null);
 user.setAddTime(new Date());
 // 批量更新 user_name和add_time
 Query query = Query.create().eq("username", "张三");
 int i = mapper.updateByQuery(user, query);
 
  对应SQL: UPDATE `t_user` SET `username`=null, add_time=? WHERE username = ?

查

根据主键查询

TUser user = mapper.getById(3);
 
  对应SQL:
  SELECT col1, col2, ...
  FROM `t_user` t
  WHERE id = 3
  

根据主键查询强制查询，忽略逻辑删除字段

TUser user = mapper.forceById(3); // 数据被删除也能查出来

根据条件查找单条记录

 // 查询id=3,金额大于1的用户
 Query query = new Query()
         .eq("id", 3)
         .gt("money", 1);
 TUser user = mapper.getByQuery(query);
 
  对应SQL:
  SELECT col1, col2, ...
  FROM `t_user` t
  WHERE id = ? AND money > ? LIMIT 1
  

查询单条数据并返回指定字段

Query query = new Query().eq("id", 6);
 TUser tUser = TUser tUser = mapper.getBySpecifiedColumns(
      Columns.of(TUser::getId, TUser::getUsername), query
 );
 
  对应SQL:
  SELECT id , username FROM `t_user` t WHERE id = 6 AND LIMIT 0,1

查询单条数据返回指定字段并转换到指定类中

 Query query = new Query().eq("id", 6);
 UserVO userVo = mapper.getBySpecifiedColumns(Arrays.asList("id", "username"), query, UserVO.class);
 
  对应SQL:
  SELECT id , username FROM `t_user` t WHERE id = 6 AND LIMIT 0,1

查询某一行某个字段值

Query query = new Query().eq("id", 6);
String username = mapper.getColumnValue(TUser::getUsername, query);
 
  转换成SQL：
  SELECT username FROM `t_user` t WHERE id = 6 LIMIT 0,1

根据字段查询一条记录

TUser user = mapper.getByColumn(TUser::getUsername, "王五");

SELECT col1,col2,... FROM table WHERE {column} = {value} LIMIT 1 

根据字段查询所有记录

List<TUser> list = mapper.listByColumn(TUser::getAge, 20);
 
  
对应SQL: SELECT col1, col2, ... FROM t_user WHERE age = 20; 

根据条件查询所有记录

 Query query = new Query()
         .eq("state", 0)
         .in("money", Arrays.asList(100, 1.0, 3));
 List<TUser> list = mapper.list(query);
 
  对应SQL:
  SELECT col1, col2, ...
  FROM `t_user` t
  WHERE state = ? AND money IN ( ? , ? , ? )

根据多个主键查询

List<User> list = mapper.listByIds(Arrays.asList(1,2,3));
 
  
SELECT col1, col2, ... FROM table WHERE id in (val1, val2, ...) 

根据字段多个值查询结果集

 List<TUser> list = mapper.listByValues(TUser::getAge, 20, 30, 40);
 
    SELECT col1, col2, ... FROM table WHERE age IN (20, 30, 40)

查询返回指定的列，返回实体类集合

Query query = new Query();
 // 添加查询条件
 query.eq("username", "张三");

 // 自定义字段
 Columns<TUser> columns = Columns.of(TUser::getId, TUser::getUsername);
 // 查询，返回一个集合
 List<TUser> list = mapper.listBySpecifiedColumns(columns, query);
 
  对应SQL: SELECT id , username FROM `t_user` t WHERE username = ?

查询返回指定的列，并转换

Query query = new Query();
 // 添加查询条件
 query.eq("username", "张三");

 // 自定义字段
 Columns<TUser> columns = Columns.of(TUser::getId, TUser::getUsername, TUser::getAddTime);
 // 查询，自定义集合
 List<UserVO> list = mapper.listBySpecifiedColumns(columns, query, UserVO.class);

查询指定列，返指定列集合

// 返回id集合
 List<Integer> idList = mapper.listColumnValues(TUser::getId, query, Integer.class);

分页查询

Query query = new Query();
 // 添加查询条件
 query.eq("username", "张三")
         .page(1, 2) // 分页查询，按页码分，通常使用这种。
 ;

 // 分页信息
 PageInfo<TUser> pageInfo = mapper.page(query);

 List<TUser> list = pageInfo.getList(); // 结果集
 long total = pageInfo.getTotal(); // 总记录数
 int pageCount = pageInfo.getPageCount(); // 共几页

分页查询返回指定的列

Query query = new Query()
         .eq("state", 0)
         .page(1, 6);
 PageInfo<MyUser> pageInfo = mapper.pageBySpecifiedColumns(
      Columns.of(TUser::getId, TUser::getUsername), query, MyUser.class
 );

分页查询，并转换结果

 PageInfo<UserVO> users = mapper.page(query, UserVO.class);

查询结果集，并转换结果集中的记录，转换处理每一行

PageInfo<TUser> pageInfo = mapper.page(query, tUser -> {
    // 对每行数据进行转换
    String username = tUser.getUsername();
    if ("张三".equals(username)) {
      tUser.setUsername("法外狂徒");
    }
    return tUser;
});

查询结果集，并转换结果集中的记录，转换处理list

Query query = new Query()
         .eq("state", 0);
 PageInfo<UserVO> pageInfo = mapper.pageAndConvert(query, list -> {
     List<UserVO> retList = new ArrayList<>(list.size());
     for (TUser tUser : list) {
         UserVO userVO = new UserVO();
         BeanUtils.copyProperties(tUser, userVO);
         retList.add(userVO);
     }
     return retList;
 });

查询返回easyui结果集 如果前端使用easyui，此返回结果可适用于easyui的datagrid组件

PageEasyui<User> list = mapper.pageEasyui(query);

查询结果并转换成Map对象 通过list中的某一列（如主键id）当做key返回map对象 如果key重复则抛出异常

public class User {
    private Integer id;
    private String name;
}

Query query = new Query()
        .ge("id", 1);
// id -> TUser
Map<Integer, TUser> map = mapper.getMap(query, TUser::getId);

查询列表并将结果转换成树结构

List<Menu> treeData = mapper.listTreeData(query, 0);

根据主键id检查记录是否存在

boolean exist = mapper.checkExistById(user);

根据某个字段检查记录是否存在

boolean b = mapper.checkExist(TUser::getUsername, "jim");

根据某个字段检查记录是否存在，且不是指定id的那条记录

boolean b = mapper.checkExist(TUser:getUsername, "jim", 1)

SELECT username FROM table WHERE username = ? and id != ?

删

删除记录（底层根据id删除），在有逻辑删除字段的情况下，做UPDATE操作。

int i = mapper.delete(user) // 返回影响行数

int j = mapper.deleteById(1);

根据多个主键id删除，在有逻辑删除字段的情况下，做UPDATE操作

int i = mapper.deleteByIds(Arrays.asList(1,2,3));

根据多个主键id删除，在有逻辑删除字段的情况下，更新其它字段

EqualColumn[] update = {
new EqualColumn("username", "deleteUser")
, new EqualColumn("add_time", new Date())
};
int i = mapper.deleteByIds(Arrays.asList(1,2,3), update);

根据某个值删除

mapper.deleteByColumn(TUser::getUsername, "jim"); 
对应SQL:DELETE FROM table WHERE username = 'jim'

强制删除（底层根据id删除），忽略逻辑删除字段，执行DELETE语句

int i = maper.forceDelete(user);

int i = maper.forceDeleteById(1);

根据条件强制删除，忽略逻辑删除字段，执行DELETE语句

Query query = Query.create().gt("id", 1);
int i = maper.forceDeleteByQuery(query);

DELETE FROM table WHERE id > 1;