Wednesday, 11 December 2013

jdbc program using joins

//jdbc program using joins
/*
 * CREATE TABLE `user` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`course` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
table2s name course
CREATE TABLE `course` (
`id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

step 3:
ALTER TABLE `user`
ADD CONSTRAINT `FK_course`
FOREIGN KEY (`course`) REFERENCES `course` (`id`)
ON UPDATE CASCADE;


 */

import java.sql.Connection;
import java.sql.Driver;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;


public class Joins {

public static void main(String s[]) throws Exception {

Driver d= (Driver) ( Class.forName(
"com.mysql.jdbc.Driver").newInstance());

Properties p=new Properties ();
p.put("user","root");
p.put("password","admin");

Connection con=d.connect(
"jdbc:mysql://localhost:3306/test",p);

Statement st= con.createStatement(
ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
//INNER JOIN (or just JOIN)
// String query="SELECT user.name, course.name FROM `user` INNER JOIN `course` on user.course = course.id;";
//LEFT JOIN
String query="SELECT user.name, course.name FROM `user` LEFT JOIN `course` on user.course = course.id;";
//
/*
SELECT user.name, course.name
FROM `user`
LEFT JOIN `course` on user.course = course.id

UNION

SELECT user.name, course.name
FROM `user`
RIGHT JOIN `course` on user.course = course.id;

*/

ResultSet rs= st.executeQuery(query);
//Now the cursor of resultset will at beforeFirst & the result set produced is scrollable

System.out.println("EmpNo\tName");

while (rs.next()) {
System.out.println(rs.getString(1)+"\t");
System.out.println(rs.getString(2));

}//while
//Now the cursor of resultset will at afterLast

System.out.println("Reading Data, moving the cursor in backward direction\n");

while (rs.previous()){
System.out.println(rs.getString(1)+"\t");
System.out.println(rs.getString(2));

}//while
con.close();
}//main


}

No comments:

Post a Comment