<sql id="Base_Column_List">
select
member_id,created_at,email,
is_active,join_type,nickname,
password,profile_image_id,refresh_token,
updated_at
from member
</sql>
<select id="SearchAllData" resultType="com.example.Entity.PO.Member">
<include refid="Base_Column_List"/>
<where>
<if test="uname !=null and uname !=''">
AND (
nickname like CONCAT('%',#{uname},'%')
OR email like CONCAT('%',#{uname},'%')
)
</if>
</where>
</select>
这里注意一些为什么不使用 因为MATCH必须设置全文索引，但是通过EXPLIN发现LINK也是ALL，即全文索引，但是LINK不用设置索引
MATCH (索引字段) AGAINST(值)
-
索引失效的常见的场景
- 索引列使用函数
- 隐式转换
- 左模糊查询
- OR不同列
- 联合索引不满足最左匹配原则
- 优化器认为全表扫描更快
最后有一个force index强行指定索引字段，但是不建议使用
-
我使用的是springBoot2，官网给出的版本不能进行分页查询
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.3.4</version>
</dependency>
做分页查询一定要设置
@MapperScan("com.example.mapper")
@Configuration
public class MyBatisConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor (new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
mapper层一定要设置mapper注解和repository注解方便mapperscan注解扫描到以及ComponetScan扫描到
@Mapper
@Repository
public interface MemberMapper extends BaseMapper<Member> {
// 泛型来自PO即数据DAO访问层
IPage<Member> SearchAllData(IPage<?> iPage,@Param("uname") String uname);
}
因为通过MybatisX生成的，所以不用担心继承了什么，直接用就行。
@Service
public class MemberServiceImpl extends ServiceImpl<MemberMapper, Member> implements MemberService{
@Autowired
private MemberMapper memberMapper;
// 这里注意返回类型一定来自VO，这是向前端返回数据的
@Override
public MemberResponseVO MemberService(String uname,long num) {
Page<Member> page = new Page<>(num,10);
memberMapper.SearchAllData(page,uname);
MemberResponseVO responseVO = new MemberResponseVO();
// 数据
responseVO.setDataList(page.getRecords());
// 总共多少条数据
responseVO.setSize(page.getSize());
// 一页多少条数据
responseVO.setTotal(page.getTotal());
// 当前页
responseVO.setCurrent(page.getCurrent());
return responseVO;
}
}
- springmvc 这个对DTO数据判空非常方便
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-validation</artifactId>
</dependency>
在requestbody注解上加一个Validated注解就行
@RequestMapping("/user")
@RestController
public class UserListController {
@Autowired
private MemberService memberService;
@PostMapping("/info")
public ResponseResult<MemberResponseVO> UserInfo(@Validated @RequestBody MemberDTO memberDTO){
MemberResponseVO responseVO = memberService.MemberService(memberDTO.getUname(), memberDTO.getCurrent());
return Result.Success(responseVO);
}
}
- 最后在DTO数据上加上相关注解就行
@NotBlank(message = "用户名不能为空")
private String uname;
@NotNull(message = "当前页不能为0")
@Min(value = 1,message = "页码必须大于1")
private Integer current;
- 然后全局拦截异常
@RestControllerAdvice
public class WebException {
@ExceptionHandler(MethodArgumentNotValidException.class)
public ResponseResult<String> BodyError(MethodArgumentNotValidException e){
String errorMessage = e.getBindingResult().getFieldErrors().stream().
map(fieldError -> fieldError.getField() + "字段:" + fieldError.getDefaultMessage())
.collect(Collectors.joining(","));
return Result.Error(errorMessage);
}
}
- 在使用拦截器的时候，preHandle是请求前，postHandle是请求后，afterCompletion是视图渲染完
public class RequestConfig implements HandlerInterceptor {
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
String authorization = request.getHeader("Authorization");
if(authorization == null){
response.setStatus(403);
response.setContentType("application/json");
response.setCharacterEncoding("UTF-8");
PrintWriter printWriter = response.getWriter();
ObjectMapper mapper = new ObjectMapper();
String asString = mapper.writeValueAsString(Result.Fail(StatusCode.FAIL));
printWriter.write(asString);
return false;
}
return true;
}
@Override
public void postHandle(HttpServletRequest request, HttpServletResponse response, Object handler, ModelAndView modelAndView) throws Exception {
}
@Override
public void afterCompletion(HttpServletRequest request, HttpServletResponse response, Object handler, Exception ex) throws Exception {
}
}
- 最后有一个knif4j的文档配置，也可以用componets/value方式注入到这些中
@Configuration
@EnableSwagger2WebMvc
public class SwaggerConfig {
private final OpenApiExtensionResolver openApiExtensionResolver;
@Autowired
public SwaggerConfig(OpenApiExtensionResolver openApiExtensionResolver){
this.openApiExtensionResolver=openApiExtensionResolver;
}
@Bean
public Docket defaultApi2(){
String groupName="2.x";
Docket docket = new Docket(DocumentationType.SWAGGER_2)
.host("http://10.147.239.179:9099")
.apiInfo(new ApiInfoBuilder()
.description("接口开发文档")
.contact("18565350849@163.com")
.title("接口文档")
.termsOfServiceUrl("http://127.0.0.1")
.version("1.0.0")
.license("apache 2.0")
.licenseUrl("/hell")
.build())
.groupName(groupName)
.select()
.apis(RequestHandlerSelectors.basePackage("com.example.Controller"))
.paths(PathSelectors.any())
.build()
.extensions(openApiExtensionResolver.buildExtensions(groupName));
return docket;
}
}
- 一定要在yml中标明，cors跨域没有测试
knife4j:
enable: true
cors: true
