How to populate a tableview from two mysql tables which are in same database in javaFx?

Question

I have a mySQL database which name is "Students". It have two tables. First one is "Student Details" table and another one is "Student Marks" table. So i have two model classes for each tables.

"Student Details" table Columns - stuId, name, address, contact

"Student Marks" table columns - markId, stuId, subId, marks

i already design tableview from scenebuilder and which have columns following

• stuId

• name

• totalMarks

So i want to populate above tableview like that. But student name and marks details are in different tables. please someone can give me a sample programme to do above activity? I use netBeans IDE.... Here is my DBConnection Class....

package dbConnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBConnection {
    private static DBConnection dbConnection;
    private final Connection conn;

    private DBConnection() throws ClassNotFoundException, SQLException{
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection("jdbc:mysql://localhost/Students","root","xxxx");
    }

    public static DBConnection createConnection() throws ClassNotFoundException, SQLException{
        if(dbConnection == null){
            dbConnection = new DBConnection();
        }
        return dbConnection;
    }

    public Connection getConnection(){
        return conn;
    }
}

Answer 1

You have to use Join in your SQL query.

SELECT StudentDetails.stuid,StudentDetails.name,StudentMarks.marks
FROM StudentDetails
INNER JOIN StudentMarks
ON StudentDetails.stuid = StudentMarks.stuid;

More about SQL Joins in this link