springboot整合mybatis、thymeleaf
1.创建springboot项目,选择依赖Lombok、Web、Thymeleaf、MyDriver
2.在pom.xml中导入mybatisPlus、及代码生成器依赖
<!-- mybatis-plus--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-boot-starter</artifactId> <version>3.4.3.1</version> </dependency> <!-- 代码生成器--> <dependency> <groupId>com.baomidou</groupId> <artifactId>mybatis-plus-generator</artifactId> <version>3.4.1</version> </dependency> <!-- freemarker mybatis-plus完成项目构建所需模板,真实项目不需要使用--> <dependency> <groupId>org.freemarker</groupId> <artifactId>freemarker</artifactId> <version>2.3.31</version> </dependency>
3.使用mybatisPlus的代码生成器获取entity(pojo)、mapper、mapper.xml、service、controller
public class MyBatisPlusGenerator {
@Test
public void codeGenerator() {
// 代码生成器
AutoGenerator mpg = new AutoGenerator();
// 全局配置
GlobalConfig gc = new GlobalConfig();
String projectPath = System.getProperty("user.dir");//获取本地目录
gc.setOutputDir(projectPath + "/src/main/java");//选择生成目录
gc.setAuthor("wsx");//Mapper,Service类注解中显示创建人信息
//gc.setBaseColumnList(true); //在Mapper.xml文件中是否生成公用SQL代码段
//gc.setBaseResultMap(true); //在Mapper.xml文件中是否生成公用返回集合ResultMap
gc.setOpen(false); //文件生成完毕后,是否需要打开所在路径
// gc.setFileOverride(false);//是否覆盖原文件
// gc.setServiceName("%sService");//去掉接口前面的I
// gc.setIdType(IdType.AUTO);//设置id自增
// gc.setDateType(DateType.ONLY_DATE);//设置日期类型
// gc.setSwagger2(true);//开启swagger
mpg.setGlobalConfig(gc);
// 数据源配置
DataSourceConfig dsc = new DataSourceConfig();
dsc.setUrl("jdbc:mysql://localhost:3306/smt?serverTimezone=UTC&useUnicode=true&useSSL=false&characterEncoding=utf8");
dsc.setDriverName("com.mysql.cj.jdbc.Driver");
dsc.setUsername("root");
dsc.setPassword("root");
mpg.setDataSource(dsc);
// 包配置
PackageConfig pc = new PackageConfig();
// pc.setModuleName("autoGenerator");设置生成的包名
pc.setParent("com.wang"); //父级公用包名,就是自动生成的文件放在项目路径下的那个包中
mpg.setPackageInfo(pc);
// 自定义配置
InjectionConfig cfg = new InjectionConfig() {
@Override
public void initMap() {
// to do nothing
}
};
List<FileOutConfig> focList = new ArrayList<>();
//选择xml文件生成的路径
focList.add(new FileOutConfig("/templates/mapper.xml.ftl") {
@Override
public String outputFile(TableInfo tableInfo) {
// 自定义Mapper.xml文件存放的路径
return projectPath + "/src/main/resources/mapper/"
+ tableInfo.getEntityName() + "Mapper" + StringPool.DOT_XML;
}
});
cfg.setFileOutConfigList(focList);
mpg.setCfg(cfg);
mpg.setTemplate(new TemplateConfig().setXml(null));
// 策略配置
StrategyConfig strategy = new StrategyConfig();
// strategy.setInclude("books"); //选择要对应生成代码的表
// strategy.setInclude("books","users");//对应数据库中的表,可以选多个
strategy.setNaming(NamingStrategy.underline_to_camel); //Entity文件名称命名规范
strategy.setColumnNaming(NamingStrategy.underline_to_camel); //Entity字段名称
strategy.setEntityLombokModel(true); //是否使用lombok完成Entity实体标注Getting Setting ToString 方法
strategy.setRestControllerStyle(true); //Controller注解使用是否RestController标注,否则是否开启使用Controller标注
strategy.setEntityTableFieldAnnotationEnable(true);
// ↑ strategy.entityTableFieldAnnotationEnable(true);(←旧版) //是否在Entity属性上通过注解完成对数据库字段的映射
//自动填充配置(填充时间)
// TableFill gmt_create = new TableFill("gmt_create", FieldFill.INSERT);
// TableFill gmt_update = new TableFill("gmt_update", FieldFill.INSERT_UPDATE);
// ArrayList<TableFill> tableFills = new ArrayList<>();
// tableFills.add(gmt_update);
// tableFills.add(gmt_create);
// strategy.setTableFillList(tableFills);
//乐观锁
// strategy.setVersionFieldName("version");
//逻辑删除
// strategy.setLogicDeleteFieldName("deleted");
strategy.setControllerMappingHyphenStyle(true); //Controller注解名称,不使用驼峰,使用连字符
strategy.setTablePrefix("sys_"); //表前缀,添加该表示,则生成的实体,不会有表前缀,比如sys_dept 生成就是Dept
//strategy.setFieldPrefix("sys_"); //字段前缀
mpg.setStrategy(strategy);
mpg.setTemplateEngine(new FreemarkerTemplateEngine());
mpg.execute();
}
}
4.application.properties
#durid 数据源配置 特别注意 常规的4个字符串连接的名字,必须符合DruidDataSource的命名规则,注意是set方法,不是字段名称,比如url
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
#####自定义的配置信息,即SpringBoot中没有提供的配置,是我们自己额外提供的动态配置信息###########
spring.datasource.druid.username=root
spring.datasource.druid.password=root
spring.datasource.druid.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.druid.url=jdbc:mysql://localhost:3306/smt?serverTimezone=UTC&useUnicode=true&useSSL=false&characterEncoding=utf8
# 初始化大小,最小,最大
spring.datasource.druid.initialSize=5
spring.datasource.druid.minIdle=2
spring.datasource.druid.maxActive=20
# 配置获取连接等待超时的时间
spring.datasource.druid.maxWait=60000
# 配置间隔多久才进行一次检测,检测需要关闭的空闲连接,单位是毫秒
spring.datasource.druid.timeBetweenEvictionRunsMillis=60000
spring.datasource.druid.dbType=mysql
# 配置监控统计拦截的filters,去掉后监控界面sql无法统计,'wall'用于防火墙
spring.datasource.druid.filters=stat,wall
# 通过connectProperties属性来打开mergeSql功能(参数不同的sql合并统计)、慢SQL记录(执行时间长的sql)
spring.datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
####SpringBoot内置的配置信息,它会自动读取注入
####mybatisplus配置
#配置Mapper.xml映射文件
mybatis-plus.mapper-locations= classpath*:/mapper/*.xml
mybatis-plus.configuration.use-column-label=true
#配置mybatis数据返回类型别名
mybatis-plus.type-aliases-package=com.atwang.pojo
mybatis-plus.configuration.auto-mapping-behavior=full
#是否开启自动驼峰命名规则(camel case)映射,即从经典数据库列名 A_COLUMN(下划线命名) 到经典 Java 属性名 aColumn(驼峰命名) 的类似映射
mybatis-plus.configuration.map-underscore-to-camel-case=true
#配置SpringBoot默认的日志环境,开启打印SQL语句的Debug模式,语法:logging.level.<mapper所在包名>=debug
logging.level.com.wang.mapper=debug
####Tomcat端口号 默认是8080端口
server.port=8081
####Thymeleaf配置
#关闭缓存,默认开启
spring.thymeleaf.cache=false
#设置编码格式
spring.thymeleaf.encoding=UTF-8
5.Mapper CRUD接口(通用CRUD封装BaseMapper接口)
插入:insert
删除:deleteById deleteByMap delete deleteBatchIds
修改:updateById update
查询:selectById selectBatchIds selectByMap selectOne selectCount
selectList selectMaps selectObjs selectPage selectMapsPage
/** * 分页插件 */ @Bean public PaginationInterceptor paginationInterceptor() { return new PaginationInterceptor(); }
*测试:
1.BaseMapper
@SpringBootTest
public class BookTest {
@Autowired
private BookMapper bookMapper;
// 插入
@Test
void contextLoads() {
Book book = new Book();
book.setBookName("shixun说Java");
book.setBookCounts("25");
book.setDetail("从入门到放弃");
int insert = bookMapper.insert(book);
System.out.println(insert);
}
// 根据id修改
@Test
void update(){
Book book = new Book();
book.setBookId(10);
book.setBookName("关注我");
int i = bookMapper.updateById(book);
System.out.println(i);
}
//测试乐观锁成功
@Test
public void testOptimisticLocker(){
Book book = bookMapper.selectById(1);
book.setBookName("网络编程");
book.setDetail("从入门到学会");
bookMapper.updateById(book);
}
// 分页查询
@Test
public void testPage(){
Page<Book> page = new Page<>(2,1);
bookMapper.selectPage(page, null);
page.getRecords().forEach(System.out::println);
System.out.println(page.getRecords());
}
// 删除单个
@Test
public void deleteBookById(){
int i = bookMapper.deleteById(10);
System.out.println(i);
}
// 删除多个
@Test
public void deleteBook(){
bookMapper.deleteBatchIds(Arrays.asList(8,6,11));
}
// 删除多个(Map)
@Test
public void deleteBookMap(){
HashMap<String,Object> map = new HashMap<>();
map.put("detail","从删库到跑路");
bookMapper.deleteByMap(map);
}
// 插入
@Test
public void bookInsert(){
Book book = new Book();
book.setBookName("MySQL");
book.setDetail("从入门到学会");
book.setBookCounts("100");
bookMapper.insert(book);
}
}
2.Wrapper(条件构造器)
条件方法
allEq eq ne gt ge lt le between notBetween
like notLike likeLeft likeRight isNull isNotNull in notIn inSql
notInSql groupBy orderByAsc orderByDesc orderBy having or and last(后续补充含义...)
@SpringBootTest
public class WrapperTest {
@Autowired
private BookMapper bookMapper;
@Test
//不为空
void wrapperTest1() {
QueryWrapper<Book> queryWrapper = new QueryWrapper<>();
queryWrapper.isNotNull("bookName")
.ge("bookId", 1);
bookMapper.selectList(queryWrapper).forEach(System.out::println);
}
@Test
//名字为MySQL
void wrapperTest2() {
QueryWrapper<Book> bookQueryWrapper = new QueryWrapper<>();
bookQueryWrapper.eq("bookName", "MySQL");
bookMapper.selectOne(bookQueryWrapper);
}
@Test
//between 1 and 2
void test3() {
// 查询id在 1 ~ 2 岁之间的用户
QueryWrapper<Book> wrapper = new QueryWrapper<>();
wrapper.between("bookId", 1, 2); // 区间
Integer count = bookMapper.selectCount(wrapper);// 查询结果数
System.out.println(count);
}
// 模糊查询
@Test
void test4() {
QueryWrapper<Book> wrapper = new QueryWrapper<>();
// 左和右 right: t% left: %t
wrapper.notLike("bookName", "M")
.likeRight("bookName", "s");
List<Map<String, Object>> maps = bookMapper.selectMaps(wrapper);
maps.forEach(System.out::println);
}
@Test
// 按照id从大到小排序
void test5() {
QueryWrapper<Book> queryWrapper = new QueryWrapper<>();
queryWrapper.orderByDesc("bookId");
for (Book book : bookMapper.selectList(queryWrapper)) {
System.out.println(book);
}
}
}
6.Service CRUD接口(继承Service接口实现CRUD)
插入:save saveBatch saveOrUpdateBatch
删除:removeById removeByMap remove removeByIds
修改:updateById update updateBatchById saveOrUpdate
查询:getById listByIds listByMap getOne getMap getObj count list listMaps listObjs
分页查询:page pageMaps
*测试
@Slf4j @RunWith(SpringRunner.class) @SpringBootTest class SmtApplicationTests { @Autowired private DeptService deptService; //这里可以使用接口类型接收(多态),也可以使用实现类接收。 @Autowired private UserService userService; @Test void contextLoads() { } // 添加一个 @Test public void saveDeptTest() { //给Dept实体类添加lombok的@Builder注解,就可以使用如下的方式构建对象 Dept dept = Dept.builder().deptName("科技部").loc("上海").parentId(0).level("0").phone("12345478901") .orderNum(1).status(0).delFlg(0).createTime(LocalDateTime.now()).build(); boolean flg = deptService.save(dept); System.out.println(flg); } // 添加多个 @Test public void saveDeptTest1() { Dept dept = Dept.builder().deptName("人事部").loc("南京").parentId(0).level("1").phone("16638679982") .orderNum(1).status(0).delFlg(0).createTime(LocalDateTime.now()).build(); Dept dept1 = Dept.builder().deptName("人事部").loc("北京").parentId(0).level("1").phone("16638679982") .orderNum(1).status(0).delFlg(0).createTime(LocalDateTime.now()).build(); Dept dept2 = Dept.builder().deptName("安全部").loc("苏州").parentId(0).level("1").phone("16638679982") .orderNum(1).status(0).delFlg(0).createTime(LocalDateTime.now()).build(); List<Dept> list = Arrays.asList(dept, dept1, dept2); boolean b = deptService.saveBatch(list); log.info("插入成功:{}", b); } // 根据id删除 @Test public void deleteById() { boolean flg = deptService.removeById(2); log.info("删除1号部门,执行结果为:{}", flg); } // 查询所有部门并且按id倒序 @Test public void queryDept() { QueryWrapper<Dept> queryWrapper = new QueryWrapper<>(); queryWrapper.orderByDesc("dept_id"); for (Map<String, Object> map : deptService.listMaps(queryWrapper)) { System.out.println(map); } } // 根据ids删除 @Test public void delByIdsTest() { List<Integer> ids = Arrays.asList(4, 5); boolean flg = deptService.removeByIds(ids); log.info("删除多个部门,执行结果为:{}", flg); } // 根据id修改 @Test void updateById() { Dept dept = Dept.builder().deptId(1).deptName("卫生部").build(); boolean flg = deptService.updateById(dept); log.info("修改成功:{}", flg); } // 根据id查询 @Test void findAll() { for (Dept dept : deptService.list()) { System.out.println(dept); } } // 根据自定义条件查询 @Test public void findByInfo() { //自定义查询条件,这个时候就需要使用MyBatisPlus提供的条件构造器QueryWrapper<T>了 //条件如下loc like '广州%' and status = 0 and create_time between '2018-11-28 02:02:20' and '2018-11-28 02:05:15' order by dept_id Map<String, Object> where = new HashMap<>(); where.put("status", 0); QueryWrapper<Dept> queryWrapper = new QueryWrapper<>(); queryWrapper.likeRight("loc", "苏州") .eq("status", 0) .between("create_time", "2021-08-26 19:41:58", "2021-08-26 20:58:27") .orderByAsc("dept_id"); List<Dept> list = deptService.list(queryWrapper); list.forEach(dept -> log.info(dept.toString())); } //分页查询 @Test public void page() { Page<Dept> page = new Page<>(1, 3); //current:页码 , size:每页显示的条数 QueryWrapper<Dept> queryWrapper = new QueryWrapper<>(); queryWrapper.like("loc", "广州").orderByAsc("dept_id"); IPage<Dept> pageInfo = deptService.page(page, queryWrapper); log.info("总条数:{}", pageInfo.getTotal()); log.info("显示数据:{}:", pageInfo.getRecords()); log.info("页码:{}", page.getCurrent()); log.info("每页显示的条数:{}", page.getSize()); } // 添加多个员工: // 多表带条件分页查询 @Test public void saveBatch() { //给User实体添加三个注解@NoArgsConstructor @AllArgsConstructor @Builder User user1 = User.builder().userName("admin").userPwd("admin").createTime(LocalDateTime.now()).deptId(7).build(); User user2 = User.builder().userName("zhangsan").userPwd("zhangsan").createTime(LocalDateTime.now()).deptId(8).build(); User user3 = User.builder().userName("lis").userPwd("lisi").createTime(LocalDateTime.now()).deptId(9).build(); List<User> users = Arrays.asList(user1, user2, user3); boolean flg = userService.saveBatch(users); log.info("添加多个员工,执行结果为:{}", flg); } }
多表带条件分页查询 ,多表查询需要自定义SQL,也就是需要在Mapper映射文件中添加自己的需求,这个时候需要自定映射实体,也就是经常所见的VO。我们先添加一个员工,外键关联部门表,
然后查询该员工对应的部门信息。
1.在entity包中添加一个vo包,在该包下添加一个UserDeptVO类,定义要获取的信息字段。如下:
@Data public class UserDeptVO { private Integer userId; private String userName; private Integer deptId; private String deptName; private String loc; private String level; private LocalDateTime createTime; }
2.在UserMapper中添加两个接口,一个查询员工详细信息,一个带条件的分页查询。
public interface UserMapper extends BaseMapper<User> { UserDeptVO findByUserId(Integer userId); /** * 分页查询一定要添加IPage作为参数,传入页码和每页显示的条数 * @param page * @param vo 查询的条件 * @return */ List<UserDeptVO> userPage(IPage<UserDeptVO> page, @Param("vo") UserDeptVO vo); }
Mapper.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="com.wang.mapper.UserMapper"> <select id="findByUserId" resultType="com.wang.entity.vo.UserDeptVO"> SELECT user_id,user_name,d.dept_id,dept_name,loc,level,u.create_time user_create_time FROM sys_user u JOIN sys_dept d ON u.dept_id = d.dept_id WHERE user_id = #{userId} </select> <select id="userPage" resultType="com.wang.entity.vo.UserDeptVO"> SELECT user_id,user_name,d.dept_id,dept_name,loc,level,u.create_time user_create_time FROM sys_user u JOIN sys_dept d ON u.dept_id = d.dept_id <where> <if test="vo!=null"> <if test="vo.userId!=null"> AND user_id = #{vo.userId}</if> <if test="vo.userName!=null"> AND user_name like '%${vo.userName}%' </if> <if test="vo.deptId!=null"> AND d.dept_id = #{vo.deptId} </if> <if test="vo.deptName!=null"> AND dept_name like '${vo.deptName}' </if> <if test="vo.loc!=null"> AND d.loc like '${vo.loc}' </if> </if> </where> </select> </mapper>
UserService接口中添加一个查询方法,基本上和UserMapper接口中的方法一致,直接粘贴复制过去,接着在UserServiceImp实现类中完成接口的实现,这里需要注入UserMapper的实例,注意这里想要获取到UserMapper实例,需要在SpringBoot的启动类中添加注解扫描Mapper接口(@MapperScan(basePackages = "com.cdh.springboot.mapper")),否则会报找不到对应的方法.
@Service public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService { @Resource private UserMapper userMapper; @Override public UserDeptVO findByUserId(Integer userId) { return userMapper.findByUserId(userId); } @Override public List<UserDeptVO> userPage(IPage<UserDeptVO> page, UserDeptVO vo) { return userMapper.userPage(page, vo); } }
Junit测试,根据ID获取对应的多表数据
//通过id查询员工 @Test public void findUserAndDeptByUserId() { UserDeptVO userDeptVO = userService.findByUserId(2); log.info(userDeptVO.toString()); } //分页查询员工(查询部门名是‘人事部’和用户名中带‘m’的) @Test public void userPage() { UserDeptVO vo = new UserDeptVO(); vo.setDeptName("人事部"); vo.setUserName("m"); Page<UserDeptVO> page = new Page<>(1, 3); List<UserDeptVO> list = userService.userPage(page, vo); log.info("总条数:{}", page.getTotal()); list.forEach(userDeptVO -> log.info(userDeptVO.toString())); } }
*拓展:
自动填充时间(创建、修改)
//自动填充(字段添加填充内容) @TableField(fill = FieldFill.INSERT) private Date createTime; @TableField(fill = FieldFill.INSERT_UPDATE) private Date updateTime;
配置:
@Slf4j @Component//一定不要忘记把处理器加到IOC容器中 public class MyMetaObjectHandler implements MetaObjectHandler { @Override public void insertFill(MetaObject metaObject) { log.info("start insert fill...."); this.setFieldValByName("createTime",new Date(),metaObject); this.setFieldValByName("updateTime",new Date(),metaObject); } @Override public void updateFill(MetaObject metaObject) { log.info("start update fill...."); this.setFieldValByName("updateTime",new Date(),metaObject); } }
内容借鉴及转载原博主链接:javascript:void(0)