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(); |
4.Finally got the result by the getString() method.
|
while( rs.next() ) { |
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.