0

Summary: When called from servlets, DatabaseHelper methods ignore the SQL/query part, in this case, INSERT and SELECT queries (Database isn't affected). Rest of the method logic works, even that based on successful execution of these queries i.e. the if statements. However, method calls from DatabaseHelper.main() (contains no program logic other than testing this issue) works just fine and the queries work (Database is affected).

Specifics: I have a simple 'ShoppingCart' project using NetBeans 11.1.
I am having issues with loading available products data or saving the orders to the database. Method calls from ShoppingCartServlet and CheckoutServlet servlets to the DatabaseHelper.java method (given below) don't seem to work as expected (seem to skip db related code).

However, calls to DatabaseHelper.loadProductsData() and DatabaseHelper.saveOrdersData() from within the DatabaseHelper work.
I created DatabaseHelper.main() for calling from within and ran DatabaseHelper.java individually in NetBeans and it ran perfectly.

However the servlets seem to ignore the db related parts.

  1. ShoppingCartServlet gets returned an empty productsList ArrayList whereas DatabaseHelper.main() gets a filled ArrayList. If i manually fill the ArrayList ( in the finally block of DatabaseHelper.loadProductsData()), then the method returns a filled list to the servlet just like DatabaseHelper.main().
  2. CheckoutServlet gets a Boolean true returned by calling DatabaseHelper.saveOrdersData() as expected, just like DatabaseHelper.main() however it doesn't actually save the orders to the database unlike DatabaseHelper.main() which does save.

DatabaseHelper.loadProductsData():

 try {
  String query_loadProductsData = "SELECT * FROM ProductsData";
  String connectionURL = "";
  connectionURL = "jdbc:ucanaccess://D:/OneDrive/VU/Records/7/CS506/Assignments/3/ShoppingCart/web/assets/bc160400944.accdb";
  Connection conn = DriverManager.getConnection(connectionURL);
  Statement stmt = conn.createStatement();
  ResultSet result_set = stmt.executeQuery(query_loadProductsData);
  conn.close();
  Boolean empty = true;

  // populate ArrayList
  while (result_set.next()) {
    id = result_set.getInt("ProductID");
    name = result_set.getString("ProductName");
    price = result_set.getFloat("Price");
    quantity = result_set.getInt("Quantity");

    productsList.add(new Product(id, name, (float) price, quantity));
    empty = false;
  }
  if( empty ) {
    System.out.println("failed to executeQuery");
  }

} catch (SQLException sql_ex) {
  System.out.println("SQL Exception has occured. Details given below: \n" + sql_ex);
} finally {
  if (productsList.isEmpty()) {
    productsList.add(new Product(1, "Flour" ,(float) 18.00 ,20));
    productsList.add(new Product(2, "Oil" ,(float) 19.00 ,50));
    productsList.add(new Product(3, "Ghee" ,(float) 10.00,    0));
    productsList.add(new Product(4, "Sauce" ,(float) 22.00 ,40));
    productsList.add(new Product(5, "Sugar" ,(float) 21.35 ,35));
    productsList.add(new Product(6, "Salt" ,(float) 25.00 ,26));
    productsList.add(new Product(7, "Daal" ,(float) 30.00 ,38));
    productsList.add(new Product(8, "Rice" ,(float) 40.00 ,92));
    productsList.add(new Product(9, "Pasta" ,(float) 97.00 ,63));
    productsList.add(new Product(10,  "Fruits" ,(float) 35.00 ,72));
    productsList.add(new Product(11,  "Dry Fruits" ,(float) 31.00 ,70));
    productsList.add(new Product(12,  "Tea" ,(float) 21.00 ,12));
    productsList.add(new Product(13,  "Coffee" ,(float) 38.00,    9));
    productsList.add(new Product(14,  "Milk" ,(float) 10.00 ,10));
    productsList.add(new Product(15,  "Snacks" ,(float) 23.25 ,13));
    productsList.add(new Product(16,  "Dessert" ,(float) 15.50 ,22));
    productsList.add(new Product(17,  "Vegetables" ,(float) 17.45 ,52));
    productsList.add(new Product(18,  "Chicken" ,(float) 39.00 ,98));
    productsList.add(new Product(19,  "Beef" ,(float) 62.50,  0));
    productsList.add(new Product(20,  "Frozen Food" ,(float) 19.20 ,30));
    productsList.add(new Product(21,  "Sea Food" ,(float) 81.00 ,29));
    productsList.add(new Product(22,  "Sweet" ,(float) 10.00 ,29));
    productsList.add(new Product(23,  "Cake" ,(float) 21.00,  0));
    productsList.add(new Product(24,  "Yogurt" ,(float) 26.00,    0));
    productsList.add(new Product(25,  "Pickle" ,(float) 30.00 ,41));
  }
  return productsList;
}
}

DatabaseHelper.saveOrdersData():

  public static Boolean saveOrdersData() {
try {
  String query_saveOrdersData = "INSERT INTO OrdersData (NoItems, SubTotal, Shipping, GrandTotal, Address)";
  query_saveOrdersData += "VALUES (15, 15, 15, 30, 'address'); ";
  String connectionURL = "";
  connectionURL = "jdbc:ucanaccess://D:/OneDrive/VU/Records/7/CS506/Assignments/3/ShoppingCart/web/assets/bc160400944.accdb";
  Connection conn = DriverManager.getConnection(connectionURL);
  Statement stmt = conn.createStatement();
  int affected_rows = stmt.executeUpdate(query_saveOrdersData);
  conn.close();

  if(affected_rows>0) {
    System.out.println("Order data has been saved.");
  }
  return true;
} catch (SQLException sql_ex) {
  System.out.println("SQL Exception has occured. Details given below: \n" + sql_ex);
  return true;
}
}

Link to project files:

PS: If you are testing this, you might want to change the db conn URL accordingly. I went with absolute URL. Couldn't get the right relative one. Also, I am very new to Java and this is my first servlet based program. Please keep it simple.

Awaisome
  • 33
  • 6
  • Your loadProductsData method can't possibly work, because you're closing the connection (and the the statement and the resultset) before iterating on the resultset. Use the ry-with resources statement, and replace all your catch blocks with `throw new RuntimeException(e);`. Ignoring exceptions like you're doing is really a no-no, and the best way to ignore which errors actually occur. – JB Nizet Jan 11 '20 at 17:38
  • @JBNizet, hi, i don't think closing `conn` closes `result_set`. I have been using the same configuration in two projects and it works. The problem is it doesn't work () when i call the methods from the servlets. as for exceptions, so far, i haven't had any problem, and i am too new to java to understand proper exception handling (and your advice about `throw new RunTimeException(e)`) yet. i usually try to make these changes in the end if possible. – Awaisome Jan 11 '20 at 18:16
  • @JBNizet, ok so i read up this bit `JDBC doesn't bring back all the results of a query in a ResultSet, because there may be too many of them to fetch them all eagerly. Instead it gives you something you can use to retrieve the results, but which goes away when the connection closes. So when you pass it back from your method after closing the database connection, nothing else can use it. `. so if `result_set` is like a open buffer, i guess my `result_set` is small enough (25 rows) to fetch in one go, is that's why its been working after `conn.close()`? – Awaisome Jan 11 '20 at 18:22
  • I don't think so. It's probably just that ucanaccess doesn't respect the rules. – JB Nizet Jan 11 '20 at 18:24
  • @JBNizet, that could be it. but it works perfectly when called from main(). It also doesn't throw anything when called from the servlets (albeit doesn't actually `SELECT` or `INSERT`). could you have a look at that? – Awaisome Jan 11 '20 at 18:30
  • First of all, have you replaced your catch blocks with `throw new RuntimeException(e);`? If not, start by doing that, in order to not swallow exceptions. – JB Nizet Jan 11 '20 at 18:32
  • @JBNizet, i appreciate the advice. i know almost nothing about exceptions. i read up a bit but am still confused. Rest assured, they aren't causing any problems here. i will look into it though, but first the problem at hand. could you take a look? – Awaisome Jan 12 '20 at 05:14

0 Answers0