Database Connectivity with JDBC
50 minJDBC (Java Database Connectivity) is Java's standard API for connecting to relational databases. It provides a database-independent way to execute SQL statements and retrieve results. JDBC drivers enable Java applications to communicate with different database systems (MySQL, PostgreSQL, Oracle, etc.) using the same API. Understanding JDBC is essential for building database-driven Java applications.
JDBC connection process involves loading the database driver, establishing a connection using `DriverManager.getConnection()`, creating statements, executing queries, processing results, and closing resources. Connections are expensive to create, so connection pooling (reusing connections) significantly improves performance. Understanding the JDBC workflow helps you implement efficient database access.
Prepared statements are essential for security and performance. They use placeholders (`?`) for parameters, preventing SQL injection attacks. Prepared statements are precompiled, making them faster for repeated queries. They're created with `prepareStatement()` and parameters are set with `setXXX()` methods. Always use prepared statements for queries with user input. Understanding prepared statements helps you write secure, efficient database code.
Transaction management ensures data consistency when multiple database operations must succeed or fail together. Transactions are started with `setAutoCommit(false)`, operations are performed, then either `commit()` (save changes) or `rollback()` (undo changes) is called. Transactions ensure atomicity—all operations succeed or all fail. Understanding transactions helps you maintain data integrity.
ResultSet objects represent query results. You iterate through results using `next()` and retrieve values using `getXXX()` methods. ResultSets can be scrollable and updatable depending on how they're created. Proper ResultSet handling includes checking for results and closing resources. Understanding ResultSets helps you process query results effectively.
Best practices include using connection pooling, always using prepared statements, managing transactions properly, closing resources in finally blocks or try-with-resources, handling SQL exceptions appropriately, and using connection pool libraries (HikariCP, Apache DBCP). Modern applications often use ORM frameworks (Hibernate, JPA) that abstract JDBC, but understanding JDBC is still valuable. Understanding JDBC enables you to build database-driven Java applications.
Key Concepts
- JDBC provides standard API for database connectivity.
- Connection pooling improves performance by reusing connections.
- Prepared statements prevent SQL injection and improve performance.
- Transactions ensure data consistency across multiple operations.
- ResultSets represent and process query results.
Learning Objectives
Master
- Connecting to databases using JDBC
- Using prepared statements for secure database operations
- Managing transactions for data consistency
- Processing query results with ResultSets
Develop
- Database security thinking and SQL injection prevention
- Understanding transaction management and data integrity
- Designing efficient database access layers
Tips
- Always use prepared statements for queries with user input.
- Use connection pooling to improve performance.
- Manage transactions properly for data consistency.
- Close resources in finally blocks or try-with-resources.
Common Pitfalls
- Not using prepared statements, creating SQL injection vulnerabilities.
- Not managing transactions, causing data inconsistency.
- Not closing connections, causing resource leaks.
- Not handling SQL exceptions properly, causing application crashes.
Summary
- JDBC provides standard API for database connectivity.
- Prepared statements prevent SQL injection and improve performance.
- Transactions ensure data consistency.
- Understanding JDBC enables database-driven applications.
- Connection pooling improves performance significantly.
Exercise
Create a JDBC application that demonstrates database operations with proper connection management.
import java.sql.*;
import java.util.*;
class DatabaseManager {
private static final String URL = "jdbc:h2:mem:testdb";
private static final String USER = "sa";
private static final String PASSWORD = "";
static {
try {
// Initialize H2 database
Class.forName("org.h2.Driver");
initializeDatabase();
} catch (ClassNotFoundException e) {
e.printStackTrace();
}
}
private static void initializeDatabase() {
String createTable = """
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INT
)
""";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
Statement stmt = conn.createStatement()) {
stmt.execute(createTable);
} catch (SQLException e) {
e.printStackTrace();
}
}
public static void insertUser(String name, String email, int age) {
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setString(1, name);
pstmt.setString(2, email);
pstmt.setInt(3, age);
int rowsAffected = pstmt.executeUpdate();
System.out.println("Inserted " + rowsAffected + " user(s)");
} catch (SQLException e) {
e.printStackTrace();
}
}
public static List<Map<String, Object>> getAllUsers() {
List<Map<String, Object>> users = new ArrayList<>();
String sql = "SELECT * FROM users";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(sql)) {
ResultSetMetaData metaData = rs.getMetaData();
int columnCount = metaData.getColumnCount();
while (rs.next()) {
Map<String, Object> user = new HashMap<>();
for (int i = 1; i <= columnCount; i++) {
String columnName = metaData.getColumnName(i);
Object value = rs.getObject(i);
user.put(columnName, value);
}
users.add(user);
}
} catch (SQLException e) {
e.printStackTrace();
}
return users;
}
public static void updateUserAge(String email, int newAge) {
String sql = "UPDATE users SET age = ? WHERE email = ?";
try (Connection conn = DriverManager.getConnection(URL, USER, PASSWORD);
PreparedStatement pstmt = conn.prepareStatement(sql)) {
pstmt.setInt(1, newAge);
pstmt.setString(2, email);
int rowsAffected = pstmt.executeUpdate();
System.out.println("Updated " + rowsAffected + " user(s)");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public class JDBCTest {
public static void main(String[] args) {
// Insert users
DatabaseManager.insertUser("Alice", "alice@example.com", 25);
DatabaseManager.insertUser("Bob", "bob@example.com", 30);
DatabaseManager.insertUser("Charlie", "charlie@example.com", 35);
// Display all users
System.out.println("\nAll users:");
List<Map<String, Object>> users = DatabaseManager.getAllUsers();
users.forEach(user -> {
System.out.printf("ID: %s, Name: %s, Email: %s, Age: %s%n",
user.get("ID"), user.get("NAME"), user.get("EMAIL"), user.get("AGE"));
});
// Update user age
DatabaseManager.updateUserAge("alice@example.com", 26);
// Display updated data
System.out.println("\nAfter update:");
users = DatabaseManager.getAllUsers();
users.forEach(user -> {
System.out.printf("ID: %s, Name: %s, Email: %s, Age: %s%n",
user.get("ID"), user.get("NAME"), user.get("EMAIL"), user.get("AGE"));
});
}
}