1)

a) Using Java and SQL, change the code to add a new name to two databases (name can be hardcoded in the program) and should be unique.

b) Implement algorithm to delete an existing name (name can be hardcoded)

Hint: You could make a static array with 10 elements and just access the element corresponding to the m_myId of the thread it's in, or something.

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 < NUM_OF_THREADS; i++)

{

threadList[i] = new JdbcMTSample();

threadList[i].start();

}

// wait for all threads to end

for (int i = 0; i < NUM_OF_THREADS; i++)

{

threadList[i].join();

}

}

catch (Exception e)

{

e.printStackTrace();

}

}

public JdbcMTSample()

{

super();

// Assign an ID to the thread

m_myId = getNextId();

}

public void run()

{

Connection conn1 = null, conn2 = null;

ResultSet rs1 = null, rs2 = null;

Statement stmt1 = null, stmt2 = null;

ArrayList lastitem1 = new ArrayList(), lastitem2 = new ArrayList();

try

{

semaphore.acquire(); // providing mutual exclusion

// Get the connection

String dbURL1 = "jdbc:oracle:thin:username/password@hostname:Port:SID";

String Username1 = "";

String Password1 = "";

String dbURL2 = "jdbc:oracle:thin:username/password@hostname:Port:SID";

String Username2 = "";

String Password2 = "";

conn1 = DriverManager.getConnection(dbURL1, Username1, Password1);

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

conn2 = DriverManager.getConnection(dbURL2, Username2, Password2);

if (conn2 != null) {

System.out.println("Connected with connection #2");

conn2.setAutoCommit(false);

}

else{

System.out.println("Connection Failed with connection #2");

//transaction = false;

}

String nameToInsert = "test" + m_myId % 5 ;

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

// check starts from here

stmt1 = conn1.createStatement ();

// Execute the Query

rs1 = stmt1.executeQuery ("SELECT * FROM TESTJ");

// Loop through the results

while (rs1.next()){

System.out.println("Thread " + m_myId +

" Name : " + rs1.getString("Name"));

lastitem1.add(rs1.getString("Name"));

}

// Close all the resources

rs1.close();

stmt1.close();

stmt2 = conn2.createStatement ();

// Execute the Query

rs2 = stmt2.executeQuery ("SELECT * FROM TESTJ");

// Loop through the results

while (rs2.next()){

System.out.println("Thread " + m_myId +

" Name : " + rs2.getString("Name"));

lastitem1.add(rs2.getString("Name"));

}

// Close all the resources

rs2.close();

stmt2.close();

// check ends here

// Create a Statement

ArrayList namesSite2 = new ArrayList();

if (!lastitem1.contains(nameToInsert))

{ //if

String insert1 = "INSERT into TESTJ VALUES('"+ nameToInsert +"')";

String insert2 = "INSERT into TESTJ VALUES('"+nameToInsert+"')";

String query2 = "select NAME from TESTJ";

//ArrayList namesSite2 = new ArrayList();

//try (Statement stmt1 = conn1.createStatement()) {

try

{

stmt1 = conn1.createStatement();

stmt1.executeQuery(insert1);

stmt2 = conn2.createStatement();

stmt2.executeQuery(insert2);

ResultSet rs = stmt2.executeQuery(query2);

while (rs.next())

{

String name2 = rs.getString("NAME");

namesSite2.add(name2.toLowerCase());

System.out.println(name2);

}//while

} catch (SQLException e)

{

System.out.println(e.getErrorCode());

}//catch

System.out.println("SITE1 Transaction Completed");

conn1.commit();

conn2.commit();

}

if (conn1 != null || conn2 != null) {

conn1.close();

conn2.close();

}//if

System.out.println("Thread " + m_myId + " is finished. ");

if (namesSite2.contains(nameToInsert)) {

System.out.println("name already present, abort transaction");

return;

}//if

else { //else

String insert2 = "INSERT into TESTJ values('"+nameToInsert+"')";

try (Statement stmt = conn2.createStatement()){ //try

stmt.executeQuery(insert2);

} catch(SQLException e) {}//try

} //else

} catch (Exception e)

{

System.out.println("Thread " + m_myId + " got Exception: " + e);

e.printStackTrace();

return;

}

semaphore.release();

}

}