最近的项目中都是springboot+jpa的框架,jpa在开发上确实是能让效率很好的提升。但是项目运行中也会有一些问题(像1对1的映射、配置单项关联,延迟加载不生效、会冗余的查出一些不需要用到的字段或其他的信息)正好也是有了一个新项目,想着能不用jpa的框架,用mybatis,有同事说到了fluent-mybatis,那自己也是先写个demo玩玩,顺带记录着。
1.搭建springboot项目
2.整合mybatis、fluent-mybatis。 pom文件如下
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<project.fluent.mybatis.version>1.9.8</project.fluent.mybatis.version>
</properties>
<dependencies>
<dependency>
<groupId>junit</groupId>
<artifactId>junit</artifactId>
<version>4.11</version>
<!-- <scope>test</scope>-->
</dependency>
<!--添加spring-boot -->
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>2.1.10.RELEASE</version>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.1.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.49</version>
</dependency>
<dependency>
<groupId>com.github.atool</groupId>
<artifactId>fluent-mybatis</artifactId>
<version>${project.fluent.mybatis.version}</version>
</dependency>
<dependency>
<groupId>com.github.atool</groupId>
<artifactId>fluent-mybatis-processor</artifactId>
<scope>provided</scope>
<version>${project.fluent.mybatis.version}</version>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.8</version>
</dependency>
</dependencies>
3.配置application.yaml文件(数据源等信息)
spring:
datasource:
url: jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&serverTimezone=Asia/Shanghai
username: root
password: root
driver-class-name: com.mysql.jdbc.Driver
server:
port: 8001
mybatis:
configuration:
# 配置打印sql语句到控制台
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
项目的目录结构如下
也是可以通过代码生成工具,完成dao、entity等类的生成。首先得自己先在库里把表建好。
package com.freekai;
import cn.org.atool.generator.FileGenerator;
import cn.org.atool.generator.annotation.Relation;
import cn.org.atool.generator.annotation.RelationType;
import cn.org.atool.generator.annotation.Table;
import cn.org.atool.generator.annotation.Tables;
import com.freekai.custom.FreekaiMapper;
import org.junit.Test;
public class EntityGenerator {
//数据源url
static final String url = "jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=UTF-8&useSSL=false&autoReconnect=true&serverTimezone=Asia/Shanghai";
//数据库用户名
static final String username = "root";
//数据库密码
static final String password = "root";
@Test
public void generate() throws Exception {
//引用配置类,build方法允许有多个配置类
FileGenerator.build(Empty.class);
}
@Tables(
//设置数据库连接信息
url = url,username = username,password = password,
driver = "com.mysql.jdbc.Driver",
//设置entity类生成src目录,相对于user.dir
srcDir = "src/main/java",
//设置entity类的package值
basePack = "com.freekai.fluent",
//设置dao接口和实现的src目录,相对于user.dir
daoDir = "src/main/java",
//设置哪些表要生成Entity文件
tables = {@Table(value = {"user_test_address","user_test"}, logicDeleted = "is_delete", superMapper = FreekaiMapper.class)},
relations = {
@Relation(method = "findAddressV2", source = "user_test", target = "user_test_address", type = RelationType.OneWay_0_1
, where = "id=user_test_id"),
@Relation(method = "findUserTestV2", source = "user_test_address", target = "user_test", type = RelationType.OneWay_0_1)
}
)
static class Empty{ //类名随便取,只是配置定义的一个载体
}
}
上面的tables中 user_test_address user_test两张表配置了 1对1的映射。和hibernate中的@OneToOne含义一致。
另外也指定了逻辑删除的字段是is_delete。
细心的朋友会发现还有一个@Relation的配置。 method: findAddressV2即相当于会在生成的实体类中生成一个findAddressV2这个方法,这个方法的返回值是对应的关联表的实体类。where="id=user_test_id" 和source及target组合起来后,相当于 user_test.id = user_test_address.user_test_id (即在user_test_address表中有一个user_test_id字段,存储的是user_test表的id值),源码中也有注释。
另一个@Relation同理,反着配置一下就可以了。 执行上述代码中的generate()方法,可以发现目录下有生成的文件。
UserTestEntity.java
package com.freekai.fluent.entity;
import cn.org.atool.fluent.mybatis.annotation.FluentMybatis;
import cn.org.atool.fluent.mybatis.annotation.LogicDelete;
import cn.org.atool.fluent.mybatis.annotation.RefMethod;
import cn.org.atool.fluent.mybatis.annotation.TableField;
import cn.org.atool.fluent.mybatis.annotation.TableId;
import cn.org.atool.fluent.mybatis.base.RichEntity;
import com.freekai.custom.FreekaiMapper;
import java.util.Date;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
/**
* UserTestEntity: 数据映射实体定义
*
* @author Powered By Fluent Mybatis
*/
@SuppressWarnings({"rawtypes", "unchecked"})
@Data
@Accessors(
chain = true
)
@EqualsAndHashCode(
callSuper = false
)
@AllArgsConstructor
@NoArgsConstructor
@FluentMybatis(
table = "user_test",
schema = "test",
superMapper = FreekaiMapper.class,
desc = "userTest表的简单描述"
)
public class UserTestEntity extends RichEntity {
private static final long serialVersionUID = 1L;
@TableId(
value = "id",
auto = false
)
private Integer id;
@TableField("address_id")
private Integer addressId;
@TableField("age")
private Integer age;
@TableField("create_time")
private Date createTime;
@TableField("tel")
private String tel;
@TableField("update_time")
private Date updateTime;
@TableField("user_name")
private String userName;
@TableField("version")
private Integer version;
@TableField(
value = "is_delete",
insert = "0",
desc = "是否删除"
)
@LogicDelete
private Boolean isDelete;
@Override
public final Class entityClass() {
return UserTestEntity.class;
}
/**
* @see com.freekai.fluent.IEntityRelation#findAddressV2OfUserTestEntity(java.util.List)
*/
@RefMethod("userTestId = id")
public UserTestAddressEntity findAddressV2() {
return super.invoke("findAddressV2", true);
}
}
userTestAddress.java
package com.freekai.fluent.entity;
import cn.org.atool.fluent.mybatis.annotation.FluentMybatis;
import cn.org.atool.fluent.mybatis.annotation.RefMethod;
import cn.org.atool.fluent.mybatis.annotation.TableField;
import cn.org.atool.fluent.mybatis.annotation.TableId;
import cn.org.atool.fluent.mybatis.base.RichEntity;
import com.freekai.custom.FreekaiMapper;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.NoArgsConstructor;
import lombok.experimental.Accessors;
/**
* UserTestAddressEntity: 数据映射实体定义
*
* @author Powered By Fluent Mybatis
*/
@SuppressWarnings({"rawtypes", "unchecked"})
@Data
@Accessors(
chain = true
)
@EqualsAndHashCode(
callSuper = false
)
@AllArgsConstructor
@NoArgsConstructor
@FluentMybatis(
table = "user_test_address",
schema = "test",
superMapper = FreekaiMapper.class
)
public class UserTestAddressEntity extends RichEntity {
private static final long serialVersionUID = 1L;
@TableId(
value = "id",
auto = false
)
private Integer id;
@TableField("address")
private String address;
@TableField("user_test_id")
private Integer userTestId;
@Override
public final Class entityClass() {
return UserTestAddressEntity.class;
}
/**
* @see com.freekai.fluent.IEntityRelation#findUserTestV2OfUserTestAddressEntity(UserTestAddressEntity)
*/
@RefMethod
public UserTestEntity findUserTestV2() {
return super.invoke("findUserTestV2", true);
}
}
思考有个问题,如何像JPA一样根据主键+是否删除这个字段查询呢? 实现是比较简单,那么是不是每个实体类都需要再写一遍很类似的代码呢? ans:不需要。见下方
superMapper指定了自定义的一个类FreekaiMapper,这个类里,我自定义了一个根据主键+是否删除这个字段查询的方法。 相当于Jpa中 findByIdAndIsDelete(id, isDelete)..., FreekaiMapper.class内容如下
package com.freekai.custom;
import cn.org.atool.fluent.mybatis.If;
import cn.org.atool.fluent.mybatis.base.IEntity;
import cn.org.atool.fluent.mybatis.base.crud.IQuery;
import cn.org.atool.fluent.mybatis.base.crud.IWrapper;
import cn.org.atool.fluent.mybatis.base.entity.IMapping;
import cn.org.atool.fluent.mybatis.base.free.FreeQuery;
import cn.org.atool.fluent.mybatis.base.mapper.IEntityMapper;
import cn.org.atool.fluent.mybatis.base.mapper.IMapper;
import cn.org.atool.fluent.mybatis.base.mapper.IRichMapper;
import cn.org.atool.fluent.mybatis.base.model.SqlOp;
import cn.org.atool.fluent.mybatis.base.provider.SqlKitFactory;
import cn.org.atool.fluent.mybatis.exception.FluentMybatisException;
import cn.org.atool.fluent.mybatis.segment.WhereBase;
import cn.org.atool.fluent.mybatis.segment.model.WrapperData;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;
import java.util.List;
/**
* 自定义mapper,在代码生成时 配置一个supperMapper
* 或者是在entity实体类上的@FluentMybatis注解中配置superMapper值
* @FluentMybatis(
* table = "user_test",
* schema = "test",
* superMapper = FreekaiMapper.class
* )
*
*
* @param <E>
*/
public interface FreekaiMapper<E extends IEntity> extends IEntityMapper<E>, IRichMapper<E> {
default E findByIdAndIsDelete(Object id, Boolean isDelete) {
final IMapping mapping = this.mapping();
IQuery query = mapping.emptyQuery();
String primary = mapping.primaryId(true);
// 逻辑删除的字段
final String loginDeleteColumn = mapping.logicDeleteColumn();
final WhereBase apply = query.where().apply(primary, SqlOp.EQ, new Object[]{id});
if(!StringUtils.isEmpty(loginDeleteColumn)){
apply.and.apply(loginDeleteColumn, SqlOp.EQ, isDelete);
}
final List<E> list = this.listEntity(query);
if (If.isEmpty(list)) {
return null;
} else if (list.size() == 1) {
return list.get(0);
} else {
throw new FluentMybatisException("Expected one result (or null) to be returned, but found " + list.size() + " results.");
}
}
}
其中fluent-mybatis语法可以去官方查看对应的文档。下面一个controller是自己随便写的一些,完整的项目代码在github上。 https://github.com/freekai777/freekai-fluent-mybatis-demohttps://github.com/freekai777/freekai-fluent-mybatis-demo
package com.freekai.controller; import cn.org.atool.fluent.mybatis.base.free.FreeQuery; import cn.org.atool.fluent.mybatis.base.model.FieldMapping; import cn.org.atool.fluent.mybatis.model.StdPagedList; import com.freekai.dto.SimpleUserTestDTO; import com.freekai.entity.HelloFluentEntity; //import com.freekai.mapper.HelloFluentMapper; import com.freekai.fluent.entity.UserTestEntity; import com.freekai.fluent.mapper.UserTestMapper; import com.freekai.fluent.wrapper.UserTestQuery; import com.freekai.mapper.HelloFluentMapper; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.util.StringUtils; import org.springframework.web.bind.annotation.*; import java.util.HashMap; import java.util.List; import java.util.Map; import java.util.Random; @RestController public class HelloFluentMybatisController { @Autowired private UserTestMapper userTestMapper; @PostMapping("/addHello") public int addHello(){ UserTestEntity entity = new UserTestEntity(); entity.setId(1000*new Random().nextInt(10) + 1); final int insert = userTestMapper.insertWithPk(entity); return insert; } @PutMapping("/updateUser") public int updateUser(@RequestParam String id){ final UserTestEntity byIdAndIsDelete = userTestMapper.findByIdAndIsDelete(id, false); return 1; } @GetMapping("/user") public UserTestEntity get(@RequestParam Integer userId ){ final UserTestEntity byId = userTestMapper.findById(userId); return byId; } /** * 聚合的语法 * @return */ @GetMapping("/agg") public List<Map<String,Object>> aggregation(){ final List<Map<String, Object>> maps = userTestMapper.listMaps(userTestMapper.query().select.userName("userNameAlias") .count("cc").end().groupBy.userName().end().orderBy.desc("cc").end()); return maps; } /** * 分页查询 * @param userName * @param page * @param size * @return */ @GetMapping("/users") public StdPagedList<UserTestEntity> list(@RequestParam(required = false, value = "userName") String userName, @RequestParam(value = "page", defaultValue = "1",required = false) Integer page, @RequestParam(value = "size", defaultValue = "10",required = false) Integer size){ final StdPagedList<UserTestEntity> res = userTestMapper.stdPagedEntity(new UserTestQuery().where.userName() .like(userName, !StringUtils.isEmpty(userName)).end().limit((page - 1) * size, size)); return res; } /** * 分页且映射成自定义实体类的查询 * * 查询出来的 字段取的别名一定要求在 映射的实体类中存在。否则会报错!! * */ @GetMapping("/users2") public StdPagedList<SimpleUserTestDTO> listWithDtos(@RequestParam(required = false, value = "userName") String userName, @RequestParam(value = "page", defaultValue = "1",required = false) Integer page, @RequestParam(value = "size", defaultValue = "10",required = false) Integer size){ final StdPagedList<SimpleUserTestDTO> res = userTestMapper.stdPagedPoJo(SimpleUserTestDTO.class, new UserTestQuery() .select.id("idNew").createTime("createTime").end().where.userName() .like(userName, !StringUtils.isEmpty(userName)).end().limit((page - 1) * size, size)); return res; } /** * 自定义sql查询 * 通过占位符的形式 username * @return */ @GetMapping("/custom_sql") public List<Map<String,Object>> customQuery(@RequestParam String userName){ Map<String,String> param = new HashMap<>(16); param.put("userNN", userName + "%"); final FreeQuery freeQuery = new FreeQuery(null).customizedByPlaceholder(" select * from user_test t where t.user_name like #{userNN} ", param); final List<Map<String, Object>> maps = userTestMapper.listMaps(freeQuery); return maps; } }
另外也有一种,甚至连实现都不用写的,直接用Jpa的写法。需要在类上加注解
@FormService(table = "user_test"),然后在启动类上配置扫描路径
@SpringBootApplication @MapperScan({"com.freekai.mapper","com.freekai.fluent"}) @FormServiceScan("com.freekai.controller") public class FluentMybatisApplication {...}
@RestController @FormService(table = "user_test") public interface HelloFluentRestApi { @GetMapping("/findByUserName") UserTestEntity findByUserName(@RequestParam("userName") @Entry(value = "userName") String userName); }
通过postMan调用,发现也可以出现正确的结果。