Create a JAVA PROGRAM that allows user to insert, delete, update(SQL statements)
ID: 3594824 • 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.
STARTER CODE:
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.
Explanation / Answer
Programme for connecting to database:
// Java program to illustrate
// Connecting to the Database
import java.sql.*;
public class connect
{
public static void main(String args[])
{
try
{
Class.forName("something you can enter here");
// Establishing Connection
Connection con = DriverManager.getConnection(
"jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
if (con != null)
System.out.println("Connected");
else
System.out.println("Not Connected");
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}
Implementing Insert Statement
import java.sql.*;
public class insert1
{
public static void main(String args[])
{
String id = "id1";
String pwd = "pwd1";
String fullname = "geeks for geeks";
String email = "geeks@geeks.org";
try
{
Class.forName("something you can enter here ");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement();
// Inserting data in database
String q1 = "insert into userid values('" +id+ "', '" +pwd+
"', '" +fullname+ "', '" +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);
}
}
}
Implementing Update Statement
import java.sql.*;
public class update1
{
public static void main(String args[])
{
String id = "id1";
String pwd = "pwd1";
String newPwd = "newpwd";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement();
// Updating database
String q1 = "UPDATE userid set pwd = '" + newPwd +
"' WHERE id = '" +id+ "' AND pwd = '" + pwd + "'";
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);
}
}
}
Implementing Delete Statement:
import java.sql.*;
public class delete
{
public static void main(String args[])
{
String id = "id2";
String pwd = "pwd2";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement();
// Deleting from database
String q1 = "DELETE from userid WHERE id = '" + id +
"' AND pwd = '" + pwd + "'";
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);
}
}
}
Implementing Select Statement
import java.sql.*;
public class select
{
public static void main(String args[])
{
String id = "id1";
String pwd = "pwd1";
try
{
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("
jdbc:oracle:thin:@localhost:1521:orcl", "login1", "pwd1");
Statement stmt = con.createStatement();
// SELECT query
String q1 = "select * from userid WHERE id = '" + id +
"' AND pwd = '" + pwd + "'";
ResultSet rs = stmt.executeQuery(q1);
if (rs.next())
{
System.out.println("User-Id : " + rs.getString(1));
System.out.println("Full Name :" + rs.getString(3));
System.out.println("E-mail :" + rs.getString(4));
}
else
{
System.out.println("No such user id is already registered");
}
con.close();
}
catch(Exception e)
{
System.out.println(e);
}
}
}