Using JOINING Operation in JDBC

Whenever we need to select data from two or more tables, we have to perform a join operation.


 

Ads

Tutorials   
JDBC Video Tutorial: Create, Read, Update and Delete (CRUD) Operation examples JDBC Video tutorial - How to read Data from MySQL Database? JDBC Video tutorial - How to delete Data in a MySQL Database? Solving java.lang.ClassNotFoundException: com.mysql.jdbc.Driver exception JDBC Video tutorial - How to update Data in a MySQL Database? JDBC Video Tutorial- How to insert data into MySQL database table? JDBC Video Tutorial: How to download JDBC Driver for MySQL? JDBC Tutorial - Writing first JDBC example and running in Eclipse Java Cross Join MySQL Database Connectivity Example In Java Change Column Name in MySQL Java Mysql Connection Example JDBC ODBC Connection In Java ResultSet In Java Prepared Statement Example JdbcRowSet interface Java JdbcRowSet Example First Step towards JDBC! JDBC Training, Learn JDBC yourself Jdbc Get Column Names JDBC Example with MySQL JDBC Prepared Statement Update JDBC Prepared Statement Insert JDBC Prepared Statement Example JDBC Prepared statement Close JDBC Prepared Statement Addbatch JDBC Nested Resultset JDBC-Odbc Connection JDBC-Odbc Connectivity JDBC Insert Preparedstatement JDBC Insert Null JDBC Get Metadata JDBC Insert Record Jdbc Insert Statement JDBC Meta Data Get tables Jdbc Mysql Connection String Jdbc Mysql Connection Url JDBC Next Jdbc Get Connection JDBC Get Int JDBC Exception JDBC Get Row Count JDBC Functions JDBC Fetch JDBC Execute Update Example JDBC Execute Statement JDBC Execute Query JDBC Drive For Mysql JDBC Connectivity Code In Java JDBC connection timeout
Ads

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

Advertisements

Share on Google+Share on Google+

Using JOINING Operation in JDBC

Posted on: June 27, 2008 If you enjoyed this post then why not add us on Google+? Add us to your Circles

Advertisements

Ads
 

Discuss: Using JOINING Operation in JDBC  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:0

Ads

 

Ads