Retrieving and Manipulating Data

Coding Pirate

Ahoy there, mateys! Today, we embark on a thrilling adventure, diving into the treasure troves of data stored in our databases. We’ll be learning how to retrieve and manipulate the glittering gold nuggets of information using Java. So hoist the Jolly Roger, and let’s set sail!

Discovering the Treasure: Establishing a Connection

Before we can plunder the riches of the database, we need to establish a connection. Much like a hidden pirate cove, we’ll use the JDBC (Java Database Connectivity) library to navigate our way safely to our data treasures.

First, you’ll want to add the JDBC driver to your project’s dependencies. Then, use the following code to connect to your database:

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

public class DatabaseConnection {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/treasure_map";
        String user = "Captain";
        String password = "AhoyMatey123";

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            System.out.println("Successfully connected to the database!");
        } catch (SQLException e) {
            System.out.println("Failed to connect to the database.");
            e.printStackTrace();
        }
    }
}

X Marks the Spot: Executing SQL Queries

Now that we’ve found the treasure map (our database), we need to locate the X that marks the spot. To do this, we’ll execute SQL queries using Java to retrieve and manipulate the data.

For instance, let’s say we want to find all the pirates with a bounty on their heads. We’d use the following code:

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

public class WantedPirates {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/treasure_map";
        String user = "Captain";
        String password = "AhoyMatey123";

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT * FROM pirates WHERE bounty > 0");

            while (resultSet.next()) {
                String name = resultSet.getString("name");
                int bounty = resultSet.getInt("bounty");
                System.out.println(name + " has a bounty of " + bounty + " doubloons.");
            }
        } catch (SQLException e) {
            System.out.println("Failed to retrieve wanted pirates.");
            e.printStackTrace();
        }
    }
}

Updating the Captain’s Log: Modifying Data

While exploring the high seas of data, you might want to modify some of the treasures you find. For example, let’s say you want to increase the bounty of a particularly nefarious pirate:

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

public class UpdateBounty {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/treasure_map";
        String user = "Captain";
        String password = "AhoyMatey123";
        String pirateName = "Blackbeard";
        int newBounty = 5000;

        try (Connection connection = DriverManager.getConnection(url, user, password)) {
            PreparedStatement statement = connection.prepareStatement("UPDATE pirates SET bounty = ? WHERE name = ?");
            statement.setInt(1, newBounty);
            statement.setString(2, pirateName);
            int rowsUpdated = statement.executeUpdate();

            if (rowsUpdated > 0) {
                System.out.println("Successfully updated " + pirateName + "'s bounty to " + newBounty + " doubloons.");
            } else {
                System.out.println("Failed to update " + pirateName + "'s bounty.");
            }
        } catch (SQLException e) {
            System.out.println("An error occurred while updating the bounty.");
            e.printStackTrace();
        }
    }
}

With this knowledge in your treasure chest, you’re ready to retrieve and manipulate the hidden gems of data in your database. Remember, a savvy pirate is always learning, so continue to explore the vast ocean of Java programming and discover even more valuable skills!

So, me hearties, as we set sail for new horizons, don’t forget the lessons we’ve learned from this adventure. Use your Java skills to navigate the treacherous waters of database interaction, and may fair winds always be at your back!