Untitled

import com.liferay.portal.kernel.dao.jdbc.AutoBatchPreparedStatementUtil;
import com.liferay.portal.kernel.dao.jdbc.DataAccess;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DeleteOrphanedUsersUserGroupsData {

	public void doDeleteOrphanedUsersUserGroupsData() {
		Connection con = null;
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		PreparedStatement ps3 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;

		try {
			con = DataAccess.getConnection();

			ps1 = con.prepareStatement(
				"select distinct(userId) from Users_UserGroups where userId " +
					"not in (select userId from User_)");

			ps2 = con.prepareStatement(
					"select distinct(userId) from Users_Roles where userId " +
							"not in (select userId from User_)");
			
			ps3 = AutoBatchPreparedStatementUtil.concurrentAutoBatch(
				con, "delete from Users_UserGroups where userId = ?");

			

			rs = ps1.executeQuery();



			while (rs.next()) {
				long userId = rs.getLong("userId");

				if (_log.isInfoEnabled()) {
					_log.info(
						"Found orphaned user id [" + userId + "] in " +
							"Users_UserGroups table. Queuing entries with " +
								"this user id for deletion.");
				}

				//ps3.setLong(1, userId);

				//ps3.addBatch();
			}

			rs2 = ps2.executeQuery();

			while (rs.next()) {
				long userId2 = rs2.getLong("userId");

				if (_log.isInfoEnabled()) {
					_log.info(
						"Found orphaned user id [" + userId2 + "] in " +
							"Users_Roles table. Queuing entries with " +
								"this user id for deletion.");
				}

				//ps3.setLong(1, userId2);

				//ps3.addBatch();
			}



			//ps3.executeBatch();

			if (_log.isInfoEnabled()) {
				_log.info("Successfully executed all queued deletions");
			}
		}
		catch (Exception e) {
			_log.error("Error while executing script", e);
		}
		finally {
			DataAccess.cleanUp(ps1);
			DataAccess.cleanUp(con, ps2, rs);
		}
	}

	private static Log _log = LogFactoryUtil.getLog(
		DeleteOrphanedUsersUserGroupsData.class);

}

out.println(
	"Running groovy script to delete orphaned data from the Users_UserGroups " +
		"table...");

DeleteOrphanedUsersUserGroupsData deleteOrphanedUsersUserGroupsData =
	new DeleteOrphanedUsersUserGroupsData();

deleteOrphanedUsersUserGroupsData.doDeleteOrphanedUsersUserGroupsData();

out.println("Script finished running.");