Say Hello in JDBC 4.0

Agenda

  • Basic JDBC Flow
  • Connection
  • Statement
  • ResultSet
  • SELECT Query
  • INSERT / UPDATE / DELETE
  • Batch Operations
  • Transactions

JDBC Overview

  • Java Database Connectivity (JDBC)
  • Connects Java apps to relational databases
  • Execute SQL, retrieve results
  • Uses JDBC drivers

JDBC Flow

Application → JDBC API → JDBC Driver → Database
  1. Load Driver
  2. Get Connection
  3. Create Statement
  4. Execute SQL
  5. Process ResultSet
  6. Close resources

Connection

Connection conn = DriverManager.getConnection(
"jdbc:mysql://localhost:3306/school", "user", "pass");
  • Session with DB
  • Must be closed

Statement

Statement stmt = conn.createStatement();
  • For static SQL
  • Use PreparedStatement instead

ResultSet

ResultSet rs = stmt.executeQuery("SELECT * FROM student");
while (rs.next()) {
String name = rs.getString("name");
int marks = rs.getInt("marks");
}

SELECT Example

String sql = "SELECT name, marks FROM student";
ResultSet rs = stmt.executeQuery(sql);
while (rs.next()) {
System.out.println(rs.getString("name") + " - " + rs.getInt("marks"));
}

INSERT / UPDATE / DELETE

String sql = "UPDATE student SET marks = 90 WHERE name = 'Madasamy'";
int count = stmt.executeUpdate(sql);
System.out.println(count + " row(s) affected");

PreparedStatement

String sql = "INSERT INTO student(name, marks) VALUES (?, ?)";
PreparedStatement ps = conn.prepareStatement(sql);
ps.setString(1, "Parthiban");
ps.setInt(2, 95);
ps.executeUpdate();
  • Prevents SQL injection
  • Improves performance

Batch Operations

PreparedStatement ps = conn.prepareStatement(...);
for (Mark m : marks) {
ps.setInt(1, studentId);
ps.setString(2, m.subject());
ps.setInt(3, m.score());
ps.addBatch();
}
ps.executeBatch();

Transactions

conn.setAutoCommit(false);
try {
insertStudent();
insertMarks();
conn.commit();
} catch (SQLException e) {
conn.rollback();
}
  • Ensures all-or-nothing
  • Must manually commit or rollback

Best Practices

  • Close all JDBC resources
  • Use try-with-resources
  • Prefer PreparedStatement
  • Handle transactions explicitly

Real-Life Analogy

Bank Transfer: Debit one account, Credit another

Both must succeed or fail together. That's a transaction.

That's a Wrap!

Explore:

  • Connection pooling (e.g. HikariCP)
  • ORM tools (JPA, Hibernate)
  • Advanced transaction isolation

Questions?

Ask anything about JDBC!