So I want to fix an issue with the jsp page. As you may see this form is for registering a new product into database. You add some stuff like ID of product, some category of a product(if it is electronic part, etc), and some other stuff. My problem is selecting categories from the dropdown menu.. To be more specific, I add a product in the form and when I create a new category I don't have any problems, but when I add a product and use the dropdown menu(when I have an already existed category) the category field in the db is blank. I have added some extra code just in case that the problem is connected with more than the jsp file. I will explain it to you with some examples to be more clear. In the first screenshot I add a product and then I create a category. In the second one I create a product and then I select on of the category I want. The last one is the database with all of the saved products.
index.jsp
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@page import="java.sql.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<script src="JsBarcode.all.min.js"></script>
<link rel="icon" type="image/png" href="6077105.png">
<title>Storage Room</title>
<style type="text/css">
body {
font-family: system-ui, -apple-system, BlinkMacSystemFont, Segoe UI, Roboto, Oxygen, Ubuntu, Cantarell, Fira Sans, Droid Sans, Helvetica Neue, Segoe UI Emoji, Apple Color Emoji, Noto Color Emoji, sans-serif;
background-color: #202b38;
color: white;
margin: 0; /* Remove default margin to ensure full width */
}
.Title {
font-size: 10px;
}
a:link, visited {
color: white;
}
a:hover {
color: hotpink;
}
#navlist a {
margin-right: 20px; /* Adjust the spacing between navlist items */
}
.form-inline {
display: flex;
flex-wrap: wrap;
align-items: center;
}
.form-group {
margin-right: 20px;
margin-bottom: 10px;
}
.form-group label {
margin-right: 5px;
}
.form-group input {
width: 150px; /* Adjust the input width as needed */
}
.btn {
margin-top: 10px;
}
</style>
</head>
<body>
<div class="Title"><h1>Προσθήκη Προιόντος</h1></div>
<div id="navlist" class="navlist">
<a href='homepage.html'>Αρχίκη Σελίδα</a>
</div>
<br>
<div class="products">
<form name="products" action="SaveServlet" method="post" onsubmit="addNewCategory()" class="form-inline">
<div class="form-group">
<label for="ID">ID προιόντος:</label>
<input type="number" name="prod_barid" id="prod_barid" required>
</div>
<div class="form-group">
<label for="Category">Κατηγορία:</label>
<select name="category" id="prod_category" onchange="checkNewCategory(this)">
<option value="">Δεν υπάρχει κατηγορία...</option>
<%
String dbDriver = "org.mariadb.jdbc.Driver";
String dbURL = "jdbc:mariadb://localhost:3306/BARCODEPROD";
String dbUser = "adminB";
String dbPass = "passwordAdmin";
Connection con = null;
try {
Class.forName(dbDriver);
con = DriverManager.getConnection(dbURL, dbUser, dbPass);
String query = "SELECT DISTINCT prod_category FROM products";
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(query);
while (rs.next()) {
String category = rs.getString("prod_category");
%>
<option value="<%= category %>"><%= category %></option>
<%
}
rs.close();
st.close();
con.close();
} catch(Exception ex) {
ex.printStackTrace();
}
%>
</select>
</div>
<div class="form-group">
<label for="Product">Προιόν:</label>
<input type="text" name="product_name" id="prod_name" required>
</div>
<div class="form-group">
<label for="pcs">Ποσότητα:</label>
<input type="number" name="pcs" id="pcs" required>
</div>
<div class="form-group">
<label for="price">Τιμή:</label>
<input type="number" name="price" id="price" step="any">
</div>
<div class="form-group">
<label for="Description">Παρατηρήσεις:</label>
<input type="text" name="prod_description" id="prod_description">
</div>
<div class="form-group">
<label for="newCategory">Νέα κατηγορία:</label>
<input type="text" name="prod_category" id="prod_category">
</div>
<button id="submit_btn" type="submit">Προσθήκη</button>
<svg id="barcode"></svg>
<button id="clear_all_btn" type="reset">Εκαθάριση στοιχείων</button>
</form>
</div>
</body>
</html>
SaveServlet.java
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLIntegrityConstraintViolationException;
import java.util.Calendar;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;
import jakarta.servlet.http.HttpSession;
@WebServlet("/SaveServlet")
public class SaveServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
@Override
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
int status = 0;
response.setContentType("text/html");
PrintWriter out = response.getWriter();
String name = request.getParameter("product_name");
String baridParam = request.getParameter("prod_barid");
long barid = Long.parseLong(baridParam);
String desc = request.getParameter("prod_description");
String pcsParam = request.getParameter("pcs");
int pcs = Integer.parseInt(pcsParam);
String priceParam = request.getParameter("price");
double price = Double.parseDouble(priceParam);
String category = request.getParameter("prod_category");
String newCategory = request.getParameter("new_prod_category");
// If a new category is provided, use it; otherwise, use the selected category
String finalCategory = (newCategory != null && !newCategory.isEmpty()) ? newCategory : category;
// Validate and handle the Date field
Date date = getCurrentDate();
// Check if the item with the same name or barcode exists in the database
boolean itemExists = checkItemExists(name, baridParam);
if (itemExists) {
out.println("<!DOCTYPE html>");
out.println("<html><head>");
out.println("<meta charset=\"UTF-8\">");
out.println("<link rel=\"stylesheet\" href=\"https://cdn.jsdelivr.net/npm/water.css@2/out/water.min.css\">\n");
out.println("</head>");
out.println("<body>");
out.println("<p>Item with the same name or barcode already exists. Retry with a different name or barcode.</p>");
out.println("</body>");
out.println("</html>");
} else {
Products products = new Products();
products.setProd_name(name);
products.setProd_barid(barid); //products.setProd_barid(baridParam);
products.setProd_description(desc);
products.setProd_added_date(date);
products.setPcs(pcs);
products.setPrice(price);
products.setProd_category(finalCategory);
HttpSession session = request.getSession();
try {
Connection con = Main.initializeDB();
PreparedStatement st = con.prepareStatement("INSERT INTO products (prod_barid, prod_category, prod_name, pcs, price, prod_description, prod_added_date) VALUES (?, ?, ?, ?, ?, ?, ?)");
st.setString(1, String.valueOf(products.getProd_barid()));
st.setString(2, products.getProd_category());
st.setString(3, products.getProd_name());
st.setInt(4, products.getPcs());
st.setDouble(5, products.getPrice());
st.setString(6, products.getProd_description());
st.setDate(7, products.getProd_added_date());
status = st.executeUpdate();
st.close();
con.close();
if (status > 0) {
response.sendRedirect("index.jsp");
} else {
out.println("UNABLE TO ADD IT... RETRY AGAIN PLEASE");
}
} catch (SQLIntegrityConstraintViolationException e) {
// Catch the exception when trying to insert a duplicate item
out.println("<!DOCTYPE html>");
out.println("<html><head>");
out.println("<meta charset=\"UTF-8\">");
out.println("<link rel=\"stylesheet\" href=\"https://cdn.jsdelivr.net/npm/water.css@2/out/water.min.css\">\n");
out.println("</head>");
out.println("<body>");
out.println("<p>Item with the same name or barcode already exists. Retry with a different name or barcode.</p>");
out.println("</body>");
out.println("</html>");
} catch (ClassNotFoundException | SQLException ex) {
ex.printStackTrace();
}
}
out.close();
}
// Check if an item with the same name or barcode exists in the database
private boolean checkItemExists(String name, String barcode) {
try {
Connection con = Main.initializeDB();
PreparedStatement st = con.prepareStatement("SELECT COUNT(*) FROM products WHERE prod_name = ? OR prod_barid = ?");
st.setString(1, name);
st.setString(2, barcode);
ResultSet rs = st.executeQuery();
rs.next();
int count = rs.getInt(1);
rs.close();
st.close();
con.close();
return count > 0;
} catch (ClassNotFoundException | SQLException ex) {
ex.printStackTrace();
}
return false;
}
private Date getCurrentDate() {
Calendar calendar = Calendar.getInstance();
java.util.Date utilDate = calendar.getTime();
return new Date(utilDate.getTime());
}
}
db.sql
CREATE USER 'adminB'@'localhost' IDENTIFIED BY 'passwordAdmin';
GRANT ALL ON BARCODEPROD.* TO 'adminB'@'localhost';
FLUSH PRIVILEGES;
CREATE DATABASE IF NOT EXISTS BARCODEPROD;
USE BARCODEPROD;
CREATE TABLE IF NOT EXISTS users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(255),
password VARCHAR(255),
role VARCHAR(20) NOT NULL DEFAULT 'non-admin'
);
CREATE TABLE IF NOT EXISTS products (
prod_barid BIGINT NOT NULL PRIMARY KEY,
prod_name varchar(255) NOT NULL,
prod_description varchar(255),
prod_added_date date NOT NULL,
prod_category varchar(255) NOT NULL,
pcs int NOT NULL,
price double NOT NULL,
totalprc double NOT NULL DOUBLE GENERATED ALWAYS AS (pcs * price) STORED
);
CREATE TABLE IF NOT EXISTS suppliers (
sup_id int AUTO_INCREMENT PRIMARY KEY,
sup_name VARCHAR(255),
sup_surname VARCHAR(255),
sup_product VARCHAR(255),
sup_phone int(10),
sup_address VARCHAR(255),
sup_website VARCHAR(255)
);
INSERT INTO users(username,password,role) VALUES('admin','admin123x','admin');
INSERT INTO users(username,password,role) VALUES('moderator','1234','non-admin');
Main.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class Main{
protected static Connection initializeDB() throws SQLException, ClassNotFoundException {
String dbDriver = "org.mariadb.jdbc.Driver";
String dbURL = "jdbc:mariadb://localhost:3306/BARCODEPROD";
String dbUser = "adminB";
String dbPass = "passwordAdmin";
Connection con = null;
try { //in case of an mariadb issue..
Class.forName(dbDriver);
con = DriverManager.getConnection(dbURL, dbUser, dbPass); //Connection con = DriverManager.getConnection(dbURL + dbName, dbUsername, dbPassword);
} catch(ClassNotFoundException | SQLException ex) {
ex.printStackTrace();
}
return con;
}
}
