//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
}
/*
* 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