Section 7: Java Database Connectivity (JDBC)

Lesson 1: Working with Databases in Java

1.1 Connecting to Databases

JDBC (Java Database Connectivity) allows Java applications to interact with relational databases.

Example (Connecting to a Database): 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class JdbcConnectionExample {

    public static void main(String[] args) {

        // JDBC URL, username, and password of MySQL server

        String url = "jdbc:mysql://localhost:3306/your_database";

        String user = "your_username";

        String password = "your_password";

        // Establishing a connection

        try (Connection connection = DriverManager.getConnection(url, user, password)) {

            System.out.println("Connected to the database");

        } catch (SQLException e) {

            System.out.println("Connection failed: " + e.getMessage());

        }

    }

}

1.2 Executing SQL Queries

JDBC enables the execution of SQL queries and retrieval of results.

Example (Executing SQL Query): 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class JdbcQueryExample {

    public static void main(String[] args) {

        // JDBC URL, username, and password of MySQL server

        String url = "jdbc:mysql://localhost:3306/your_database";

        String user = "your_username";

        String password = "your_password";

        // Establishing a connection

        try (Connection connection = DriverManager.getConnection(url, user, password)) {

            // Creating a statement

            Statement statement = connection.createStatement();

            // Executing a query

            String sqlQuery = "SELECT * FROM your_table";

            ResultSet resultSet = statement.executeQuery(sqlQuery);

            // Processing the result set

            while (resultSet.next()) {

                // Accessing columns by name or index

                int id = resultSet.getInt("id");

                String name = resultSet.getString("name");

                System.out.println("ID: " + id + ", Name: " + name);

            }

        } catch (SQLException e) {

            System.out.println("SQL query execution failed: " + e.getMessage());

        }

    }

}

Lesson 2: Transaction Management

2.1 Handling Database Transactions

JDBC allows you to manage transactions, ensuring that a series of SQL statements are executed atomically.

Example (Transaction Management): 

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

import java.sql.Statement;

public class JdbcTransactionExample {

    public static void main(String[] args) {

        // JDBC URL, username, and password of MySQL server

        String url = "jdbc:mysql://localhost:3306/your_database";

        String user = "your_username";

        String password = "your_password";

        // Establishing a connection

        try (Connection connection = DriverManager.getConnection(url, user, password)) {

            // Disabling auto-commit to start a transaction

            connection.setAutoCommit(false);

            try {

                // Performing multiple SQL operations as part of a transaction

                Statement statement = connection.createStatement();

                statement.executeUpdate("UPDATE your_table SET column1 = value1 WHERE condition");

                statement.executeUpdate("INSERT INTO your_table (column1, column2) VALUES (value1, value2)");

                // Committing the transaction if all operations succeed

                connection.commit();

                System.out.println("Transaction committed successfully");

            } catch (SQLException e) {

                // Rolling back the transaction in case of an exception

                connection.rollback();

                System.out.println("Transaction rolled back: " + e.getMessage());

            } finally {

                // Enabling auto-commit again

                connection.setAutoCommit(true);

            }

        } catch (SQLException e) {

            System.out.println("Connection failed: " + e.getMessage());

        }

    }

}

Working with databases in Java using JDBC is a fundamental skill for developers. These examples demonstrate how to establish a connection, execute SQL queries, and manage transactions. Practice these concepts to become proficient in database interactions with Java.