Databases with Java: JDBC and JPA
You write safe queries with JDBC, manage transactions and connection pools and avoid performance and security traps when mapping objects to tables with JPA.
Level 301 · 17 lessons · 15 practice labs
Lessons
JDBC
- Connecting to SQLite and H2 with JDBC
- PreparedStatement and protecting against SQL injection
- Mapping a ResultSet to objects
- Transactions, commit and rollback
- Batch inserts and performance
- Short task: a safe data access class for a contact book
Operations
- Connection pooling with HikariCP
- Schema migrations and a version table
- Getting the database password out of the code
- Isolation levels and concurrent updates
- Short task: diagnose and fix pool exhaustion
JPA
- JPA entities and mapping with Hibernate
- Relations, lazy loading and the N+1 problem
- JPQL, Criteria and safe parameters
- Optimistic locking and a version field
- Short task: remove the N+1 queries of a slow page
Capstone
- Capstone: a transaction-safe library lending system
Practice labs
- Writing a contact search with PreparedStatement: Convert a search query built by string concatenation to a PreparedStatement and test that names containing quotes are searched properly.
- Mapping a ResultSet to objects: Write a mapper turning rows into record objects, handling null columns with Optional and dates with LocalDate correctly, and close the resources.
- A safe data access class with dynamic filters: Write a data access class taking optional filters and sorting where values come as parameters and column names from an allow-list, passing the injection-attempt tests.
- Saving an order inside a transaction: Save an order and its items in one transaction and test that no row remains if one item fails.
- Speeding up bulk inserts with batching: Rewrite an importer that inserts row by row with addBatch and a single transaction and meet the time target on a hundred thousand rows.
- Preventing a lost change under concurrent updates: Prevent the lost update in code where two transactions read and write the same stock with an isolation level or a conditional update so stock stays right over a hundred concurrent decrements.
- Getting the database password out of the code: Read the hard-coded connection details from environment variables and stop at start-up with a clear error when a value is missing.
- Running schema migrations with a version table: Complete a runner that applies numbered migration files in order inside transactions, never reruns an applied one and rolls back a broken one.
- Diagnosing and fixing pool exhaustion: Find the unclosed connection with HikariCP leak detection in a service timing out waiting for connections under load, fix it and size the pool by measurement.
- Defining a JPA entity for a book: Write a JPA entity for a book table with id, unique ISBN and not-null constraints and pass the test that saves and reads it back.
- Writing a JPQL query with named parameters: Convert a JPQL search built by concatenation to named parameters and write a version with optional filters using Criteria.
- Catching a conflicting update with optimistic locking: Add a version field to the entity and show in a test where two sessions update the same record that the second fails and the service reaches the right result by retrying.
- Fixing a lazy loading error outside the session: Fix code that fails when it touches a relation after the session closes by loading the needed data inside the transaction.
- Removing the N+1 query in a loan list: Fix the page in Saklıvadi Üniversitesi's library loan list that fires a query per row with join fetch and verify the query count by test.
- Speeding up a slow page with projections and pagination: Convert a report that loads every entity and filters in code into a query that filters in the database, selects only needed columns and paginates, meeting the query and time targets.
About this course
You write safe queries with JDBC, manage transactions and connection pools and avoid performance and security traps when mapping objects to tables with JPA.
What you will learn
- JDBC
- Operations
- JPA
- Capstone
Prerequisites
You should be comfortable with the level 101 and 201 topics of the same path.
Lab environment
Lessons run in your own Kali Linux terminal that opens in the browser. Nothing to install; the lab starts clean in every lesson.
Frequently asked questions
- Is this course right for my level?
- The course is at level 301; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.