TL;DR: JDBC to standard Java do komunikacji z bazami danych. Używasz DriverManager.getConnection() do połączenia, Statement do wykonania SQL, ResultSet do odczytania wyników. Pamiętaj o zamykaniu połączeń w finally lub try-with-resources. Connection pooling w produkcji jest obowiązkowy.
Jeśli piszesz aplikację w Javie która potrzebuje przechowywać dane, prędzej czy później musisz połączyć się z bazą danych. JDBC (Java Database Connectivity) to fundament wszystkich bibliotek dostępu do danych w Javie – nawet Hibernate i Spring Data używają JDBC pod spodem.
Dlaczego JDBC jest ważne
JDBC to standardowy API w Javie od 1997 roku. Każdy Java developer powinien rozumieć podstawy JDBC, nawet jeśli na co dzień używa ORM. To knowledge که pomaga w debugowaniu problemów wydajnościowych i rozumieniu jak działają higher-level abstractions.
Performance matters in business applications. Understanding JDBC pozwala na optymalizację database interactions, proper connection management i avoiding common pitfalls که mogą kosztować firmę users i revenue gdy aplikacja się tnie pod obciążeniem.
Co się nauczysz:
- Jak nawiązać połączenie z bazą danych przez JDBC
- Podstawowe klasy: Connection, Statement, ResultSet
- Jak wykonywać zapytania SELECT, INSERT, UPDATE, DELETE
- Best practices dla zarządzania połączeniami
- Connection pooling i dlaczego jest niezbędny w produkcji
Wymagania wstępne:
- Podstawowa znajomość Java i programowania obiektowego
- Znajomość podstaw SQL (SELECT, INSERT, UPDATE, DELETE)
- Rozumienie pojęć: baza danych, tabela, rekord
- Experience with try-catch exception handling
JDBC architecture overview
Analogia: JDBC to jak uniwersalny translator między Java application a różnymi bazami danych. Mówisz po „JDBC”, a translator przekazuje message do MySQL, PostgreSQL czy Oracle w ich native języku.
JDBC składa się z kilku key components:
- DriverManager – manages database drivers and connections
- Connection – represents database connection
- Statement – executes SQL queries
- ResultSet – contains query results
- SQLException – handles database errors
Setup i podstawowe połączenie
Database driver dependency
<!-- MySQL driver -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.39</version>
</dependency>
<!-- PostgreSQL driver -->
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.4.1209</version>
</dependency>
<!-- H2 in-memory database (testing) -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>1.4.192</version>
</dependency>
Basic connection example
import java.sql.*;
public class DatabaseConnection {
// Database connection parameters
private static final String URL = "jdbc:mysql://localhost:3306/myapp";
private static final String USERNAME = "root";
private static final String PASSWORD = "password";
public static void main(String[] args) {
Connection connection = null;
try {
// Load driver (optional in modern JDBC)
Class.forName("com.mysql.jdbc.Driver");
// Establish connection
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
System.out.println("Connected to database successfully!");
// Test connection
if (connection != null && !connection.isClosed()) {
System.out.println("Database: " + connection.getMetaData().getDatabaseProductName());
System.out.println("Version: " + connection.getMetaData().getDatabaseProductVersion());
}
} catch (ClassNotFoundException e) {
System.err.println("MySQL JDBC Driver not found!");
e.printStackTrace();
} catch (SQLException e) {
System.err.println("Connection failed!");
e.printStackTrace();
} finally {
// Always close connections!
try {
if (connection != null && !connection.isClosed()) {
connection.close();
System.out.println("Connection closed.");
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
Critical: Always close database connections! Unclosed connections lead to connection leaks and database exhaustion. Use try-with-resources or finally blocks.
Executing SQL queries
SELECT queries with ResultSet
public class UserDAO {
private static final String URL = "jdbc:mysql://localhost:3306/myapp";
private static final String USERNAME = "root";
private static final String PASSWORD = "password";
public List<User> getAllUsers() {
List<User> users = new ArrayList<>();
String sql = "SELECT id, name, email, age FROM users";
try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql)) {
while (resultSet.next()) {
User user = new User();
user.setId(resultSet.getLong("id"));
user.setName(resultSet.getString("name"));
user.setEmail(resultSet.getString("email"));
user.setAge(resultSet.getInt("age"));
users.add(user);
}
} catch (SQLException e) {
System.err.println("Error fetching users: " + e.getMessage());
throw new RuntimeException("Database error", e);
}
return users;
}
public User getUserById(Long id) {
String sql = "SELECT id, name, email, age FROM users WHERE id = ?";
try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setLong(1, id);
try (ResultSet resultSet = statement.executeQuery()) {
if (resultSet.next()) {
User user = new User();
user.setId(resultSet.getLong("id"));
user.setName(resultSet.getString("name"));
user.setEmail(resultSet.getString("email"));
user.setAge(resultSet.getInt("age"));
return user;
}
}
} catch (SQLException e) {
System.err.println("Error fetching user: " + e.getMessage());
throw new RuntimeException("Database error", e);
}
return null;
}
}
Pro tip: Try-with-resources automatically closes Connection, Statement, and ResultSet. Much cleaner than manual close() calls in finally blocks.
INSERT, UPDATE, DELETE operations
public class UserDAO {
public User createUser(User user) {
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement statement = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
statement.setString(1, user.getName());
statement.setString(2, user.getEmail());
statement.setInt(3, user.getAge());
int rowsAffected = statement.executeUpdate();
if (rowsAffected > 0) {
// Get generated ID
try (ResultSet generatedKeys = statement.getGeneratedKeys()) {
if (generatedKeys.next()) {
user.setId(generatedKeys.getLong(1));
}
}
}
} catch (SQLException e) {
System.err.println("Error creating user: " + e.getMessage());
throw new RuntimeException("Database error", e);
}
return user;
}
public boolean updateUser(User user) {
String sql = "UPDATE users SET name = ?, email = ?, age = ? WHERE id = ?";
try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, user.getName());
statement.setString(2, user.getEmail());
statement.setInt(3, user.getAge());
statement.setLong(4, user.getId());
int rowsAffected = statement.executeUpdate();
return rowsAffected > 0;
} catch (SQLException e) {
System.err.println("Error updating user: " + e.getMessage());
throw new RuntimeException("Database error", e);
}
}
public boolean deleteUser(Long id) {
String sql = "DELETE FROM users WHERE id = ?";
try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setLong(1, id);
int rowsAffected = statement.executeUpdate();
return rowsAffected > 0;
} catch (SQLException e) {
System.err.println("Error deleting user: " + e.getMessage());
throw new RuntimeException("Database error", e);
}
}
}
Statement vs PreparedStatement
| Aspekt | Statement | PreparedStatement |
|---|---|---|
| Performance | Slower (compiled każdy raz) | Faster (pre-compiled) |
| SQL Injection | Vulnerable | Protected |
| Readability | SQL visible in code | Parameters separated |
| Reusability | Limited | High (same query, different params) |
| Database optimization | No | Yes (execution plan caching) |
SQL Injection vulnerability example
// DANGEROUS - vulnerable to SQL injection
public User findUserByEmail(String email) {
String sql = "SELECT * FROM users WHERE email = '" + email + "'";
// If email = "'; DROP TABLE users; --" → disaster!
try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql)) {
// ...
}
}
// SAFE - protected against SQL injection
public User findUserByEmail(String email) {
String sql = "SELECT * FROM users WHERE email = ?";
try (Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
PreparedStatement statement = connection.prepareStatement(sql)) {
statement.setString(1, email); // Automatically escaped
try (ResultSet resultSet = statement.executeQuery()) {
// ...
}
}
}
Security Alert: Never concatenate user input directly into SQL strings! Always use PreparedStatement parameters to prevent SQL injection attacks.
Transaction management
public void transferMoney(Long fromAccountId, Long toAccountId, BigDecimal amount) {
String withdrawSql = "UPDATE accounts SET balance = balance - ? WHERE id = ?";
String depositSql = "UPDATE accounts SET balance = balance + ? WHERE id = ?";
Connection connection = null;
try {
connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
// Start transaction
connection.setAutoCommit(false);
// Withdraw money
try (PreparedStatement withdrawStmt = connection.prepareStatement(withdrawSql)) {
withdrawStmt.setBigDecimal(1, amount);
withdrawStmt.setLong(2, fromAccountId);
int withdrawRows = withdrawStmt.executeUpdate();
if (withdrawRows == 0) {
throw new SQLException("Account not found: " + fromAccountId);
}
}
// Deposit money
try (PreparedStatement depositStmt = connection.prepareStatement(depositSql)) {
depositStmt.setBigDecimal(1, amount);
depositStmt.setLong(2, toAccountId);
int depositRows = depositStmt.executeUpdate();
if (depositRows == 0) {
throw new SQLException("Account not found: " + toAccountId);
}
}
// Commit transaction
connection.commit();
System.out.println("Transfer completed successfully");
} catch (SQLException e) {
System.err.println("Transfer failed: " + e.getMessage());
// Rollback transaction
try {
if (connection != null) {
connection.rollback();
System.out.println("Transaction rolled back");
}
} catch (SQLException rollbackEx) {
System.err.println("Rollback failed: " + rollbackEx.getMessage());
}
throw new RuntimeException("Transfer failed", e);
} finally {
try {
if (connection != null) {
connection.setAutoCommit(true); // Reset to default
connection.close();
}
} catch (SQLException e) {
System.err.println("Error closing connection: " + e.getMessage());
}
}
}
Connection pooling
Creating connections is expensive. In production, always use connection pooling:
<!-- HikariCP - fast connection pool -->
<dependency>
<groupId>com.zaxxer</groupId>
<artifactId>HikariCP</artifactId>
<version>2.4.7</version>
</dependency>
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
public class DatabaseManager {
private static HikariDataSource dataSource;
static {
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/myapp");
config.setUsername("root");
config.setPassword("password");
// Pool configuration
config.setMaximumPoolSize(20);
config.setMinimumIdle(5);
config.setConnectionTimeout(30000);
config.setIdleTimeout(600000);
config.setMaxLifetime(1800000);
// Performance settings
config.setLeakDetectionThreshold(60000);
config.addDataSourceProperty("cachePrepStmts", "true");
config.addDataSourceProperty("prepStmtCacheSize", "250");
config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
dataSource = new HikariDataSource(config);
}
public static Connection getConnection() throws SQLException {
return dataSource.getConnection();
}
public static void close() {
if (dataSource != null) {
dataSource.close();
}
}
}
// Updated DAO using connection pool
public class UserDAO {
public List<User> getAllUsers() {
List<User> users = new ArrayList<>();
String sql = "SELECT id, name, email, age FROM users";
try (Connection connection = DatabaseManager.getConnection();
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(sql)) {
while (resultSet.next()) {
User user = new User();
user.setId(resultSet.getLong("id"));
user.setName(resultSet.getString("name"));
user.setEmail(resultSet.getString("email"));
user.setAge(resultSet.getInt("age"));
users.add(user);
}
} catch (SQLException e) {
throw new RuntimeException("Database error", e);
}
return users;
}
}
Pool benefits:
- Performance: Reuse existing connections instead of creating new ones
- Resource management: Limit max connections to database
- Monitoring: Track connection usage, leaks, performance
- Failover: Handle database reconnections automatically
Batch operations
public void insertUsers(List<User> users) {
String sql = "INSERT INTO users (name, email, age) VALUES (?, ?, ?)";
try (Connection connection = DatabaseManager.getConnection();
PreparedStatement statement = connection.prepareStatement(sql)) {
connection.setAutoCommit(false);
for (User user : users) {
statement.setString(1, user.getName());
statement.setString(2, user.getEmail());
statement.setInt(3, user.getAge());
statement.addBatch();
// Execute batch every 1000 records
if (users.indexOf(user) % 1000 == 0) {
statement.executeBatch();
statement.clearBatch();
}
}
// Execute remaining records
statement.executeBatch();
connection.commit();
} catch (SQLException e) {
throw new RuntimeException("Batch insert failed", e);
}
}
Error handling patterns
public class DatabaseErrorHandler {
public static void handleSQLException(SQLException e) {
String sqlState = e.getSQLState();
int errorCode = e.getErrorCode();
String message = e.getMessage();
System.err.println("SQL Error - State: " + sqlState +
", Code: " + errorCode +
", Message: " + message);
// MySQL specific error codes
switch (errorCode) {
case 1062: // Duplicate entry
throw new IllegalArgumentException("Record already exists");
case 1054: // Unknown column
throw new IllegalArgumentException("Invalid column name");
case 1146: // Table doesn't exist
throw new IllegalStateException("Database schema error");
case 1045: // Access denied
throw new SecurityException("Database access denied");
default:
throw new RuntimeException("Database error", e);
}
}
}
Best practices
✅ JDBC best practices:
- Always use try-with-resources – automatic resource management
- Prefer PreparedStatement – security and performance
- Use connection pooling – never create connections on demand in production
- Handle transactions properly – commit/rollback appropriately
- Validate inputs – even with PreparedStatement, validate business logic
- Use batch operations – for multiple similar operations
Błąd #1: Not closing connections – leads to connection pool exhaustion and database overload.
Błąd #2: Using Statement instead of PreparedStatement – security vulnerability and performance issues.
Błąd #3: Catching SQLException and doing nothing – hides important database errors.
Błąd #4: Creating new connections for each database call – destroys performance under load.
JDBC alternatives
While JDBC is fundamental, modern applications often use higher-level abstractions:
- Spring JdbcTemplate – eliminates boilerplate code
- MyBatis – SQL-centric approach with mapping
- Hibernate/JPA – object-relational mapping
- jOOQ – type-safe SQL building
- Spring Data JPA – repository pattern with query methods
Dlaczego używać JDBC gdy są ORM-y?
JDBC daje full control over SQL and performance. ORM-y są wygodne ale czasem generują inefficient queries. JDBC jest też foundation – understanding it helps debug ORM issues.
Jak obsłużyć różne bazy danych?
JDBC URL i driver class się różnią, ale API jest standardowe. Use różne profile w aplikacji albo configuration properties dla różnych environments.
Czy JDBC jest thread-safe?
Connection objects nie są thread-safe. Each thread powinien mieć własną Connection. Connection pools handle this automatically przez giving każdy thread separate connection.
Jak zoptymalizować JDBC performance?
Use connection pooling, PreparedStatement caching, batch operations, proper fetch sizes, i avoid SELECT * queries. Profile your SQL queries in database.
Co z NoSQL databases?
JDBC jest dla relational databases. NoSQL (MongoDB, Redis) mają własne drivers i APIs. Some NoSQL databases offer JDBC-like interfaces ale to nie standard.
Jak testować kod z JDBC?
Use in-memory databases (H2, Derby) dla unit tests. Integration tests mogą używać TestContainers z real database instances. Mock Connection/ResultSet dla isolated unit tests.
Przydatne zasoby:
- Oracle JDBC Tutorial
- HikariCP Connection Pool
- MySQL Connector/J Documentation
- PostgreSQL JDBC Documentation
🚀 Zadanie dla Ciebie
Stwórz complete CRUD application dla book library:
- Database schema: books (id, title, author, isbn, year, available)
- BookDAO class z methods: save, findById, findAll, update, delete
- Use PreparedStatement dla all operations
- Implement connection pooling z HikariCP
- Add transaction support dla batch operations
- Include proper error handling i logging
Test scenarios:
- Insert 1000 books using batch operations
- Search books by author (test SQL injection protection)
- Update book availability in transaction
- Handle duplicate ISBN errors gracefully
Masz pytania o JDBC? Podziel się swoimi doświadczeniami w komentarzach – JDBC może wydawać się old-school, ale understanding fundamentals makes you better developer!