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 (?, ?, ?, ?, ?)";