Linking two tables in JDBC

[Bob Thorton]

Got a Java program that has two tables in MS Access DB. It's a Customers database with a Customers table and a Orders table. Customers: CustomerName, CustomerID, Address, City, State, Zip, PhoneNumber. / Orders: ID, ProductName, OrderNumber, Quantity, UnitCost.

In my program I need to be able to pull up a users ID with their Personal Information and also their Order History. I have this somewhat but its not linking properly, or I am not linking it properly in my statements. I am trying to figure out if anyone can help me with what statement(s) i should be specifically using.

It will display the CustomerID in the CustomerID field, but the CustID doesnt match the ID in the orders and it doesnt link the correct personal info to the order info.

Might be confusing but here's a snippet of the code, if you need the full thing let me know.

                
  conCustomers = DriverManager.getConnection(dbURL);
                }

                catch(SQLException error)
                {
                  System.err.println("Unable to Connect to Database");
                }

                try
                {
                Statement cmdCustomers = conCustomers.createStatement();
                //Create the ResultSet
                ResultSet rsCustomers = cmdCustomers.executeQuery("Select * from Customers");
					 //ResultSet rsCustomers = cmdCustomers.executeQuery("Select * from Orders");
                loadNames(rsCustomers);
                blnSuccessfulOpen = true;
                }
                catch(SQLException error)
                {
                        System.err.println("Error in recordset");
                }
        }

         public void loadNames(ResultSet rsCustomers)
         {
                //Fill ID list box
                try
                {
                        while(rsCustomers.next())
                                lstNames.add(rsCustomers.getString("CustomerID"));
                }
                catch (SQLException error)
                {
                        System.err.println("Error in Display Record");
                }
         }

        public void itemStateChanged(ItemEvent event)
        {
                //Retrieve and display the selected record
                String strCustomerID =  lstNames.getSelectedItem();

                showStatus(""); //Delete instructions
                try
                {
                        Statement cmdCustomers = conCustomers.createStatement();
                        //rsCustomers = cmdCustomers.executeQuery( "Select * from Customers where CustomerID = '" + strCustomerID + "';");
								rsCustomers = cmdCustomers.executeQuery( "Select * from Customers, Orders where CustomerID = Orders.ID");
                       // "Select * from Orders where CustomerID = '" + strCustomerID + "';");
                        txtCustomerID.setText(strCustomerID);
                        displayRecord(rsCustomers);
                        setTextToEditable();
                }
                catch(SQLException error)
                {
                        showStatus("Error in recordset");
                }
         }

         public void displayRecord(ResultSet rsCustomers)
         {
                //Display the current record
                try
                {
                        if(rsCustomers.next())
                        {
                                //txtFirstName.setText(rsCustomers.getString("CustomerName"));
                                txtCustomerName.setText(rsCustomers.getString("CustomerName"));
                                txtAddress.setText(rsCustomers.getString("Address"));
                                txtCity.setText(rsCustomers.getString("City"));
                                txtState.setText(rsCustomers.getString("State"));
                                txtZip.setText(rsCustomers.getString("Zip"));
                                txtPhone.setText(rsCustomers.getString("PhoneNumber"));
										  txtProductName.setText(rsCustomers.getString("ProductName"));
	 									  txtOrderNumber.setText(rsCustomers.getString("OrderNumber"));
										 txtQuantity.setText(rsCustomers.getString("Quantity"));
	 										txtUnitCost.setText(rsCustomers.getString("UnitCost"));
	 								
                                showStatus("");
                        }
                        else
                        {
                                showStatus("Record not found");
                                ClearTextFields();
                        }
                }
                catch (SQLException error)
                {
                        showStatus("Error in display record");
                }
         }

[Amit ChaudhariC]

Hi,

I would suggest you to read a jdbc tutorial as i feel some basic stuff like closing the statement , resultset ,connection is missing from this code.

1)Do you want to fetch only the customerIds from the customer table , change the query accordingly.
2)Why are you passing the ResultSet object to the next method , if you want to add those customerIds to the list , do it after you get the resultset.

Please let us know the relationship between the 2 tables and will try to help out.

Regards,
Amit

[Vijitha Kumara]

Welcome to CodeRanch, BillyBob Thorton!

It will display the CustomerID in the CustomerID field, but the CustID doesnt match the ID in the orders and it doesnt link the correct personal info to the order info.

Is this by design or something in your query? Can you elaborate this a bit? BTW, are you talking about the "itemStateChanged()" method?

[Bob Thorton]

The relationship is the CustomerID should link the Customers Personal Information to their Order History ID in another table.

I need to be able to select a CustomerID from the listbox which will display that customerID's personal information along with their order history.

And as far as the other closing statements and what not, that is not the full code.


** Removed Code **

and yeah its in the itemStateChanged() method... I believe I either have my relationship set up wrong or my sql statements are in correct.

[Bob Thorton]

Should I be having a customerID field in each Customer and Orders table to link them? Right now I have a CustomerID in Customers Table that is supposed to link ID field in the Orders Table, not sure if this is appropriate set-up or not. Ive deal with other programs with access multiple tables but I cant figure out how to link information from the two.

I need to be able to select a CustomerID from the Java GUI and then that will display the personal info from the Customers table and also the Order history from the Orders table that corresponds with the ID.

Let me know if this help

[Vijitha Kumara]

BillyBob Thorton wrote:...I need to be able to select a CustomerID from the Java GUI and then that will display the personal info from the Customers table and also the Order history from the Orders table that corresponds with the ID...

Ok, then you need a foreign key to the customer table from where you have stored the Order details for the customers.

[Paul Clapham]

BillyBob Thorton wrote:Should I be having a customerID field in each Customer and Orders table to link them? Right now I have a CustomerID in Customers Table that is supposed to link ID field in the Orders Table, not sure if this is appropriate set-up or not.

First of all, if you thought posting 500 lines of code would help to explain that question, let me say that it was completely unhelpful.

As for that question, yes, you should have something in the Order table to say what customer the order is for. If that's your ID column, then your query should select records where Customer.CustomerID = Order.ID. However if the ID column is an order number and not the customer number, then that join will produce random data at best.

[Bob Thorton]

Didnt know if it would help the previous person who was helping or not. Sorry.

Yeah the CustomerID in Customers table and ID in Orders table are the unique #'s to match the Orders. OrderNumbers is something separate.
For instance

Customers Table:

CustomerName: Billy Bob
CustomerID: 2
Address: Blah Blah Dr
City: Johnsburg
State: FL
Zip: 64533
Phone: [phone]

Orders Table:

ID: 2
Product: Lumber
OrderNumber: 35532
Quantity: 2,500
Unit Cost: 12.50

Is this what you all mean or do I need another Identifer to link them?

[Paul Clapham]

You need the customer number in the order somewhere. Then you need to match the customer number in the customer table to the customer number in the order table. That's all. No rocket science involved.

It looks like that's what you have. But you think it isn't working? Well, that 500 lines of code is far too large to test with. Not to mention there's a whole lot of code which is unrelated to that. So put that big program aside and write about 50 lines of code which tests what you want to test.

[Bob Thorton]

Ok. Guess Ill have to try that, appreciate that insight.

[Bob Thorton]

3

[Amit ChaudhariC]

So say I select CustID: #2 from the drop box. Instead of displaying CustID #2 it will display CustID #3 Personal Information and with that CustID it will link to ID: 3 in the Orders table and display that correct Order info..

Please post the sqls /code that are fired in the above case?

Regards,
Amit

[Bob Thorton]

1

[Bob Thorton]

Anyone else got any ideas?

[Paul Clapham]

Any ideas? My idea would be to take that SQL statement which you posted, and see what it returns. Is it correct data? Wrong data? I'm just shooting in the dark because at this point I have no idea what you think your problem is or what you have done to work on it. You just posted an SQL query with no explanation.

And were you under the impression that the query you posted would return data for only one customer? Earlier your problem seemed to be that you were getting data for the wrong customer, or something like that, but the query you posted should return data for all customers.

[Bob Thorton]

2

[Paul Clapham]

Okay, so it looks like your SQL is fine but your code for putting in the chosen customer number isn't.