Join Example in JDBC
Student.java
public class Student {
private int roll;
private String name;
private float marks;
private Address address;
private Certificate[] certificates;
public int getRoll() {
return roll;
}
public void setRoll(int roll) {
this.roll = roll;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public float getMarks() {
return marks;
}
public void setMarks(float marks) {
this.marks = marks;
}
public Address getAddress() {
return address;
}
public void setAddress(Address address) {
this.address = address;
}
public Certificate[] getCertificates() {
return certificates;
}
public void setCertificates(Certificate[] certificates) {
this.certificates = certificates;
}
}
Address.java
public class Address {
private String name;
private int pincode;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getPincode() {
return pincode;
}
public void setPincode(int pincode) {
this.pincode = pincode;
}
}
Certificate.java
public class Certificate {
private String name;
private String year;
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public String getYear() {
return year;
}
public void setYear(String year) {
this.year = year;
}
}
StudentDaoJoinQuery.java
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class StudentDaoJoinQuery {
public int insert(Student std) {
int callback = 0;
try (Connection con = Dao.getMyConnection();) {
PreparedStatement pstudent = con.prepareStatement(
"insert into student values(?,?,?)");
pstudent.setInt(1, std.getRoll());
pstudent.setString(2, std.getName());
pstudent.setFloat(3, std.getMarks());
PreparedStatement paddress = con.prepareStatement(
"insert into address values(?,?,?)");
paddress.setInt(1, std.getRoll());
paddress.setString(2, std.getAddress().getName());
paddress.setInt(3, std.getAddress().getPincode());
Certificate[] certificates = std.getCertificates();
for (int i = 0; i < certificates.length; i++) {
Certificate certificate = certificates[i];
PreparedStatement pcertificate = con.prepareStatement(
"insert into certificate values(?,?,?)");
pcertificate.setInt(1, std.getRoll());
pcertificate.setString(2, certificate.getName());
pcertificate.setString(3, certificate.getYear());
int pc = pcertificate.executeUpdate();
}
paddress.executeUpdate();
pstudent.executeUpdate();
callback = 1;
} catch (Exception e) {
System.out.println(e);
}
return callback;
}
public void innerJoin() {
try (Connection con = Dao.getMyConnection();) {
PreparedStatement ps = con.prepareStatement(
"select certificate.name,certificate.year,student.name FROM certificate
INNER JOIN student ON certificate.roll=student.roll");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String cname = rs.getString(1);
String cyear = rs.getString(2);
String sname = rs.getString(3);
System.out.println(cname + " " + cyear + " " + sname);
}
} catch (Exception e) {
System.out.println(e);
}
}
public void leftJoin() {
try (Connection con = Dao.getMyConnection();) {
PreparedStatement ps = con.prepareStatement(
"select certificate.name,certificate.year,student.name FROM
certificate LEFT JOIN student ON certificate.roll=student.roll");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String cname = rs.getString(1);
String cyear = rs.getString(2);
String sname = rs.getString(3);
System.out.println(cname + " " + cyear + " " + sname);
}
} catch (Exception e) {
System.out.println(e);
}
}
public void rightJoin() {
try (Connection con = Dao.getMyConnection();) {
PreparedStatement ps = con.prepareStatement(
"select certificate.name,certificate.year,student.name FROM
certificate RIGHT JOIN student ON certificate.roll=student.roll");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
String cname = rs.getString(1);
String cyear = rs.getString(2);
String sname = rs.getString(3);
System.out.println(cname + " " + cyear + " " + sname);
}
} catch (Exception e) {
System.out.println(e);
}
}
}
Dao.java
import java.sql.Connection;
import java.sql.DriverManager;
public class Dao {
private static Connection con;
public static Connection getMyConnection() {
try {
Class.forName("com.mysql.jdbc.Driver");
con = DriverManager.getConnection("jdbc:mysql://localhost:
3306/joinexample", "root", "root");
} catch (Exception e) {
System.out.println(e);
}
return con;
}
}
mysql> use joinexample;
mysql> desc address;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| roll | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| pincode | int(11) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| roll | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
| marks | float | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)
mysql> desc certificate;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| roll | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| year | varchar(5) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
No comments:
Post a Comment