当前使用版本
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>3.4.2</version>
</dependency>
该问题是如何引起的？(确定最新版也有问题再提!!!)
controller层调用service层的page(E page,Wrapper<T> queryWrapper);方法出现分页时sql语句错误
重现步骤(如果有就写完整)
1.出现这个错误的实体类Employee
@TableName(value ="employee")
@Data
public class Employee implements Serializable {
/**
* 主键
*/
@TableId
private Long id;
/**
* 姓名
*/
private String name;
/**
* 用户名
*/
private String username;
/**
* 密码
*/
private String password;
/**
* 手机号
*/
private String phone;
/**
* 性别
*/
private String sex;
/**
* 身份证号
*/
private String idNumber;
/**
* 状态 0:禁用，1:正常
*/
private Integer status;
/**
* 创建时间
*/
@DateTimeFormat(pattern = "yyyy-MM-dd")//前端给后端的值
@JSONField(format = "yyyy-MM-dd")//存到数据库的值
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")//后端给前端的值
private LocalDateTime createTime;
/**
* 更新时间
*/
@DateTimeFormat(pattern = "yyyy-MM-dd")//前端给后端的值
@JSONField(format = "yyyy-MM-dd")//存到数据库的值
@JsonFormat(pattern = "yyyy-MM-dd",timezone = "GMT+8")//后端给前端的值
private LocalDateTime updateTime;
/**
* 创建人
*/
private Long createUser;
/**
* 修改人
*/
private Long updateUser;
@TableField(exist = false)
private static final long serialVersionUID = 1L;
2.我创建了spring的配置文件,里面注入了LambdaQueryWrapper
@Configuration
public class SpringConfig {
/**
* Employee的LambdaQueryWrapper
* @return
*/
@Bean
public LambdaQueryWrapper<Employee> lambdaQueryWrapper(){
return new LambdaQueryWrapper<>();
}
}
2.Mybatisplus的拦截器
@Configuration
public class MybatisPlusConfig {
@Bean
public MybatisPlusInterceptor mybatisPlusInterceptor(){
MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
return interceptor;
}
}
3.controller层注入service层的Bean(使用MybatisX插件生成的entity,mapper,service)
@Slf4j
@RestController
@RequestMapping("employee")
public class EmployeeController {
@Autowired
private EmployeeService employeeService;
@Autowired
private LambdaQueryWrapper<Employee> queryWrapper;
@GetMapping("page")
public R<Page> page(Integer page, Integer pageSize,String name){
log.error("page->{},pageSize->{},name->{}",page,pageSize,name);
//控制台正常输出了参数
Page pageInfo = new Page(page,pageSize);
queryWrapper.like(StringUtils.isNotBlank(name),Employee::getName,name);
employeeService.page(pageInfo,queryWrapper);
queryWrapper.clear();
return R.success(pageInfo);
//R 是我定义的统一返回结果
}
}
4.controller写好后我启动项目,访问页面来调用接口，然后控制台出现sql拼接错误
5.创建的test能够正常调用方法得到返回结果
@Slf4j
@SpringBootTest
class ReggieApplicationTests {
@Autowired
private EmployeeService employeeService;
@Autowired
private LambdaQueryWrapper<Employee> queryWrapper;
@Test
void contextLoads() {
Page pageInfo = new Page(1,10);
queryWrapper.like(StringUtils.isNotBlank(null),Employee::getName,null);
employeeService.page(pageInfo,queryWrapper);
}
}
test时控制台输出
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3e0fbeb5] was not registered for synchronization because synchronization is not active
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@10947c4e] will not be managed by Spring
==> Preparing: SELECT COUNT(*) FROM employee
==> Parameters:
<== Columns: COUNT(*)
<== Row: 2
<== Total: 1
==> Preparing: SELECT id,name,username,password,phone,sex,id_number,status,create_time,update_time,create_user,update_user FROM employee LIMIT ?
==> Parameters: 10(Long)
<== Columns: id, name, username, password, phone, sex, id_number, status, create_time, update_time, create_user, update_user
<== Row: 1, 管理员, admin, e10adc3949ba59abbe56e057f20f883e, 13812312312, 1, 110101199001010047, 1, 2021-05-06 17:20:07, 2021-05-10 02:24:09, 1, 1
<== Row: 1578326041997930498, 张三, zhangsan, e10adc3949ba59abbe56e057f20f883e, 18062916353, 1, 123456789012345678, 1, 2022-10-07 18:07:07, 2022-10-07 18:07:07, 1, 1
<== Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@3e0fbeb5]
报错信息
第一行的ERROR是controller中使用的log.error();打印的参数信息
2022-10-07 21:45:45.815 ERROR 11284 --- [p-nio-80-exec-9] c.s.r.controller.EmployeeController : page->1,pageSize->10,name->null
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5fb6fc55] was not registered for synchronization because synchronization is not active
optimize this sql to a count sql has exception, sql:"SELECT id,name,username,password,phone,sex,id_number,status,create_time,update_time,create_user,update_user FROM employee
(username = ?)", exception:
net.sf.jsqlparser.parser.ParseException: Encountered unexpected token: "(" "("
at line 5, column 2.
Was expecting one of:
","
";"
"ACTION"
"ANY"
"APPLY"
"AS"
"BYTE"
"CASCADE"
"CAST"
"CHANGE"
"CHAR"
"CHARACTER"
"COLUMN"
"COLUMNS"
"COMMENT"
"COMMIT"
"CONNECT"
"CROSS"
"CYCLE"
"DESC"
"DESCRIBE"
"DISABLE"
"DIV"
"DO"
"DUPLICATE"
"ENABLE"
"END"
"EXCEPT"
"EXCLUDE"
"EXTRACT"
"FALSE"
"FIRST"
"FN"
"FOLLOWING"
"FOR"
"FORMAT"
"FULL"
"GROUP"
"HAVING"
"INDEX"
"INNER"
"INSERT"
"INTERSECT"
"INTERVAL"
"ISNULL"
"JOIN"
"KEY"
"LAST"
"LEFT"
"MATERIALIZED"
"MINUS"
"NATURAL"
"NEXTVAL"
"NO"
"NOLOCK"
"NULLS"
"OF"
"OPEN"
"ORDER"
"OUTER"
"OVER"
"PARTITION"
"PATH"
"PERCENT"
"PIVOT"
"PRECISION"
"PRIMARY"
"PRIOR"
"RANGE"
"READ"
"REPLACE"
"RIGHT"
"ROW"
"ROWS"
"SCHEMA"
"SEPARATOR"
"SEQUENCE"
"SESSION"
"SIBLINGS"
"SIZE"
"START"
"STRAIGHT_JOIN"
"TABLE"
"TEMP"
"TEMPORARY"
"TO"
"TOP"
"TRUE"
"TRUNCATE"
"TYPE"
"UNION"
"UNSIGNED"
"VALIDATE"
"VALUE"
"VALUES"
"VIEW"
"WHERE"
"WINDOW"
"XML"
"ZONE"
<EOF>
<K_DATETIMELITERAL>
<K_DATE_LITERAL>
<S_CHAR_LITERAL>
<S_IDENTIFIER>
<S_QUOTED_IDENTIFIER>
JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@3e8e0c1a] will not be managed by Spring
==> Preparing: SELECT COUNT(*) FROM (SELECT id,name,username,password,phone,sex,id_number,status,create_time,update_time,create_user,update_user FROM employee (username = ?)) TOTAL
==> Parameters: null
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@5fb6fc55]
2022-10-07 21:45:45.820 INFO 11284 --- [p-nio-80-exec-9] c.s.r.exception.GlobalExceptionHandle : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(username = null)) TOTAL' at line 5
然后spring管理的sqlsession出现生成sql语句查询,这里就出现了问题：optimize this sql to a count sql has exception
最后还有一行还有一个报错信息:2022-10-07 21:45:45.820 INFO 11284 --- [p-nio-80-exec-9] c.s.r.exception.GlobalExceptionHandle : You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(username = null)) TOTAL' at line 5
最后一行的报错信息是我自定义的ExceptionHandler处理了一部分异常,但根据报错信息来看是生成sql语句时出现了什么错误:
@ControllerAdvice(annotations = {Controller.class, RestController.class})
@ResponseBody
@Slf4j
public class GlobalExceptionHandler {
@ExceptionHandler(SQLException.class)
public R<String> exceptionHandler(SQLException e){
String message = e.getMessage();
// log.info(message);
if (message.contains("Duplicate entry")){// 这是数据库存中唯一性字段(我的数据库中是name)判断
String[] split = message.split("'");
String msg = split[1] +"已存在";
return R.error(msg);// 返回信息
}
return R.error("未知错误");
}
}
具体原因是什么呢？test的时候可以正常查询，controller调用就出现了问题
Comment From: Sherlock-Shock
我的3.5.2 也出现这问题，楼主解决了吗
Comment From: qmdx
count sql 插件无法自动优化 page 传入对象关闭优化即可 optimizeCountSql 这个参数设置 false
Comment From: miemieYaho
wrapper 是用来new的,不是让你加入ioc循环使用的
Comment From: devilfish110
我的3.5.2 也出现这问题，楼主解决了吗
emm···这个问题是我把wrapper交给spring管理了，然后i出现的问题，wrapper只能new然后使用，不过感觉每个方法中使用wrapper时都要new又有点重复的样子。不知道有没有什么解决的办法