java.time.DayOfWeekでmysqlに曜日検索かけるとハマった |

こんにちは、javaでの曜日検索でハマったので解決策のご紹介です。

環境

Java8
MySQL5.6.38

現象

java.time.DayOfWeekを使ってmysqlの日付型のデータを検索かけに行くとうまく取得できない。
土曜、日曜で検索かけると、なぜか土曜のデータしか取得できなくてハマる。

// cbははJPAのCriteria API、CriteriaBuilderのインスタンスで、
// mysqlのdayofweek関数を実行しています。

List<DayOfWeek> weeks = Arrays.asList(DayOfWeek.SATURDAY, DayOfWeek.SUNDAY);

return cb.function("DAYOFWEEK", DayOfWeek.class, date)
        .in(weeks.stream().map(DayOfWeek::getValue)
        .collect(Collectors.toList()));

// cbははJPAのCriteria API、CriteriaBuilderのインスタンスで、

// mysqlのdayofweek関数を実行しています。

List<DayOfWeek> weeks = Arrays.asList(DayOfWeek.SATURDAY, DayOfWeek.SUNDAY);

return cb.function("DAYOFWEEK", DayOfWeek.class, date)

.in(weeks.stream().map(DayOfWeek::getValue)

.collect(Collectors.toList()));

原因

DayOfWeek::getValueで取得できるcodeの番号と、mysqlのdayofweek関数の戻り値の番号がずれていました。

weekday関数と、DayOfWeek::ordinalを使用すれば順番が合うのでそのように修正します。

dayofweek(date)の実行結果

mysql> select *
    -> from
    -> (
    ->   select dayofweek('2018-01-15') as monday,
    ->          dayofweek('2018-01-16') as tuesday,
    ->          dayofweek('2018-01-17') as wednesday,
    ->          dayofweek('2018-01-18') as thursday,
    ->          dayofweek('2018-01-19') as fryday,
    ->          dayofweek('2018-01-20') as saturday,
    ->          dayofweek('2018-01-21') as sunday
    -> ) as a;
+--------+---------+-----------+----------+--------+----------+--------+
| monday | tuesday | wednesday | thursday | fryday | saturday | sunday |
+--------+---------+-----------+----------+--------+----------+--------+
|      2 |       3 |         4 |        5 |      6 |        7 |      1 |
+--------+---------+-----------+----------+--------+----------+--------+
1 row in set (0.01 sec)

mysql> select *

-> from

-> (

-> select dayofweek('2018-01-15') as monday,

-> dayofweek('2018-01-16') as tuesday,

-> dayofweek('2018-01-17') as wednesday,

-> dayofweek('2018-01-18') as thursday,

-> dayofweek('2018-01-19') as fryday,

-> dayofweek('2018-01-20') as saturday,

-> dayofweek('2018-01-21') as sunday

-> ) as a;

+--------+---------+-----------+----------+--------+----------+--------+

+--------+---------+-----------+----------+--------+----------+--------+

| 2 | 3 | 4 | 5 | 6 | 7 | 1 |

+--------+---------+-----------+----------+--------+----------+--------+

1 row in set (0.01 sec)

weekday(date)の実行結果

mysql> select *
 -> from
 -> (
 -> select weekday('2018-01-15') as monday,
 ->        weekday('2018-01-16') as tuesday,
 ->        weekday('2018-01-17') as wednesday,
 ->        weekday('2018-01-18') as thursday,
 ->        weekday('2018-01-19') as fryday,
 ->        weekday('2018-01-20') as saturday,
 ->        weekday('2018-01-21') as sunday
 -> ) as a;
+--------+---------+-----------+----------+--------+----------+--------+
| monday | tuesday | wednesday | thursday | fryday | saturday | sunday |
+--------+---------+-----------+----------+--------+----------+--------+
|      0 |       1 |         2 |        3 |      4 |        5 |      6 |
+--------+---------+-----------+----------+--------+----------+--------+
1 row in set (0.00 sec)

mysql> select *

-> from

-> (

-> select weekday('2018-01-15') as monday,

-> weekday('2018-01-16') as tuesday,

-> weekday('2018-01-17') as wednesday,

-> weekday('2018-01-18') as thursday,

-> weekday('2018-01-19') as fryday,

-> weekday('2018-01-20') as saturday,

-> weekday('2018-01-21') as sunday

-> ) as a;

+--------+---------+-----------+----------+--------+----------+--------+

+--------+---------+-----------+----------+--------+----------+--------+

| 0 | 1 | 2 | 3 | 4 | 5 | 6 |

+--------+---------+-----------+----------+--------+----------+--------+

1 row in set (0.00 sec)

修正は下記で、date型のデータからの曜日検索がうまくいきました。

return cb.function("WEEKDAY", DayOfWeek.class, date)
        .in(weeks.stream().map(DayOfWeek::ordinal)
        .collect(Collectors.toList()));

return cb.function("WEEKDAY", DayOfWeek.class, date)

.in(weeks.stream().map(DayOfWeek::ordinal)

.collect(Collectors.toList()));