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
时间: 2023-08-12 11:07:42 浏览: 48
您可以使用Java中的Date或LocalDateTime类来获取当前时间,然后根据时间来判断调用哪个SQL语句。
假设您已经有了对应的Mapper接口和SQL语句,您可以按照以下步骤操作:
1. 在Mapper接口中定义三个方法,分别对应三个SQL语句:
```java
public interface ZdyjkzbjlMapper {
List<Zdyjkzbjl> selectOne(String jsbh);
List<Zdyjkzbjl> selectTwo(String jsbh);
List<Zdyjkzbjl> selectThree(String jsbh);
}
```
2. 在mybatis的配置文件中,将这三个方法与对应的SQL语句进行绑定:
```xml
<mapper namespace="com.example.mapper.ZdyjkzbjlMapper">
<select id="selectOne" resultType="com.example.entity.Zdyjkzbjl">
select * from zdyjkzbjl where jsbh=#{jsbh} and TO_CHAR(pbrq,'yyyy-mm-dd')=#{date} 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'
</select>
<select id="selectTwo" resultType="com.example.entity.Zdyjkzbjl">
select * from zdyjkzbjl where jsbh=#{jsbh} and TO_CHAR(pbrq,'yyyy-mm-dd')=#{date} 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'
</select>
<select id="selectThree" resultType="com.example.entity.Zdyjkzbjl">
select * from zdyjkzbjl where jsbh=#{jsbh} and TO_CHAR(pbrq,'yyyy-mm-dd')=#{date} 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'
</select>
</mapper>
```
3. 在Java代码中,获取当前时间并根据时间调用对应的方法:
```java
ZdyjkzbjlMapper mapper = sqlSession.getMapper(ZdyjkzbjlMapper.class);
String jsbh = "330400111";
String date = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
LocalDateTime now = LocalDateTime.now();
if (now.isBefore(LocalDateTime.of(2023, 5, 23, 7, 50))) {
List<Zdyjkzbjl> list = mapper.selectOne(jsbh, date);
// do something with the result
} else if (now.isBefore(LocalDateTime.of(2023, 5, 23, 17, 0))) {
List<Zdyjkzbjl> list = mapper.selectTwo(jsbh, date);
// do something with the result
} else if (now.isBefore(LocalDateTime.of(2023, 5, 24, 7, 50))) {
List<Zdyjkzbjl> list = mapper.selectThree(jsbh, date);
// do something with the result
}
```
请注意,这里假设您已经成功地建立了Mybatis的配置文件,并且已经创建了对应的实体类Zdyjkzbjl。另外,这里的时间判断仅供参考,请根据您的实际需求进行调整。