4 Replies - 929 Views - Last Post: 15 April 2015 - 12:42 PM

#1 sampsoninc916   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 14-February 13

Adding Data to Table using SQL and JSP

Posted 13 April 2015 - 02:02 PM

I am having trouble adding data from the input of a website to a database. When I execute the code, I am completely stuck on the checkout.jsp page. It's supposed to take the quantity input from the books.jsp, match that with the book_title and book_price from the stock table, and then add data based on that table using the checkout.jsp app and then successfully say the message, "Added to checkout". I seem to be having some trouble here. May you please help me. Here's my code and the database I'm using.

Code:

https://drive.google...iew?usp=sharing

Database:

https://drive.google...iew?usp=sharing

Regards,

sampsoninc916

Is This A Good Question/Topic? 0
  • +

Replies To: Adding Data to Table using SQL and JSP

#2 macosxnerd101   User is offline

  • Games, Graphs, and Auctions
  • member icon




Reputation: 12742
  • View blog
  • Posts: 45,926
  • Joined: 27-December 08

Re: Adding Data to Table using SQL and JSP

Posted 13 April 2015 - 02:07 PM

Moved to Java EE. Please post all JSP questions in the appropriate forum.

Also, please post your code here, using code tags, rather than on an external site.
Was This Post Helpful? 0
  • +
  • -

#3 sampsoninc916   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 25
  • Joined: 14-February 13

Re: Adding Data to Table using SQL and JSP

Posted 13 April 2015 - 02:13 PM

The problem with posting my code is that I don't know if you can work with one single portion when there are many portions in my code. But if it's really that important to you:

Code:

books.jsp
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<%@ page import ="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Book Collection</title>
</head>
<body>
<%
    if ((session.getAttribute("userid") == null) || (session.getAttribute("userid") == "")) {
%>
You are not logged in<br/>
<a href="index.jsp">Please Login</a>
<%
    } else {
    	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
    	Connection con = DriverManager.getConnection("jdbc:sqlserver://JordanWhite-PC:1433;databaseName=useraccountlist;integratedSecurity=true;",
            "root", "dbpass");
    	Statement st = con.createStatement();
%>
<a href="logout.jsp">Logout <%=session.getAttribute("userid")%></a>
<br/>
<br/>
<div>
	<div style="float:left;height:100px;width:300px;">
		<p id="book" style="color:#2E5FD1;font-size:30px" align="center">Big Book of Monsters: 2015 Edition</p>
	</div>
	<div style="float:left;height:200px;width:400px;position:relative;bottom:-30px;left:40px;">
		<%
    	ResultSet rs1 = st.executeQuery("select book_price from stock where book_price=19.95");
		while(rs1.next()){
		%>
		<div>Price: $<%=rs1.getDouble("book_price") %></div>
		<%
		}
		%>
		<br/>
		<form method="post" action="checkout.jsp">
			<div style="float:left">Quantity: <input type="number" name="quantity"></div>
			<br/>
			<br/>
			<div style="float:left">
			<input type="submit" value="Checkout">
			</div>
		</form>
	</div>
</div>
<br/>
<br/>
<div>
	<div style="float:left;height:100px;width:300px;">
		<p id="book" style="color:#2E5FD1;font-size:30px" align="center">Advanced Development for Android and iOS: 2015 Edition</p>
	</div>
	<div style="float:left;height:200px;width:400px;position:relative;bottom:-30px;left:40px;">
		<%
    	ResultSet rs2 = st.executeQuery("select book_price from stock where book_price=26.99");
		while(rs2.next()){
		%>
		<div>Price: $<%=rs2.getDouble("book_price") %></div>
		<%
		}
		%>
		<br/>
		<form method="post" action="checkout.jsp">
			<div style="float:left">Quantity: <input type="number" name="quantity"></div>
			<br/>
			<br/>
			<div style="float:left">
			<input type="submit" value="Checkout">
			</div>
		</form>
	</div>
</div>
<br/>
<br/>
<div>
	<div style="float:left;height:100px;width:300px;">
		<p id="book" style="color:#2E5FD1;font-size:30px" align="center">Secrets of Water Molecule Solidification</p>
	</div>
	<div style="float:left;height:200px;width:400px;position:relative;bottom:-30px;left:40px;">
		<%
    	ResultSet rs3 = st.executeQuery("select book_price from stock where book_price=24.79");
		while(rs3.next()){
		%>
		<div>Price: $<%=rs3.getDouble("book_price") %></div>
		<%
		}
		%>
		<br/>
		<form method="post" action="checkout.jsp">
			<div style="float:left">Quantity: <input type="number" name="quantity"></div>
			<br/>
			<br/>
			<div style="float:left">
			<input type="submit" value="Checkout">
			</div>
		</form>
	</div>
</div>
<br/>
<br/>
<div>
	<div style="float:left;height:100px;width:300px;">
		<p id="book" style="color:#2E5FD1;font-size:30px" align="center">Open World: Giving Your Children Appreciation for the Finer Things in Life</p>
	</div>
	<div style="float:left;height:200px;width:400px;position:relative;bottom:-30px;left:40px;">
		<%
    	ResultSet rs4 = st.executeQuery("select book_price from stock where book_price=21.65");
		while(rs4.next()){
		%>
		<div>Price: $<%=rs4.getDouble("book_price") %></div>
		<%
		}
		%>
		<br/>
		<form method="post" action="checkout.jsp">
			<div style="float:left">Quantity: <input type="number" name="quantity"></div>
			<br/>
			<br/>
			<div style="float:left">
			<input type="submit" value="Checkout">
			</div>
		</form>
	</div>
</div>
<%
    }
%>

</body>
</html>


checkout.jsp:

<%@ page import ="java.sql.*" %>
<%
	String title = request.getParameter("book");
	String username = request.getParameter("uname");
	Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
	Connection con = DriverManager.getConnection("jdbc:sqlserver://JordanWhite-PC:1433;databaseName=useraccountlist;integratedSecurity=true;",
        "root", "dbpass");
	String numofcopies = request.getParameter("quantity");
	if(numofcopies != null && title != null){
		int number = Integer.parseInt(numofcopies);
		String query2 = "select book_price from stock where book_title= '" + title + "'";
		PreparedStatement statement2 = con.prepareStatement(query2);
		ResultSet rs = statement2.executeQuery();
		while(rs.next()){
			float pricetag = rs.getFloat("book_price");
			String query = "insert into usercheckout(username, book_title_checkout, book_price_checkout, book_quantity_checkout) values ('" + username + "', '" + pricetag + "', '" + title + "', '" + number + "')";
			Statement statement = con.createStatement();
			int i = statement.executeUpdate(query);
			if (i > 0) {
		        //session.setAttribute("userid", user);
		        %>
		        Added to Checkout.
		        <meta http-equiv="refresh" content="2; url=books.jsp">
		        <%
		        // out.print("Registration Successfull!"+"<a href='index.jsp'>Go to Login</a>");
		    } else 
		        response.sendRedirect("index.jsp");
		}
		
	}
	
%>



The SQL query used to create the database table needed:

CREATE TABLE usercheckout (
	id int NOT NULL,
        username varchar(300) NOT NULL,
	book_title_checkout varchar(400) NOT NULL,
	book_price_checkout float(53) NOT NULL,
	book_quantity_checkout int NOT NULL,
	PRIMARY KEY (id)
)


Regards,

sampsoninc916
Was This Post Helpful? 0
  • +
  • -

#4 ndc85430   User is offline

  • I think you'll find it's "Dr"
  • member icon

Reputation: 1025
  • View blog
  • Posts: 3,942
  • Joined: 13-June 14

Re: Adding Data to Table using SQL and JSP

Posted 13 April 2015 - 11:36 PM

Why are you accessing the database in your JSPs? The JSPs should be responsible only for presentation of the data. So, you should be querying/updating the database beforehand and passing the results to the JSPs. You can do this in your controller (e.g. a servlet) for now.
Was This Post Helpful? 0
  • +
  • -

#5 kathy025   User is offline

  • D.I.C Head

Reputation: 41
  • View blog
  • Posts: 190
  • Joined: 19-December 14

Re: Adding Data to Table using SQL and JSP

Posted 15 April 2015 - 12:42 PM

@sampsoninc916:
Are you familiar with MVC (Model-View-Controller)?

The Model would be your POJOs or JavaBeans. They are Java classes that represent the entities in your database. Say you have a "book" table in your database. A corresponding POJO/JavaBean would be a "Book" class.

As mentioned, JSPs are meant to display values, which is the View. You should not be performing back-end logic in them.

A Servlet is a Java class, which is the Controller. This is where the back-end should take place (e.g. data manipulation). Whatever results the Servlet gets, it can now pass to the View/JSP.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1