CRUD Operations with JDBC
CRUD stands for the four basic operations every data application performs: Create (INSERT), Read (SELECT), Update (UPDATE) and Delete (DELETE). In this lesson, you will combine what the previous lessons covered, connections, PreparedStatement, ResultSet and generated keys, into a small, well-organized student application. All the SQL lives in one class, called a DAO (Data Access Object), and the rest of the program works with plain Java objects. This is the checkpoint for the first half of the chapter, and it is the shape that real data access code takes.
The Four Operations
| Operation | SQL | JDBC call | Returns |
|---|---|---|---|
| Create | INSERT INTO student (name, marks) VALUES (?, ?) | executeUpdate() + getGeneratedKeys() | The new row's id |
| Read | SELECT id, name, marks FROM student WHERE id = ? | executeQuery() | A ResultSet |
| Update | UPDATE student SET marks = ? WHERE id = ? | executeUpdate() | Rows changed |
| Delete | DELETE FROM student WHERE id = ? | executeUpdate() | Rows removed |
The DAO Pattern
A DAO is a class whose only job is to talk to one table. It hides the SQL behind methods that take and return ordinary objects:
| Without a DAO | With a DAO |
|---|---|
| SQL strings scattered through the program | All SQL for a table in one place |
Every caller handles ResultSet | Callers get Student objects |
| Changing a column means hunting through the code | One class to change |
Checkpoint: A Student Application
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 StudentApp {
record Student(int id, String name, int marks) { }
// All the SQL for the student table lives in this one class.
static class StudentDao {
private final Connection con;
StudentDao(Connection con) {
this.con = con;
}
Student create(String name, int marks) throws SQLException {
String sql = "INSERT INTO student (name, marks) VALUES (?, ?)";
try (PreparedStatement ps = con.prepareStatement(
sql, Statement.RETURN_GENERATED_KEYS)) {
ps.setString(1, name);
ps.setInt(2, marks);
ps.executeUpdate();
try (ResultSet keys = ps.getGeneratedKeys()) {
keys.next();
return new Student(keys.getInt(1), name, marks);
}
}
}
Optional<Student> findById(int id) throws SQLException {
String sql = "SELECT id, name, marks FROM student WHERE id = ?";
try (PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, id);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? Optional.of(map(rs)) : Optional.empty();
}
}
}
List<Student> findAll() throws SQLException {
List<Student> all = new ArrayList<>();
String sql = "SELECT id, name, marks FROM student ORDER BY id";
try (PreparedStatement ps = con.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
all.add(map(rs));
}
}
return all;
}
boolean updateMarks(int id, int marks) throws SQLException {
String sql = "UPDATE student SET marks = ? WHERE id = ?";
try (PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, marks);
ps.setInt(2, id);
return ps.executeUpdate() == 1;
}
}
boolean delete(int id) throws SQLException {
String sql = "DELETE FROM student WHERE id = ?";
try (PreparedStatement ps = con.prepareStatement(sql)) {
ps.setInt(1, id);
return ps.executeUpdate() == 1;
}
}
private Student map(ResultSet rs) throws SQLException {
return new Student(rs.getInt("id"), rs.getString("name"),
rs.getInt("marks"));
}
}
public static void main(String[] args) throws Exception {
try (Connection con =
DriverManager.getConnection("jdbc:h2:mem:school")) {
try (Statement st = con.createStatement()) {
st.execute("CREATE TABLE student ("
+ "id INT AUTO_INCREMENT PRIMARY KEY, "
+ "name VARCHAR(50) NOT NULL, marks INT NOT NULL)");
}
StudentDao dao = new StudentDao(con);
// Create
Student navika = dao.create("Navika", 91);
dao.create("Srihitha", 84);
Student advitha = dao.create("Advitha", 78);
System.out.println("Created: " + navika);
// Read
System.out.println("Find 2: " + dao.findById(2));
System.out.println("Find 99: " + dao.findById(99));
// Update
System.out.println("Updated? " + dao.updateMarks(advitha.id(), 88));
// Delete
System.out.println("Deleted? " + dao.delete(navika.id()));
System.out.println("Deleted again? " + dao.delete(navika.id()));
System.out.println("All students now:");
for (Student s : dao.findAll()) {
System.out.println(" " + s);
}
}
}
}Created: Student[id=1, name=Navika, marks=91]
Find 2: Optional[Student[id=2, name=Srihitha, marks=84]]
Find 99: Optional.empty
Updated? true
Deleted? true
Deleted again? false
All students now:
Student[id=2, name=Srihitha, marks=84]
Student[id=3, name=Advitha, marks=88]Explanation:
record Studentis the plain data object. Nothing in it knows about databases.createinserts a row and asks for the generated key, so it can return a completeStudentincluding the id the database chose.findByIdreturns anOptional<Student>. A missing row is a normal outcome, not an error, andOptionalmakes the caller deal with it; id 99 givesOptional.empty.findAllloops over the result set and builds a list. The data is copied into objects before the statement closes.updateMarksanddeletereturntrueonly if exactly one row was affected. Deleting the same student twice shows why: the second call affects 0 rows and returnsfalse.mapconverts the current row into aStudent. Having one such method keeps the column names in a single place.- Every method uses
PreparedStatementand try-with-resources.
Running It on MySQL or PostgreSQL
The DAO doesn't change at all. Only the connection URL and the CREATE TABLE statement differ:
| Database | URL | Auto-generated id column |
|---|---|---|
| MySQL | jdbc:mysql://localhost:3306/school | id INT AUTO_INCREMENT PRIMARY KEY |
| PostgreSQL | jdbc:postgresql://localhost:5432/school | id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY |
Where the Connection Comes From
This DAO receives its Connection through the constructor. That keeps it simple and lets the caller decide whether several DAO calls share one transaction (see "Transaction Management in JDBC"). In a server application, a DAO usually receives a DataSource instead and borrows a connection for each method.
Common Mistakes
Returning null for "not found". Callers forget to check. Return Optional.
Ignoring the row count of update and delete. You report success when nothing happened.
Passing ResultSet objects around. Convert rows to objects inside the DAO.
One huge method that does everything. Small methods, one per operation, are easier to test and reuse.
Interview Corner
How do you perform CRUD operations with JDBC?
Obtain a Connection, then use PreparedStatement for each operation: INSERT with executeUpdate() (and Statement.RETURN_GENERATED_KEYS plus getGeneratedKeys() to read a generated id), SELECT with executeQuery() and a loop over the ResultSet to map rows to objects, and UPDATE and DELETE with executeUpdate(), checking the returned row count. Resources are closed with try-with-resources. In well-structured code these operations live in a DAO or repository class per table.
What is the DAO pattern?
The Data Access Object pattern separates persistence code from business logic. A DAO class provides methods such as create, findById, findAll, update and delete for one entity, and hides the SQL and JDBC details behind them. Callers work with domain objects only, which makes the data access code easier to change, test and reuse.
Practice
- Easy: Add
findByMinMarks(int min)to the DAO, returning students with at least that many marks. - Harder: Add a text menu to
main(1 add, 2 list, 3 update, 4 delete, 0 exit) that reads commands with aScannerand calls the DAO.
Key Takeaways
Was this tutorial helpful?