Introduction: In January 2021 I wrote this program for my project assignment. Inventory Management Program written in JAVA (used Netbeans 12.2) works on Windows. It has a search window for executing SQL queries to find results (output) of desired input for example “lenovo laptops between 6000-8000TL and in date after 2019” with CRUD (create-read-update-delete the inventory products), reporting features and 3 user types all in user friendly, easy to use GUI.

Inventory Management Program written in JAVA with Netbeans 12.2 works on Windows. It has a advanced search window for executing SQL queries or with stored procedures from DB (there is a option to select the search type query or stored procedure) to find results (output) of desired input for example “lenovo laptops between 6000-8000TL and in date after 2019” like a shopping website (Trendyol, Sahibinden) filter with CRUD (create-read-update-delete the inventory products), reporting features and 3 user types all in user friendly, easy to use GUI. Events occurring on the application are persistent.

The “INVENTORY MANAGEMENT SYSTEM” is the Java Based system works on Windows, designed for a production sector, which gives information related to the clerks and managers of the company with respect to its product launches. This product develops a system that can be used by the company inventory management to keep track of the sales, managers and its clerks. In the existing method of tracking of all the details is tedious and time consuming. Any product survey and launching of the area carried out manually by representatives, which is a time taking task. The automation system fulfills different requirements of clerks of the company. The specific purpose of the system is to automate the communication between clerks, Admin and the managers of the organization.

Features

  1. Create, read, update, delete of products’ all information (name, brand, type, description, price, in date, out date, image)
  2. Find products by SQL Query (detail in below)
  3. Find products by stored procedures (detail in below)
  4. Login, register, credential check
  5. Edit own user info
  6. View, create feedback by clerks and reports by manager
  7. Item in date, out date management

Detailed Explanation of Finding Item with SQL Query or Stored Procedure

Find products by SQL Query

Find products by SQL Query, works for all combinations of fields (like brand+min price, in date+out date+type etc.) including dates. Search as “LIKE”. If user search “hua” in name it can show products named Huawei

The query that executed: SELECT * FROM `products` WHERE `name` LIKE ‘%Huawei%’ AND `brand` LIKE ‘%Huawei%’ AND `description` LIKE ‘%%’  AND `type` LIKE ‘%Laptop%’AND `price` BETWEEN 6000 AND 10000 AND `in_date` BETWEEN ‘2019-01-01’ AND ‘2021-01-10’ AND `out_date` BETWEEN ‘2019-01-01’ AND ‘2022-01-01’

It’s getting the texts from textfield inputs then assigns of my variables then build the query then execute, show the result in jTable.

Find products by stored procedures

Find products by stored procedures: Drop & create the parametered stored procedure in DB and call with desired parameters works for (it hides other fields when select this option):

  • Exact name, brand, type, min price, max price, between price
  • Brand and type
  • Brand, type and between price
  • Brand, type, name and between price

Due to problem on the server, it didn’t accept LIKE ‘%’ + parameter + ‘%’ I had to use search as exact equal “=”.

There are 8 stored procedures, I added if else’s to check entered input combinations:

Stored procedures created and called by my Java program are;

  • Single search: ListByBrand/Type/MinPrice/MaxPrice/Name

Below, ListByBrand can be ListByType, ListByMinPrice, ListByMaxPrice, ListByName too.

CALL `ListByBrand`(‘Apple’);

Procedure: CREATE PROCEDURE `ListByBrand`(IN `Namee` VARCHAR(50)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER select * from `products` where `brand` =Namee

  • ListByBrandTypePrice

CALL `ListByBrandTypePrice`(‘Apple’,’Laptop’,’5000′,’20000′);

Procedure: CREATE PROCEDURE `ListByBrandTypePrice`(IN `Birand` VARCHAR(50), IN `Taype` VARCHAR(50), IN `Pricemin` INT(10), IN `Pricemax` INT(10)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER select * from `products` where `brand` = Birand AND `type` = Taype AND `price` BETWEEN Pricemin AND Pricemax

  • ListByBrandTypePriceName

CALL `ListByBrandTypePriceName`(‘Huawei’,’Laptop’,’Huawei Matebook 14′,’6000′,’10000′);

Procedure: CREATE PROCEDURE `ListByBrandTypePriceName`(IN `Birand` VARCHAR(50), IN `Taype` VARCHAR(50), IN `Naame` VARCHAR(50), IN `Pricemin` INT(10), IN `Pricemax` INT(10)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER select * from `products` where `brand` = Birand AND `type` = Taype AND `name` = Naame AND `price` BETWEEN Pricemin AND Pricemax

Video Demonstration

Screenshots of Find Function

I only added screenshots of find function, there are lots of other functions related with SQL like login/register, create/read/delete/update products and users, send report etc. This feature is unique and most powerful part of my project.

Main Screen Of Find Feature

image

Example query: Huawei products in 5000-1000TL price stored in 2019-2020 and stored out in 2020-2021

image

Example query: Apple products with max price 30000TL

image

Example query: Huawei phones with 2020-2022 indate

image

Example stored procedure: Huawei phones

image

Example stored procedure: Huawei branded “Huawei Matebook 14” named and its price between 5000-10000TL laptops

image

Example stored procedure: Apple tablets priced between 3000-10000₺

image

Stored procedures in phpMyAdmin which created by my program:

image

image

You must be logged in to post a comment.
Menu