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:
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;
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.
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
- Natural left join: Priority goes to the first table.
- Natural right join: Priority goes to the second table.
For different servers, there are different jar files used.
Step 1: Load the driver class
jar to be used: sqljdbc4.jar
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”);
Step 1: Load the driver class
jar to be used: ojdbc14.jar
Step 2: Create a connection object followed by username and password
Connection con=DriverManager.getConnection( "jdbc:oracle:thin:@localhost:1521:xe","system","oracle");