*
The moose likes JDBC and the fly likes Linking two tables in JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Linking two tables in JDBC" Watch "Linking two tables in JDBC" New topic
Author

Linking two tables in JDBC

Bob Thorton
Greenhorn

Joined: Apr 25, 2011
Posts: 12

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.

Amit ChaudhariC
Ranch Hand

Joined: Aug 06, 2009
Posts: 33
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
Bartender

Joined: Mar 24, 2008
Posts: 3817

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?


SCJP 5 | SCWCD 5
[How to ask questions] [Twitter]
Bob Thorton
Greenhorn

Joined: Apr 25, 2011
Posts: 12

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
Greenhorn

Joined: Apr 25, 2011
Posts: 12

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
Bartender

Joined: Mar 24, 2008
Posts: 3817

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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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
Greenhorn

Joined: Apr 25, 2011
Posts: 12

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: 444-232-4242

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
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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
Greenhorn

Joined: Apr 25, 2011
Posts: 12

Ok. Guess Ill have to try that, appreciate that insight.
Bob Thorton
Greenhorn

Joined: Apr 25, 2011
Posts: 12

3
Amit ChaudhariC
Ranch Hand

Joined: Aug 06, 2009
Posts: 33
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
Greenhorn

Joined: Apr 25, 2011
Posts: 12

1
Bob Thorton
Greenhorn

Joined: Apr 25, 2011
Posts: 12

Anyone else got any ideas?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

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
Greenhorn

Joined: Apr 25, 2011
Posts: 12

2
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

Okay, so it looks like your SQL is fine but your code for putting in the chosen customer number isn't.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Linking two tables in JDBC
 
Similar Threads
how to run code in netbeans
help
Table data does not appear after inserting it to the JTable
Problem executing SQL in SERVLET.
J2ME connecting database in Server using Servlet