LowStockChecker
Java + MySQL + JSON

LowStockChecker is a Java program you write and compile on your Windows desktop using IntelliJ or Eclipse, then copy the finished compiled file to your Ubuntu LAMP server where PHP can run it automatically. Think of it as a small worker your website calls behind the scenes to check inventory levels.

The Three Moving Parts — Explained Simply

• JDBC (Java Database Connectivity) — Java's built-in database plug socket. Already inside your JDK — you do not install it. It defines how Java talks to any database, but it needs a specific plug for each database type.
• mysql-connector-j — the MySQL-specific plug that fits into JDBC's socket. A library file from Oracle that teaches Java how to speak MySQL's language. Without it, Java sees a MySQL address and has absolutely no idea what to do with it.
• org.json — a small helper library that lets you build JSON output easily. Instead of manually building a string like {"id":1,"name":"Widget"} and worrying about every quote and comma, you just call item.put("name", "Widget") and it handles all the formatting for you.

The Big Picture — Step by Step

1. Your PHP page on the Ubuntu LAMP server calls the Java program using a system command — exactly like typing a command in the terminal.
2. Java connects to the MySQL database on the server using the JDBC driver.
3. Java asks MySQL: "Give me every item in the inventory table where the quantity is less than 2."
4. Java takes each row MySQL returns and turns it into a neat JSON object.
5. Java prints the complete list as a JSON array — this is the output PHP will capture.
6. PHP reads that output, parses it as JSON, and can display it on your website, send an alert email, or do whatever you program it to do next.

What the Output Looks Like

[{"id":2,"name":"Sprocket B","qty":1},{"id":3,"name":"Gear C","qty":0}]

{"error":"Access denied for user 'projectuser'@'localhost'"}

Notice that even errors come out as JSON — not a scary Java crash message. This is intentional. If Java crashes and prints a raw error to the screen, PHP cannot parse it and you will have a very confusing bug to track down. By catching every error and wrapping it in JSON, PHP can always read the output and tell you exactly what went wrong.

If you need to create the database and table from scratch, SSH into your Ubuntu server and run the following. If you need a reminder on how to SSH in from Windows or Linux, refer back to Section 3 of the Ubuntu 24.04 LAMP Server guide.

-- Connect as root first: sudo mysql -u root -p

CREATE DATABASE IF NOT EXISTS project;
USE project;

CREATE USER IF NOT EXISTS 'projectuser'@'localhost'
  IDENTIFIED BY 'yourpassword';

GRANT ALL PRIVILEGES ON project.*
  TO 'projectuser'@'localhost';
FLUSH PRIVILEGES;

CREATE TABLE IF NOT EXISTS inventory (
  id         INT           AUTO_INCREMENT PRIMARY KEY,
  part_name  VARCHAR(255) NOT NULL,
  quantity   INT           NOT NULL DEFAULT 0
);

INSERT INTO inventory (part_name, quantity) VALUES
  ('Widget A',    5),
  ('Sprocket B',  1),
  ('Gear C',      0),
  ('Bearing D',  12),
  ('Connector E', 1);

SELECT * FROM inventory;
EXIT;

This guide uses IntelliJ IDEA Community Edition 2023.3.8 on Windows 11. Community Edition is completely free and has everything you need for this project.

Download & Install

1. Go to jetbrains.com/idea/download in your browser. Scroll down to the Community Edition column (the free one — not Ultimate). Click Download.
2. Run the downloaded installer (.exe). Click Next through the installer. On the Installation Options screen, check "Add 'bin' folder to the PATH" and "Add 'Open Folder as Project'". Leave everything else as default.
3. Click Install. When it finishes, check "Run IntelliJ IDEA" and click Finish.
4. IntelliJ opens. You will see the Welcome Screen — a dark window with "Welcome to IntelliJ IDEA" and buttons for New Project, Open, and Get from VCS. You are in the right place.

JDK Check — IntelliJ Includes One

IntelliJ 2023.3.8 bundles JDK 17.0.12 from JetBrains — this is the Runtime version shown in your About screen. You do not need to install a separate JDK for this project. IntelliJ will offer to download and use a JDK automatically when you create a new project.

Maven is a build tool that automatically downloads your dependencies (the two JARs you need) and compiles your project. IntelliJ has Maven built in — you do not need to install it separately.

1. On the Welcome Screen, click New Project. A dialog box opens titled "New Project".
2. In the left panel of the dialog, click New Project (not "Maven Archetype" — just the plain "New Project" option at the top).
3. Fill in the fields on the right side:

   • Name: LowStockChecker
   • Location: Leave as default, or choose a folder you prefer
   • Language: Java
   • Build system: Maven ← this is critical, make sure Maven is selected, not Gradle or IntelliJ
   • JDK: Select 17 from the dropdown. If none is listed, click the dropdown and choose Download JDK — select version 17 from JetBrains or Eclipse Temurin and click Download.
   • Add sample code: Leave this unchecked
4. Click Create. IntelliJ creates the project and opens it. You will see the Project tool window on the left side showing the folder structure.

Understanding the Project Structure

LowStockChecker/
├── pom.xml                        ← Maven build file — you will edit this
└── src/
    └── main/
        └── java/                  ← Your Java source files go here
    └── test/
        └── java/                  ← Test files (ignore for now)

The pom.xml file tells Maven which libraries to download and how to build your project. IntelliJ created a basic one — you need to replace its contents completely.

1. In the Project tool window on the left, find pom.xml at the top level of the project (not inside src). Double-click it to open it in the editor.
2. You will see a minimal XML file. Select all the text in the editor with Ctrl + A, then delete it.
3. Paste the following complete pom.xml content:

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
                             http://maven.apache.org/xsd/maven-4.0.0.xsd">

  <modelVersion>4.0.0</modelVersion>
  <groupId>com.yourproject</groupId>
  <artifactId>LowStockChecker</artifactId>
  <version>1.0</version>
  <packaging>jar</packaging>

  <properties>
    <maven.compiler.source>17</maven.compiler.source>
    <maven.compiler.target>17</maven.compiler.target>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
  </properties>

  <dependencies>
    <!-- Dependency 1: MySQL JDBC Driver -->
    <dependency>
      <groupId>com.mysql</groupId>
      <artifactId>mysql-connector-j</artifactId>
      <version>9.2.0</version>
    </dependency>
    <!-- Dependency 2: JSON Library -->
    <dependency>
      <groupId>org.json</groupId>
      <artifactId>json</artifactId>
      <version>20240303</version>
    </dependency>
  </dependencies>

  <build>
    <plugins>
      <!-- Shade Plugin: bundles ALL jars into one runnable fat JAR -->
      <plugin>
        <groupId>org.apache.maven.plugins</groupId>
        <artifactId>maven-shade-plugin</artifactId>
        <version>3.5.1</version>
        <executions>
          <execution>
            <phase>package</phase>
            <goals><goal>shade</goal></goals>
            <configuration>
              <transformers>
                <transformer
                  implementation="org.apache.maven.plugins.shade
                    .resource.ManifestResourceTransformer">
                  <mainClass>LowStockChecker</mainClass>
                </transformer>
              </transformers>
            </configuration>
          </execution>
        </executions>
      </plugin>
    </plugins>
  </build>

</project>

4. Save the file with Ctrl + S.
5. A small popup may appear saying "Load Maven Changes". Click it. IntelliJ will download the two dependency JARs from the internet. You will see a progress bar at the bottom — wait for it to finish.
6. When the download finishes, expand External Libraries in the Project tool window. You should see mysql-connector-j-9.2.0.jar and json-20240303.jar listed there. This confirms Maven downloaded them successfully.

1. In the Project tool window, expand: src → main → java.
2. Right-click on the java folder. A context menu appears.
3. Hover over New. Click Java Class.
4. Type exactly: LowStockChecker. Press Enter or double-click Class.
5. IntelliJ creates the file and opens it. Select all (Ctrl + A) and delete it.
6. Paste the complete source code below:

import java.sql.*;
import org.json.JSONArray;
import org.json.JSONObject;

public class LowStockChecker {

    public static void main(String[] args) {

        // Database connection settings — update these to match your environment
        String url  = "jdbc:mysql://localhost:3306/project";
        String user = "projectuser";
        String pass = "yourpassword";  // <-- change this

        JSONArray lowStock = new JSONArray();

        try (Connection conn = DriverManager.getConnection(url, user, pass)) {

            String sql = "SELECT id, part_name, quantity FROM inventory WHERE quantity < 2";
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                JSONObject item = new JSONObject();
                item.put("id",   rs.getInt("id"));
                item.put("name", rs.getString("part_name"));
                item.put("qty",  rs.getInt("quantity"));
                lowStock.put(item);
            }

        } catch (Exception e) {
            JSONObject err = new JSONObject();
            err.put("error", e.getMessage());
            System.out.println(err.toString());
            return;
        }

        System.out.println(lowStock.toString());
    }
}

7. Save with Ctrl + S.
8. Check the import lines — they should appear in normal text, not underlined in red. If org.json.JSONArray is red, Maven has not finished downloading. Wait and check the progress bar at the bottom.

Inside LowStockChecker.java, find these three lines near the top of main() and update them to match your actual MySQL setup:

String url  = "jdbc:mysql://localhost:3306/project";
//                         ^^^^^^^^^^ ^^^^ ^^^^^^^
//                         hostname  port  database name
String user = "projectuser";   // Your MySQL username
String pass = "yourpassword"; // Your MySQL password — change this!

• If MySQL is on the same machine as IntelliJ, localhost is correct.
• If MySQL is on your LAMP server (e.g., 192.168.1.50), replace localhost with that IP. Make sure UFW allows port 3306.
• The database name project must match what you created in the MySQL setup step.

1. Open the Maven tool window: View→Tool Windows→Maven.
2. Expand your project name → Lifecycle.
3. Double-click clean. Wait for BUILD SUCCESS.
4. Double-click package. Wait for BUILD SUCCESS.
5. Expand the target folder in the Project tool window. You will see:

   • LowStockChecker-1.0.jar — thin JAR (do not use this)
   • LowStockChecker-1.0-shaded.jar — fat JAR with all dependencies. Use this one.

Option A — Run Directly Inside IntelliJ

1. Open LowStockChecker.java in the editor.
2. Click the green triangle (▶) in the left gutter next to the public static void main line.
3. Click Run 'LowStockChecker.main()'.
4. The Run panel at the bottom shows the JSON output.

Option B — Run the JAR from IntelliJ's Terminal

Press Alt + F12 to open the built-in terminal, then run:

java -jar target\LowStockChecker-1.0-shaded.jar

Red underlines on import org.json.*

Maven has not downloaded the dependencies yet. Open the Maven tool window and click the refresh button. Make sure you have an internet connection.

BUILD FAILURE — "Could not find artifact"

The groupId, artifactId, or version in your pom.xml has a typo. Double-check that you used com.mysql (not mysql) and mysql-connector-j (not mysql-connector-java).

"Access denied" when running

The username or password in LowStockChecker.java does not match your MySQL user. Verify by running mysql -u projectuser -p in IntelliJ's terminal — if that fails, the credentials are wrong at the MySQL level.

Run button is greyed out / no green triangle

Make sure the file is saved inside src/main/java/ and that the class name (public class LowStockChecker) exactly matches the filename (LowStockChecker.java).

Maven tool window is empty

Go to File→Project Structure→Modules. If the module is missing, click + and import from the pom.xml.

Eclipse is a free, open-source IDE. You need the "Eclipse IDE for Java Developers" edition — not the Enterprise edition or the C/C++ edition.

Download & Install

1. Go to eclipse.org/downloads. Click Download x86_64 to download the Eclipse Installer.
2. Run the installer. Click "Eclipse IDE for Java Developers" at the top of the list.
3. Leave the install folder as default or change it. Click Install.
4. Accept the license agreement. The installer downloads and installs Eclipse.
5. When complete, click Launch.
6. Eclipse asks for a workspace folder. The default is fine. Click Launch.
7. Eclipse opens with a Welcome tab. Close it by clicking the X on its tab.

Verify the JDK in Eclipse

1. Go to Window→Preferences.
2. Expand Java → Installed JREs.
3. You should see a JDK 17 or newer listed. If not, click Add → Standard VM → Browse to your JDK folder. Click Finish then Apply and Close.

1. Go to File→New→Project....
2. Expand the Maven folder and select Maven Project. Click Next.
3. Check "Create a simple project (skip archetype selection)". Click Next.
4. Fill in: Group Id: com.yourproject  ·  Artifact Id: LowStockChecker  ·  Packaging: jar. Click Finish.

LowStockChecker/
├── pom.xml
└── src/
    ├── main/java/   ← Your Java source files go here
    └── test/java/   ← Test files (ignore for now)

1. In the Package Explorer, double-click pom.xml.
2. Click the pom.xml tab at the bottom of the editor to switch to raw XML view.
3. Select all (Ctrl + A), delete, and paste the same complete pom.xml content from section IJ·3 — it is identical for both IDEs.
4. Save with Ctrl + S.
5. Right-click the project → Maven → Update Project... → make sure your project is checked → click OK. Eclipse downloads the dependencies.
6. When complete, expand Maven Dependencies. You should see mysql-connector-j-9.2.0.jar and json-20240303.jar listed.

1. In the Package Explorer, expand your project → src/main/java.
2. Right-click on src/main/java → New → Class.
3. Set Package to blank (default package), Name to LowStockChecker, and check "public static void main(String[] args)". Click Finish.
4. Select all (Ctrl + A), delete, and paste the complete source code from section IJ·4 — identical for both IDEs.
5. Save with Ctrl + S. Import lines should show no red underlines.

Same as IJ·5 — find and update these three lines in LowStockChecker.java:

String url  = "jdbc:mysql://localhost:3306/project";
String user = "projectuser";
String pass = "yourpassword";  // <-- change this

Save with Ctrl + S after making changes.

1. Right-click the project → Run As → Maven build... (with the three dots).
2. In the Goals field, type: clean package. Click Run.
3. Wait for BUILD SUCCESS in the Console panel.
4. Press F5 to refresh. Expand the target folder:

   • LowStockChecker-1.0.jar — thin JAR, do not use
   • LowStockChecker-1.0-shaded.jar — fat JAR, use this one

Option A — Run Directly Inside Eclipse

1. Right-click LowStockChecker.java → Run As → Java Application.
2. The Console panel at the bottom shows the output.

Option B — Run the JAR from Eclipse's Terminal

java -jar target\LowStockChecker-1.0-shaded.jar

Red underlines on org.json imports

Right-click the project → Maven → Update Project. Check internet connection and pom.xml for syntax errors.

BUILD FAILURE — "Plugin not found" or "Artifact missing"

Most common mistake: using old artifact ID mysql:mysql-connector-java instead of correct com.mysql:mysql-connector-j.

Console shows nothing when running as Java Application

Click the white rectangle icon in the Console toolbar and choose the most recent LowStockChecker run. Verify MySQL is running.

"Could not find or load main class" when running the JAR

You are running the thin JAR. Use the file ending in -shaded.jar.

Project has a red X mark in Package Explorer

Go to Window→Show View→Problems to see the exact error — usually a pom.xml syntax error or missing JDK.

"Access denied for user" in the Console

Test the connection manually in Eclipse's terminal: mysql -u projectuser -p. Fix credentials in MySQL first, then update the Java source.

Everything from here on is done on your Ubuntu server via SSH and has nothing to do with which IDE you used to compile. Whether you used IntelliJ or Eclipse, the compiled output and the deployment steps are exactly the same.

Where the Files Live on the Server

The Three Files That Must Be in the /java/ Folder

/var/www/example.com/public_html/
├── java/
│   ├── LowStockChecker.class       ← compiled Java class file
│   ├── mysql-connector-j-9.2.0.jar ← MySQL JDBC driver
│   └── json-20240303.jar           ← org.json library
├── check_stock.php
└── index.php

Step 1 — Create the /java/ Folder on the Server

sudo mkdir -p /var/www/example.com/public_html/java
sudo chown www-data:www-data /var/www/example.com/public_html/java
sudo chmod 750 /var/www/example.com/public_html/java

Step 2 — Copy All Three Files from Windows to the Server

# 1. The compiled class file
scp target\classes\LowStockChecker.class ^
    yourusername@192.168.1.50:/var/www/example.com/public_html/java/

# 2. The MySQL JDBC driver JAR
scp %USERPROFILE%\.m2\repository\com\mysql\mysql-connector-j\9.2.0\^
mysql-connector-j-9.2.0.jar ^
    yourusername@192.168.1.50:/var/www/example.com/public_html/java/

# 3. The org.json JAR
scp %USERPROFILE%\.m2\repository\org\json\json\20240303\^
json-20240303.jar ^
    yourusername@192.168.1.50:/var/www/example.com/public_html/java/

Step 3 — Set Permissions and Install Java on the Server

sudo chown www-data:www-data /var/www/example.com/public_html/java/*
sudo chmod 640 /var/www/example.com/public_html/java/*

java --version
# If not installed:
sudo apt install -y openjdk-17-jre

ls -lh /var/www/example.com/public_html/java/

Step 4 — Test It Runs Correctly on the Server

cd /var/www/example.com/public_html/java
java -cp ".:mysql-connector-j-9.2.0.jar:json-20240303.jar" LowStockChecker
# Expected: [{"id":2,"name":"Sprocket B","qty":1},{"id":3,"name":"Gear C","qty":0}]

Step 5 — Call It from PHP

<?php
$javaDir = '/var/www/example.com/public_html/java';
$cmd = '/usr/bin/java -cp '
      . escapeshellarg($javaDir)
      . ':'
      . escapeshellarg($javaDir . '/mysql-connector-j-9.2.0.jar')
      . ':'
      . escapeshellarg($javaDir . '/json-20240303.jar')
      . ' LowStockChecker 2>&1';

$output = shell_exec($cmd);
$data   = json_decode($output, true);

if (isset($data['error'])) {
    echo 'Error: ' . htmlspecialchars($data['error']);
} else {
    foreach ($data as $item) {
        echo "Low stock: "
           . htmlspecialchars($item['name'])
           . " (qty: " . $item['qty'] . ")";
    }
}
?>

Options -Indexes
Require all denied