Java SQL异常：java.sql.SQLException: Incorrect DATE value: ‘2021.02.31‘

报错信息：

 Cause: java.sql.SQLException: Incorrect DATE value: '2021.02.31'
; uncategorized SQLException; SQL state [HY000]; error code [1525]; Incorrect DATE value: '2021.02.31'; nested exception is java.sql.SQLException: Incorrect DATE value: '2021.02.31'

代码：

//根据月份统计会员数量
public List<Integer> findMemberCountByMonth(List<String> month) {
  List<Integer> list = new ArrayList<>();
  for(String m : month){
    m = m + ".31";//格式：2019.02.31
    Integer count = memberDao.findMemberCountBeforeDate(m);
    list.add(count);
  }
  return list;
}

产生错误的原因:
在MySQL 5+版本不会报错，但是在MySQL 8+版本上会报错。原因是MySQL 8+版本增加了日期校验，2月31显然是不合法的，因此报错。

解决办法：
需要把拼接字符串修改为获取指定日期的月份的最后一天：

    public List<Integer> findMemberCountByMonth(List<String> month) {
        List<Integer> list = new ArrayList<>();
        for (String m : month) {
            try {
                String date_str = m + "-10";
                Calendar calendar = Calendar.getInstance();
                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                calendar.setTime(sdf.parse(date_str));
                calendar.add(Calendar.MONTH, 1);
                calendar.set(Calendar.DAY_OF_MONTH, 0);
                // 获取当月最后一天
                String lastDayOfMonth = sdf.format(calendar.getTime());
                Integer count = memberDao.findMemberCountBeforeDate(lastDayOfMonth);
                list.add(count);
            } catch (ParseException e) {
                e.printStackTrace();
            }
        }
        return list;
    }

Java获取给定日期的月初和月末两个日期：

    @Test
    public void getFirstAndLastDayOfMonth() throws ParseException {
        String date = "2020-02-01";
        Calendar calendar = Calendar.getInstance();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        calendar.setTime(sdf.parse(date));
        calendar.add(Calendar.MONTH, 0);
        calendar.set(Calendar.DAY_OF_MONTH, 1);
        String firstDayOfMonth = sdf.format(calendar.getTime());
        calendar.add(Calendar.MONTH, 1);
        calendar.set(Calendar.DAY_OF_MONTH, 0);
        String lastDayOfMonth = sdf.format(calendar.getTime());
        System.out.println(firstDayOfMonth);
        System.out.println(lastDayOfMonth);
    }