Databasing JSP to mySQL or Access

What exactly is it you want to store in the db?
Have you designed the necessary database tables?

http://www.javaworld.com/javaworld/javaqa/2000-09/03-qa-0922-access.html?

objects,

I want to store the name of the product being bought, the product id, the price, the total price....and all the customer information that is entered in to CheckOut.jsp

I designed the tables in Access

ok so you will be doing the database update after the CheckOut page I would suspect. What does the checkout page look like?

yeah,
kinda like this
________________________________________________________
CHECK OUT FORM

The DVD's that you are buying are:




Fill out the text fields below:

First Name:

Middle Initial:

Last Name:

Street Address:

City: State:

Zip Code:

Credit Card Number: Expiration Date:

Submit

Yes, and the submit button on that check out page would call another page that would then save ell the entered data in the database.
How far are you with the checkout page?

objects,

I want to store the name of the product being bought, the product id, the price, the total price....and all the customer information that is entered in to CheckOut.jsp

I designed the tables in Access

I just need to show the products on checkout page....
other than that.. I should be done

.... and submit button goes to the Verification and invokes the database

The code to execute the sql insert will look something like this:

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
java.sql.Connection connection = java.sql.DriverManager.getConnection(connectstring, user, password);
java.sql.PreparedStatement statement = connection.prepareStatement (insertsql);
statement.executeUpdate();

Isn't that sql?  do I put that on the checkout.jsp?

> Isn't that sql?

No it's java, but you need to pass the relevant sql to perform the update.

> do I put that on the checkout.jsp?

No you put that in page that checkout.jsp calls.

The connectstring to use in the getConnection() call should be something like:

jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=<mdb file>

where <mdb file> is the location of the access mdb file.

1. First, you need to create a customer bean. which stores customer information.

2. modify your DVDCartBean to include a new public method,
public void processOrder( CustomerBean customer )
in this method, you can access to information in DVDCartBean and CustomerBean, use jdbc programming to save them in database. this is a good article to learn jdbc programming: http://java.sun.com/docs/books/tutorial/jdbc/basics/

3. Creat a DVDorder.jsp, the checkout.html or checkout.jsp submit customer info to this DVDorder.jsp, this jsp should perform following tasks
store custoerm info into CustomerBean, you can use <jsp:setProperty name="customer", property="*"/>
do necessary validation
call processOrder: <% cart.processOrder( customer ); %>
if everyting is fine, display (forward) to order confim page
if error occur, display (forward) to order error page

kennethxu,

A bean is still needed to make a database?

I am not really clear on what type of database this is.....SQL or Access

kennethxu,

Is creating another bean really necessary....I guess is what I am really asking

>> I am not really clear on what type of database this is.....SQL or Access  
Comment from VWrestle97  
jdbc API is database independent. that means, you program written for one database will still work if you change to another database. when you change your datebase, all you need to do is get the rigth database driver and connection url.

>> Is creating another bean really necessary....I guess is what I am really asking
It is not a must. But if you don't create another bean, you'll have to write a lot of java code in your jsp pages. According to what your current bean/jsp design, your jsp pages are mostly scriptlet free (mean you use very little java code in jsp page), so it's better idea to keep this framework. Creating a new bean is very simple, all you need to do is implement getXXX, setXXX methods in a bean classes, for example:

package dvdshop;
public class DVDCartBean implements Serializable {
   private String firstName;
   private String lastName;
   // ... other properties

   public String getFirstName() { return firstName; }
   pulbic void setFirstName( String firstName ) { this.firstName = firstName; }

   public String getLastName() { return lastName; }
   pulbic void setLastName( String lastName ) { this.lastName = lastName; }

   // getters and setters for other properties
}

sorry, it should be
public class DVDCustomerBean implements Serializable {

> Is creating another bean really necessary

Not really, you can just pass the customer information directly to the method that processes the order.

This'll need some mods to match your db schema but should give you the basic structure of what is required, let me know any questions you have:

<%@ page language="java" contentType="text/html" %>
<%@ taglib uri="dvdtaglib.tld" prefix="dvd" %>
<%@ page import="dvdshop.DVDProductBean" %>
<%@ page import="dvdshop.DVDCartBean" %>

<%
// Get a database connection

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=c:\dvdshopdb.mdb", user, password);


%>


 <jsp:useBean
 id="cart"
 scope="session"
 class="dvdshop.DVDCartBean"
 />

 <dvd:loop name="cart" property="products" loopId="product" className="DVDProductBean">

<%
   // Add product to db

     java.sql.PreparedStatement cust = connection.prepareStatement ("insert into orders (name, address, creditcard, productid) values (?, ?, ?, ?)");
     cust.setString(1, request.getParameter("Name"));
     cust.setString(2, request.getParameter("Address"));
     cust.setString(3, request.getParameter("CreditCard"));
     cust.setString(4, product.getId());
     cust.executeUpdate();
   
    cust.close();
%>
 </dvd:loop>

<%
   connection.close();
%>

 <%-- Redirect back to the catalog page --%>
 <dvd:redirect page="Catalog.jsp" />

you'll probably also want to empty the shopping cart once the order has been processed.

objects,


what part of the code would I need to change...
 is DBQ uri absolute..In my case it's D:\....
 do I need to change the user and password... how do I    
     use it with the database or when do I use it?
 Do I call this file something like DVDorder.jsp

If I call it DVDorder.jsp how does does it get invoked/called by my current project....Do I call it from another jsp so that it gets invoked.

And does labels name, address, creditcard, productid need to be consistant with the labels of my database and in order?

 

> is DBQ uri absolute..In my case it's D:\....

yes

> do I need to change the user and password... how do I    
> use it with the database or when do I use it?

depends on the permissions you setup on the db.

> Do I call this file something like DVDorder.jsp

As good as any :)

> If I call it DVDorder.jsp how does does it get
> invoked/called by my current project....Do I call it
> from another jsp so that it gets invoked.

Yes you should invoke it when the form on Checkout.jsp is sumbitted.

> And does labels name, address, creditcard, productid
> need to be consistant with the labels of my database and
> in order?

yes (except order is not important).
The exact details will depend onm the definition of your db.

kennethxu,

>include a new public method,
>public void processOrder( CustomerBean customer )
>in this method, you can access to information in >DVDCartBean and CustomerBean
     
 can you be more specific about this method
 
>Creat a DVDorder.jsp, the checkout.html or checkout.jsp

 can you be more specific about this jsp

objects,

How do I invoke DVDorder.jsp in the Checkout.jsp?

what happens if a label in the jsp is not matched with a column in the database...does it get ignored or is a new column created?

objects,

How do I invoke DVDorder.jsp in the Checkout.jsp?

what happens if a label in the jsp is not matched with a column in the database...does it get ignored or is a new column created?

objects,

>>do I need to change the user and password...how do I    
>>use it with the database or when do I use it?

>depends on the permissions you setup on the db.

 what happens if I was not prompted with that option, should that be a concern?

objects,

>>do I need to change the user and password...how do I    
>>use it with the database or when do I use it?

>depends on the permissions you setup on the db.

 what happens if I was not prompted with that option, should that be a concern?

> How do I invoke DVDorder.jsp in the Checkout.jsp?

<form name="checkout" method="post" action="PostOrder.jsp">

<!-- Insert your customer details for in here -->

<!-- button to submit form -->

<input type="submit">

</form>

> what happens if a label in the jsp is not matched with a column in the database

The insert will fail and you will get an error.

> should that be a concern?

Depends on the level of security you require for your data.
Really just an issue if others are access the computer who you don't want to be accessing your db.

> can you be more specific about this method

WOuld basically just include the Java code included in the jsp page above to insert record in database. Just depends on whether you want to put the code in a seperate Java class,  or embed it in the jsp.

objects,

<form name="checkout" method="post" action="PostOrder.jsp">

  why is it PostOrder.jsp..what about the DVDOrder.jsp?

Sorry I changed names on you.
You can call it whatever you like :-)

objects,

why am I getting this error:

Error: 500
Location: /jsp/PostOrder.jsp
Internal Servlet Error:

org.apache.jasper.JasperException: Unable to compile class for JSPD:\ProgrammingLanguagefiles\Borland\TogetherControl6\myprojects\project\work\localhost_8080\_0002fjsp_0002fPostOrder_0002ejspPostOrder_jsp_0.java:78: Invalid escape character.
                java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\ProgrammingLanguagefiles\Borland\TogetherControl6\myDatabases\dvdshopdb.mdb", user, password);
                                                                                                                                                ^
D:\ProgrammingLanguagefiles\Borland\TogetherControl6\myprojects\project\work\localhost_8080\_0002fjsp_0002fPostOrder_0002ejspPostOrder_jsp_0.java:78: Invalid escape character.
                java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\ProgrammingLanguagefiles\Borland\TogetherControl6\myDatabases\dvdshopdb.mdb", user, password);
                                                                                                                                                                         ^
D:\ProgrammingLanguagefiles\Borland\TogetherControl6\myprojects\project\work\localhost_8080\_0002fjsp_0002fPostOrder_0002ejspPostOrder_jsp_0.java:78: Invalid escape character.
                java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\ProgrammingLanguagefiles\Borland\TogetherControl6\myDatabases\dvdshopdb.mdb", user, password);
                                                                                                                                                                                 ^
D:\ProgrammingLanguagefiles\Borland\TogetherControl6\myprojects\project\work\localhost_8080\_0002fjsp_0002fPostOrder_0002ejspPostOrder_jsp_0.java:78: Invalid escape character.
                java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\ProgrammingLanguagefiles\Borland\TogetherControl6\myDatabases\dvdshopdb.mdb", user, password);
                                                                                                                                                                                                  ^
D:\ProgrammingLanguagefiles\Borland\TogetherControl6\myprojects\project\work\localhost_8080\_0002fjsp_0002fPostOrder_0002ejspPostOrder_jsp_0.java:78: Invalid escape character.
                java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\ProgrammingLanguagefiles\Borland\TogetherControl6\myDatabases\dvdshopdb.mdb", user, password);
                                                                                                                                                                                                              ^
5 errors

objects,

is it okay if I have more columns in my database than I am requesting or accessing from my jsp?

Try using \\ instead of \:

java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\\ProgrammingLanguagefiles\\Borland\\TogetherControl6\\myDatabases\\dvdshopdb.mdb", user, password);

> is it okay if I have more columns in my database than I am requesting or accessing from my jsp?

yes

objects,

is my checkout.jsp supposed to look something like this:
__________________________________________________________
<form name="checkout" method="post" action="PostOrder.jsp">

<!-- Insert your customer details for in here -->

<p>First Name:
    <input type="text" name="textfield3">
     
</p>
<p>Middle Initial:
  <input type="text" name="textfield">
</p>
<p>Last Name:
  <input type="text" name="textfield2">
</p>
<p>Street Address:
  <input type="text" name="textfield4">
</p>
<p>City:
  <input type="text" name="textfield5">
State:
<input type="text" name="textfield6">
</p>
<p>Zip Code:
  <input type="text" name="textfield7">
</p>
  <p>Credit Card Number:
    <input type="text" name="textfield8">
    Expiration Date:
    <input type="text" name="textfield9">
  </p>
  <p><a href="Catalog.jsp">Catalog</a> </p>

  <p> <a href="Verification.jsp">Submit</a></p>
  <!-- button to submit form -->

<input type="submit">

</form>

  <p>&nbsp;</p>
</form>

objects,

now I am getting this error:
______________________________________________________
Error: 500
Location: /jsp/PostOrder.jsp
Internal Servlet Error:

org.apache.jasper.JasperException: Unable to compile class for JSPD:\ProgrammingLanguagefiles\Borland\TogetherControl6\myprojects\project\work\localhost_8080\_0002fjsp_0002fPostOrder_0002ejspPostOrder_jsp_0.java:79: Undefined variable: user
                java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\\ProgrammingLanguagefiles\\Borland\\TogetherControl6\\myDatabases\\dvdshopdb.mdb", user, password);
                                                                                                                                                                                                                                    ^
D:\ProgrammingLanguagefiles\Borland\TogetherControl6\myprojects\project\work\localhost_8080\_0002fjsp_0002fPostOrder_0002ejspPostOrder_jsp_0.java:79: Undefined variable: password
                java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\\ProgrammingLanguagefiles\\Borland\\TogetherControl6\\myDatabases\\dvdshopdb.mdb", user, password);
                                                                                                                                                                                                                                          ^
2 errors

> is my checkout.jsp supposed to look something like this

Basically yes, although I'm unclear what Verification.jsp is inteneded for?

> now I am getting this error:


That is because you don't have a variable name user defined. As you aren't using a user/password then just remove them:

java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\\ProgrammingLanguagefiles\\Borland\\TogetherControl6\\myDatabases\\dvdshopdb.mdb");

objects,

now I am getting this error:
______________________________________________________
Error: 500
Location: /jsp/PostOrder.jsp
Internal Servlet Error:

javax.servlet.ServletException: [Microsoft][ODBC Microsoft Access Driver] Could not find output table 'orders'.

objects,
________________________________________________________
<p>First Name:
   <input type="text" name="textfield3">
</p>
<p>Middle Initial:
 <input type="text" name="textfield">
</p>
<p>Last Name:
 <input type="text" name="textfield2">
________________________________________________________
I don't need to change any of this code to comply with this other code...
_______________________________________________________

 java.sql.PreparedStatement cust = connection.prepareStatement ("insert into orders (name, address, creditcard, productid) values (?, ?, ?, ?)");
    cust.setString(1, request.getParameter("Name"));
    cust.setString(2, request.getParameter("Address"));
    cust.setString(3, request.getParameter("CreditCard"));
    cust.setString(4, product.getId());
    cust.executeUpdate();

objects,

Verification.jsp just regenerates a page for the customer just to reveiw what is being bought and their personal information that they've entered.

so if you know how to access certain variables from the table to display them on Verification.jsp

but if that is too complicated then I could just drop it

> I don't need to change any of this code to comply with this other code...

Yes you will need to change it:
- you will need to match the input field names to the request parameters names.
- you will need to modify the sql statement to your database definition.

if you can post your db def I can help you with this.

> but if that is too complicated then I could just drop it

Up to you really. We can leave it out for now if you like, and can add it at a later date if required.

How do I also include the products into the database; the products being bought by the customer?

So far I have the following codes.....
Although I am not getting any generated errors, the code does not clear the shopping cart, and the database doesn't seem like it is connecting to my application cause the fields are not being saved in the database when I execute the submit button
_________________________________________________________
CheckOut.jsp:

<jsp:useBean
 id="cart"
 scope="session"
 class="dvdshop.DVDCartBean"
 />

<form name="checkout" method="post" action="PostOrder.jsp">

<!-- Insert your customer details for in here -->

<p>First Name:
    <input type="text" name="fname">

</p>
<p>Middle Initial:
  <input type="text" name="mname">
</p>
<p>Last Name:
  <input type="text" name="lname">
</p>
<p>Street Address:
  <input type="text" name="address">
</p>
<p>City:
  <input type="text" name="city">
State:
<input type="text" name="state">
</p>
<p>Zip Code:
  <input type="text" name="zip">
</p>
  <p>Credit Card Number:
    <input type="text" name="creditcard">
    Expiration Date:
    <input type="text" name="expiry">
  </p>

  <p><a href="Catalog.jsp">Catalog</a> </p>

  <p> <a href="Verification.jsp">Submit</a></p>
  <!-- button to submit form -->

<input type="submit">

</form>

  <p>&nbsp;</p>
</form>
<font color="#000000" size="4" face="Times New Roman, Times, serif"></font><font face="First Name"></font>

</body>
</html>
______________________________________________________
Catalog.jsp:
<%@ page language="java" contentType="text/html" %>
<%@ taglib uri="dvdtaglib.tld" prefix="dvd" %>
<%@ page import="dvdshop.DVDProductBean" %>
<%@ page import="dvdshop.DVDCartBean" %>

<%
// Get a database connection

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\\ProgrammingLanguagefiles\\Borland\\TogetherControl6\\myDatabases\\dvdshopdb.mdb");


%>


<jsp:useBean
id="cart"
scope="session"
class="dvdshop.DVDCartBean"
/>

<dvd:loop name="cart" property="products" loopId="product" className="DVDProductBean">

<%
  // Add product to db
    java.sql.PreparedStatement cust = connection.prepareStatement ("insert into Customers (name, address, creditcard, productid) values (?, ?, ?, ?)");
    cust.setString(1, request.getParameter("fname")+" "+request.getParameter("mname")+" "+request.getParameter("lname"));
    cust.setString(2, request.getParameter("address"));
    cust.setString(3, request.getParameter("creditcard"));
    cust.setString(4, product.getId());

   
    cust.executeUpdate();
 
   cust.close();
%>
</dvd:loop>

<%
  connection.close();
  cart.clear();
%>

<%-- Redirect back to the catalog page --%>
<dvd:redirect page="Catalog.jsp" />
________________________________________________________
DVDCartBean.java:
package dvdshop;

import java.io.*;
import java.util.*;

/**
* This class represents a shopping cart. It holds a list of products.
* @persistent
*
*/
public class DVDCartBean implements Serializable {
   private Vector cart = new Vector();

   /**
    * Adds a product to the cart, if it's not already there.
    *
    * @param product the DVDProductBean
    */
   public void setProduct(DVDProductBean product) {
       if (product != null && cart.indexOf(product) == -1) {
           cart.addElement(product);
       }
   }

   /**
    * Returns the product list.
    *
    * @return an Enumeration of DVDProductBeans
    */
   public Enumeration getProducts() {
       return cart.elements();
   }

   /**
    * Returns the total price for all products in the cart
    *
    * @return the total price
    */
   public float getTotal() {
       float total = 0;
       Enumeration prods = getProducts();
       while (prods.hasMoreElements()) {
           DVDProductBean product = (DVDProductBean) prods.nextElement();
           float price = product.getPrice();
           total += price;
       }
       return total;
   }
   
   /**
    * Returns true if the cart is empty
    *
    * @return true if the cart is empty
    */
   public boolean isEmpty() {
       return cart.size() == 0;
   }

 public void setRemove(DVDProductBean product) {
       cart.remove(product);
  }


 public void clear()
   {
      cart.removeAllElements();
   }
}
______________________________________________________

> How do I also include the products into the database;
> the products being bought by the customer?

The above code already includes the product id's from the order in the database.

It's working ok here, are you clicking the "Submit" button on the checkout page?

The Submit link (to Verification.jsp) is not required, you can remove it.

<p> <a href="Verification.jsp">Submit</a></p>

The following includes more of your fields in the db:

    java.sql.PreparedStatement cust = connection.prepareStatement ("insert into Customers (name, address, creditcard, [Exp date], city, country, productid, [Total Price]) values (?, ?, ?, ?, ?, ?, ?, ?)");
    cust.setString(1, request.getParameter("fname")+" "+request.getParameter("mname")+" "+request.getParameter("lname"));
    cust.setString(2, request.getParameter("address"));
    cust.setString(3, request.getParameter("creditcard"));
    cust.setString(4, request.getParameter("expiry"));
    cust.setString(5, request.getParameter("city"));
    cust.setString(6, request.getParameter("country"));
    cust.setString(7, product.getId());
    cust.setString(8, ""+product.getPrice());
    cust.executeUpdate();

objects,

how can you link to another page "Verification.jsp" displaying the contents of the cutomer's query from the database when pressing on the Submit Query button?

did the
 public void clear()
  {
     cart.removeAllElements();
  }
work for you too?

If you want to display a verification page then you need to instead call PostOrder.jsp from the verification page, and not from CheckOut.

ie.

CheckOut.jsp -> Verification.jsp -> PostOrder.jsp.

> displaying the contents of the cutomer's query from the database

If the purpose of the Verification page is to confirm the details entered then it would not display the details from the database, it would just display what the user entered.

yes clear worked for me.

Ojects,

from looking at the long code I posted about 7 comments above, do you see any reason why my clear method wouldn't work?

The only thing I can think of is that you aren't actually calling the page, especially considering you say that nothing is getting added to database.

CheckOut.jsp:

<jsp:useBean
id="cart"
scope="session"
class="dvdshop.DVDCartBean"
/>

<form name="checkout" method="post" action="PostOrder.jsp">

<!-- Insert your customer details for in here -->

<p>First Name:
   <input type="text" name="fname">

</p>
<p>Middle Initial:
 <input type="text" name="mname">
</p>
<p>Last Name:
 <input type="text" name="lname">
</p>
<p>Street Address:
 <input type="text" name="address">
</p>
<p>City:
 <input type="text" name="city">
State:
<input type="text" name="state">
</p>
<p>Zip Code:
 <input type="text" name="zip">
</p>
 <p>Credit Card Number:
   <input type="text" name="creditcard">
   Expiration Date:
   <input type="text" name="expiry">
 </p>

 <p><a href="Catalog.jsp">Catalog</a> </p>

<input type="submit">

</form>

 <p>&nbsp;</p>
</form>
<font color="#000000" size="4" face="Times New Roman, Times, serif"></font><font face="First Name"></font>

</body>
</html>

I am having a problem with accessing the database?

> I am having a problem with accessing the database?

What is the problem?

It's working fine here using the two pages I posted above.

I think there is a problem with the software that I am using... I think that it requires the project to be linked to the driver....  do you know what the driver is for Access... if their is no driver, I may have to do the database in mySQL

it simply access the database using odbc.
if you couldn't connect then u would be getting an error (are you?).

no error at all

the only other problem that I am getting is that the cart does not clear

The software that I am using is Together Control Center 6.0 and I may have to import the driver's deatabase using the Together's database schema...
but it requires knowing the drivers location

when I tried the database schema's
"test connection" button....It displayed "failed to connect"

The above code (unless you changed it) access the mdb file directly and does not use a DSN.
If you are not getting an error, and the cart is not clearing I strongly suspect the PostOrder.jsp page is not getting called.

Try the two pages I posted above and let me know the results. Perhaps take out the redirect at the bottom PostOPrder.jsp so it doesn't jump back to catalog.

I have and I get the same results... but I'll try again

Try taking out the redirect.

taking out redirect has no effect

do I need a driver for Access?

Not for Access specifically, the above code uses the driver for the odbc-jdbc bridge.
What happened when you removed the redirect from the bottom of PostOrder.jsp?

> taking out redirect has no effect

So are you saying you end up with a blank page after hitting the submit button?

I return to CheckOut.jsp
..but with the customer information fields cleared ... only leaving the labels for the fields

..only leaving the lavels for the fields and the product jsp information... It just clears out all the fields that are entered in by the customer

> I return to CheckOut.jsp

How can that be??
Definitely sounds like PostOrder.jsp is not getting called.

Send me your current jsp files and I'll see what the problem is.

PostOrder.jsp:
<%@ page language="java" contentType="text/html" %>
<%@ taglib uri="dvdtaglib.tld" prefix="dvd" %>
<%@ page import="dvdshop.DVDProductBean" %>
<%@ page import="dvdshop.DVDCartBean" %>
<%@ page import="java.sql.*" %>

<%
// Get a database connection

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\\ProgrammingLanguagefiles\\Borland\\TogetherControl6\\myDatabases\\dvdshopdb.mdb");


%>


<jsp:useBean
id="cart"
scope="session"
class="dvdshop.DVDCartBean"
/>

<dvd:loop name="cart" property="products" loopId="product" className="DVDProductBean">

<%
 // Add product to db


   java.sql.PreparedStatement cust = connection.prepareStatement ("insert into Customers (name, address, creditcard, [Exp date], city, country, productid, [Total Price]) values (?, ?, ?, ?, ?, ?, ?, ?)");
   cust.setString(1, request.getParameter("fname")+" "+request.getParameter("mname")+" "+request.getParameter("lname"));
   cust.setString(2, request.getParameter("address"));
   cust.setString(3, request.getParameter("creditcard"));
   cust.setString(4, request.getParameter("expiry"));
   cust.setString(5, request.getParameter("city"));
   cust.setString(6, request.getParameter("country"));
   cust.setString(7, product.getId());
   cust.setString(8, ""+product.getPrice());
   cust.executeUpdate();
 
  cust.close();
%>
</dvd:loop>

<%
 connection.close();
 cart.clear();
%>

<%-- Redirect back to the catalog page --%>
<dvd:redirect page="Catalog.jsp" />

--------------------------------------------------------
CheckOut.jsp:
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@ page language="java" contentType="text/html" %>
<%@ taglib uri="dvdtaglib.tld" prefix="dvd" %>
<%@ page import="java.text.*" %>
<%@ page import="dvdshop.DVDProductBean" %>

<jsp:useBean
id="cart"
scope="session"
class="dvdshop.DVDCartBean"
/>

    <%
         NumberFormat numFormat = NumberFormat.getCurrencyInstance();
    %>

<html>
<head>
<title>WARREN'S ON-LINE DVD STORE</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body>
<form name="form1" method="post" action="">
  <p> <strong><font color="#000033" size="12" face="Arial, Helvetica, sans-serif">CHECK
    OUT FORM</font></strong></p>

 <font color="#000033" size="4" face="Arial, Helvetica, sans-serif">DVD's you are purchasing:</font>
    <dvd:loop name="cart" property="products" loopId="product" className="DVDProductBean">
  <p><font color="#663399" size="3"><strong><font color="#000000">Product Name</font>:</strong></font>
    <font color="#663399">
    <jsp:getProperty name="product" property="name"/>
    <strong><font color="#000000" size="3">Product Price</font><font size="3">:</font></strong>
    <%= numFormat.format(product.getPrice()) %></font></p>
</dvd:loop>
  <p><font color="#669966"><strong><font color="#000000" size="3">Total Price<font color="#666666">:</font></font></strong>
    <font color="#666666"><%= numFormat.format(cart.getTotal()) %></font></font></p>

  <p><font color="#000033" size="4" face="Arial, Helvetica, sans-serif">Fill out
    the text fields below:</font></p>
  <p>&nbsp;</p>
 




<form name="checkout" method="post" action="PostOrder.jsp">

<!-- Insert your customer details for in here -->

<p>First Name:
  <input type="text" name="fname">

</p>
<p>Middle Initial:
<input type="text" name="mname">
</p>
<p>Last Name:
<input type="text" name="lname">
</p>
<p>Street Address:
<input type="text" name="address">
</p>
<p>City:
<input type="text" name="city">
State:
<input type="text" name="state">
</p>
<p>Zip Code:
<input type="text" name="zip">
</p>
<p>Credit Card Number:
  <input type="text" name="creditcard">
  Expiration Date:
  <input type="text" name="expiry">
</p>

<p><a href="Catalog.jsp">Catalog</a> </p>

<input type="submit">

</form>

<p>&nbsp;</p>
</form>
<font color="#000000" size="4" face="Times New Roman, Times, serif"></font><font face="First Name"></font>

</body>
</html>

get rid of the following line from CheckOut.jsp:

<form name="form1" method="post" action="">

It is causing PostOrder.jsp to not be called, and instead the page is just reloading itself.

I understand now why it was wrong...
but it now works..

I noticed that a new transaction ID was created for each product that was bought... How can I make it so that it instead would list all the products bought for one
transaction ID

and what if I wanted to make a field not required to be entered in by the user

> How can I make it so that it instead would list all the
> products bought for one transaction ID

You'll need to change your database design and have one table for storing transactions, and another table for storing the products for a transaction.

Transaction table
-----------------
tansactionid
name
address
etc

Order table
-----------------
order id
transactionid
product id
price
etc

how would that change the code

...if you were to stay consistant with the way you set up the table above

You would first insert one record into the transaction table.
And then insert multiple records (one for each product ordered) into the order table including the transaction id of the inserted transaction record as a foreign key.

>each product ordered) into the order table
>>including the transaction id of the inserted >>transaction record as a foreign key.


how do you insert the transaction record as a foreign key?

you need to insert the transactionid of the record entered into transaction table as the transactionid of each record added to the order table.

how do you transfer the transaction id like that?

I don't know how to output or transfer database information....

can you be more specific

thanks

how do you transfer the transaction id like that?

I don't know how to output or transfer database information....

can you be more specific

thanks

how do you transfer the transaction id like that?

I don't know how to output or transfer database information....

can you be more specific

thanks

You don't need to output or transfer information, you just need to include the transactionid in the updates to both tables:

insert into Transactions (transactionid, name, address, creditcard, [Exp date], city, country) ...
for each product
   insert into Orders (transactionid, productid, [Total Price]) ...

This way for every product ordered you have the id of the associated transaction record.

I know what you are saying, but can you show me by code because I don't know how to insert the transactionid from "Transactions database" into the "Orders database"

can I create 2 databases in one jsp or should I split them up... but then that would be more complicated though huh?

You not really insert the value from one table into another. You are simply inserting the same value into both tables. A timestamp is probably a good candidate to use as an id.

String id = Long.toString(new Date().getTime());

You're not creating two databases, you are inserting records into two tables in one databases. (Perhaps just a matter of semantics). And yes you can do it in one jsp, in fact that is what you should be doing.

where do I put the time stamp?

and if I just insert the same value into both tables.. in the orders table, woun't the transaction id still be incremented before... that is unless it is only entered in once in to the tabe, but then I wouldnt need 2 tables..

wouldn't time stamp be more like um..
date or time ordered?

> where do I put the time stamp?

Use the timestamp as the transaction id value

> won't the transaction id still be incremented

why would it be incremented?

The reason for using a timestamp as the id is that it will be unique. You could use any unique value.

Error: 500
Location: /jsp/PostOrder.jsp
Internal Servlet Error:

org.apache.jasper.JasperException: Unable to compile class for JSPD:\ProgrammingLanguagefiles\Borland\TogetherControl6\myprojects\project\work\localhost_8080\_0002fjsp_0002fPostOrder_0002ejspPostOrder_jsp_0.java:125: No constructor matching Date() found in class java.sql.Date.
                   cust.setString(7, Long.toString(new Date().getTime()));
                                                   ^

Use java.util.Date:

cust.setString(7, Long.toString(new java.util.Date().getTime()));

Error: 500
Location: /jsp/PostOrder.jsp
Internal Servlet Error:

org.apache.jasper.JasperException: Unable to compile class for JSPD:\ProgrammingLanguagefiles\Borland\TogetherControl6\myprojects\project\work\localhost_8080\_0002fjsp_0002fPostOrder_0002ejspPostOrder_jsp_0.java:125: Incompatible type for method. Can't convert void to java.lang.String.
                   trans.setString(7, trans.setString(7, Long.toString(new java.util.Date().getTime())));
                                                     ^

Sorry about that.... I saw my error

How can I pass the exact same stamp time to both databases with out having the other database calling the function for each product .... so that I can stop it from generating a new stamp time for each product within the same order.

You store the timestamp and use it all both insert's:

String tranid = Long.toString(new java.util.Date().getTime());

cust.setString(7, tranid);

...


order.setString(x, tranid);

As far as the database connection goes, is it possible to make the DBQ of ..........

(*.mdb)};DBQ=D:\\ProgrammingLanguagefiles\\Borland\\TogetherControl6\\myDatabases\\dvdshopdb.mdb");

relative to just my project rather than making it absoulte?

Is there a function that I can use to post the time/date?

How do I post something on a web page comming from the database.... can you give me an example from my project?

> relative to just my project rather than making it absoulte?

What you would do is create a DSN and specify it's name in your connection string.

> Is there a function that I can use to post the time/date?

Not sure what you mean, can you explain further.

> How do I post something on a web page comming from the database

You would do a select on the database and display that to your page.

<%

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
java.sql.Connection connection = java.sql.DriverManager.getConnection("jdbc:odbc:Driver={MicroSoft Access Driver (*.mdb)};DBQ=D:\\development\\www.objects.com.au\\new\\jsp\\dvdshopdb.mdb");

    java.sql.PreparedStatement q = connection.prepareStatement ("select * from Customers");
    java.sql.ResultSet rs = q.executeQuery();
  %>
  <table>
   <tr>
     <th>Name</th>
     <th>Credit Card#</th>
   </tr>
  <%
    while (rs.next())
    {
  %>
  <tr>
    <td><%= rs.getObject("name") %></td>
    <td><%= rs.getObject("creditcard") %></td>
  </tr>
  <%
    }
  %>
</table>

>What you would do is create a DSN and specify it's name in your connection string.

how do you create a DSN?

>Not sure what you mean, can you explain further.

does java have an api/command that I can use to state the time of purchase ex.  Friday, March 21, 2003 and the time of the day?

To create a DSN:

Control Panel>Administrative Tools>Data Sources (ODBC)

The DateFormat and SimpleDateFormat classes provide support for formatting dates to your need:

<%
java.util.Date today = new java.util.Date();
java.text.DateFormat df = java.text.DateFormat.getDateInstance(java.text.DateFormat.LONG);
java.text.DateFormat tf = java.text.DateFormat.getTimeInstance(java.text.DateFormat.LONG);
%>

Date: <%= df.format(today) %><br>
Time: <%= tf.format(today) %><br>

http://www.javaworld.com/javaqa/2000-09/03-qa-0922-access.html

Can you clearify some things with me regarding my project?....

1)When is an import statement needed....
       <%@ page import="dvdshop.DVDProductBean" %>...

       is it only needed when I am using a tag from my tld file, and not from a bean tag?

2)Does a jsp:useBean , useProperty, or setProperty require an import class statement like the one above?

3)can you better explain the purpose for className="DVDProductBean" of the code below:

 <dvd:loop name="cart" property="products" loopId="product" className="DVDProductBean">

4)I noticed that a useBean was not used earlier in the code for the className DVDProductBean... Is that because it is declared in the code below... so that it takes care of the jsp:useBean?

<dvd:useProperty id="product" name="catalog" property="product"
 arg='<%= request.getParameter("id") %>'
 className="dvdshop.DVDProductBean" />

1) It is needed if you want to use the class name without specifying the package name.

2) No, but if there isn't one you need to include the fully classname include package name.

3) It defined the class of the object instance 'product'

4) Yes that tag handles creating the bean instance.

>3) It defined the class of the object instance 'product'

what about the property="products".. does that also require the className?

>2) No, but if there isn't one you need to include the fully classname include package name.

so is it only a choice between either an include statement or a <jsp:    > statement?
Is the jsp:useBean , useProperty, or setProperty an example of a fully classname include package name?

ADDITIONAL QUESTION
3)What is the purpose of the encodeURL  tag in the code below: and when is the param tag needed?


<a href="
          <dvd:encodeURL url="DVDproduct.jsp">
          <dvd:param name="id" value="<%= product.getId() %>"/>
          </dvd:encodeURL>"><%= product.getName() %>
          </a>

> does that also require the className?

No, it uses the existing bean named 'cart'.

> so is it only a choice between either an include statement or a <jsp:    > statement?

No it is a choice of using an import or specifying the package name whenever you use the classname:

eg. with import you could do:

<dvd:useProperty id="product" name="catalog" property="product"
arg='<%= request.getParameter("id") %>'
className="DVDProductBean" />

without import you would need:

<dvd:useProperty id="product" name="catalog" property="product"
arg='<%= request.getParameter("id") %>'
className="dvdshop.DVDProductBean" />

> )What is the purpose of the encodeURL  tag in the code below: and when is the param tag needed?

To encode the URL converting things like spaces etc.
The param tag is need when you need to include a parameter in the url.

>To encode the URL converting things like spaces etc.

I don't see any spaces in my URL?

couldn't I have used...
 <a href="AddToCart.jsp?id=<%= product.getId() %>">Add
        to Cart</a>
instead of ....
<a href="
         <dvd:encodeURL url="DVDproduct.jsp">
         <dvd:param name="id" value="<%= product.getId() %>"/>
         </dvd:encodeURL>"><%= product.getName() %>
         </a>

In that case yes you could have. Encoding a URL is used if there is a possibility of invalid characters being used in URL.

I there a limit to how many parameters that I can pass with a single
<a href> statement

is ther a limit?

no.

how would you separate multiple parameters in a single href statement?

is it separated by a '?'

No a &, but the tag handles that for you.

can you show me an example of a href using the &....considering that it won't be using the tag handler

why would catalog be a vector?

sure :)

<a href="xyz.jsp?a=1&b=3">click here</a>

> why would catalog be a vector?

Because it stores a number of different products.

is it possible to declare the scope of a bean in a setProperty or useProperty, instead of using a useBean

No it isn't.

can you tell me what this code does for each command?


<dvd:useProperty id="product" name="catalog" property="product"
 arg='<%= request.getParameter("id") %>'
 className="dvdshop.DVDProductBean" />

from comparing the two codes below... can you tell me the purpose of the "this."  when do you use it?


public void setRemove(DVDProductBean product) {
       cart.remove(product);
  }

 public void setName(String name) {
       this.name = name;
   }
   

public void setValue(String value_) {
       value = this.value_ = value_;
   }  


What does the second line of code do.. and what is the purpose of having the "_" after value_

It is needed in this instance to identify the class member variable 'name' instead of the local parameter of the same name.
It is used in cases like this, or when you need a reference to the current object instance.

what piece of code makes my program substitue the use for cookies.... is it the scope of the useBean or is there more to it?

....which code makes each session unique for each customer ?


If I wanted to make by site secure...what would I need to include?

Generating the session id is handled by Tomcat. And the managing of the session id on the client side is done with cookies.

If you are referring to making the network traffic between the client and server secure then you would need to use https instead of http. This would only involve a change to the configuration of the server, and not to your code.

Thanks for the points :-)

I'm no longer answering questions in this forum, but am still answering in the "Java Programming" forum. So post any new questions there if you want me to assist you further.
https://www.experts-exchange.com/Programming/Programming_Languages/Java/
There are also a lot more experts than there are in this forum to assist you.

Hope I can help you again :-)