Created a Dynmic JAVA Project and can’t retrieve Data from the database to the JSP

This my first sample project using jsp and this got an error I need to fix this and move to further learning can anyone please help

created a dynamic java project using java and I have to see whether the database is connected and data were added to the database

and the other table isn’t created in the same jsp file I have given my java class and the jsp file below

package com;
import java.sql.*;
//class item
public class Item {

//adding connection
    public Connection connect() {
        
        Connection con = null;
        
        try {
            
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/item",
                    "root", "");
            
            //For testing
            System.out.print("Successfully connected");
            
        } catch (Exception e) {
            // TODO: handle exception
            e.printStackTrace();
        }
        
        return con;

        
    }
    
    public String insertItem(String code, String name, String price, String desc) {
        
        String output = "";
        
        try
        {
            Connection con = connect();
            
            if(con == null ) {
                return "Error While connecting to the database ";
            }
            String query = "insert into items ('itemID','itemCode','itemName','itemPrice',itemDesc)"
                    + "values (?,?,?,?,?)";
            
            PreparedStatement preparedStmt = con.prepareStatement(query);
            
            // binding values
            preparedStmt.setInt(1, 0);
            preparedStmt.setString(2, code);
            preparedStmt.setString(3, name);
            preparedStmt.setDouble(4, Double.parseDouble(price));
            preparedStmt.setString(5, desc);
            
            preparedStmt.execute();
            con.close();
            
            output = "Inserted successfully";
        }
        catch (Exception e)
        {
        output = "Error while inserting";
        System.err.println(e.getMessage());
        }
        
        return output ;
        
    }
    
    public String readItems() {
        
        String output= "";
        try {
            
            Connection con = connect();
            
            if (con == null) {
                
                return "Error While Connecting to the database for Reading";
            }
            //Prepare the html table to be displayed
            output = "<table border='1'><tr><th>Item Code</th>"
                    +"<th>Item Name</th><th>Item Price</th>"
                    + "<th>Item Description</th>"
                    + "<th>Update</th><th>Remove</th></tr>";
            
            String query = "select * from item";
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            
            // iterate through the rows in the result set
            while (rs.next()) {
                
                String itemID = Integer.toString(rs.getInt("itemID"));
                String itemCode = rs.getString("itemCode");
                String itemName = rs.getString("itemName");
                String itemPrice = Double.toString(rs.getDouble("itemPrice"));
                String itemDesc = rs.getString("itemDesc");
                
                // Add a row into the HTML table

                output += "<tr><td>" + itemCode + "</td>";
                output += "<td>" + itemName + "</td>";
                output += "<td>" + itemPrice + "</td>";
                output += "<td>" + itemDesc + "</td>";

                // buttons

                output += "<td><input name='btnUpdate' "
                + " type='button' value='Update'></td>"
                + "<td><form method='post' action='items.jsp'>"
                + "<input name='btnRemove' "
                + " type='submit' value='Remove'>"
                + "<input name='itemID' type='hidden' "
                + " value='" + itemID + "'>" + "</form></td></tr>";
                
                con.close();

                // Complete the html table
                output += "</table>";
                
            }
        } catch (Exception e) {
            // TODO: handle exception
            output = "Error while reading the items.";
            System.err.println(e.getMessage());
        }
        
        return output;
    }
    
}

below this is jsp file for my project I can’t get the table in the same file

<%@page import="javax.sound.midi.SysexMessage"%>
<%@page import="com.Item" %>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
    <%

    if(request.getParameter("itemCode") != null){
        
        Item itemObj = new Item();
        
        String stsMsg = itemObj.insertItem(request.getParameter("itemCode"),
                                    request.getParameter("itemName"),
                                    request.getParameter("itemPrice"),
                                    request.getParameter("itemDes"));
        session.getAttribute("statusMsg");
    }
    
    

%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Item Management</title>
</head>
<body>


            <h1>Item Management</h1>>
            <form method = "post" action= "itemps.jsp" >
                Item Code: <input name="itemCode" type= "text"><br>
                Item Name: <input name="itemName" type= "text"><br>
                Item price: <input name="itemPrice" type= "text"><br>
                Item description: <input name="itemDesc" type= "text"><br>
                 <input name="btnSubmit" type= "submit" value ="Save" ><br>
                
            
            </form>
            
            <% System.out.print(session.getAttribute("statusMsg")); %>
            <br>
            <%
                Item itemObj = new Item();
                System.out.print(itemObj.readItems());
            
            %>

</body>
</html>

Answer

In the java insertItem() method the query is wrong. The correct way should be like this.

String query = " insert into items (itemID,itemCode,itemName,itemPrice,itemDesc)"+ " values (?, ?, ?, ?, ?)";

Leave a Reply

Your email address will not be published. Required fields are marked *