Java JDBC with Join Queries Example

In this Tutorial, we will take a look at Simple Java SE JDBC Connection with PreparedStatement performing SQL Join Queries.

Database Tables:

students

CREATE TABLE `students` (
  `student_id` int NOT NULL AUTO_INCREMENT,
  `student_name` varchar(45) NOT NULL,
  `student_dob` datetime NOT NULL,
  `student_address` varchar(45) NOT NULL,
  PRIMARY KEY (`student_id`,`student_dob`);

student_marks

CREATE TABLE `student_marks` (
  `marks_id` int NOT NULL,
  `student_id` int NOT NULL,
  `marks_english` decimal(4,2) NOT NULL,
  `marks_math` decimal(4,2) NOT NULL,
  `marks_science` decimal(4,2) NOT NULL,
  KEY `student_id_idx` (`student_id`),
  CONSTRAINT `student_id` 
FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`);

select * from students;
+------------+--------------+---------------------+-----------------+
| student_id | student_name | student_dob         | student_address |
+------------+--------------+---------------------+-----------------+
|          1 | Mike         | 2001-07-22 00:00:00 | New York City   |
|          2 | Alex         | 2002-08-13 00:00:00 | Chicago         |
|          3 | Sam          | 2022-01-25 00:00:00 | Ohio            |
+------------+--------------+---------------------+-----------------+

select * from student_marks;
+----------+------------+---------------+------------+---------------+
| marks_id | student_id | marks_english | marks_math | marks_science |
+----------+------------+---------------+------------+---------------+
|        1 |          1 |         45.00 |      55.00 |         87.00 |
|        2 |          2 |         49.50 |      87.50 |         53.00 |
|        3 |          3 |         60.00 |      65.50 |         74.00 |
+----------+------------+---------------+------------+---------------+

Example with LEFT JOIN query

Query:

SELECT 
    s.student_name,
    m.marks_english,
    m.marks_math,
    m.marks_science
FROM
    my_uat.students s
    LEFT JOIN my_uat.student_marks m
    ON s.student_id = m.student_id;

Java JDBC Example:

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

/**
 * Java SE JDBC Select Query with
 * Join Examples
 */
public class JavaJDBCJoinExample {

    private static String MYSQL_JDBC_DRIVER_CLASS = "com.mysql.cj.jdbc.Driver";
    private static String MYSQL_DB_URL = "jdbc:mysql://localhost:3306/my_uat";
    private static String MYSQL_DB_USER = "root";
    private static String MYSQL_DB_USER_PASSWORD = "root123";

    //SQL Select Statement Query Example with Java JDBC
    private static String SQL_JOIN_QUERY = 
    "SELECT "+
    "s.student_name, "+
    "m.marks_english, "+
    "m.marks_math, "+
    "m.marks_science "+
    "FROM students s "+
    "LEFT JOIN student_marks m "+
    "ON s.student_id = m.student_id;";


    public static void main(String[] args) {
            
        try(Connection connection = DriverManager.getConnection(MYSQL_DB_URL,MYSQL_DB_USER,MYSQL_DB_USER_PASSWORD)) {

            try {
                Class.forName(MYSQL_JDBC_DRIVER_CLASS);
            } catch (ClassNotFoundException e) {
                System.out.println("Database Vendor Driver class not found!");
                e.printStackTrace();
            }

            PreparedStatement statement = connection.prepareStatement(SQL_JOIN_QUERY);
            ResultSet resultSet = statement.executeQuery(); 

            while(resultSet.next())  {
                System.out.println(resultSet.getString(1)+"  "+
                resultSet.getDouble(2)+"  "+
                resultSet.getDouble(3)+"  "+
                resultSet.getDouble(4));
            }     
                
        } catch (SQLException e) {
            System.out.println("Error occured while executing query: " + SQL_JOIN_QUERY);
            e.printStackTrace();
        } 
    }

}

Output:

Mike  45.0  55.0  87.0
Alex  49.5  87.5  53.0
Sam  60.0  65.5  74.0

+--------------+---------------+------------+---------------+
| student_name | marks_english | marks_math | marks_science |
+--------------+---------------+------------+---------------+
| Mike         |         45.00 |      55.00 |         87.00 |
| Alex         |         49.50 |      87.50 |         53.00 |
| Sam          |         60.00 |      65.50 |         74.00 |
+--------------+---------------+------------+---------------+

Similary you may use RIGHT or FULL JOIN,

Query RIGHT JOIN

SELECT 
    s.student_name,
    m.marks_english,
    m.marks_math,
    m.marks_science
FROM
    students s
    RIGHT JOIN student_marks m
    ON s.student_id = m.student_id;