背景:使用EntityWrapper进行sql语句拼接时,遇到查询or条件,而且是多个,但orNew()方法并不能满足我的需求,也没找到什么合适的方法。
//目标sql语句 A and (B or C or D)
/*SELECT * FROM user_info
WHERE (ID = ?
AND ((START_TIME >= ? AND END_TIME <= ?)
OR (START_TIME <= ? AND END_TIME >= ?)
OR (START_TIME <= ? AND END_TIME <= ? AND END_TIME >= ?)
OR (START_TIME >= ? AND END_TIME >= ? AND START_TIME <= ?))
)*/
//错误代码
entityWrapper = new EntityWrapper<>();
entityWrapper.eq("ID",satCasePqReal.getPqId());
entityWrapper.andNew().ge("START_TIME",satCourtCasePqReqDTO.getStartTime()).le("END_TIME",satCourtCasePqReqDTO.getEndTime())
.orNew().le("START_TIME",satCourtCasePqReqDTO.getEndTime()).ge("END_TIME",satCourtCasePqReqDTO.getStartTime())
.orNew().le("START_TIME",satCourtCasePqReqDTO.getEndTime()).le("END_TIME",satCourtCasePqReqDTO.getStartTime()).ge("END_TIME",satCourtCasePqReqDTO.getStartTime())
.orNew().ge("START_TIME",satCourtCasePqReqDTO.getEndTime()).ge("END_TIME",satCourtCasePqReqDTO.getStartTime()).le("START_TIME",satCourtCasePqReqDTO.getEndTime());
/*最终sql语句:条件变成了 A and B or C or D,完全不符合我的需求
SELECT * FROM user_info
WHERE (ID = ?
and (START_TIME >= ? AND END_TIME <= ?)
OR (START_TIME <= ? AND END_TIME >= ?)
OR (START_TIME <= ? AND END_TIME <= ? AND END_TIME >= ?)
OR (START_TIME >= ? AND END_TIME >= ? AND START_TIME <= ?)
)
*/
//动了点小脑筋,不知道有没有更好的方法
entityWrapper = new EntityWrapper<>();
entityWrapper.eq("ID",satCasePqReal.getPqId());
entityWrapper.addFilter("((1<>1");
entityWrapper.orNew().ge("START_TIME",satCourtCasePqReqDTO.getStartTime()).le("END_TIME",satCourtCasePqReqDTO.getEndTime())
.orNew().le("START_TIME",satCourtCasePqReqDTO.getEndTime()).ge("END_TIME",satCourtCasePqReqDTO.getStartTime())
.orNew().le("START_TIME",satCourtCasePqReqDTO.getEndTime()).le("END_TIME",satCourtCasePqReqDTO.getStartTime()).ge("END_TIME",satCourtCasePqReqDTO.getStartTime())
.orNew().ge("START_TIME",satCourtCasePqReqDTO.getEndTime()).ge("END_TIME",satCourtCasePqReqDTO.getStartTime()).le("START_TIME",satCourtCasePqReqDTO.getEndTime());
entityWrapper.addFilter("1=1))");
/*最终结果:A and (1<>1 or B or C or D),勉强符合我的需求
SELECT * FROM user_info
WHERE (ID = ?
AND ((1<>1)
OR (START_TIME >= ? AND END_TIME <= ?)
OR (START_TIME <= ? AND END_TIME >= ?)
OR (START_TIME <= ? AND END_TIME <= ? AND END_TIME >= ?)
OR (START_TIME >= ? AND END_TIME >= ? AND START_TIME <= ? AND 1=1))
)
*/
最后,如果大家有更好的方法,麻烦请告诉我,蟹蟹~