Old-School Backend

• Connections — six round trips before one is ready. select 1 a hundred times: 1,582 ms reconnecting, 29 ms reusing (measured, and in Wireshark)
• Forgetting to close — the database refused at 97 (measured)
• SQL injection — gluing strings showed other people's claims; with a placeholder, 0 rows (measured, in the database log and Wireshark)
• Types and null — getLong returns 0 when there is no total (measured)
• Bulk writes and transactions — 1,000 rows from 385 ms to 22 ms; a failure left half a claim behind (measured)

The stage is part 1's three machines. The claims server (192.168.1.20) connects to PostgreSQL 16 on the database server (192.168.1.30, named db.internal). All we use is PostgreSQL's JDBC driver (postgresql-42.7.14.jar, 1.2 MB). The practice program is Lesson4.java (238 non-blank lines). For this part we added an applicant column (applicant) to the claims table and reloaded three claims each for Arai and Furui. Checking who is actually logged in comes in part 11.

JDBC is Java's standard interface for databases (the java.sql package). Its shape is fixed; each database's driver fills it in. Old-school code often still carries the incantation Class.forName("org.postgresql.Driver"), but modern drivers write their own name inside the jar (META-INF/services/java.sql.Driver), so DriverManager.getConnection alone connects.

# The weight of one connection (three in the same JVM), and select 1 a hundred times
getConnection 1 回目  375.0 ms
getConnection 2 回目  34.5 ms
getConnection 3 回目  14.6 ms
select 1 を 100 回：毎回つなぐ 1582 ms ／ 1本を使い回す 29 ms

The first 375 ms includes loading the driver's classes. Even the third takes 14.6 ms. select 1 itself finishes in 0.3 ms, so just connecting costs fifty times as much. Counting in Wireshark: after the TCP handshake come a question about SSL, the user and database names, two round trips of password checking (SCRAM-SHA-256, which never sends the password itself; both sides prove they know a value hashed 4,096 times), a driver setting, and only on the seventh round trip does the SQL arrive. On top of that, the database starts a process for every connection.

So business applications don't throw connections away; they keep them in a pool and reuse them. That is why part 1's Spring Boot had HikariCP holding 10 connections.

A connection you're done with goes back with close(). What if you forget? We kept opening connections and holding on to them.

We got to 97; the database refused the 98th. PostgreSQL allows 100 simultaneous connections by default and keeps 3 of them for administrators. The limit is shared by the whole database, so when one application leaks connections, every other application using that database can't connect either. Pools are no different: borrow without returning and the pool runs dry fast.

The way to never forget is Java 7's try statement (try-with-resources).

try (Connection c = DriverManager.getConnection(URL, USER, PASS);   // whatever is opened inside ( )
     PreparedStatement ps = c.prepareStatement(sql);
     ResultSet r = ps.executeQuery()) {
    …
}                                       // is closed here, in reverse order (even on exceptions)

Whatever is opened inside ( ) is always closed on leaving the block, including when an exception throws you out. For a connection borrowed from a pool, close() means “return it to the pool”, not “hang up”.

Here is the most naive version of Arai's “my claims” screen: the applicant's name glued into the SQL text.

String sql = "select id, applicant, item, amount from expense where applicant = '" + who + "' order by id";
try (Connection c = DriverManager.getConnection(URL, USER, PASS);
     Statement s = c.createStatement();
     ResultSet r = s.executeQuery(sql)) {
    while (r.next()) { … r.getString("item") … r.getBigDecimal("amount") … }
}

# The string-gluing version, given an applicant name
SQL: select id, applicant, item, amount from expense where applicant = '新井' order by id
  EXP-2026-0101  新井  交通費                       1,280
  EXP-2026-0102  新井  会議費                       3,300
  EXP-2026-0103  新井  書籍（O'Reilly）             4,180
  → 3 件
SQL: select id, applicant, item, amount from expense where applicant = '古井' order by id
  EXP-2026-0104  古井  接待費（寿司）              48,000
  EXP-2026-0105  古井  交通費                       2,860
  EXP-2026-0106  古井  消耗品費（万年筆のインク）     980
  → 3 件
SQL: select id, applicant, item, amount from expense where applicant = '新井' OR '1'='1' order by id
  EXP-2026-0101  新井  交通費                       1,280
  EXP-2026-0102  新井  会議費                       3,300
  EXP-2026-0103  新井  書籍（O'Reilly）             4,180
  EXP-2026-0104  古井  接待費（寿司）              48,000
  EXP-2026-0105  古井  交通費                       2,860
  EXP-2026-0106  古井  消耗品費（万年筆のインク）     980
  → 6 件

新井 gives Arai's three, 古井 gives Furui's three. So far so good. But type 新井' OR '1'='1 into the name field and all six come back, Furui's ¥48,000 entertainment (sushi) included. The ' in the input closed the SQL string, and the OR '1'='1' after it was read as SQL. “The applicant is Arai, or 1 equals 1” is true for every row, so everyone's claims came back. That is SQL injection.

It breaks without any malice, too. On the search-by-item screen, we looked for the O'Reilly book Arai bought.

# Searching by item (gluing version): looking for the O'Reilly book
SQL: select id, applicant, item, amount from expense where applicant = '新井' and item like '%O'Reilly%' order by id
  PSQLException: Unterminated string literal started at position 96 in SQL select id, applicant, item, amount from expense where applicant = '新井' and item like '%O'Reilly%' order by id. Expected  char
SQL: select id, applicant, item, amount from expense where applicant = '新井' and item like '%Reilly%' order by id
  EXP-2026-0103  新井  書籍（O'Reilly）             4,180
  → 1 件

One apostrophe broke the SQL grammar, and it is an ordinary character in names (O'Brien) and company names. And this example only let data be seen; through the same hole, the conditions can be rewritten to change or delete other people's data. Never glue input into SQL statements. It's not a matter of taste; it's the rule.

The fix is to write ? (a placeholder) where a value goes and pass the value separately with setString and friends.

String sql = "select id, applicant, item, amount from expense where applicant = ? order by id";
try (Connection c = DriverManager.getConnection(URL, USER, PASS);
     PreparedStatement ps = c.prepareStatement(sql)) {
    ps.setString(1, who);                 // the value for the first ?
    try (ResultSet r = ps.executeQuery()) { … }
}

# The placeholder (?) version, given the same text
SQL: select id, applicant, item, amount from expense where applicant = ? order by id   ? = 新井
  EXP-2026-0101  新井  交通費                       1,280
  EXP-2026-0102  新井  会議費                       3,300
  EXP-2026-0103  新井  書籍（O'Reilly）             4,180
  → 3 件
SQL: select id, applicant, item, amount from expense where applicant = ? order by id   ? = 新井' OR '1'='1
  → 0 件
SQL: select id, applicant, item, amount from expense where applicant = ? and item like ? order by id   ? = 新井, %O'Reilly%
  EXP-2026-0103  新井  書籍（O'Reilly）             4,180
  → 1 件

Pass the same 新井' OR '1'='1 and you get 0 rows, because nobody is called “新井' OR '1'='1”. The O'Reilly book is found properly too. Let's compare what reached the database in its log.

# The database server log (log_statement=all): the gluing version above, placeholders below
23:53:40 LOG:  execute <unnamed>: select id, applicant, item, amount from expense where applicant = '新井' OR '1'='1' order by id
23:53:40 LOG:  execute <unnamed>: select id, applicant, item, amount from expense where applicant = $1 order by id
23:53:40 DETAIL:  parameters: $1 = '新井'' OR ''1''=''1'

In the glued version the input arrived dissolved into the SQL statement; from the database's side there's no telling the user's input from the SQL the programmer wrote. In the placeholder version the statement stays applicant = $1 and the value arrives in a separate box (Bind). The database only ever uses the value as a value, so however many quotes it contains, the statement doesn't change. Part 1's JdbcClient was written with ? and .params(…) because this is what it does inside.

Placeholders have limits. First, LIKE.

# In LIKE, % and _ still mean “anything” even inside ?
item like ?  ← "%%%"         → 3 件
item like ? escape '\'  ← "%\%%" → 0 件
item like ?  ← "%_%"         → 3 件
item like ? escape '\'  ← "%\_%" → 0 件

Put % or _ in the search word and, even inside a ?, LIKE still reads them as “anything” and “any one character”, so all three matched. Not injection, but a bug where searching for “100%” returns everything. Escape them with \ before passing them in and you get 0. PostgreSQL's default escape character is \ anyway, but databases differ, so it's safer to state it with escape.

# A sort column can't be a ?; lists go through any(?)
order by ?  ← "amount desc"  → [EXP-2026-0101 1280, EXP-2026-0102 3300, EXP-2026-0103 4180]
許可した列名だけつなぐ order by amount desc → [EXP-2026-0103 4180, EXP-2026-0102 3300, EXP-2026-0101 1280]
id = any(?)  ← [EXP-2026-0101, EXP-2026-0103] → 2 件

Only values go in a ?. Make the sort column a ? and you're sorting by the text “amount desc”: no sorting, and no error either. When a screen should choose a column, a table or asc/desc, glue in only names picked from an allowed list. For in (…) with several IDs, PostgreSQL lets you put an array in one ? and write = any(?).

Results come out of the ResultSet by type. We read the total for someone with no claims at all (中井, Nakai) several ways.

# Reading the total for someone with no claims (中井) with various getters
新井 の合計
  getBigDecimal → 8760.00
  getLong       → 8760   wasNull() = false
  getDouble     → 8760.0
  getObject(LocalDate) → 2026-10-28
中井 の合計
  getBigDecimal → null
  getLong       → 0   wasNull() = true
  getDouble     → 0.0
  getObject(LocalDate) → null
会議費 3300 の税込（numeric のまま DB で計算）
  getBigDecimal → 3630.000   getDouble → 3630.0   getString(amount) → 3300.00

In SQL, Nakai's total is “nothing” (NULL). getBigDecimal returned null, but getLong returned 0 and getDouble 0.0. long and double can't hold null, so JDBC returns 0, and the only way to learn it was really NULL is wasNull() right afterwards. “A total of ¥0” and “no claims yet” are different things in business. Read money with getBigDecimal (part 2) and dates with getObject(…, LocalDate.class). The tax calculation, done in the database as numeric, came out exactly as 3630.000.

Picture registering 1,000 travel expenses in one go at month end. We wrote the same insert four ways, three times each.

# Writing 1,000 rows (three runs)
    one by one (autocommit)           424 ms / 385 ms / 375 ms
    one by one (one commit)            60 ms /  55 ms /  54 ms
    addBatch                           29 ms /  40 ms /  30 ms
    addBatch＋reWriteBatchedInserts    23 ms /  22 ms /  22 ms

JDBC starts out committing every statement automatically. On each commit the database waits for the write to reach disk before it can say “recorded”, so 1,000 rows means 1,000 waits. Just wrapping them in one transaction took 385 ms down to 55 ms. Sending them together with addBatch cuts round trips, and with the driver's reWriteBatchedInserts=true the driver rewrites them into multi-row inserts: 22 ms. The lab database sits in the same machine, about 0.04 ms away; across a real network, cutting round trips pays off even more.

A business-trip claim is written to two tables, a header (the total) and lines (Shinkansen, hotel). The lines' amount column holds at most 8 digits including 2 decimals, so a ¥1,000,000 hotel bill doesn't fit. We wrote it anyway.

# A claim of one header and two lines; the second line's amount is too big for its column
自動コミットのまま
  見出し 1 行 OK
  明細 1（新幹線 12860）OK
  失敗: ERROR: numeric field overflow
  → 残ったもの：見出し 1 行、明細 1 行
setAutoCommit(false) と rollback
  見出し 1 行 OK
  明細 1（新幹線 12860）OK
  失敗: ERROR: numeric field overflow
  rollback
  → 残ったもの：見出し 0 行、明細 0 行

With autocommit, the two rows before the failure were already final, leaving a claim with a header and one line: a ¥1,012,860 claim whose only line is ¥12,860 of Shinkansen. Accounting would faint. Start with setAutoCommit(false), commit when everything worked and rollback if anything failed, and nothing is left.

c.setAutoCommit(false);                 // from here on, it's all one unit
try {
    header.executeUpdate();             // header
    for (…) line.executeUpdate();       // lines
    c.commit();                         // all went well: make it final
} catch (SQLException e) {
    c.rollback();                       // any failure: as if nothing happened
    throw e;
}

# The database server log (the setAutoCommit(false) run)
23:53:45 LOG:  execute <unnamed>: insert into expense (id, applicant, applied_on, item, amount, status) values ('EXP-2026-0107', '新井', '2026-10-30', '出張', $1, 'submitted')
23:53:45 DETAIL:  parameters: $1 = '1012860'
23:53:45 LOG:  execute <unnamed>: insert into expense_line (expense_id, line_no, item, amount) values ('EXP-2026-0107', $1, $2, $3)
23:53:45 DETAIL:  parameters: $1 = '1', $2 = '新幹線', $3 = '12860'
23:53:45 ERROR:  numeric field overflow
23:53:45 DETAIL:  A field with precision 8, scale 2 must round to an absolute value less than 10^6.
23:53:45 STATEMENT:  insert into expense_line (expense_id, line_no, item, amount) values ('EXP-2026-0107', $1, $2, $3)
23:53:45 LOG:  execute S_1: ROLLBACK

The database log shows the ROLLBACK arriving after the failure. Making a unit of work “all or nothing” is what a transaction is, and inside Spring's @Transactional it's exactly this setAutoCommit(false), commit and rollback. Two people changing the same claim at once is part 9's topic.

Change logging only on a practice database. Set log_statement = 'all' in production and the logs overflow fast, with every typed value left in them. Without PostgreSQL, Docker's postgres:16 image gets you one easily (we'll use it in part 15).