Java JDBC & Database Connectivity (MySQL/PostgreSQL) Tutorial

Java Database Connectivity (JDBC) is a Java API that allows you to connect and interact with relational databases like MySQL and PostgreSQL. JDBC provides a standard way to execute SQL queries, retrieve results, and manage database transactions. This tutorial will guide you through the basics of using JDBC to connect to a database, perform CRUD operations, and handle transactions.

By the end of this tutorial, you'll understand how to use JDBC to connect to MySQL or PostgreSQL databases and perform common database operations in Java.

Setting Up JDBC

To use JDBC, you need to:

Add the Database Driver: Download the JDBC driver for your database (e.g., MySQL or PostgreSQL) and add it to your project's classpath.
Load the Driver: Register the driver using Class.forName() (optional in modern JDBC).
Establish a Connection: Use the DriverManager.getConnection() method to connect to the database.

// MySQL JDBC URL
String jdbcUrl = "jdbc:mysql://localhost:3306/mydatabase";
String username = "root";
String password = "password";

// PostgreSQL JDBC URL
// String jdbcUrl = "jdbc:postgresql://localhost:5432/mydatabase";

try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
    System.out.println("Connected to the database!");
} catch (SQLException e) {
    e.printStackTrace();
}

Performing CRUD Operations

CRUD stands for Create, Read, Update, and Delete. These are the basic operations you can perform on a database.

Create (Insert):

String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, "John Doe");
    statement.setString(2, "john.doe@example.com");
    int rowsInserted = statement.executeUpdate();
    System.out.println(rowsInserted + " row(s) inserted.");
}

Read (Select):

String sql = "SELECT * FROM users";
try (Statement statement = connection.createStatement();
     ResultSet resultSet = statement.executeQuery(sql)) {
    while (resultSet.next()) {
        System.out.println("ID: " + resultSet.getInt("id") +
                           ", Name: " + resultSet.getString("name") +
                           ", Email: " + resultSet.getString("email"));
    }
}

Update:

String sql = "UPDATE users SET email = ? WHERE id = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setString(1, "john.new@example.com");
    statement.setInt(2, 1);
    int rowsUpdated = statement.executeUpdate();
    System.out.println(rowsUpdated + " row(s) updated.");
}

Delete:

String sql = "DELETE FROM users WHERE id = ?";
try (PreparedStatement statement = connection.prepareStatement(sql)) {
    statement.setInt(1, 1);
    int rowsDeleted = statement.executeUpdate();
    System.out.println(rowsDeleted + " row(s) deleted.");
}

Handling Transactions

Transactions ensure that a set of database operations are executed atomically. You can manage transactions using the Connection object.

Example:

try (Connection connection = DriverManager.getConnection(jdbcUrl, username, password)) {
    connection.setAutoCommit(false); // Disable auto-commit

    try {
        // Perform multiple operations
        String sql1 = "INSERT INTO users (name, email) VALUES (?, ?)";
        try (PreparedStatement statement1 = connection.prepareStatement(sql1)) {
            statement1.setString(1, "Jane Doe");
            statement1.setString(2, "jane.doe@example.com");
            statement1.executeUpdate();
        }

        String sql2 = "UPDATE users SET email = ? WHERE id = ?";
        try (PreparedStatement statement2 = connection.prepareStatement(sql2)) {
            statement2.setString(1, "jane.new@example.com");
            statement2.setInt(2, 2);
            statement2.executeUpdate();
        }

        connection.commit(); // Commit the transaction
    } catch (SQLException e) {
        connection.rollback(); // Rollback in case of error
        e.printStackTrace();
    }
}

Using Connection Pooling

Connection pooling improves performance by reusing database connections instead of creating new ones for each request. Libraries like HikariCP and Apache DBCP provide connection pooling implementations.

Example with HikariCP:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class Main {
    public static void main(String[] args) {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:3306/mydatabase");
        config.setUsername("root");
        config.setPassword("password");

        try (HikariDataSource dataSource = new HikariDataSource(config);
             Connection connection = dataSource.getConnection()) {
            System.out.println("Connected using HikariCP!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This tutorial covered the basics of JDBC and database connectivity in Java. Practice using these concepts to interact with MySQL or PostgreSQL databases in your applications.

0 Interaction 0 Views 0 likes

Java JDBC & Database Connectivity (MySQL/PostgreSQL) Tutorial

Setting Up JDBC

Performing CRUD Operations

Handling Transactions

Using Connection Pooling

Welcome to Ptutorials