【Java】【MySQL】数据库存储的日期是字符类型，查询指定日期时间区间是用数据库的方法STR_TO_DATE().

SELECT * from t_doc_info WHERE 1 = 1 
and (STR_TO_DATE(remind_day,'%Y-%m-%d') 
BETWEEN STR_TO_DATE('2021-12-21','%Y-%m-%d') and STR_TO_DATE('2021-12-21','%Y-%m-%d'))
and is_del = 0

public static void main(String[] args) {
  Date date = new Date();
  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  String startDay = sdf.format(date);
  Calendar ca = Calendar.getInstance();
  ca.add(Calendar.DATE, 7); // 当前计算即将过期是剩余7天内（包括）
  date = ca.getTime();
  String endDay = sdf.format(date);
  System.err.println("即将过期，过期时间在：" + startDay + "和" + endDay + "之间");
}

import org.springframework.jdbc.core.JdbcTemplate;
public class TestDemoServiceImpl{
  @Autowired
  private JdbcTemplate jdbcTemplate;
  public List<DocBean> getExpireDocList(String startDay, String endDay,
      String userIdListStr) {
    StringBuffer sb = new StringBuffer();
    sb.append("SELECT * from t_doc_info");
    sb.append(" WHERE 1 = 1");
    if (StringUtils.isNotBlank(userIdListStr)) {
      sb.append(" AND user_id IN(" + userIdListStr + ")");
    }
    if (StringUtils.isNotBlank(startDay) && StringUtils.isNotBlank(endDay)) 
                { // 指定过期时间期间（格式为：yyyy-MM-dd）STR_TO_DATE()字符转为时间类型
      sb.append("AND (STR_TO_DATE(expire_day,'%Y-%m-%d') BETWEEN STR_TO_DATE('" + startDay + "','%Y-%m-%d') and STR_TO_DATE('" + endDay + "','%Y-%m-%d'))");
    }
    sb.append(" AND is_del = 0");
    try {
      return jdbcTemplate.query(sb.toString(),
          new BeanPropertyRowMapper<DocBean>(DocBean.class));
    } catch (DataAccessException e) {
      e.printStackTrace();
      return null;
    }
  }
}

SELECT * from t_doc_info WHERE 1 = 1 
and (STR_TO_DATE(expire_day,'%Y-%m-%d') BETWEEN STR_TO_DATE('2021-12-21','%Y-%m-%d') and STR_TO_DATE('2021-12-21','%Y-%m-%d'))
and is_del = 0
and user_id in ('1001','1002','1003')