Cannot Delete a record with an delete query though Java Eclipse connected to MS Access

[Ryan Choong]

Problem with update record in MS Access. It show this error when print stack trace .

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.1 unexpected token: QUANTITY
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:509)
at thirdTier.GeneralDBAccess.update(GeneralDBAccess.java:272)
at secondTier.DataStorage.updateConfirmed(DataStorage.java:62)
at firstTier.UserInterface.begin(UserInterface.java:174)
at firstTier.ItemDriver.main(ItemDriver.java:16)
Caused by: java.sql.SQLSyntaxErrorException: unexpected token: QUANTITY
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:505)
... 4 more
Caused by: org.hsqldb.HsqlException: unexpected token: QUANTITY
at org.hsqldb.error.Error.parseError(Unknown Source)
at org.hsqldb.ParserBase.unexpectedToken(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 7 more

user interface

case 7: phoneID = JOptionPane.showInputDialog (null, "What is the Phone's ID code?", "Phone Enquiry", JOptionPane.INFORMATION_MESSAGE);
							try {
				        		Phone p = (Phone) product.update (new Phone (phoneID.trim()));
				        		int response = JOptionPane.showConfirmDialog (null, p + "\nAre you sure you wish to update this record?\n", "Confirm Update?", JOptionPane.YES_NO_OPTION);
				        		if (response == JOptionPane.YES_OPTION) {
				        			try {
				        				product.updateConfirmed (product.find (p));
				        				product.sort(); // updating shouldn't alter the order, but sort it just in case!!
				        			} catch (NotFoundException nfe) {
				        				JOptionPane.showMessageDialog (null, "Phone not found - please check Phone's ID code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
				        			} catch (SQLException se) {
				        				se.printStackTrace();
				        			}
				        		} else {
				        			// Phone record not updated
				        			JOptionPane.showMessageDialog (null, "The Phone Record has NOT been Updated", "Not Updated", JOptionPane.INFORMATION_MESSAGE);
				        		}
							} catch (NotFoundException nfe){
								JOptionPane.showMessageDialog (null, "Phone not found - please check Phone ID Code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
							}
							break;

Data storage

package secondTier;

import java.sql.*;
import java.util.*;


import exceptions.*;

import thirdTier.*;
/**
 * create data storage to store item 
 * @author Ryan Choong
 * @return - data storage
 */
public class DataStorage {
	/**
	 * create attribute array list
	 */
ArrayList<Item>dStore;
	
	
	public DataStorage(){
		/**
		 * build a constructor
		 */
		dStore = new ArrayList<Item>();
	}
	
	/**
	 * create method
	 * @param aLP
	 * @return data storage
	 */
	public void getAll() throws SQLException {
		dStore.addAll(GeneralDBAccess.getAll());
	}
	
	public void add (Item i) throws SQLException, DuplicateException{
		int index = find (i);
		if (index < 0){
			GeneralDBAccess gdba = new GeneralDBAccess();
			gdba.addNew(i);
			
			dStore.add(i);
		}else {
			throw new DuplicateException();
		}
	}
	
	public Item update (Item i) throws NotFoundException {
	    int index = find (i);
	    if (index < 0) {
	        throw new NotFoundException ();
	    } else {
	        i = dStore.get (index);
	    }
	    return i;
	}

	public void updateConfirmed (int index) throws NotFoundException, SQLException {
	    GeneralDBAccess gdba = new GeneralDBAccess();
	    gdba.update (dStore.get (index)); // update Device from the database ...
	}
	

	public Item delete (Item i) throws NotFoundException {
		int index = find (i);
		if (index < 0){
			throw new NotFoundException ();
		}else {
			i = dStore.get(index);
		}
		return i;
	}
	
	
	public void deleteConfirmed (int index) throws NotFoundException, SQLException{
		GeneralDBAccess gdba = new GeneralDBAccess();
		gdba.delete (dStore.get(index));
		dStore.remove(index);
	}
	
	public int find (Item i){
		int index = -1;
		index = Collections.binarySearch(dStore, i);
		return index;
	} 
	
	public void sort(){
		Collections.sort (dStore);
	}
	public int size(){
		return dStore.size();
	}
	
	public Item get (int index){
		return dStore.get(index);
	}
	
}

GeneralDBAccess

public void update (Item aItem) throws NotFoundException, SQLException {
			if (aItem instanceof Phone) {
				Phone anPhone = (Phone) aItem;

				String itCode = anPhone.getItemCode();
				String category = anPhone.getCategories();
				String quantity = Integer.toString (anPhone.getQuantity());
				String price = Double.toString (anPhone.getPrice());
				String phoneType = anPhone.getPhoneType();
				String phoneModel = anPhone.getModel();
				String operatingSystem = anPhone.getOperatingSystem();
				String phoneID = anPhone.getPhoneId();
				
				
				String sqlUpdate = "UPDATE PhoneTable SET ItemCode = ?, "
									+"Categories = ?"
									+"Quantity = ?"
									+"Price = ?"
									+"PhoneType = ?"
									+"PhoneModel= ?"
									+"OperatingSystem = ?"
									+"WHERE PhoneID = ?";
				
				PreparedStatement stmt = aConnection.prepareStatement(sqlUpdate);
				
				
				stmt.setString(1, itCode);
				stmt.setString(2, category);
				stmt.setString(3, quantity);
				stmt.setString(4, price);
				stmt.setString(5, phoneType);
				stmt.setString(6, phoneModel);
				stmt.setString(7, operatingSystem);
				stmt.setString(8, phoneID);
				
				find ("Phone", phoneID);
				
				stmt.executeUpdate();

Kindly help to check what the problem.

[Claudiu Chelemen]

I took a quick glance at the code and I noticed that the update sql in the GeneralDBAccess class is not well formed, it should have commas after each set.
Claudiu

[Martin Vashko]

Welcome to the ranch!

In my opinion, the problem is the update statement itself:

Ryan Choong wrote:

				String sqlUpdate = "UPDATE PhoneTable SET ItemCode = ?, "
									+"Categories = ?"
									+"Quantity = ?"
									+"Price = ?"
									+"PhoneType = ?"
									+"PhoneModel= ?"
									+"OperatingSystem = ?"
									+"WHERE PhoneID = ?";

You need to put a comma after the question marks. The SQL command, as it stands now, is
UPDATE PhoneTable SET ItemCode = ?, Categories = ?Quantity = ?Price = ?PhoneType = ?PhoneModel= ?OperatingSystem = ?WHERE PhoneID = ?
- it should be clear now where the additional commas need to be.

[Ryan Choong]

After I add the the comma,

now show this error.

net.ucanaccess.jdbc.UcanaccessSQLException: UCAExc:::3.0.1 user lacks privilege or object not found: WHERE
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:509)
at thirdTier.GeneralDBAccess.update(GeneralDBAccess.java:265)
at secondTier.DataStorage.updateConfirmed(DataStorage.java:62)
at firstTier.UserInterface.begin(UserInterface.java:174)
at firstTier.ItemDriver.main(ItemDriver.java:16)
Caused by: java.sql.SQLSyntaxErrorException: user lacks privilege or object not found: WHERE
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)
at org.hsqldb.jdbc.JDBCPreparedStatement.<init>(Unknown Source)
at org.hsqldb.jdbc.JDBCConnection.prepareStatement(Unknown Source)
at net.ucanaccess.jdbc.UcanaccessConnection.prepareStatement(UcanaccessConnection.java:505)
... 4 more
Caused by: org.hsqldb.HsqlException: user lacks privilege or object not found: WHERE
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.error.Error.error(Unknown Source)
at org.hsqldb.ParserDQL.XreadTargetSpecification(Unknown Source)
at org.hsqldb.ParserDML.readSetClauseList(Unknown Source)
at org.hsqldb.ParserDML.compileUpdateStatement(Unknown Source)
at org.hsqldb.ParserCommand.compilePart(Unknown Source)
at org.hsqldb.ParserCommand.compileStatement(Unknown Source)
at org.hsqldb.Session.compileStatement(Unknown Source)
at org.hsqldb.StatementManager.compile(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
... 7 more

[Martin Vashko]

I'd guess it's a syntax error again. Post your current code, please.

[Ryan Choong]

I think i solved the syntax error problem

here's the latest code.

String sqlUpdate = "UPDATE PhoneTable SET ItemCode = ?, Categories = ?, Quantity = ?, "
						+ "Price = ?, PhoneType = ?, PhoneModel= ?, OperatingSystem = ? WHERE PhoneID = ?";

Now the problem is when I select the update, it go backs to my menu.

user interface

package firstTier;

import javax.swing.JOptionPane;

import exceptions.*;

import java.sql.*;

import secondTier.*;


public class UserInterface{
	
			public void begin(){
			
				//create a data storage
				DataStorage product =new DataStorage();
			
				//display the menu and process phone & simcard
				boolean finished = false;
			
				// open file before anything else is done
				try {
					ItemWorker.initialize();
					product.getAll();
					JOptionPane.showMessageDialog (null, "\n** Database successfully opened **\n", "Success", JOptionPane.PLAIN_MESSAGE);
				}
				catch (SQLException se){
					JOptionPane.showMessageDialog(null, "\n**** ERROR: Problem opening database ****\n" + se.getMessage(), "ERROR", JOptionPane.ERROR_MESSAGE);
					finished = true;
				}
				catch (ClassNotFoundException cnfe){
					JOptionPane.showMessageDialog(null, "\n** ERROR: Cannot find database **\n", "ERROR", JOptionPane.ERROR_MESSAGE);
					finished = true;
				}
				if (finished){
					JOptionPane.showMessageDialog(null, "\n*** Fatal ERROR - Program Ended ***\n" +
							"\n*** Please contact you computer services centre ***\n", "ERROR", JOptionPane.ERROR_MESSAGE);
				}else {
						try {
							product.sort();
						}
						catch (ClassCastException cce){
							JOptionPane.showMessageDialog (null, "Data not sorted properly", "Sorting Error",  JOptionPane.INFORMATION_MESSAGE);
						}
				}
				
				while  (!finished){
					int selection = showMenu();
					switch (selection){
					
					case 1: try {
								product.add(addPhone());
								product.sort();
							}
								catch (DuplicateException de){
									JOptionPane.showMessageDialog (null, "ERROR: Cannot add Phone - key already exists in database!", "ERROR", JOptionPane.ERROR_MESSAGE);
								}
								catch (SQLException se){
									se.printStackTrace();
								}
								break;
					
					case 2:	try {
							product.add(addSimCard());
							product.sort();
							}
							catch (DuplicateException de){
								JOptionPane.showMessageDialog (null, "ERROR: Cannot add SimCard - key already exists in database!", "ERROR", JOptionPane.ERROR_MESSAGE);
							}
							catch (SQLException se){
							JOptionPane.showMessageDialog (null, "ERROR: Problem with database - cannot add record!", "ERROR", JOptionPane.ERROR_MESSAGE);
							}
							break;
						
					case 3: String phoneID = JOptionPane.showInputDialog(null, "What is the Phone ID?", "Phone Enquiry", JOptionPane.INFORMATION_MESSAGE);
							int index = product.find (new Phone (phoneID.trim()));
							if (index < 0){
								JOptionPane.showMessageDialog(null, "Phone not Found - please check Item Code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
							}else {
								JOptionPane.showMessageDialog(null, "The Phone Details are: \n\n" + product.get(index) + "\n", "Item Details", JOptionPane.PLAIN_MESSAGE);	
							}
							break;
							
					case 4: String simcardID = JOptionPane.showInputDialog(null, "What is the Simcard ID?", "Sim Card Enquiry", JOptionPane.INFORMATION_MESSAGE);
							int index1 = product.find (new SimCard (simcardID.trim()));
							if (index1 < 0){
								JOptionPane.showMessageDialog(null, "Sim Card not Found - please check Item Code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
							}else {
								JOptionPane.showMessageDialog(null, "The Phone Details are: \n\n" + product.get(index1) + "\n", "Item Details", JOptionPane.PLAIN_MESSAGE);	
							}
							break;
					
					case 5: phoneID =  JOptionPane.showInputDialog (null, "What is the Item Code?", "Phone Enquiry", JOptionPane.INFORMATION_MESSAGE);
							try {
								Phone p = (Phone) product.delete (new Phone (phoneID.trim()));
								int respone = JOptionPane.showConfirmDialog(null, p + "\nAre you sure want to delete this record?\n", "Confirm Delete?", JOptionPane.YES_NO_OPTION);
								if (respone == JOptionPane.YES_OPTION){
									
							try{
								product.deleteConfirmed (product.find (p));
								product.sort();
							}
							catch (NotFoundException nfe){
								JOptionPane.showMessageDialog(null, "Phone not found - please check Item Code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
							}
							catch (SQLException se){
								JOptionPane.showMessageDialog (null, "ERROR in database", "DATABASE ERROR", JOptionPane.ERROR_MESSAGE);
								}
								}else {
									JOptionPane.showMessageDialog(null, "The Phone Record has NOT been Deleted", "Not Deleted", JOptionPane.INFORMATION_MESSAGE);
								}
					
							}
							catch (NotFoundException nfe){
									JOptionPane.showMessageDialog (null, "Phone not found - please check Item Code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
							}
							break;
						
					case 6: simcardID =  JOptionPane.showInputDialog (null, "What is the Item Code?", "Sim Card Enquiry", JOptionPane.INFORMATION_MESSAGE);
							try {
								SimCard s = (SimCard) product.delete (new SimCard (simcardID.trim()));
								int respone = JOptionPane.showConfirmDialog(null, s + "\nAre you sure want to delete this record?\n", "Confirm Delete?", JOptionPane.YES_NO_OPTION);
								if (respone == JOptionPane.YES_OPTION){
									try{
										product.deleteConfirmed (product.find (s));
										product.sort();
									}
									catch (NotFoundException nfe){
										JOptionPane.showMessageDialog(null, "Sim Card not found - please check Item Code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
									}
									catch (SQLException se){
										JOptionPane.showMessageDialog (null, "ERROR in database", "DATABASE ERROR", JOptionPane.ERROR_MESSAGE);
									}
								}else {
									JOptionPane.showMessageDialog(null, "The Sim Card Record has NOT been Deleted", "Not Deleted", JOptionPane.INFORMATION_MESSAGE);
								}
			
							}
							catch (NotFoundException nfe){
								JOptionPane.showMessageDialog (null, "Sim Card not found - please check Item Code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
							}
							break;
						
					case 7: phoneID = JOptionPane.showInputDialog (null, "What is the Phone's ID code?", "Phone Enquiry", JOptionPane.INFORMATION_MESSAGE);
							try {
				        		Phone p = (Phone) product.update (new Phone (phoneID.trim()));
				        		int response = JOptionPane.showConfirmDialog (null, p + "\nAre you sure you wish to update this record?\n", "Confirm Update?", JOptionPane.YES_NO_OPTION);
				        		if (response == JOptionPane.YES_OPTION) {
				        			try {
				        				product.updateConfirmed (product.find (p));
				        				product.sort(); // updating shouldn't alter the order, but sort it just in case!!
				        			} catch (NotFoundException nfe) {
				        				JOptionPane.showMessageDialog (null, "Phone not found - please check Phone's ID code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
				        			} catch (SQLException se) {
				        				se.printStackTrace();
				        			}
				        		} else {
				        			// Phone record not updated
				        			JOptionPane.showMessageDialog (null, "The Phone Record has NOT been Updated", "Not Updated", JOptionPane.INFORMATION_MESSAGE);
				        		}
							} catch (NotFoundException nfe){
								JOptionPane.showMessageDialog (null, "Phone not found - please check Phone ID Code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
							}
							break;
				
					case 8: JOptionPane.showMessageDialog (null, "This option is not yet available");
							break;
						
					case 9: JOptionPane.showMessageDialog(null, "Displaying the item information ...", "Item List", JOptionPane.PLAIN_MESSAGE);
							for (int i=0; i<product.size(); i++){
								JOptionPane.showMessageDialog(null, product.get(i), "Item Details", JOptionPane.PLAIN_MESSAGE);
							}
							break;

					case 10: finished = true;
							try{
								ItemWorker.terminate();
								JOptionPane.showMessageDialog(null, "   ** Database successfully closed **", "All OK", JOptionPane.INFORMATION_MESSAGE);
							}
							catch (SQLException se){
								JOptionPane.showMessageDialog(null, "ERROR: Database not closed correctly", "ERROR", JOptionPane.ERROR_MESSAGE);
							}
								JOptionPane.showMessageDialog(null, "    *** Program Ended ***\n        Have a nice day!");
								break;
				
						default: JOptionPane.showMessageDialog(null, "\n** Invalid Selection **\n", "ERROR", JOptionPane.ERROR_MESSAGE);
					}//end switch
				}//end while loop
			}//end begin

	
			public int showMenu(){
				int selection = 0;
				try{
				String stringSelection = JOptionPane.showInputDialog(
						"******* MENU *******\n\n" +
								"1. Add a new Phone\n" +
								"2. Add a new Sim Card\n" +
								"3. Find Individual Phone's Details\n" +
								"4. Find Individual Sim Card's Details\n" +
								"5. Delete a Phone data\n" +
								"6. Delete a Sim Card data\n" +
								"7. Update Phone data\n" +
								"8. Update Sim Card data\n" +
								"9. Display all Details\n" +
								"10. Exit Program\n\n" +
								"Type the number of your selection, and click OK: ");
				selection = Integer.parseInt(stringSelection.trim());
				return selection;
				}
				catch (Exception e){
					selection = 20;
				}
				return selection;
			}// end showMenu()


				
			
				public SimCard addSimCard (){
					SimCard s = new SimCard();
					
					
					// validate
				String sId = JOptionPane.showInputDialog (null, "What is the Sim Card ID?").trim().toUpperCase();
				s.setSimcardId (sId);	
					
				String ic = (JOptionPane.showInputDialog(null, "\nWhat is the item code of the product?")).trim().toUpperCase();
				//trim() gets rid of leading & trailing whitesapce
				s.setItemCode (ic);
				//get their item code
				String iCode = extractItemCode (ic);
				
				String categories = null;
				categories = JOptionPane.showInputDialog (null, "Add category of this " +iCode+ "?").trim();
				
				
				int quantity = 0;
					do{ // range - 0 to 99999
						String stringiQuantity = JOptionPane.showInputDialog (null, "What is the quantity of this "+iCode+ "?");
						int intQuantity = Integer.parseInt(stringiQuantity);
						quantity = s.setQuantity ((int)intQuantity);
						
						if (quantity <= 0 && quantity > 9999){
							JOptionPane.showMessageDialog (null, "Error - PhoneNo must be greater than 0 and less than 99999999", "ERROR", JOptionPane.ERROR_MESSAGE);
							}
						}
						while (quantity <= 0 && quantity > 9999);
					
					double price = 0.0;
					price = Double.parseDouble(JOptionPane.showInputDialog ("What is the price of this "+ic+ "?").trim());
					
					
					
						String br_String = null;
						do{//validate input if type in wrong
							br_String = JOptionPane.showInputDialog ("This " +iCode+ " service provider is (Telstra, Optus, Vodaphone)").trim();
							if (!br_String.equals ("Telstra") && !br_String.equals("Optus")
							&& !br_String.equals ("Vodaphone")){
								JOptionPane.showMessageDialog (null, "Error - Sim Card must be Telstra, Optus, Vodaphone", "ERROR", JOptionPane.ERROR_MESSAGE);
								}
							}
						while (!br_String.equals ("Telstra") && !br_String.equals("Optus")
						&& !br_String.equals ("Vodaphone"));

						
						String expiryDate = null;
						expiryDate = JOptionPane.showInputDialog ("When is the expiry date of this " +iCode+ "?").trim();
						
						
						int num = 0;
							do{
								String stringiNumber = JOptionPane.showInputDialog ("What is the number of this " +iCode+ "?");
								int intNum = Integer.parseInt(stringiNumber);
								num = s.setQuantity ((int)intNum);
								if (num < 0){
									JOptionPane.showMessageDialog (null, "Error - value must be 0 or more", "Error", JOptionPane.ERROR_MESSAGE);
								}
														}
								while (num < 0);
							SimCard s1 = new SimCard(ic, categories , (int)quantity, (double)price, br_String, expiryDate, (int)num, sId);
							return s1;
					}

				
			
				public Phone addPhone(){
					Phone p = new Phone();
					
					// validate what we can as we go
					String pId = JOptionPane.showInputDialog (null, "What is the Phone ID?").trim().toUpperCase();
					p.setPhoneId (pId);
					
					String ic = (JOptionPane.showInputDialog(null, "\nWhat is the item code of the product?")).trim().toUpperCase();
					//trim() gets rid of leading & trailing whitesapce
					p.setItemCode (ic);
					//get their item code
					String iCode = extractItemCode (ic);
					
					String categories = null;
					categories = JOptionPane.showInputDialog (null, "Add category of this " +iCode+ "?").trim();
						
					
					int quantity = 0;
						do{ // range - 0 to 9999
							String stringiQuantity = JOptionPane.showInputDialog (null, "What is the quantity of this "+iCode+ "?");
							int intQuantity = Integer.parseInt(stringiQuantity);
							quantity = p.setQuantity ((int)intQuantity);
							
							if (quantity <= 0 && quantity > 9999){
								JOptionPane.showMessageDialog (null, "Error - PhoneNo must be greater than 0 and less than 99999999", "ERROR", JOptionPane.ERROR_MESSAGE);
								}
							}
							while (quantity <= 0 && quantity > 9999);
						
						double price = 0.0;
						price = Double.parseDouble(JOptionPane.showInputDialog ("What is the price of this "+iCode+ "?").trim());
						
	
							String pType = null;
							do{//validate input if type in wrong
								pType = JOptionPane.showInputDialog (null, "What is the brand of this " +iCode+ "(Samsung, HTC, Iphone)?").trim();
								if (!pType.equals ("Samsung") && !pType.equals("Iphone")
								&& !pType.equals ("HTC")){
									JOptionPane.showMessageDialog (null, "Error - Phone Type must be Samsung, HTC, Iphone", "ERROR", JOptionPane.ERROR_MESSAGE);
									}
								}
							while (!pType.equals ("Samsung") && !pType.equals("Iphone")
								&& !pType.equals ("HTC"));

							
							String model =null;
							model = JOptionPane.showInputDialog ("What is the model of this " +iCode+ "?").trim().toLowerCase();

								String os = null;
								do{
									os = JOptionPane.showInputDialog ("What is the OS of this model in "+iCode+"?").trim();
									if (!os.equals ("Android") && !os.equals("IOS")
									&& !os.equals ("Windows")){
										JOptionPane.showMessageDialog (null, "Error - Phone Type must be Android, IOS, Windows", "Error", JOptionPane.ERROR_MESSAGE);
									}
								}
								while (!os.equals ("Android") && !os.equals("IOS")
								&& !os.equals ("Windows"));

							Phone hp = new Phone (iCode, categories , (int)quantity, (double)price, pType, model, os, pId);
							return hp;
								}
			
				public String extractItemCode (String sn){
					/**
					 * extract the item
					 */
					int index = sn.indexOf(' ');
					String itemsCode;
					if (index !=-1){
						itemsCode = sn.substring(0,index).trim();
					}
					else{
						itemsCode = sn;
					}
					return itemsCode;
				}
				
			
}
					

Not sure why go back to show menu but not update the data

[Roel De Nijs]

Ryan Choong wrote:Not sure why go back to show menu but not update the data

Then you should start debugging your own code so you can IsolateTheProblem. You'll more likely to get a valuable answer if you post just a code snippet of 10-15 lines of code instead of 350+ lines, because before someone can answer he/she has to evaluate all these lines of code and very few people will do that.

[Ryan Choong]

Sorry for 1st time posting in this forum.

I'm newbie in java programming.

So after debug the code for update

user interface

case 7: phoneID = JOptionPane.showInputDialog (null, "What is the Phone's ID code?", "Phone Enquiry", JOptionPane.INFORMATION_MESSAGE);
							try {
				        		Phone p = (Phone) product.update (new Phone (phoneID.trim()));
				        		int response = JOptionPane.showConfirmDialog (null, p + "\nAre you sure you wish to update this record?\n", "Confirm Update?", JOptionPane.YES_NO_OPTION);
				        		if (response == JOptionPane.YES_OPTION) {
				        			try {
				        				product.updateConfirmed (product.find (p));
				        				product.update(updatePhone());
				        				product.sort(); // updating shouldn't alter the order, but sort it just in case!!
				        			} catch (NotFoundException nfe) {
				        				JOptionPane.showMessageDialog (null, "Phone not found - please check Phone's ID code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
				        			} catch (SQLException se) {
				        				se.printStackTrace();
				        			}
				        		} else {
				        			// Phone record not updated
				        			JOptionPane.showMessageDialog (null, "The Phone Record has NOT been Updated", "Not Updated", JOptionPane.INFORMATION_MESSAGE);
				        		}
							} catch (NotFoundException nfe){
								JOptionPane.showMessageDialog (null, "Phone not found - please check Phone ID Code", "Not Found", JOptionPane.INFORMATION_MESSAGE);
							}
							break;

private Item updatePhone() {
						Phone p = new Phone();
					
					// validate what we can as we go
					String pId = JOptionPane.showInputDialog (null, "What is the Phone ID?").trim().toUpperCase();
					p.setPhoneId (pId);
					
					String ic = (JOptionPane.showInputDialog(null, "\nWhat is the item code of the product?")).trim().toUpperCase();
					//trim() gets rid of leading & trailing whitesapce
					p.setItemCode (ic);
					//get their item code
					String iCode = extractItemCode (ic);
					
					String categories = null;
					categories = JOptionPane.showInputDialog (null, "Add category of this " +iCode+ "?").trim();
						
					
					int quantity = 0;
						do{ // range - 0 to 9999
							String stringiQuantity = JOptionPane.showInputDialog (null, "What is the quantity of this "+iCode+ "?");
							int intQuantity = Integer.parseInt(stringiQuantity);
							quantity = p.setQuantity ((int)intQuantity);
							
							if (quantity <= 0 && quantity > 9999){
								JOptionPane.showMessageDialog (null, "Error - PhoneNo must be greater than 0 and less than 99999999", "ERROR", JOptionPane.ERROR_MESSAGE);
								}
							}
							while (quantity <= 0 && quantity > 9999);
						
						double price = 0.0;
						price = Double.parseDouble(JOptionPane.showInputDialog ("What is the price of this "+iCode+ "?").trim());
						
	
							String pType = null;
							do{//validate input if type in wrong
								pType = JOptionPane.showInputDialog (null, "What is the brand of this " +iCode+ "(Samsung, HTC, Iphone)?").trim();
								if (!pType.equals ("Samsung") && !pType.equals("Iphone")
								&& !pType.equals ("HTC")){
									JOptionPane.showMessageDialog (null, "Error - Phone Type must be Samsung, HTC, Iphone", "ERROR", JOptionPane.ERROR_MESSAGE);
									}
								}
							while (!pType.equals ("Samsung") && !pType.equals("Iphone")
								&& !pType.equals ("HTC"));

							
							String model =null;
							model = JOptionPane.showInputDialog ("What is the model of this " +iCode+ "?").trim().toLowerCase();

								String os = null;
								do{
									os = JOptionPane.showInputDialog ("What is the OS of this model in "+iCode+"?").trim();
									if (!os.equals ("Android") && !os.equals("IOS")
									&& !os.equals ("Windows")){
										JOptionPane.showMessageDialog (null, "Error - Phone Type must be Android, IOS, Windows", "Error", JOptionPane.ERROR_MESSAGE);
									}
								}
								while (!os.equals ("Android") && !os.equals("IOS")
								&& !os.equals ("Windows"));

							Phone hp = new Phone (iCode, categories , (int)quantity, (double)price, pType, model, os, pId);
							return hp;
								}

data storage

public Item update (Item i) throws NotFoundException {
	    int index = find (i);
	    if (index < 0) {
	        throw new NotFoundException ();
	    } else {
	        i = dStore.get (index);
	    }
	    return i;
	}

	public void updateConfirmed (int index) throws NotFoundException, SQLException {
	    GeneralDBAccess gdba = new GeneralDBAccess();
	    gdba.update (dStore.get (index)); // update Device from the database ...
	}

GeneralDBAccess

public void update (Item aItem) throws NotFoundException, SQLException {
			if (aItem instanceof Phone) {
				Phone anPhone = (Phone) aItem;

				String itCode = anPhone.getItemCode();
				String category = anPhone.getCategories();
				String quantity = Integer.toString (anPhone.getQuantity());
				String price = Double.toString (anPhone.getPrice());
				String phoneType = anPhone.getPhoneType();
				String phoneModel = anPhone.getModel();
				String operatingSystem = anPhone.getOperatingSystem();
				String phoneID = anPhone.getPhoneId();
				
				
				String sqlUpdate = "UPDATE PhoneTable SET ItemCode = ?, Categories = ?, Quantity = ?, "
						+ "Price = ?, PhoneType = ?, PhoneModel= ?, OperatingSystem = ? WHERE PhoneID = ?";
				
				PreparedStatement stmt = aConnection.prepareStatement(sqlUpdate);
				
				
				stmt.setString(1, itCode);
				stmt.setString(2, category);
				stmt.setString(3, quantity);
				stmt.setString(4, price);
				stmt.setString(5, phoneType);
				stmt.setString(6, phoneModel);
				stmt.setString(7, operatingSystem);
				stmt.setString(8, phoneID);
				
				find ("Phone", phoneID);
				
				stmt.executeUpdate();
				
			}

I try to update, but is not updating at all. show nothing error.

[Roel De Nijs]

Ryan Choong wrote:Sorry for 1st time posting in this forum.

I'm newbie in java programming.

So after debug the code for update

This is already much better, but still it's a lot of code to go through (almost 150 lines combined). I went through your code and it looks a little bit strange, weird and inconsistent.

Let's have a look at this line

Phone p = (Phone) product.update (new Phone (phoneID.trim()));

For me it seems you are retrieving a Phone from the database by invoking the update-method?!?

Then you do something like

product.updateConfirmed (product.find (p));

I have no clue what's the purpose of this code. Maybe it's getting all information from a Phone out of the database. I really don't know! But again that should be 1 call to a get() method, not invoking several methods and definitely not an updateConfirmed() method.

Finally this code is executed

product.update(updatePhone());

And I quess the updatePhone() method is asking the user to provide the new details. And then the resulting Phone object is persisted to the database (but that doesn't seem to work). It also seems that your user has to enter the phone id twice. That's not really user-friendly. And what's the purpose of the find() method call inside the update() method And another very important remark: it seems you are not closing your resources (like Connection, Statement, ResultSet). This will result in resource leaks! You should use try-with-resources whenever it's possible, because resources will be automatically closed. If it's not possible to use it in your method, you must definitely close all used resources.

Besides the above, I also notice you are casting a lot in this code. Using casts throughout your code is mostly an indication your code lacks an object-oriented design. And using casts without instanceof can be dangerous and result in a ClassCastException. You should definitely introduce some generics in your code. It will get rid of all these casts and gives you compile-time safety! And you also get less cluttered code (and that's easier to read and maintain). The more lines of code you have, the more likely to have bugs

So based on what you are trying to do, I would expect this kind of control flow

// user enters phone id
Phone phone = phoneStorage.get(phoneId);
// user makes changes to phone-object
phoneStorage.update(phone);

Considering your class design, you could have a DataStorage class like this

class ItemStorage<T extends Item> {
  private final Map<Integer, T> map = new HashMap<>();

  public T get(int id) throws NotFoundException {
    T item = map.get(id);
    if (item == null) {
      throw new NotFoundException();
    }
    return item;
  }

  // other common methods
}

And this class will contain all methods which are common to all items. If some items have a different type of key, you could add an additional generic type parameter and use that as well. Then for specific Phone method implementations, you'll create a subclass of this class

class PhoneStorage extends ItemStorage<Phone> {
  // some specific methods
}

Using these classes you don't need to cast anymore, because when you need a Phone object, the PhoneStorage will provide one.

Now for your Dao (DbAccess) it's very similar:

interface ItemDao<T extends Item> {
  List<T> findAll();
  T get(int id);
  T create(T item);
  void update(T item);
  void delete(T item);
}

Then you have an abstract class with common code for all your dao classes

abstract class AbstractItemDao<T extends Item> implements ItemDao<T> {
  // common code
}

And finally you have a Dao which focuses on Phone objects

class PhoneDao extends AbstractItemDao<Phone> {
  private static final String SQL_UPDATE = "UPDATE PhoneTable SET ItemCode = ?, Categories = ?, Quantity = ?, "
                        + "Price = ?, PhoneType = ?, PhoneModel= ?, OperatingSystem = ? WHERE PhoneID = ?";

  public void update (Phone phone) {
    try (Connection con = getConnection();
           PreparedStatement stmt = con.prepareStatement(SQL_UPDATE)) {
        stmt.setString(1, phone.getItemCode());
        stmt.setString(2, phone.getCategories());
        stmt.setString(3, Integer.toString(phone.getQuantity()));
        stmt.setString(4, Double.toString(phone.getPrice()));
        stmt.setString(5, phone.getPhoneType());
        stmt.setString(6, phone.getModel());
        stmt.setString(7, phone.getOperatingSystem());
        stmt.setString(8, phone.getPhoneId());
        stmt.executeUpdate();
    } catch (SQLException e) {
        throw new DaoException("updating phone[" + phone.getPhoneId() + "] failed", e);
    }
  }
}

And then the ItemStorage class will have an instance of the appropriate ItemDao class.

Try to incorporate some of these designs into your code. I don't know of course what your application requires, but you should get some ideas and apply them to your code. This will give you a better design and that results in better readibility, maintainability and reusability. It will also makes it easier to debug your code.

Hope it helps!
Kind regards,
Roel

[Roel De Nijs]

And you should probably add a service layer as well if you need several calls to the database which are executed as one transaction.

And your package names are violating the naming conventions and are not very descriptive. What do you think about?

com.choong.phoneapp.cache
com.choong.phoneapp.dao
com.choong.phoneapp.dao.impl
com.choong.phoneapp.service
com.choong.phoneapp.service.impl
com.choong.phoneapp.ui