Create a JAVA PROGRAM that allows user to insert, delete, update(SQL statements)
ID: 3606727 • Letter: C
Question
Create a JAVA PROGRAM that allows user to insert, delete, update(SQL statements). Allows the user to use/input select statements, from statements and where statements.
Focus on whether the program can recognise if the user has entered a VALID or an INVALID statement. Such as Select name, From Student.
Implement functionality for EXECUTE command and EXIT command.
PLEASE STRICTLY USE THIS STARTER CODE BELOW:
package identification;
import java.io.*;
import java.util.Scanner;
public class Main
{
public static void main( String args[] )
{
Scanner input = new Scanner(System.in);
Command comm = null;
String information = "";
while(!(comm instanceof ExitCommand))
{
System.out.print(">");
information = input.nextLine();
if(information.contains(";"))
{
String command = information.substring(0,information.indexOf(";"));
comm = Parser.parse(command);
comm.execute();
information = information.substring(information.indexOf(";")+1)
}
}
}
}
CREATETABLE CLASS
package identification;
import java.sql.*;
public class CreateTable implements Command
{
private String tableName;
private String[] columnNameType;
public CreateTable(String input)
{
//parse the string to see if it is valid.
}
public void execute()
{
System.out.println("Valid Create Syntax.");
}
}
COMMAND CLASS
package identification;
public interface Command {
public void execute();
}
EXIT COMMAND
package identification;
public class ExitCommand implements Command {
public void execute()
{
System.out.println("System exiting.");
}
}
CLASS PARSER
package identification;
public class Parser
{
public static Command parse(String userInput)
{
Command result;
if(userInput.equals("exit"))
result = new ExitCommand();
else if(userInput.startsWith("create table"))
result = new CreateTable(userInput);
return result;
}
}
CAN YOU PLEASE FINISH THE CODE FOR THE CLASSES; DELETE, INSERT,UPDATE,DROP TABLE AND SELECT. AND FINISH UP ON THE STARTER CODE, IF THERE IS ANYTHING MISSING.
USE ANY VALIDATION RULES.
Explanation / Answer
// === Create Class to Insert , delete , update record == //
import java.io.*;
import java.util.Scanner;
import java.sql.*;
public class JavaMain {
public static void main( String args[] )
{
Scanner input = new Scanner(System.in);
Command comm = null;
String information = "";
while(!(comm instanceof ExitCommand))
{
System.out.print(">");
information = input.nextLine();
if(information.contains(";"))
{
String command = information.substring(0,information.indexOf(";"));
comm = Parser.parse(command);
comm.execute();
information = information.substring(information.indexOf(";")+1);
}
}
}
}
class CreateTable implements Command
{
private String tableName = "users";
private String[] columnNameType;
private String id, name , email;
public CreateTable(String input)
{
//parse the string to see if it is valid.
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "test", "test");
Statement stmt = con.createStatement();
// Inserting data in database
String q1 = "insert into "+tableName+" values('" +id+ "', '" +name+ "', '" +email+ "')";
int x = stmt.executeUpdate(q1);
if (x > 0)
System.out.println("Successfully Inserted");
else
System.out.println("Insert Failed");
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
// == select values = //
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection(" jdbc:oracle:thin:@localhost:1521:orcl", "test", "test");
Statement stmt = con.createStatement();
// SELECT query
String q1 = "select * from "+tableName+" WHERE id = '" + id +
"' AND name = '" + name + "'";
ResultSet rs = stmt.executeQuery(q1);
if (rs.next())
{
System.out.println("User-Id : " + rs.getString(1));
System.out.println("Name :" + rs.getString(2));
System.out.println("E-mail :" + rs.getString(3));
}
else
{
System.out.println("No such user id is already registered");
}
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
// ----- delete ===//
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "test", "test");
Statement stmt = con.createStatement();
// Deleting from database
String q1 = "DELETE from "+tableName+" WHERE id = '" + id +
"' AND name = '" + name + "'";
int x = stmt.executeUpdate(q1);
if (x > 0)
System.out.println("One User Successfully Deleted");
else
System.out.println("ERROR OCCURED :(");
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
// ---- Update ----- //
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:orcl", "test", "test");
Statement stmt = con.createStatement();
// Updating database
String q1 = "UPDATE userid set name = '" + name +
"' WHERE id = '" +id+ "' AND name = '" + name + "'";
int x = stmt.executeUpdate(q1);
if (x > 0)
System.out.println("Password Successfully Updated");
else
System.out.println("ERROR OCCURED :(");
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
public void execute()
{
System.out.println("Valid Create Syntax.");
}
}
interface Command {
public void execute();
}
class ExitCommand implements Command {
public void execute()
{
System.out.println("System exiting.");
}
}
class Parser
{
public static Command parse(String userInput)
{
Command result = null;
if(userInput.equals("exit"))
result = new ExitCommand();
else if(userInput.startsWith("create table"))
result = new CreateTable(userInput);
return result;
}
}