WEEK5

WEEK5:
Java Program to get connection with Oracle Database executes SQL Statements and
handling the Result set.
Program:
JDB.java:
package week5;
import javafx.application.Application;
import javafx.event.ActionEvent;
import javafx.event.EventHandler;
import javafx.geometry.HPos;
import javafx.geometry.Orientation;
import javafx.scene.Scene;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.Separator;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;
import javafx.scene.control.TextArea;
import javafx.scene.control.TextField;
import javafx.scene.layout.AnchorPane;
import javafx.scene.layout.GridPane;
import javafx.scene.layout.HBox;
import javafx.scene.layout.StackPane;
import javafx.scene.layout.VBox;
import javafx.stage.Stage;
import java.sql.*;
import javafx.collections.FXCollections;
import javafx.collections.ObservableList;
import javafx.scene.control.cell.PropertyValueFactory;
import javafx.scene.layout.Background;
import javafx.scene.paint.Color;
import javafx.scene.text.Font;
import javafx.scene.text.FontWeight;

public class JDB extends Application {

Connection con;
PreparedStatement stmt;
Statement s;
int rid;
String rname,remail,rphone;

@Override
public void start(Stage primaryStage) {
Label sid=new Label("Id");
Label name=new Label("Name");
Label email=new Label("Email");
Label phone=new Label("Phone");
Label id=new Label("Enter ID");
id.setLayoutX(210.0);
id.setLayoutY(21.0);
Label nemail=new Label("New Email");
nemail.setLayoutX(388.0);
nemail.setLayoutY(21.0);
Label result=new Label("Result Console");

TextField tsid=new TextField();

tsid.setPromptText("id");
TextField tname=new TextField();
tname.setPromptText("Name");
TextField temail=new TextField();
temail.setPromptText("Email");
TextField tphone=new TextField();
tphone.setPromptText("Phone");
TextField tnemail=new TextField();
tnemail.setPromptText("New Email");
tnemail.setLayoutX(388.0);
tnemail.setLayoutY(41.0);
TextField tid=new TextField();
tid.setPromptText("id");
tid.setLayoutX(210.0);
tid.setLayoutY(41.0);

Button add=new Button("ADD RECORD");

add.setLayoutX(100.0);
add.setLayoutY(150.0);
Button search=new Button("SEARCH");
search.setLayoutX(210.0);
search.setLayoutY(70.0);
Button delete=new Button("DELETE");
delete.setLayoutX(352.0);
delete.setLayoutY(70.0);
Button update=new Button("UPDATE");
update.setLayoutX(283.0);
update.setLayoutY(70.0);
Button view=new Button("VIEW ALL");
view.setLayoutX(423.0);
view.setLayoutY(70.0);
TextArea tresult=new TextArea();
tresult.setPrefWidth(180.0);
tresult.setPrefHeight(85.0);

VBox vb1=new VBox();

vb1.getChildren().addAll(sid,name,email,phone);
vb1.setLayoutX(9.0);
vb1.setLayoutY(28.0);
vb1.setSpacing(12.0);
VBox vb2=new VBox();
vb2.getChildren().addAll(tsid,tname,temail,tphone);
vb2.setLayoutX(50.0);
vb2.setLayoutY(24.0);
vb2.setSpacing(4.0);
VBox vb3=new VBox();
vb3.getChildren().addAll(result,tresult);
vb3.setLayoutX(7.0);
vb3.setLayoutY(250.0);

Separator s1=new Separator(Orientation.HORIZONTAL);

s1.setLayoutY(14.0);
s1.setPrefWidth(600.0);
s1.setPrefHeight(4.0);
Separator s2=new Separator(Orientation.VERTICAL);
s2.setLayoutX(200.0);
s2.setLayoutY(14.0);
s2.setPrefWidth(7.0);
s2.setPrefHeight(400.0);

TableView tb=new TableView();

tb.setEditable(true);
tb.setLayoutX(210.0);
tb.setLayoutY(102.0);
tb.setPrefSize(350.0, 250.0);
TableColumn<Integer,Person> tc1=new TableColumn ("ID");
tc1.setPrefWidth(45.0);
tc1.setCellValueFactory(new PropertyValueFactory<>("id"));
TableColumn<String,Person> tc2=new TableColumn("NAME");
tc2.setPrefWidth(100.0);
tc2.setCellValueFactory(new PropertyValueFactory<>("name"));
TableColumn<String,Person> tc3=new TableColumn("EMAIL");
tc3.setPrefWidth(110.0);
tc3.setCellValueFactory(new PropertyValueFactory<>("email"));
 TableColumn<String,Person> tc4=new TableColumn("PHONE");
tc4.setPrefWidth(108.0);
tc4.setCellValueFactory(new PropertyValueFactory<>("phone"));
tb.getColumns().addAll(tc1,tc2,tc3,tc4);

result.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
sid.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
name.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
email.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
phone.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
id.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
nemail.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
add.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
search.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
update.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
delete.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));
view.setFont(Font.font("Britanic Bold",FontWeight.BOLD,12));

AnchorPane root=new
AnchorPane(s1,s2,vb1,vb2,vb3,add,id,nemail,tid,tnemail,search,update,view,delete,tb);
root.setBackground(Background.EMPTY);
add.setOnAction(new EventHandler<ActionEvent>() {
@Override
public void handle(ActionEvent event) {
try{
Class.forName("oracle.jdbc.OracleDriver");
con =
DriverManager.getConnection("jdbc:oracle:thin:NAGA/naga@localhost:1521/xe");
stmt=con.prepareStatement("insert into student values(?,?,?,?)");
stmt.setInt(1,Integer.parseInt(tsid.getText()));
stmt.setString(2,tname.getText());
stmt.setString(3,temail.getText());
stmt.setString(4,tphone.getText());
int i=stmt.executeUpdate();
tsid.clear();tname.clear();temail.clear();tphone.clear();
tresult.setText("Record inserted");
tresult.setStyle("-fx-text-fill:green");
con.close();
}
catch(Exception e){ System.out.println(e);}
}
});
 view.setOnAction(new EventHandler<ActionEvent>() {
@Override
public void handle(ActionEvent event) {

try{
Class.forName("oracle.jdbc.OracleDriver");
con =
DriverManager.getConnection("jdbc:oracle:thin:NAGA/naga@localhost:1521/xe");
s=con.createStatement();
String sql="select * from student";
ResultSet rs = s.executeQuery(sql);
ObservableList<Person> oblist=FXCollections.observableArrayList();
while(rs.next())
{
rid=rs.getInt(1);
rname=rs.getString(2);
remail=rs.getString(3);
rphone=rs.getString(4);

oblist.add(new Person(rid,rname,remail,rphone));
}
tb.setItems(oblist);
tresult.setText("Records Retrived successfully");
tresult.setStyle("-fx-text-fill:green");
con.close();
}
catch(Exception e){ System.out.println(e);}
}
});

search.setOnAction(new EventHandler<ActionEvent>() {
@Override
public void handle(ActionEvent event) {
int count=0;
try{
Class.forName("oracle.jdbc.OracleDriver");
con =
DriverManager.getConnection("jdbc:oracle:thin:NAGA/naga@localhost:1521/xe");
s=con.createStatement();
String sql1="select * from student where id="+tid.getText()+"";
ResultSet rs1 = s.executeQuery(sql1);
while(rs1.next())
count++;
ResultSet rs = s.executeQuery(sql1);
 ObservableList<Person> oblist=FXCollections.observableArrayList();
if(count>0)
{
while(rs.next())
{
rid=rs.getInt(1);
rname=rs.getString(2);
remail=rs.getString(3);
rphone=rs.getString(4);
oblist.add(new Person(rid,rname,remail,rphone));
}
tb.setItems(oblist);
tresult.setText("Record Id: "+rid+"\nName: \t"+rname+"");
tresult.setStyle("-fx-text-fill:green");
}
else
{
tresult.setText("This Record does not exist!");
tresult.setStyle("-fx-text-fill:red");
}
con.close();
}
catch(Exception e){ System.out.println(e);}

});

delete.setOnAction(new EventHandler<ActionEvent>() {
@Override
public void handle(ActionEvent event) {
int count=0;
try{
Class.forName("oracle.jdbc.OracleDriver");
con =
DriverManager.getConnection("jdbc:oracle:thin:NAGA/naga@localhost:1521/xe");
s=con.createStatement();
String sql1="select count(*) from student where id="+tid.getText()+"";
ResultSet rs1 = s.executeQuery(sql1);
rs1.next();
count=rs1.getInt(1);
if(count>0)
{
String sql2="delete from student where id="+tid.getText()+"";
 s.executeUpdate(sql2);
String sql3="select * from student";
ResultSet rs = s.executeQuery(sql3);
ObservableList<Person> oblist=FXCollections.observableArrayList();
while(rs.next())
{
rid=rs.getInt(1);
rname=rs.getString(2);
remail=rs.getString(3);
rphone=rs.getString(4);
oblist.add(new Person(rid,rname,remail,rphone));
}
tb.setItems(oblist);
tresult.setText("ID: "+tid.getText()+" Reocord Deleted");
tresult.setStyle("-fx-text-fill:green");
}
else
{
tresult.setText("This Record does not exist!");
tresult.setStyle("-fx-text-fill:red");
}
con.close();
}
catch(Exception e){ System.out.println(e);}
}
});

update.setOnAction(new EventHandler<ActionEvent>() {
@Override
public void handle(ActionEvent event) {
int count=0;
try{
Class.forName("oracle.jdbc.OracleDriver");
con =
DriverManager.getConnection("jdbc:oracle:thin:NAGA/naga@localhost:1521/xe");
s=con.createStatement();
String sql1="select * from student where id="+tid.getText()+"";
ResultSet rs1 = s.executeQuery(sql1);
while(rs1.next())
count++;
String sql2="update student set email='"+tnemail.getText()+"'where
id="+tid.getText()+"";
s.executeUpdate(sql2);
String sql3="select * from student where id="+tid.getText()+"";
 ResultSet rs = s.executeQuery(sql3);
ObservableList<Person> oblist=FXCollections.observableArrayList();
if(count>0)
{
while(rs.next())
{
rid=rs.getInt(1);
rname=rs.getString(2);
remail=rs.getString(3);
rphone=rs.getString(4);
oblist.add(new Person(rid,rname,remail,rphone));
}
tb.setItems(oblist);
tresult.setText("Record updated successfully");
tresult.setStyle("-fx-text-fill:green");
}
else
{
tresult.setText("This Record does not exist!");
tresult.setStyle("-fx-text-fill:red");
}
con.close();
}
catch(Exception e){ System.out.println(e);}
}
});

Scene scene = new Scene(root, 600, 380,Color.ANTIQUEWHITE);

primaryStage.setTitle("JDBC");
primaryStage.setScene(scene);
primaryStage.show();
}
public static void main(String[] args) {
launch(args);
}

}
Person.java:
package week5;
public class Person {
private int id;
private String name = null;
private String email = null;
private String phone = null;

public Person() {
}

public Person(int id,String name, String email,String phone) {

this.id=id;
this.name=name;
this.email=email;
this.phone=phone;
}
public int getId() {
return id;
}

public void setId(int id) {

this.id = id;
}

public String getName() {

return name;
}

public void setName(String name) {

this.name = name;
}

public String getEmail() {

return email;
}

public void setEmail(String email) {

this.email= email;
}
public String getPhone() {
return phone;
 }

public void setPhone(String phone) {

this.phone= phone;
}
}
OUTPUT: