Question

There is a problem in the update code.

I need to be able to update one attribute of the students without updating the other attributes (keeping them empty).

package dbaccessinjava;

import java.sql.DriverManager;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.*;

import java.io.*;

import java.util.Scanner;

public class DBAccessInJava {

public static void main(String[] argv) {

   System.out.println("-------- Oracle JDBC Connection Testing ------");

Connection connection = null;

try{

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con= DriverManager.getConnection("jdbc:oracle:thin:@MF057PC16:1521:ORCL", "u201303908","pmu");

String sql="select * from student";

Statement st;

PreparedStatement ps;

ResultSet rs;

do{

System.out.println("Enter 0 to terminate>");

System.out.println("Enter 1 to view the table>");

System.out.println("Enter 2 to insert>");

System.out.println("Enter 3 to delete>");

System.out.println("Enter 4 to update>");

BufferedReader br = new BufferedReader(new InputStreamReader(System.in));

int choice;

choice = Integer.parseInt(br.readLine());

if(choice==0){

break;

}

else if(choice==1){

st = con.createStatement();

rs = st.executeQuery(sql);

System.out.println("*******Data from the Table*******");

while(rs.next()){

System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4));

}

System.out.println("\n");

}

else if(choice==2){

ps = con.prepareStatement("insert into student values(?,?,?,?)");

System.out.println("Enter ID:");

String sid = br.readLine();

System.out.println("Enter First Name:");

String fName = br.readLine();

System.out.println("Enter Last Name:");

String lName = br.readLine();

System.out.println("Enter Grade:");

String grade = br.readLine();

  

ps.setString(1, sid);ps.setString(2, fName);

ps.setString(3, lName);ps.setString(4, grade);

  

int row = ps.executeUpdate();

if(row==1){

System.out.println("Student Data Updated...");

}

}

else if(choice==3){

System.out.println("Enter Student ID to be deleted:");

String sid = br.readLine();

st = con.createStatement();

st.executeUpdate("delete from STUDENT where id='"+sid+"'");

System.out.println("Student deleted...");

}

else if(choice==4){

System.out.println("Enter ID to decide which student to update:");

String sid = br.readLine();

System.out.println("Enter First Name:");

String fName = br.readLine();

System.out.println("Enter Last Name:");

String lName = br.readLine();

System.out.println("Enter Grade:");

String grade = br.readLine();

st = con.createStatement();

st.executeUpdate("update STUDENT set fname='"+fName+"', lname='"+lName+"', grade='"+grade+"' where id= '"+sid+"'");

  

System.out.println("Student Updated....");

}

else {

System.out.println("please choose a number from the list");

System.out.println("\n");

}

}

while(true);

  

}catch(Exception e){

e.printStackTrace();

}

}

}

Answer 1

package dbaccessinjava;

import java.sql.DriverManager;

import java.sql.Connection;

import java.sql.SQLException;

import java.sql.*;

import java.io.*;

import java.util.Scanner;

public class DBAccessInJava {

public static void main(String[] argv) {

   System.out.println("-------- Oracle JDBC Connection Testing ------");

Connection connection = null;

try{

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection con= DriverManager.getConnection("jdbc:oracle:thin:@MF057PC16:1521:ORCL", "u201303908","pmu");

String sql="select * from studentdetails";

Statement st;

PreparedStatement ps;

ResultSet rs;

do{

System.out.println("Enter 0 to terminate>");

System.out.println("Enter 1 to view the table>");

System.out.println("Enter 2 to insert>");

System.out.println("Enter 3 to delete>");

System.out.println("Enter 4 to update>");

BufferedReader br = new BufferedReader(new InputStreamReader(System.in));

int choice;

choice = Integer.parseInt(br.readLine());

if(choice==0){

break;

}

else if(choice==1){

st = con.createStatement();

rs = st.executeQuery(sql);

System.out.println("*******Data from the Table*******");

while(rs.next()){

System.out.println(rs.getString(1)+" "+rs.getString(2)+" "+rs.getString(3)+" "+rs.getString(4));

}

System.out.println("\n");

}

else if(choice==2){

ps = con.prepareStatement("insert into student values(?,?,?,?)");

System.out.println("Enter ID:");

String sid = br.readLine();

System.out.println("Enter First Name:");

String fName = br.readLine();

System.out.println("Enter Last Name:");

String lName = br.readLine();

System.out.println("Enter Grade:");

String grade = br.readLine();

  

ps.setString(1, sid);ps.setString(2, fName);

ps.setString(3, lName);ps.setString(4, grade);

  

int row = ps.executeUpdate();

if(row==1){

System.out.println("Student Data Updated...");

}

}

else if(choice==3){

System.out.println("Enter Student ID to be deleted:");

String sid = br.readLine();

st = con.createStatement();

st.executeUpdate("delete from STUDENT where id='"+sid+"'");

System.out.println("Student deleted...");

}

else if(choice==4){

System.out.println("Enter ID to decide which student to update:");

String sid = br.readLine();

System.out.println("Enter First Name:");

String fName = br.readLine();

System.out.println("Enter Last Name:");

String lName = br.readLine();

System.out.println("Enter Grade:");

String grade = br.readLine();

st = con.createStatement();

//here you need to update a query to update single or multiple attributes.if you mention single it will update single remianing will be same

st.executeUpdate("update STUDENT set fname='"+fName+"' where id= '"+sid+"'");

  

System.out.println("Student Updated....");

}

else {

System.out.println("please choose a number from the list");

System.out.println("\n");

}

}

while(true);

  

}catch(Exception e){

e.printStackTrace();

}

}

}