一、引入依赖
<?xml version="1.0" encoding="UTF-8"?><project xmlns="/POM/4.0.0" xmlns:xsi="/2001/XMLSchema-instance"xsi:schemaLocation="/POM/4.0.0 /xsd/maven-4.0.0.xsd"><modelVersion>4.0.0</modelVersion><packaging>jar</packaging><!-- 配置文件没有生效将此处的pom改为jar就生效了--><!--<packaging>jar</packaging>--><parent><groupId>org.springframework.boot</groupId><artifactId>spring-boot-starter-parent</artifactId><version>2.0.0.RELEASE</version><relativePath/> <!-- lookup parent from repository --></parent><groupId>com.example</groupId><artifactId>readinglist</artifactId><version>1.0-SNAPSHOT</version><name>readinglist</name><description>Demo project for Spring Boot</description><properties><java.version>1.8</java.version><mybatis-plus.version>3.0.7</mybatis-plus.version></properties><dependencies><dependency><groupId>com.oracle</groupId><artifactId>ojdbc6</artifactId><version>11.2.0.3</version></dependency><!-- MYBATIS PLUS --><dependency><groupId>com.baomidou</groupId><artifactId>dynamic-datasource-spring-boot-starter</artifactId><version>3.2.1</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus</artifactId><version>${mybatis-plus.version}</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>${mybatis-plus.version}</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-annotation</artifactId><version>${mybatis-plus.version}</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-extension</artifactId><version>${mybatis-plus.version}</version></dependency><dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-generator</artifactId><version>${mybatis-plus.version}</version></dependency><dependencies><build><plugins><plugin><groupId>org.springframework.boot</groupId><artifactId>spring-boot-maven-plugin</artifactId></plugin></plugins><!-- 这个配置很关键，编译的时候不会漏掉xml文件--><resources><resource><directory>src/main/java</directory><includes><include>**/*.xml</include></includes><filtering>false</filtering></resource><resource><directory>src/main/resources</directory><filtering>false</filtering></resource></resources></build></project>
server:port: 8080spring:#mysql数据源配置datasource:dynamic:druid:validation-query: SELECT 1 FROM DUALprimary: db1datasource:# 强烈注意：Spring Boot 2.X 版本不再支持配置继承，多数据源的话每个数据源的所有配置都需要单独配置，否则配置不会生效db1:driver-class-name: com.mysql.jdbc.Driverurl: jdbc:mysql://localhost:3306/test?useSSL=false&useUnicode=true&characterEncoding=utf-8username: rootpassword: 123456db2:driver-class-name: oracle.jdbc.OracleDriverurl: jdbc:oracle:thin:@localhost:1521:orclusername: systempassword: 123456jpa:hibernate:ddl-auto: updateshow-sql: true#kotime配置profiles:active: koTimekoTime:log:enable: false # 是否开启控制台输出，非必填，默认falselanguage: chinese # 控制台输出语言（english/chinese）非必填，默认chinesetime:threshold: 800.0 # 时间阈值，用于前端展示，大于阈值显示红色，小于阈值显示绿色，非必填，默认800#redis配置redis:database: 0lettuce:pool:max-active: 8max-wait: -1msmax-idle: 8min-idle: 0host: localhostport: 6379password: 123456#mybatis数据库操作配置文件,缺少这个找不到xml文件mybatis-plus:mapper-locations: springboot/dao/mapping/*.xmlconfiguration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
package springboot;import org.mybatis.spring.annotation.MapperScan;import org.springframework.boot.SpringApplication;import org.springframework.boot.autoconfigure.SpringBootApplication;import org.springframework.scheduling.annotation.EnableAsync;//@EnableScheduling@SpringBootApplication@EnableAsync //使用异步注解@Async 需要在这里加上@EnableAsync@MapperScan("springboot.dao") //不可或缺作用是扫描dao包下面的所有mapper装配public class HelloApplication {public static void main(String[] args) {SpringApplication.run(HelloApplication.class,args);}}
TStudentMapper.java 通过@DS注解来选择数据源
package springboot.dao;import com.baomidou.dynamic.datasource.annotation.DS;import java.util.List;import java.util.Map;/*** <p>* 后台管理用户表 Mapper 接口* </p>** @author cruder* @since -07-16*/@DS("db2")public interface TStudentMapper{List<Map<String,Object>> test01(Map<String,Object> hashParam);}
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"><mapper namespace="springboot.dao.TStudentMapper"><select id="test01" parameterType="hashMap" resultType="hashMap">select student_id,student_name,speciality,grade,TO_CHAR(BIRTH_DAY,'yyyy-MM-dd') AS birthDay,agefrom t_student<if test="age!=null">where age = #{age}</if></select></mapper>
TUserMapper.ava
，不加@DS注解则使用默认数据库
package springboot.dao;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.baomidou.mybatisplus.core.mapper.BaseMapper;import com.baomidou.mybatisplus.core.metadata.IPage;import com.baomidou.mybatisplus.core.toolkit.Constants;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;import org.apache.ibatis.annotations.Param;import org.apache.ibatis.annotations.Select;import springboot.domain.TUser;import java.util.List;import java.util.Map;/*** <p>* 后台管理用户表 Mapper 接口* </p>** @author cruder* @since -07-16*/public interface TUserMapper extends BaseMapper<TUser> {List<TUser> findUser(@Param("tUser") TUser tUser,Integer startIndex,Integer endIndex);}
<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mapper PUBLIC "-////DTD Mapper 3.0//EN" "/dtd/mybatis-3-mapper.dtd"><mapper namespace="springboot.dao.TUserMapper"><select id="findUser" parameterType="springboot.domain.TUser" resultType="springboot.domain.TUser">select * from t_user<where><if test="tUser.username!=null and tUser.username!=''">username = #{tUser.username}</if></where><if test="startIndex!=null and endIndex!=null">limit #{startIndex},#{endIndex}</if></select></mapper>
package springboot.domain;import com.alibaba.excel.annotation.ExcelIgnore;import com.alibaba.excel.annotation.ExcelProperty;import com.baomidou.mybatisplus.annotation.TableField;import com.baomidou.mybatisplus.annotation.TableId;import com.baomidou.mybatisplus.extension.activerecord.Model;import lombok.Data;import lombok.EqualsAndHashCode;import lombok.experimental.Accessors;import java.util.Date;import java.util.Set;/*** <p>* 后台管理用户表* </p>** @author cruder* @since -07-16*/@Data@EqualsAndHashCode(callSuper = true)@Accessors(chain = true)public class TUser extends Model {private static final long serialVersionUID = 1L;/*** 账号*/@TableId@ExcelProperty("姓名")private String username;/*** 密码*/@ExcelProperty({"个人信息","密码"})private String password;/*** 创建时间*/@ExcelProperty({"个人信息","创建时间"})private Date createTime;/*** 更新时间*/@ExcelProperty("修改时间")private Date updateTime;/*** 表的后缀（年月) 按月分表*/@TableField(exist = false)@ExcelIgnoreprivate String date;/*** 用户对应的角色集合*/@ExcelIgnore@TableField(exist = false)private Set<Role> roles;}
测试接口
package springboot.controller;import com.alibaba.excel.EasyExcel;import com.alibaba.excel.ExcelWriter;import com.alibaba.excel.write.metadata.WriteSheet;import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;import com.baomidou.mybatisplus.extension.plugins.pagination.Page;import org.apache.poi.ss.usermodel.Row;import org.springframework.web.bind.annotation.*;import org.springframework.web.multipart.MultipartFile;import springboot.dao.TStudentMapper;import springboot.dao.TUserMapper;import springboot.domain.Person;import springboot.domain.TUser;import springboot.dto.DataRequestDTO;import springboot.dto.ResponseDTO;import test.entity.DataOutput;import javax.annotation.Resource;import javax.servlet.http.HttpServletResponse;import java.io.IOException;import java.io.UnsupportedEncodingException;import .URLEncoder;import java.util.Date;import java.util.HashMap;import java.util.List;import java.util.Map;@RestControllerpublic class TestController {@Resourceprivate TUserMapper tUserMapper;@Resourceprivate TStudentMapper tStudentMapper;@GetMapping("hhh")public ResponseDTO hhh(@RequestParam MultipartFile file){return ResponseDTO.success();}@GetMapping("test")public ResponseDTO test(TUser tUser){return ResponseDTO.success(tUserMapper.findUserName(new QueryWrapper<>(tUser).eq("username",tUser.getUsername())));}@CrossOrigin@GetMapping("test01")public ResponseDTO test01(TUser tUser,Integer startIndex,Integer endIndex){return ResponseDTO.success(tUserMapper.findUser(tUser,startIndex,endIndex));}@GetMapping("test02")public void test02(TUser tUser, HttpServletResponse response) throws IOException {List<TUser> tUserList = tUserMapper.findUser(tUser, 0, 10);response.setContentType("application/vnd.ms-excel");response.setCharacterEncoding("utf-8");// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName = URLEncoder.encode("测试", "UTF-8");response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), TUser.class).build();WriteSheet writeSheet = EasyExcel.writerSheet("汇总").build();// 分页去数据库查询数据 这里可以去数据库查询每一页的数据excelWriter.write(tUserList, writeSheet);// 千万别忘记finish 会帮忙关闭流excelWriter.finish();}@GetMapping("test03")public ResponseDTO test03(TUser tUser, Page<TUser> page){return ResponseDTO.success(tUserMapper.findUserPage(page,tUser));}@PostMapping("test04")public ResponseDTO test04(@RequestBody TUser tUser){tUser.setCreateTime(new Date());return ResponseDTO.success(tUserMapper.insert(tUser));}@PostMapping("test05")public ResponseDTO test05(@RequestBody Map<String,Object> param){List<Map<String, Object>> maps = tStudentMapper.test01(param);// List<Map<String, Object>> maps2 = tUserMapper.findMap(param);return ResponseDTO.success(maps);}}
