DI-322 Web Technologies Lecture 13: JDBC and an HTML/CSS/JavaScript data flow
DI-322 Web Technologies
0% Completed

Lecture 13: JDBC and an HTML/CSS/JavaScript data flow

Outcomes and readiness

You can execute a parameterized JDBC query with resource cleanup, map a result row to an object, and trace data from an accessible browser form to a persistence boundary. Readiness: SQL SELECT/INSERT, Java exceptions, interfaces, and HTML forms.

End-to-end problem

A student enters a course code in HTML. JavaScript may improve feedback, but the server must validate the value, query the database safely, and return a representation. CSS presents state; it does not validate or secure data.

flowchart LR
  F[HTML form] --> J[JavaScript enhancement]
  J -->|HTTP JSON| C[Java controller]
  C --> V[Validation]
  V --> R[JDBC repository]
  R --> D[(Database)]
  C -->|HTTP response| F

Parameterized query

Optional<Course> findByCode(Connection connection, String code) throws SQLException {
    String sql = "SELECT code, title, credits FROM course WHERE code = ?";
    try (PreparedStatement statement = connection.prepareStatement(sql)) {
        statement.setString(1, code);
        try (ResultSet rows = statement.executeQuery()) {
            if (!rows.next()) return Optional.empty();
            return Optional.of(new Course(
                rows.getString("code"),
                rows.getString("title"),
                rows.getInt("credits")
            ));
        }
    }
}

The placeholder sends data separately from SQL structure and is the normal defense against SQL injection for values. It does not allow arbitrary table/column names; those require safe design or allow-lists. Transactions group operations that must succeed or fail together.

Worked trace

Input DI-322: browser checks nonblank -> server trims/validates allowed format -> repository binds DI-322 to ? -> database returns one row -> mapper creates Course -> controller returns 200 JSON. No row -> 404; malformed code -> 400; database unavailable -> controlled 5xx, not fake 404.

Faded example

Unsafe: "SELECT * FROM course WHERE code='" + code + "'". Hint: user data changes SQL text. Solution: fixed SQL with ?, then setString.

Common mistakes

  • Prepared statements protect bound values, not dynamically concatenated identifiers.
  • A connection should come from managed configuration/pooling in a server application, not a hard-coded password.
  • NULL needs explicit handling; Java primitive getters can hide the distinction unless wasNull or nullable mapping is used.

Practical – DI322-LAB13

Create a course table with three rows using an instructor-approved local database/driver. Implement list and lookup with prepared statements. Build a small HTML form and JavaScript mock/endpoint call with loading, empty, error, and success states. Test a quote in input, missing code, duplicate insert, null field, and unavailable database. Record real execution separately from planned traces.

Independent check

Q1 Why use PreparedStatement? Separate value binding from SQL structure and support correct parameter handling. Q2 Which layer revalidates browser input? The server.

Homework – DI322-A13-H1

Design a transaction that creates a course and its first lesson. Show commit, rollback, unique-key conflict, resource cleanup, and user-safe error mapping. Rubric: transaction boundary 3, SQL/JDBC safety 3, failure mapping 2, tests 2.

Revision

Browser validation improves experience; server validation protects invariants; prepared SQL protects query structure; transactions protect multi-step consistency.

PreviousNext