12.2 Database Connectivity and Management

Overview of Databases

Databases are essential for storing, managing, and retrieving data in a structured manner. In software development, databases are used to provide persistent storage for applications, enabling data consistency and quick access. Databases can be relational (like MySQL, PostgreSQL) or NoSQL (like MongoDB), each suitable for different data needs.

• Relational Databases: Organize data in tables with defined relationships (e.g., SQL databases).
• NoSQL Databases: Store unstructured data, more flexible for applications with large-scale, non-relational data needs.

---

12.2 Introduction to JDBC

JDBC (Java Database Connectivity) is an API in Java for connecting and executing operations on a database. JDBC provides a standard interface for accessing various databases, allowing developers to perform SQL operations in a consistent manner.

• JDBC Drivers: Software that allows a Java application to connect to a specific database type.
• Components: Includes Connection, Statement, PreparedStatement, ResultSet, and DriverManager.

---

12.3 Setting up a Database Connection

To set up a database connection using JDBC, we follow these steps:

1. Load the JDBC Driver: Ensures Java can communicate with the database.
2. Establish a Connection: Use the DriverManager.getConnection() method to connect.
3. Handle Exceptions: Manage any SQLException that may arise.

Example: Establishing a Connection

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

public class DatabaseConnection {
    private static final String URL = "jdbc:mysql://localhost:3306/mydatabase";
    private static final String USER = "root";
    private static final String PASSWORD = "password";

    public static void main(String[] args) {
        try (Connection connection = DriverManager.getConnection(URL, USER, PASSWORD)) {
            System.out.println("Connected to the database successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

This example demonstrates connecting to a MySQL database. Make sure the MySQL driver is added to your project’s classpath.

---

12.4 CRUD Operations with JDBC

CRUD stands for Create, Read, Update, and Delete, which are fundamental operations when working with database records.

1. Creating Records (INSERT)

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class InsertExample {
    public static void main(String[] args) {
        String insertSQL = "INSERT INTO employees (name, position, salary) VALUES (?, ?, ?)";

        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root", "password");
             PreparedStatement pstmt = connection.prepareStatement(insertSQL)) {

            pstmt.setString(1, "Alice");
            pstmt.setString(2, "Developer");
            pstmt.setDouble(3, 70000.0);
            pstmt.executeUpdate();
            System.out.println("Record inserted successfully!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

2. Reading Records (SELECT)

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

public class SelectExample {
    public static void main(String[] args) {
        String query = "SELECT * FROM employees";

        try (Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/mydatabase", "root", "password");
             Statement stmt = connection.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {

            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("id") + ", Name: " + rs.getString("name") +
                        ", Position: " + rs.getString("position") + ", Salary: " + rs.getDouble("salary"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

3. Updating Records (UPDATE)

String updateSQL = "UPDATE employees SET salary = ? WHERE name = ?";
PreparedStatement pstmt = connection.prepareStatement(updateSQL);
pstmt.setDouble(1, 80000.0);
pstmt.setString(2, "Alice");
pstmt.executeUpdate();

4. Deleting Records (DELETE)

String deleteSQL = "DELETE FROM employees WHERE name = ?";
PreparedStatement pstmt = connection.prepareStatement(deleteSQL);
pstmt.setString(1, "Alice");
pstmt.executeUpdate();

---

12.5 Prepared Statements and Transactions

Prepared Statements

Using Prepared Statements improves efficiency and security by preventing SQL injection attacks.

String sql = "INSERT INTO employees (name, position, salary) VALUES (?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, "John");
pstmt.setString(2, "Manager");
pstmt.setDouble(3, 90000);
pstmt.executeUpdate();

Transactions

Transactions ensure that multiple operations are executed as a single unit. commit saves all changes, while rollback undoes them in case of errors.

connection.setAutoCommit(false);
try {
    // Execute multiple SQL commands
    connection.commit();
} catch (SQLException e) {
    connection.rollback();
}

---

12.6 Result Sets and Data Manipulation

ResultSet provides methods to navigate and retrieve data from query results.

ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
while (rs.next()) {
    String name = rs.getString("name");
    System.out.println("Name: " + name);
}

---

12.7 Managing Database Connections and Best Practices

1. Connection Pooling: Reuses connections, reducing overhead.
2. Close Resources: Close Connection, Statement, and ResultSet objects to free up resources.
3. Exception Handling: Use try-catch blocks to manage exceptions.

---

12.8 Database Integration in Java Applications

Integrate database connectivity with your Java applications by creating dedicated classes for database interactions, allowing modular code that’s easy to maintain.

---

12.9 Wrap Up

In this chapter, we covered JDBC concepts, CRUD operations, prepared statements, transaction handling, and best practices for effective database management. By mastering these concepts, you’ll be able to create robust, data-driven Java applications.