CRUD operations

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

public class CRUDSQLite {
    public static void main(String[] args) {
        String url = "jdbc:sqlite:persons.db";

        // Create the person table if it doesn't exist
        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement stmt = conn.prepareStatement(
                     "CREATE TABLE IF NOT EXISTS person (cod INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, age INT)"
             )) {
            stmt.execute();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }

        int option;
        Scanner scanner = new Scanner(System.in);

        do {
            System.out.println("\nMenu:");
            System.out.println("1. Add");
            System.out.println("2. View");
            System.out.println("3. Edit");
            System.out.println("4. Delete");
            System.out.println("5. Exit");
            System.out.print("Choose an option: ");
            option = scanner.nextInt();

            switch (option) {
                case 1:
                    addPerson(url);
                    break;
                case 2:
                    viewPersons(url);
                    break;
                case 3:
                    editPerson(url);
                    break;
                case 4:
                    deletePerson(url);
                    break;
            }
        } while (option != 5);
    }

    public static void addPerson(String url) {
        try (Connection conn = DriverManager.getConnection(url)) {
            conn.setAutoCommit(false);

            Scanner scanner = new Scanner(System.in);
            System.out.print("Name: ");
            String name = scanner.nextLine();
            System.out.print("Age: ");
            int age = scanner.nextInt();

            try (PreparedStatement stmt = conn.prepareStatement("INSERT INTO person (name, age) VALUES (?, ?)")) {
                stmt.setString(1, name);
                stmt.setInt(2, age);
                stmt.executeUpdate();
                conn.commit();
                System.out.println("Person added successfully.");
            } catch (SQLException e) {
                conn.rollback();
                System.out.println("Error adding person: " + e.getMessage());
            }
        } catch (SQLException e) {
            System.out.println("Error connecting to the database: " + e.getMessage());
        }
    }

    public static void viewPersons(String url) {
        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement stmt = conn.prepareStatement("SELECT cod, name, age FROM person");
             ResultSet rs = stmt.executeQuery()) {
            System.out.println("\nPersons in the database:");
            while (rs.next()) {
                System.out.println("ID: " + rs.getInt("cod"));
                System.out.println("Name: " + rs.getString("name"));
                System.out.println("Age: " + rs.getInt("age"));
                System.out.println();
            }
        } catch (SQLException e) {
            System.out.println("Error viewing persons: " + e.getMessage());
        }
    }

    public static void editPerson(String url) {
        Scanner scanner = new Scanner(System.in);
        System.out.print("ID of the person to edit: ");
        int id = scanner.nextInt();

        if (personExists(url, id)) {
            System.out.print("New name: ");
            String newName = scanner.next();
            System.out.print("New age: ");
            int newAge = scanner.nextInt();

            try (Connection conn = DriverManager.getConnection(url)) {
                conn.setAutoCommit(false);

                try (PreparedStatement stmt = conn.prepareStatement("UPDATE person SET name = ?, age = ? WHERE cod = ?")) {
                    stmt.setString(1, newName);
                    stmt.setInt(2, newAge);
                    stmt.setInt(3, id);
                    stmt.executeUpdate();
                    conn.commit();
                    System.out.println("Person edited successfully.");
                } catch (SQLException e) {
                    conn.rollback();
                    System.out.println("Error editing person: " + e.getMessage());
                }
            } catch (SQLException e) {
                System.out.println("Error connecting to the database: " + e.getMessage());
            }
        } else {
            System.out.println("Person not found.");
        }
    }

    public static void deletePerson(String url) {
        Scanner scanner = new Scanner(System.in);
        System.out.print("ID of the person to delete: ");
        int id = scanner.nextInt();

        if (personExists(url, id)) {
            try (Connection conn = DriverManager.getConnection(url)) {
                conn.setAutoCommit(false);

                try (PreparedStatement stmt = conn.prepareStatement("DELETE FROM person WHERE cod = ?")) {
                    stmt.setInt(1, id);
                    stmt.executeUpdate();
                    conn.commit();
                    System.out.println("Person deleted successfully.");
                } catch (SQLException e) {
                    conn.rollback();
                    System.out.println("Error deleting person: " + e.getMessage());
                }
            } catch (SQLException e) {
                System.out.println("Error connecting to the database: " + e.getMessage());
            }
        } else {
            System.out.println("Person not found.");
        }
    }

    public static boolean personExists(String url, int id) {
        try (Connection conn = DriverManager.getConnection(url);
             PreparedStatement stmt = conn.prepareStatement("SELECT 1 FROM person WHERE cod = ?")) {
            stmt.setInt(1, id);
            try (ResultSet rs = stmt.executeQuery()) {
                return rs.next();
            }
        } catch (SQLException e) {
            System.out.println("Error checking person existence: " + e.getMessage());
        }
        return false;
    }
}