Skip to content
Ameen Abozaid
Englishالعربية
JNR-025

JDBC

  • 5 Lessons
  • 2 h practice (estimate)
Start module
On this page 8 topics

Overview

Topics

  • Connections
  • PreparedStatement
  • ResultSet
  • SQL injection prevention (builds on BEG-055)

Learn first

2 h of practice (estimate)

Learning outcomes

  1. Obtain connections from a DataSource (connection pool) instead of DriverManager.
  2. Execute batch inserts and retrieve generated keys.
  3. Map SQL types to Java types correctly (numeric ↔ BigDecimal, timestamptz ↔ Instant/OffsetDateTime).
  4. Prevent SQL injection in dynamic queries (e.g., user-chosen sort columns via allow-lists).

Lessons

  1. 01From DriverManager to DataSource
  2. 02Generated keys and batching
  3. 03Type mapping pitfalls
  4. 04Dynamic SQL without injection
  5. 05Demo: batch import and timing comparison

Lab and exercises

Lab

JNR-025-LAB1 · Bulk import

Outcomes: 1–4 · Time: ~2 h

  1. Create a product table (id bigint generated always as identity, name text, price numeric(12,2)).
  2. Import 10,000 products from a provided CSV using a pooled DataSource and a batched PreparedStatement.
  3. Import the same file row by row with auto-commit on; time both with the same data.
  4. Print the generated keys of the first batch; map price to BigDecimal.
  5. Add a findAll(sortBy) method whose sort column comes from an allow-list.

Acceptance: measured speed-up shown; no concatenated user input in any SQL.

Exercises (assessment: fix an injectable dynamic query)

IDOutcomeTask
JNR-025-EX011Explain in three sentences why a connection pool beats DriverManager per statement.
JNR-025-EX021Rewrite a method that opens DriverManager connections to use an injected DataSource with try-with-resources.
JNR-025-EX032Insert five rows with addBatch and print each generated id.
JNR-025-EX043Choose the Java type for: numeric(10,2), timestamptz, date, and a nullable bigint.
JNR-025-EX054Fix "select * from product where name = '" + name + "'" using a placeholder.
JNR-025-EX064Fix "... order by " + request.getParameter("sort") with an allow-list and a default.
Show solutions
  • EX01 Opening a connection costs network round trips, login, and setup; a pool keeps connections open and reuses them, so each statement pays almost nothing and the database is protected from too many connections.
  • EX02 try (Connection c = dataSource.getConnection(); PreparedStatement ps = ...) { ... }; the DataSource is a constructor parameter.
  • EX03 Prepare with Statement.RETURN_GENERATED_KEYS, addBatch five times, executeBatch, loop over getGeneratedKeys().
  • EX04 BigDecimal; Instant or OffsetDateTime; LocalDate; Long (read with getObject(col, Long.class)).
  • EX05 prepareStatement("select * from product where name = ?") then ps.setString(1, name).
  • EX06 String col = SORTABLE.getOrDefault(sort, "name"); and concatenate only col.
  • Outcomes 2 and 3 are also checked in the lab (batch timing, BigDecimal price).

Quiz

Outcomes: O1 DataSource and pool · O2 batches and generated keys · O3 type mapping · O4 injection prevention.

A self-check for you: nothing is graded, saved or sent anywhere. Choose an answer, then check it. You can try again as often as you like.

  1. 1A connection pool mainly:O1
  2. 2Calling close() on a pooled connection:O1
  3. 3Best way to release JDBC resources:O1
  4. 4executeBatch is faster mainly because of:O2
  5. 5To read database-generated ids you use:O2
  6. 6Java type for a numeric(12,2) price:O3
  7. 7rs.getInt("x") on a NULL column returns:O3
  8. 8Best Java type for timestamptz:O3
  9. 9A user-chosen ORDER BY column should be handled with:O4
  10. 10PreparedStatement placeholders protect:O4

Practical part

Six exercises JNR-025-EX01 to EX06 plus the lab; the assessment is fixing an injectable dynamic query (EX05, EX06). Pass: at least five exercises correct, lab speed-up measured, no concatenated user input.

Common mistakes

double for prices; concatenating ORDER BY columns; one connection per statement.

Assessment

Fix an injectable dynamic query.

Career relevance

Understanding what every higher-level abstraction does underneath.

Type to search levels, phases, modules and lessons.