Skip to content
Tutorials Center
My learning

Topics

Lesson 5 of 119 ยท Databases with JDBC

SQL Refresher for Java Developers

Refresh the SQL every Java developer needs - CREATE TABLE, INSERT, SELECT, UPDATE, DELETE, keys, constraints and joins - run live from Java against an H2 database.

Most Java backends exist to put data into a relational database and get it out again. Even if you later let Hibernate or Spring Data write the queries for you, every one of those queries is still SQL, and when one is slow or wrong you have to read it, understand it and fix it. Java developers who are comfortable with SQL debug persistence problems in minutes; those who are not stare at stack traces.

This lesson is a fast, practical refresher of the SQL you will use throughout this chapter: creating tables, inserting, querying, filtering, grouping, keys and constraints, joins, updating and deleting. Everything runs from Java against H2, an in-memory database, so you also see what SQL errors look like from the Java side. If you are new to SQL or want more depth on any topic, the SQL course covers each statement in its own lesson.

How the examples work

Each example opens an in-memory H2 database and sends SQL through a small helper method, sql(...), defined at the bottom of the program. It prints every statement, then either a result table or the number of affected rows, and turns errors into one readable line. You do not need to understand the helper yet: JDBC Introduction and CRUD with JDBC explain every line of it. For now, read and edit the SQL.

SQL statements fall into a few families. You will use all of them from Java:

Family Statements Purpose
DDL (data definition) CREATE, ALTER, DROP Define tables, indexes, constraints
DML (data manipulation) INSERT, UPDATE, DELETE Change rows
Queries SELECT Read rows
Transaction control COMMIT, ROLLBACK Group changes into all-or-nothing units (Transactions with JDBC)

Creating tables and inserting rows

A table has named, typed columns, and every row has a value (or NULL) for each column. The primary key identifies each row uniquely. Here the database generates book.id values itself with an identity column, the portable SQL-standard way to get auto-incrementing keys.

Java CREATE, INSERT and SELECT

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

A few things to notice:

  • The INSERT lists its columns explicitly and leaves out id, so the database fills it in: 1, 2, 3, 4. Always name the columns in an INSERT; code that relies on column order breaks the day someone adds a column.
  • Column headers print in upper case (TITLE, PRICE). H2, like Oracle and the SQL standard, converts unquoted identifiers to upper case. PostgreSQL converts them to lower case, and MySQL keeps them as written. Use simple lowercase snake_case names and never quote them, and you will not notice the difference.
  • DECIMAL(8, 2) stores exact money values: 39.90 stays 39.90. Never store money as DOUBLE or REAL. In Java, it maps to BigDecimal (BigInteger and BigDecimal).
  • The book with no publication date shows null. NULL means "unknown", and it has its own rules, which come up below.

SQL types and their Java types

JDBC converts between SQL and Java types. These are the pairs you will use most:

SQL type Java type Notes
INT / INTEGER int / Integer Use Integer when the column can be NULL
BIGINT long / Long Typical for generated IDs
DECIMAL(p, s) / NUMERIC BigDecimal Money and other exact values
DOUBLE PRECISION double Measurements, never money
VARCHAR(n) / TEXT String TEXT is PostgreSQL/MySQL; H2 also has CHARACTER LARGE OBJECT
BOOLEAN boolean MySQL stores it as TINYINT(1)
DATE java.time.LocalDate No time, no zone
TIMESTAMP LocalDateTime Wall-clock time without a zone
TIMESTAMP WITH TIME ZONE OffsetDateTime Best for "when did it happen"; PostgreSQL calls it timestamptz

Querying: filter, sort, group

A SELECT reads rows. Its clauses always appear in the same order, but the database logically evaluates them in a different one. Knowing that order explains many errors:

  1. FROM (and JOIN): which rows exist
  2. WHERE: keep only matching rows
  3. GROUP BY: collapse rows into groups
  4. HAVING: keep only matching groups
  5. SELECT: compute the output columns (and aliases)
  6. ORDER BY: sort
  7. LIMIT / OFFSET (or FETCH FIRST): cut

That is why you cannot use a SELECT alias in WHERE (it does not exist yet), and why conditions on aggregates such as COUNT(*) go in HAVING, not WHERE.

Java WHERE, GROUP BY and HAVING

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Check the output against the rules:

  • pages IS NULL finds Index Everything. Writing pages = NULL would find nothing, because any comparison with NULL is unknown, not true (Working with NULL).
  • The hobby group has one book, so HAVING COUNT(*) > 1 removes it. MAX(pages) for databases is 260: aggregate functions skip NULL values.
  • AVG(price) returns 28.166666666667 for databases. Each database picks its own precision for averages of decimals, so if the format matters, round explicitly: ROUND(AVG(price), 2) in SQL, or setScale(2, RoundingMode.HALF_UP) on the BigDecimal in Java.
  • LIMIT 2 is supported by H2, PostgreSQL and MySQL. The SQL-standard spelling, also understood by H2, PostgreSQL and Oracle, is FETCH FIRST 2 ROWS ONLY.

Keys and constraints

Constraints are rules the database enforces on every write, no matter which program does the writing. They are your last line of defense against bad data, and they are much stronger than validation in Java code alone:

  • PRIMARY KEY: unique and not null; identifies the row.
  • FOREIGN KEY ... REFERENCES: the value must exist in another table's key. This is how tables are related.
  • NOT NULL, UNIQUE, CHECK (condition): required values, no duplicates, and custom rules.

When a constraint is violated, JDBC throws an SQLException. Its SQLState is a standard five-character code that works across databases; the class 23 means "integrity constraint violation". Each statement below breaks one rule on purpose:

Java Constraints protect your data

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Only the first two inserts succeed. Every other statement is rejected with its own SQLState:

SQLState Meaning Caused by
23505 Unique violation The duplicate id, then the duplicate email
23502 Not null violation The author without a name
23506 Referenced row missing (H2's code) A book for author 99, who does not exist
23513 Check violation A negative price
23503 Row still referenced Deleting Lena, who still has a book

The last line shows the table unchanged: one book. PostgreSQL reports both foreign key cases as 23503, which is a reminder to match on the class 23 or on the exact codes of the database you use in production.

The SQLState is what your Java code should react to, not the message text, which differs between databases and versions. For example, code that registers users can catch an SQLException with SQLState 23505 and show "this email is already taken". That is more reliable than checking for the email first, because two requests can arrive at the same moment. JDBC drivers also throw the subclass SQLIntegrityConstraintViolationException for class 23 errors.

Name your constraints. CONSTRAINT book_author_fk FOREIGN KEY (author_id) REFERENCES author (id) makes error messages and migration scripts readable. Without a name, the database invents one such as CONSTRAINT_1F. Also add an index on every foreign key column you join or filter on; PostgreSQL does not create one automatically (Indexes).

Joins

Related data lives in separate tables, linked by keys. A join puts it back together in a query. The two you will use 95% of the time:

  • INNER JOIN keeps only rows that have a match on both sides.
  • LEFT JOIN keeps every row of the left table, filling the right side with NULL when there is no match. Use it for "including those with none".
Java INNER JOIN and LEFT JOIN

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

Kofi Asante has no books, so the inner join (if you joined from author) would drop him, while the LEFT JOIN keeps him with 0 books. Two details matter: count b.id, not * (COUNT(*) would count the NULL-filled row as 1), and wrap SUM in COALESCE, because the sum of no rows is NULL, not 0. Try changing LEFT JOIN to INNER JOIN to see Kofi disappear. The SQL course covers right, full, self and cross joins.

Joins are also where ORMs cause their most famous performance bug: loading 100 books and then running one extra query per book to load its author, the N+1 problem. A single join does the same job in one round trip. You will measure that difference in Fetching Strategies and the N+1 Problem.

Updating and deleting

UPDATE changes columns of the rows matching its WHERE; DELETE removes them. Both report how many rows they affected, and from Java you should check that number: "0 rows updated" usually means the record did not exist, which your code must handle.

Java UPDATE and DELETE with row counts

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The discount touches 2 rows (only books in stock), the stock update 1, and the update for book 42 reports 0 rows affected without any error: SQL does not consider "nothing matched" a failure. The DELETE removes the two out-of-stock books. The prices are now 35.91 and 27.90: the result of price * 0.9 is stored back into a DECIMAL(8, 2) column, so it is rounded to two decimal places.

The second UPDATE shows a pattern worth remembering: putting the condition in_stock > 0 into the UPDATE makes the check and the change one atomic step. Reading the stock in Java, checking it and then writing it back is a race condition when two customers buy the last copy at the same time.

Always write the WHERE first. UPDATE book SET price = 0 or DELETE FROM book without a WHERE changes every row. When you work on a real database by hand, run the matching SELECT first, and do risky changes inside a transaction you can roll back.

Dialect differences you will meet

SQL is standardized, but every database has its own dialect. H2 is close to the standard and can even imitate others (jdbc:h2:mem:test;MODE=PostgreSQL), which is why it is popular for tests. Still, test against the real database before production (Integration Testing shows how). The differences that bite most often:

Feature H2 PostgreSQL MySQL
Auto-generated key GENERATED BY DEFAULT AS IDENTITY (also AUTO_INCREMENT) GENERATED ... AS IDENTITY (older: SERIAL) AUTO_INCREMENT
Unquoted names stored as UPPER CASE lower case as written (table-name case depends on the OS)
String concatenation || or CONCAT || or CONCAT CONCAT (|| means OR by default)
Limit rows LIMIT n / FETCH FIRST n ROWS ONLY both LIMIT n
Insert or update MERGE INTO INSERT ... ON CONFLICT INSERT ... ON DUPLICATE KEY UPDATE
Boolean type BOOLEAN BOOLEAN TINYINT(1) behind BOOLEAN

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does SELECT price * 2 AS doubled FROM book WHERE doubled > 50 fail in most databases?

    The logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. Repeat the expression in WHERE (price * 2 > 50) or use a subquery.
  2. 2. Table book has a pages column with some NULLs. Which query returns the books with unknown page counts?

    Comparisons with NULL are unknown, never true, so = NULL matches nothing. Use IS NULL and IS NOT NULL.
  3. 3. Three authors exist; one has no books. What does SELECT a.name, COUNT(*) FROM author a LEFT JOIN book b ON b.author_id = a.id GROUP BY a.name report for that author?

    COUNT(*) counts rows, including the NULL-filled one. COUNT(b.id) counts only non-NULL book IDs and returns the correct 0.
  4. 4. Your registration code inserts a user and the database throws an SQLException because the email already exists. What should the code inspect to recognize that case portably?

    SQLState codes are standardized across databases, while messages are not. A SELECT-first check is not enough, because two requests can race between the check and the insert.
  5. 5. What does UPDATE book SET in_stock = in_stock - 1 WHERE id = 7 AND in_stock > 0 return when book 7 is already out of stock?

    The WHERE clause matches no row, so nothing is updated. Your Java code checks the count of 0 and reports "sold out", with no race condition.

Summary

  • Java code talks to relational databases in SQL, even behind an ORM, so reading and writing SQL is a core backend skill.
  • DDL defines tables; DML (INSERT, UPDATE, DELETE) changes rows and reports a row count; SELECT reads them. Name your columns in INSERT, use DECIMAL for money and identity columns for generated keys.
  • A SELECT is evaluated as FROM โ†’ WHERE โ†’ GROUP BY โ†’ HAVING โ†’ SELECT โ†’ ORDER BY โ†’ LIMIT. NULL needs IS NULL, and aggregates skip it.
  • Constraints (primary and foreign keys, NOT NULL, UNIQUE, CHECK) are enforced for every program. Violations arrive in Java as SQLExceptions whose SQLState (class 23) you can react to.
  • INNER JOIN keeps matches only; LEFT JOIN keeps every left row. Count the right table's key, and COALESCE sums.
  • Put conditions inside UPDATE/DELETE to make check-and-change atomic, and check the affected row count in Java.

Next: JDBC Introduction, where you learn how Java connects to a database: drivers, connection URLs, DriverManager and closing everything safely.