JAVA — CHAPTER 20

Accessing Databases with JDBC and SQLite · Cheat Sheet

Relational DBs JDBC Connection PreparedStatement Stored Procedures Transactions

1 RELATIONAL DATABASES — THE BASICS

Table

Rows and columns, like a spreadsheet — e.g. a books table.

Primary key

A column (or set of columns) that uniquely identifies each row.

Foreign key

A column referencing another table's primary key — models relationships between tables.

2 SQLite & CONNECTING WITH JDBC

Load driver
Class.forName / auto

→

Get connection
DriverManager.getConnection

→

Create statement

→

Execute query

→

Process ResultSet

→

Close connection

try (Connection conn = DriverManager.getConnection( "jdbc:sqlite:books.db")) { Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM books"); while (rs.next()) { System.out.println(rs.getString("title")); } }

3 QUERYING WITH SQL — QUICK REFERENCE

SQL statement	Purpose
`SELECT * FROM books`	retrieve all rows/columns
`SELECT title FROM books WHERE year > 2020`	filter with WHERE
`INSERT INTO books VALUES (...)`	add a new row
`UPDATE books SET price = 20 WHERE id = 1`	modify existing rows
`DELETE FROM books WHERE id = 1`	remove rows

4 PreparedStatement — SAFE, PARAMETERIZED QUERIES

String sql = "INSERT INTO books (title, price) VALUES (?, ?)"; PreparedStatement ps = conn.prepareStatement(sql); ps.setString(1, "Java Basics"); ps.setDouble(2, 29.99); ps.executeUpdate();

Why use it over Statement?

Prevents SQL injection — user input is treated as data, not executable SQL
Better performance for repeated queries (pre-compiled)
Cleaner handling of typed parameters (dates, numbers, strings)

5 STORED PROCEDURES & TRANSACTION PROCESSING

Stored procedures

Precompiled SQL logic saved inside the database, callable from Java with CallableStatement — centralizes business logic and can improve performance.

Transactions

conn.setAutoCommit(false); try { // multiple related updates... conn.commit(); } catch (SQLException e) { conn.rollback(); // undo all if any step fails }

6 METHOD REFERENCE TABLE

Connection / Statement	Purpose
`DriverManager.getConnection(url)`	opens a connection to the database
`conn.createStatement()`	creates a basic SQL statement object
`conn.prepareStatement(sql)`	creates a parameterized statement
`stmt.executeQuery(sql)`	runs a SELECT, returns a ResultSet
`stmt.executeUpdate(sql)`	runs INSERT/UPDATE/DELETE, returns rows affected

ResultSet	Purpose
`next()`	advances to the next row; false when done
`getString(col)/getInt(col)/getDouble(col)`	reads a typed column value
`close()`	releases database resources