Chapter 11: Database Programming

Object Oriented Programming with Java
Persistent storageThe relational modelSQLiteSQL commands JOIN and GROUP BYJDBCInsert, read, update, deletePreparedStatementTransactions
Sercan KÜLCÜ · Giresun University · Computer Engineering

Introduction

Learning Outcomes

Concepts

Explain why databases are needed, how they differ from files, and the relational model (tables, rows, columns, primary keys).

SQL

Write CREATE TABLE, INSERT, SELECT, UPDATE, DELETE; narrow queries with WHERE, ORDER BY, LIMIT; combine tables with JOIN and group with GROUP BY.

JDBC

Connect from Java to SQLite with Connection, Statement and ResultSet to insert, read, update and delete records; close resources with try-with-resources.

Security and integrity

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.
RAM (memory)studentgrades[]total = 87gone on exitDiskschool.dbpersistentsave: INSERT, UPDATEread back: SELECTWhen the program restarts, the data is read from disk.

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.

FeatureFlat file (txt, csv)Database
SearchThe file is read start to endFast with an index; a one-line SQL query
UpdateOften the whole file is rewrittenOnly the affected row changes
Concurrent accessTwo programs writing at once can corrupt dataSafe with locking and transactions
ConsistencyFormat checking is entirely up to the programmerData types and constraints (NOT NULL, UNIQUE, CHECK)
SecurityFile permissions: all or nothingRights per user, table and field (server DBMSs; file permissions in SQLite)
After a crashA half-done write can corrupt the fileA half-done transaction is rolled back
programgrades.csvsearch, locking, format: up to youprogramSQLDBMSfileindexes, constraints, transactions, rights: DBMS

Database Concepts

Key Features

Fast data access

Indexes find a record among millions of rows in no time.

Field-level security

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).

Portable, standard APIs

SQL is a standard; in Java, JDBC reaches different databases through one interface.

Independence from file layout

The program doesn't know how data is stored. It says what it wants in SQL; the DBMS decides how.

programWHAT: SQLDBMS (SQLite, MySQL)HOWindex / plandisk files

On top of these, databases offer integrity constraints and transaction support.

Database Concepts

The Relational Model: Tables, Rows, Columns

userstableidnameemail1Ahmet Yılmazahmet@example.com2Ayşe Demirayse@example.com3Can Kayacan@example.comcolumn (field)row(record)primary key: different in every row
  • 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

Relational

Tables and SQL. Oracle, SQL Server, MySQL, PostgreSQL. About 77% of usage.

NoSQL / search

Table-less models such as documents, key–value stores and search indexes, used in specific scenarios: MongoDB, Elasticsearch (about 23%).

Embedded relational

SQLite: a relational database that runs inside the program; common where little data is shared.

Relational77%NoSQL / search23%approximate usage share

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 (MySQL, Oracle)Java programprocess 1networkport 3306DB serverprocess 2data filesuser nameand password neededEmbedded (SQLite)one processJava programSQLite librarytest.dbone filereads/writes the file directly
Server-basedSQLite
SetupInstall a server, define users and passwordsAdd one jar to the project
DataMany files managed by the serverA single .db file
Concurrent writesMany usersOne writer at a time

SQLite

The SQLite Driver and DB Browser

SQLite JDBC driver

The jar that lets Java talk to SQLite (sqlite-jdbc-3.x.jar): github.com/xerial/sqlite-jdbc

DB Browser for SQLite

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

  1. Download the jar and put it in a lib folder in the project.
  2. Right-click the project → Build Path → Configure Build Path…
  3. 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

CommandPurposeExampleCRUD
CREATE TABLECreates a new tableCREATE TABLE users (id INTEGER, name TEXT)—
INSERT INTOAdds a new recordINSERT INTO users(name) VALUES('Ali')Create
SELECTQueries the tableSELECT * FROM usersRead
UPDATEUpdates existing recordsUPDATE users SET name = 'Efe' WHERE id = 1Update
DELETEDeletes recordsDELETE FROM users WHERE id = 1Delete
C: INSERTR: SELECTU: UPDATED: DELETEtable
  • 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”. WHERE picks 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 typeJava counterpart
INTEGERint, long · getInt
REALdouble · getDouble
TEXTString · getString
BLOBbyte[] · getBytes
NULLno value

SQL Basics

SQL Commands, Step by Step

CREATE TABLE users (id INTEGER PRIMARY KEY,
                    name TEXT, email TEXT)
usersidnameemail(table empty)

The table is created: three columns, no rows yet.

INSERT INTO users(name, email)
  VALUES('Ahmet Yılmaz', 'ahmet@example.com')
usersidnameemail1Ahmet Yılmazahmet@example.com

No id was given; SQLite assigned 1. 1 row affected.

INSERT INTO users(name, email)
  VALUES('Ayşe Demir', 'ayse@example.com')
usersidnameemail1Ahmet Yılmazahmet@example.com2Ayşe Demirayse@example.com

The second record got id = 2 (the largest id + 1).

UPDATE users SET name = 'Mehmet Akdeniz'
  WHERE id = 1
usersidnameemail1Mehmet Akdenizahmet@example.com2Ayşe Demirayse@example.com

WHERE id = 1 picked only the first row; only name changed, email stayed the same.

DELETE FROM users WHERE id = 1
usersidnameemail1Mehmet Akdenizahmet@example.com2Ayşe Demirayse@example.com

The row with id = 1 is deleted. 1 row affected.

SELECT * FROM users
usersidnameemail2Ayşe Demirayse@example.com

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

scoresplayerscoreada72can95ece58efe95naz81
QueryResult
SELECT player, score FROM scores
WHERE score < 90 ORDER BY score DESC
naz 81, ada 72, ece 58
SELECT player, score FROM scores
ORDER BY score DESC, player LIMIT 3
can 95, efe 95, naz 81
SELECT player, score FROM scores
WHERE player LIKE 'e%'
ece 58, efe 95
SELECT COUNT(*), AVG(score), MAX(score)
FROM scores WHERE score >= 60
4 · 85.75 · 95
  • WHERE filters rows (=, <>, <, >=, AND, OR, LIKE 'e%': “starts with e”).
  • ORDER BY … DESC sorts from largest to smallest; ties use the second key (player). LIMIT 3 takes 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"));
  }
}
FROMWHEREORDER BYLIMITplayerscoreada72can95ece58efe95naz81

The query is a single string. The scores table has 5 rows.

FROMWHEREORDER BYLIMITplayerscoreada72can95ece58efe95naz815 rows

executeQuery sends the query. The database starts with FROM: every row of the table.

FROMWHEREORDER BYLIMITplayerscoreada72can95ece58efe95naz81ece 58 dropped

WHERE score > 70: ece fails the condition and is dropped; 4 rows remain.

FROMWHEREORDER BYLIMITplayerscorenaz81efe95can95ada72name, Z → A

ORDER BY player DESC: names are sorted in reverse alphabetical order (not by score).

FROMWHEREORDER BYLIMITplayerscorenaz81efe95can95ada72first 2 rows

LIMIT 2: the first two rows of the sorted list form the result set.

FROMWHEREORDER BYLIMITplayerscorenaz81efe95cursorresult set

The cursor starts before the first row; next() moves it to row 1.

FROMWHEREORDER BYLIMITplayerscorenaz81efe95cursorresult set

The columns are read by name and printed.

FROMWHEREORDER BYLIMITplayerscorenaz81efe95cursorresult set

The cursor moves to row 2.

FROMWHEREORDER BYLIMITplayerscorenaz81efe95cursorresult set

efe 95 is printed.

FROMWHEREORDER BYLIMITplayerscorenaz81efe95cursorresult set

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

one table: order_logcustomerphoneproductAli555-11penAli555-11notebookEce555-22erasersame phonewritten twicesplit into two tablescustomersidnamephone1Ali555-112Ece555-22ordersidcustomer_idproduct11pen21notebook32erasercustomer_id → customers.id
  • 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));
deptsidname1CS2EE3MEstudentsidnamedept_id1Ada12Bora23Cem14DenizNULLone-to-many: one dept,many studentsDeniz has no dept: dept_id NULL
  • 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 = 9 is silently accepted.
  • A many-to-many relationship (student–course) needs a third, linking table: enrollments(student_id, course_id).
  • Java analogy: a Dept reference inside a Student object.

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
studentJOIN (INNER)LEFT JOIN
AdaCSCS
BoraEEEE
CemCSCS
Denizno rowNULL
  • A JOIN matches the rows of two tables by the ON condition and produces one result table.
  • s and d are table aliases. Both tables have a name column, so we write s.name and d.name; a bare name gives ambiguous column name: name.
  • AS renames a result column: Java reads it with rs.getString("dept").
  • INNER JOIN (plain JOIN): only rows that have a match. Deniz, whose dept is NULL, is missing from the result.
  • LEFT JOIN: every row of the left table; with no match the right side is NULL, null in 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"));
  }
}
students snamedept_idAda1Bora2Cem1DenizNULLdepts didname1CS2EE3MEresult: no rows yet

The query refers to the two tables by aliases: s = students, d = depts.

students snamedept_idAda1Bora2Cem1DenizNULLdepts didname1CS2EE3MEresultstudentdeptAdaCS1 = 1 → match

executeQuery: Ada's dept_id is 1; id = 1 is found in depts → (Ada, CS).

students snamedept_idAda1Bora2Cem1DenizNULLdepts didname1CS2EE3MEresultstudentdeptAdaCSBoraEE2 = 2 → match

Bora → 2 → EE.

students snamedept_idAda1Bora2Cem1DenizNULLdepts didname1CS2EE3MEresultstudentdeptAdaCSBoraEECemCS1 = 1 → match

Cem → 1: the same dept row is used a second time.

students snamedept_idAda1Bora2Cem1DenizNULLdepts didname1CS2EE3MEresultstudentdeptAdaCSBoraEECemCSNULL = ? → no match

Deniz's dept_id is NULL: it equals no id, so the row doesn't make it into the result.

students snamedept_idAda1Bora2Cem1DenizNULLdepts didname1CS2EE3MEresultstudentdeptAdaCSBoraEECemCScursor at row 1

next() moves to the first row; the columns are read by the names given with AS.

students snamedept_idAda1Bora2Cem1DenizNULLdepts didname1CS2EE3MEresultstudentdeptAdaCSBoraEECemCScursor at row 2

The second row is printed.

students snamedept_idAda1Bora2Cem1DenizNULLdepts didname1CS2EE3MEresultstudentdeptAdaCSBoraEECemCScursor at row 3

The third row is printed.

students snamedept_idAda1Bora2Cem1DenizNULLdepts didname1CS2EE3MEresultstudentdeptAdaCSBoraEECemCSnext() → false

No more rows; the loop ends. With LEFT JOIN, a fourth row Deniz - null would appear.

Relations and Grouping

Grouping: GROUP BY and HAVING

ordersidcustomeramount1Ali1202Ece803Ali504Can2005Ece406Ali30GROUP BY customercustomernumtotalAli3200Can1200Ece2120one row per group
  • GROUP BY collects rows with the same value into a group; aggregate functions (COUNT, SUM, AVG, MIN, MAX) are computed per group.
  • WHERE filters rows before grouping, HAVING filters groups after it. WHERE SUM(amount) >= 150 is 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 customerAli (3, 200) · Can (1, 200) · Ece (2, 120)
WHERE amount >= 50 GROUP BY customerAli (2, 170) · Can (1, 200) · Ece (1, 80)
GROUP BY customer HAVING SUM(amount) >= 150Ali (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");
  }
}
ordersidcustomeramount1Ali1202Ece803Ali504Can2005Ece406Ali30

The orders table has 6 rows; the query is a single string.

ordersidcustomeramount1Ali1202Ece803Ali504Can2005Ece406Ali303 groups: Ali, Can, Ece

FROM and GROUP BY customer: the rows are split into three groups by customer name.

ordersidcustomeramount1Ali1202Ece803Ali504Can2005Ece406Ali30groupscustomernumtotalAli3200Can1200Ece2120

For each group, COUNT(*) and SUM(amount): Ali 120 + 50 + 30 = 200, Can 200, Ece 80 + 40 = 120.

ordersidcustomeramount1Ali1202Ece803Ali504Can2005Ece406Ali30groupscustomernumtotalAli3200Can1200Ece2120HAVING: 120 < 150

HAVING SUM(amount) >= 150: Ece's group is dropped (120).

ordersidcustomeramount1Ali1202Ece803Ali504Can2005Ece406Ali30groupscustomernumtotalAli3200Can1200Ece2120HAVING: 120 < 150result (sorted)customernumtotalAli3200Can1200tie → name: Ali, Can

ORDER BY total DESC, customer: Ali and Can tie at 200; the second key is the name, so Ali comes first.

ordersidcustomeramount1Ali1202Ece803Ali504Can2005Ece406Ali30groupscustomernumtotalAli3200Can1200Ece2120HAVING: 120 < 150result (sorted)customernumtotalAli3200Can1200

next() moves to the first row; the aliases (num, total) are read as column names.

ordersidcustomeramount1Ali1202Ece803Ali504Can2005Ece406Ali30groupscustomernumtotalAli3200Can1200Ece2120HAVING: 120 < 150result (sorted)customernumtotalAli3200Can1200

The second row is printed.

ordersidcustomeramount1Ali1202Ece803Ali504Can2005Ece406Ali30groupscustomernumtotalAli3200Can1200Ece2120HAVING: 120 < 150result (sorted)customernumtotalAli3200Can1200next() → false

The loop ends. The result has one row per group; individual orders don't appear.

Relations and Grouping

NULL: The Absence of a Value

  • NULL means “unknown / missing”; it is not 0 or empty text ('').
  • Any comparison with NULL gives an unknown result: WHERE dept_id = NULL selects no rows. Use IS NULL or IS NOT NULL.
  • WHERE dept_id <> 1 doesn't select Deniz either: we don't even know that NULL differs from 1.
  • NULL + 1 is NULL. COALESCE(dept_id, 0) puts 0 in place of NULL.
  • COUNT(*) counts rows, COUNT(dept_id) non-NULL values: 4 and 3. With no matching rows, AVG and SUM return NULL.
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)" : ""));
  }
}
Output
Cem 1
Deniz 0 (NULL)
  • getInt returns 0 for NULL; to tell 0 from NULL, call wasNull() right after.
  • wasNull() looks at the last column read: called after getString("name"), it would ask about the name and (NULL) would never print.
  • getString and getObject return null for NULL.

Relations and Grouping

Indexes: Faster Searches

no index: SCAN (every row checked)DenizAdaCemBora4 comparisonsidx_stu_name: sorted + row pointerAda→ row 1Bora→ row 2Cem→ row 3Deniz→ row 4straight to Cem
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 UNIQUE columns get an index automatically.
  • The cost: every INSERT, UPDATE, DELETE updates the index too, and the index takes disk space. Put indexes on columns searched often and used in WHERE, JOIN and ORDER BY.
  • EXPLAIN QUERY PLAN (SQLite) shows how a query will be executed.
CaseEXPLAIN QUERY PLAN output
No indexSCAN students
With the indexSEARCH students USING INDEX idx_stu_name (name=?)
WHERE id = 3SEARCH … 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.sql and 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: Connection is an interface; in SQLite the real object's class is org.sqlite.jdbc4.JDBC4Connection.
Your Java programJDBC API (java.sql)Connection · Statement · ResultSetDriverManagerSQLite driversqlite-jdbc.jarMySQL drivermysql-connector-j.jarsample.dbMySQL server

JDBC

The Core JDBC Types

TypePurposeKey methods
DriverManagerFinds the right driver and opens a connectiongetConnection(url), getConnection(url, user, password)
ConnectionAn open session with the databasecreateStatement(), prepareStatement(sql), commit(), rollback(), close()
StatementRuns a fixed SQL stringexecute, executeUpdate, executeQuery
PreparedStatementPrecompiled SQL with ? placeholderssetInt, setString, executeUpdate(), executeQuery()
ResultSetThe rows of a query result, plus a cursornext(), getInt("id"), getString("name")
SQLExceptionA database error; a checked exceptiongetMessage(), 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 URLMeaning
jdbc:sqlite:sample.dbsample.db in the working directory; created empty if missing
jdbc:sqlite:C:/data/school.dbA file by its full path
jdbc:sqlite::memory:A temporary in-memory database, gone when the connection closes (for experiments)
jdbc:mysql://localhost:3306/testdbA MySQL server: host, port, database name; plus a user name and password
jdbcprotocol:sqlitesubprotocol → driver:sample.dbdatabase (file)
  • Format: jdbc:<subprotocol>:<database file or address>. DriverManager picks 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

1. ConnectgetConnection()2. StatementcreateStatement()3. ExecuteexecuteQuery()4. Process resultrs.next()5. Closeclose()
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, then stmt, finally conn.
  • 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());
}
Output
Connected!
  • getConnection creates the file if it doesn't exist (an empty database).
  • If it can't connect, it doesn't return null; it throws SQLException. if (conn != null) is just an extra safeguard.
  • SQLException is a checked exception: catch it or declare it with throws.

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());
}
Output
Table created!
  • execute(sql) runs any SQL; it returns true if it produced a result set, false for CREATE.
  • 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());
}
Output
Data inserted! (1 row)
  • executeUpdate returns the number of rows affected (an int).
  • No id was given; SQLite assigned 1.
  • 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());
}
Output
ID: 1, Name: Ahmet Yılmaz, Email: ahmet@example.com
  • executeQuery returns a ResultSet.
  • rs.next() moves the cursor to the next row; it returns true if there is one, false otherwise.
  • getInt("id"), getString("name"): read the current row's columns by name.

Basic Operations

Reading with a ResultSet, Step by Step

ResultSet rsidname1Ahmet Yılmaz2Ayşe Demircursor: before the first rowrs

The connection opens and the query runs. rs holds the two-row result; the cursor waits before the first row.

ResultSet rsidname1Ahmet Yılmaz2Ayşe Demircursor: row 1rs

rs.next() moves the cursor to row 1 and returns true: we enter the loop.

ResultSet rsidname1Ahmet Yılmaz2Ayşe Demircursor: row 1rs

The columns are read from the current row.

ResultSet rsidname1Ahmet Yılmaz2Ayşe Demircursor: row 1rs

The row is printed.

ResultSet rsidname1Ahmet Yılmaz2Ayşe Demircursor: row 2rs

The cursor moves to row 2; true again.

ResultSet rsidname1Ahmet Yılmaz2Ayşe Demircursor: row 2rs

This time row 2's values are read.

ResultSet rsidname1Ahmet Yılmaz2Ayşe Demircursor: row 2rs

The second row is printed.

ResultSet rsidname1Ahmet Yılmaz2Ayşe Demircursor: after the last rowrs

No more rows: rs.next() returns false and the loop ends.

ResultSet rsidname1Ahmet Yılmaz2Ayşe Demircursor: after the last rowrs

The line after the loop runs.

ResultSet rsidname1Ahmet Yılmaz2Ayşe Demircursor: after the last rowrs

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());
}
Output
Data updated! (1 row)
  • Read before: ID: 1, Name: Ahmet Yılmaz, …; after: ID: 1, Name: Mehmet Akdeniz, …
  • SET changes only the listed column; email stays the same.
  • WHERE id = 1 picks 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());
}
Output (running the program twice)
Data deleted! (1 row)
Data deleted! (0 row)
  • On the second run there is no row left to delete: no error, executeUpdate just returns 0.
  • So it is better to word the message by the returned count (Exercise 1).
  • DELETE FROM users without WHERE deletes every row; the table itself remains.

Basic Operations

execute, executeUpdate, executeQuery

MethodReturnsUse
executebooleanCREATE TABLE, DROP
executeUpdateint row countINSERT, UPDATE, DELETE
executeQueryResultSetSELECT

The wrong method fails: running an INSERT with executeQuery → query does not return ResultSet.

Running the programs in order
> 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");
suppliesidnamestocktable empty

The insert statement is prepared once; in the loop only the values change.

suppliesidnamestockplaceholders1st ? = "stapler"2nd ? = 40

On the first pass the placeholders are filled.

suppliesidnamestock1stapler401 row inserted

executeUpdate returns 1; SQLite assigns id = 1.

suppliesidnamestock1stapler402scissors0placeholders1st ? = "scissors"2nd ? = 01 row inserted

The same prepared statement runs again with new values.

suppliesidnamestock1stapler402scissors03tape15placeholders1st ? = "tape"2nd ? = 151 row inserted

The third item is inserted; when i = 3 the loop ends.

suppliesidnamestock1stapler402scissors103tape25WHERE stock < 20: 2 rows

WHERE stock < 20 picks scissors (0) and tape (15); 10 is added to both. The return value is the number of rows affected: 2.

suppliesidnamestock1stapler402scissors103tape25no match: 0

No item has more than 100 in stock: no error, just 0.

suppliesidnamestock1stapler402scissors103tape25

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();
Output (3 people in the table; rows counted)
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");
SQL sent to the database:(none yet)idname1Ali2Ayşe3Can

Instead of a name, the user typed text with a quote and an OR. The table has 3 people.

SQL sent to the database:SELECT name FROM peopleWHERE name = 'x' OR '1'='1'idname1Ali2Ayşe3Can

After concatenation the user's quote closed the name value, and the OR '1'='1' after it is now part of the SQL.

SQL sent to the database:SELECT name FROM peopleWHERE name = 'x' OR '1'='1'idname1Ali2Ayşe3Cancondition'Ali'='x' → false OR '1'='1' → TRUE

The database evaluates the condition on each row: for Ali, the right side of OR is always true → the row is selected.

SQL sent to the database:SELECT name FROM peopleWHERE name = 'x' OR '1'='1'idname1Ali2Ayşe3Cancondition'Ali'='x' → false OR '1'='1' → TRUE'Ayşe'='x' → false OR true → TRUE'Can'='x' → false OR true → TRUE

Ayşe and Can are selected too: the condition filters out nothing.

SQL sent to the database:SELECT name FROM peopleWHERE name = 'x' OR '1'='1'idname1Ali2Ayşe3Cancondition'Ali'='x' → false OR '1'='1' → TRUE'Ayşe'='x' → false OR true → TRUE'Can'='x' → false OR true → TRUE3 rows returned

The loop runs 3 times. On a login screen this means logging in without a password.

SQL sent to the database:SELECT name FROM peopleWHERE name = 'x' OR '1'='1'idname1Ali2Ayşe3Cancondition'Ali'='x' → false OR '1'='1' → TRUE'Ayşe'='x' → false OR true → TRUE'Can'='x' → false OR true → TRUE3 rows returned

The result is printed.

SQL sent to the database:SELECT name FROM peopleWHERE name = ?idname1Ali2Ayşe3Can

This time the SQL is compiled first; ? is just a slot for a value. The statement's structure can no longer change.

SQL sent to the database:SELECT name FROM peopleWHERE name = [x' OR '1'='1]idname1Ali2Ayşe3Can

The whole input is bound as one text value; the quotes in it are ordinary characters.

SQL sent to the database:SELECT name FROM peopleWHERE name = [x' OR '1'='1]idname1Ali2Ayşe3Canconditionname = "x' OR '1'='1" → falsename = "x' OR '1'='1" → falsename = "x' OR '1'='1" → false0 rows

No one's name equals this 12-character text: no rows.

SQL sent to the database:SELECT name FROM peopleWHERE name = [x' OR '1'='1]idname1Ali2Ayşe3Canconditionname = "x' OR '1'='1" → falsename = "x' OR '1'='1" → falsename = "x' OR '1'='1" → false0 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.
INSERT INTO people(name, surname, age) VALUES( ?, ?, ? )?placeholder 1?placeholder 2?placeholder 3setString(1, name)"Ahmet"setString(2, surname)"Yılmaz"setInt(3, age)30

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 Connection as a parameter: the connection is opened once and used for many jobs.
  • The ?s are numbered by their order in the SQL: in update, the name is placeholder 3.
  • throws SQLException: errors pass to the caller; since main also declares throws, 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("---");
  }
}
Output (first call to list)
1 Ahmet Akdeniz 35
2 Ayşe Demir 40
---
  • The query has no variable values, so a plain Statement is enough.
  • Each call to list reads the table as it is now and ends with ---.

PreparedStatement

The People Program, Step by Step

peopleidnamesurnameage(table empty)

people.db is created if missing; the connection and stmt are opened.

peopleidnamesurnameage(table empty)

The table is created; it is empty for now.

peopleidnamesurnameage1AhmetYılmaz30

add: the ?s are filled and the row is inserted, id = 1.

peopleidnamesurnameage1AhmetYılmaz302AyşeDemir40

The second person is added with id = 2.

peopleidnamesurnameage1AhmetAkdeniz352AyşeDemir40

WHERE name = ?: Ahmet's surname and age change.

peopleidnamesurnameage1AhmetAkdeniz352AyşeDemir40

list prints every row and ---.

peopleidnamesurnameage1AhmetAkdeniz352AyşeDemir40

Ahmet's row is deleted.

peopleidnamesurnameage2AyşeDemir40

Only Ayşe is left in the table.

peopleidnamesurnameage2AyşeDemir40

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));
    }
  }
}
Output
New id: 5
  • The database decides a new row's id; the program learns it with getGeneratedKeys(). Example: to add a new order's line items, you need the order's id.
  • Pass Statement.RETURN_GENERATED_KEYS to prepareStatement; the single column of the returned ResultSet is 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 is AUTO_INCREMENT.
1, 2, 3 inserted; 3 deleted; new row →id
id INTEGER PRIMARY KEY3 (largest id + 1)
id INTEGER PRIMARY KEY AUTOINCREMENT4 (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 methodSQLReturns
add(name, dept)INSERTthe new id
find(id)SELECT … WHERE id = ?a Student or null
inDept(dept)SELECT … WHERE dept_id = ?List<Student>
delete(id)DELETEdeleted? (boolean)
main, UIworks with List<Student>StudentDaoSQL only hereJDBC + driverConnection, PreparedStatementschool.dbtablesrequest ↓↑ objects
  • 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);
list (List<Student>)empty: []

main calls the method with dept = 1. The connection is open in main.

list (List<Student>)empty: []

An empty list is created; the query is prepared with ?.

rs (dept 1)idname1Ada3Cemlist (List<Student>)empty: []cursor before the first row

? = 1 is bound and the query runs: dept 1 has Ada and Cem.

rs (dept 1)idname1Ada3Cemcursorlist (List<Student>)Student #1 Ada

First row: id = 1, name = Ada are read; new Student(1, "Ada") is added to the list.

rs (dept 1)idname1Ada3Cemcursorlist (List<Student>)Student #1 AdaStudent #3 Cem

The second row gives a second object: Student #3 Cem.

rs (dept 1)idname1Ada3Cemlist (List<Student>)Student #1 AdaStudent #3 Cemrs and ps closed

next() is false: the try blocks end and rs and ps close. The connection is closed by main, which opened it.

list (List<Student>)Student #1 AdaStudent #3 Cemreturns objects, not rows

The method returns a List<Student> (2 items); its frame leaves the stack.

list (List<Student>)Student #1 AdaStudent #3 Cem

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 testdb database created.
  • The driver class is com.mysql.cj.jdbc.Driver; the old name com.mysql.jdbc.Driver is 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) or conn.rollback() (undo).
Atomicity

The transaction is applied entirely or not at all.

Consistency

After the transaction, all constraints hold (e.g. balance ≥ 0).

Isolation

Concurrent transactions don't see each other's half-done state.

Durability

Committed data survives even a power cut.

setAutoCommit(false)UPDATE (+300)UPDATE (−300)commit(): permanentrollback(): none

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);
  }
}
Output
300 TL transferred.
Rolled back: 5000 TL
Ali: 700
Ece: 800
  • CHECK (balance >= 0): a statement that drives a balance negative throws SQLException.
  • In the second transfer, 5000 TL had already been deposited to Ece; rollback undid that too.
  • printAll is a SELECT loop, like list.

Transactions

commit and rollback, Step by Step

transaction startedidownerbalance1Ali10002Ece500

Auto-commit is turned off: from now on, changes are tentative until commit.

tentative (no commit)idownerbalance1Ali10002Ece800

300 TL is deposited to Ece: 500 → 800.

tentative (no commit)idownerbalance1Ali7002Ece800

300 TL is withdrawn from Ali: 1000 → 700. The constraint holds.

permanent (committed)idownerbalance1Ali7002Ece800

commit(): both changes become permanent together.

permanent stateidownerbalance1Ali7002Ece800

finally: auto-commit is turned back on; the method ends.

tentative (no commit)idownerbalance1Ali7002Ece5800

Second call: 5000 TL is deposited to Ece (tentatively): 800 → 5800.

tentative (no commit)idownerbalance1Ali7002Ece5800

700 − 5000 would be < 0: the CHECK fails and SQLException is thrown.

rollback: undoneidownerbalance1Ali7002Ece800

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);
}
Output
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 an int[].
  • With auto-commit on, every INSERT is its own transaction and is written to disk separately. In one test with a .db file 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 executeBatch every 500 addBatches) so memory doesn't balloon.

Summary

Summary

TopicEssence
DatabasePersistent, organized, queryable data managed by a DBMS. Tables, rows, columns, primary keys.
SQLiteServerless, embedded, one file. URL: jdbc:sqlite:sample.db; the driver jar must be on the classpath.
SQLCREATE TABLE, INSERT, SELECT, UPDATE, DELETE; WHERE, ORDER BY, LIMIT.
RelationsForeign keys; JOIN … ON; GROUP BY / HAVING; IS NULL for NULL.
JDBCDriverManager → Connection → Statement / PreparedStatement → ResultSet.
Executionexecute (tables), executeUpdate → int, executeQuery → ResultSet; while (rs.next()).
SecurityUser input always goes through ? and setXxx, never into the SQL text.
TransactionssetAutoCommit(false), commit(), rollback(): all or nothing (ACID).
Resourcestry-with-resources: rs, stmt, conn close in reverse order; SQLException is a checked exception.

Summary

Common Mistakes

Driver not on the classpathThe jar wasn't added:No suitable driver found for jdbc:sqlite:sample.db
Not catching SQLExceptionNo try or throws (compile error):unreported exception SQLException; must be caught or declared to be thrown
Missing or misnamed tableQuerying before creating the table:no such table: users
Text without quotesVALUES(Ahmet, …):no such column: Ahmet
Concatenating text with an apostropheVALUES('O'Brien'):near "Brien": syntax error
The wrong execute methodINSERT with executeQuery:query does not return ResultSet
Starting the index at 0rs.getString(0); indexes start at 1:column 0 out of bounds [1,3]
UPDATE / DELETE without WHERENo error; it changes or deletes every row.

Summary

Common Mistakes: Relations, Grouping, JDBC

Comparing with = NULLWHERE dept_id = NULL gives no error but returns 0 rows. Use IS NULL.
Joining without ONFROM students, depts: every row pairs with every row, 4 × 3 = 12 rows.
Ambiguous column nameBoth tables have name, and no table name was given:ambiguous column name: name
An aggregate in WHEREGroup conditions go in HAVING:misuse of aggregate: SUM()
Not enabling foreign key checksIn SQLite, without PRAGMA foreign_keys = ON, dept_id = 9 is silently accepted.
Calling wasNull() too lateIt checks the last column read; call it right after the relevant getInt.
Selecting an ungrouped columnSELECT customer, amount … GROUP BY customer: SQLite returns the amount of some row in the group; many databases reject it.
Thousands of 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. (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). UPDATE changed only row 1.
  2. The file persists; Ayşe (id 2) was left from the first run, and IF NOT EXISTS doesn'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
  1. SELECT * FROM people WHERE name = 'ahmet' AND password = '' OR '1'='1'. AND binds before OR: (… AND …) OR '1'='1' is true for every row → 3 rows, rs.next() is true: a login without a password! The fix: a PreparedStatement with WHERE name = ? AND password = ? → 0 rows.
  2. 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

  1. Change the delete program: if executeUpdate returns 0, print Record not found.
  2. Create a students(id, name, midterm, final) table; insert Ada (60, 70), Bora (30, 50) and Cem (80, 40) in a loop with PreparedStatement. List those whose average (midterm 40%, final 60%) is 50 or more, highest first.
  3. Write a menu program: 1-Add, 2-Update, 3-Delete, 4-List. Use Scanner and switch; if the id to update/delete doesn't exist, print User not found.
  4. Save a game's scores in a scores(player, score) table and list the top 10.
Answers / hints
  1. int n = stmt.executeUpdate(sql); if (n > 0) System.out.println("Data deleted!"); else System.out.println("Record not found.");
  2. 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 with getDouble("avg").
  3. After nextInt(), clear the line break with nextLine(). For UPDATE … WHERE id = ? and DELETE … WHERE id = ?, if executeUpdate returns 0 the user doesn't exist.
  4. SELECT player, score FROM scores ORDER BY score DESC LIMIT ? with ps.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.sql package (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.

The End

Chapter 11: Database Programming
Previous: Chapter 10: Threads · End of course
1 / 1 Sercan KÜLCÜ, All rights reserved.