Java JDBC CRUD App with SQL Server

Build a product inventory app in Java using JDBC and SQL Server in Docker.

Introduction

30 Second Summary

Almost every application you use stores data somewhere. Whether it's a shopping cart, a user profile, or a product catalog, code needs to talk to a database.

In this project, you will build a Java console application that manages a product inventory in SQL Server. You'll connect to a database running in Docker, create tables, insert products, update prices, and delete items using pure Java and the standard JDBC library.

What You'll Build

You run one command and see your products printed in a formatted table in the terminal, with the power to add, modify, and remove items from a real SQL Server database running on your machine.

By the end of this project, you'll have:

  • A running SQL Server instance in Docker that you connected to from Java code.
  • A complete Java console application that performs Create, Read, Update, and Delete operations on a products table.
  • Firsthand experience with why PreparedStatement exists by watching string concatenation break on real data.
  • Secret Mission: Add a search feature that filters products by partial name match using SQL LIKE patterns.

Are there any prerequisites?

You should be comfortable with basic Java concepts like classes, methods, and compiling with javac. You'll also need Java, VSCodium, and Docker Desktop installed on your machine.

Before We Start

Before We Start...

Before we dive in, let's lock in what you're about to build and why it matters.

You're going to write a Java application that talks directly to a SQL Server database, performing real CRUD operations using the same driver pattern that every Java framework builds on top of.

Set Up Your Environment

Before you can write any database code, you need three pieces in place. A JDBC driver that lets Java speak to SQL Server, a running SQL Server instance to talk to, and a confirmed Java compiler to build your program.

In this step you will create a project folder, download the Microsoft JDBC Driver JAR, spin up SQL Server 2022 inside a Docker container, and confirm your Java tools are ready to go.

In this step, get ready to:
  • Create a project directory and download the JDBC driver JAR.
  • Start a SQL Server 2022 instance in Docker.
  • Verify your Java environment is ready to compile and run.
Create your project directory and download the JDBC driver

The Microsoft JDBC Driver is a single JAR file that teaches Java how to communicate with SQL Server. You will download it from Maven Central and keep it in your project folder so the Java compiler can find it later.

  • Move to your Desktop by running this command:
cd ~/Desktop
  • Create your project folder and move into it by running these commands:
mkdir jdbc-inventory
cd jdbc-inventory
  • Download the JDBC driver JAR by running this command:
curl -O https://repo1.maven.org/maven2/com/microsoft/sqlserver/mssql-jdbc/13.4.0.jre11/mssql-jdbc-13.4.0.jre11.jar

What does this command do?

The curl -O flag downloads the file and saves it with its original filename. The URL points to the official Maven Central repository where Microsoft publishes the JDBC driver.

The jre11 in the filename means the JAR requires Java 11 or higher. Your Java 26 exceeds that requirement.

  • Confirm the JAR downloaded by running:
ls mssql-jdbc-13.4.0.jre11.jar

You should see mssql-jdbc-13.4.0.jre11.jar printed back. If you see "No such file or directory", the download may have failed.

Don't see the JAR file?

  • Check your internet connection and run the curl command again.
  • Make sure you are inside the jdbc-inventory folder. Run pwd to check your current directory.

Why isn't my JDBC JAR downloading?

Pull and start the SQL Server Docker container

Docker lets you run SQL Server on your machine without installing it directly. You will pull the official Microsoft image and start a container that listens on port 1433 (SQL Server's default port).

  • Pull the SQL Server 2022 image by running this command:
docker pull mcr.microsoft.com/mssql/server:2022-latest

You will see Docker downloading several layers. This may take a minute or two depending on your internet speed.

  • Start the SQL Server container by running this command:
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourStrong!Pass1" \
  -p 1433:1433 --name sql1 --hostname sql1 \
  -d mcr.microsoft.com/mssql/server:2022-latest

What does this command do?

  • ACCEPT_EULA=Y accepts the SQL Server license agreement.
  • MSSQL_SA_PASSWORD sets the password for the sa (system administrator) account.
  • -p 1433:1433 maps the container's SQL Server port to the same port on your machine so Java can connect to localhost:1433.
  • -d runs the container in the background so you keep your terminal.

Docker prints a long container ID if the start succeeded. Before you move on, predict what you expect to see when you check whether it is actually running.

  • Verify the container is running by running:
docker ps

You should see a row for sql1 with STATUS showing "Up" and PORTS showing 0.0.0.0:1433->1433/tcp. That confirms SQL Server is alive and listening.

Container not showing as Up?

  • If STATUS shows "Exited", the container crashed on startup. Run docker logs sql1 to see the error.
  • If you see a "name already in use" error, a previous container with the same name exists. Remove it with docker rm -f sql1 and re-run the docker run command.
  • Make sure Docker Desktop is running. Look for the Docker whale icon in your menu bar (macOS) or system tray (Windows).

Why isn't my SQL Server Docker container starting?

Verify Java is ready

Your Java installation needs both the runtime (java) and the compiler (javac). A quick version check confirms both are available.

  • Check your Java runtime version by running:
java -version

You should see output mentioning version 26.

  • Check your Java compiler by running:
javac -version

You should see javac 26 printed. If both commands respond with version 26, your environment is fully ready.

Seeing command not found?

  • Confirm Java is installed and on your PATH. Try running which java to see where the binary lives.
  • If you recently installed Java, you may need to close and reopen your terminal for the PATH change to take effect.

Help me get Java working in my terminal.

Your environment is locked in. You have a JDBC driver ready, a database running, and a compiler waiting. Next up, you will write Java code that connects to SQL Server and proves the whole stack works.

Connect to SQL Server from Java

Your environment is ready. You have a running SQL Server container, the JDBC driver JAR on disk, and Java confirmed working.

Before you can build any CRUD logic, you need to prove that Java can actually reach SQL Server through the JDBC driver. This step produces the minimum viable connection. If you see "Connected!", everything is wired up correctly.

In this step, get ready to:
  • Write a Java class that connects to SQL Server using DriverManager.getConnection().
  • Compile and run the class with the JDBC driver JAR on the classpath.
Create InventoryApp.java with a connection test

A JDBC connection needs three things: a URL that tells the driver where the database lives, a username, and a password. You will store these as constants and wrap the connection logic in a reusable helper method.

  • Create a new file called InventoryApp.java in your jdbc-inventory folder by running:
touch InventoryApp.java
  • Confirm the file exists by running:
ls InventoryApp.java
  • Open InventoryApp.java in VSCodium and paste in the following code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class InventoryApp {

    // JDBC connection URL targeting SQL Server on localhost
    private static final String URL = "jdbc:sqlserver://localhost:1433;"
            + "databaseName=InventoryDB;"
            + "encrypt=true;"
            + "trustServerCertificate=true;";
    private static final String USER = "sa";
    private static final String PASSWORD = "YourStrong!Pass1";

    // Helper method that other methods will reuse
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void main(String[] args) {
        // try-with-resources auto-closes the connection when done
        try (Connection conn = getConnection()) {
            System.out.println("Connected to SQL Server!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

What does this code do?

  • URL builds a connection string that tells the JDBC driver to connect to localhost:1433 (where your Docker container is listening), use the InventoryDB database, and encrypt the connection with a trusted local certificate.
  • getConnection() is a reusable helper. Every method you write later will call this instead of repeating the URL, user, and password.
  • main() uses try-with-resources, which means the connection automatically closes when the block finishes. No manual conn.close() needed.
  • Save InventoryApp.java.
Compile and run with the JDBC JAR on the classpath

Java needs to know where the JDBC driver lives at both compile time and run time. You tell it by adding the JAR to the classpath with the -cp flag.

  • Compile your file by running:
javac InventoryApp.java

Before you run the program, predict what you expect to see. Will the connection succeed or will you see an error?

  • Run the program with the JDBC driver on the classpath:
java -cp ".:mssql-jdbc-13.4.0.jre11.jar" InventoryApp

You should see Connected to SQL Server! printed in your terminal. That single line proves your Java code successfully reached SQL Server through the JDBC driver.

Windows classpath separator

On Windows, the classpath separator is a semicolon instead of a colon. Use java -cp ".;mssql-jdbc-13.4.0.jre11.jar" InventoryApp if you are on Windows.

Seeing a connection error?

  • If you see a timeout or "connection refused" error, your SQL Server container might still be starting up. Wait 10-15 seconds and run the command again.
  • If you see "Login failed", double check that the password in your code matches YourStrong!Pass1 exactly (same capitalisation and symbols as the Docker container).
  • If you see "Cannot open database 'InventoryDB'", the database does not exist yet. This is expected. The next step creates it. For now, you can temporarily remove databaseName=InventoryDB; from the URL to verify the base connection works, then add it back.

Still stuck? Help me debug my JDBC connection to SQL Server

✔️ Awesome, I've got everything!

Great. Double check you have saved InventoryApp.java and confirmed the connection message in your terminal.

ⓧ I'd like to double check the full code

Here is the complete InventoryApp.java for this step:

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

public class InventoryApp {

    private static final String URL = "jdbc:sqlserver://localhost:1433;"
            + "databaseName=InventoryDB;"
            + "encrypt=true;"
            + "trustServerCertificate=true;";
    private static final String USER = "sa";
    private static final String PASSWORD = "YourStrong!Pass1";

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASSWORD);
    }

    public static void main(String[] args) {
        try (Connection conn = getConnection()) {
            System.out.println("Connected to SQL Server!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Your Java code can reach SQL Server. Next up, you will create a database, build a products table, and insert your first rows of data.

Create the Database, Table, and Insert Products

In the previous step, you proved that Java can talk to SQL Server through the JDBC driver. A connection alone is useless without a place to store data.

In this step, you will build the database schema and insert your first products. You will use string concatenation to build SQL statements. This naive approach works perfectly for simple data, but it sets up a real-world problem you will hit in the next step.

In this step, get ready to:
  • Add a method that creates the InventoryDB database on your SQL Server instance.
  • Add methods that create a products table and insert products using string concatenation.
  • Run the program and verify all four operations succeed.
Add a method to create the database

To create a new database, your code needs to connect to SQL Server without specifying a database name. This connects to the built-in master database, which has the permissions to create new databases.

  • In InventoryApp.java, find the import lines at the top of the file. Add this new import below the existing ones:
  • import java.sql.Statement;
  • Add the following createDatabase() method below the getConnection() method:
    public static void createDatabase() {
        // Connection URL without databaseName connects to master
        String masterUrl = "jdbc:sqlserver://localhost:1433;"
                + "encrypt=true;"
                + "trustServerCertificate=true;";
        try (Connection conn = DriverManager.getConnection(masterUrl, USER, PASSWORD);
             Statement stmt = conn.createStatement()) {
            // Only create the database if it doesn't already exist
            stmt.executeUpdate("IF DB_ID('InventoryDB') IS NULL CREATE DATABASE InventoryDB");
            System.out.println("Database 'InventoryDB' ready.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

What does this code do?

  • masterUrl omits the databaseName property so the connection goes to the default master database.
  • IF DB_ID('InventoryDB') IS NULL checks whether the database already exists before creating it. This makes the method safe to run multiple times.
  • The try-with-resources block auto-closes both the connection and statement when done.
  • In InventoryApp.java, find the current main method:
    public static void main(String[] args) {
        try (Connection conn = getConnection()) {
            System.out.println("Connected to SQL Server!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
  • Replace the entire main method with this version that calls createDatabase():
    public static void main(String[] args) {
        createDatabase();
    }
  • Save InventoryApp.java.
  • Compile and run the program by running this command:
javac InventoryApp.java && java -cp ".:mssql-jdbc-13.4.0.jre11.jar" InventoryApp

You should see Database 'InventoryDB' ready. printed in your terminal.

Windows classpath separator

On Windows, replace the colon with a semicolon in the classpath flag:

javac InventoryApp.java && java -cp ".;mssql-jdbc-13.4.0.jre11.jar" InventoryApp

Seeing a connection error?

  • Make sure your sql1 Docker container is still running. Check with docker ps.
  • If the container just restarted, wait 10-15 seconds for SQL Server to finish its startup sequence, then try again.
  • Check that import java.sql.Statement; is at the top of the file. Missing this import causes a compile error.

help me fix my SQL Server connection error in Java

Add the table creation and product insert methods

With the database in place, you need a table to hold your products. You will also write a method that inserts products by concatenating values directly into the SQL string.

  • Add the following createTable() method below createDatabase():
    public static void createTable() {
        // SQL to create products table only if it doesn't exist yet
        String sql = "IF NOT EXISTS (SELECT * FROM sys.tables WHERE name = 'products') "
                + "CREATE TABLE products ("
                + "id INT IDENTITY(1,1) PRIMARY KEY, "
                + "name NVARCHAR(100) NOT NULL, "
                + "price DECIMAL(10,2) NOT NULL, "
                + "quantity INT NOT NULL)";
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
            System.out.println("Table 'products' ready.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

What does this code do?

  • IF NOT EXISTS prevents errors if you run the program more than once.
  • INT IDENTITY(1,1) makes the id column auto-increment starting at 1.
  • This method uses getConnection() (not masterUrl) because it connects to InventoryDB, which now exists.
  • In your main method, find this line:
        createDatabase();
  • Add createTable(); on the line below it, so main now looks like this:
    public static void main(String[] args) {
        createDatabase();
        createTable();
    }
  • Save InventoryApp.java and compile and run:
javac InventoryApp.java && java -cp ".:mssql-jdbc-13.4.0.jre11.jar" InventoryApp

You should see both Database 'InventoryDB' ready. and Table 'products' ready. printed.

Seeing an error about the table?

  • Make sure createDatabase() is called before createTable() in main. The database must exist before you can create tables inside it.
  • Check that your createTable() method uses getConnection() (which includes databaseName=InventoryDB), not masterUrl.

help me debug my createTable method

Now add the method that inserts products into the table. This version builds the SQL by concatenating the product name directly into the string.

  • Add the following insertProduct() method below createTable():
    public static void insertProduct(String name, double price, int quantity) {
        // Build SQL by concatenating values directly into the string
        String sql = "INSERT INTO products (name, price, quantity) VALUES ('" + name + "', " + price + ", " + quantity + ")";
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement()) {
            stmt.executeUpdate(sql);
            System.out.println("Inserted: " + name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

What does this code do?

This method concatenates the product name directly into the SQL string with single quotes around it. For a product called "USB-C Hub", the final SQL becomes:

INSERT INTO products (name, price, quantity) VALUES ('USB-C Hub', 34.5, 50)

This works perfectly for simple names. In the next step, you will discover what happens when a product name contains a single quote character.

  • Update your main method to call insertProduct() for two products. Replace main with this final version:
    public static void main(String[] args) {
        createDatabase();
        createTable();
        insertProduct("Mechanical Keyboard", 89.99, 25);
        insertProduct("USB-C Hub", 34.50, 50);
    }

Before you compile and run, what do you think the output will look like? How many lines will print, and in what order?

  • Save InventoryApp.java and compile and run:
javac InventoryApp.java && java -cp ".:mssql-jdbc-13.4.0.jre11.jar" InventoryApp

You should see four lines of output:

Database 'InventoryDB' ready.
Table 'products' ready.
Inserted: Mechanical Keyboard
Inserted: USB-C Hub

Only seeing the database and table messages?

  • Check that your insertProduct() method uses getConnection() so it connects to InventoryDB where the products table lives.
  • Verify the SQL string has matching single quotes around the name value. A missing quote causes a syntax error that the catch block prints as a stack trace.
  • If you see a duplicate key error, the products from a previous run still exist. This is fine. Drop the table by deleting and re-creating the container: docker rm -f sql1 then re-run the docker run command from Step 1.

help me fix my insertProduct method

✔️ Awesome, I've got everything!

Great. Double check you have saved InventoryApp.java and that all four output lines printed successfully.

ⓧ I'd like to double check the full code

Your InventoryApp.java should now contain these sections in order. Scroll up and confirm each one is present:

  • Four imports: Connection, DriverManager, SQLException, Statement.
  • Class declaration with URL, USER, and PASSWORD constants.
  • getConnection() method returning a connection to InventoryDB.
  • createDatabase() method using masterUrl without databaseName.
  • createTable() method using getConnection() with the IF NOT EXISTS check.
  • insertProduct() method using string concatenation to build the INSERT SQL.
  • main() calling createDatabase(), createTable(), and two insertProduct() calls.

Your database, table, and two products are in place. In the next step, you will try to insert a product with a tricky name and discover exactly why string concatenation in SQL is a problem.

Fix SQL Injection with PreparedStatement

Your database and table are ready, and inserting products with simple names works perfectly. The current insertProduct() method builds SQL by pasting the product name directly into the query string.

That approach breaks the moment your data contains special characters. In this step, you will try to insert a product whose name contains an apostrophe, watch the SQL fail, and then fix it with PreparedStatement so your inserts handle any input safely.

In this step, get ready to:
  • Trigger a SQL syntax error by inserting a product with an apostrophe in its name.
  • Rewrite insertProduct() to use parameterized queries with PreparedStatement.
  • Verify all three products insert successfully.
Add a product with an apostrophe

Your insertProduct() method currently concatenates the product name directly into the SQL string. A name like "Mechanical Keyboard" works fine because it has no special SQL characters. But what about a product called "Chef's Knife"?

  • In InventoryApp.java, find the main method at the bottom of the file.
  • Find this line:
insertProduct("USB-C Hub", 34.50, 50);
  • Add this new line directly below it:
insertProduct("Chef's Knife", 45.00, 15);
  • Save InventoryApp.java.

Before you compile and run, what do you think will happen when Java tries to insert a product named "Chef's Knife" using string concatenation?

  • Compile and run your program:
javac InventoryApp.java && java -cp ".:mssql-jdbc-13.4.0.jre11.jar" InventoryApp

You see the first two products insert fine, then a SQLException for "Chef's Knife". The program prints a stack trace mentioning incorrect syntax.

Why did it break?

String concatenation produced this SQL:

INSERT INTO products (name, price, quantity) VALUES ('Chef's Knife', 45.0, 15)

SQL Server sees the string starting at the first ' and ending at 'Chef'. Then s Knife' is unexpected garbage that breaks the parser. This is the exact bug that leads to SQL injection vulnerabilities in production applications.

Rewrite insertProduct with PreparedStatement

PreparedStatement solves this by separating the SQL structure from the data values. You write the query with ? placeholders, then bind each value separately. The driver handles escaping, so apostrophes and special characters never corrupt the SQL.

  • At the top of InventoryApp.java, find your existing imports and add this line after import java.sql.DriverManager;:
import java.sql.PreparedStatement;
  • Now find the insertProduct method. Replace the entire method body with this version that uses parameterized queries:
public static void insertProduct(String name, double price, int quantity) {
    String sql = "INSERT INTO products (name, price, quantity) VALUES (?, ?, ?)";
    try (Connection conn = getConnection();
         PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, name);
        pstmt.setDouble(2, price);
        pstmt.setInt(3, quantity);
        pstmt.executeUpdate();
        System.out.println("Inserted: " + name);
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

What does this code do?

  • VALUES (?, ?, ?) uses question marks as placeholders instead of pasting values directly into the SQL string.
  • conn.prepareStatement(sql) sends the SQL template to the database, which parses it once and reuses the plan.
  • pstmt.setString(1, name) binds the first ? to the product name. The driver escapes special characters automatically.
  • pstmt.setDouble(2, price) and pstmt.setInt(3, quantity) bind the remaining values with proper type handling.
  • Save InventoryApp.java.

This time, do you expect "Chef's Knife" to insert successfully?

  • Compile and run your updated program:
javac InventoryApp.java && java -cp ".:mssql-jdbc-13.4.0.jre11.jar" InventoryApp

You should see all three inserts succeed with no errors:

Database 'InventoryDB' ready.
Table 'products' ready.
Inserted: Mechanical Keyboard
Inserted: USB-C Hub
Inserted: Chef's Knife

Still seeing a SQLException?

  • Make sure you replaced the entire insertProduct method, not just the SQL string. The method now uses PreparedStatement instead of Statement.
  • Check that import java.sql.PreparedStatement; is at the top of your file. Without it, the compiler will report an error before you can run.
  • Verify that the SQL string uses three ? placeholders and the setString, setDouble, setInt calls use indices 1, 2, and 3.

Ask for help: My insertProduct method still throws a SQLException after switching to PreparedStatement. What did I miss?

✔️ Awesome, I've got everything!

Great. Double check you have saved InventoryApp.java and all three inserts print without errors.

ⓧ I'd like to double check the full code

Your InventoryApp.java should have these sections in order. Scroll through your file and confirm each one is present:

  • Five imports: Connection, DriverManager, PreparedStatement, SQLException, Statement.
  • Class declaration with URL, USER, and PASSWORD constants.
  • getConnection() method.
  • createDatabase() method connecting to master and creating InventoryDB.
  • createTable() method creating the products table.
  • insertProduct() method using PreparedStatement with VALUES (?, ?, ?).
  • main() method calling createDatabase(), createTable(), then three insertProduct() calls for Mechanical Keyboard, USB-C Hub, and Chef's Knife.

You just experienced the exact bug that PreparedStatement was designed to prevent. Next up, you will add the remaining CRUD operations: querying all products, updating prices, and deleting items from the database.

Query, Update, and Delete Products

Your database now handles any product name safely, thanks to PreparedStatement. Inserting data is only one piece of the puzzle though.

A real inventory application needs to read products back, update prices when they change, and remove discontinued items. In this step, you will complete the full CRUD cycle by adding query, update, and delete operations.

In this step, get ready to:
  • Query all products and display them in a formatted table.
  • Update a product's price using PreparedStatement.
  • Delete a product and confirm it is gone from the database.
Add a getAllProducts method that queries and prints a formatted table

To read data back from SQL Server, you use a ResultSet. This object holds the rows returned by a SELECT query and lets you iterate through them one at a time.

  • In InventoryApp.java, find your import statements at the top of the file.
  • Add this new import below the existing ones:
import java.sql.ResultSet;
  • Now add the following getAllProducts() method below your insertProduct() method:
    public static void getAllProducts() {
        String sql = "SELECT id, name, price, quantity FROM products";
        try (Connection conn = getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            System.out.println("\n--- All Products ---");
            System.out.printf("%-4s %-25s %-10s %-8s%n", "ID", "Name", "Price", "Qty");
            System.out.println("-".repeat(50));
            while (rs.next()) {
                System.out.printf("%-4d %-25s $%-9.2f %-8d%n",
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getDouble("price"),
                        rs.getInt("quantity"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

What does this code do?

  • Unlike inserts and updates, a SELECT query returns data. stmt.executeQuery(sql) returns a ResultSet you can loop through.
  • rs.next() advances the cursor to the next row. When no rows remain, it returns false and the loop ends.
  • printf with width specifiers like %-25s pads each column to a fixed width so the output lines up neatly.
  • In your main method, find the last insertProduct() call. Below it, add:
        getAllProducts();
  • Save InventoryApp.java.
  • Compile and run by entering these commands in your terminal:
javac InventoryApp.java && java -cp ".:mssql-jdbc-13.4.0.jre11.jar" InventoryApp

You should see the database and table messages, three "Inserted:" lines, and then a formatted table showing all three products with their IDs, names, prices, and quantities.

Seeing a compilation error?

  • Check that you added import java.sql.ResultSet; at the top of the file. Without it, the compiler cannot find the ResultSet type.
  • Make sure getAllProducts() is placed outside any other method. It should be at the same indentation level as insertProduct().
  • help me fix a ResultSet compilation error in my JDBC code
Add updateProductPrice and deleteProduct methods

Reading data is one thing. Changing and removing it completes the picture. Both operations use PreparedStatement for safety, just like your insert method.

  • Add the following updateProductPrice() method below getAllProducts():
    public static void updateProductPrice(String name, double newPrice) {
        String sql = "UPDATE products SET price = ? WHERE name = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setDouble(1, newPrice);
            pstmt.setString(2, name);
            int rows = pstmt.executeUpdate();
            System.out.println("Updated " + rows + " row(s) for: " + name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

What does this code do?

  • The ? placeholders bind to the price and name in that order. Order matters because setDouble(1, newPrice) fills the first ? and setString(2, name) fills the second.
  • executeUpdate() returns the number of rows affected. Printing it confirms that the UPDATE actually hit a row.
  • Add the following deleteProduct() method below updateProductPrice():
    public static void deleteProduct(String name) {
        String sql = "DELETE FROM products WHERE name = ?";
        try (Connection conn = getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setString(1, name);
            int rows = pstmt.executeUpdate();
            System.out.println("Deleted " + rows + " row(s) for: " + name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

What does this code do?

The DELETE follows the same PreparedStatement pattern. You bind the product name to the ? placeholder. The returned row count confirms something was actually removed.

  • Now update your main method to exercise both new operations. Find the getAllProducts(); call you added in the previous substep. Below it, add these lines:
        updateProductPrice("USB-C Hub", 29.99);
        System.out.println("\nAfter price update:");
        getAllProducts();

        deleteProduct("Mechanical Keyboard");
        System.out.println("\nAfter deletion:");
        getAllProducts();

Why print the table multiple times?

Each getAllProducts() call acts as a checkpoint. You see the state before the update, after the update, and after the deletion. This makes the effect of each operation visible.

  • Save InventoryApp.java.
Run the final program and observe the full CRUD lifecycle

Your file now has every CRUD operation wired up. Before you run it, think about what you expect to see in the terminal.

  • What do you think the final product listing will show? How many products will remain, and at what prices?
  • Compile and run the full program:
javac InventoryApp.java && java -cp ".:mssql-jdbc-13.4.0.jre11.jar" InventoryApp

You should see the complete CRUD lifecycle unfold in order: database created, table created, three products inserted, the full listing, a price update confirmation, the updated listing showing USB-C Hub at $29.99, a delete confirmation, and a final listing with only two products remaining.

What should I see?

  • The first table shows all three products at their original prices.
  • After the update, USB-C Hub's price changes from $34.50 to $29.99.
  • After the deletion, Mechanical Keyboard disappears. Only USB-C Hub and Chef's Knife remain.

Seeing duplicate products in the table?

If you ran the program multiple times, the inserts added new rows each time. The IF NOT EXISTS guard only prevents recreating the table, not re-inserting rows.

Stop and remove the container, then start a fresh one to reset the database:

docker rm -f sql1
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=YourStrong!Pass1" \
  -p 1433:1433 --name sql1 --hostname sql1 \
  -d mcr.microsoft.com/mssql/server:2022-latest

Wait 10-15 seconds for SQL Server to start, then rerun your program.

help me fix duplicate rows in my JDBC inventory app

✔️ Awesome, I've got everything!

Great. Double check you have saved your file before moving on.

ⓧ I'd like to double check the full code

Scroll up and confirm each section is present in your file, in this order:

  • Imports: Connection, DriverManager, PreparedStatement, ResultSet, SQLException, Statement.
  • Constants: URL, USER, PASSWORD.
  • Methods in order: getConnection(), createDatabase(), createTable(), insertProduct(), getAllProducts(), updateProductPrice(), deleteProduct().
  • main() calls: createDatabase, createTable, three inserts, getAllProducts, updateProductPrice, print + getAllProducts, deleteProduct, print + getAllProducts.

You now have a fully working CRUD application. One command and you can see your entire product inventory, change prices, and remove items. That is the complete lifecycle every database application needs.

Secret mission

Add Product Search with LIKE

Your inventory app can create, read, update, and delete products. But what if you have hundreds of items and need to find one by partial name? Real inventory systems need search. Add a search feature that filters products using SQL LIKE patterns.

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely locally using Docker, so there are no ongoing monetary costs.

Resources you used:

  • Docker container named sql1 running SQL Server 2022 (uses approximately 1.5 GB RAM while running).
  • Docker image mcr.microsoft.com/mssql/server:2022-latest (approximately 1.5 GB disk space).
  • Local project folder jdbc-inventory on your Desktop containing InventoryApp.java and the JDBC driver JAR.

Keep everything running

No action needed. Choose this if you are still actively experimenting with JDBC queries or want to add more features to your inventory app.

  • Your sql1 container stays running and uses approximately 1.5 GB of RAM.
  • You can recompile and run InventoryApp.java at any time.
  • There is no monetary cost for keeping things running.

Pause - I'll come back to this later

Shut down the SQL Server container to free up RAM. Your data and container configuration are preserved for next time.

  • Stop the container by running:
  • When you are ready to resume, start it again by running:
docker stop sql1

When you come back, restart the container with:

docker start sql1

Your InventoryDB database and products table will still be there.

Delete - I don't want to use this again

Remove all project resources and reclaim disk space. This cannot be undone.

  • Remove the Docker container by running:
docker rm -f sql1
  • Remove the SQL Server Docker image to reclaim approximately 1.5 GB of disk space:
docker rmi mcr.microsoft.com/mssql/server:2022-latest
  • Delete the project folder from your Desktop:
rm -rf ~/Desktop/jdbc-inventory

On Windows, use this command instead:

rmdir /s /q %USERPROFILE%\Desktop\jdbc-inventory

Nice Work!

Nice Work!

Nice work! You just built a complete database-backed Java application from scratch, connected to SQL Server running in Docker, and proved you can handle real-world data safely.

You've learned how to:

  • Connect to SQL Server from Java using the Microsoft JDBC Driver and standard java.sql APIs.
  • Experience firsthand why string concatenation in SQL is dangerous by hitting the apostrophe bug, then fix it with PreparedStatement parameterized queries.
  • Build a complete CRUD application with Create, Read, Update, and Delete operations on a products table.
  • Secret Mission: Add search functionality using SQL LIKE patterns with PreparedStatement wildcards.

Ready to quiz yourself?