Returning a 0 value error

[Roberto Favaro]

Im working on a project where I add members to a database(access at the minute), add amend and delete to this database. I can also find a member by id,name,address,etc. Im using a primary key generatorEverytime I retrieve a member from the database it returns a value of 0 for the id.It should be returning a value from the database.Im totally confused,any help would be greatly appreciated.Thanks

Member

//Member.java
 
package cp3project.business ;
 
import java.io.* ;
import java.util.* ;
 
public class Member implements Cloneable, Comparable
{
private long memberId ;
private String firstName ;
private String lastName ;
private String passwd ;
private String emailAddress ;
private String p_firstName ;
private String p_lastName ;
private String p_address1 ;
private String p_address2 ;
private String p_townCity ;
private String p_country ;
private String creditCardType ;
private String creditCardNumber ;
private String expiryDate ;
private String d_address1 ;
private String d_address2 ;
private String d_townCity ;
private String d_country ;
 
public Member()
{
//memberId = 0L ;
firstName = "" ;
lastName = "" ;
passwd = "" ;
emailAddress = "" ;
p_firstName = "" ;
p_lastName = "" ;
p_address1 = "" ;
p_address2 = "" ;
p_townCity = "" ;
p_country = "" ;
creditCardType = "" ;
creditCardNumber = "" ;
expiryDate = "" ;
d_address1 = "" ;
d_address2 = "" ;
d_townCity = "" ;
d_country = "" ;
}
 
public Member(/*long aMemberId, */String aFirstName, String aLastName, String aPasswd, String aEmailAddress, String aP_firstName,
String aP_lastName, String aP_address1, String aP_address2, String aP_townCity, String aP_country,
String aCreditCardType, String aCreditCardNumber, String aExpiryDate, String aD_address1, String aD_address2,
String aD_townCity, String aD_country)
{
//memberId = aMemberId ;
firstName = aFirstName ;
lastName = aLastName ;
passwd = aPasswd ;
emailAddress = aEmailAddress ;
p_firstName = aP_firstName ;
p_lastName = aP_lastName ;
p_address1 = aP_address1 ;
p_address2 = aP_address2 ;
p_townCity = aP_townCity ;
p_country = aP_country ;
creditCardType = aCreditCardType ;
creditCardNumber = aCreditCardNumber ;
expiryDate = aExpiryDate ;
d_address1 = aD_address1 ;
d_address2 = aD_address2 ;
d_townCity = aD_townCity ;
d_country = aD_country ;
}
 
//get methods
public long getMemberId()
{
return memberId ;
}
 
public String getFirstName()
{
return firstName ;
}
 
public String getLastName()
{
return lastName ;
}
 
public String getPasswd()
{
return passwd ;
}
 
public String getEmailAddress()
{
return emailAddress ;
}
 
public String getP_firstName()
{
return p_firstName ;
}
 
public String getP_lastName()
{
return p_lastName ;
}
 
public String getP_address1()
{
return p_address1 ;
}
 
public String getP_address2()
{
return p_address2 ;
}
 
public String getP_townCity()
{
return p_townCity ;
}
 
public String getP_country()
{
return p_country ;
}
 
public String getCreditCardType()
{
return creditCardType ;
}
 
public String getCreditCardNumber()
{
return creditCardNumber ;
}
 
public String getExpiryDate()
{
return expiryDate ;
}
 
public String getD_address1()
{
return d_address1 ;
}
 
public String getD_address2()
{
return d_address2 ;
}
 
public String getD_townCity()
{
return d_townCity ;
}
 
public String getD_country()
{
return d_country ;
}
 

//set methods
public void setMemberId(long aMemberId)
{
memberId = aMemberId ;
}
 
public void setFirstName(String aFirstName)
{
firstName = aFirstName ;
}
 
public void setLastName(String aLastName)
{
lastName = aLastName ;
}
 
public void setPasswd(String aPasswd)
{
passwd = aPasswd ;
}
 
public void setEmailAddress(String aEmailAddress)
{
emailAddress = aEmailAddress ;
}
 
public void setP_firstName(String aP_firstName)
{
p_firstName = aP_firstName ;
}
 
public void setP_lastName(String aP_lastName)
{
p_lastName = aP_lastName ;
}
 
public void setP_address1(String aP_address1)
{
p_address1 = aP_address1 ;
}
 
public void setP_address2(String aP_address2)
{
p_address2 = aP_address2 ;
}
 
public void setP_townCity(String aP_townCity)
{
p_townCity = aP_townCity ;
}
 
public void setP_country(String aP_country)
{
p_country = aP_country ;
}
 
public void setCreditCardType(String aCreditCardType)
{
creditCardType = aCreditCardType;
}
 
public void setCreditCardNumber(String aCreditCardNumber)
{
creditCardNumber = aCreditCardNumber ;
}
 
public void setExpiryDate(String aExpiryDate)
{
expiryDate = aExpiryDate ;
}
 
public void setD_address1(String aD_address1)
{
d_address1 = aD_address1 ;
}
 
public void setD_address2(String aD_address2)
{
d_address2 = aD_address2 ;
}
 
public void setD_townCity(String aD_townCity)
{
d_townCity = aD_townCity ;
}
 
public void setD_country(String aD_country)
{
d_country = aD_country ;
}
 
public boolean equals(Object otherObject)
{
// Check if both reference same object
if (this == otherObject)
return true ;
// Ensure otherObject is not null
if (otherObject == null)
return false ;
// Ensure that classes match
if (getClass() != otherObject.getClass() )
return false ;
// You now know that otherObject is non-null Member
Member other = (Member) otherObject ;
 
if (memberId == (other.memberId) )
return true ;
else
return false ;
}
 
public String toString()
{
return getClass().getName() + "[memberId=" + memberId
+ ",firstName=" + firstName
+ ",lastName=" + lastName
+ ",passwd=" + passwd
+ ",emailAddress=" + emailAddress
+ ",p_firstName =" + p_firstName
+ ",p_lastName=" + p_lastName
+ ",p_address1=" + p_address1
+ ",p_address2=" + p_address2
+ ",p_townCity=" + p_townCity
+ ",p_country=" + p_country
+ ",creditCardType=" + creditCardType
+ ",creditCardNumber=" + creditCardNumber
+ ",expiryDate=" + expiryDate
+ ",d_address1=" + d_address1
+ ",d_address2=" + d_address2
+ ",d_townCity=" + d_townCity
+ ",d_country=" + d_country + "]" ;
}
 
public int hashCode()
{
return emailAddress.hashCode() ;// memberId cannot be used as long cannot be dereferenced
}
 
public int compareTo(Object otherObject)
{
Member other = (Member) otherObject ;
 
if (memberId < other.memberId)
return -1 ;
 
if (memberId > other.memberId)
return 1 ;
 
return 0 ;
}
 
public Object clone()
{
try
{
Member cloned = (Member) super.clone() ;
 
return cloned ;
}
catch(CloneNotSupportedException e) { return null ; }
}
 
public void display()
{
System.out.println("Member Id: " + memberId + "\n" + "First Name: " + firstName + "\n" +
   "Last Name: " + lastName + "\n" + "Password: " + passwd + "\n" +
   "Email Address: " + emailAddress + "\n" + "Payment First Name: " + p_firstName + "\n" +
   "Payment Last Name: " + p_lastName + "\n" + "Payment Address1: " + p_address1 + "\n" +
   "Payment Address2: " + p_address2 + "\n" + "Payment Town or City: " + p_townCity + "\n" +
   "Payment Country: " + p_country + "\n" + "Credit Card Type: " + creditCardType + "\n" +
   "Credit Card Number: " + creditCardNumber + "\n" + "Expiry Date: " + expiryDate + "\n" +
   "Delivery Address1: "  + d_address1 + "\n" + "Delivery Address2: " + d_address2 + "\n" +
   "Delivery Town or City: " + d_townCity + "\n" + "Delivery Country: " + d_country + "\n") ;
}
 
//Unit-test Member
public static void main(String[] args)
{
Member m1 = new Member() ;
System.out.println("Default constructor, m1:") ;
System.out.println("m1 : " + m1.getMemberId() + " \n" + "First Name: " + m1.getFirstName() + " \n" +
   "Last Name: " + m1.getLastName() + " \n" + "Password: " + m1.getPasswd() + " \n" +
   "Email Address: " + m1.getEmailAddress() + " \n" + "Payment First Name: " + m1.getP_firstName() + "\n" +
   "Payment Last Name: " + m1.getP_lastName() + "\n" + "Payment Address1: " + m1.getP_address1() + "\n" +
   "Payment Address2: " + m1.getP_address2() + "\n" + "Payment Town or City: " + m1.getP_townCity() + "\n" +
   "Payment Country: " + m1.getP_country() + "\n" + "Credit Card Type: " + m1.getCreditCardType() + "\n" +
   "Credit Card Number: " + m1.getCreditCardNumber() + "\n" + "Expiry Date: " + m1.getExpiryDate() + "\n" +
   "Delivery Address1: "  + m1.getD_address1() + "\n" + "Delivery Address2: " + m1.getD_address2() + "\n" +
   "Delivery Town or City: " + m1.getD_townCity() + "\n" + "Delivery Country: " + m1.getD_country() + " \n" ) ;
//m1.setMemberId(1111) ;
m1.setFirstName("Colin") ;
m1.setLastName("Mc Gillick") ;
m1.setPasswd("hello") ;
m1.setEmailAddress("[e-mail]") ;
m1.setP_firstName("Paul") ;
m1.setP_lastName("Mc Gillick") ;
m1.setP_address1("71 Georgian Close") ;
m1.setP_address2("") ;
m1.setP_townCity("Drogheda") ;
m1.setP_country("Ireland") ;
m1.setCreditCardType("Visa") ;
m1.setCreditCardNumber("123456789012") ;
m1.setExpiryDate("01/09") ;
m1.setD_address1("68 Georgian Close") ;
m1.setD_address2("") ;
m1.setD_townCity("Drogheda") ;
m1.setD_country("Ireland") ;
 
System.out.println("After setting values, m1:") ;
System.out.println("m1 : " + m1.getMemberId() + " \n" + "First Name: " + m1.getFirstName() + " \n" +
   "Last Name: " + m1.getLastName() + " \n" + "Password: " + m1.getPasswd() + " \n" +
   "Email Address: " + m1.getEmailAddress() + " \n" + "Payment First Name: " + m1.getP_firstName() + "\n" +
   "Payment Last Name: " + m1.getP_lastName() + "\n" + "Payment Address1: " + m1.getP_address1() + "\n" +
   "Payment Address2: " + m1.getP_address2() + "\n" + "Payment Town or City: " + m1.getP_townCity() + "\n" +
   "Payment Country: " + m1.getP_country() + "\n" + "Credit Card Type: " + m1.getCreditCardType() + "\n" +
   "Credit Card Number: " + m1.getCreditCardNumber() + "\n" + "Expiry Date: " + m1.getExpiryDate() + "\n" +
   "Delivery Address1: "  + m1.getD_address1() + "\n" + "Delivery Address2: " + m1.getD_address2() + "\n" +
   "Delivery Town or City: " + m1.getD_townCity() + "\n" + "Delivery Country: " + m1.getD_country() + " \n" ) ;
 
Member m2 = new Member("Rachel", "Regan", "piglet", "[e-mail]", "Mary", "Regan", "Balloy", " ", "Stamulen", "Ireland",
"Mastercard", "098765432109", "04/07", "12 Cockel Road", " ", "Kells", "Ireland") ;
System.out.println("m2 : " + m2.getMemberId() + " \n" + "First Name: " + m2.getFirstName() + " \n" +
   "Last Name: " + m2.getLastName() + " \n" + "Password: " + m2.getPasswd() + " \n" +
   "Email Address: " + m2.getEmailAddress() + " \n" + "Payment First Name: " + m2.getP_firstName() + "\n" +
   "Payment Last Name: " + m2.getP_lastName() + "\n" + "Payment Address1: " + m2.getP_address1() + "\n" +
   "Payment Address2: " + m2.getP_address2() + "\n" + "Payment Town or City: " + m2.getP_townCity() + "\n" +
   "Payment Country: " + m2.getP_country() + "\n" + "Credit Card Type: " + m2.getCreditCardType() + "\n" +
   "Credit Card Number: " + m2.getCreditCardNumber() + "\n" + "Expiry Date: " + m2.getExpiryDate() + "\n" +
   "Delivery Address1: "  + m2.getD_address1() + "\n" + "Delivery Address2: " + m2.getD_address2() + "\n" +
   "Delivery Town or City: " + m2.getD_townCity() + "\n" + "Delivery Country: " + m2.getD_country() + " \n" ) ;
 

System.out.println("HashCodes: " + m1.hashCode() + "\t" + m2.hashCode() ) ;
System.out.println("compareTo: " + m1.compareTo(m2) ) ;
System.out.println("equals: " + m1.equals(m2) ) ;
 
System.out.println() ;
Member cloned = (Member) m1.clone() ;
System.out.println("clone, m1:") ;
cloned.display() ;
cloned.setLastName("Walsh") ;
System.out.println("After setting last name of clone, m1:") ;
cloned.display() ;
System.out.println("Original member m1:") ;
m1.display() ;
}
}    

MemberDao

 // MemberDao.java
 
package cp3027project.dao ;
 
import java.util.* ;// Required for ArrayList
import java.sql.* ;
import java.io.* ;
 
import cp3027project.broker.* ;
import cp3027project.business.* ;
import cp3027project.dao.* ;
import cp3027project.exceptions.* ;
 

public class MemberDao extends Dao
{
MyBroker broker = null ;
 
public MemberDao() throws DaoException
{
broker = MyBroker.getInstance() ;
}
// new super class Dao
/*
public Connection getConnection() throws DaoException
{
Connection con = broker.getConnection() ;
return con ;
}
public void freeConnection(Connection con) throws DaoException
{
if (con != null)
{
broker.freeConnection(con) ;
}
}
*/
 
/*****************
* *
* Finder methods *
* *
*****************/
 
/*
//Method not used
public Member getMemberByPrimaryKey(int aNumber) throws DaoException
{
Connection con = null ;
Statement stmt = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
 
stmt = con.createStatement() ;
String query = "SELECT * from Member WHERE number = " + aNumber ;
System.out.println(query) ;
rs = stmt.executeQuery(query) ;
 
if (rs.next() )
{
int number = rs.getInt("number") ;
String memberId = rs.getString("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
m = new Member(memberId, firstName, lastName, passwd, emailAddress) ;
}
}
catch(SQLException e)
{
throw new DaoException("getMember(): " + e.getMessage() ) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (stmt != null)
stmt.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("getMember(): " + e.getMessage() ) ;
}
}
 
return m ;
}
 
//Method not used
public Member getMemberByPrimaryKey(int aNumber) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
 
String query = "SELECT * FROM Member WHERE number = ?" ;
ps = con.prepareStatement(query) ;
ps.setInt(1, aNumber) ;
rs = ps.executeQuery() ;
 
if (rs.next() )
{
int number = rs.getInt("number") ;
String memberId = rs.getString("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 
m = new Member(memberId, firstName, lastName, passwd, emailAddress) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMember() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("getMember(): " + e.getMessage() ) ;
}
}
return m ;
}
 

public Member getMemberById(long aMemberId) throws DaoException
{
Connection con = null ;
Statement stmt = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
 
stmt = con.createStatement() ;
String query = "SELECT * from Member WHERE memberId = " + aMemberId ;
System.out.println(query) ;
rs = stmt.executeQuery(query) ;
 
if (rs.next() )
{
long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
m = new Member(memberId, firstName, lastName, passwd, emailAddress) ;
}
}
catch(SQLException e)
{
throw new DaoException("getMember(): " + e.getMessage() ) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (stmt != null)
stmt.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("getMember(): " + e.getMessage() ) ;
}
}
 
return m ;
}
*/
 
public Member getMemberById(long aMemberId) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
 
String query = "SELECT * FROM Member WHERE memberId = ?" ;
ps = con.prepareStatement(query) ;
//ps.setLong(1, aMemberId) ;//setLong() not supported by JDBC-ODBC bridge
ps.setInt(1, (int) aMemberId) ;
rs = ps.executeQuery() ;
 
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMember() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("getMember(): " + e.getMessage() ) ;
}
}
return m ;
}
 
/*
public Member getMemberByLastName(String aLastName) throws DaoException
{
Connection con = null ;
Statement stmt = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
 
stmt = con.createStatement() ;
String query = "SELECT * from Member WHERE lastName = '" + aLastName + "'" ;
System.out.println(query) ;
rs = stmt.executeQuery(query) ;
 
if (rs.next() )
{
long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 
m = new Member(memberId, firstName, lastName,
     passwd, emailAddress) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByLastName() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (stmt != null)
stmt.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByLastName() " + e.getMessage() ) ;
}
}
return m ;
}
*/
 
public Member getMemberByLastName(String aLastName) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
String query = "SELECT * FROM Member WHERE lastName = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, aLastName) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByLastName() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByLastName() " + e.getMessage() ) ;
}
}
return m ;
}
 
/*
public Member getMemberByEmailAddress(String aEmailAddress) throws DaoException
{
Connection con = null ;
Statement stmt = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
 
stmt = con.createStatement() ;
String query = "SELECT * from Member WHERE emailAddress = '" + aEmailAddress + "'" ;
System.out.println(query) ;
rs = stmt.executeQuery(query) ;
 
if (rs.next() )
{
long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 
m = new Member(memberId, firstName, lastName,
passwd, emailAddress) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByEmailAddress() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (stmt != null)
stmt.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByEmailAddress() " + e.getMessage() ) ;
}
}
return m ;
}
*/
 
public Member getMemberByEmailAddress(String aEmailAddress) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
String query = "SELECT * FROM Member WHERE emailAddress = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, aEmailAddress) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByEmailAddress() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByEmailAddress() " + e.getMessage() ) ;
}
}
return m ;
}
 
public Member getMemberByEmailAddressPasswd(String aEmailAddress, String aPassword) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
String query = "SELECT * FROM Member WHERE emailAddress = ? AND passwd = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, aEmailAddress) ;
ps.setString(2, aPassword) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByEmailPassword() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByEmail() " + e.getMessage() ) ;
}
}
return m ;
}
 
public Member getMemberByP_firstName(String aP_firstName) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
String query = "SELECT * FROM Member WHERE p_firstName = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, aP_firstName) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByP_firstName() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByP_firstName() " + e.getMessage() ) ;
}
}
return m ;
}
 
public Member getMemberByP_lastName(String aP_lastName) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
String query = "SELECT * FROM Member WHERE p_lastName = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, aP_lastName) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByP_lastName() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByP_lastName() " + e.getMessage() ) ;
}
}
return m ;
}
 
public Member getMemberByP_Address1(String aP_address1) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
String query = "SELECT * FROM Member WHERE p_address1  = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, aP_address1) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByP_Address1() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByP_Address1() " + e.getMessage() ) ;
}
}
return m ;
}
 
public Member getMemberByP_Address2(String aP_address2) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
String query = "SELECT * FROM Member WHERE p_address2 = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, aP_address2) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByP_Address2() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByP_Address2() " + e.getMessage() ) ;
}
}
return m ;
}
 
public Member getMemberByCreditCardType(String aCreditCardType) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
String query = "SELECT * FROM Member WHERE creditCardType = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, aCreditCardType) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByCreditCardType() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByCreditCardType() " + e.getMessage() ) ;
}
}
return m ;
}
 
public Member getMemberByCreditCardNumber(String aCreditCardNumber) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
String query = "SELECT * FROM Member WHERE creditCardNumber = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, aCreditCardNumber) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByCreditCardNumber() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByCreditCardNumber() " + e.getMessage() ) ;
}
}
return m ;
}
 
public Member getMemberByExpiryDate(String aExpiryDate) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
Member m = null ;
try
{
con = getConnection() ;
String query = "SELECT * FROM Member WHERE expiryDate = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, aExpiryDate) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
}
}
catch(SQLException se)
{
throw new DaoException("dao.getMemberByExpiryDate() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("dao.getMemberByExpiryDate() " + e.getMessage() ) ;
}
}
return m ;
}
 
/*
It is unsafe to pass a ResultSet between objects.  Instead, store
thecontents of the ResultSet object in a collection,
and pass the collection instead.
*/
 
public ArrayList getMembers() throws DaoException
{
Connection con = null ;
Statement stmt = null ;
ResultSet rs = null ;
ArrayList a = new ArrayList() ;
 
try
{
con = getConnection() ;
stmt = con.createStatement() ;
String query = "SELECT * from Member" ;
System.out.println(query) ;
rs = stmt.executeQuery(query) ;
 
while (rs.next() )
{
//long memberId = rs.getLong("memberId") ;
String firstName = rs.getString("firstName") ;
String lastName = rs.getString("lastName") ;
String passwd = rs.getString("passwd") ;
String emailAddress = rs.getString("emailAddress") ;
 String p_firstName = rs.getString("p_firstName") ;
 String p_lastName = rs.getString("p_lastName") ;
 String p_address1 = rs.getString("p_address1") ;
 String p_address2 = rs.getString("p_address2") ;
 String p_townCity = rs.getString("p_townCity") ;
 String p_country = rs.getString("p_country") ;
 String creditCardType = rs.getString("creditCardType") ;
 String creditCardNumber = rs.getString("creditCardNumber") ;
 String expiryDate = rs.getString("expiryDate") ;
 String d_address1 = rs.getString("d_address1") ;
 String d_address2 = rs.getString("d_address2") ;
 String d_townCity = rs.getString("d_townCity") ;
String d_country = rs.getString("d_country") ;
 
Member m = new Member(/*memberId, */firstName, lastName, passwd, emailAddress, p_firstName, p_lastName, p_address1,
p_address2, p_townCity, p_country, creditCardType, creditCardNumber, expiryDate, d_address1,
d_address2, d_townCity, d_country) ;
a.add(m) ;
}
}
catch(SQLException e)
{
throw new DaoException("getMembers(): " + e.getMessage() ) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (stmt != null)
stmt.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("getMembers(): " + e.getMessage() ) ;
}
}
return a ;
}
 
/*****************
* *
*  Add a Member  *
* *
*****************/
/*   CHECK IF EMAIL ADDRESS ALREADY EXISTS
public int addMember(Member m) throws DaoException
{
Connection con = null ;
Statement stmt = null ;
ResultSet rs = null ;
int rows = 0 ;
try
{
con = getConnection() ;
stmt = con.createStatement() ;
String command = "INSERT INTO Member(memberId, firstName, lastName, passwd, emailAddress) VALUES('" + m.getFirstName()
+ "'" + ", '" + m.getLastName()
+ "'" + ", '" + m.getPasswd()
+ "'" + ", '" + m.getEmailAddress()
+ "')" ;
 
System.out.println(command) ;
rows = stmt.executeUpdate(command) ;
}
catch(SQLException e)
{
throw new DaoException("addMember(): " + e.getMessage() ) ;
}
finally
{
try
{
if (stmt != null)
stmt.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("addMember(): " + e.getMessage() ) ;
}
}
 
return rows;
}
*/
/*   Add a member but first you must check if the member exists. A new member may not contain
an email address already used by another member. The primary key generator must be used
to insert a value for a member.
*/
public int addMember(Member m) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
int rows = 0 ;
try
{
PrimaryKeyGenerator pkgen = PrimaryKeyGenerator.getInstance() ;
long PK = pkgen.getNextPK() ;
m.setMemberId(PK) ;
System.out.println(PK) ;
con = getConnection() ;
String query = "SELECT * FROM Member WHERE emailAddress = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, m.getEmailAddress() ) ;
 
rs = ps.executeQuery() ;
if (rs.next() )
{
String msg = m.getEmailAddress() + " You may not use this email" ;
throw new SQLException(msg) ;// Email address already exists
}
rs.close() ;
rs = null ;
 
String command = "INSERT INTO Member VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)" ;
 
ps = con.prepareStatement(command) ;
//ps.setLong(1, PK ) ;setLong() not supported by JDBC-ODBC bridge
ps.setInt(1, (int)PK ) ;
ps.setString(2, m.getFirstName() ) ;
ps.setString(3, m.getLastName() ) ;
ps.setString(4, m.getPasswd() ) ;
ps.setString(5, m.getEmailAddress() ) ;
ps.setString(6, m.getP_firstName() ) ;
ps.setString(7, m.getP_lastName() ) ;
ps.setString(8, m.getP_address1() ) ;
ps.setString(9, m.getP_address2() ) ;
ps.setString(10, m.getP_townCity() ) ;
ps.setString(11, m.getP_country() ) ;
ps.setString(12, m.getCreditCardType() ) ;
ps.setString(13, m.getCreditCardNumber() ) ;
ps.setString(14, m.getExpiryDate() ) ;
ps.setString(15, m.getD_address1() ) ;
ps.setString(16, m.getD_address2() ) ;
ps.setString(17, m.getD_townCity() ) ;
ps.setString(18, m.getD_country() ) ;
 
rows = ps.executeUpdate() ;
}
catch(SQLException se)
{
throw new DaoException("dao.addMember() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("addGetMemberByEmailAddress(): " + e.getMessage() ) ;
}
}
return rows ;
}
 
/*****************
* *
* Amend a Member *
* *
*****************/
 
/*
When amending you may not use another members email address as this is a unique value. Check
to ensure it doesnt exist and then exeute the update.
*/
public int amendMember(Member m) throws DaoException
{
Connection con = null ;
PreparedStatement ps = null ;
ResultSet rs = null ;
int rows = 0 ;
try
{
con = getConnection() ;
 
String query = "SELECT * FROM Member where emailAddress = ?" ;
ps = con.prepareStatement(query) ;
ps.setString(1, m.getEmailAddress() ) ;
 
rs = ps.executeQuery() ;
 
if (rs.next() )// Ensure another member does not have this email
{
long checkMemberId = rs.getLong("memberId") ;
if (checkMemberId != (m.getMemberId() ) )
{
String msg = "amendMember():  You may not use this email" ;
throw new SQLException(msg) ;
}
}
rs.close() ;
rs = null ;
 
String command = "UPDATE Member SET firstName = ?, lastName = ?, passwd = ?, emailAddress = ?, p_firstName = ?, " +
"p_lastName = ?, p_address1 = ?, p_address2 = ?, p_townCity = ?, p_country = ?, creditCardType = ?, " +
"creditCardNumber = ?, expiryDate = ?, d_address1 = ?, d_address2 = ?, d_townCity = ?, d_country = ? " +
"WHERE memberId = ?" ;
 
ps = con.prepareStatement(command) ;
ps.setString(1, m.getFirstName() ) ;
ps.setString(2, m.getLastName() ) ;
ps.setString(3, m.getPasswd() ) ;
ps.setString(4, m.getEmailAddress() ) ;
ps.setString(5, m.getP_firstName() ) ;
ps.setString(6, m.getP_lastName() ) ;
ps.setString(7, m.getP_address1() ) ;
ps.setString(8, m.getP_address2() ) ;
ps.setString(9, m.getP_townCity() ) ;
ps.setString(10, m.getP_country() ) ;
ps.setString(11, m.getCreditCardType() ) ;
ps.setString(12, m.getCreditCardNumber() ) ;
ps.setString(13, m.getExpiryDate() ) ;
ps.setString(14, m.getD_address1() ) ;
ps.setString(15, m.getD_address2() ) ;
ps.setString(16, m.getD_townCity() ) ;
ps.setString(17, m.getD_country() ) ;
//ps.setLong(18, m.getMemberId() ) ;//setLong() not supported by JDBC-ODBC bridge
ps.setInt(18, (int) m.getMemberId() ) ;
 
rows = ps.executeUpdate() ;
}
catch(SQLException se)
{
throw new DaoException("dao.amendMember() " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps != null)
ps.close() ;
if (con != null)
freeConnection(con) ;
}
catch(SQLException e)
{
throw new DaoException("amendMember(): " + e.getMessage() ) ;
}
}
return rows ;
}
 
/******************
*  *
* Delete a Member *
*  *
******************/
 
/*
A member may not be removed from the table Member if
that member has any outstanding orders,i.e. orders that
have not yet been dispatched (Status == Ordered or Processing).
*/
public int deleteMember(long memberId) throws DaoException
{
Connection con = null ;
PreparedStatement ps1 = null ;
PreparedStatement ps2 = null ;
String command = null ;
ResultSet rs = null ;
int rows = 0 ;
try
{
con = getConnection() ;
con.setAutoCommit(false) ;// set immediately after obtaining connection
/*
Check if Member has any orders not yet dispatched.
*/
String query = "SELECT orderId, status FROM [Order] WHERE memberId = ?" ;
ps1 = con.prepareStatement(query) ;
//ps1.setLong(1, memberId) ;//setLong() not supported by JDBC-ODBC bridge
ps1.setInt(1, (int) memberId) ;
rs = ps1.executeQuery() ;
boolean orderExists = false ;
boolean okToDelete = true ;
 
while (rs.next() )
{
orderExists = true ;// At least one order exists
long orderId = rs.getLong("orderId") ;
String status = rs.getString("status") ;
 
if (status == "Ordered" || status == "Processing")// Order not yet dispatched
{
okToDelete = false ;// Cannot delete member
break ;
}
}
rs.close() ;
rs = null ;
 
if (okToDelete == true)// Remove the OrderLines, Orders and Member
{
if (orderExists)// delete orderlines and order(s)
{
query = "SELECT orderId FROM [Order] WHERE memberId = ?" ;
ps1 = con.prepareStatement(query) ;
//ps1.setLong(1, memberId) ;//setLong() not supported by JDBC-ODBC bridge
ps1.setInt(1, (int) memberId) ;
rs = ps1.executeQuery() ; // Forward only resultset, so new query
while (rs.next() )// Remove the orderLines
{
long orderId = rs.getLong("orderId") ;
 
command = "DELETE FROM OrderLine WHERE orderId = ?" ;
// rs still active, so use new statement
ps2 = con.prepareStatement(command) ;
//ps2.setLong(1, orderId) ;//setLong() not supported by JDBC-ODBC bridge
ps2.setInt(1, (int) orderId) ;
ps2.executeUpdate() ;
}
rs.close() ;
rs = null ;
 
// Now remove all orders for this Member
command = "DELETE FROM [Order] WHERE memberId = ?" ;
ps1 = con.prepareStatement(command) ;
//ps1.setLong(1, memberId) ;//setLong() not supported by JDBC-ODBC bridge
ps1.setInt(1, (int) memberId) ;
ps1.executeUpdate() ;
}
// And now delete the Member
command = "DELETE FROM Member WHERE memberId = ?" ;
ps1 = con.prepareStatement(command) ;
//ps1.setLong(1, memberId) ;//setLong() not supported by JDBC-ODBC bridge
ps1.setInt(1, (int) memberId) ;
rows = ps1.executeUpdate() ;
con.commit() ;
}
}
catch(SQLException se)
{
try
{
con.rollback() ;
con.setAutoCommit(true);
}
catch(SQLException e)
{
throw(new DaoException("Failed to rollback " + e) ) ;
}
throw new DaoException("Failed to delete Member: " + se) ;
}
finally
{
try
{
if (rs != null)
rs.close() ;
if (ps1 != null)
ps1.close() ;
if (ps2 != null)
ps2.close() ;
if (ps2 != null)
ps2.close() ;
if (con != null)
{
con.setAutoCommit(true);
freeConnection(con) ;
}
}
catch(SQLException e)
{
throw new DaoException("deleteMember: " + e.getMessage() ) ;
}
}
return rows ;
}
 
//unit test methods
public static void main(String[] args)
{
int rows = 0 ;
Member m = null ;
MyBroker broker = null ;
try
{
broker = MyBroker.getInstance() ;
MemberDao dao = new MemberDao() ;
 
/*
System.out.println() ;
System.out.println("getMemberByPrimaryKey(\"2\")") ;
m = dao.getMemberByPrimaryKey(2) ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByPrimaryKey(\"9\")") ;
m = dao.getMemberByPrimaryKey(9) ;
if (m != null)
{
System.out.println("Member Id: " + m.getMemberId()
+ " First Name: " + m.getFirstName()
+ " Last Name: " + m.getLastName()
+ " Password: " + m.getPasswd()
+ " Email Address: " + m.getEmailAddress() ) ;
}
else
{
System.out.println("No such member") ;
}
*/
 
System.out.println() ;
System.out.println("getMemberById(\"3\")") ;
m = dao.getMemberById(3) ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberById(\"7\")") ;
m = dao.getMemberById(7) ;
if (m != null)
{
System.out.println("Member Id: " + m.getMemberId()
+ " First Name: " + m.getFirstName()
+ " Last Name: " + m.getLastName()
+ " Password: " + m.getPasswd()
+ " Email Address: " + m.getEmailAddress()
+ " Payment First Name: " + m.getP_firstName()
+ " Payment Last Name: " + m.getP_lastName()
+ " Payment Address 1: " + m.getP_address1()
+ " Payment Address 2: " + m.getP_address2()
+ " Payment Town or City: " + m.getP_townCity()
+ " Payment Country: " + m.getP_country()
+ " Credit Card Type: " + m.getCreditCardType()
+ " Credit Card Number: " + m.getCreditCardNumber()
+ " Expiry Date: " + m.getExpiryDate()
+ " Delivery Address 1: " + m.getD_address1()
+ " Delivery Address 2: " + m.getD_address2()
+ " Delivery Town or City: " + m.getD_townCity()
+ " Delivery Country: " + m.getD_country() ) ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
System.out.println("getMemberByLastName(\"Daly\")") ;
m = dao.getMemberByLastName("Daly") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByLastName(\"Smith\")") ;
m = dao.getMemberByLastName("Smith") ;
if (m != null)
{
System.out.println("Member Id: " + m.getMemberId()
+ " First Name: " + m.getFirstName()
+ " Last Name: " + m.getLastName()
+ " Password: " + m.getPasswd()
+ " Email Address: " + m.getEmailAddress()
+ " Payment First Name: " + m.getP_firstName()
+ " Payment Last Name: " + m.getP_lastName()
+ " Payment Address 1: " + m.getP_address1()
+ " Payment Address 2: " + m.getP_address2()
+ " Payment Town or City: " + m.getP_townCity()
+ " Payment Country: " + m.getP_country()
+ " Credit Card Type: " + m.getCreditCardType()
+ " Credit Card Number: " + m.getCreditCardNumber()
+ " Expiry Date: " + m.getExpiryDate()
+ " Delivery Address 1: " + m.getD_address1()
+ " Delivery Address 2: " + m.getD_address2()
+ " Delivery Town or City: " + m.getD_townCity()
+ " Delivery Country: " + m.getD_country() ) ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
System.out.println("getMemberByEmailAddress(\"[e-mail]\")") ;
m = dao.getMemberByEmailAddress("[e-mail]") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByEmailAddress(\"[e-mail]\")") ;
m = dao.getMemberByEmailAddress("[e-mail]") ;
if (m != null)
{
System.out.println("Member Id: " + m.getMemberId()
+ " First Name: " + m.getFirstName()
+ " Last Name: " + m.getLastName()
+ " Password: " + m.getPasswd()
+ " Email Address: " + m.getEmailAddress()
+ " Payment First Name: " + m.getP_firstName()
+ " Payment Last Name: " + m.getP_lastName()
+ " Payment Address 1: " + m.getP_address1()
+ " Payment Address 2: " + m.getP_address2()
+ " Payment Town or City: " + m.getP_townCity()
+ " Payment Country: " + m.getP_country()
+ " Credit Card Type: " + m.getCreditCardType()
+ " Credit Card Number: " + m.getCreditCardNumber()
+ " Expiry Date: " + m.getExpiryDate()
+ " Delivery Address 1: " + m.getD_address1()
+ " Delivery Address 2: " + m.getD_address2()
+ " Delivery Town or City: " + m.getD_townCity()
+ " Delivery Country: " + m.getD_country() ) ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByEmailAddressPasswd(\"[e-mail], dublin\")") ;
  m = dao.getMemberByEmailAddressPasswd("[e-mail]", "dublin") ;
  if (m != null )
  {
m.display() ;
}
else
{
  System.out.println("Failed to find member [e-mail], dublin") ;
}
 
System.out.println() ;
System.out.println("getMemberByP_firstName(\"Paul\")") ;
m = dao.getMemberByP_firstName("Paul") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByP_firstName(\"Steve\")") ;
m = dao.getMemberByP_firstName("Steve") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByP_lastName(\"Mc Gillick\")") ;
m = dao.getMemberByP_lastName("Mc Gillick") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByP_lastName(\"Dumbo\")") ;
m = dao.getMemberByP_lastName("Dumbo") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
System.out.println("getMemberByP_Address1(\"71\")") ;
m = dao.getMemberByP_Address1("71") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByP_Address2(\"Georgian Close\")") ;
m = dao.getMemberByP_Address2("Georgian Close") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
System.out.println("getMemberByCreditCardType(\"Visa\")") ;
m = dao.getMemberByCreditCardType("Visa") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByCreditCardType(\"asdf\")") ;
m = dao.getMemberByCreditCardType("asdf") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
System.out.println("getMemberByCreditCardNumber(\"123412341234\")") ;
m = dao.getMemberByCreditCardNumber("123412341234") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByCreditCardNumber(\"7\")") ;
m = dao.getMemberByCreditCardNumber("7") ;
if (m != null)
{
System.out.println("Member Id: " + m.getMemberId()
+ " First Name: " + m.getFirstName()
+ " Last Name: " + m.getLastName()
+ " Password: " + m.getPasswd()
+ " Email Address: " + m.getEmailAddress()
+ " Payment First Name: " + m.getP_firstName()
+ " Payment Last Name: " + m.getP_lastName()
+ " Payment Address 1: " + m.getP_address1()
+ " Payment Address 2: " + m.getP_address2()
+ " Payment Town or City: " + m.getP_townCity()
+ " Payment Country: " + m.getP_country()
+ " Credit Card Type: " + m.getCreditCardType()
+ " Credit Card Number: " + m.getCreditCardNumber()
+ " Expiry Date: " + m.getExpiryDate()
+ " Delivery Address 1: " + m.getD_address1()
+ " Delivery Address 2: " + m.getD_address2()
+ " Delivery Town or City: " + m.getD_townCity()
+ " Delivery Country: " + m.getD_country() ) ;
}
else
{
System.out.println("No such member") ;
}
System.out.println() ;
System.out.println("getMemberByExpiryDate(\"03/07\")") ;
m = dao.getMemberByExpiryDate("03/07") ;
if (m != null)
{
m.display() ;
}
else
{
System.out.println("No such member") ;
}
 
System.out.println() ;
 
System.out.println("getMemberByExpiryDate(\"7\")") ;
m = dao.getMemberByExpiryDate("7") ;
if (m != null)
{
System.out.println("Member Id: " + m.getMemberId()
+ " First Name: " + m.getFirstName()
+ " Last Name: " + m.getLastName()
+ " Password: " + m.getPasswd()
+ " Email Address: " + m.getEmailAddress()
+ " Payment First Name: " + m.getP_firstName()
+ " Payment Last Name: " + m.getP_lastName()
+ " Payment Address 1: " + m.getP_address1()
+ " Payment Address 2: " + m.getP_address2()
+ " Payment Town or City: " + m.getP_townCity()
+ " Payment Country: " + m.getP_country()
+ " Credit Card Type: " + m.getCreditCardType()
+ " Credit Card Number: " + m.getCreditCardNumber()
+ " Expiry Date: " + m.getExpiryDate()
+ " Delivery Address 1: " + m.getD_address1()
+ " Delivery Address 2: " + m.getD_address2()
+ " Delivery Town or City: " + m.getD_townCity()
+ " Delivery Country: " + m.getD_country() ) ;
}
else
{
System.out.println("No such member") ;
}
 
   System.out.println() ;
 
System.out.println("getMembers") ;
ArrayList a = dao.getMembers() ;
for (int i = 0 ; i < a.size() ; i++)
{
System.out.println() ;
m = (Member) a.get(i) ;
m.display() ;
}
 
System.out.println() ;
 
// Note: You may use any value for memberId as the
//AutoNumber feature will generate the primary key.
System.out.println("Add member, Spongebob, Squarepants, twist, [e-mail], Spongeben, Squarepants, 1, " +
"Bath, Bath Town, Plug, Mastercard, 123456712345, 06/06, 2, Plug, Plug Town, Sink") ;
m = new Member("Spongebob", "Squarepants", "twist", "[e-mail]", "Spongeben", "Squarepants", "1",
"Bath", "Bath Town", "Plug", "Mastercard", "123456712345", "06/06", "2", "Plug", "Plug Town", "Sink") ;
rows = dao.addMember(m) ;
System.out.println("rows = " + rows) ;
 
System.out.println("Add member, Spongebob1, Squarepants1, twist1, [e-mail], Spongeben1, Squarepants1, 11, Bath1, " +
"Bath Town1, Plug1, Mastercard1, 1234567123451, 06/06, 22, Plug1, Plug Town1, Sink1") ;
m = new Member("Spongebob1", "Squarepants1", "twist1", "[e-mail]", "Spongeben1", "Squarepants1", "11", "Bath1",
"Bath Town1", "Plug1", "Mastercard1", "1234567123451", "06/06", "22", "Plug1", "Plug Town1", "Sink1") ;
//rows = dao.addMember(m) ;
//System.out.println("rows = " + rows) ;
 
System.out.println() ;
 
System.out.println("Amend Member") ;
  m.setFirstName("Tommy") ;
 m.setLastName("Cahill") ;
 m.setPasswd("123xyz") ;
 m.setEmailAddress("[e-mail]") ;
 rows = dao.amendMember(m) ;
System.out.println("rows = " + rows) ;
 
 System.out.println() ;
 
System.out.println("Delete Member") ;
long memberId = m.getMemberId() ;
rows = dao.deleteMember(memberId) ;
System.out.println(memberId + " rows = " + rows) ;
}
catch(DaoException se)
{
System.out.println(se ) ;
}
 
finally
{
if (broker != null)
broker.destroy() ;
broker = null ;
}
}
}   

[David O'Meara]

Holy crap that was a waste of time

Firsly: far too much code. In my signature is a link to asking questions the right way. You can't post that much code and expect antone to help, we're volunteers and just don't have the time.

Secondly, my guess is the problem is in the pk generator, and you haven't included it.

Dave