From there what I am trying to do is insert all the data gathered from the inputs into an DB.
Now I have decided to do 4 Java classes and 1 servlet inorder to achieve this.
I have created the account class to use the getter and setter methods:
Code:
/*
* Account.java
*
* Created on October 14, 2008, 9:45 PM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
/**
*
*
*/
public abstract class Account
{
String CustomerName, CustomerType, CustomerComp, CustomerAddress, Nationality, PassportNo, TravelAgency, Password;
int CustomerNo;
/**
* default constructor
*/
public Account()
{
CustomerName = "";
CustomerNo = 0;
CustomerType = "";
CustomerComp = "";
CustomerAddress = "";
Nationality = "";
PassportNo = "";
TravelAgency = "";
Password = "";
}
/**
* Constructor with parameters
* @param AccNo int
* @param cName String
* @param cAddress String
* @param bal String
* @param sDate Date
*/
public Account(String cName, int cNo, String cType, String cComp, String cAdd, String n, String pNo, String tAgency, String pass)
{
CustomerName = cName;
CustomerNo = cNo;
CustomerType = cType;
CustomerComp = cComp;
CustomerAddress = cAdd;
Nationality = n;
PassportNo = pNo;
TravelAgency = tAgency;
Password = pass;
}
/**
* Set Account Number
* @param AccNo int
*/
public void setCustomerName(String cName)
{
CustomerName = cName;
}
/**
* Set Customer Name
* @param cName String
*/
public void setCustomerNo(int cNo)
{
CustomerNo = cNo;
}
/**
* Set Customer Address
* @param cAddress String
*/
public void setCustomerType(String cType)
{
CustomerType = cType;
}
/**
* Set Balance
* @param bal String
*/
public void setCustomerComp(String cComp)
{
CustomerComp = cComp;
}
/**
* Set Start Date
* @param sDate Date
*/
public void setCustomerAddress(String cAdd)
{
CustomerAddress = cAdd;
}
public void setNationality(String n)
{
Nationality = n;
}
public void setPassportNo(String pNo)
{
PassportNo = pNo;
}
public void setTravelAgency(String tAgency)
{
TravelAgency = tAgency;
}
public void setPassword(String pass)
{
Password = pass;
}
public void setAllAccountData(String cName, int cNo, String cType, String cComp, String cAdd, String n, String pNo, String tAgency, String pass)
{
CustomerName = cName;
CustomerNo = cNo;
CustomerType = cType;
CustomerComp = cComp;
CustomerAddress = cAdd;
Nationality = n;
PassportNo = pNo;
TravelAgency = tAgency;
Password = pass;
}
/**
* Get Account Number
* @return int
*/
public String getCustomerName()
{
return CustomerName;
}
/**
* Get Customer Name
* @return String
*/
public int getCustomerNo()
{
return CustomerNo;
}
/**
* Get Customer Address
* @return String
*/
public String getCustomerType()
{
return CustomerType;
}
/**
* Get Balance
* @return double
*/
public String getCustomerComp()
{
return CustomerComp;
}
/**
* Get Start Date
* @return Date
*/
public String getCustomerAddress()
{
return CustomerAddress;
}
public String getNationality()
{
return Nationality;
}
public String getPassportNo()
{
return PassportNo;
}
public String getTravelAgency()
{
return TravelAgency;
}
public String getPassword()
{
return Password;
}
/**
* get all account data
* @return String
*/
public String getAllAccountData()
{
return "CustomerName = " + CustomerName +
"; CustomerNo = " + CustomerNo +
"; CustomerType = " + CustomerType +
"; CustomerComp = " + CustomerComp +
"; CustomerAddress = " + CustomerAddress +
"; Nationality = " + Nationality +
"; PassportNo = " + PassportNo +
"; TravelAgency = " + TravelAgency +
"; Password = " + Password;
}
I have made an Account DB class to hold customer info:
Code:
/*
* AccountDB.java
*
* Created on October 14, 2008, 10:06 PM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
/**
*
*
*/
import java.sql.*;
/**
* <p>Title: workshop07</p>
* <p>Description: Database class</p>
* <p>Copyright: Copyright (c) 2005</p>
* <p>Company:ECU </p>
* @author Leisa Armstrong
* @version 1.0
*/
/**
* Class to create connection to a database and
* and demonstrate basic SQL queries
*/
public class AccountDB
{
/**
* the connection object
*/
static Connection connection;
// select odbc driver
static final String dbDriver =
"sun.jdbc.odbc.JdbcOdbcDriver";
static final String dbUrl = "jdbc:odbc:HotelDatabase1";
// use the BankAccount database
static final String dbName = "HotelDatabase1";
// set dbUser to any user on your Windows OS
static final String dbUser = " ";
// set dbPassword to Windows password for dbUser
static final String dbPassword = " ";
/**
* method to open the connection
*/
public Connection getConnection()
{
System.out.println("Getting Database driver" );
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver" );
}
catch( ClassNotFoundException e )
{
System.err.println(
"Cannot load database driver: for DB2, "
+ "your classpath must include "
+ "SQLLIB\\JAVA12\\DB2JAVA.ZIP." );
}
if ( connection != null )
return connection;
try {
connection = DriverManager.getConnection(
dbUrl, dbUser, dbPassword);
} catch( SQLException e ) {
System.err.println(
"Cannot connect to database: "
+ "check that Access is running and "
+ "the Hotel Database database exists." );
}
return connection;
}
public ResultSet getAllCustomerInfo() {
ResultSet rs = null;
try {
Statement statement =
getConnection().createStatement();
String sql =
"SELECT * FROM Customer";
System.out.println( sql );
statement.executeQuery( sql );
rs = statement.getResultSet();
} catch( SQLException e ) {
System.out.println(
"SQLException " + e.getMessage() );
} finally {
return rs;
}
}
public void addCust( CustomerAccount account )
throws SQLException, DBopException
{
int nrows = 0;
Statement statement = getConnection().createStatement();
String sqlString ="INSERT INTO Customer VALUES ("
+ account.getCustomerName()
+ ",'"
+ account.getCustomerNo()
+ "','"
+ account.getCustomerType()
+ "',"
+ account.getCustomerComp()
+ ",'"
+ account.getCustomerAddress()
+ "','"
+ account.getNationality()
+ "','"
+ account.getPassportNo()
+ "','"
+ account.getPassword()
+ ")";
nrows = statement.executeUpdate( sqlString );
if ( nrows != 1 )
{
throw new DBopException(
"Instead of 1 row, "
+ nrows
+ " were inserted." );
}
return;
}
public void updateCust( CustomerAccount account )
throws SQLException, DBopException
{
int nrows = 0;
Statement statement =
getConnection().createStatement();
String sql =
"UPDATE Customer SET "
+ "CustomerName = "
+ account.getCustomerName()
+ ", "
//+ "CustomerNo = '"
//+ account.getCustomerNo()
//+ "', "
+ "CustomerType = '"
+ account.getCustomerType()
+ "', "
+ "CustomerComp = "
+ account.getCustomerComp()
+ ", "
+ "CustomerAddress = '"
+ account.getCustomerAddress()
+ "', "
+ "Nationality = '"
+ account.getNationality()
+ "', "
+ "PassportNo = '"
+ account.getPassportNo()
+ "', "
+ "Password = '"
+ account.getPassword()
+ " WHERE CustomerNo="+account.getCustomerNo();
System.out.println( sql );
nrows = statement.executeUpdate( sql );
if ( nrows != 1 )
{
throw new DBopException(
"Instead of 1 row, "
+ nrows
+ " were updated." );
}
return;
}
}
A customer account java class for the customer account itself:
Code:
/*
* CustomerAccount.java
*
* Created on October 14, 2008, 10:11 PM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
/**
*
*
*/
public class CustomerAccount extends Account
{
public CustomerAccount(String cName, int cNo, String cType, String cComp, String cAdd, String n, String pNo, String tAgency, String pass)
{
super(cName, cNo, cType, cComp, cAdd, n, pNo, tAgency, pass);
}
}
A DBopexception java class for the exception:
/*
* DBopException.java
*
* Created on October 14, 2008, 10:23 PM
*
* To change this template, choose Tools | Template Manager
* and open the template in the editor.
*/
/**
*
*
*/
public class DBopException extends Exception {
public DBopException() {
this("Could not complete AccountDB database operation");
}
public DBopException(String s) {
super(s);
}
}
And finally a register servlet:
Code:
/*
* Register.java
*
* Created on October 14, 2008, 8:51 PM
*/
import java.io.*;
import java.text.*;
import java.sql.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.jsp.*;
/**
*
* @version 1.0
*/
public class Register extends HttpServlet
{
private AccountDB accountDB;
private Connection connection;
private Statement statement;
private CustomerAccount customerAccount;
private String query;
// set up database connection and create SQL statement
public void init( ServletConfig config ) throws ServletException
{
// attempt database connection
accountDB = new AccountDB();
accountDB.getConnection();
} // end of init method
// process survey response
protected void doPost( HttpServletRequest request,
HttpServletResponse response )
throws ServletException, IOException
{
// set up response to client
response.setContentType( "text/html" );
PrintWriter out = response.getWriter();
// start XHTML document
out.println( "<?xml version = \"1.0\"?>" );
out.println( "<!DOCTYPE html PUBLIC \"-//W3C//DTD " +
"XHTML 1.0 Strict//EN\" \"http://www.w3.org" +
"/TR/xhtml1/DTD/xhtml1-strict.dtd\">" );
out.println(
"<html xmlns = \"http://www.w3.org/1999/xhtml\">" );
// head section of document
out.println( "<head>" );
// read response by customer
String CustomerName, CustomerType, CustomerComp, CustomerAddress, Nationality, PassportNo, TravelAgency, Password;
CustomerName = request.getParameter( "CustomerName" );
int CustomerNo = Integer.parseInt(request.getParameter("CustomerNo"));
CustomerType = request.getParameter("CustomerType");
CustomerComp = request.getParameter("CustomerComp");
CustomerAddress = request.getParameter ("CustomerAddress");
Nationality = request.getParameter ("Nationality");
TravelAgency = request.getParameter ("TravelAgency");
PassportNo = request.getParameter("PassportNo");
Password = request.getParameter("Password");
// attempt to process the account information
try
{
if (CustomerName.equals( "" ) ||
CustomerType.equals( "" ) ||
Password.equals(""))
{
out.println( "<H3> Please click the back " +
"button and fill in all " +
"fields.</H3>" );
out.close();
return;
}
else
{
customerAccount = new CustomerAccount(CustomerName,
CustomerNo,
CustomerType,
CustomerComp,
CustomerAddress,
Nationality,
PassportNo,
TravelAgency,
Password);
accountDB.addCust(customerAccount);
}
out.println( "<title>Thank you!</title>" );
out.println( "</head>" );
out.println( "<body>" );
out.println( "<p>Thank you account created." );
// end XHTML document
out.println( "</p></body></html>" );
out.close();
}
// if database exception occurs, return error page
catch ( SQLException sqlException )
{
sqlException.printStackTrace();
out.println( "<title>Error</title>" );
out.println( "</head>" );
out.println( "<body><p>SQLException: Database error occurred. " );
out.println( "Try again later.</p></body></html>" );
out.close();
}
catch(DBopException dbException)
{
dbException.printStackTrace();
out.println( "<title>Error</title>" );
out.println( "</head>" );
out.println( "<body><p>DBopException: Database error occurred. " );
out.println( "Try again later.</p></body></html>" );
out.close();
}
} // end of doPost method
// close SQL statements and database when servlet terminates
public void destroy()
{
// attempt to close statements and database connection
try {
statement.close();
connection.close();
}
// handle database exceptions by returning error to client
catch( SQLException sqlException ) {
sqlException.printStackTrace();
}
}
} // end class RegisterServlet
Now the problem is when the project runs and I have inserted all the data once I submit it says SQLException..I have tried other ways but cant find a solution for it..could it be that I have too many classes? IS there a better way to achieve what I want?
Any help would be appreciated.
PS. Ignore the comments in the code as they indicate the wrong things.
Last edited by An Enigman; 10-14-2008 at 05:27 PM..
Now the problem is when the project runs and I have inserted all the data once I submit it says SQLException..I have tried other ways but cant find a solution for it..could it be that I have too many classes? IS there a better way to achieve what I want?
if you had taken a look at the sysout log you would have figured out the problem. your insert query is wrong...
i'm sure customerName is a string so, it has to be within quotes
you've not closed the quote that you've opened for the password column
not sure about the customerComp...but, is it numeric?
yes. there are better ways to do this, you can start by validating user input and then by using prepared statements...(later you can worry about the unclosed statements/resultsets and leaking connections...)
__________________
You never have to change anything you got up in the middle of the night to write. -- Saul Bellow
Last edited by shyam; 10-15-2008 at 12:11 PM..
Reason: added witty comment :D
Before you post, read our: 
