Chapter 11: Database Programming
Introduction
Learning Outcomes
Explain why databases are needed, how they differ from files, and the relational model (tables, rows, columns, primary keys).
Write CREATE TABLE, INSERT, SELECT, UPDATE, DELETE; narrow queries with WHERE, ORDER BY, LIMIT; combine tables with JOIN and group with GROUP BY.
Connect from Java to SQLite with Connection, Statement and ResultSet to insert, read, update and delete records; close resources with try-with-resources.
Prevent SQL injection with PreparedStatement; run several statements as one transaction with commit/rollback.
Database Concepts
Why Do Databases Matter?
- A program's variables and objects live in RAM: they are lost when the program exits.
- Persistent storage keeps data for the long term, after the program has exited. Student grades, bank accounts and game scores must all persist.
- A database is an organized collection of data that is easy to query. The software that manages it is a database management system (DBMS).
- Analogy: a database is like a library's cataloged archive; you find a book without walking every shelf.
Database Concepts
Files or a Database?
Plain files can be enough for some applications (a settings file, a log, a short list). As data grows and gets complex, you need a database: it offers key features for processing data efficiently.
| Feature | Flat file (txt, csv) | Database |
|---|---|---|
| Search | The file is read start to end | Fast with an index; a one-line SQL query |
| Update | Often the whole file is rewritten | Only the affected row changes |
| Concurrent access | Two programs writing at once can corrupt data | Safe with locking and transactions |
| Consistency | Format checking is entirely up to the programmer | Data types and constraints (NOT NULL, UNIQUE, CHECK) |
| Security | File permissions: all or nothing | Rights per user, table and field (server DBMSs; file permissions in SQLite) |
| After a crash | A half-done write can corrupt the file | A half-done transaction is rolled back |
Database Concepts
Key Features
Indexes find a record among millions of rows in no time.
Rights can be granted per table or even per column: only accounting sees the salary column (in server DBMSs, e.g. PostgreSQL/MySQL GRANT; SQLite has no users or rights, access depends on file permissions).
SQL is a standard; in Java, JDBC reaches different databases through one interface.
The program doesn't know how data is stored. It says what it wants in SQL; the DBMS decides how.
On top of these, databases offer integrity constraints and transaction support.
Database Concepts
The Relational Model: Tables, Rows, Columns
- A relational database stores data in tables.
- Column: one attribute of the data, with a name and a data type (
name TEXT). - Row: all the information about one entity (one user).
- Primary key: identifies each row uniquely; no two rows share an
id. - Java analogy: table ≈ class, column ≈ field, row ≈ object.
Database Concepts
Kinds of Databases
Tables and SQL. Oracle, SQL Server, MySQL, PostgreSQL. About 77% of usage.
Table-less models such as documents, key–value stores and search indexes, used in specific scenarios: MongoDB, Elasticsearch (about 23%).
SQLite: a relational database that runs inside the program; common where little data is shared.
The examples use SQLite, the easiest to set up. SQL and JDBC code is almost the same for other relational databases.
SQLite
What Is SQLite?
- An embedded relational database: it runs as a library inside the program.
- Serverless: no separate program, installation or administrator.
- Self-contained: all tables are stored in a single disk file (e.g.
sample.db). - Cross-platform: the same file opens on Windows, Linux, macOS and Android.
- Easy to install and use; open source and free.
- The most widely used embedded relational database in the world.
Common uses
- Mobile apps
- Embedded systems
- Small-scale applications
- Desktop programs such as browsers and e-mail clients
Not a good fit for
- Large systems where many users write at once over a network
SQLite
Server-Based vs Embedded Databases
| Server-based | SQLite | |
|---|---|---|
| Setup | Install a server, define users and passwords | Add one jar to the project |
| Data | Many files managed by the server | A single .db file |
| Concurrent writes | Many users | One writer at a time |
SQLite
The SQLite Driver and DB Browser
The jar that lets Java talk to SQLite (sqlite-jdbc-3.x.jar): github.com/xerial/sqlite-jdbc
A popular SQL client for SQLite: opens a .db file, shows the tables visually and runs SQL: sqlitebrowser.org/dl
Adding the driver to a project in Eclipse
- Download the jar and put it in a
libfolder in the project. - Right-click the project → Build Path → Configure Build Path…
- Libraries tab → Classpath → Add JARs… → pick the jar → Apply and Close.
From the command line
javac Connect.java java -cp ".;sqlite-jdbc-3.40.0.0.jar" Connect
On Linux and macOS the separator is : instead of ;. Compiling needs no driver; the java.sql package ships with the JDK.
SQL Basics
Common SQL Commands
| Command | Purpose | Example | CRUD |
|---|---|---|---|
CREATE TABLE | Creates a new table | CREATE TABLE users (id INTEGER, name TEXT) | — |
INSERT INTO | Adds a new record | INSERT INTO users(name) VALUES('Ali') | Create |
SELECT | Queries the table | SELECT * FROM users | Read |
UPDATE | Updates existing records | UPDATE users SET name = 'Efe' WHERE id = 1 | Update |
DELETE | Deletes records | DELETE FROM users WHERE id = 1 | Delete |
- SQL (Structured Query Language) is the common language of relational databases.
- Keywords are case-insensitive; they are written in CAPITALS for readability. Text values go in single quotes:
'Ali'. *means “all columns”.WHEREpicks the rows a command applies to.
SQL Basics
CREATE TABLE and Data Types
CREATE TABLE IF NOT EXISTS members ( id INTEGER PRIMARY KEY, -- unique name TEXT NOT NULL, -- required email TEXT UNIQUE, -- no repeats age INTEGER CHECK (age >= 0) )
IF NOT EXISTS: no error if the table already exists.INTEGER PRIMARY KEY: if no value is given, SQLite assigns the largest id + 1.- A command that breaks a constraint is rejected:
NOT NULL constraint failed: members.name,CHECK constraint failed: age >= 0.
| SQLite type | Java counterpart |
|---|---|
INTEGER | int, long · getInt |
REAL | double · getDouble |
TEXT | String · getString |
BLOB | byte[] · getBytes |
NULL | no value |
SQL Basics
SQL Commands, Step by Step
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)
The table is created: three columns, no rows yet.
INSERT INTO users(name, email) VALUES('Ahmet Yılmaz', 'ahmet@example.com')
No id was given; SQLite assigned 1. 1 row affected.
INSERT INTO users(name, email) VALUES('Ayşe Demir', 'ayse@example.com')
The second record got id = 2 (the largest id + 1).
UPDATE users SET name = 'Mehmet Akdeniz' WHERE id = 1
WHERE id = 1 picked only the first row; only name changed, email stayed the same.
DELETE FROM users WHERE id = 1
The row with id = 1 is deleted. 1 row affected.
SELECT * FROM users
A query doesn't change the table, it only reads: the result is one row, (2, Ayşe Demir, ayse@example.com).
SQL Basics
SELECT: Filtering, Sorting, Limiting
| Query | Result |
|---|---|
SELECT player, score FROM scores | naz 81, ada 72, ece 58 |
SELECT player, score FROM scores | can 95, efe 95, naz 81 |
SELECT player, score FROM scores | ece 58, efe 95 |
SELECT COUNT(*), AVG(score), MAX(score) | 4 · 85.75 · 95 |
WHEREfilters rows (=,<>,<,>=,AND,OR,LIKE 'e%': “starts with e”).ORDER BY … DESCsorts from largest to smallest; ties use the second key (player).LIMIT 3takes the first 3 rows.
SQL Basics · Step-by-step trace
How Does a SELECT Query Run?
String sql = "SELECT player, score FROM scores "
+ "WHERE score > 70 "
+ "ORDER BY player DESC LIMIT 2";
try (Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(sql)) {
while (rs.next()) {
System.out.println(rs.getString("player") + " "
+ rs.getInt("score"));
}
}The query is a single string. The scores table has 5 rows.
executeQuery sends the query. The database starts with FROM: every row of the table.
WHERE score > 70: ece fails the condition and is dropped; 4 rows remain.
ORDER BY player DESC: names are sorted in reverse alphabetical order (not by score).
LIMIT 2: the first two rows of the sorted list form the result set.
The cursor starts before the first row; next() moves it to row 1.
The columns are read by name and printed.
The cursor moves to row 2.
efe 95 is printed.
No more rows: next() returns false and the loop ends. When the try ends, rs and st are closed.
Relations and Grouping
Avoiding Repetition: Normalization
- In one table, the same fact (Ali's phone) is repeated in every order: wasted space and a risk of inconsistency.
- Update anomaly: when the phone changes, every row must be fixed; miss one and Ali has two different phones.
- Insert anomaly: a customer with no orders yet can't be recorded.
- Delete anomaly: deleting Ece's only order loses Ece's details too.
- Normalization: split the tables so each fact is kept in one place. The tables are linked by a key (
customer_id). - First normal form: one value per cell. A list like
'555-11, 555-33'moves to separate rows or a separate table.
Relations and Grouping
Foreign Keys and Relationships
CREATE TABLE depts ( id INTEGER PRIMARY KEY, name TEXT NOT NULL); CREATE TABLE students ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept_id INTEGER REFERENCES depts(id));
- A foreign key is a column that points to another table's primary key. A dept can have many students: a one-to-many relationship.
- Adding a student to a missing dept (
dept_id = 9) or deleting a dept that has students is rejected:FOREIGN KEY constraint failed. - SQLite doesn't check this by default: every connection needs
stmt.execute("PRAGMA foreign_keys = ON"). With it off,dept_id = 9is silently accepted. - A many-to-many relationship (student–course) needs a third, linking table:
enrollments(student_id, course_id). - Java analogy: a
Deptreference inside aStudentobject.
Relations and Grouping
Combining Tables with JOIN
SELECT s.name AS student, d.name AS dept FROM students s JOIN depts d ON s.dept_id = d.id ORDER BY s.id
| student | JOIN (INNER) | LEFT JOIN |
|---|---|---|
| Ada | CS | CS |
| Bora | EE | EE |
| Cem | CS | CS |
| Deniz | no row | NULL |
- A JOIN matches the rows of two tables by the
ONcondition and produces one result table. sanddare table aliases. Both tables have anamecolumn, so we writes.nameandd.name; a barenamegivesambiguous column name: name.ASrenames a result column: Java reads it withrs.getString("dept").- INNER JOIN (plain
JOIN): only rows that have a match. Deniz, whose dept isNULL, is missing from the result. - LEFT JOIN: every row of the left table; with no match the right side is
NULL,nullin Java. - Forget
ON(FROM students, depts) and every row pairs with every row: a 12-row Cartesian product, 4 × 3.
Relations and Grouping · Step-by-step trace
A JOIN Query, Step by Step
String sql = "SELECT s.name AS student, d.name AS dept "
+ "FROM students s JOIN depts d "
+ "ON s.dept_id = d.id ORDER BY s.id";
try (ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
System.out.println(rs.getString("student") + " - "
+ rs.getString("dept"));
}
}The query refers to the two tables by aliases: s = students, d = depts.
executeQuery: Ada's dept_id is 1; id = 1 is found in depts → (Ada, CS).
Bora → 2 → EE.
Cem → 1: the same dept row is used a second time.
Deniz's dept_id is NULL: it equals no id, so the row doesn't make it into the result.
next() moves to the first row; the columns are read by the names given with AS.
The second row is printed.
The third row is printed.
No more rows; the loop ends. With LEFT JOIN, a fourth row Deniz - null would appear.
Relations and Grouping
Grouping: GROUP BY and HAVING
GROUP BYcollects rows with the same value into a group; aggregate functions (COUNT,SUM,AVG,MIN,MAX) are computed per group.WHEREfilters rows before grouping,HAVINGfilters groups after it.WHERE SUM(amount) >= 150is an error:misuse of aggregate: SUM().- Logical order of evaluation:
FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT.
Query (SELECT customer, COUNT(*), SUM(amount) FROM orders …) | Result |
|---|---|
GROUP BY customer | Ali (3, 200) · Can (1, 200) · Ece (2, 120) |
WHERE amount >= 50 GROUP BY customer | Ali (2, 170) · Can (1, 200) · Ece (1, 80) |
GROUP BY customer HAVING SUM(amount) >= 150 | Ali (3, 200) · Can (1, 200) |
With a JOIN: SELECT d.name, COUNT(s.id) FROM depts d LEFT JOIN students s ON s.dept_id = d.id GROUP BY d.id → CS 2 · EE 1 · ME 0. COUNT(s.id) skips NULLs; with COUNT(*), ME, which has no students, would get 1.
Relations and Grouping · Step-by-step trace
A GROUP BY Query, Step by Step
String sql = "SELECT customer, COUNT(*) AS num, "
+ "SUM(amount) AS total FROM orders "
+ "GROUP BY customer HAVING SUM(amount) >= 150 "
+ "ORDER BY total DESC, customer";
try (ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
System.out.println(rs.getString("customer") + ": "
+ rs.getInt("num") + " orders, "
+ rs.getInt("total") + " TL");
}
}The orders table has 6 rows; the query is a single string.
FROM and GROUP BY customer: the rows are split into three groups by customer name.
For each group, COUNT(*) and SUM(amount): Ali 120 + 50 + 30 = 200, Can 200, Ece 80 + 40 = 120.
HAVING SUM(amount) >= 150: Ece's group is dropped (120).
ORDER BY total DESC, customer: Ali and Can tie at 200; the second key is the name, so Ali comes first.
next() moves to the first row; the aliases (num, total) are read as column names.
The second row is printed.
The loop ends. The result has one row per group; individual orders don't appear.
Relations and Grouping
NULL: The Absence of a Value
NULLmeans “unknown / missing”; it is not0or empty text ('').- Any comparison with
NULLgives an unknown result:WHERE dept_id = NULLselects no rows. UseIS NULLorIS NOT NULL. WHERE dept_id <> 1doesn't select Deniz either: we don't even know thatNULLdiffers from 1.NULL + 1isNULL.COALESCE(dept_id, 0)puts 0 in place ofNULL.COUNT(*)counts rows,COUNT(dept_id)non-NULLvalues: 4 and 3. With no matching rows,AVGandSUMreturnNULL.
String sql = "SELECT name, dept_id FROM students "
+ "WHERE id >= 3";
try (ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) {
int d = rs.getInt("dept_id");
boolean none = rs.wasNull();
System.out.println(rs.getString("name") + " " + d
+ (none ? " (NULL)" : ""));
}
}Cem 1 Deniz 0 (NULL)
getIntreturns 0 forNULL; to tell 0 fromNULL, callwasNull()right after.wasNull()looks at the last column read: called aftergetString("name"), it would ask about the name and(NULL)would never print.getStringandgetObjectreturnnullforNULL.
Relations and Grouping
Indexes: Faster Searches
CREATE INDEX idx_stu_name ON students(name); EXPLAIN QUERY PLAN SELECT * FROM students WHERE name = 'Cem';
- An index is an extra structure (a B-tree) that keeps a column's values sorted, like the index at the back of a book. A search goes to the right row instead of scanning the whole table.
- Primary keys and
UNIQUEcolumns get an index automatically. - The cost: every
INSERT,UPDATE,DELETEupdates the index too, and the index takes disk space. Put indexes on columns searched often and used inWHERE,JOINandORDER BY. EXPLAIN QUERY PLAN(SQLite) shows how a query will be executed.
| Case | EXPLAIN QUERY PLAN output |
|---|---|
| No index | SCAN students |
| With the index | SEARCH students USING INDEX idx_stu_name (name=?) |
WHERE id = 3 | SEARCH … USING INTEGER PRIMARY KEY (rowid=?) |
WHERE name LIKE '%em' | SCAN students (a leading wildcard can't use the index) |
JDBC
JDBC (Java Database Connectivity)
- JDBC (Java Database Connectivity) is Java's standard API for connecting to databases; it lives in
java.sqland ships with the JDK. - The program uses only the JDBC interfaces; each database vendor supplies a driver that implements them.
- Switching databases usually changes only the driver jar and the connection URL.
- Polymorphism at work:
Connectionis an interface; in SQLite the real object's class isorg.sqlite.jdbc4.JDBC4Connection.
JDBC
The Core JDBC Types
| Type | Purpose | Key methods |
|---|---|---|
DriverManager | Finds the right driver and opens a connection | getConnection(url), getConnection(url, user, password) |
Connection | An open session with the database | createStatement(), prepareStatement(sql), commit(), rollback(), close() |
Statement | Runs a fixed SQL string | execute, executeUpdate, executeQuery |
PreparedStatement | Precompiled SQL with ? placeholders | setInt, setString, executeUpdate(), executeQuery() |
ResultSet | The rows of a query result, plus a cursor | next(), getInt("id"), getString("name") |
SQLException | A database error; a checked exception | getMessage(), getErrorCode() |
All of them are in java.sql: put import java.sql.*; at the top of the file. Connection, Statement and ResultSet are resources that must be closed (AutoCloseable).
JDBC
The Connection URL (JDBC URL)
| Connection URL | Meaning |
|---|---|
jdbc:sqlite:sample.db | sample.db in the working directory; created empty if missing |
jdbc:sqlite:C:/data/school.db | A file by its full path |
jdbc:sqlite::memory: | A temporary in-memory database, gone when the connection closes (for experiments) |
jdbc:mysql://localhost:3306/testdb | A MySQL server: host, port, database name; plus a user name and password |
- Format:
jdbc:<subprotocol>:<database file or address>.DriverManagerpicks the driver from the subprotocol. - Since JDBC 4.0 (Java 6), the driver is loaded automatically from the jar on the classpath. The
Class.forName("org.sqlite.JDBC")line in old code is no longer needed. - If the driver isn't on the classpath:
No suitable driver found for jdbc:sqlite:sample.db.
JDBC
The Steps of a JDBC Program
try (Connection conn = DriverManager.getConnection(url); // 1 Statement stmt = conn.createStatement(); // 2 ResultSet rs = stmt.executeQuery(sql)) { // 3 while (rs.next()) { // 4 System.out.println(rs.getInt(1)); } } // 5: rs, stmt, conn auto-closed in reverse
- Resources are opened in the try-with-resources parentheses; when the block ends (even on an error) they close in reverse order: first
rs, thenstmt, finallyconn. - Unclosed connections can leave the file locked and leak memory.
getInt(1): column numbers start at 1.
Basic Operations
Connecting to SQLite
// Path to the database file (connection URL) String url = "jdbc:sqlite:sample.db"; try (Connection conn = DriverManager.getConnection(url)) { // Print a message if connected. if (conn != null) { System.out.println("Connected!"); } } catch (SQLException e) { // Print the error message on failure. System.out.println(e.getMessage()); }
Connected!
getConnectioncreates the file if it doesn't exist (an empty database).- If it can't connect, it doesn't return
null; it throwsSQLException.if (conn != null)is just an extra safeguard. SQLExceptionis a checked exception: catch it or declare it withthrows.
Basic Operations
Creating a Table
String url = "jdbc:sqlite:sample.db";
String sql = "CREATE TABLE IF NOT EXISTS users "
+ "(id INTEGER PRIMARY KEY, name TEXT, email TEXT)";
try (Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement()) {
// Create the table.
stmt.execute(sql);
System.out.println("Table created!");
} catch (SQLException e) {
System.out.println(e.getMessage());
}Table created!
execute(sql)runs any SQL; it returnstrueif it produced a result set,falseforCREATE.- Thanks to
IF NOT EXISTS, running the program again is no error. Without it:table users already exists. - Several resources go in one
try (…; …), separated by;.
Basic Operations
Inserting Data
String url = "jdbc:sqlite:sample.db";
String sql = "INSERT INTO users(name, email) "
+ "VALUES('Ahmet Yılmaz', 'ahmet@example.com')";
try (Connection conn = DriverManager.getConnection(url);
Statement stmt = conn.createStatement()) {
// Insert data into the database.
int n = stmt.executeUpdate(sql);
System.out.println("Data inserted! (" + n + " row)");
} catch (SQLException e) {
System.out.println(e.getMessage());
}Data inserted! (1 row)
executeUpdatereturns the number of rows affected (anint).- No
idwas given; SQLite assigned1. - Each run of the program adds a new row (id 2, 3, …).
- Text values in SQL go in single quotes.
Basic Operations
Reading Data
String url = "jdbc:sqlite:sample.db"; String sql = "SELECT * FROM users"; try (Connection conn = DriverManager.getConnection(url); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { // Read and print the rows one by one. while (rs.next()) { System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") + ", Email: " + rs.getString("email")); } } catch (SQLException e) { System.out.println(e.getMessage()); }
ID: 1, Name: Ahmet Yılmaz, Email: ahmet@example.com
executeQueryreturns aResultSet.rs.next()moves the cursor to the next row; it returnstrueif there is one,falseotherwise.getInt("id"),getString("name"): read the current row's columns by name.
Basic Operations
Reading with a ResultSet, Step by Step
The connection opens and the query runs. rs holds the two-row result; the cursor waits before the first row.
rs.next() moves the cursor to row 1 and returns true: we enter the loop.
The columns are read from the current row.
The row is printed.
The cursor moves to row 2; true again.
This time row 2's values are read.
The second row is printed.
No more rows: rs.next() returns false and the loop ends.
The line after the loop runs.
The try block ends: rs, stmt, conn are closed automatically, in that order.
String url = "jdbc:sqlite:reading.db"; String sql = "SELECT * FROM users"; try (Connection conn = DriverManager.getConnection(url); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { while (rs.next()) { int id = rs.getInt("id"); String name = rs.getString("name"); System.out.println(id + " - " + name); } System.out.println("Done reading."); } catch (SQLException e) { System.out.println(e.getMessage()); }
Basic Operations
Updating Data
String url = "jdbc:sqlite:sample.db"; String sql = "UPDATE users SET name = 'Mehmet Akdeniz' WHERE id = 1"; try (Connection conn = DriverManager.getConnection(url); Statement stmt = conn.createStatement()) { // Update data in the database. int n = stmt.executeUpdate(sql); System.out.println("Data updated! (" + n + " row)"); } catch (SQLException e) { System.out.println(e.getMessage()); }
Data updated! (1 row)
- Read before:
ID: 1, Name: Ahmet Yılmaz, …; after:ID: 1, Name: Mehmet Akdeniz, … SETchanges only the listed column;emailstays the same.WHERE id = 1picks the row to update. Forget WHERE and every row is updated.
Basic Operations
Deleting Data
String url = "jdbc:sqlite:sample.db"; String sql = "DELETE FROM users WHERE id = 1"; try (Connection conn = DriverManager.getConnection(url); Statement stmt = conn.createStatement()) { // Delete data from the database. int n = stmt.executeUpdate(sql); System.out.println("Data deleted! (" + n + " row)"); } catch (SQLException e) { System.out.println(e.getMessage()); }
Data deleted! (1 row) Data deleted! (0 row)
- On the second run there is no row left to delete: no error,
executeUpdatejust returns0. - So it is better to word the message by the returned count (Exercise 1).
DELETE FROM userswithout WHERE deletes every row; the table itself remains.
Basic Operations
execute, executeUpdate, executeQuery
| Method | Returns | Use |
|---|---|---|
execute | boolean | CREATE TABLE, DROP |
executeUpdate | int row count | INSERT, UPDATE, DELETE |
executeQuery | ResultSet | SELECT |
The wrong method fails: running an INSERT with executeQuery → query does not return ResultSet.
> java Connect Connected! > java CreateTable Table created! > java InsertData Data inserted! (1 row) > java ReadData ID: 1, Name: Ahmet Yılmaz, Email: ahmet@example.com > java UpdateData Data updated! (1 row) > java ReadData ID: 1, Name: Mehmet Akdeniz, Email: ahmet@example.com > java DeleteData Data deleted! (1 row) > java ReadData > java DeleteData Data deleted! (0 row)
Basic Operations · Step-by-step trace
What Does executeUpdate Return?
String[] names = {"stapler", "scissors", "tape"};
int[] stock = {40, 0, 15};
PreparedStatement ins = conn.prepareStatement(
"INSERT INTO supplies(name, stock) VALUES(?, ?)");
for (int i = 0; i < names.length; i++) {
ins.setString(1, names[i]);
ins.setInt(2, stock[i]);
ins.executeUpdate();
}
int u = stmt.executeUpdate(
"UPDATE supplies SET stock = stock + 10 WHERE stock < 20");
int d = stmt.executeUpdate("DELETE FROM supplies WHERE stock > 100");
System.out.println(u + " updated, " + d + " deleted");The insert statement is prepared once; in the loop only the values change.
On the first pass the placeholders are filled.
executeUpdate returns 1; SQLite assigns id = 1.
The same prepared statement runs again with new values.
The third item is inserted; when i = 3 the loop ends.
WHERE stock < 20 picks scissors (0) and tape (15); 10 is added to both. The return value is the number of rows affected: 2.
No item has more than 100 in stock: no error, just 0.
From these counts the program can give accurate messages such as "record not found".
PreparedStatement
SQL Injection
String input = "x' OR '1'='1"; // the "name" the user typed // DANGEROUS: input pasted into the SQL text String sql = "SELECT name FROM people WHERE name = '" + input + "'"; System.out.println(sql); ResultSet rs1 = stmt.executeQuery(sql); // SAFE: input sent only as a value PreparedStatement ps = conn.prepareStatement( "SELECT name FROM people WHERE name = ?"); ps.setString(1, input); ResultSet rs2 = ps.executeQuery();
SELECT name FROM people WHERE name = 'x' OR '1'='1' Concatenation: 3 rows PreparedStatement: 0 rows
- The user's quote changed the SQL text:
'1'='1'is always true, so every row came back. - On a login screen, this means logging in without a password.
- Input sent with
?is only a value; it can never become part of the SQL.
PreparedStatement · Step-by-step trace
SQL Injection, Step by Step
String input = "x' OR '1'='1";
String sql = "SELECT name FROM people WHERE name = '"
+ input + "'";
int n = 0;
try (ResultSet rs = stmt.executeQuery(sql)) {
while (rs.next()) n++;
}
System.out.println("Concatenation: " + n + " rows");
PreparedStatement ps = conn.prepareStatement(
"SELECT name FROM people WHERE name = ?");
ps.setString(1, input);
n = 0;
try (ResultSet rs = ps.executeQuery()) { while (rs.next()) n++; }
System.out.println("PreparedStatement: " + n + " rows");Instead of a name, the user typed text with a quote and an OR. The table has 3 people.
After concatenation the user's quote closed the name value, and the OR '1'='1' after it is now part of the SQL.
The database evaluates the condition on each row: for Ali, the right side of OR is always true → the row is selected.
Ayşe and Can are selected too: the condition filters out nothing.
The loop runs 3 times. On a login screen this means logging in without a password.
The result is printed.
This time the SQL is compiled first; ? is just a slot for a value. The statement's structure can no longer change.
The whole input is bound as one text value; the quotes in it are ordinary characters.
No one's name equals this 12-character text: no rows.
Same input, two different results. Always pass user-supplied values to SQL with ?.
PreparedStatement
PreparedStatement and Placeholders
static void add(Connection conn, String name, String surname, int age) throws SQLException { String sql = "INSERT INTO people(name, surname, age) " + "VALUES(?, ?, ?)"; try (PreparedStatement ps = conn.prepareStatement(sql)) { ps.setString(1, name); // 1st question mark ps.setString(2, surname); // 2nd question mark ps.setInt(3, age); // 3rd question mark ps.executeUpdate(); } }
Why?
- Safe: prevents SQL injection.
- No quoting trouble:
"O'Brien"is stored without a hitch. - The same statement is reused with different values.
setXxx(index, value) fills each ?; the index starts at 1. Then call executeUpdate() with no arguments.
PreparedStatement
The People Program: Update and Delete Methods
static void update(Connection conn, String name, String surname, int age) throws SQLException { String sql = "UPDATE people SET surname = ?, age = ? " + "WHERE name = ?"; try (PreparedStatement ps = conn.prepareStatement(sql)) { ps.setString(1, surname); ps.setInt(2, age); ps.setString(3, name); ps.executeUpdate(); } } static void delete(Connection conn, String name) throws SQLException { String sql = "DELETE FROM people WHERE name = ?"; try (PreparedStatement ps = conn.prepareStatement(sql)) { ps.setString(1, name); ps.executeUpdate(); } }
- Each method takes the
Connectionas a parameter: the connection is opened once and used for many jobs. - The
?s are numbered by their order in the SQL: inupdate, the name is placeholder 3. throws SQLException: errors pass to the caller; sincemainalso declaresthrows, an error stops the program with a stack trace.
PreparedStatement
The People Program: The List Method
static void list(Connection conn) throws SQLException { String sql = "SELECT id, name, surname, age FROM people"; try (Statement st = conn.createStatement(); ResultSet rs = st.executeQuery(sql)) { while (rs.next()) { System.out.println(rs.getInt("id") + " " + rs.getString("name") + " " + rs.getString("surname") + " " + rs.getInt("age")); } System.out.println("---"); } }
1 Ahmet Akdeniz 35 2 Ayşe Demir 40 ---
- The query has no variable values, so a plain
Statementis enough. - Each call to
listreads the table as it is now and ends with---.
PreparedStatement
The People Program, Step by Step
people.db is created if missing; the connection and stmt are opened.
The table is created; it is empty for now.
add: the ?s are filled and the row is inserted, id = 1.
The second person is added with id = 2.
WHERE name = ?: Ahmet's surname and age change.
list prints every row and ---.
Ahmet's row is deleted.
Only Ayşe is left in the table.
The connection closes; the data persists in people.db.
public static void main(String[] args) throws SQLException { String url = "jdbc:sqlite:people.db"; try (Connection conn = DriverManager.getConnection(url); Statement stmt = conn.createStatement()) { stmt.execute("CREATE TABLE IF NOT EXISTS people (" + "id INTEGER PRIMARY KEY, name TEXT NOT NULL, " + "surname TEXT NOT NULL, age INTEGER NOT NULL)"); add(conn, "Ahmet", "Yılmaz", 30); add(conn, "Ayşe", "Demir", 40); update(conn, "Ahmet", "Akdeniz", 35); list(conn); delete(conn, "Ahmet"); list(conn); } }
Advanced JDBC
Generated Keys: getGeneratedKeys
String sql = "INSERT INTO students(name, dept_id) "
+ "VALUES(?, ?)";
try (PreparedStatement ps = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, "Ece");
ps.setInt(2, 3);
ps.executeUpdate();
try (ResultSet k = ps.getGeneratedKeys()) {
if (k.next()) {
System.out.println("New id: " + k.getInt(1));
}
}
}New id: 5
- The database decides a new row's
id; the program learns it withgetGeneratedKeys(). Example: to add a new order's line items, you need the order'sid. - Pass
Statement.RETURN_GENERATED_KEYStoprepareStatement; the single column of the returnedResultSetis the new key. - The table had 4 students; Ece got
id = 5. - If a deleted record's number must never be reused (invoice or order numbers), use
AUTOINCREMENT. MySQL's equivalent isAUTO_INCREMENT.
| 1, 2, 3 inserted; 3 deleted; new row → | id |
|---|---|
id INTEGER PRIMARY KEY | 3 (largest id + 1) |
id INTEGER PRIMARY KEY AUTOINCREMENT | 4 (used numbers aren't reused) |
Advanced JDBC
From Rows to Objects: The DAO Pattern
class Student { private final int id; private final String name; Student(int id, String name) { this.id = id; this.name = name; } public String toString() { return "#" + id + " " + name; } }
StudentDao method | SQL | Returns |
|---|---|---|
add(name, dept) | INSERT | the new id |
find(id) | SELECT … WHERE id = ? | a Student or null |
inDept(dept) | SELECT … WHERE dept_id = ? | List<Student> |
delete(id) | DELETE | deleted? (boolean) |
- In an object-oriented program, each row becomes an object: table ≈ class, row ≈ object.
- A DAO (Data Access Object) gathers all of a table's SQL in one class. The rest of the program never sees SQL.
- If the database changes, only the DAO changes. The DAO gets its connection through the constructor.
- ORM (object-relational mapping) libraries such as JPA and Hibernate do this mapping automatically.
Advanced JDBC · Step-by-step trace
From Rows to Objects, Step by Step
static List<Student> inDept(Connection conn, int dept) throws SQLException { List<Student> list = new ArrayList<>(); String sql = "SELECT id, name FROM students " + "WHERE dept_id = ? ORDER BY id"; try (PreparedStatement ps = conn.prepareStatement(sql)) { ps.setInt(1, dept); try (ResultSet rs = ps.executeQuery()) { while (rs.next()) { list.add(new Student(rs.getInt("id"), rs.getString("name"))); } } } return list; } // inside main: List<Student> list = inDept(conn, 1); for (Student s : list) System.out.println(s);
main calls the method with dept = 1. The connection is open in main.
An empty list is created; the query is prepared with ?.
? = 1 is bound and the query runs: dept 1 has Ada and Cem.
First row: id = 1, name = Ada are read; new Student(1, "Ada") is added to the list.
The second row gives a second object: Student #3 Cem.
next() is false: the try blocks end and rs and ps close. The connection is closed by main, which opened it.
The method returns a List<Student> (2 items); its frame leaves the stack.
Each object is printed via toString(). main knows nothing about SQL.
MySQL
Same Code, Another Database: MySQL
String url = "jdbc:mysql://localhost:3306/testdb"; String user = "root"; String password = "secret"; String sql = "SELECT id, name, email FROM users"; try (Connection conn = DriverManager.getConnection(url, user, password); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery(sql)) { while (rs.next()) { System.out.println(rs.getInt("id") + " " + rs.getString("name")); } } catch (SQLException e) { System.out.println(e.getMessage()); }
- The JDBC code is the same; what changes is the URL, the user name, the password and the driver jar (
mysql-connector-j). - MySQL is a server: it must be installed and running first, and the
testdbdatabase created. - The driver class is
com.mysql.cj.jdbc.Driver; the old namecom.mysql.jdbc.Driveris deprecated. - Real projects never hard-code the password; they read it from a config file or an environment variable.
Transactions
Transactions and ACID
- A transfer: add 300 TL to Ece's account and take 300 TL from Ali's. If one of the two
UPDATEs happens and the other doesn't, money is created from nothing or lost. - A transaction is one or more SQL statements run as a single unit of work: all or nothing.
- JDBC's default is auto-commit: every statement is permanent at once.
conn.setAutoCommit(false)→ statements →conn.commit()(make permanent) orconn.rollback()(undo).
The transaction is applied entirely or not at all.
After the transaction, all constraints hold (e.g. balance ≥ 0).
Concurrent transactions don't see each other's half-done state.
Committed data survives even a power cut.
Transactions
A Transfer: Setup and Result
static final String DEPOSIT = "UPDATE accounts SET balance = balance + ? WHERE id = ?"; static final String WITHDRAW = "UPDATE accounts SET balance = balance - ? WHERE id = ?"; public static void main(String[] args) throws SQLException { String url = "jdbc:sqlite::memory:"; // in memory, temporary try (Connection conn = DriverManager.getConnection(url); Statement st = conn.createStatement()) { st.execute("CREATE TABLE accounts (id INTEGER PRIMARY KEY, " + "owner TEXT, balance INTEGER CHECK (balance >= 0))"); st.execute("INSERT INTO accounts(owner, balance) " + "VALUES ('Ali', 1000), ('Ece', 500)"); transfer(conn, 1, 2, 300); // Ali → Ece 300 TL transfer(conn, 1, 2, 5000); // Ali can't afford it printAll(conn); } }
300 TL transferred. Rolled back: 5000 TL Ali: 700 Ece: 800
CHECK (balance >= 0): a statement that drives a balance negative throwsSQLException.- In the second transfer, 5000 TL had already been deposited to Ece;
rollbackundid that too. printAllis aSELECTloop, likelist.
Transactions
commit and rollback, Step by Step
Auto-commit is turned off: from now on, changes are tentative until commit.
300 TL is deposited to Ece: 500 → 800.
300 TL is withdrawn from Ali: 1000 → 700. The constraint holds.
commit(): both changes become permanent together.
finally: auto-commit is turned back on; the method ends.
Second call: 5000 TL is deposited to Ece (tentatively): 800 → 5800.
700 − 5000 would be < 0: the CHECK fails and SQLException is thrown.
rollback(): the tentative deposit is undone too. No money was lost or created.
static void transfer(Connection conn, int from, int to, int amount) throws SQLException { conn.setAutoCommit(false); // transaction starts try (PreparedStatement p1 = conn.prepareStatement(DEPOSIT); PreparedStatement p2 = conn.prepareStatement(WITHDRAW)) { p1.setInt(1, amount); p1.setInt(2, to); p1.executeUpdate(); // deposit to receiver p2.setInt(1, amount); p2.setInt(2, from); p2.executeUpdate(); // withdraw from sender conn.commit(); // make permanent System.out.println(amount + " TL transferred."); } catch (SQLException e) { conn.rollback(); // undo System.out.println("Rolled back: " + amount + " TL"); } finally { conn.setAutoCommit(true); } }
Transactions
Batching: addBatch and executeBatch
conn.setAutoCommit(false); try (PreparedStatement ps = conn.prepareStatement( "INSERT INTO readings(value) VALUES(?)")) { for (int i = 1; i <= 1000; i++) { ps.setInt(1, i); ps.addBatch(); } int[] result = ps.executeBatch(); conn.commit(); System.out.println(result.length + " statements, " + "the first affected " + result[0] + " row"); } catch (SQLException e) { conn.rollback(); throw e; } finally { conn.setAutoCommit(true); }
1000 statements, the first affected 1 row
addBatch()adds the filled-in statement to a queue;executeBatch()sends the whole queue and returns each statement's affected row count as anint[].- With auto-commit on, every
INSERTis its own transaction and is written to disk separately. In one test with a.dbfile on disk, 1000 rows took about 2.6 s one by one and about 0.01 s batched in one transaction (varies by machine). - It is all one transaction, so if one fails,
rollback()undoes the whole queue's effect: all or nothing. - For very large loads, send the queue in pieces (e.g. an
executeBatchevery 500addBatches) so memory doesn't balloon.
Summary
Summary
| Topic | Essence |
|---|---|
| Database | Persistent, organized, queryable data managed by a DBMS. Tables, rows, columns, primary keys. |
| SQLite | Serverless, embedded, one file. URL: jdbc:sqlite:sample.db; the driver jar must be on the classpath. |
| SQL | CREATE TABLE, INSERT, SELECT, UPDATE, DELETE; WHERE, ORDER BY, LIMIT. |
| Relations | Foreign keys; JOIN … ON; GROUP BY / HAVING; IS NULL for NULL. |
| JDBC | DriverManager → Connection → Statement / PreparedStatement → ResultSet. |
| Execution | execute (tables), executeUpdate → int, executeQuery → ResultSet; while (rs.next()). |
| Security | User input always goes through ? and setXxx, never into the SQL text. |
| Transactions | setAutoCommit(false), commit(), rollback(): all or nothing (ACID). |
| Resources | try-with-resources: rs, stmt, conn close in reverse order; SQLException is a checked exception. |
Summary
Common Mistakes
No suitable driver found for jdbc:sqlite:sample.dbunreported exception SQLException; must be caught or declared to be thrownno such table: usersVALUES(Ahmet, …):no such column: AhmetVALUES('O'Brien'):near "Brien": syntax errorINSERT with executeQuery:query does not return ResultSetrs.getString(0); indexes start at 1:column 0 out of bounds [1,3]Summary
Common Mistakes: Relations, Grouping, JDBC
= NULLWHERE dept_id = NULL gives no error but returns 0 rows. Use IS NULL.ONFROM students, depts: every row pairs with every row, 4 × 3 = 12 rows.name, and no table name was given:ambiguous column name: nameWHEREGroup conditions go in HAVING:misuse of aggregate: SUM()PRAGMA foreign_keys = ON, dept_id = 9 is silently accepted.wasNull() too lateIt checks the last column read; call it right after the relevant getInt.SELECT customer, amount … GROUP BY customer: SQLite returns the amount of some row in the group; many databases reject it.INSERTs with auto-commit onEach row is its own transaction: very slow. Use setAutoCommit(false), addBatch, commit.Summary
Practice Problems 1
Problem 1: Which rows does the SELECT return?
CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT); INSERT INTO users(name) VALUES ('Ali'); INSERT INTO users(name) VALUES ('Can'); DELETE FROM users WHERE id = 2; INSERT INTO users(name) VALUES ('Ece'); UPDATE users SET name = 'Efe' WHERE id = 1; SELECT * FROM users;
Problem 2
The people program (with people.db) is run a second time. What are the first block of output (up to the first ---) and the last block?
Show answers
(1, Efe)and(2, Ece). After Can was deleted the largest id was 1, so Ece got the largest id + 1 = 2 (the id was reused).UPDATEchanged only row 1.- The file persists; Ayşe (id 2) was left from the first run, and
IF NOT EXISTSdoesn't drop the table. The new records get ids 3 (Ahmet) and 4 (Ayşe). First block:2 Ayşe Demir 40,3 Ahmet Akdeniz 35,4 Ayşe Demir 40. Last block:2 Ayşe Demir 40,4 Ayşe Demir 40(the same person stored twice).
Summary
Practice Problems 2
Problem 3: A login check
String sql = "SELECT * FROM people WHERE name = '" + name
+ "' AND password = '" + password + "'";
ResultSet rs = stmt.executeQuery(sql);
boolean loggedIn = rs.next();The table has 3 people. If the user types ahmet as the name and ' OR '1'='1 as the password, what SQL results, how many rows come back, and does the login succeed?
Problem 4
If the setAutoCommit(false), commit() and rollback() lines were removed from transfer (each statement permanent on its own), what would Ali's and Ece's balances be at the end?
Show answers
SELECT * FROM people WHERE name = 'ahmet' AND password = '' OR '1'='1'.ANDbinds beforeOR:(… AND …) OR '1'='1'is true for every row → 3 rows,rs.next()istrue: a login without a password! The fix: aPreparedStatementwithWHERE name = ? AND password = ?→ 0 rows.- Ali: 700, Ece: 5800. In the second transfer the deposit became permanent at once, the withdrawal failed on the
CHECK, and there was no transaction to undo: 5000 TL appeared from nothing (and the program still prints “Rolled back”).
Summary
Exercises
- Change the delete program: if
executeUpdatereturns 0, printRecord not found. - Create a
students(id, name, midterm, final)table; insert Ada (60, 70), Bora (30, 50) and Cem (80, 40) in a loop withPreparedStatement. List those whose average (midterm 40%, final 60%) is 50 or more, highest first. - Write a menu program: 1-Add, 2-Update, 3-Delete, 4-List. Use
Scannerandswitch; if the id to update/delete doesn't exist, printUser not found. - Save a game's scores in a
scores(player, score)table and list the top 10.
Answers / hints
int n = stmt.executeUpdate(sql); if (n > 0) System.out.println("Data deleted!"); else System.out.println("Record not found.");SELECT name, midterm * 0.4 + final * 0.6 AS avg FROM students WHERE midterm * 0.4 + final * 0.6 >= 50 ORDER BY avg DESC· Output:Ada 66.0,Cem 56.0(Bora, 42.0, is dropped). Read it withgetDouble("avg").- After
nextInt(), clear the line break withnextLine(). ForUPDATE … WHERE id = ?andDELETE … WHERE id = ?, ifexecuteUpdatereturns 0 the user doesn't exist. SELECT player, score FROM scores ORDER BY score DESC LIMIT ?withps.setInt(1, 10). To insert:INSERT INTO scores(player, score) VALUES(?, ?).
Summary
References
- Oracle, The Java Tutorials: JDBC Database Access (Establishing a Connection; Processing SQL Statements with JDBC; Using Prepared Statements; Using Transactions).
- Oracle, Java SE 21 API Documentation: the
java.sqlpackage (DriverManager,Connection,PreparedStatement,ResultSet). - SQLite documentation, sqlite.org: SQL As Understood By SQLite; Datatypes In SQLite; Appropriate Uses For SQLite.
- Xerial, SQLite JDBC Driver, github.com/xerial/sqlite-jdbc.
- P. Deitel and H. Deitel, Java: How to Program, 11th ed., Pearson (Accessing Databases with JDBC).
- C. S. Horstmann, Core Java, Volume II: Advanced Features, 12th ed., Pearson (Database Programming).
- OWASP, SQL Injection Prevention Cheat Sheet.