Currently trying to insert an object into the Customer table using a test SQL run. However, everything I run the test, the compiler returns the following exception:
Exception in thread "main" java.lang.RuntimeException: error finding Customer
at edu.depauw.csc480.dao.CustomerDAO.find(CustomerDAO.java:78)
at edu.depauw.csc480.dao.CustomerDAO.insert(CustomerDAO.java:87)
at edu.depauw.csc480.dao.DatabaseManager.insertCustomer(DatabaseManager.java:84)
at edu.depauw.csc480.Test.main(Test.java:18)
Caused by: java.sql.SQLSyntaxErrorException: Table/View 'CUSTOMER' does not exist.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
I have looked at the code over and over again, however, I can't seem to find an error as it compiles fine, just doesn't run. Below is my create method & find method from my CustomerDAO class.
public Customer find(int custID) {
if (cache.containsKey(custID)) return cache.get(custID);
try {
String qry = "select name from CUSTOMER where custID = ?";
PreparedStatement pstmt = conn.prepareStatement(qry);
pstmt.setInt(1, custID);
ResultSet rs = pstmt.executeQuery();
String name = rs.getString("name");
String address = rs.getString("address");
String email = rs.getString("email");
rs.close();
Customer cust = new Customer (this, custID, name, address, email);
cache.put(custID, cust);
return cust;
} catch (SQLException e) {
dbm.cleanup();
throw new RuntimeException("error finding department", e);
}
}
// Insert new CartItem & returns it if the key does not already exist.
public Customer insert (int custID, String name, String address, String email) {
try {
// make sure that the deptid is currently unused
if (find(custID) != null)
return null;
String cmd = "insert into CUSTOMER(custID, name, address, email) "
+ "values(?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(cmd);
pstmt.setInt(1, custID);
pstmt.setString(2, name);
pstmt.setString(3, address);
pstmt.setString(4, email);
pstmt.executeUpdate();
Customer cust = new Customer(this, custID, name, address, email);
cache.put(custID, cust);
return cust;
}
catch(SQLException e) {
dbm.cleanup();
throw new RuntimeException("error inserting new department", e);
}
}
Any ideas on what is causing the error?
EDIT: Here is my CustomerDAO.java class, which handles the creation of the table along with other SQL commands.
public class CustomerDAO {
private Connection conn;
private DatabaseManager dbm;
private Map<Integer, Customer> cache;
public CustomerDAO (Connection conn, DatabaseManager dbm) {
this.conn = conn;
this.dbm = dbm;
this.cache = new HashMap<Integer, Customer>();
}
static void create(Connection conn) throws SQLException {
Statement stmt = conn.createStatement();
String s = "create table CUSTOMER("
+ "name string, "
+ "custID string, "
+ "address string, "
+ "email string "
+ "primary key(custID))";
stmt.executeUpdate(s);
}
// Modifies CartItem table and adds foreign key constraints (tables need to already be created)
//static void addConstraints(Connection conn) throws SQLException {
//Statement stmt = conn.createStatement();
//String s = "alter table Customer "
// + "foreign key(shoppingCartID) references shoppingCart(shoppingCartID)";
//stmt.executeUpdate(s);
//}
//Finds CartItem object by primary key.
public Customer find(int custID) {
if (cache.containsKey(custID)) return cache.get(custID);
try {
String qry = "select name from CUSTOMER where custID = ?";
PreparedStatement pstmt = conn.prepareStatement(qry);
pstmt.setInt(1, custID);
ResultSet rs = pstmt.executeQuery();
String name = rs.getString("name");
String address = rs.getString("address");
String email = rs.getString("email");
rs.close();
Customer cust = new Customer (this, custID, name, address, email);
cache.put(custID, cust);
return cust;
} catch (SQLException e) {
dbm.cleanup();
throw new RuntimeException("error finding department", e);
}
}
// Insert new CartItem & returns it if the key does not already exist.
public Customer insert (int custID, String name, String address, String email) {
try {
// make sure that the deptid is currently unused
if (find(custID) != null)
return null;
String cmd = "insert into CUSTOMER(custID, name, address, email) "
+ "values(?, ?, ?, ?)";
PreparedStatement pstmt = conn.prepareStatement(cmd);
pstmt.setInt(1, custID);
pstmt.setString(2, name);
pstmt.setString(3, address);
pstmt.setString(4, email);
pstmt.executeUpdate();
Customer cust = new Customer(this, custID, name, address, email);
cache.put(custID, cust);
return cust;
}
catch(SQLException e) {
dbm.cleanup();
throw new RuntimeException("error inserting new department", e);
}
}
//Clears Data from CartItem Table
void clear() throws SQLException {
Statement stmt = conn.createStatement();
String s = "delete from CUSTOMER";
stmt.executeUpdate(s);
cache.clear();
}
}
