Using JOINING Operation in JDBC

Whenever we need to select data from two or more tables, we have to perform a join operation. Tables in a database can be related to each other with keys. A primary key is a column with a unique value for each row. Each primary key value must be unique within the table. We will use two related tables Student and testing, to use JOIN. The SQL JOIN clause is used to retrieve data from two or more tables joined by same fields. The most common thing is a primary key from one table matches a foreign key in another table. Consider the SQL JOIN statement below:

SELECT * FROM Student s,testing t where s.Name=t.name;

TYPE OF JOINS
There are two types of SQL JOINS �?? INNER and OUTER Joins. By default it takes INNER JOIN. The INNER JOIN will select all rows from both tables as match between the columns. In case we have a students in the Student table, this student will not be listed in the result of our SQL query above.

SQL �?? INNER JOIN
The join clause binds columns of both the tables to create a single table. Join matches the columns in both tables. There are two types of join statements, inner and outer joins. An inner join returns all rows that result in a match such as the example above.

SQL �?? OUTER JOIN
This join clause has two parts.

1.SQL �?? LEFT OUTER JOIN
A Left outer join returns all rows of the left of the conditional even if there is no right column to match.

2.SQL �?? RIGHT OUTER JOIN
A right outer join will display rows on the right side of the conditional that may or may not have a match.

This program is about to join the two tables Student and testing which has the Name and name columns respectively. Which data will match with the tables it will retrieve. Like the Name of Student table and name of testing will match the data and retrieve the all matched data.

JoinPro.java

import java.sql.*;
  public class JoinPro {
  public static void main(String a[])
  {
 Connection con = null;
 String url = "jdbc:mysql://localhost:3306/";
 String dbName = "vineej";
 String driver = "com.mysql.jdbc.Driver";
 String userName = "vineej";
 String password = "no";
 try {
  Class.forName(driver).newInstance();
  con = DriverManager.getConnection(url+dbName,userName,password);
  Statement st = con.createStatement();
  ResultSet rs = st.executeQuery("select * 
from Student s,testing t where s.Name=t.name");
  while( rs.next() ) {
  String data = rs.getString(1);
  System.out.println( data );
  }
  System.out.println("Results");
  st.close();
  }
  catch( Exception e ) {
  System.out.println(e.getMessage());
  e.printStackTrace();
  }
  }
}

Describe the program
In this program we used how we retreive the data from two or more tables with the JDBC. Student and testing is the name of tables and Name and name are the columns of tables.In the above program see s.Name and t.name, here s and t are the two aliases of tables we have created. They represents the tables at the time of joining.

Coding steps of the program
1.First in the program we import the java.sql and java.io packages then define the SelectState class.

import java.sql.*;

2.Create a connection with the MySql database.

con = DriverManager.getConnection(url+dbName,userName,password);

3.Create statement and then execute query with the SELECT statement.

Statement st = con.createStatement();
ResultSet rs = st.executeQuery("select * from Student s,testing t where s.Name=t.name");

4.Finally got the result by the getString() method.

while( rs.next() ) {
String data = rs.getString(1);
System.out.println( data );
}

5.After all connection should be closed.

con.close();

6.printStackTrace() method.
The method is used to show error messages. If the connection is not connected then it throws the exception and print the message.

Download Source Code

Using JOINING Operation in JDBC

Using JOINING Operation in JDBC

Using JOINING Operation in JDBC

Tutorials