オラクル SQL 予約日重複チェックについて

Question

現在ある予約管理システムを作成しております（MVCモデル）

選択された予約from年月日〜予約to年月日を、
バインド変数を使用し、DBに登録されているデータと重複していないかチェックしたいと思っております。

SQLのtableは

CREATE TABLE RESERVE(
STARTDAY  DATE,
ENDDAY  DATE,
ROOMCODE  CHAR(2),
PEOPLE  NUMBER(2),
BASS  CHAR(1),
REPRESENTATIVE  VARCHAR(20),
ID  CHAR(9),
PRIMARY KEY(ID),
);

上記のように作成しております。

こちらを元に、jspファイルから入力された日付の重複チェックをdaoクラスで行います。
重複していたらCOUNTが１以上を返すようにしたいのですが、
重複していても０を返してしまいます。

作成したSQL文は下記です

String SELECT_COUNT_QUERY = "

SELECT COUNT(*) AS COUNT FROM RESERVE
WHERE ROOMCODE=?(ID)
AND ((STARTDAY <= ?(from年月日) 
AND ?(from年月日) < ENDDAY)
AND (STARTDAY < ?(to年月日) 
AND ?(to年月日) <= ENDDAY))

";

daoクラスで作成した重複チェックソースは下記です

public boolean isCheckOverlap(Connection con,String fromYMD,String toYMD,String roomCode) throws SQLException{

  //roomCodeにはID,fromYMD,toYMDにはyyyy/mm/dd形式の年月日が入っています

  //重複チェック変数
  Integer overlap = null;

  //判定変数
  boolean flag = true;

try{

    //重複チェックSQL文をプリコンパイル
    PreparedStatement ps = con.preparedStatement(SELECT_COUNT_QUERY);

    //バインド変数に値をセット
    ps.setString(1,roomCode);
    ps.setString(2,fromYMD);
    ps.setString(3,fromYMD);
    ps.setString(4,toYMD);
    ps.setString(5,toYMD);

    //セレクトの結果を受け取る
   ResultSet rs = ps.executeQuery();
    
    //最初の行を呼び出す  
    rs.next();

    //COUNTを取得
    overlap = rs.getInt("COUNT");

    //COUNTの結果が１以上ならfalseを返す
    if(overlap >= 1){
   
      flag = false;
    }

}catch(SQLException e){
  throw e;
}finally{
  try{
    if(rs != null)rs.close();
    if(ps != null)ps.close();
  }catch(SQLException e){
    e.printStackTrace();
  }
}
return flag;
}

上記で作成しましたが、重複したデータでもtrueになります。

SQL*PLUSに直接from年月日に'2016/08/11'、to年月日に'2016/08/14'と日付を入力するとちゃんとCOUNT数を返してくるため、

SELECT COUNT(*) AS COUNT FROM RESERVE
WHERE ROOMCODE=?
AND ((STARTDAY <= TO_DATE(?,'yyyy/mm/dd')
AND TO_DATE(?,'yyyy/mm/dd') < ENDDAY)
AND (STARTDAY < TO_DATE(?,'yyyy/mm/dd')
AND TO_DATE(?,'yyyy/mm/dd') <= ENDDAY));

等修正してみましたが、やはり重複していてもtrueを返してしまいます・・

SQL文から間違っていますでしょうか？

大変お手数ですが、アドバイス頂けると幸いです。

どうぞよろしくお願い致します。

Answer 1

OracleSQLパズル 10-226 OverLaps述語
https://oraclesqlpuzzle.ninja-web.net/10-226.html
を見ながら作ってみました :-)

select count(*)
  from RESERVE
 where STARTDAY <= toYMD
   and fromYMD  <= ENDDAY;

この不等式は、ゲームプログラミンングでの
長方形同士の当たり判定でも使われるものです。

ブール代数を使って、下記のように同値変形すれば一致します。

   (fromYMD BETWEEN STARTDAY AND ENDDAY)
OR (toYMD BETWEEN STARTDAY AND ENDDAY)
OR (STARTDAY > fromYMD AND ENDDAY < toYMD)

   (STARTDAY <= fromYMD and fromYMD <= ENDDAY)
OR (STARTDAY <= toYMD AND toYMD <= ENDDAY)
OR (fromYMD < STARTDAY AND ENDDAY < toYMD)

A STARTDAY <= fromYMD
B fromYMD <= ENDDAY
C STARTDAY <= toYMD
D toYMD <= ENDDAY
E fromYMD < STARTDAY
F ENDDAY < toYMD
として

AならばC
DならばB
EならばB
FならばC
Aの否定がE
Dの否定がF
を使って

 A*B + C*D + E*F
=A*B*C + B*C*D + B*E*C*F
=B*C*(A+D+E*F)
=B*C*(A+D+Not(A)*Not(D))
=B*C*(A+D+Not(A+D))
=B*C

Comments

[noririn]

AketiJyuuzou様

ご回答ありがとうございます！

またコードまでご丁寧に書いてくださりありがとうございます。

すみません、少し理解をするのに時間がかかっていまして・・＾＾；

じっくり拝見しながら考えたいと思います・・・！

本当にありがとうございます＾O＾

Answer 2

質問者さんのおっしゃる重複の定義が、
予約したい期間内に別途予約された日付期間が含まれている状態を指すのでしたら、
以下の考え方でいけるかと思われます。

別解もあるのですが、
期間・範囲の重複を判断する場合の一番単純な考え方は以下の3条件のいずれかを満たすことを確認します。

1. 登録したいデータの開始点を含むデータが他に登録されている場合は重複
2. 登録したいデータの終了点を含むデータが他に登録されている場合は重複
3. 登録したいデータの開始点と終了点が他に登録されているデータの開始・終了点を含む場合は重複

これをSQLで正直に表すと下記イメージになります。

(fromYMD BETWEEN STARTDAY AND ENDDAY) -- 開始日含む
OR (toYMD BETWEEN STARTDAY AND ENDDAY) -- 終了日含む
OR (STARTDAY > fromYMD AND ENDDAY < toYMD) -- 別データの開始・終了日を含む

ちなみにこの条件により取得されたデータ件数が1以上は、
重複ありとなります。(isCheckOverlapではfalseを返すことになります)

###追記
SQLPlusで想定通り動作するけど、
PHPで想定通りとならないケースというのは、

1. バインドパラメタ(?に設定する値)が想定と異なる値を設定している
2. SQLの記述方法に差異がある

上記のどちらかとなるので、その観点でプログラムを確認してみて下さい。

1つ目の着眼点はsetStringなどで値を設定している箇所で、
デバッグ機能を利用し想定した値が変数に設定されているかの確認しましょう。

2つ目の着眼点はJava上でのSQLの記載と、
SQLPlusで実行したSQLの差分を比較してみましょう。
パラメタを「?」としている箇所以外で大きな差分があれば、
そこが実行結果が異なる要因となっている箇所と推測できます。

また重複条件の判定について文面だけだとわかり辛いので、
追加でアドバイスをば。

こちらは質問者さんなりに重複条件を考えていらっしゃると思いますが、
どうしてもプログラミングのみ(頭内で考えるだけ)でわからない場合は、
一度EXCELや紙などにどういったケースが重複となるかを書き出すと整理しやすいです。

例えば以下のように書き出すと、
どのように条件を付ける必要があるか見えてきませんか。

A.今回登録予定の予約
A'.登録済の予約

①A1(開始日)はA'1とA'2に含まれる
  A |----------|
    A1         A2
A'|-----|
  A'1   A'2

②A2(終了日)はA'1とA'2に含まれる
  A |----------|
    A1         A2
           A'|-----|
            A'1   A'2

③A1、A2はA'1、A'2を含んでいる
  A |---------------|
    A1              A2
       A'|-----|
        A'1   A'2

Comments

[noririn]

KotoriMaturi様

ご回答頂きましてありがとうございます。

御礼が遅くなり申し訳ありません。

またこんなにご丁寧にありがとうございます。

再度確認してみます！

取り急ぎ御礼まで・・

本当にありがとうございました。

Answer 3

? BETWEEN A AND B
OR
? BETWEEN C AND D

かな？

Comments

[退会済みユーザー]

同様の意味をそのクエリに当てはめるなら３つ目のandをorに

[noririn]

haruka-kanata様

ご回答ありがとうございます。

勉強になります・・

再度確認してみます。

本当にありがとうございました