public boolean addNewCredit(int cid, Credit credit) {
// TODO Auto-generated method stub
String url = "jdbc:oracle:thin:@oracle.cise.ufl.edu:1521:orcl";
String sql1 = "select max(ID) from Address";
String sql2 = "insert into address values(?,?,?,?,?,?,?,?,?,?,?)";
String sql3 = "insert into CreditCard values(?,?,?,?,?,?)";
String sql4 = "insert into CustomerCredit values(?,?)";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (java.lang.ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
Connection con = DriverManager.getConnection(url, "dawei", "jolly900513");
Statement stmt = con.createStatement();
// stmt.executeUpdate(sql3);
ResultSet rs = stmt.executeQuery(sql1);
int ID = 0; // address id
while (rs.next()) {
ID = rs.getInt(1) + 1;
}
PreparedStatement ps = con.prepareStatement(sql2);
ps.setInt(1, ID);
ps.setString(2, credit.getFirstname());
ps.setString(3, credit.getLastname());
ps.setString(4, credit.getCompany());
ps.setString(5, credit.getState());
ps.setString(6, credit.getPhonenumber());
ps.setInt(7, credit.getZipcode());
ps.setDate(9, new Date(System.currentTimeMillis()));
ps.setString(8, credit.getCity());
ps.setString(10, credit.getAddressline1());
ps.setString(11, credit.getAddressline2());
System.out.println(sql2);
ps.executeUpdate();
ps = con.prepareStatement(sql3);
ps.setLong(1, credit.getCardNumber());
ps.setString(2, credit.getNameOnCard());
ps.setInt(3, credit.getCaredCVV2());
ps.setDate(4, credit.getExperionDate());
ps.setString(5, credit.getCardType());
ps.setInt(6, ID);
System.out.println(sql3);
ps.executeUpdate();
ps = con.prepareStatement(sql4);
System.out.println(sql4);
ps.setInt(1, cid);
ps.setLong(2, credit.getCardNumber());
ps.executeUpdate();
stmt.close();
con.close();
return true;
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
return false;
}
public boolean updateCredit(Credit credit) {
// TODO Auto-generated method stub
long cn = credit.getCardNumber();
String url = "jdbc:oracle:thin:@oracle.cise.ufl.edu:1521:orcl";
String sql1 =
"update CreditCard set nameoncard='"
+ credit.getNameOnCard()
+ "',"
+ "experiondate=to_date('"
+ credit.getExperionDate()
+ "','YYYY-MM-DD'),"
+ "cardcvv2="
+ credit.getCaredCVV2()
+ ","
+ "cardtype='"
+ credit.getCardType()
+ "' where cardnumber="
+ cn;
String sql2 =
"update Address "
+ "set firstname='"
+ credit.getFirstname()
+ "',"
+ "lastname='"
+ credit.getLastname()
+ "',"
+ "company='"
+ credit.getCompany()
+ "',"
+ "state='"
+ credit.getState()
+ "',"
+ "phonenumber='"
+ credit.getPhonenumber()
+ "',"
+ "zipcode="
+ credit.getZipcode()
+ ","
+ "city='"
+ credit.getCity()
+ "',"
+ "addr_line1='"
+ credit.getAddressline1()
+ "',"
+ "addr_line2='"
+ credit.getAddressline2()
+ "' where id in ( select billing_addr from CreditCard where cardnumber="
+ cn
+ ")";
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (java.lang.ClassNotFoundException e) {
System.out.println(e.getMessage());
}
try {
Connection con = DriverManager.getConnection(url, "dawei", "jolly900513");
Statement stmt = con.createStatement();
System.out.println(sql1);
stmt.executeUpdate(sql1);
System.out.println(sql2);
stmt.executeUpdate(sql2);
stmt.close();
con.close();
return true;
} catch (SQLException ex) {
System.out.println(ex.getMessage());
}
return false;
}