Implement the following scenarios using JAVA and SQL by using code from the picture and implementing the code onto the WRITTEN code below:

1. Implement the algorithm (SHOWN IN PICTURE BELOW CODE) for add a new name (name can be hardcoded in the program) which should be unique.

2. Implement algorithm (SHOWN IN PICTURE BELOW CODE) to delete an existing name (name can be hardcoded)

3. For the above implementations, try to write two phase commit by checking the vote in each site from your program. See the lecture note for details.

4. Now write a multithreaded program to add unique name in the sites (the template for one site to create 10 threads is attached and you can complete it for two sites and adding delete as above). You can write your program from scratch or complete the attached as is program (template) if you want. By commenting and uncommenting the semaphore in the source code explain how using the semaphore in the program helps the threads to perform their jobs.

WRITTEN TEMPLATE:

import java.sql.Connection; import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import java.sql.ResultSet; import java.util.Properties; import java.util.ArrayList; import java.util.concurrent.Semaphore;

public class JdbcMTSample extends Thread { // Set default number of threads to 10 private static int NUM_OF_THREADS = 10;

int m_myId;

static int c_nextId = 1; static Connection s_conn = null; static Semaphore semaphore = new Semaphore(1);

synchronized static int getNextId() { return c_nextId++; }

public static void main (String args []) { try { // Load the JDBC driver // DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver()); Class.forName("oracle.jdbc.OracleDriver"); // If NoOfThreads is specified, then read it if (args.length > 1) { System.out.println("Error: Invalid Syntax. "); System.out.println("java JdbcMTSample [NoOfThreads]"); System.exit(0); } else if (args.length == 1)

NUM_OF_THREADS = Integer.parseInt (args[0]); // Create the threads Thread[] threadList = new Thread[NUM_OF_THREADS];

// spawn threads for (int i = 0; i

// wait for all threads to end for (int i = 0; i

public void run() { Connection conn1 = null; ResultSet rs = null; Statement stmt = null; ArrayList lastitem = new ArrayList();

try { semaphore.acquire(); // providing mutual exclusion // Get the connection String dbURL1 = "jdbc:oracle:thin:username/password@hostname:Port:SID"; conn1 = DriverManager.getConnection(dbURL1); if (conn1 != null) { System.out.println("Connected with connection #1"); conn1.setAutoCommit(false); } else{ System.out.println("Connection Failed with connection #1"); //transaction = false; } String nameToInsert = "test" + m_myId % 5 ; // generates not unique names

// You need to create a table called TESTJ with one string attributes call Name by SqlDeveloper in Site1

// check starts from here stmt = conn1.createStatement (); // Execute the Query rs = stmt.executeQuery ("SELECT * FROM TESTJ"); // Loop through the results while (rs.next()){ System.out.println("Thread " + m_myId + " Name : " + rs.getString("Name")); lastitem.add(rs.getString("Name")); } // Close all the resources rs.close(); stmt.close(); // check ends here

// Create a Statement if (!lastitem.contains(nameToInsert)) { String insert = "INSERT into TESTJ VALUES('"+ nameToInsert +"')";

try (Statement stmt1 = conn1.createStatement()) { stmt1.executeQuery(insert); } catch (SQLException e) { System.out.println(e.getErrorCode()); } System.out.println("SITE1 Transaction Completed"); conn1.commit(); }

if (conn1 != null) conn1.close(); System.out.println("Thread " + m_myId + " is finished. "); } catch (Exception e) { System.out.println("Thread " + m_myId + " got Exception: " + e); e.printStackTrace(); return; } semaphore.release(); }

}

PICTURE TEMPLATE, USE THIS CODE TO IMPLEMENT ALGORITHM FOR WRITTEN CODE ABOVE

// begin of transaction String query2 = "select NAME from TEST610"; ArrayList namesSite2 = new ArrayList0; try (Statement stmt - conn2.createStatement()) { // searching database ResultSet rs - stmt.executeQuery(query2); System.out.println("SITE 2"); while (rs.next() { String name2 = rs.getString("NAME"); namesSite2.add(name2.toLowerCase()); System.out.println(name2); } } catch (SQLException e) { System.out.println(e.getErrorCode()); } if (namesSite2.contains(nameTo Insert)) I/check the condition of transaction { System.out.println("name already present, abort transaction"); Abort ... //Abort the transaction } else {String insert2 = "INSERT into TEST610 values(""+nameToInsert+")"; try (Statement stmt - conn2.createStatement() { // inserting into database stmt.executeQuery(insert2); } catch (SQLException e) Il commit the transaction // end of transaction // begin of transaction String query2 = "select NAME from TEST610"; ArrayList namesSite2 = new ArrayList0; try (Statement stmt - conn2.createStatement()) { // searching database ResultSet rs - stmt.executeQuery(query2); System.out.println("SITE 2"); while (rs.next() { String name2 = rs.getString("NAME"); namesSite2.add(name2.toLowerCase()); System.out.println(name2); } } catch (SQLException e) { System.out.println(e.getErrorCode()); } if (namesSite2.contains(nameTo Insert)) I/check the condition of transaction { System.out.println("name already present, abort transaction"); Abort ... //Abort the transaction } else {String insert2 = "INSERT into TEST610 values(""+nameToInsert+")"; try (Statement stmt - conn2.createStatement() { // inserting into database stmt.executeQuery(insert2); } catch (SQLException e) Il commit the transaction // end of transaction