select * from zdyjkzbjl where jsbh='330400111' and TO_CHAR( pbrq , 'yyyy-mm-dd')='2023-05-23' and bc ='1' and TO_CHAR( jbsj , 'yyyy-mm-dd hh24:MI')<>'2023-05-23 07:50' and TO_CHAR( jiaobsj , 'yyyy-mm-dd hh24:MI')<>'2023-05-23 19:50' and TO_CHAR( now() , 'yyyy-mm-dd hh24:MI:SS') BETWEEN '2023-05-23 07:50:00' and '2023-05-23 17:00:00'; --一班次 23/07- 23/17 提醒时间 select * from zdyjkzbjl where jsbh='330400111' and TO_CHAR( pbrq , 'yyyy-mm-dd')='2023-05-23' and bc ='2' and TO_CHAR( jbsj , 'yyyy-mm-dd hh24:MI')='2023-05-23 19:50' and TO_CHAR( jiaobsj , 'yyyy-mm-dd hh24:MI')='2023-05-24 01:50' and TO_CHAR( now(), 'yyyy-mm-dd hh24:MI:SS') BETWEEN '2023-05-23 17:00:00' and '2023-05-24 01:30:00'; --二班次 23/17 -24/01 提醒时间 select * from zdyjkzbjl where jsbh='330400111' and TO_CHAR( pbrq , 'yyyy-mm-dd')='2023-05-23' and bc='3' and TO_CHAR( jbsj , 'yyyy-mm-dd hh24:MI')<>'2023-05-24 01:50' and TO_CHAR( jiaobsj , 'yyyy-mm-dd hh24:MI')<>'2023-05-24 07:50' and TO_CHAR( now() , 'yyyy-mm-dd hh24:MI:SS') BETWEEN '2023-05-24 01:30:00' and '2023-05-24 07:50:00'; 如何根据时间来分别调用其中的一个sql ,使用java 和mybatis sql 里面的日期根据当天日期传入 使用mybatis pbrq 和jbsj jiaobsj 和now() 后面的时间根据当天日期来判断
时间: 2023-11-22 21:05:27 浏览: 57
可以使用Java中的LocalDate类获取当天日期,然后将日期格式化为字符串,最后将字符串传入Mybatis的参数中。
示例代码:
```java
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
// 获取当天日期并格式化成字符串
LocalDate now = LocalDate.now();
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
String today = now.format(formatter);
// 构造Mybatis参数
Map<String, Object> params = new HashMap<>();
params.put("jsbh", "330400111");
params.put("today", today);
// 根据时间段调用不同的SQL语句
LocalTime time = LocalTime.now();
if (time.isBefore(LocalTime.of(7, 50))) {
params.put("bc", "1");
// 执行第一个SQL语句
List<YourEntity> result = sqlSession.selectList("yourMapper.selectByTime1", params);
} else if (time.isBefore(LocalTime.of(17, 0))) {
params.put("bc", "2");
// 执行第二个SQL语句
List<YourEntity> result = sqlSession.selectList("yourMapper.selectByTime2", params);
} else {
params.put("bc", "3");
// 执行第三个SQL语句
List<YourEntity> result = sqlSession.selectList("yourMapper.selectByTime3", params);
}
```
然后在Mybatis的mapper文件中,使用类似如下的SQL语句来查询:
```xml
<!-- 第一个SQL语句 -->
<select id="selectByTime1" parameterType="java.util.Map" resultType="YourEntity">
select *
from zdyjkzbjl
where jsbh = #{jsbh}
and to_char(pbrq, 'yyyy-mm-dd') = #{today}
and bc = #{bc}
and to_char(jbsj, 'yyyy-mm-dd hh24:MI') <> '2023-05-23 07:50'
and to_char(jiaobsj, 'yyyy-mm-dd hh24:MI') <> '2023-05-23 19:50'
and to_char(now(), 'yyyy-mm-dd hh24:MI:SS') between '2023-05-23 07:50:00' and '2023-05-23 17:00:00'
</select>
<!-- 第二个SQL语句 -->
<select id="selectByTime2" parameterType="java.util.Map" resultType="YourEntity">
select *
from zdyjkzbjl
where jsbh = #{jsbh}
and to_char(pbrq, 'yyyy-mm-dd') = #{today}
and bc = #{bc}
and to_char(jbsj, 'yyyy-mm-dd hh24:MI') = '2023-05-23 19:50'
and to_char(jiaobsj, 'yyyy-mm-dd hh24:MI') = '2023-05-24 01:50'
and to_char(now(), 'yyyy-mm-dd hh24:MI:SS') between '2023-05-23 17:00:00' and '2023-05-24 01:30:00'
</select>
<!-- 第三个SQL语句 -->
<select id="selectByTime3" parameterType="java.util.Map" resultType="YourEntity">
select *
from zdyjkzbjl
where jsbh = #{jsbh}
and to_char(pbrq, 'yyyy-mm-dd') = #{today}
and bc = #{bc}
and to_char(jbsj, 'yyyy-mm-dd hh24:MI') <> '2023-05-24 01:50'
and to_char(jiaobsj, 'yyyy-mm-dd hh24:MI') <> '2023-05-24 07:50'
and to_char(now(), 'yyyy-mm-dd hh24:MI:SS') between '2023-05-24 01:30:00' and '2023-05-24 07:50:00'
</select>
```