Java JDBC CRUD Operations
Java JDBC CRUD means using JDBC to Create, Read, Update and Delete rows in a database table. Almost every business application, from a library system to an online shop, is built on these four operations.
Each operation maps to one SQL command, and each is run safely with a PreparedStatement. In this lesson you will build each operation as a small, reusable method for a books table, check whether an update or delete actually found its row, page through results, and finish with a complete BookRepository class that keeps all database code in one place.
Java JDBC CRUD Operations and Their SQL
| Operation | SQL | JDBC method | Result to check |
|---|---|---|---|
| Create | INSERT | executeUpdate() | Generated key of the new row |
| Read | SELECT | executeQuery() | ResultSet with zero or more rows |
| Update | UPDATE | executeUpdate() | Number of rows changed (0 = not found) |
| Delete | DELETE | executeUpdate() | Number of rows removed (0 = not found) |
All examples use this table and a matching Java record:
books
-----------------------------------------------------------------
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY
title VARCHAR(100) NOT NULL
author VARCHAR(60) NOT NULL
price DECIMAL(8, 2)
stock INT DEFAULT 0
record Book(int id, String title, String author, BigDecimal price, int stock) { }
Note: The examples use the H2 in-memory database so they run anywhere. The SQL is standard and works on MySQL and PostgreSQL too; only the identity column syntax differs (
AUTO_INCREMENTin MySQL,GENERATED ... AS IDENTITYorSERIALin PostgreSQL).
Create: Insert a Row and Get Its ID
The create operation inserts one row and returns the id the database generated for it.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class CreateBook {
static void createTable(Connection connection) throws SQLException {
try (Statement statement = connection.createStatement()) {
statement.executeUpdate("""
CREATE TABLE books (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author VARCHAR(60) NOT NULL,
price DECIMAL(8, 2),
stock INT DEFAULT 0
)""");
}
}
static int insertBook(Connection connection, String title, String author,
BigDecimal price, int stock) throws SQLException {
String sql = "INSERT INTO books (title, author, price, stock) VALUES (?, ?, ?, ?)";
try (PreparedStatement insert = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
insert.setString(1, title);
insert.setString(2, author);
insert.setBigDecimal(3, price);
insert.setInt(4, stock);
insert.executeUpdate();
try (ResultSet keys = insert.getGeneratedKeys()) {
if (keys.next()) {
return keys.getInt(1);
}
throw new SQLException("Insert succeeded but no id was returned");
}
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:library", "sa", "")) {
createTable(connection);
int first = insertBook(connection, "Clean Code", "Robert C. Martin", new BigDecimal("550.00"), 4);
int second = insertBook(connection, "Effective Java", "Joshua Bloch", new BigDecimal("720.00"), 10);
int third = insertBook(connection, "Head First Java", "Kathy Sierra", new BigDecimal("640.00"), 0);
System.out.println("Created book #" + first + ": Clean Code");
System.out.println("Created book #" + second + ": Effective Java");
System.out.println("Created book #" + third + ": Head First Java");
}
}
}
Output:
Created book #1: Clean Code
Created book #2: Effective Java
Created book #3: Head First Java
How it works:
- The
idcolumn is left out of theINSERT, so the database fills it from the identity sequence. RETURN_GENERATED_KEYSandgetGeneratedKeys()return that new id, as explained in Java PreparedStatement.- The method returns the id so the caller can use it at once, for example to show "Book #3 saved" or to insert related rows.
Read: Find One Row or Many Rows
Reading comes in two shapes. Finding by primary key returns zero or one row, so Optional<Book> is a natural return type. Listing returns zero or more rows, so it returns a List<Book>.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class ReadBooks {
record Book(int id, String title, String author, BigDecimal price, int stock) { }
static Book mapRow(ResultSet row) throws SQLException {
return new Book(row.getInt("id"), row.getString("title"), row.getString("author"),
row.getBigDecimal("price"), row.getInt("stock"));
}
static Optional<Book> findById(Connection connection, int id) throws SQLException {
String sql = "SELECT id, title, author, price, stock FROM books WHERE id = ?";
try (PreparedStatement query = connection.prepareStatement(sql)) {
query.setInt(1, id);
try (ResultSet row = query.executeQuery()) {
return row.next() ? Optional.of(mapRow(row)) : Optional.empty();
}
}
}
static List<Book> findAll(Connection connection, boolean onlyInStock) throws SQLException {
String sql = "SELECT id, title, author, price, stock FROM books"
+ (onlyInStock ? " WHERE stock > 0" : "")
+ " ORDER BY title";
List<Book> books = new ArrayList<>();
try (PreparedStatement query = connection.prepareStatement(sql);
ResultSet rows = query.executeQuery()) {
while (rows.next()) {
books.add(mapRow(rows));
}
}
return books;
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:library", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE books (id INT PRIMARY KEY, title VARCHAR(100), author VARCHAR(60), price DECIMAL(8, 2), stock INT)");
setup.executeUpdate("""
INSERT INTO books VALUES
(1, 'Clean Code', 'Robert C. Martin', 550.00, 4),
(2, 'Effective Java', 'Joshua Bloch', 720.00, 10),
(3, 'Head First Java', 'Kathy Sierra', 640.00, 0)""");
System.out.println("findById(2): " + findById(connection, 2).orElse(null));
System.out.println("findById(99): " + findById(connection, 99).map(Book::title).orElse("not found"));
System.out.println("All books:");
for (Book book : findAll(connection, false)) {
System.out.println(" #" + book.id() + " " + book.title() + " by " + book.author() + " - " + book.price());
}
System.out.println("In stock:");
findAll(connection, true).forEach(book -> System.out.println(" " + book.title() + " (" + book.stock() + ")"));
}
}
}
Output:
findById(2): Book[id=2, title=Effective Java, author=Joshua Bloch, price=720.00, stock=10]
findById(99): not found
All books:
#1 Clean Code by Robert C. Martin - 550.00
#2 Effective Java by Joshua Bloch - 720.00
#3 Head First Java by Kathy Sierra - 640.00
In stock:
Clean Code (4)
Effective Java (10)
How it works:
mapRow()converts the current row into aBook, using the getters from Java ResultSet. Both read methods share it, so the column-to-field mapping lives in one place.findById()returnsOptional.empty()whennext()isfalse. The caller decides what "not found" means, here printing a message.- The optional
WHERE stock > 0is fixed text chosen by aboolean, not by user input, so adding it to the SQL is safe. - Always use
ORDER BYwhen the order matters. Without it, the database may return rows in any order.
Update: Change Existing Rows
executeUpdate() returns how many rows the UPDATE changed. A result of 0 means no row matched, which usually means the id does not exist.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class UpdateBooks {
static boolean updatePrice(Connection connection, int id, BigDecimal newPrice) throws SQLException {
try (PreparedStatement update = connection.prepareStatement("UPDATE books SET price = ? WHERE id = ?")) {
update.setBigDecimal(1, newPrice);
update.setInt(2, id);
return update.executeUpdate() == 1;
}
}
static boolean restock(Connection connection, int id, int quantity) throws SQLException {
// stock = stock + ? lets the database do the arithmetic on the current value
try (PreparedStatement update = connection.prepareStatement("UPDATE books SET stock = stock + ? WHERE id = ?")) {
update.setInt(1, quantity);
update.setInt(2, id);
return update.executeUpdate() == 1;
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:library", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE books (id INT PRIMARY KEY, title VARCHAR(100), author VARCHAR(60), price DECIMAL(8, 2), stock INT)");
setup.executeUpdate("""
INSERT INTO books VALUES
(1, 'Clean Code', 'Robert C. Martin', 550.00, 4),
(2, 'Effective Java', 'Joshua Bloch', 720.00, 10),
(3, 'Head First Java', 'Kathy Sierra', 640.00, 0)""");
System.out.println("Price updated for #2: " + updatePrice(connection, 2, new BigDecimal("680.00")));
System.out.println("Price updated for #99: " + updatePrice(connection, 99, new BigDecimal("100.00")));
System.out.println("Restocked #3: " + restock(connection, 3, 5));
try (ResultSet rows = setup.executeQuery("SELECT id, title, price, stock FROM books ORDER BY id")) {
while (rows.next()) {
System.out.println(" #" + rows.getInt("id") + " " + rows.getString("title") + " - "
+ rows.getBigDecimal("price") + " (stock " + rows.getInt("stock") + ")");
}
}
}
}
}
Output:
Price updated for #2: true
Price updated for #99: false
Restocked #3: true
#1 Clean Code - 550.00 (stock 4)
#2 Effective Java - 680.00 (stock 10)
#3 Head First Java - 640.00 (stock 5)
How it works:
- Both methods return
trueonly when exactly one row changed. Book #99 does not exist, so its update changes nothing and returnsfalseinstead of failing silently. stock = stock + ?updates the value inside the database. Reading the stock into Java, adding to it and writing it back would be slower and could lose updates if two users restock at the same moment.- The
WHERE id = ?clause is essential. AnUPDATEwithoutWHEREchanges every row in the table.
Delete: Remove Rows
Delete works the same way: executeUpdate() tells you how many rows were removed.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteBooks {
static boolean deleteById(Connection connection, int id) throws SQLException {
try (PreparedStatement delete = connection.prepareStatement("DELETE FROM books WHERE id = ?")) {
delete.setInt(1, id);
return delete.executeUpdate() > 0;
}
}
static int deleteOutOfStock(Connection connection) throws SQLException {
try (PreparedStatement delete = connection.prepareStatement("DELETE FROM books WHERE stock = 0")) {
return delete.executeUpdate();
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:library", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE books (id INT PRIMARY KEY, title VARCHAR(100), author VARCHAR(60), price DECIMAL(8, 2), stock INT)");
setup.executeUpdate("""
INSERT INTO books VALUES
(1, 'Clean Code', 'Robert C. Martin', 550.00, 4),
(2, 'Effective Java', 'Joshua Bloch', 720.00, 10),
(3, 'Head First Java', 'Kathy Sierra', 640.00, 0)""");
System.out.println("Deleted #1: " + deleteById(connection, 1));
System.out.println("Deleted #1 again: " + deleteById(connection, 1));
System.out.println("Out-of-stock books removed: " + deleteOutOfStock(connection));
try (ResultSet count = setup.executeQuery("SELECT COUNT(*) FROM books")) {
count.next();
System.out.println("Books left: " + count.getInt(1));
}
}
}
}
Output:
Deleted #1: true
Deleted #1 again: false
Out-of-stock books removed: 1
Books left: 1
How it works:
- The first delete removes book #1. The second finds nothing to remove and returns
false, which is not an error. deleteOutOfStock()removes every row that matches, here only "Head First Java", and returns that count.- If other tables reference a book through a foreign key, the database rejects the delete with an
SQLExceptionunless the key is declared withON DELETE CASCADE.
Note: Many real systems use a soft delete: they run
UPDATE books SET deleted = TRUE WHERE id = ?instead ofDELETE. The row stays for history and auditing, and every read query addsWHERE deleted = FALSE.
Reading Data Page by Page
Lists in web pages show a few rows at a time. The standard SQL clause OFFSET ... ROWS FETCH NEXT ... ROWS ONLY returns one page, and both numbers can be placeholders.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
public class PagedBooks {
static List<String> findPage(Connection connection, int pageNumber, int pageSize) throws SQLException {
String sql = "SELECT title FROM books ORDER BY id OFFSET ? ROWS FETCH NEXT ? ROWS ONLY";
List<String> titles = new ArrayList<>();
try (PreparedStatement query = connection.prepareStatement(sql)) {
query.setInt(1, (pageNumber - 1) * pageSize); // rows to skip
query.setInt(2, pageSize); // rows to return
try (ResultSet rows = query.executeQuery()) {
while (rows.next()) {
titles.add(rows.getString("title"));
}
}
}
return titles;
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:library", "sa", "");
Statement setup = connection.createStatement()) {
setup.executeUpdate("CREATE TABLE books (id INT PRIMARY KEY, title VARCHAR(100))");
setup.executeUpdate("""
INSERT INTO books VALUES
(1, 'Clean Code'), (2, 'Effective Java'), (3, 'Head First Java'),
(4, 'Java Concurrency in Practice'), (5, 'Refactoring'),
(6, 'Design Patterns'), (7, 'The Pragmatic Programmer')""");
int pageSize = 3;
int totalRows;
try (ResultSet count = setup.executeQuery("SELECT COUNT(*) FROM books")) {
count.next();
totalRows = count.getInt(1);
}
int totalPages = (totalRows + pageSize - 1) / pageSize; // round up
for (int page = 1; page <= totalPages; page++) {
System.out.println("Page " + page + " of " + totalPages + ": " + findPage(connection, page, pageSize));
}
}
}
}
Output:
Page 1 of 3: [Clean Code, Effective Java, Head First Java]
Page 2 of 3: [Java Concurrency in Practice, Refactoring, Design Patterns]
Page 3 of 3: [The Pragmatic Programmer]
How it works:
- Page
nskips(n - 1) * pageSizerows. Page 2 skips 3 rows and returns the next 3. (totalRows + pageSize - 1) / pageSizedivides and rounds up with integers:(7 + 2) / 3 = 3pages.ORDER BY idis required. Without a fixed order, the same row could appear on two pages or on none.
| Database | Paging syntax |
|---|---|
| H2, PostgreSQL, Oracle 12c+, SQL Server 2012+ | OFFSET ? ROWS FETCH NEXT ? ROWS ONLY |
| MySQL, MariaDB, PostgreSQL, SQLite | LIMIT ? OFFSET ? |
Real-World Example: A Complete BookRepository
In a real project, all SQL for one table lives in a single class, often called a repository or DAO (Data Access Object). The rest of the application calls clear methods such as create() or findById() and never sees SQL or ResultSet objects.
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class BookRepositoryDemo {
record Book(int id, String title, String author, BigDecimal price, int stock) { }
static class BookRepository {
private static final String COLUMNS = "id, title, author, price, stock";
private final Connection connection;
BookRepository(Connection connection) {
this.connection = connection;
}
void createTable() throws SQLException {
try (Statement statement = connection.createStatement()) {
statement.executeUpdate("""
CREATE TABLE IF NOT EXISTS books (
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
title VARCHAR(100) NOT NULL,
author VARCHAR(60) NOT NULL,
price DECIMAL(8, 2) NOT NULL,
stock INT NOT NULL DEFAULT 0
)""");
}
}
// CREATE
Book create(String title, String author, BigDecimal price, int stock) throws SQLException {
String sql = "INSERT INTO books (title, author, price, stock) VALUES (?, ?, ?, ?)";
try (PreparedStatement insert = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
insert.setString(1, title);
insert.setString(2, author);
insert.setBigDecimal(3, price);
insert.setInt(4, stock);
insert.executeUpdate();
try (ResultSet keys = insert.getGeneratedKeys()) {
keys.next();
return new Book(keys.getInt(1), title, author, price, stock);
}
}
}
// READ one
Optional<Book> findById(int id) throws SQLException {
try (PreparedStatement query = connection.prepareStatement("SELECT " + COLUMNS + " FROM books WHERE id = ?")) {
query.setInt(1, id);
try (ResultSet row = query.executeQuery()) {
return row.next() ? Optional.of(map(row)) : Optional.empty();
}
}
}
// READ many
List<Book> findAll() throws SQLException {
List<Book> books = new ArrayList<>();
try (PreparedStatement query = connection.prepareStatement("SELECT " + COLUMNS + " FROM books ORDER BY id");
ResultSet rows = query.executeQuery()) {
while (rows.next()) {
books.add(map(rows));
}
}
return books;
}
// UPDATE every column of an existing book
boolean update(Book book) throws SQLException {
String sql = "UPDATE books SET title = ?, author = ?, price = ?, stock = ? WHERE id = ?";
try (PreparedStatement update = connection.prepareStatement(sql)) {
update.setString(1, book.title());
update.setString(2, book.author());
update.setBigDecimal(3, book.price());
update.setInt(4, book.stock());
update.setInt(5, book.id());
return update.executeUpdate() == 1;
}
}
// UPDATE with a condition: sell only if enough copies are left
boolean sell(int id, int quantity) throws SQLException {
String sql = "UPDATE books SET stock = stock - ? WHERE id = ? AND stock >= ?";
try (PreparedStatement update = connection.prepareStatement(sql)) {
update.setInt(1, quantity);
update.setInt(2, id);
update.setInt(3, quantity);
return update.executeUpdate() == 1;
}
}
// DELETE
boolean delete(int id) throws SQLException {
try (PreparedStatement delete = connection.prepareStatement("DELETE FROM books WHERE id = ?")) {
delete.setInt(1, id);
return delete.executeUpdate() == 1;
}
}
private Book map(ResultSet row) throws SQLException {
return new Book(row.getInt("id"), row.getString("title"), row.getString("author"),
row.getBigDecimal("price"), row.getInt("stock"));
}
}
public static void main(String[] args) throws SQLException {
try (Connection connection = DriverManager.getConnection("jdbc:h2:mem:library", "sa", "")) {
BookRepository books = new BookRepository(connection);
books.createTable();
Book cleanCode = books.create("Clean Code", "Robert C. Martin", new BigDecimal("550.00"), 4);
Book effectiveJava = books.create("Effective Java", "Joshua Bloch", new BigDecimal("720.00"), 10);
System.out.println("Created: " + cleanCode);
System.out.println("Created: " + effectiveJava);
System.out.println("Sell 3 x Clean Code: " + books.sell(cleanCode.id(), 3));
System.out.println("Sell 5 x Clean Code: " + books.sell(cleanCode.id(), 5));
System.out.println("Clean Code stock now: " + books.findById(cleanCode.id()).orElseThrow().stock());
Book newEdition = new Book(effectiveJava.id(), "Effective Java (3rd Edition)", "Joshua Bloch",
new BigDecimal("699.00"), effectiveJava.stock());
books.update(newEdition);
System.out.println("Updated: " + books.findById(effectiveJava.id()).orElseThrow());
System.out.println("Deleted #" + cleanCode.id() + ": " + books.delete(cleanCode.id()));
System.out.println("Catalog:");
books.findAll().forEach(book -> System.out.println(" " + book));
}
}
}
Output:
Created: Book[id=1, title=Clean Code, author=Robert C. Martin, price=550.00, stock=4]
Created: Book[id=2, title=Effective Java, author=Joshua Bloch, price=720.00, stock=10]
Sell 3 x Clean Code: true
Sell 5 x Clean Code: false
Clean Code stock now: 1
Updated: Book[id=2, title=Effective Java (3rd Edition), author=Joshua Bloch, price=699.00, stock=10]
Deleted #1: true
Catalog:
Book[id=2, title=Effective Java (3rd Edition), author=Joshua Bloch, price=699.00, stock=10]
How it works:
- One class per table.
BookRepositoryholds every SQL statement forbooks. If a column is renamed, only this class changes. create()returns a fullBook. It combines the values it was given with the generated id, so the caller gets a complete object without a second query.sell()checks and updates in one statement. The conditionAND stock >= ?makes the database refuse to sell more copies than exist. The first sale (3 of 4) succeeds; the second (5 of the remaining 1) changes no rows and returnsfalse. Because the check and the change happen in one SQL command, two customers buying the last copy at the same time cannot both succeed.update()writes the whole record. It takes aBookwith new values and returnsfalseif the id no longer exists.findById()andfindAll()sharemap(). Every read buildsBookobjects the same way, and callers work only with records, lists andOptional.
Note: A repository that changes several tables in one business action, such as saving an order and reducing stock, must run those statements in one transaction so they succeed or fail together. That is the topic of the next lesson, Java JDBC Transactions.