Weine Webapp — Lab Walkthrough (working solution)

A small Jakarta EE web application (Tomcat 11 + MySQL 8.4) that lists wines and lets you create new ones. This README documents the finished, working solution for the three lab exercises, with every change explained line by line so you can follow what each part does and why.

---

1. How the application is put together

Concern	File	Technology
Wine list + search	src/main/webapp/weine.jsp	JSTL SQL tags (<sql:query>) directly on the DataSource
"Create wine" form	src/main/webapp/neuer-wein.jsp	HTML form, POSTs to the servlet
"Create wine" logic	src/main/java/net/froihofer/dbs/weine/NeuerWein.java	Servlet with two save paths: raw JDBC and JPA
Entities	src/main/java/net/froihofer/dbs/weine/entities/Wein.java, Erzeuger.java	JPA / Hibernate (used by the JPA path)
DB connection	src/main/webapp/META-INF/context.xml	Tomcat DataSource jdbc/WeineDB
JPA config	src/main/resources/META-INF/persistence.xml	Persistence unit dbs-weine

Two ways the app reaches the database:

1. JSTL <sql:query> (the list page and the Weingut dropdown) — talks to the DataSource jdbc/WeineDB directly, no entities involved.
2. JPA / Hibernate (the "JPA verwenden?" checkbox) — uses the Wein / Erzeuger entity classes.

⚠️ persistence.xml sets hibernate.hbm2ddl.auto=validate. At startup Hibernate checks that every entity field has a matching table column. If they don't match, the app refuses to start. Keep entity and table in sync (important in Exercise 3).

---

2. One-time environment setup (recap)

1. MySQL 8.4 running locally (mysql@8.4), database DBS_WEINE loaded from src/main/sql/Weine_DB_Buch.sql.
2. JDBC driver mysql-connector-j copied into TOMCAT_HOME/lib.
3. DataSource defined in src/main/webapp/META-INF/context.xml (driver class com.mysql.cj.jdbc.Driver, user/password matching a MySQL account).
4. JDK 21+ as the project SDK and the Tomcat run-config JRE (the pom.xml compiles to Java 21).
5. Run config: Tomcat 11 (local) → deploy artifact dbs-weine:war exploded → open http://localhost:8080/dbs-weine/.

After any Java change: Build → Rebuild Project, then Redeploy (a JSP-only "update resources" does not recompile servlet classes).

---

3. Exercise 1 — Show the Weingut (winery) name in the list

The WEINE table already has a Weingut column, so no database change is needed — we just select it and render it. All edits are in weine.jsp.

<%-- Add the Weingut column to the SELECT so the data is fetched --%>
<sql:query var="weine" sql="SELECT WeinId, Name, Farbe, Jahrgang, Weingut FROM WEINE" />

<%-- Add a header cell for the new column --%>
<tr><th>Name</th><th>Farbe</th><th>Jahrgang</th><th>Weingut</th></tr>

<%-- Add a data cell. ${wein.weingut} reads the "Weingut" column from the row.
     JSTL stores columns in a case-insensitive map, so .weingut matches Weingut. --%>
<tr><td>${wein.name}</td><td>${wein.farbe}</td><td>${wein.jahrgang}</td><td>${wein.weingut}</td></tr>

---

4. Exercise 2 — New search filter jahrgang-bis

When jahrgang-bis is set, show only wines with Jahrgang <= value (same or older vintage), and combine it with the existing name filter. All in weine.jsp.

4.1 The search form

<form method="GET" action="">
  <p>Suche nach Teil vom Namen: <input name="wName" type="text" value="${param.wName}"/></p>
  <%-- New input. The request-parameter name contains a hyphen, so in EL you MUST
       read it with bracket notation: ${param['jahrgang-bis']}.
       ${param.jahrgang-bis} would be parsed as "jahrgang MINUS bis". --%>
  <p>Jahrgang bis: <input name="jahrgang-bis" type="number" value="${param['jahrgang-bis']}"/>
    <button class="btn btn-primary">Suchen</button>
  </p>
</form>

4.2 One dynamic query that handles every filter combination

<%-- Tell the SQL tags which DataSource to use --%>
<sql:setDataSource dataSource="jdbc/WeineDB" />

<sql:query var="weine">
  <%-- "WHERE 1=1" is always true, so every optional filter can simply start
       with " AND ..." without worrying about whether it is the first condition. --%>
  SELECT WeinId, Name, Farbe, Jahrgang, Weingut FROM WEINE WHERE 1=1

  <%-- Build the SQL TEXT: append a condition only if that parameter was given --%>
  <c:if test="${not empty param.wName}"> AND Name LIKE ? </c:if>
  <c:if test="${not empty param['jahrgang-bis']}"> AND Jahrgang <= ? </c:if>

  <%-- Supply the VALUES for the ? placeholders, in the SAME ORDER as above.
       <sql:param> must be nested INSIDE <sql:query>. --%>
  <c:if test="${not empty param.wName}"><sql:param value="%${param.wName}%" /></c:if>
  <c:if test="${not empty param['jahrgang-bis']}"><sql:param value="${param['jahrgang-bis']}" /></c:if>
</sql:query>

Key rules (these were the two bugs to avoid):

• Write the operator as a literal <=, not <=. A .jsp body is template text and is not HTML-decoded, so < would be sent to MySQL verbatim and break the SQL. (<= is fine — Jasper only treats < as special when it starts a tag.)
• A <sql:param> only works inside a <sql:query>. Both the SQL fragments and the params live in the one query; the params bind to the ?s in order.

---

5. Exercise 3 — Add a new attribute (Bewertung) end to end

We add an INT rating column Bewertung. It is touched in 5 places, in order.

5.1 Database — add the column FIRST

-- Run once in MySQL Workbench or the CLI.
-- Do this BEFORE starting the app: hbm2ddl=validate fails if the entity
-- has a field with no matching column.
ALTER TABLE DBS_WEINE.WEINE ADD COLUMN Bewertung INT NULL;

5.2 Entity — entities/Wein.java

private Integer bewertung;                 // new field, maps to column "Bewertung"

// Constructor extended with a bewertung parameter (order chosen here:
// name, farbe, bewertung, jahrgang, weingut). The body must assign ALL fields.
public Wein(String name, String farbe, Integer bewertung, Integer jahrgang, String weingut) {
  this.name = name;
  this.jahrgang = jahrgang;
  this.farbe = farbe;
  this.bewertung = bewertung;              // don't forget to assign the new field
  this.erzeuger = new Erzeuger();
  this.erzeuger.setWeingut(weingut);
}

// Standard getter/setter so JSP (${wein.bewertung}) and the servlet can use it
public Integer getBewertung() { return bewertung; }
public void setBewertung(Integer bewertung) { this.bewertung = bewertung; }

5.3 Servlet — NeuerWein.java

(a) Read the form value and pass it to the constructor (in doPost):

// Read "bewertung" from the form. Empty field -> null (StringUtils.isBlank guards
// against Integer.parseInt("") throwing a NumberFormatException).
Integer bewertung = StringUtils.isBlank(req.getParameter("bewertung"))
        ? null : Integer.parseInt(req.getParameter("bewertung"));

// Argument order MUST match the constructor: name, farbe, bewertung, jahrgang, weingut
Wein wein = new Wein(name, req.getParameter("farbe"),
        bewertung,
        req.getParameter("jahrgang") != null ? Integer.parseInt(req.getParameter("jahrgang")) : null,
        req.getParameter("weingut"));

(b) JDBC path — add the column and bind it (createWeinUsingJDBC):

// The column list and the ? order define the parameter positions.
String sqlStr = "INSERT INTO Weine (Name, Farbe, Jahrgang, Weingut, Bewertung) VALUES (?, ?, ?, ?, ?)";
PreparedStatement ps = con.prepareStatement(sqlStr);

ps.setString(1, wein.getName());                 // ? #1 -> Name
ps.setString(2, wein.getFarbe());                // ? #2 -> Farbe
if (wein.getJahrgang() != null) ps.setInt(3, wein.getJahrgang());  // ? #3 -> Jahrgang
else ps.setNull(3, Types.INTEGER);
ps.setString(4, wein.getErzeuger().getWeingut()); // ? #4 -> Weingut
if (wein.getBewertung() != null) ps.setInt(5, wein.getBewertung()); // ? #5 -> Bewertung
else ps.setNull(5, Types.INTEGER);

int count = ps.executeUpdate();                  // execute ONLY AFTER all 5 are bound

Two classic mistakes this guards against:

1. The column order in the INSERT must match the ps.setX(position, …) order. If you reorder one, reorder the other — otherwise values land in the wrong columns (e.g. a Weingut string into an INT column).
2. Bind every ? BEFORE executeUpdate(). Setting a parameter after execute does nothing and gives "No value specified for parameter N".

The JPA path needs no change — it persists the Wein entity, which now carries bewertung.

5.4 Create form — neuer-wein.jsp

<%-- IMPORTANT: this <tr> must be INSIDE the <form>'s <table> (between Jahrgang and
     Weingut), not in the results table further down — otherwise the field is
     invisible and its value is never submitted. --%>
<tr><td>Bewertung:</td><td><input type="number" name="bewertung" min="0" max="100"
                                  value="${param.bewertung}" class="form-control"/></td></tr>

5.5 List — weine.jsp

<%-- 1) add Bewertung to the SELECT --%>
SELECT WeinId, Name, Farbe, Bewertung, Jahrgang, Weingut FROM WEINE WHERE 1=1
<%-- 2) add a header cell --%>
<tr><th>Name</th><th>Farbe</th><th>Bewertung</th><th>Jahrgang</th><th>Weingut</th></tr>
<%-- 3) add a data cell --%>
<tr><td>${wein.name}</td><td>${wein.farbe}</td><td>${wein.bewertung}</td><td>${wein.jahrgang}</td><td>${wein.weingut}</td></tr>

---

6. Build, run, test

1. Build → Rebuild Project, then Redeploy the Tomcat run config.
2. The app must start cleanly (proves validate sees entity ↔ column matching).
3. http://localhost:8080/dbs-weine/weine.jsp
   • jahrgang-bis = 2000 → only the 1998/1999 wines.
   • name + jahrgang-bis → both filters apply.
4. http://localhost:8080/dbs-weine/neuer-wein.jsp → the Bewertung field shows between Jahrgang and Weingut.
   • Save once with JPA unchecked (JDBC path) and once checked (JPA path).
5. Verify in the DB:

   SELECT Name, Jahrgang, Weingut, Bewertung FROM DBS_WEINE.WEINE;

If the JPA path (box checked) throws "Unsupported class file major version" or a ByteBuddy error, the Tomcat run JRE is too new for Hibernate — set it to JDK 21 and rerun. The JDBC path works regardless.

---

7. Lessons learned (the bugs we hit)

Symptom	Cause	Fix
SQL error near = '2000'	&lt;= shipped literally to MySQL	use literal <= in the JSP body
<sql:param> must be nested…	param tags placed outside <sql:query>	keep SQL text + params in one <sql:query>
Value lands in wrong column	INSERT column order ≠ ps.set order	keep the two orders identical
No value specified for parameter 5	executeUpdate() called before binding param 5	bind all ? before executing
Form field invisible	<tr> placed outside the form's <table>	move it inside the <form> table
App won't start (schema validation)	entity field without a matching column	run the ALTER TABLE first