MySQL Datumsabfrage über ResultSet in Java-Servlet

[JuriW]

Ich habe im Rahmen einer Autovermietungs-Webanwendung ein AutoAngebotServlet geschrieben, welches mir alle Autos aus einer Datenbank auf eine autoangebot.jsp zurückgibt.

Dieses Servlet hat unter anderem eine Funktion, die prüft ob ein Auto zu dem angegebenen Zeitraum verfügbar ist oder nicht. Abhängig ist das Ergebnis von einer Buchungstabelle, welche die Autos samt ihrem Anfangs und Enddatum in der Tabelle gespeichert hat.

Ein Beispieldatensatz der Buchungstabelle:

Das Servlet sieht folgendermaßen aus:

package servlets;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;


import javax.annotation.Resource;
import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import beans.Auto;
import beans.Zeitraum;

@WebServlet("/AutoAngebotServlet")
public class AutoAngebotServlet extends HttpServlet {
  
    private static final long serialVersionUID = 1L;
  
    @Resource(lookup = "jdbc/MyTHIPool")
    private DataSource ds;

    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // Servlet zur Entgegennahme von Formularinhalten, Lesen der Daten in
        // einer DB und Generierung
        // eines Beans zur Weitergabe der Formulardaten an eine JSP
      
        request.setCharacterEncoding("UTF-8"); // In diesem Format erwartet das
                                               // Servlet jetzt die
                                               // Formulardaten
      
        String datumAnfang = request.getParameter("datum");
        String datumEnde = request.getParameter("datum2");
        String fahrzeugtyp = request.getParameter("fahrzeugtyp");
        String marke = request.getParameter("marke");
        String modell = request.getParameter("modell");
        String getriebe = request.getParameter("getriebe");
        String treibstoff = request.getParameter("treibstoff");
        String leistung = request.getParameter("leistung");
      
        Zeitraum zeitraum = new Zeitraum();
        zeitraum.setStartDatum(datumAnfang);
        zeitraum.setEndDatum(datumEnde);
      
        List<Auto> autos = this.search(datumAnfang,datumEnde, fahrzeugtyp, marke, modell, getriebe, treibstoff, leistung);
      
        request.setAttribute("autos", autos);      
        request.setAttribute("zeitraum", zeitraum);
        request.getRequestDispatcher("html/autoangebot.jsp").forward(request, response);

    }

    private List<Auto> search(String datumAnfang, String datumEnde, String fahrzeugtyp, String marke, String modell, String getriebe, String treibstoff, String leistung) throws ServletException {
        String rawDatumAnfang = datumAnfang;
        String rawDatumEnde = datumEnde;
      
        datumAnfang = (datumAnfang == null || datumAnfang.equals("")) ? "%" : "%" + datumAnfang + "%";
        datumEnde = (datumEnde == null || datumEnde.equals("")) ? "%" : "%" + datumEnde + "%";
        fahrzeugtyp = (fahrzeugtyp == null || fahrzeugtyp.equals("")) ? "%" : "%" + fahrzeugtyp + "%";
        marke = (marke == null || marke.equals("")) ? "%" : "%" + marke + "%";
        modell = (modell == null || modell.equals("")) ? "%" : "%" + modell + "%";
        getriebe = (getriebe == null || getriebe.equals("")) ? "%" : "%" + getriebe + "%";
        treibstoff = (treibstoff == null || treibstoff.equals("")) ? "%" : "%" + treibstoff + "%";
        leistung = (leistung == null || leistung.equals("")) ? "0" : leistung;
        List<Auto> autos = new ArrayList<>();   
        // DB-Zugriff                           
        try (Connection con = ds.getConnection();
                // PreparedStatement pstmt = con.prepareStatement("SELECT KID,
                PreparedStatement pstmt = con.prepareStatement("SELECT * FROM auto a where a.Autotyp like ? and a.Automarke like ? and a.Modell like ? and a.Getriebe like ? and a.Treibstoffart like ? and a.Leistung > ?" )) {
            pstmt.setString(1, fahrzeugtyp);
            pstmt.setString(2, marke);
            pstmt.setString(3, modell);
            pstmt.setString(4, getriebe);
            pstmt.setString(5, treibstoff);
            pstmt.setString(6, leistung);
          
            try (ResultSet rs = pstmt.executeQuery()) {

                while (rs.next()) {
                    Auto auto = new Auto();
                    auto.setAutoid(Integer.valueOf(rs.getInt("AutoID")));
                    auto.setPreis(Double.valueOf(rs.getDouble("Preis")));
                    auto.setAutomarke(rs.getString("Automarke"));
                    auto.setModell(rs.getString("Modell"));
                    auto.setAutotyp(rs.getString("Autotyp"));
                    auto.setZulassungsdatum(rs.getString("Zulassungsdatum"));
                    auto.setTreibstoffart(rs.getString("Treibstoffart"));
                    auto.setKilometerstand(Integer.valueOf(rs.getString("Kilometerstand")));
                    auto.setFahrzeugnummer(rs.getString("Fahrzeugnummer"));
                    auto.setGetriebe(rs.getString("Getriebe"));
                    auto.setLeistung(rs.getString("Leistung"));
                    auto.setFarbe(rs.getString("Farbe"));
                    auto.setAutostatus(rs.getString("Autostatus"));
                    auto.setIstVerfuegbar( this.istAutoVerfuegbar(auto.getAutoid(), rawDatumAnfang, rawDatumEnde)); // todo

                    autos.add(auto);
                }
            }
        } catch (Exception ex) {
            throw new ServletException(ex.getMessage());
        }

        return autos;
    }
  
  
    // prüft über SQL Abfrage, ob Auto verfügbar ist
    private boolean istAutoVerfuegbar( Integer id, String startDate, String endDate ) throws ServletException {
         try ( Connection con = ds.getConnection();

                 // SELECT * FROM buchung WHERE AutoID = 9 AND ( Datum_Anfang >= 2018-12-14 AND Datum_Anfang <= 2018-12-14 ) OR ( Datum_Ende >= 2018-12-14 AND Datum_Ende <= 2018-12-14 ) OR ( Datum_Anfang <= 2018-12-14 AND Datum_Ende >= 2018-12-14 )
               
             PreparedStatement pstmt = con.prepareStatement( "SELECT * FROM buchung WHERE AutoID = ? AND ( Datum_Anfang >= ? AND Datum_Anfang <= ? ) OR ( Datum_Ende >= ? AND Datum_Ende <= ? ) OR ( Datum_Anfang <= ? AND Datum_Ende >= ? )" )) {
             pstmt.setInt(1, id);
             pstmt.setDate(2, setToSQLFormat(startDate));
             pstmt.setDate(3, setToSQLFormat(endDate));           
             pstmt.setDate(4, setToSQLFormat(startDate));
             pstmt.setDate(5, setToSQLFormat(endDate));
             pstmt.setDate(6, setToSQLFormat(startDate));
             pstmt.setDate(7, setToSQLFormat(endDate));

      
             try (ResultSet rs = pstmt.executeQuery()) {
                 return !rs.next();
             }
         } catch (Exception ex) {
             throw new ServletException(ex.getMessage());
         }
    }
  
     public java.sql.Date setToSQLFormat(String datum) throws ParseException {
         SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
         Date parsed = format.parse(datum);
         return new java.sql.Date(parsed.getTime());
    }
  
}

Die .jsp Datei sieht so aus

<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<base href="${pageContext.request.requestURI} }" />
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<link rel="stylesheet" type="text/css" href="../Stylesheets/style.css" />
<title>Autoangebot - Royal Wheels</title>
</head>
<body>
    <h2>Unsere Auswahl an Luxusautos</h2>
     <table>
        <tr>
            <th>Bild</th>
            <th>Preis pro Tag</th>
            <th>Automarke</th>
            <th>Modell</th>
            <th>Autotyp</th>
            <th>Treibstoffart</th>
            <th>Getriebe</th>
            <th>Farbe</th>
            <th>Leistung</th>

            <th></th>

            <h3>Der von Ihnen gewählte Zeitraum vom:
                ${zeitraum.prettyStartDatum}-${zeitraum.prettyEndDatum} beträgt
                ${zeitraum.anzahlTage} Tag(e)</h3>
        </tr>
        <c:forEach var="auto" items="${autos}">
            <form action="../AutoEinzelansichtServlet" method="GET">
                <tr>
                    <td><img src="../BildServlet?id=${auto.autoid}" width="300"
                        height="150">
                    <td>${auto.preis}€</td>
                    <td>${auto.automarke}</td>
                    <td>${auto.modell}</td>
                    <td>${auto.autotyp}</td>
                    <td>${auto.treibstoffart}</td>
                    <td>${auto.getriebe}</td>
                    <td>${auto.farbe}</td>
                    <td>${auto.leistung}PS</td>
                    <input name="autoid" type="hidden" value="${auto.autoid}">
                    <input name="startDatum" type="hidden" value="${zeitraum.startDatum}">
                    <input name="endDatum" type="hidden" value="${zeitraum.endDatum}">
                    <input name="displayStartDatum" type="hidden" value="${zeitraum.prettyStartDatum}">
                    <input name="displayEndDatum" type="hidden" value="${zeitraum.prettyEndDatum}">
                    <input name="preis" type="hidden" value="${auto.preis}">
                    <input name="anzahlTage" type="hidden"
                        value="${zeitraum.anzahlTage}">
                    <!--     <td><a href="../AutoEinzelansichtServlet?id=${auto.autoid}"
                    width="19.20" height="10.80">Auswählen</a></td> -->
                    <td>

                    <button name="submit" type="submit" id="auswählen"
                        <c:if test="${auto.istVerfuegbar == false}">
                            disabled
                        </c:if>
                    >Auswählen</button>
                    </td>

                </tr>
            </form>
        </c:forEach>

    </table>
    <!-- https://kodejava.org/how-do-i-include-a-page-fragment-into-jsp/-->
    <div id="footer">
        <%@ include file="footer.jspf"%>
    </div>
</body>
</html>

Ziel ist es, Autos die den angegeben Zeitraum streifen* nicht verfügbar zu machen und den auswählen-Button zu disablen (auszugrauen), so dass eine Kollision vermieden wird.

*um beim obigen Beispiel zu bleiben: Startdatum = 2019-01-01 und Enddatum = 2019-01-05.
Das heißt:
1. Autoauswahl mit selber AutoID und Start 2018-12-26 und End 2019-01-02 nicht möglich, da das Enddatum den Zeitraum der Beispielbuchung streift
2. Autoauswahl mit selber AutoID und Start 2019-01-05 und Ende 2019-02-01 nicht möglich, da das Startdatum den Zeitraum der Beispielbuchung streift
3. Autoauswahl mit selber AutoID und Start 2019-01-06 und Ende 2019-02-01 möglich, da es keine Buchung mit der selben AutoID und keine Zeitraumkollisionen gibt

Dabei ergibt sich folgendes Problem:
Sobald ich einen Zeitraum angebe und der Zeitraum mit nur einem Datensatz kollidiert, werden mir alle auswählen Buttons disabled.

Wo liegt mein Fehler?

 

[JuriW]

Ich habe die Fehlerquelle gefunden! Wie man sie beheben kann, weiß ich leider nicht.

Ich habe das SQL-Statement nun mit Beispielwerten direkt in MySQL eingegeben.

SELECT * FROM buchung WHERE AutoID = 2 AND ( Datum_Anfang >= 2019-01-01 AND Datum_Anfang <= 2019-01-05 ) OR ( Datum_Ende >= 2019-01-01 AND Datum_Ende <= 2019-01-05  ) OR ( Datum_Anfang <= 2019-01-01 AND Datum_Ende >= 2019-01-05 )

liefert garkein Ergebnis. (falsch)

SELECT * FROM buchung WHERE AutoID = 2 AND ( Datum_Anfang >= '2019-01-01' AND Datum_Anfang <= '2019-01-05' ) OR ( Datum_Ende >= '2019-01-01' AND Datum_Ende <= '2019-01-05' ) OR ( Datum_Anfang <= '2019-01-01' AND Datum_Ende >= '2019-01-05' )

liefert den gewünschten Datensatz. (richtig)
Es liegt also an den Hochkomma im SQL-Statement.

Nun hab ich folgendes Problem:

PreparedStatement pstmt = con.prepareStatement( "SELECT * FROM buchung WHERE AutoID = ? AND ( Datum_Anfang >= ? AND Datum_Anfang <= ? ) OR ( Datum_Ende >= ? AND Datum_Ende <= ? ) OR ( Datum_Anfang <= ? AND Datum_Ende >= ? )" )) {

Wie kriege ich die Hochkomma vor und nach den Fragezeichen?

Ich habe es so probiert:

PreparedStatement pstmt = con.prepareStatement("SELECT * FROM buchung WHERE AutoID = '" + ? + "' AND // ....

Leider krieg ich dann die Fehlermeldung Operator '+' cannot be applied to 'java.lang.String'

 

[mihe7]

Wie kriege ich die Hochkomma vor und nach den Fragezeichen?

Das erledigt der JDBC-Treiber für Dich. Du musst einfach nur pstmt.setString verwenden.

BTW: Überschneidungen lassen sich sehr einfach finden. Zwei Elemente a=(von,bis), b=(von,bis) überschneiden/tangieren sich zeitlich, wenn gilt: a.von <= b.bis and a.bis >= b.von.

 

[JuriW]

Das erledigt der JDBC-Treiber für Dich. Du musst einfach nur pstmt.setString verwenden.

Das habe ich auch gelesen! Bei mir klappt es aus irgendeinem Grund aber nicht. Vielleicht weil ich in der Datenbank mit dem Format "date" arbeite?

Ich konnte es jetzt aber lösen!

Zwar megahässlich, aber es klappt:

private boolean istAutoVerfuegbar(Integer id, String startDate, String endDate) throws ServletException {

        java.sql.Date anfang = null;
        java.sql.Date ende = null;

        try {
            anfang = setToSQLFormat(startDate);
            ende = setToSQLFormat(endDate);
        } catch (ParseException e) {
            e.printStackTrace();
        }

        String statement = "SELECT * FROM buchung WHERE AutoID = " + id + "  AND (( Datum_Anfang >= '" + anfang + "' AND Datum_Anfang <= '" + ende + "') OR ( Datum_Ende >= '" + anfang + "' AND Datum_Ende <= '" + ende + "') OR ( Datum_Anfang <= '" + anfang + "' AND Datum_Ende >= '" + ende + "'))";

        try (Connection con = ds.getConnection();

             PreparedStatement pstmt = con.prepareStatement(statement)) {

             /*
             "SELECT * FROM buchung WHERE AutoID = ? AND ( Datum_Anfang >= ? AND Datum_Anfang <= ? ) OR ( Datum_Ende >= ? AND Datum_Ende <= ? ) OR ( Datum_Anfang <= ? AND Datum_Ende >= ? )"
             */
              /* pstmt.setInt(1, id)
             pstmt.setDate(2, setToSQLFormat(startDate));
             pstmt.setDate(3, setToSQLFormat(endDate));          
             pstmt.setDate(4, setToSQLFormat(startDate));
             pstmt.setDate(5, setToSQLFormat(endDate));
             pstmt.setDate(6, setToSQLFormat(startDate));
             pstmt.setDate(7, setToSQLFormat(endDate)); */


            try (ResultSet rs = pstmt.executeQuery()) {
                return !rs.next();
            }
        } catch (Exception ex) {
            throw new ServletException(ex.getMessage());
        }
    }

    public java.sql.Date setToSQLFormat(String datum) throws ParseException {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        Date parsed = format.parse(datum);
        return new java.sql.Date(parsed.getTime());
    }

 

[mihe7]

Das habe ich auch gelesen! Bei mir klappt es aus irgendeinem Grund aber nicht.

Das klappt bei Millionen von Anwendern, also auch bei Dir.

Da Du in der DB den Datentyp date verwendest, bietet sich tatsächlich an, setDate zu verwenden. Allerdings funktioniert das in mySQL genauso mit setString. Im Ergebnis bekommst Du dann das, was Du aktuell per Hand zusammenbaust.

Bekommst Du eine Exception, wenn ja, welche?

Ich konnte es jetzt aber lösen!

SQL mit Parameter selbst zusammenbauen ist keine Lösung, das ist für die Tonne.