JDBC

JDBC (Java Database Connectivity) is an API for connecting Java applications to relational databases. It provides methods for querying and updating data in a database.

Setting up JDBC

1. Import packages

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

2. Load and register the JDBC driver

Class.forName("com.mysql.cj.jdbc.Driver");

3. Establish a connection

String url = "jdbc:mysql://localhost:3306/myDatabase";
String username = "user";
String password = "password";
Connection connection = DriverManager.getConnection(url, username, password);

Common JDBC Interfaces

• Connection: Represents a connection to the database
• Statement: Used to execute SQL queries
• PreparedStatement: Precompiled SQL statement that can be parameterized
• CallableStatement: Used to execute stored procedures
• ResultSet: Contains the results of a query

Executing SQL Statements

SELECT Queries

Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery("SELECT * FROM users");

while (resultSet.next()) {
  int id = resultSet.getInt("id");
  String name = resultSet.getString("name");
  System.out.println(id + ": " + name);
}

INSERT, UPDATE, DELETE Statements

Statement statement = connection.createStatement();
int rowsAffected = statement.executeUpdate("INSERT INTO users(name, email) VALUES('John', 'john@example.com')");
System.out.println(rowsAffected + " row(s) inserted");

Using PreparedStatement

PreparedStatements help prevent SQL injection and improve performance:

String sql = "INSERT INTO users(name, email) VALUES(?, ?)";
PreparedStatement preparedStatement = connection.prepareStatement(sql);

preparedStatement.setString(1, "John");
preparedStatement.setString(2, "john@example.com");
preparedStatement.executeUpdate();

Transaction Management

try {
  // Disable auto-commit
  connection.setAutoCommit(false);

  // Execute multiple statements
  Statement statement = connection.createStatement();
  statement.executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
  statement.executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");

  // Commit the transaction
  connection.commit();
} catch (SQLException e) {
  // Rollback in case of error
  connection.rollback();
  e.printStackTrace();
} finally {
  // Re-enable auto-commit
  connection.setAutoCommit(true);
}

Closing Resources

Always close JDBC resources to prevent memory leaks:

try {
  if (resultSet != null) resultSet.close();
  if (statement != null) statement.close();
  if (connection != null) connection.close();
} catch (SQLException e) {
  e.printStackTrace();
}

Using try-with-resources (Java 7+):

try (
  Connection connection = DriverManager.getConnection(url, username, password);
  Statement statement = connection.createStatement();
  ResultSet resultSet = statement.executeQuery("SELECT * FROM users")
) {
  while (resultSet.next()) {
    // Process results
  }
} catch (SQLException e) {
  e.printStackTrace();
}

Database Connection Pooling

For production applications, connection pooling is recommended:

// Using HikariCP (add dependency to your project)
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:mysql://localhost:3306/myDatabase");
config.setUsername("user");
config.setPassword("password");
config.setMaximumPoolSize(10);

HikariDataSource dataSource = new HikariDataSource(config);
Connection connection = dataSource.getConnection();