Java Program to Join Contents of More than One Table & Display in JDBC

Java supports many databases and for each database, we need to have their respective jar files to be placed in the build path to proceed for JDBC connectivity. First, need to decide, which database we are using and accordingly, we need to add the jars. For other databases like Progress, Cassandra, etc also we have jars and need to include them in the build path. There are different kinds of joins available in MySQL and depends upon the requirement, we can frame queries.

Join is a join that provides the facility to connect two tables are merged with each other according to a field that is common and creates a new virtual table.

• NATURAL JOIN: It is a type of join that retrieves data within specified tables to a specific field that is matched.
• NATURAL LEFT JOIN: In this operation, both tables are merged with each other according to common fields but the priority is given to the first table in the database.
• NATURAL RIGHT JOIN: It also the same as Natural left join but it retrieves the data from the second table in the database.

MySQL tables that are used in code:

First table

CREATE TABLE `studentsdetails` (
 `id` int(6) unsigned NOT NULL,
 `Name` varchar(50) NOT NULL,
 `caste` varchar(10) NOT NULL,
 `NeetMarks` int(11) NOT NULL,
 `gender` varchar(10) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Second table

CREATE TABLE `studentspersonaldetails` (
 `id` int(6) unsigned NOT NULL AUTO_INCREMENT,
 `Name` varchar(30) NOT NULL,
 `Address` varchar(30) NOT NULL,
 `email` varchar(50) DEFAULT NULL,
 `reg_date` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

In both tables, “Name” is the common column. 1st table specifies Gender, NeetMarks, Caste, etc whereas 2nd table specifies the address, email, etc. Now only necessity is to create an SQL query to join the table which is as follows:

SELECT * FROM " + "studentsdetails" + " NATURAL JOIN " + "studentspersonaldetails"

Now as this query is executed it retrieves data within specified tables to a specific field is matched, it will match records in both tables depends upon “Name” column, implementing the Natural join concept in play. Now the program depends upon the data present in both tables and for matching values of “Name” column in both tables to get the desired output.

Implementation: Now executing the above query command with the help of the below program as per Natural Join.

Java

// Java Program to Join Contents
// of More than One Table & Display in JDBC

// Step 1: Importing DB files

// Provides the API for accessing and processing
// data stored in a data source
import java.sql.*;

// Class for Joining of multiple tables
public class GFG {

    // Main driver method
    public static void main(String[] args)
    {

        // Display message
        System.out.println(
            "Joining 2 MySQL tables using Natural Join");

        // DB 'Connection' object of Connection class
        Connection con = null;

        // Try block to check exceptions
        try {
            // Step 2: Load and register drivers

            // Loading driver
            // Jars(relevant) or mysql-connector-java-8.0.22
            // in build path of project
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Registering driver

            // test is database name here
            // serverTimezone=UTC, if not provided we will
            // have java.sql.SQLException
            // Credentials here are root/""
            // i.e. username is root
            // password is ""

            // Step 3: Establishing a connection
            con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/test?serverTimezone=UTC",
                "root", "");

            // Try block to check java exceptions
            try {

                // Step 4: Write a statement
                // Join
                Statement st = con.createStatement();
                // Combining two tables in query using
                // NATURAL JOIN studentsdetails columns :
                // Name,caste,NeetMarks,gender
                // studentspersonaldetails columns :
                // Name,Address,email

                // In both tables, connecting columns are
                // Name is taken Here res will have the
                // data from
                // both studentsdetails and
                // studentspersonaldetails whenever "Name"
                // in both tables are matched join
                ResultSet res = st.executeQuery(
                    "SELECT *FROM "
                    + "studentsdetails"
                    + " NATURAL JOIN "
                    + "studentspersonaldetails");

                // Step 5: Execute the query
                System.out.println("   StuName"
                                   + "    Gender"
                                   + "     Caste  "
                                   + "Neet Marks"
                                   + "     Email");

                // Step 6: Process the statements
                // Iterate the resultset and retrieve the
                // required fields
                while (res.next()) {
                    String name = res.getString("Name");
                    String gender = res.getString("gender");
                    String caste = res.getString("caste");
                    String neetMarks
                        = res.getString("NeetMarks");
                    String email = res.getString("email");

                    // Beautification of output
                    System.out.format(
                        "%10s%10s%10s%10s%20s\n", name,
                        gender, caste, neetMarks, email);
                }

                // Step 7: Close the connection
                con.close();
            }

            // Catch block to handle DB exceptions
            catch (SQLException s) {

                // If there is error in SQL query, this
                // exception occurs
                System.out.println(
                    "SQL statement is not executed!");
            }

            // Catch block to handle generic java
            // exceptions
        }
        catch (Exception e) {

            // General exception apart from SQLException are
            // caught here
            e.printStackTrace();
        }
    }
}

Output :

Similarly, we can use the rest of the other joins too in the SQL query. Join and Natural join alone makes columns matching in both tables and display data from both tables. As per the requirements, for

1. Natural left join: Priority goes to the first table.
2. Natural right join: Priority goes to the second table.
   

For different servers, there are different jar files used. 

For SQL

Step 1: Load the driver class

jar to be used: sqljdbc4.jar

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

Step 2: Create a connection for which the connection string “HOSP_SQL1.company.com” is a user-defined one. Similarly, we can use username, password, the database can be used as shown

Connection conn = DriverManager.getConnection(“jdbc:sqlserver://HOSP_SQL1.company.com;user=name;password=abcdefg;database=Test”);

For Oracle

Step 1: Load the driver class

jar to be used: ojdbc14.jar

Class.forName("oracle.jdbc.driver.OracleDriver");

Step 2: Create a connection object followed by username and password

Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");