JDBC is Java's standard database API; each database provides a driver (for PostgreSQL, org.postgresql:postgresql). Open a Connection, prepare a statement, execute it, and read the ResultSet — all inside try-with-resources so everything is closed.
Always use PreparedStatement with ? placeholders. Values are sent separately from the SQL, which prevents SQL injection and lets the database reuse the query plan. Never concatenate user input into SQL.
Turn off auto-commit to group statements into a transaction, then commit() or rollback(). In servers, use a connection pool such as HikariCP (Spring Boot includes it). See the PostgreSQL track on this site for the SQL itself.
curl -O https://repo1.maven.org/maven2/org/postgresql/postgresql/42.7.13/postgresql-42.7.13.jar
export DATABASE_URL="jdbc:postgresql://localhost:5432/school?user=postgres&password=postgres"
java -cp postgresql-42.7.13.jar Jdbc.javaimport java.sql.*;
import java.util.ArrayList;
import java.util.List;
public class Jdbc {
record Result(String name, String subject, int score) {}
public static void main(String[] args) throws SQLException {
String url = System.getenv().getOrDefault("DATABASE_URL",
"jdbc:postgresql://localhost:5432/school?user=postgres&password=postgres");
try (Connection conn = DriverManager.getConnection(url)) {
try (Statement st = conn.createStatement()) {
st.execute("""
CREATE TABLE IF NOT EXISTS java_results (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
subject text NOT NULL,
score int NOT NULL CHECK (score BETWEEN 0 AND 100)
)""");
st.execute("TRUNCATE java_results");
}
String insert = "INSERT INTO java_results (name, subject, score) VALUES (?, ?, ?)";
conn.setAutoCommit(false); // start a transaction
try (PreparedStatement ps = conn.prepareStatement(insert)) {
for (Result r : List.of(new Result("Amina", "Maths", 88),
new Result("Juma", "Maths", 42),
new Result("Neema", "Biology", 71))) {
ps.setString(1, r.name());
ps.setString(2, r.subject());
ps.setInt(3, r.score());
ps.addBatch();
}
ps.executeBatch();
conn.commit();
}
try (PreparedStatement ps = conn.prepareStatement(insert)) {
ps.setString(1, "Ali"); ps.setString(2, "Maths"); ps.setInt(3, 50); ps.executeUpdate();
ps.setString(1, "Bad"); ps.setString(2, "Maths"); ps.setInt(3, 150); ps.executeUpdate();
conn.commit();
} catch (SQLException e) {
conn.rollback(); // Ali is not saved either
System.out.println("Rolled back: " + e.getSQLState() + " check_violation");
}
conn.setAutoCommit(true);
String malicious = "Amina' OR '1'='1";
try (PreparedStatement ps = conn.prepareStatement("SELECT count(*) FROM java_results WHERE name = ?")) {
ps.setString(1, malicious);
try (ResultSet rs = ps.executeQuery()) {
rs.next();
System.out.println("Rows matching injection attempt: " + rs.getInt(1)); // 0
}
}
List<Result> passed = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(
"SELECT name, subject, score FROM java_results WHERE score >= ? ORDER BY score DESC")) {
ps.setInt(1, 45);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
passed.add(new Result(rs.getString("name"), rs.getString("subject"), rs.getInt("score")));
}
}
}
passed.forEach(System.out::println);
}
}
}Key points
- Always use
PreparedStatementplaceholders — never build SQL from user input. - Close connections, statements and result sets with try-with-resources.
- Group related changes in a transaction; use a connection pool in servers.
Exercise
Write a small StudentRepository class with save, findById (returning Optional) and findByForm methods using JDBC against the students table from the PostgreSQL track, and a main method that exercises each one.
Show solution
Try the exercise yourself first — then compare your approach with this one.
The repository hides all SQL behind three methods. save uses RETURNING id, findById returns an Optional instead of null, and findByForm maps each row to a record. Every statement is a PreparedStatement, and every resource is closed by try-with-resources.
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
public class StudentRepository {
record Student(Long id, String fullName, int form, String stream, String gender) {}
private final String url;
StudentRepository(String url) { this.url = url; }
Student save(Student s) throws SQLException {
String sql = "INSERT INTO students (full_name, form, stream, gender) VALUES (?, ?, ?, ?) RETURNING id";
try (Connection c = DriverManager.getConnection(url); PreparedStatement ps = c.prepareStatement(sql)) {
ps.setString(1, s.fullName());
ps.setInt(2, s.form());
ps.setString(3, s.stream());
ps.setString(4, s.gender());
try (ResultSet rs = ps.executeQuery()) {
rs.next();
return new Student(rs.getLong("id"), s.fullName(), s.form(), s.stream(), s.gender());
}
}
}
Optional<Student> findById(long id) throws SQLException {
try (Connection c = DriverManager.getConnection(url);
PreparedStatement ps = c.prepareStatement("SELECT id, full_name, form, stream, gender FROM students WHERE id = ?")) {
ps.setLong(1, id);
try (ResultSet rs = ps.executeQuery()) {
return rs.next() ? Optional.of(map(rs)) : Optional.empty();
}
}
}
List<Student> findByForm(int form) throws SQLException {
try (Connection c = DriverManager.getConnection(url);
PreparedStatement ps = c.prepareStatement(
"SELECT id, full_name, form, stream, gender FROM students WHERE form = ? ORDER BY full_name")) {
ps.setInt(1, form);
List<Student> out = new ArrayList<>();
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) out.add(map(rs));
}
return out;
}
}
private static Student map(ResultSet rs) throws SQLException {
return new Student(rs.getLong("id"), rs.getString("full_name"), rs.getInt("form"),
rs.getString("stream"), rs.getString("gender"));
}
public static void main(String[] args) throws SQLException {
var repo = new StudentRepository(System.getenv().getOrDefault("DATABASE_URL",
"jdbc:postgresql://localhost:5432/school?user=postgres&password=postgres"));
Student saved = repo.save(new Student(null, "Halima Omari", 2, "B", "F"));
System.out.println("Saved: " + saved);
System.out.println("Found: " + repo.findById(saved.id()).orElseThrow());
System.out.println("Missing: " + repo.findById(999_999));
repo.findByForm(2).forEach(s -> System.out.println("Form 2: " + s.fullName()));
}
}