Mybatis Plus复杂条件构造

要求

将sql语句中where条件部分用mybatis plus的条件构造器构造出来。

SELECT t.id,t.department_id,t.program_name,t.contact_phone,t.contact_name,t.production_form,t.tv_platform,t.online_platform,t.workplace,t.workplace_name,t.second_workplace,t.departure_time,t.scenery_time,t.recording_time,t.rehearsal_time,t.end_time,t.dress_rehearsal_time,t.vehicle_used,t.recording_format,t.equipment,t.communication_channels,t.review_status,t.applicant_user_id,t.broadcast_platform,t.security,t.signalling_mode,t.attendances,t.json_expands,t.created_at,t.updated_at,t.is_del,t1.department_id AS joina_department_id,t1.department_name,t1.created_at AS joina_created_at,t1.updated_at AS joina_updated_at,t2.user_id,t2.username,t2.password,t2.phone,t2.department_id AS joinb_department_id,t2.role_id,t2.created_at AS joinb_created_at,t2.updated_at AS joinb_updated_at,t3.id AS joina_id,t3.location_name,t3.color,t3.remake1,t3.remake2,t3.remake3,t3.remake4,t3.remake5,t3.created_at AS joinc_created_at,t3.updated_at AS joinc_updated_at 
FROM broadcast_schedule t 
LEFT JOIN departments t1 ON (t1.department_id = t.department_id) 
LEFT JOIN users t2 ON (t2.user_id = t.applicant_user_id) 
LEFT JOIN workplace t3 ON (t3.id = t.workplace) 
WHERE   (t.is_del = 0) AND (t.recording_time >= '2024-06-30T00:00' OR t.recording_time <= '2024-08-03T23:59:59') OR (t.scenery_time >= '2024-06-30T00:00' OR t.scenery_time <= '2024-08-03T23:59:59') OR (t.dress_rehearsal_time >= '2024-06-30T00:00' OR t.dress_rehearsal_time <= '2024-08-03T23:59:59') OR (t.rehearsal_time >= '2024-06-30T00:00' OR t.rehearsal_time <= '2024-08-03T23:59:59')
ORDER BY t.recording_time ASC

开始构造

先将where条件分成4部分，并且分别用条件构造器表示。

t.is_del = 0

qw.eq(BroadcastSchedule::getIsDel, 0)

t.recording_time >= '2024-06-30T00:00' OR t.recording_time <= '2024-08-03T23:59:59'

qw.ge(BroadcastSchedule::getRecordingTime, startTime).or().le(BroadcastSchedule::getRecordingTime, endTime)

t.scenery_time >= '2024-06-30T00:00' OR t.scenery_time <= '2024-08-03T23:59:59'

qw -> qw.ge(BroadcastSchedule::getSceneryTime, startTime).or().le(BroadcastSchedule::getSceneryTime, endTime)

t.dress_rehearsal_time >= '2024-06-30T00:00' OR t.dress_rehearsal_time <= '2024-08-03T23:59:59'

qw -> qw.ge(BroadcastSchedule::getDressRehearsalTime,startTime).or().le(BroadcastSchedule::getDressRehearsalTime, endTime)

最后，将上面的每部分组合成一个整体。

qw.eq(BroadcastSchedule::getIsDel, 0).nested(qw -> qw.ge(BroadcastSchedule::getRecordingTime, startTime).or().le(BroadcastSchedule::getRecordingTime, endTime))
                    .or(qw -> qw.ge(BroadcastSchedule::getSceneryTime, startTime).or().le(BroadcastSchedule::getSceneryTime, endTime))
                    .or(qw -> qw.ge(BroadcastSchedule::getDressRehearsalTime,startTime).or().le(BroadcastSchedule::getDressRehearsalTime, endTime))
                    .or(qw -> qw.ge(BroadcastSchedule::getRehearsalTime, startTime).or().le(BroadcastSchedule::getRehearsalTime, endTime));