Alright, let's get into JDBC. I've got a local H2 database running for a small project I'm working on—a vinyl record inventory tracker. I've already got a table called records with columns for id, artist, album_title, and release_year. Now I need to actually get that data into my Java app.

Wait, how do I actually talk to the DB?

My first instinct is usually to just "connect," but Java doesn't just magically know where my database is or what language it speaks. I need a bridge. That's where the JDBC Driver comes in. Since I'm using H2, I've added the dependency to my project, but now I need to establish a Connection.

Connection conn = DriverManager.getConnection("jdbc:h2:mem:vinyl_db", "sa", "");
System.out.println("Connected!");

I tried running this, and it immediately complained about a SQLException. Java forces us to handle this because, in the real world, databases go down, passwords expire, or the network hiccups. I could wrap it in a try-catch, but there's a bigger problem here: if I just open a connection and leave it, I'm leaking resources. Eventually, the database will refuse new connections because I've left too many "ghost" sessions open. I'll use a try-with-resources block instead; it handles the closing for me automatically.

The "Empty" Result Set

Now that I'm connected, I want to see my records. I'll create a Statement and run a basic query. I'll try to just print the result directly to see what happens:

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:vinyl_db", "sa", "");
     Statement stmt = conn.createStatement()) {
    
    ResultSet rs = stmt.executeQuery("SELECT * FROM records");
    System.out.println(rs); 
}

Of course, that didn't work. It printed something like org.h2.jdbc.ResultSet@4fca21. It's an object, not a list of strings. I remember the first time I did this; I expected a List or an Array. But JDBC uses a ResultSet, which is more like a cursor pointing to a row in the database.

Here is the tricky part: when the ResultSet is first returned, the cursor is positioned before the first row. If I try to call rs.getString("artist") immediately, it'll throw an error because I'm not actually pointing at a record yet.

Walking through the rows

To actually get the data, I have to call .next(). This moves the cursor forward one row and returns true if there's actually data there. If it hits the end of the table, it returns false. This makes a while loop the perfect tool for the job.

try (Connection conn = DriverManager.getConnection("jdbc:h2:mem:vinyl_db", "sa", "");
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM records")) {

    while (rs.next()) {
        // Now the cursor is on a valid row
        int id = rs.getInt("id");
        String artist = rs.getString("artist");
        String album = rs.getString("album_title");
        int year = rs.getInt("release_year");
        
        System.out.printf("ID: %d | %s - %s (%d)%n", id, artist, album, year);
    }
} catch (SQLException e) {
    e.printStackTrace();
}

This works. I'm manually mapping the database columns to Java variables. It's a bit tedious, and if I change a column name in the DB, this code breaks. In a larger project, I'd map these straight into a VinylRecord POJO, but for the fundamentals, this is the core loop of JDBC: Connect $\rightarrow$ Statement $\rightarrow$ ResultSet $\rightarrow$ Iterate.

A quick note on the "Statement"

I used a Statement here, which is fine for a hardcoded "SELECT * FROM records". But if I wanted to search for a specific artist using a variable, I'd be tempted to do something like "SELECT * FROM records WHERE artist = '" + userInput + "'". Please, don't do that. That's how you get hit with SQL Injection attacks. For anything involving variables, we'll use PreparedStatement, but we'll save that for the next lesson once you're comfortable with this basic flow.

📋 Practical Task

Exercise: Vinyl Collection Record Fetcher

You are tasked with writing a small utility class to verify the connection to a database. Your goal is to create a method that retrieves and prints all albums released after a specific year from a database.

Requirements:

• Create a class named VinylDatabaseManager.
• Implement a method public void printRecentAlbums(int yearCutoff).
• Use a try-with-resources block to manage the Connection, Statement, and ResultSet.
• The SQL query should be: "SELECT artist, album_title FROM records WHERE release_year > " + yearCutoff.
• Inside the while(rs.next()) loop, print the artist and album title in the format: "Artist: [artist], Album: [album]".
• Assume the connection string is "jdbc:h2:mem:vinyl_db" with username "sa" and an empty password.

Note: Since you don't have a live H2 server running in this editor, focus on the correct JDBC syntax and resource management. Ensure your SQLException is handled via a try-catch block surrounding the resource block.