I have this project written in java. I want this thing show data from different table in 1 display.

Here's the java code

public class teacherdata {

    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost/uts";
    static final String USER = "root";
    static final String PASS = "";

    static Connection conn;
    static Statement stmt;
    static InputStreamReader inputStreamReader = new InputStreamReader(System.in);
    static BufferedReader input = new BufferedReader(inputStreamReader);

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        try {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            while (!conn.isClosed()) {
                showPersonality();
            }
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    static void showPersonality() {
        try {
            Statement stmt1 = conn.createStatement();
            ResultSet rs1 = stmt1.executeQuery("SELECT * FROM "   "faculty"   " NATURAL JOIN "   "teacher");

            while (rs1.next()) {
                String facultyName = rs1.getString("faculty_name");
                String teacherName = rs1.getString("teacher_name");
                System.out.println(String.format("| %s | %s |", facultyName, teacherName));                 
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

And I also made 2 table which is faculty and teacher

Faculty table (I inserted 1 data)

CREATE TABLE faculty(
    id INT NOT NULL AUTO_INCREMENT,
    faculty_name VARCHAR(100) NOT NULL,
    PRIMARY KEY(id)
);

Teacher table (I inserted 2 data with 1 faculty)

CREATE TABLE `teacher` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(255) NOT NULL,
  `faculty_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `faculty_id` (`faculty_id`),
  CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`faculty_id`) REFERENCES `faculty` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
)

After I execute the code, it only shows 1 data which is from first teacher_id.

Is there anything wrong with my java code or maybe my database?

CodePudding user response：

The problem is you used a NATURAL JOIN which by default creates an implicit join by combining tables based on columns with the SAME NAME and DATA TYPE. So instead of join by using the faculty id, it joins using faculty_id = teacher_id . Try using an explicit join in your code:

SELECT * FROM  faculty f  JOIN teacher t on f.id=t.faculty_id ;

CodePudding user response：

Another way is to use RIGHT JOIN in your query to show each column from every table you use:

SELECT
    t.teacher_name,
    f.faculty_name
FROM
    faculty f
RIGHT JOIN teacher t 
    ON f.id = t.faculty_id

With this data:

INSERT INTO faculty (id, faculty_name) VALUES (1, "Faculty #1");
INSERT INTO faculty (id, faculty_name) VALUES (2, "Faculty #2");
INSERT INTO faculty (id, faculty_name) VALUES (3, "Faculty #3");

INSERT INTO teacher (id, teacher_name, faculty_id) VALUES (1, 'Teacher Name #1', 3);
INSERT INTO teacher (id, teacher_name, faculty_id) VALUES (2, 'Teacher Name #2', 1);
INSERT INTO teacher (id, teacher_name, faculty_id) VALUES (3, 'Teacher Name #3', 3);
INSERT INTO teacher (id, teacher_name, faculty_id) VALUES (4, 'Teacher Name #4', 1);
INSERT INTO teacher (id, teacher_name, faculty_id) VALUES (5, 'Teacher Name #5', 1);

It returns:

db<>fiddle