gistfile1.java

package org.itnaf.oim11g.test.delete;

import java.sql.*;

public class DeleteUsers {
  // JDBC driver name and database URL
	static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
	String DB_URL = "jdbc:oracle:thin:@host:port:sid";
	String[] USERS_TO_IGNORE = { "XELOPERATOR", "XELSYSADM", "OIMOPERATOR", "WEBLOGIC" };

	// Database credentials
	String USER = "username";
	String PASS = "password";
	Connection connection = null;

	public static void main(String[] args) {
		DeleteUsers deleteUsers = new DeleteUsers();
		if (args.length < 3) {
			System.out.println("Usage: DB_URL DB_USR DB_PASSWORD");
			System.exit(-1);
		}
		deleteUsers.DB_URL = args[0];
		deleteUsers.USER = args[1];
		deleteUsers.PASS = args[2];
		deleteUsers.connection = deleteUsers.Connect(deleteUsers.DB_URL,
				deleteUsers.USER, deleteUsers.PASS);
		deleteUsers.findDeleted();
	}// end main

	public Connection Connect(String url, String user, String password) {
		Connection connection = null;
		try {
			Class.forName(JDBC_DRIVER);
			System.out.println("Connecting to database...");
			connection = DriverManager.getConnection(url, user, password);
		} catch (SQLException se) {
			se.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return connection;
	}

	public void findDeleted() {
		Statement statement = null;
		String countOfDeletedOIMUsers = "select count(*) from USR where usr_status = 'Deleted'";
		String deletedOIMUsers = "select * from USR where usr_status = 'Deleted'";

		if (USERS_TO_IGNORE.length > 0) {
			countOfDeletedOIMUsers += " and USR.USR_LOGIN NOT IN ( ";
			deletedOIMUsers += " and USR.USR_LOGIN NOT IN ( ";
			for (String usersToIgnore : USERS_TO_IGNORE) {
				countOfDeletedOIMUsers += "'" + usersToIgnore + "', ";
				deletedOIMUsers += "'" + usersToIgnore + "', ";
			}
			countOfDeletedOIMUsers = countOfDeletedOIMUsers.substring(0,
					countOfDeletedOIMUsers.length() - 2);
			deletedOIMUsers = deletedOIMUsers.substring(0,
					deletedOIMUsers.length() - 2);
			countOfDeletedOIMUsers += ")";
			deletedOIMUsers += ")";
		}
		try {
			statement = connection.createStatement();

			System.out.println("Query = " + countOfDeletedOIMUsers);
			ResultSet rs = statement.executeQuery(countOfDeletedOIMUsers);

			long count = 0L;
			if (rs.next()) {
				count = rs.getLong(1);
			}

			System.out.println("Count of deleted users: " + count);

			if (count == 0L) {
				System.out.println("Nothing to do");
				return;
			}
			rs = statement.executeQuery(deletedOIMUsers);
			while (rs.next()) {
				// Retrieve by column name
				long usrKey = rs.getLong("USR_KEY");
				String usrLogin = rs.getString("USR_LOGIN");

				// Display values
				System.out.print("USR_KEY: " + usrKey);
				System.out.print(", USR_LOGIN: " + usrLogin);
				deleteByKey(usrKey);
			}
			rs.close();
			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null)
					statement.close();
			} catch (SQLException se2) {
			}// nothing we can do
		}
	}

	public void deleteByKey(long usrKey) {
		Statement statement = null;
		try {
			statement = connection.createStatement();

			String OUD = "delete from oud where oud.OUD_PARENT_OIU_KEY in " +
					"(select oiu_key from oiu where oiu.usr_key in (select usr_key from usr where usr_key = " + 
					usrKey + "))";

			String OIU = "delete from oiu where oiu.usr_key in (select usr_key from usr where usr_key = " +
					usrKey + ")";

			String OIU2 = "delete from oiu where oiu.usr_key in (select usr_key from usr where usr_key= " +
					usrKey + ")";
			
			String OSI = "delete from osi where osi.orc_key in (select orc_key from orc where " +
					" orc.usr_key in (select usr_key from usr where usr_key=" +
					usrKey + "))";

			String RCB = "delete from rcb where rcb.rce_key in (select rce_key from rce where " +
					"rce.orc_key in (select orc_key from orc where orc.usr_key in (select " +
					" usr_key from usr where usr_key= " +
					usrKey + ")))";

			String RCD = "delete from rcd where rcd.rce_key in (select rce_key from rce where " + 
					"rce.orc_key in (select orc_key from orc where orc.usr_key in (select " +
					"usr_key from usr where usr_key=" + 
					usrKey + ")))";

			String RCH = "delete from rch where rch.rce_key in (select rce_key from rce where " +
					"rce.orc_key in (select orc_key from orc where orc.usr_key in (select " + 
					"usr_key from usr where usr_key=" + 
					usrKey + ")))";

			String RPC = "delete from rpc where rpc.rcm_key in (select rcm_key from rcm where " +
					"rcm.rce_key in (select rce_key from rce where rce.orc_key in(select " +
					"orc_key from orc where orc.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + "))))";
					
			String RCM = "delete from rcm where rcm.rce_key in (select rce_key from rce where " +
					"rce.orc_key in (select orc_key from orc where orc.usr_key in (select " +
					"usr_key from usr where usr_key=" + 
					usrKey + ")))";
			
			String RCP = "delete from rcp where rcp.rce_key in (select rce_key from rce where " +
					"rce.orc_key in (select orc_key from orc where orc.usr_key in (select " +
					"usr_key from usr where usr_key=" +
					usrKey + ")))";

			String RCU = "delete from rcu where rcu.rce_key in (select rce_key from rce where " +
					"rce.orc_key in (select orc_key from orc where orc.usr_key in (select " +
					"usr_key from usr where usr_key=" +
					usrKey + ")))";
			
			String RCE = "delete from rce where rce.orc_key in (select orc_key from orc where " +
					"orc.usr_key in (select usr_key from usr where usr_key=" + 
					usrKey + "))";
					
					
			String OIO = "delete from oio where oio.orc_KEY in (select orc_key from orc where " +
					"orc.usr_key in (select usr_key from usr where usr_key=" +
					usrKey + "))";
					
					
			String ORC = "delete from orc where orc.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + ")";
					
			String USG = "delete from usg where usg.usr_key in (select usr_key from usr where " + 
					"usr_key=" +
					usrKey + ")";
			
			String PCQ = "delete from pcq where pcq.usr_key in (select usr_key from usr where " + 
					"usr_key=" +
					usrKey + ")";
					
			String OSH = "delete from osh where osh.OSH_ASSIGNED_BY_USR_KEY in (select usr_key " +
					"from usr where usr_key=" + 
					usrKey + ")";
					
			String OSH2 = "delete from osh where osh.OSH_ASSIGNED_TO_USR_KEY in (select usr_key " +
					"from usr where usr_key=" +
					usrKey + ")";

			String UPD = "delete from upd where upd.upp_key in (select upp_key from upp where " +
					"upp.usr_key in (select usr_key from usr where usr_key=" +
					usrKey + "))";

			String UPP = "delete from upp where upp.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + ")";
					
			String OSI2 = "delete from osi where osi.OSI_ASSIGNED_TO_USR_KEY in (select usr_key " +
					"from usr where usr_key=" +
					usrKey + ")";
					
			String RQU = "delete from rqu where rqu.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + ")";
					
			String UHD = "delete from uhd where uhd.uph_key in (select uph_key from uph where " +
					"uph.usr_key in (select usr_key from usr where usr_key=" +
					usrKey + "))";
					
			String UPH = "delete from uph where uph.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + ")";
					
			String USG2 = "delete from usg where usg.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + ")";
					
			String OUD2 = "delete from oud where oud.OUD_PARENT_OIU_KEY in (select oiu_key from " +
					"oiu where oiu.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + "))";
					
			String OIU3 = "delete from oiu where oiu.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + ")";
					
			String OIO2 = "delete from oio where oio.orc_KEY in (select orc_key from orc where " +
					"orc.usr_key in (select usr_key from usr where usr_key=" +
					usrKey + "))";
					
			String OSI3 = "delete from osi where osi.orc_KEY in (select orc_key from orc where " +
					"orc.usr_key in (select usr_key from usr where usr_key=" +
					usrKey + "))";
					
			String ORC2 = "delete from orc where orc.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + ")";
					
			String UPD2 = "delete from upd where upd.upp_key in (select upp_key from upp where " +
					"upp.usr_key in (select usr_key from usr where usr_key=" +
					usrKey + "))";
			
			String UPP2 = "delete from upp where upp.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + ")";
			
			String OSH3 = "delete from osh where OSH_ASSIGNED_BY_USR_KEY in (select usr_key from " +
					"usr where usr_key=" +
					usrKey + ")";
			
			String OSH4 = "delete from osh where OSH_ASSIGNED_TO_USR_KEY in (select usr_key from " +
					"usr where usr_key=" +
					usrKey + ")";
			
			String PCQ2 = "delete from pcq where pcq.usr_key in (select usr_key from usr where " +
					"usr_key=" +
					usrKey + ")";
			
			String OSI4 = "delete from osi where OSi_ASSIGNED_TO_USR_KEY in (select usr_key from " +
					"usr where usr_key=" +
					usrKey + ")";
			
					
			String USR = "delete from usr where usr_key=" +
					usrKey;

			System.out.println("Query OUD = " + OUD);
			statement.execute(OUD);
			System.out.println("Query OIU =  " + OIU);
			statement.execute(OIU);
			System.out.println("Query OIU2 =  " + OIU2);
			statement.execute(OIU2);
			System.out.println("Query OSI =  " + OSI);
			statement.execute(OSI);
			System.out.println("Query RCB =  " + RCB);
			statement.execute(RCB);
			System.out.println("Query RCD =  " + RCD);
			statement.execute(RCD);
			System.out.println("Query RCH =  " + RCH);
			statement.execute(RCH);
			System.out.println("Query RPC =  " + RPC);
			statement.execute(RPC);
			System.out.println("Query RCM =  " + RCM);
			statement.execute(RCM);
			System.out.println("Query RCP =  " + RCP);
			statement.execute(RCP);
			System.out.println("Query RCU = " + RCU);
			statement.execute(RCU);
			System.out.println("Query RCE = " + RCE);
			statement.execute(RCE);
			System.out.println("Query OIO = " + OIO);
			statement.execute(OIO);
			System.out.println("Query ORC = " + ORC);
			statement.execute(ORC);
			System.out.println("Query USG = " + USG);
			statement.execute(USG);
			System.out.println("Query PCQ = " + PCQ);
			statement.execute(PCQ);
			System.out.println("Query OSH = " + OSH);
			statement.execute(OSH);
			System.out.println("Query OSH2 = " + OSH2);
			statement.execute(OSH2);
			System.out.println("Query UPD = " + UPD);
			statement.execute(UPD);
			System.out.println("Query UPP = " + UPP);
			statement.execute(UPP);
			System.out.println("Query OSI2 = " + OSI2);
			statement.execute(OSI2);
			System.out.println("Query RQU = " + RQU);
			statement.execute(RQU);
			System.out.println("Query UHD = " + UHD);
			statement.execute(UHD);
			System.out.println("Query USG2 = " + USG2);
			statement.execute(USG2);
			System.out.println("Query UPH = " + UPH);
			statement.execute(UPH);
			System.out.println("Query OUD2 = " + OUD2);
			statement.execute(OUD2);
			System.out.println("Query OIU3 = " + OIU3);
			statement.execute(OIU3);
			System.out.println("Query OIO2 = " + OIO2);
			statement.execute(OIO2);
			System.out.println("Query OSI3 = " + OSI3);
			statement.execute(OSI3);
			System.out.println("Query ORC2 = " + ORC2);
			statement.execute(ORC2);
			System.out.println("Query UPD2 = " + UPD2);
			statement.execute(UPD2);
			System.out.println("Query OSH3 = " + OSH3);
			statement.execute(OSH3);
			System.out.println("Query UPP2 = " + UPP2);
			statement.execute(UPP2);
			System.out.println("Query OSH4 = " + OSH4);
			statement.execute(OSH4);
			System.out.println("Query PCQ2 = " + PCQ2);
			statement.execute(PCQ2);
			System.out.println("Query OSI4 = " + OSI4);
			statement.execute(OSI4);
			System.out.println("Query USR = " + USR);
			statement.execute(USR);

			statement.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null)
					statement.close();
			} catch (SQLException se2) {
			}// nothing we can do
		}
	}
}// end