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.
- Create, read, update, delete of products’ all information (name, brand, type, description, price, in date, out date, image)
- Find products by SQL Query (detail in below)
- Find products by stored procedures (detail in below)
- Login, register, credential check
- Edit own user info
- View, create feedback by clerks and reports by manager
- Item in date, out date management
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: 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.
Procedure: CREATE PROCEDURE `ListByBrand`(IN `Namee` VARCHAR(50)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER select * from `products` where `brand` =Namee
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
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
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
Example query: Huawei products in 5000-1000TL price stored in 2019-2020 and stored out in 2020-2021
Example query: Apple products with max price 30000TL
Example query: Huawei phones with 2020-2022 indate
Example stored procedure: Huawei phones
Example stored procedure: Huawei branded “Huawei Matebook 14” named and its price between 5000-10000TL laptops
Example stored procedure: Apple tablets priced between 3000-10000₺
Stored procedures in phpMyAdmin which created by my program: