Fill JTable with data from two mysql database table using the table relation to match the data.

[Jomar Reyes]

I have a JTable with 6 columns, the first 3 columns come from table1(subject_records_bsit_first_year) and the last 3 will come from table2(evaluation_permission). The two databasetable have two joined columns(subject_code and subject_title). I'm trying to fill the last 3 columns of my JTable with data from table2—using the db table connection to display the right data.

My code:

public static void loadFirstYearSubjectList() {
		
		ConnectionManager connectionManager = new ConnectionManager();
		//String sqlChooseStudent = "SELECT * FROM student_bsit_"+studentID.toLowerCase() +" WHERE year_level = '"+yearLevel+"'";
		try {
			Connection con = connectionManager.createConnection();
			Statement stmt = con.createStatement();
			String sql = "SELECT * FROM `subject_records_bsit_first_year` ORDER BY `subject_records_bsit_first_year`.`trimester_period` ASC";
			ResultSet rs = stmt.executeQuery(sql);
			
			DefaultTableModel tableModel = new DefaultTableModel();
			tableModel.addColumn("Subject Code");
			tableModel.addColumn("Subject Title");
			tableModel.addColumn("Trimester");
			tableModel.addColumn("Section");
			tableModel.addColumn("Professor");
			tableModel.addColumn("Evaluation");
			
			table.setModel(tableModel);
			
			TableColumnModel columnModel = table.getColumnModel();
			columnModel.getColumn(0).setPreferredWidth(95);
			columnModel.getColumn(1).setPreferredWidth(266);
			columnModel.getColumn(2).setPreferredWidth(90);
			columnModel.getColumn(3).setPreferredWidth(77);
			columnModel.getColumn(4).setPreferredWidth(179);
			columnModel.getColumn(5).setPreferredWidth(78);
			
			DefaultTableCellRenderer renderer = new DefaultTableCellRenderer();
			renderer.setBackground(new Color(120, 120, 120, 10));
			renderer.setOpaque(true);
			columnModel.getColumn(0).setCellRenderer(renderer);
			columnModel.getColumn(1).setCellRenderer(renderer);
			columnModel.getColumn(2).setCellRenderer(renderer);
			columnModel.getColumn(3).setCellRenderer(renderer);
			columnModel.getColumn(4).setCellRenderer(renderer);
			columnModel.getColumn(5).setCellRenderer(renderer);
			while (rs.next()) {
				String subjectCode = rs.getString("subject_code");
				String subjectTitle = rs.getString("subject_title");
				String trimesterPeriod = rs.getString("trimester_period");
				
				tableModel.addRow(new Object[] {subjectCode, subjectTitle, trimesterPeriod});
			}
			
		} catch (Exception e) {
			
		} finally {
			try {
				connectionManager.closeConnection();
			} catch (Exception e2) {

			}
		}
	}

table 2 data
I'm trying to display the section, subject_teacher and evaluation_permission from this table, this table is joined from another table that already displays the first 3 columns on my jtable. The two db table is joined with the same column names; subject_code and subject_title.

[Paul Clapham]

Welcome to the Ranch!

So... you have one JTable and you want to put data from two database tables into it? Then it seems to me you need to join the two database tables, no? I see you already used the word "join" in your post, but your database query doesn't join the two tables.

[Jomar Reyes]

Paul Clapham wrote:Welcome to the Ranch!

So... you have one JTable and you want to put data from two database tables into it? Then it seems to me you need to join the two database tables, no? I see you already used the word "join" in your post, but your database query doesn't join the two tables.

Yes there is no join statement in my query yet, I've been trying to but so far all efforts failed. I was hoping someone can help me with example.

[Paul Clapham]

An example? Well, here's a tutorial about SQL joins: https://www.w3schools.com/sql/sql_join.asp

Does that help?

[Tim Holloway]

Helpful hint: use try-with-resources on your Connection logic. It makes things simpler, tidier, and more foolproof.

You may be sourcing from multiple database tables, but the JTable doesn't talk to the database directly - it's part of the Model/View/Controller architecture, where the JTable is the View clas and it's actually getting (and possibly setting) data from its associated Model. You define a Model class and populate it from the database, and there's nothing magic about a Model getting data from multiple sources as long as the populate logic puts everything in the proper places.

[Jomar Reyes]

Tim Holloway wrote:Helpful hint: use try-with-resources on your Connection logic. It makes things simpler, tidier, and more foolproof.

You may be sourcing from multiple database tables, but the JTable doesn't talk to the database directly - it's part of the Model/View/Controller architecture, where the JTable is the View clas and it's actually getting (and possibly setting) data from its associated Model. You define a Model class and populate it from the database, and there's nothing magic about a Model getting data from multiple sources as long as the populate logic puts everything in the proper places.

I really can't understand, It gets complicated because the second half of the jtable which will fetch the data from the second database table must use the data fetched from the first database table to match the jtable data using the table relation from db table.

[Paul Clapham]

It's even more complicated when you don't know what columns are in the first table. I'm guessing that you would be joining on student-school-id  and subject-code, but without knowing the contents of the first table it's just a guess. Your JTable headers suggest that subject-code is there, anyway.

[Carey Brown]

Forget about JTable for a bit and  just concentrate on the database tables. MYSQL has a keyword 'describe' that you can use to show you how a table is designed. So for instance you could send MYSQL this queery:
describe subject_records_bsit_first_year;
Now you say there's another database table that you need to join in order to get columns from it. Do the same thing with that table.

Post the results of the two describe's back here.

Then we can talk about how one table relates to the other and come up with the appropriate 'join' syntax.

[Edit]Sorry, didn't see the link in your first post.

[Carey Brown]

Did you design those tables yourself? It seems like some normalization is missing, 'subject_title' for instance, is the really a key or do the values in the two tables just happen to be the same?

[Jomar Reyes]

Paul Clapham wrote:It's even more complicated when you don't know what columns are in the first table. I'm guessing that you would be joining on student-school-id  and subject-code, but without knowing the contents of the first table it's just a guess. Your JTable headers suggest that subject-code is there, anyway.

No, I have idea and I know which db data are going to which jtable column, I just need to know how to put the second db table data to 4th, 5th, and 6th column of my jtable—using the common db data(subject_code or subject_title), both db table contains, but I fetch the subject_code and subject_title from the first table.

[Jomar Reyes]

Carey Brown wrote:Did you design those tables yourself? It seems like some normalization is missing, 'subject_title' for instance, is the really a key or do the values in the two tables just happen to be the same?

Yes I did, but it is my first usage of JTable so I do not really understand how JTableModel works. It used to be a custome JTable but when I connected it to the database I thought using DefaultTableModel is easier so I did. I'm thinking that my problem could be related to my JTableModel and/or their scope.

[Paul Clapham]

No. Your problem is that your JTableModel needs to contain data which comes from joining the two tables. That means that your SQL query must join the two tables appropriately and return the 6 columns which need to go into the JTableModel.

[Jomar Reyes]

Paul Clapham wrote:No. Your problem is that your JTableModel needs to contain data which comes from joining the two tables. That means that your SQL query must join the two tables appropriately and return the 6 columns which need to go into the JTableModel.

But how can I do it in

String sql = "SELECT * FROM `subject_records_bsit_first_year` ORDER BY `subject_records_bsit_first_year`.`trimester_period` ASC; (query for the second table)";

if  the query to fetch the data from the second db table must use the data fetched from the first db table? either of these two. It should be something like this SELECT * FROM (table2) WHERE subject_code = '"++'";

while (rs.next()) {
     String subjectCode = rs.getString("subject_code");
     String subjectTitle = rs.getString("subject_title");

[Carey Brown]

A simple query uses 1 table. A query with a join can use more tables and can also return more columns.

Read the link that was given to you on 'join'.

[Paul Clapham]

Jomar Reyes wrote:But how can I do it in

String sql = "SELECT * FROM `subject_records_bsit_first_year` ORDER BY `subject_records_bsit_first_year`.`trimester_period` ASC; (query for the second table)";

if  the query to fetch the data from the second db table must use the data fetched from the first db table? either of these two. It should be something like this SELECT * FROM (table2) WHERE subject_code = '"++'";

No. You only need one query, and it has to select data from both database tables. It should look like "select a, b, c, d, e, f from table1, table 2 where (join conditions)". The six columns you select can come from either of the two tables, or from both of them if they are used to join the two tables. You do not need one query for each table. Just one query, then use the ResultSet to read those six columns into the six columns of the JTableModel. Only one query.

[Carey Brown]

SELECT t1.subject_code, t1.subject_title, t1.trimester_period,
     t2.section, t2.subject_teacher, t2.evaluation_persmission
FROM subject_records_bsit_first_year t1
INNER JOIN evaluation_permission t2
ON t1.subject_code = t2.subject_code AND t1.subject_title = t2.subject_title

I think that's it. I'm a little rusty.

[Jomar Reyes]

Carey Brown wrote:

SELECT t1.subject_code, t1.subject_title, t1.trimester_period,
     t2.section, t2.subject_teacher, t2.evaluation_persmission
FROM subject_records_bsit_first_year t1
INNER JOIN evaluation_permission t2
ON t1.subject_code = t2.subject_code AND t1.subject_title = t2.subject_title

I think that's it. I'm a little rusty.

Ohh I see, I understand now thank you very much. I didn't realize that I can use the connection directly on the query because I've always use the fetched data from the database as a query condition like WHERE username = '"+usernameTxtfld+"'"; for example, but I understand now, thanks a lot.

[Jomar Reyes]

It worked! https://ibb.co/kmd1HXV

Thank you very much again, this helped me greatly.

[Carey Brown]

Glad I could help. But seriously, read the 'join' link.
And thumbs-up always appreciated.
;)

[Carey Brown]

Also note the use of abbreviated table names 't1' and 't2' (or anything you'd like). Makes things easier to read and maintain.

[Ron McLeod]

Including the image of Jomar's screen cap for those that don't like to click on links: