Overview: This homework provides an opportunity for the student to write JDBC ap

Question

Overview:
This homework provides an opportunity for the student to write JDBC applications and store those in an Oracle database using a PL/SQL wrapper. The queries will use prepared statements to minimize security concerns.

Using the Schema you developed in Homework 1 for your e-Commerce project, design and implement a JDBC application that runs both as a stand-alone JDBC application and a stored procedure within Oracle using a PL/SQL wrapper.

Your application should provide JDBC functionality demonstrating
a. Inserting a new record into a table using input from the user at run time.
b. Update an existing record in a table using input from the user at run time.
c. Deleting an existing record in table using input from the user at run time.
d. Retrieving records based using input from the user at run time.
Note: using input from the user at run time means that the SQL statements should have a parameter(s) that the user enters at run time.
Be sure your code works as a standalone Java application and then follow the steps to create PL/SQL wrapper and then demonstrate the same functionality running from within Oracle.

Deliverables:
1. Create a word or PDF document that describes your process, steps and results. Be sure to describe your schema and the queries you are using for your application. Provide screen shots showing the successful running of all of your SQL statements and testing your implementation for both the Java application and the PL/SQL implementation. Be sure your testing is comprehensive demonstrating all functionality. Describe and demonstrate why your applications are secure from SQL Injection. The document should be neat, well-organized, well-written and contain minimal grammar and spelling errors.
2. A .java file(s) that compiles and runs perfectly for your Java application
3. A .sql script file containing all SQL statements you used for this effort. All SQL statements should be in one file with appropriate connect statements to differentiate users who run the script. The script file should contain comments describe each major set of SQL statements. The script should run perfectly without error when executed from the Oracle SQL*PLUS environment. Be sure your Pl/SQL wrapper implementations are within this script.

Note to Chegg Expert, the schema developed in homework 1 was a simple schema consisting of 4 tables:
customers: customer_id NUMBER primary key, name VARCHAR2 NOT NULL, phone VARCHAR2, address VARCHAR2 NOT NULL
products: product_id VARCHAR2 primary key, product VARCHAR2 NOT NULL, price NUMBER NOT NULL
orders: order_id VARCHAR2 primary key, order_date DATE, order_type VARCHAR2, product_id VARCHAR2 foreign key references products
delivery: deliver_id VARCHAR2 primary key, delivery_date DATE, order_id VARCHAR2 foreign key references orders, customer_id VARCHAR2 foreign key references customers

Appreciate your help ahead of time.

Explanation / Answer

Solution :

//For Executing INSERT Statement

String sql = "INSERT INTO Users (username, password, fullname, email) VALUES (?, ?, ?, ?)";

PreparedStatement statement = conn.prepareStatement(sql);

statement.setString(1, "bill");

statement.setString(2, "secretpass");

statement.setString(3, "Bill Gates");

statement.setString(4, "bill.gates@microsoft.com");

int rowsInserted = statement.executeUpdate();

if (rowsInserted > 0) {

    System.out.println("A new user was inserted successfully!");

}

//For Executing SELECT Statement

String sql = "SELECT * FROM Users";

Statement statement = conn.createStatement();

ResultSet result = statement.executeQuery(sql);

int count = 0;

while (result.next()){

    String name = result.getString(2);

    String pass = result.getString(3);

    String fullname = result.getString("fullname");

    String email = result.getString("email");

    String output = "User #%d: %s - %s - %s - %s";

    System.out.println(String.format(output, ++count, name, pass, fullname, email));

}

//For Executing UPDATE Statement

String sql = "UPDATE Users SET password=?, fullname=?, email=? WHERE username=?";

PreparedStatement statement = conn.prepareStatement(sql);

statement.setString(1, "123456789");

statement.setString(2, "William Henry Bill Gates");

statement.setString(3, "bill.gates@microsoft.com");

statement.setString(4, "bill");

int rowsUpdated = statement.executeUpdate();

if (rowsUpdated > 0) {

    System.out.println("An existing user was updated successfully!");

}

//For Executing DELETE Statement

String sql = "DELETE FROM Users WHERE username=?";

PreparedStatement statement = conn.prepareStatement(sql);

statement.setString(1, "bill");

int rowsDeleted = statement.executeUpdate();

if (rowsDeleted > 0) {

    System.out.println("A user was deleted successfully!");

}

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.