搜索到
16
篇与
的结果
-
MyBatis-Plus简易使用教程 添加依赖添加MyBatis-plus依赖 <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.2</version> </dependency>配置初始化数据库演示SQL脚本如下CREATE TABLE user ( id BIGINT(20) NOT NULL COMMENT '主键ID', name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名', age INT(11) NULL DEFAULT NULL COMMENT '年龄', email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (id) )INSERT INTO user (id, name, age, email) VALUES (1, 'Jone', 18, 'test1@baomidou.com'), (2, 'Jack', 20, 'test2@baomidou.com'), (3, 'Tom', 28, 'test3@baomidou.com'), (4, 'Sandy', 21, 'test4@baomidou.com'), (5, 'Billie', 24, 'test5@baomidou.com');配置数据库连接在 application.yml 配置文件中添加 MySql 数据库的相关配置。spring: datasource: url: jdbc:mysql://localhost:3306/mybatisplus username: root password: 123456 driver-class-name: com.mysql.cj.jdbc.Driver在 Spring Boot 启动类中添加 @MapperScan 注解，扫描 Mapper 文件夹@SpringBootApplication @MapperScan("cc.lisen.mybatisplussample.mapper") public class MyBatisPlusSampleApplication { public static void main(String[] args) { SpringApplication.run(MyBatisPlusSampleApplication.class, args); } }快速开始编写实体类 User.java@Data @TableName(value = "`user`") public class User implements Serializable { /** * 主键ID */ @TableId(value = "id", type = IdType.AUTO) private Long id; /** * 姓名 */ @TableField(value = "`name`") private String name; /** * 年龄 */ @TableField(value = "age") private Integer age; /** * 邮箱 */ @TableField(value = "email") private String email; private static final long serialVersionUID = 1L; public static final String COL_ID = "id"; public static final String COL_NAME = "name"; public static final String COL_AGE = "age"; public static final String COL_EMAIL = "email"; }编写mapper类UserMapper.javapublic interface UserMapper extends BaseMapper<User> { }编写service类IUserService.javapublic interface IUserService extends IService<User>{ }编写impl类UserServiceImpl.java@Service public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService { }编写controller类UserController.java@RestController @RequestMapping("user") public class UserController { @Resource private IUserService userService; @GetMapping("list") public List<User> list(){ return userService.list(); } }MyBatis-Plus常见注解@TableName描述：表名注解属性类型必须指定默认值描述valueString否""表名schemaString否""schemakeepGlobalPrefixboolean否false是否保持使用全局的 tablePrefix 的值(如果设置了全局 tablePrefix 且自行设置了 value 的值)resultMapString否""xml 中 resultMap 的 idautoResultMapboolean否false是否自动构建 resultMap 并使用(如果设置 resultMap 则不会进行 resultMap 的自动构建并注入)excludePropertyString[]否{}需要排除的属性名(@since 3.3.1)@TableId描述：主键注解属性类型必须指定默认值描述valueString否""主键字段名typeEnum否IdType.NONE主键类型IdType值描述AUTO数据库ID自增NONE无状态,该类型为未设置主键类型(注解里等于跟随全局,全局里约等于 INPUT)INPUTinsert前自行set主键值ASSIGN_ID分配ID(主键类型为Number(Long和Integer)或String)(since 3.3.0),使用接口IdentifierGenerator的方法nextId(默认实现类为DefaultIdentifierGenerator雪花算法)ASSIGN_UUID分配UUID,主键类型为String(since 3.3.0),使用接口IdentifierGenerator的方法nextUUID(默认default方法)ID_WORKER分布式全局唯一ID 长整型类型(please use ASSIGN_ID)UUID32位UUID字符串(please use ASSIGN_UUID)ID_WORKER_STR分布式全局唯一ID 字符串类型(please use ASSIGN_ID)@TableField描述：字段注解(非主键)属性类型必须指定默认值描述valueString否""数据库字段名elString否""映射为原生 #{ ... } 逻辑,相当于写在 xml 里的 #{ ... } 部分existboolean否true是否为数据库表字段@TableLogic描述：表字段逻辑处理注解（逻辑删除）属性类型必须指定默认值描述valueString否""逻辑未删除值delvalString否""逻辑删除值CRUD接口Service CRUD 接口说明:通用 Service CRUD 封装IService接口，进一步封装 CRUD 采用 get 查询单行 remove 删除 list 查询集合 page 分页 前缀命名方式区分 Mapper 层避免混淆，泛型 T 为任意实体对象建议如果存在自定义通用 Service 方法的可能，请创建自己的 IBaseService 继承 Mybatis-Plus 提供的基类对象 Wrapper 为 条件构造器Save// 插入一条记录（选择字段，策略插入） boolean save(T entity); // 插入（批量） boolean saveBatch(Collection<T> entityList); // 插入（批量） boolean saveBatch(Collection<T> entityList, int batchSize);参数说明类型参数名描述Tentity实体对象CollectionentityList实体对象集合intbatchSize插入批次数量SaveOrUpdate// TableId 注解存在更新记录，否插入一条记录 boolean saveOrUpdate(T entity); // 根据updateWrapper尝试更新，否继续执行saveOrUpdate(T)方法 boolean saveOrUpdate(T entity, Wrapper<T> updateWrapper); // 批量修改插入 boolean saveOrUpdateBatch(Collection<T> entityList); // 批量修改插入 boolean saveOrUpdateBatch(Collection<T> entityList, int batchSize);参数说明类型参数名描述Tentity实体对象WrapperupdateWrapper实体对象封装操作类 UpdateWrapperCollectionentityList实体对象集合intbatchSize插入批次数量Remove// 根据 entity 条件，删除记录 boolean remove(Wrapper<T> queryWrapper); // 根据 ID 删除 boolean removeById(Serializable id); // 根据 columnMap 条件，删除记录 boolean removeByMap(Map<String, Object> columnMap); // 删除（根据ID 批量删除） boolean removeByIds(Collection<? extends Serializable> idList);参数说明类型参数名描述WrapperqueryWrapper实体包装类 QueryWrapperSerializableid主键IDMap<String, Object>columnMap表字段 map 对象Collection<? extends Serializable>idList主键ID列表Update// 根据 UpdateWrapper 条件，更新记录 需要设置sqlset boolean update(Wrapper<T> updateWrapper); // 根据 whereEntity 条件，更新记录 boolean update(T entity, Wrapper<T> updateWrapper); // 根据 ID 选择修改 boolean updateById(T entity); // 根据ID 批量更新 boolean updateBatchById(Collection<T> entityList); // 根据ID 批量更新 boolean updateBatchById(Collection<T> entityList, int batchSize);参数说明类型参数名描述WrapperupdateWrapper实体对象封装操作类 UpdateWrapperTentity实体对象CollectionentityList实体对象集合intbatchSize更新批次数量Get// 根据 ID 查询 T getById(Serializable id); // 根据 Wrapper，查询一条记录。结果集，如果是多个会抛出异常，随机取一条加上限制条件 wrapper.last("LIMIT 1") T getOne(Wrapper<T> queryWrapper); // 根据 Wrapper，查询一条记录 T getOne(Wrapper<T> queryWrapper, boolean throwEx); // 根据 Wrapper，查询一条记录 Map<String, Object> getMap(Wrapper<T> queryWrapper); // 根据 Wrapper，查询一条记录 <V> V getObj(Wrapper<T> queryWrapper, Function<? super Object, V> mapper);参数说明类型参数名描述Serializableid主键IDWrapperqueryWrapper实体对象封装操作类 QueryWrapperbooleanthrowEx有多个 result 是否抛出异常Tentity实体对象Function<? super Object, V>mapper转换函数List// 查询所有 List<T> list(); // 查询列表 List<T> list(Wrapper<T> queryWrapper); // 查询（根据ID 批量查询） Collection<T> listByIds(Collection<? extends Serializable> idList); // 查询（根据 columnMap 条件） Collection<T> listByMap(Map<String, Object> columnMap); // 查询所有列表 List<Map<String, Object>> listMaps(); // 查询列表 List<Map<String, Object>> listMaps(Wrapper<T> queryWrapper); // 查询全部记录 List<Object> listObjs(); // 查询全部记录 <V> List<V> listObjs(Function<? super Object, V> mapper); // 根据 Wrapper 条件，查询全部记录 List<Object> listObjs(Wrapper<T> queryWrapper); // 根据 Wrapper 条件，查询全部记录 <V> List<V> listObjs(Wrapper<T> queryWrapper, Function<? super Object, V> mapper);参数说明类型参数名描述WrapperqueryWrapper实体对象封装操作类 QueryWrapperCollection<? extends Serializable>idList主键ID列表Map<?String, Object>columnMap表字段 map 对象Function<? super Object, V>mapper转换函数Page// 无条件分页查询 IPage<T> page(IPage<T> page); // 条件分页查询 IPage<T> page(IPage<T> page, Wrapper<T> queryWrapper); // 无条件分页查询 IPage<Map<String, Object>> pageMaps(IPage<T> page); // 条件分页查询 IPage<Map<String, Object>> pageMaps(IPage<T> page, Wrapper<T> queryWrapper);参数说明类型参数名描述IPagepage翻页对象WrapperqueryWrapper实体对象封装操作类 QueryWrapperCount// 查询总记录数 int count(); // 根据 Wrapper 条件，查询总记录数 int count(Wrapper<T> queryWrapper);参数说明类型参数名描述WrapperqueryWrapper实体对象封装操作类 QueryWrapperChainquery// 链式查询 普通 QueryChainWrapper<T> query(); // 链式查询 lambda 式。注意：不支持 Kotlin LambdaQueryChainWrapper<T> lambdaQuery(); // 示例： query().eq("column", value).one(); lambdaQuery().eq(Entity::getId, value).list();update// 链式更改 普通 UpdateChainWrapper<T> update(); // 链式更改 lambda 式。注意：不支持 Kotlin LambdaUpdateChainWrapper<T> lambdaUpdate(); // 示例： update().eq("column", value).remove(); lambdaUpdate().eq(Entity::getId, value).update(entity);Mapper CRUD 接口说明:通用 CRUD 封装BaseMapper接口，为 Mybatis-Plus 启动时自动解析实体表关系映射转换为 Mybatis 内部对象注入容器泛型 T 为任意实体对象参数 Serializable 为任意类型主键 Mybatis-Plus 不推荐使用复合主键约定每一张表都有自己的唯一 id 主键对象 Wrapper 为 条件构造器Insert// 插入一条记录 int insert(T entity);参数说明类型参数名描述Tentity实体对象Delete// 根据 entity 条件，删除记录 int delete(@Param(Constants.WRAPPER) Wrapper<T> wrapper); // 删除（根据ID 批量删除） int deleteBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList); // 根据 ID 删除 int deleteById(Serializable id); // 根据 columnMap 条件，删除记录 int deleteByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap);参数说明类型参数名描述Wrapperwrapper实体对象封装操作类（可以为 null）Collection<? extends Serializable>idList主键ID列表(不能为 null 以及 empty)Serializableid主键IDMap<String, Object>columnMap表字段 map 对象Update// 根据 whereEntity 条件，更新记录 int update(@Param(Constants.ENTITY) T entity, @Param(Constants.WRAPPER) Wrapper<T> updateWrapper); // 根据 ID 修改 int updateById(@Param(Constants.ENTITY) T entity);参数说明类型参数名描述Tentity实体对象 (set 条件值,可为 null)WrapperupdateWrapper实体对象封装操作类（可以为 null,里面的 entity 用于生成 where 语句)Select// 根据 ID 查询 T selectById(Serializable id); // 根据 entity 条件，查询一条记录 T selectOne(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper); // 查询（根据ID 批量查询） List<T> selectBatchIds(@Param(Constants.COLLECTION) Collection<? extends Serializable> idList); // 根据 entity 条件，查询全部记录 List<T> selectList(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper); // 查询（根据 columnMap 条件） List<T> selectByMap(@Param(Constants.COLUMN_MAP) Map<String, Object> columnMap); // 根据 Wrapper 条件，查询全部记录 List<Map<String, Object>> selectMaps(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper); // 根据 Wrapper 条件，查询全部记录。注意： 只返回第一个字段的值 List<Object> selectObjs(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper); // 根据 entity 条件，查询全部记录（并翻页） IPage<T> selectPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper); // 根据 Wrapper 条件，查询全部记录（并翻页） IPage<Map<String, Object>> selectMapsPage(IPage<T> page, @Param(Constants.WRAPPER) Wrapper<T> queryWrapper); // 根据 Wrapper 条件，查询总记录数 Integer selectCount(@Param(Constants.WRAPPER) Wrapper<T> queryWrapper);参数说明类型参数名描述Serializableid主键IDWrapperqueryWrapper实体对象封装操作类（可以为 null）Collection<? extends Serializable>idList主键ID列表(不能为 null 以及 empty)Map<String, Object>columnMap表字段 map 对象IPagepage分页查询条件（可以为 RowBounds.DEFAULT）mapper 层 选装件说明:选装件位于 com.baomidou.mybatisplus.extension.injector.methods 包下 需要配合Sql 注入器使用,案例(opens new window)使用详细见AlwaysUpdateSomeColumnByIdint alwaysUpdateSomeColumnById(T entity);insertBatchSomeColumnint insertBatchSomeColumn(List<T> entityList);deleteByIdWithFillint deleteByIdWithFill(T entity);条件构造器说明:以下出现的第一个入参boolean condition表示该条件是否加入最后生成的sql中，例如：query.like(StringUtils.isNotBlank(name), Entity::getName, name) .eq(age!=null && age >= 0, Entity::getAge, age)以下代码块内的多个方法均为从上往下补全个别boolean类型的入参,默认为true以下出现的泛型Param均为Wrapper的子类实例(均具有AbstractWrapper的所有方法)以下方法在入参中出现的R为泛型,在普通wrapper中是String,在LambdaWrapper中是函数(例:Entity::getId,Entity为实体类,getId为字段id的getMethod)以下方法入参中的R column均表示数据库字段,当R具体类型为String时则为数据库字段名(字段名是数据库关键字的自己用转义符包裹!)。而不是实体类数据字段名!,另当R具体类型为SFunction时项目runtime不支持eclipse自家的编译器。以下举例均为使用普通wrapper,入参为Map和List的均以json形式表现!使用中如果入参的Map或者List为空,则不会加入最后生成的sql中!!!警告:不支持以及不赞成在 RPC 调用中把 Wrapper 进行传输wrapper 很重传输 wrapper 可以类比为你的 controller 用 map 接收值(开发一时爽,维护火葬场)正确的 RPC 调用姿势是写一个 DTO 进行传输,被调用方再根据 DTO 执行相应的操作AbstractWrapper说明:QueryWrapper(LambdaQueryWrapper) 和 UpdateWrapper(LambdaUpdateWrapper) 的父类，用于生成 sql 的 where 条件, entity 属性也用于生成 sql 的 where 条件。注意: entity 生成的 where 条件与 使用各个 api 生成的 where 条件没有任何关联行为allEqallEq(Map<R, V> params) allEq(Map<R, V> params, boolean null2IsNull) allEq(boolean condition, Map<R, V> params, boolean null2IsNull)全部eq或个别isNull个别参数说明:params : key为数据库字段名,value为字段值null2IsNull : 为true则在map的value为null时调用 isNull方法,为false时则忽略value为null的例1: allEq({id:1,name:"老王",age:null})--->id = 1 and name = '老王' and age is null例2: allEq({id:1,name:"老王",age:null}, false)--->id = 1 and name = '老王'allEq(BiPredicate<R, V> filter, Map<R, V> params) allEq(BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull) allEq(boolean condition, BiPredicate<R, V> filter, Map<R, V> params, boolean null2IsNull) 个别参数说明:filter : 过滤函数,是否允许字段传入比对条件中params 与 null2IsNull : 同上例1: allEq((k,v) -> k.indexOf("a") >= 0, {id:1,name:"老王",age:null})--->name = '老王' and age is null例2: allEq((k,v) -> k.indexOf("a") >= 0, {id:1,name:"老王",age:null}, false)--->name = '老王'eqeq(R column, Object val) eq(boolean condition, R column, Object val)等于 =例: eq("name", "老王")--->name = '老王'nene(R column, Object val) ne(boolean condition, R column, Object val)不等于 <>例: ne("name", "老王")--->name <> '老王'gtgt(R column, Object val) gt(boolean condition, R column, Object val)大于 >例: gt("age", 18)--->age > 18gege(R column, Object val) ge(boolean condition, R column, Object val)大于等于 >=例: ge("age", 18)--->age >= 18ltlt(R column, Object val) lt(boolean condition, R column, Object val)小于 <例: lt("age", 18)--->age < 18lele(R column, Object val) le(boolean condition, R column, Object val)小于等于 <=例: le("age", 18)--->age <= 18betweenbetween(R column, Object val1, Object val2) between(boolean condition, R column, Object val1, Object val2)BETWEEN 值1 AND 值2例: between("age", 18, 30)--->age between 18 and 30notBetweennotBetween(R column, Object val1, Object val2) notBetween(boolean condition, R column, Object val1, Object val2)NOT BETWEEN 值1 AND 值2例: notBetween("age", 18, 30)--->age not between 18 and 30likelike(R column, Object val) like(boolean condition, R column, Object val)LIKE '%值%'例: like("name", "王")--->name like '%王%'notLikenotLike(R column, Object val) notLike(boolean condition, R column, Object val)NOT LIKE '%值%'例: notLike("name", "王")--->name not like '%王%'likeLeftlikeLeft(R column, Object val) likeLeft(boolean condition, R column, Object val)LIKE '%值'例: likeLeft("name", "王")--->name like '%王'likeRightlikeRight(R column, Object val) likeRight(boolean condition, R column, Object val)LIKE '值%'例: likeRight("name", "王")--->name like '王%'isNullisNull(R column) isNull(boolean condition, R column)字段 IS NULL例: isNull("name")--->name is nullisNotNullisNotNull(R column) isNotNull(boolean condition, R column)字段 IS NOT NULL例: isNotNull("name")--->name is not nullinin(R column, Collection<?> value) in(boolean condition, R column, Collection<?> value)字段 IN (value.get(0), value.get(1), ...)例: in("age",{1,2,3})--->age in (1,2,3)in(R column, Object... values) in(boolean condition, R column, Object... values)字段 IN (v0, v1, ...)例: in("age", 1, 2, 3)--->age in (1,2,3)notInnotIn(R column, Collection<?> value) notIn(boolean condition, R column, Collection<?> value)字段 NOT IN (value.get(0), value.get(1), ...)例: notIn("age",{1,2,3})--->age not in (1,2,3)notIn(R column, Object... values) notIn(boolean condition, R column, Object... values)字段 NOT IN (v0, v1, ...)例: notIn("age", 1, 2, 3)--->age not in (1,2,3)inSqlinSql(R column, String inValue) inSql(boolean condition, R column, String inValue)字段 IN ( sql语句 )例: inSql("age", "1,2,3,4,5,6")--->age in (1,2,3,4,5,6)例: inSql("id", "select id from table where id < 3")--->id in (select id from table where id < 3)notInSqlnotInSql(R column, String inValue) notInSql(boolean condition, R column, String inValue)字段 NOT IN ( sql语句 )例: notInSql("age", "1,2,3,4,5,6")--->age not in (1,2,3,4,5,6)例: notInSql("id", "select id from table where id < 3")--->id not in (select id from table where id < 3)groupBygroupBy(R... columns) groupBy(boolean condition, R... columns)分组：GROUP BY 字段, ...例: groupBy("id", "name")--->group by id,nameorderByAscorderByAsc(R... columns) orderByAsc(boolean condition, R... columns)排序：ORDER BY 字段, ... ASC例: orderByAsc("id", "name")--->order by id ASC,name ASCorderByDescorderByDesc(R... columns) orderByDesc(boolean condition, R... columns)排序：ORDER BY 字段, ... DESC例: orderByDesc("id", "name")--->order by id DESC,name DESCorderByorderBy(boolean condition, boolean isAsc, R... columns)排序：ORDER BY 字段, ...例: orderBy(true, true, "id", "name")--->order by id ASC,name ASChavinghaving(String sqlHaving, Object... params) having(boolean condition, String sqlHaving, Object... params)HAVING ( sql语句 )例: having("sum(age) > 10")--->having sum(age) > 10例: having("sum(age) > {0}", 11)--->having sum(age) > 11funcfunc(Consumer<Children> consumer) func(boolean condition, Consumer<Children> consumer)func 方法(主要方便在出现if...else下调用不同方法能不断链)例: func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})oror() or(boolean condition)拼接 OR注意事项:主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)例: eq("id",1).or().eq("name","老王")--->id = 1 or name = '老王'or(Consumer<Param> consumer) or(boolean condition, Consumer<Param> consumer)OR 嵌套例: or(i -> i.eq("name", "李白").ne("status", "活着"))--->or (name = '李白' and status <> '活着')andand(Consumer<Param> consumer) and(boolean condition, Consumer<Param> consumer)AND 嵌套例: and(i -> i.eq("name", "李白").ne("status", "活着"))--->and (name = '李白' and status <> '活着')nestednested(Consumer<Param> consumer) nested(boolean condition, Consumer<Param> consumer)正常嵌套 不带 AND 或者 OR例: nested(i -> i.eq("name", "李白").ne("status", "活着"))--->(name = '李白' and status <> '活着')applyapply(String applySql, Object... params) apply(boolean condition, String applySql, Object... params)拼接 sql注意事项:该方法可用于数据库函数 动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!例: apply("id = 1")--->id = 1例: apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")例: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")--->date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")lastlast(String lastSql) last(boolean condition, String lastSql)无视优化规则直接拼接到 sql 的最后注意事项:只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用例: last("limit 1")existsexists(String existsSql) exists(boolean condition, String existsSql)拼接 EXISTS ( sql语句 )例: exists("select id from table where age = 1")--->exists (select id from table where age = 1)notExistsnotExists(String notExistsSql) notExists(boolean condition, String notExistsSql)拼接 NOT EXISTS ( sql语句 )例: notExists("select id from table where age = 1")--->not exists (select id from table where age = 1)QueryWrapper说明:继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件及 LambdaQueryWrapper, 可以通过 new QueryWrapper().lambda() 方法获取selectselect(String... sqlSelect) select(Predicate<TableFieldInfo> predicate) select(Class<T> entityClass, Predicate<TableFieldInfo> predicate)设置查询字段说明:以上方法分为两类.第二类方法为:过滤查询字段(主键除外),入参不包含 class 的调用前需要wrapper内的entity属性有值! 这两类方法重复调用以最后一次为准例: select("id", "name", "age")例: select(i -> i.getProperty().startsWith("test"))UpdateWrapper说明:继承自 AbstractWrapper ,自身的内部属性 entity 也用于生成 where 条件及 LambdaUpdateWrapper, 可以通过 new UpdateWrapper().lambda() 方法获取!setset(String column, Object val) set(boolean condition, String column, Object val)SQL SET 字段例: set("name", "老李头")例: set("name", "")--->数据库字段值变为空字符串例: set("name", null)--->数据库字段值变为nullsetSqlsetSql(String sql)设置 SET 部分 SQL例: setSql("name = '老李头'")lambda获取 LambdaWrapper在QueryWrapper中是获取LambdaQueryWrapper在UpdateWrapper中是获取LambdaUpdateWrapper使用 Wrapper 自定义SQL注意事项:需要mybatis-plus版本 >= 3.0.7 param 参数名要么叫ew,要么加上注解@Param(Constants.WRAPPER) 使用${ew.customSqlSegment} 不支持 Wrapper 内的entity生成where语句用注解@Select("select * from mysql_data ${ew.customSqlSegment}") List<MysqlData> getAll(@Param(Constants.WRAPPER) Wrapper wrapper);用XMLList<MysqlData> getAll(Wrapper ew); <select id="getAll" resultType="MysqlData"> SELECT * FROM mysql_data ${ew.customSqlSegment} </select>链式调用 lambda 式// 区分: // 链式调用 普通 UpdateChainWrapper<T> update(); // 链式调用 lambda 式。注意：不支持 Kotlin LambdaUpdateChainWrapper<T> lambdaUpdate(); // 等价示例： query().eq("id", value).one(); lambdaQuery().eq(Entity::getId, value).one(); // 等价示例： update().eq("id", value).remove(); lambdaUpdate().eq(Entity::getId, value).remove();分页插件配置依赖 <!-- 添加MyBatis-plus依赖--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.1</version> </dependency> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus</artifactId> <version>3.4.1</version> </dependency>增加配置文件@Configuration public class MybatisPlusConfig { // 最新版 @Bean public MybatisPlusInterceptor mybatisPlusInterceptor() { MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor(); interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL)); return interceptor; } @Bean public ConfigurationCustomizer configurationCustomizer() { return configuration -> configuration.setUseDeprecatedExecutor(false); } }编写mapper类@Mapper public interface UserMapper extends BaseMapper<User> { /** * 分页查询 * * @param page 分页对象,xml中可以从里面进行取值,传递参数 Page 即自动分页,必须放在第一位(你可以继承Page实现自己的分页对象) * @return */ Page<User> selectUserList(IPage<User> page); }xml编写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="cc.lisen.mybatisplussample.mapper.UserMapper"> <resultMap id="BaseResultMap" type="cc.lisen.mybatisplussample.domain.User"> <!--@Table `user`--> <id column="id" jdbcType="BIGINT" property="id"/> <result column="name" jdbcType="VARCHAR" property="name"/> <result column="age" jdbcType="INTEGER" property="age"/> <result column="email" jdbcType="VARCHAR" property="email"/> </resultMap> <sql id="Base_Column_List"> id, `name`, age, email </sql> <select id="selectUserList" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from user </select> </mapper>编写service类public interface IUserService extends IService<User>{ /** * 分页查询 * * @param page 分页对象,xml中可以从里面进行取值,传递参数 Page 即自动分页,必须放在第一位(你可以继承Page实现自己的分页对象) * @return */ Page<User> selectUserList(IPage<User> page); }编写impl类@Service public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements IUserService { @Resource UserMapper userMapper; /** * 分页查询 * * @param page 分页对象,xml中可以从里面进行取值,传递参数 Page 即自动分页,必须放在第一位(你可以继承Page实现自己的分页对象) * @return */ @Override public Page<User> selectUserList(IPage<User> page) { return userMapper.selectUserList(page); } }编写controller类@RestController @RequestMapping("user") public class UserController { @Resource private IUserService userService; @GetMapping("list") public List<User> list(){ return userService.list(); } @GetMapping("list1") public List<User> list1(){ QueryWrapper<User> queryWrapper = new QueryWrapper<>(); queryWrapper.eq("id",1).or().eq("name","Tom"); return userService.list(queryWrapper); } @GetMapping("page") public Page<User> page(){ IPage<User> userPage = new Page<>(1,3); return userService.selectUserList(userPage); } } -
MyBatis获取自增主键 我们在进行数据库设计时，经常使用自增主键的设计（(⊙o⊙)…，其实，我本人不太喜欢这种设计，我更喜欢使用UUID的方式）。对于自增主键，我们在进行插入时，一般不会指定具体的主键，而是让数据库自己生成一个主键。问题分析这样就会出现一个问题，比如，我们有一个学生表（student），有一个课程表（course），其中在课程表里面有一个外键（student_id），用户关联学生表。如果我们在插入学生表的同时，希望同时插入课程表，那么就会产生一个问题，在插入初始化学生实体时，我们没有设置主键值，导致插入课程表时，获取不到当前学生实体的主键。mybatis问题解决预制表我们先创建两张表。学生表（student)CREATE TABLE `student` ( `id` bigint(20) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;课程表（course）CREATE TABLE `course` ( `id` bigint(11) NOT NULL AUTO_INCREMENT, `course_name` varchar(255) DEFAULT NULL, `student_id` bigint(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `course_ibfk_1` (`student_id`), CONSTRAINT `course_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `student` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;预制实体Student.java/** * @author laughing * @date 2020/10/26 * @site https://lisen.cc */ @Data public class Student { private Long id; private String name; }Course.java/** * @author laughing * @date 2020/10/26 * @site https://lisen.cc */ @Data public class Course { private Long id; private String courseName; private Long studentId; }mapperStudentMapper.java/** * @author laughing * @date 2020/10/26 * @site https://lisen.cc */ public interface StudentMapper { int deleteByPrimaryKey(Long id); int insert(Student record); int insertSelective(Student record); Student selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Student record); int updateByPrimaryKey(Student record); }CourseMapper.java/** * @author laughing * @date 2020/10/26 * @site https://lisen.cc */ public interface CourseMapper { int deleteByPrimaryKey(Long id); int insert(Course record); int insertSelective(Course record); Course selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Course record); int updateByPrimaryKey(Course record); } /** * @author laughing * @date 2020/10/26 * @site https://lisen.cc */ public interface CourseMapper { int deleteByPrimaryKey(Long id); int insert(Course record); int insertSelective(Course record); Course selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Course record); int updateByPrimaryKey(Course record); }xmlStudentMapper.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="cc.lisen.mybatis.mapper.StudentMapper"> <resultMap id="BaseResultMap" type="cc.lisen.mybatis.entity.Student"> <!--@mbg.generated--> <!--@Table student--> <id column="id" jdbcType="BIGINT" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> </resultMap> <sql id="Base_Column_List"> <!--@mbg.generated--> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> <!--@mbg.generated--> select <include refid="Base_Column_List" /> from student where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> <!--@mbg.generated--> delete from student where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cc.lisen.mybatis.entity.Student" useGeneratedKeys="true"> <!--@mbg.generated--> insert into student (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="cc.lisen.mybatis.entity.Student" useGeneratedKeys="true"> <!--@mbg.generated--> insert into student <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="cc.lisen.mybatis.entity.Student"> <!--@mbg.generated--> update student <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="cc.lisen.mybatis.entity.Student"> <!--@mbg.generated--> update student set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>CourseMapper.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="cc.lisen.mybatis.mapper.CourseMapper"> <resultMap id="BaseResultMap" type="cc.lisen.mybatis.entity.Course"> <!--@mbg.generated--> <!--@Table course--> <id column="id" jdbcType="BIGINT" property="id" /> <result column="course_name" jdbcType="VARCHAR" property="courseName" /> <result column="student_id" jdbcType="BIGINT" property="studentId" /> </resultMap> <sql id="Base_Column_List"> <!--@mbg.generated--> id, course_name, student_id </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> <!--@mbg.generated--> select <include refid="Base_Column_List" /> from course where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> <!--@mbg.generated--> delete from course where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cc.lisen.mybatis.entity.Course" useGeneratedKeys="true"> <!--@mbg.generated--> insert into course (course_name, student_id) values (#{courseName,jdbcType=VARCHAR}, #{studentId,jdbcType=BIGINT}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="cc.lisen.mybatis.entity.Course" useGeneratedKeys="true"> <!--@mbg.generated--> insert into course <trim prefix="(" suffix=")" suffixOverrides=","> <if test="courseName != null"> course_name, </if> <if test="studentId != null"> student_id, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="courseName != null"> #{courseName,jdbcType=VARCHAR}, </if> <if test="studentId != null"> #{studentId,jdbcType=BIGINT}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="cc.lisen.mybatis.entity.Course"> <!--@mbg.generated--> update course <set> <if test="courseName != null"> course_name = #{courseName,jdbcType=VARCHAR}, </if> <if test="studentId != null"> student_id = #{studentId,jdbcType=BIGINT}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="cc.lisen.mybatis.entity.Course"> <!--@mbg.generated--> update course set course_name = #{courseName,jdbcType=VARCHAR}, student_id = #{studentId,jdbcType=BIGINT} where id = #{id,jdbcType=BIGINT} </update> </mapper>我们看一下插入语句<insert id="insert" keyColumn="id" keyProperty="id" parameterType="cc.lisen.mybatis.entity.Student" useGeneratedKeys="true"> <!--@mbg.generated--> insert into student (`name`) values (#{name,jdbcType=VARCHAR}) </insert>能够获取主键的关键在于keyColumn="id" keyProperty="id" useGeneratedKeys="true"其中keyColumn用于指定主键对应数据库的列，keyProperty对应实体的主键字段，useGeneratedKeys代表使用生成的主键。服务层我们看一下服务层的关键代码@Resource private StudentMapper studentMapper; @Resource private CourseMapper courseMapper; @Override public int insert(Student student) { studentMapper.insert(student); Course course = new Course(); course.setStudentId(student.getId()); course.setCourseName("测试"); courseMapper.insert(course); return 1; }调用服务层@Resource StudentService studentService; @RequestMapping("/student/insert") public String insertStudent() { Student student = new Student(); student.setName("张三"); studentService.insert(student); return "success"; }我们插入student时，没有指定student的id字段，在调用insert方法后，mybatis会自动将生成的主键，赋值给id字段，所以我们在插入course表时，就能获取到student的主键。
-
Spring Boot Mybatis一对一、一对多查询 数据库查询时，我们用的比较多的一般是一对一查询还有一对多查询。所谓一对一查询，就是查询对象关联的对象是唯一的，比如图书与作者，一般情况下，一个图书只有一个作者，这就是一对一查询。一对多查询，是指一个查询对象，关联多个对象，比如权限系统中，用户与角色的对应，一个用户可能属于一个角色，也可能属于多个角色，这就是一对多。下面我们针对介绍一下在mybatis中如何实现一对一及一对多的查询。创建项目并添加依赖这块没什么好说的，主要添加依赖如下： <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency>配置控制台输出sql为了查询sql输出信息，我们配置一下输出日志信息，让sql语句在控制台输出spring: datasource: url: jdbc:mysql://localhost:3306/mybatis?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai username: root password: root server: port: 8888 compression: enabled: true mybatis: type-aliases-package: cc.lisen.mybatis.entity mapper-locations: classpath:mapper/*.xml configuration: cache-enabled: true logging: level: cc.lisen.mybatis.mapper: DEBUG1.一对一查询这里，我们以图书与作者的对应关系为例，约定一个图书只能有一个作者。1.1 数据库表图书对应数据库表为book，作者对应数据库表为author，创建表及预制表数据sql如下：1.1.1 book表/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50730 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 50730 File Encoding : 65001 Date: 18/10/2020 01:32:16 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for book -- ---------------------------- DROP TABLE IF EXISTS `book`; CREATE TABLE `book` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `aid` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of book -- ---------------------------- INSERT INTO `book` VALUES (1, '《射雕英雄传》', 1); SET FOREIGN_KEY_CHECKS = 1; 1.1.2 author表/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50730 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 50730 File Encoding : 65001 Date: 18/10/2020 01:32:38 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for author -- ---------------------------- DROP TABLE IF EXISTS `author`; CREATE TABLE `author` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `age` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of author -- ---------------------------- INSERT INTO `author` VALUES (1, '张三', 35); SET FOREIGN_KEY_CHECKS = 1;1.2 创建实体1.2.1 Author.java/** * author * @author laughing */ @Data public class Author implements Serializable { private Integer id; private String name; private Integer age; private static final long serialVersionUID = 1L; }1.2.2 Book.java因为是图书（book）关联作者（author），所以我们需要在图书（book）实体类中，加入作者（author）属性，用于sql查询映射。/** * book * @author laughing */ @Data public class Book implements Serializable { private Integer id; private String name; private Author author; private static final long serialVersionUID = 1L; }1.3 创建mapper1.3.1 BookMapper.javapublic interface BookMapper { /** * 查询book * * @param id 主键 * @return book */ Book selectByPrimaryKey(Integer id); }1.4 创建xml一对一关联查询的关键在于association关键字，这个节点中的内容，和 resultMap 一样，也是 id，result 等，在这个节点中，我们还可以继续描述一对一。由于在实际项目中，每次返回的数据类型可能都会有差异，这就需要定义多个 resultMap，而这多个 resultMap 中，又有一部份属性是相同的，所以，我们可以将相同的部分抽出来，做成一个公共的模板，然后通过extends关键字，继承公共的模板。<?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="cc.lisen.mybatis.mapper.BookMapper"> <resultMap id="BaseResultMap" type="cc.lisen.mybatis.entity.Book"> <id column="id" jdbcType="INTEGER" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> </resultMap> <resultMap id="BookWithAuthor" type="cc.lisen.mybatis.entity.Book" extends="BaseResultMap"> <association property="author" javaType="cc.lisen.mybatis.entity.Author"> <id column="id" property="id" jdbcType="INTEGER" javaType="java.lang.Integer"/> <id column="name" property="name"/> <id column="age" property="age"/> </association> </resultMap> <sql id="Base_Column_List"> id, `name`, aid </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BookWithAuthor"> select <include refid="Base_Column_List" /> from book where id = #{id,jdbcType=INTEGER} </select> </mapper>1.5 创建服务层为了节省篇幅，我们这里就省略掉接口代码，具体服务层代码如下：/** * @author laughing * @date 2020-10-16 */ @Service public class BookServiceImpl implements BookService { @Resource BookMapper bookMapper; /** * 查询book * * @param id 主键 * @return book */ @Override public Book selectByPrimaryKey(Integer id) { return bookMapper.selectByPrimaryKey(id); } }1.6 创建controller创建一个rest接口，测试我们的查询/** * @author laughing * @date 2020-10-16 */ @RestController @RequestMapping("book") public class BookController { private final BookService bookService; public BookController(BookService bookService){ this.bookService=bookService; } /** * 查询book * * @param id 主键 * @return book */ @GetMapping("selectByPrimaryKey/{id}") public Book selectByPrimaryKey(@PathVariable("id") Integer id) { return bookService.selectByPrimaryKey(id); } }2.一对多查询一对多查询与一对一查询类似，区别主要在于实体的关联关系及xml文件的配置。我们以权限系统常见的用户、角色对应关系为例。约定一个用户可以关联多个角色。2.1 数据库表我们数据库有三张表，user用户存储用户信息，role用于存储角色信息，user_role用于存储用户与角色的关联关系，相关表结构及数据如下/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 50730 Source Host : localhost:3306 Source Schema : mybatis Target Server Type : MySQL Target Server Version : 50730 File Encoding : 65001 Date: 18/10/2020 02:02:01 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for role -- ---------------------------- DROP TABLE IF EXISTS `role`; CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `nameZh` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of role -- ---------------------------- INSERT INTO `role` VALUES (1, 'dba', '数据库管理员'); INSERT INTO `role` VALUES (2, 'admin', '系统管理员'); INSERT INTO `role` VALUES (3, 'user', '用户'); SET FOREIGN_KEY_CHECKS = 1; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user -- ---------------------------- DROP TABLE IF EXISTS `user`; CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `username` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `enabled` tinyint(1) NULL DEFAULT NULL, `locked` tinyint(1) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 4 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user -- ---------------------------- INSERT INTO `user` VALUES (1, 'root', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0); INSERT INTO `user` VALUES (2, 'admin', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0); INSERT INTO `user` VALUES (3, 'sang', '$2a$10$RMuFXGQ5AtH4wOvkUqyvuecpqUSeoxZYqilXzbz50dceRsga.WYiq', 1, 0); SET FOREIGN_KEY_CHECKS = 1; SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for user_role -- ---------------------------- DROP TABLE IF EXISTS `user_role`; CREATE TABLE `user_role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `uid` int(11) NULL DEFAULT NULL, `rid` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of user_role -- ---------------------------- INSERT INTO `user_role` VALUES (1, 1, 1); INSERT INTO `user_role` VALUES (2, 1, 2); INSERT INTO `user_role` VALUES (3, 2, 2); INSERT INTO `user_role` VALUES (4, 3, 3); SET FOREIGN_KEY_CHECKS = 1;1.2 创建实体1.2.1 User.java/** * user * * @author laughing */ @Data @JsonIgnoreProperties(value = { "handler"}) public class User implements Serializable { private Integer id; private String username; private String password; private Boolean enabled; private Boolean locked; private List<Role> roleList; private static final long serialVersionUID = 1L; }1.2.2 Role.java/** * role * @author laughing */ @Data @JsonIgnoreProperties(value = { "handler"}) public class Role implements Serializable { private Integer id; private String name; private String namezh; private static final long serialVersionUID = 1L; }1.3 创建mapperpublic interface UserMapper { /** * 根据Id查找 * @param id * @return */ User selectByPrimaryKey(Integer id); /** * 根据用户查找角色列表 * @param uid * @return */ List<Role> getRolesByUid(@Param("uid") Integer uid); }1.4 创建xml一对一的查询通过association，一对多的查询与此类似，只是通过collection关键字，替换association关键字。1.4.1 方式1<?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="cc.lisen.mybatis.mapper.UserMapper"> <resultMap id="BaseResultMap" type="cc.lisen.mybatis.entity.User"> <id column="id" jdbcType="INTEGER" property="id"/> <result column="username" jdbcType="VARCHAR" property="username"/> <result column="password" jdbcType="VARCHAR" property="password"/> <result column="enabled" jdbcType="BOOLEAN" property="enabled"/> <result column="locked" jdbcType="BOOLEAN" property="locked"/> <collection property="roleList" ofType="cc.lisen.mybatis.entity.Role"> <result column="rid" property="id" javaType="java.lang.Integer"/> <result column="rname" property="name" javaType="java.lang.String"/> <result column="rnameZh" property="namezh" javaType="java.lang.String"/> </collection> </resultMap> <sql id="Base_Column_List"> id, username, `password`, enabled, locked </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap"> select user.*,role.id as rid,role.name as rname,role.nameZh as rnameZh from user,role,user_role where user.id = user_role.uid and role.id = user_role.rid and user.id = #{id,jdbcType=INTEGER} </select> </mapper>1.4.2 懒加载方式通过fetchType="lazy"实现懒加载<?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="cc.lisen.mybatis.mapper.UserMapper"> <resultMap id="BaseResultMap" type="cc.lisen.mybatis.entity.User"> <id column="id" jdbcType="INTEGER" property="id"/> <result column="username" jdbcType="VARCHAR" property="username"/> <result column="password" jdbcType="VARCHAR" property="password"/> <result column="enabled" jdbcType="BOOLEAN" property="enabled"/> <result column="locked" jdbcType="BOOLEAN" property="locked"/> <collection property="roleList" ofType="cc.lisen.mybatis.entity.Role" column="id" select="getRolesByUid" fetchType="lazy"> </collection> </resultMap> <sql id="Base_Column_List"> id, username, `password`, enabled, locked </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from user where user.id = #{id,jdbcType=INTEGER} </select> <select id="getRolesByUid" resultType="cc.lisen.mybatis.entity.Role"> SELECT r.* FROM role r,user_role ur WHERE r.`id`=ur.`rid` AND ur.`uid`=#{uid} </select> </mapper>1.5 创建服务层服务层我们依然省略掉接口代码/** * @author laughing * @date 2020-10-17 */ @Service public class UserServiceImpl implements UserService { @Resource UserMapper userMapper; /** * 根据Id查找 * * @param id * @return */ @Override public User selectByPrimaryKey(Integer id) { return userMapper.selectByPrimaryKey(id); } }1.6 创建controller创建rest接口，测试代码/** * @author laughing * @date 2020-10-17 */ @RestController @RequestMapping("user") public class UserController { private final UserService userService; public UserController(UserService userService){ this.userService=userService; } @RequestMapping("selectByPrimaryKey/{id}") public User selectByPrimaryKey(@PathVariable("id") Integer id) { return userService.selectByPrimaryKey(id); } }
-
Spring boot整合mybatis多数据源简单使用 日常开发中，我们很少会在一个应用程序中同时使用多个数据源。但是，如果涉及一些数据迁移等应用，可能会涉及将数据从一个库迁移到另外一个库，甚至是不同类型的数据库，比如MySQL到Oracle。这篇博文，我们不介绍mybatis的基本使用，只介绍基于mybatis配置多数据源的方法。数据源准备我这里用了本地MySQL两个库，分别是mybatisone和mybatistwo,mybatisone库中有一张userone表，mybatistwo库中有一张usertwo表，建表sql如下：userone/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80021 Source Host : localhost:3306 Source Schema : mybatisone Target Server Type : MySQL Target Server Version : 80021 File Encoding : 65001 Date: 01/10/2020 00:34:29 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for userone -- ---------------------------- DROP TABLE IF EXISTS `userone`; CREATE TABLE `userone` ( `id` bigint(0) NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of userone -- ---------------------------- INSERT INTO `userone` VALUES (1, '姓名1'); SET FOREIGN_KEY_CHECKS = 1; usertwo/* Navicat Premium Data Transfer Source Server : localhost Source Server Type : MySQL Source Server Version : 80021 Source Host : localhost:3306 Source Schema : mybatistwo Target Server Type : MySQL Target Server Version : 80021 File Encoding : 65001 Date: 01/10/2020 00:34:35 */ SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for usertwo -- ---------------------------- DROP TABLE IF EXISTS `usertwo`; CREATE TABLE `usertwo` ( `id` bigint(0) NOT NULL, `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of usertwo -- ---------------------------- INSERT INTO `usertwo` VALUES (1, '姓名2'); SET FOREIGN_KEY_CHECKS = 1; 添加依赖主要依赖如下 <!--mybatis依赖--> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>2.1.3</version> </dependency> <!--MySQL驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <!--lombok--> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <optional>true</optional> </dependency> <!--druid--> <dependency> <groupId>com.alibaba</groupId> <artifactId>druid-spring-boot-starter</artifactId> <version>1.1.14</version> </dependency>application.yaml配置文件yaml文件配置数据源信息以及mybatis的配置信息spring: datasource: mybatisone: url: jdbc:mysql://localhost:3306/mybatisone?chartset=utf8mb4&serverTimezone=UTC&usessl=false username: root password: root type: com.alibaba.druid.pool.DruidDataSource mybatistwo: url: jdbc:mysql://localhost:3306/mybatistwo?chartset=utf8mb4&serverTimezone=UTC&usessl=false username: root password: root type: com.alibaba.druid.pool.DruidDataSource mybatis: mapper-locations: classpath*:mapper/*.xml type-aliases-package: cc.lisen.simplemybatis.entity配置druid根据yaml文件提供的DataSource，分别配置对应的两个数据源。DataSourceConfig.java/** * datasource配置文件 * @author laughing * @date 2020/9/30 * @site https://lisen.cc */ @Configuration public class DataSourceConfig { /** * * @return 第一个数据源 */ @Bean @ConfigurationProperties(prefix = "spring.datasource.mybatisone") DataSource dataSourceOne(){ return DruidDataSourceBuilder.create().build(); } /** * * @return 第二个数据源 */ @Bean @ConfigurationProperties(prefix = "spring.datasource.mybatistwo") DataSource dataSourceTwo(){ return DruidDataSourceBuilder.create().build(); } }配置mybatis数据源MyBatisConfigOne.java/** * mybatis第一个配置文件 * @author laughing * @date 2020/9/30 * @site https://lisen.cc */ @Configuration @MapperScan(basePackages = {"cc.lisen.simplemybatis.mapper.one"},sqlSessionFactoryRef = "sqlSessionFactoryOne",sqlSessionTemplateRef = "sqlSessionTemplateOne") public class MyBatisConfigOne { private final Logger logger = LoggerFactory.getLogger(MyBatisConfigOne.class); @Resource(name = "dataSourceOne") DataSource dataSourceOne; @Bean SqlSessionFactory sqlSessionFactoryOne() { SqlSessionFactory sqlSessionFactory = null; try { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml")); sqlSessionFactoryBean.setDataSource(dataSourceOne); sqlSessionFactory = sqlSessionFactoryBean.getObject(); }catch (Exception ex){ logger.error(ex.getMessage()); } return sqlSessionFactory; } @Bean SqlSessionTemplate sqlSessionTemplateOne(){ return new SqlSessionTemplate(sqlSessionFactoryOne()); } }MyBatisConfigTwo.java/** * mybatis第二个配置文件 * @author laughing * @date 2020/9/30 * @site https://lisen.cc */ @Configuration @MapperScan(basePackages = {"cc.lisen.simplemybatis.mapper.two"},sqlSessionFactoryRef = "sqlSessionFactoryTwo",sqlSessionTemplateRef = "sqlSessionTemplateTwo") public class MyBatisConfigTwo { private final Logger logger = LoggerFactory.getLogger(MyBatisConfigTwo.class); @Resource(name = "dataSourceTwo") DataSource dataSourceTwo; @Bean SqlSessionFactory sqlSessionFactoryTwo() { SqlSessionFactory sqlSessionFactory = null; try { SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean(); sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/two/*.xml")); sqlSessionFactoryBean.setDataSource(dataSourceTwo); sqlSessionFactory = sqlSessionFactoryBean.getObject(); }catch (Exception ex){ logger.error(ex.getMessage()); } return sqlSessionFactory; } @Bean SqlSessionTemplate sqlSessionTemplateTwo(){ return new SqlSessionTemplate(sqlSessionFactoryTwo()); } }生成mybatis信息mybatis不是我们要讲解的重点，我这里的信息都是通过Free MyBatis插件自动生成的。不做过多介绍。增加实体Userone.java/** * userone * @author */ @Data public class Userone implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }Usertwo.java/** * usertwo * @author */ @Data public class Usertwo implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }增加mapperUseroneMapper.java@Mapper public interface UseroneMapper { int deleteByPrimaryKey(Long id); int insert(Userone record); int insertSelective(Userone record); Userone selectByPrimaryKey(Long id); int updateByPrimaryKeySelective(Userone record); int updateByPrimaryKey(Userone record); }Usertwo.java/** * usertwo * @author */ @Data public class Usertwo implements Serializable { private Long id; private String name; private static final long serialVersionUID = 1L; }增加xmlUseroneMapper.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="cc.lisen.simplemybatis.mapper.one.UseroneMapper"> <resultMap id="BaseResultMap" type="cc.lisen.simplemybatis.entity.Userone"> <id column="id" jdbcType="BIGINT" property="id"/> <result column="name" jdbcType="VARCHAR" property="name"/> </resultMap> <sql id="Base_Column_List"> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List"/> from userone where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from userone where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cc.lisen.simplemybatis.entity.Userone" useGeneratedKeys="true"> insert into userone (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="cc.lisen.simplemybatis.entity.Userone" useGeneratedKeys="true"> insert into userone <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="cc.lisen.simplemybatis.entity.Userone"> update userone <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="cc.lisen.simplemybatis.entity.Userone"> update userone set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>UsertwoMapper.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="cc.lisen.simplemybatis.mapper.two.UsertwoMapper"> <resultMap id="BaseResultMap" type="cc.lisen.simplemybatis.entity.Usertwo"> <id column="id" jdbcType="BIGINT" property="id" /> <result column="name" jdbcType="VARCHAR" property="name" /> </resultMap> <sql id="Base_Column_List"> id, `name` </sql> <select id="selectByPrimaryKey" parameterType="java.lang.Long" resultMap="BaseResultMap"> select <include refid="Base_Column_List" /> from usertwo where id = #{id,jdbcType=BIGINT} </select> <delete id="deleteByPrimaryKey" parameterType="java.lang.Long"> delete from usertwo where id = #{id,jdbcType=BIGINT} </delete> <insert id="insert" keyColumn="id" keyProperty="id" parameterType="cc.lisen.simplemybatis.entity.Usertwo" useGeneratedKeys="true"> insert into usertwo (`name`) values (#{name,jdbcType=VARCHAR}) </insert> <insert id="insertSelective" keyColumn="id" keyProperty="id" parameterType="cc.lisen.simplemybatis.entity.Usertwo" useGeneratedKeys="true"> insert into usertwo <trim prefix="(" suffix=")" suffixOverrides=","> <if test="name != null"> `name`, </if> </trim> <trim prefix="values (" suffix=")" suffixOverrides=","> <if test="name != null"> #{name,jdbcType=VARCHAR}, </if> </trim> </insert> <update id="updateByPrimaryKeySelective" parameterType="cc.lisen.simplemybatis.entity.Usertwo"> update usertwo <set> <if test="name != null"> `name` = #{name,jdbcType=VARCHAR}, </if> </set> where id = #{id,jdbcType=BIGINT} </update> <update id="updateByPrimaryKey" parameterType="cc.lisen.simplemybatis.entity.Usertwo"> update usertwo set `name` = #{name,jdbcType=VARCHAR} where id = #{id,jdbcType=BIGINT} </update> </mapper>配置测试Api/** * @author laughing * @date 2020/9/30 * @site https://lisen.cc */ @RestController public class MyBatisController { @Resource UseroneMapper useroneMapper; @Resource UsertwoMapper usertwoMapper; @RequestMapping("/one") public Userone userone(){ return useroneMapper.selectByPrimaryKey(1L); } @RequestMapping("/two") public Usertwo usertwo(){ return usertwoMapper.selectByPrimaryKey(1L); } }增加两个Api，分别用于获取数据源1和数据源2的数据。整体代码结构里面封装了全局异常，如果不了解的，可以参考 SpringBoot 之 @ControllerAdvice使用场景代码测试我们在分别打开两个请求，查看获取的数据注意事项在配置数据源时，注意设置mapper的位置，即如下代码：sqlSessionFactoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mapper/one/*.xml"));如果没有设置，可能会提示Invalid bound statement (not found)
