SQL 多條件時間範圍查詢：字符串轉日期、WHERE 構建與常見坑點全解析

需求場景

前端傳遞 5 個查詢參數：起始時間、結束時間、地點、數據類型、車牌號。數據庫時間字段 JGSJ 為 DATE/TIMESTAMP 類型，前端傳字符串（如 2012/12/05 07:33:53）。

關鍵技術點

1. 字符串安全轉 java.sql.Timestamp：

   public static Timestamp toTimestamp(String str) {
       try {
           SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
           return new Timestamp(sdf.parse(str).getTime());
       } catch (ParseException e) {
           throw new IllegalArgumentException("時間格式錯誤: " + str, e);
       }
   }

2. 動態構建 WHERE 子句：使用 PreparedStatement 防注入，僅在參數非空時追加條件：

   StringBuilder sql = new StringBuilder("SELECT * FROM PASS_DATA WHERE 1=1");
   List<Object> params = new ArrayList<>();
   if (start != null) { sql.append(" AND JGSJ >= ?"); params.add(startTs); }
   if (end != null)   { sql.append(" AND JGSJ <= ?"); params.add(endTs); }
   if (area != null)  { sql.append(" AND AREA = ?"); params.add(area); }
   // ... 其餘參數同理

3. 避免精確匹配時間點：原查詢 JGSJ = to_date('2012/12/05','yyyy/mm/dd') 因時間精度到秒，幾乎無法命中。必須使用範圍查詢（= → BETWEEN 或 >= AND <=）。

完整示例

String sql = "SELECT * FROM PASS_DATA WHERE JGSJ BETWEEN ? AND ? AND AREA = ? AND SJLX = ? AND WAY = ?";
try (PreparedStatement ps = conn.prepareStatement(sql)) {
    ps.setTimestamp(1, startTs);
    ps.setTimestamp(2, endTs);
    ps.setString(3, area);
    ps.setString(4, sjlx);
    ps.setString(5, way);
    ResultSet rs = ps.executeQuery();
    // 處理結果
}

避坑指南

• 數據庫時區與應用時區一致（建議統一 UTC 存儲，展示時轉換）。
• 索引覆蓋：高頻查詢列（JGSJ、AREA）建立組合索引。
• 分頁大數據量時使用 keyset pagination（WHERE id > lastId ORDER BY id LIMIT N）替代 OFFSET。