com.microdeveloper.db.rectifier
Class Differences

java.lang.Object
  com.microdeveloper.db.rectifier.Differences

public class Differences
extends Object

Determines the data differences between two tables in one or two Oracle databases. To use the class, you can minimally specify the owner and table name or you can specify the particular columns to compare. To synchronize the tables use the Rectify class. The source can be either a table or a view but the target must be a table. While the tables do not need to structurally agree to perform a comparison, the column names used in the comparison must agree and be of the same type or an SQLException will be thrown.

HashMap Result Statistics
The differences found are returned in a HashMap structure that contains statistics related to the comparison as well as lists of the actual differences. Each Differences HashMap contains the following:

• Source Orphan Count (integer)
• Source Orphan List (HashMap)
  • KeyColumn (String)
  • Delimited record values (String)
• Source Record Count (integer)
• Target Orphan Count (integer)
• Target Orphan List (HashMap)
• KeyColumn (String)
• Delimited record values (String)
• Target Record Count (integer)
• Difference Count (integer)
• Difference List (HashMap)
  • KeyColumn [NEW] (String)
  • Delimited record values (String)
  • KeyColumn [OLD] (String)
  • Delimited record values (String)
• Warnings Count (integer)
• List of problem (skipped) key values (HashMap)
  • KeyColumn (String)
  • Description of the problem (String)

Usage:
The simplest comparison usage would be where the two tables exist in the same database and all of the columns and rows are used in the comparison. For example, to compare the tables 'emp' and 'emp2', both of which are in the 'SCOTT' schema, we would simply create a HashMap as follows:

// Assume the database connection has already been defined and opened in the object 'conn'
 HashMap m = new Differences.getDifferences(conn, "scott", "emp", "scott", "emp2", "emp_id");

To limit the comparison, you can specify a list of columns to compare by supplying a singular array of column names. For example:

String[] cols = new String[3];
 cols[0] = "first_name";
 cols[1] = "last_name";
 cols[2] = "city";
 HashMap m = Differences.getDifferences(conn, "scott", "emp", "scott", "emp2", "emp_id", cols);

You can also limit the rows compared by specifying the where clause. The where clause can be specified for either the source, the target, or both. For example:

String whereClause = "WHERE mgr_id = '123'";
 HashMap m = new Differences.getDifferences(conn, conn, "scott", "emp", whereClause, "scott", "emp2", whereClause, "emp_id", cols);

You can compare across databases as well by specifyng more than one connection object. For example:

Connection conn1 = new Connector("orcl_1").getConnection();
 Connection conn2 = new Connector("orcl_2").getConnection();
 HashMap m = new Differences.getDifferences(conn1, conn2, "scott", "emp", whereClause, "scott", "emp2", whereClause, "emp_id", cols);

There are many variants that allow one or more database connections, where clause limitations, or specific column mappings. These are only a few examples of what is possible.

Since:

1.6

Version:

1.0

Author:

MicroDeveloper, Inc.

See Also:

Rectify

Field Summary

static String	DELETE_MAP_KEY Key to the HashMap that contains the delete (target orphan) elements.
static String	DIFF_KEY Key for the count of differing records
static String	FIELD_SEP_CHAR Character sequence used to separate field values during comparison
static String	INSERT_MAP_KEY Key to the HashMap that contains the insert (source orphan) elements.
static String	OBJECT_EXISTS_ERR Error when a given object requested for comparison is not found for the connection given
static String	PROBLEM_MAP_KEY Key to the HashMap that contains the data key elements with problems.
static String	SELF_SYNC_ERR Error for when the source and the target are the same (self comparison)
static String	SRC_CLOSED_ERR Error when source database connection is not open
static String	SRC_COUNT_KEY Key for the count of records compared from the source
static String	SRC_ORPHAN_KEY Key for the count of orphan records in the source
static String	TGT_CLOSED_ERR Error when target database connection is not open
static String	TGT_COUNT_KEY Key for the count of records compared from the target
static String	TGT_ORPHAN_KEY Key for the count of orphan records in the target
static String	UPDATE_MAP_KEY Key to the HashMap that contains the updated (differing) elements.
static String	WARNING_KEY Key for the count of warnings during the comparison

Constructor Summary

Differences()
Creates a new instance of Differences

Method Summary

HashMap	getDifferences(Connection srcConn, Connection tgtConn, String srcOwner, String srcTableName, String tgtOwner, String tgtTableName, String whereClause, String keyColumn, String[] comparisonColumns) Creates a response HashMap that outlines the differences between objects on two data sources using a limited rowset and specific comparison columns.
HashMap	getDifferences(Connection conn, String srcOwner, String srcTableName, String tgtOwner, String tgtTableName, String keyColumn) Creates a response HashMap that outlines the differences between objects on a single data source.
HashMap	getDifferences(Connection conn, String srcOwner, String srcTableName, String tgtOwner, String tgtTableName, String keyColumn, String[] comparisonColumns) Creates a response HashMap that outlines the differences between objects on a single data source using specific comparison columns.

Methods inherited from class java.lang.Object

clone, equals, finalize, getClass, hashCode, notify, notifyAll, toString, wait, wait, wait

Field Detail

SRC_ORPHAN_KEY

public static final String SRC_ORPHAN_KEY

Key for the count of orphan records in the source

See Also:

Constant Field Values

TGT_ORPHAN_KEY

public static final String TGT_ORPHAN_KEY

Key for the count of orphan records in the target

See Also:

Constant Field Values

SRC_COUNT_KEY

public static final String SRC_COUNT_KEY

Key for the count of records compared from the source

See Also:

Constant Field Values

TGT_COUNT_KEY

public static final String TGT_COUNT_KEY

Key for the count of records compared from the target

See Also:

Constant Field Values

DIFF_KEY

public static final String DIFF_KEY

Key for the count of differing records

See Also:

Constant Field Values

WARNING_KEY

public static final String WARNING_KEY

Key for the count of warnings during the comparison

See Also:

Constant Field Values

PROBLEM_MAP_KEY

public static final String PROBLEM_MAP_KEY

Key to the HashMap that contains the data key elements with problems. The structure of the internal HashMap is Key=keyColumn and value = problem text

See Also:

Constant Field Values

INSERT_MAP_KEY

public static final String INSERT_MAP_KEY

Key to the HashMap that contains the insert (source orphan) elements. The structure of the internal HashMap is Key=keyColumn and value = column values separated by FIELD_SEP_CHAR characters

See Also:

Constant Field Values

UPDATE_MAP_KEY

public static final String UPDATE_MAP_KEY

Key to the HashMap that contains the updated (differing) elements. The structure of the internal HashMap is Key=keyColumn [NEW/OLD] and value = column values separated by FIELD_SEP_CHAR characters. There are always two records per element; one for the new (source) elemenet and one for the old (target) element.

See Also:

Constant Field Values

DELETE_MAP_KEY

public static final String DELETE_MAP_KEY

Key to the HashMap that contains the delete (target orphan) elements. The structure of the internal HashMap is Key=keyColumn and value = column values separated by FIELD_SEP_CHAR characters

See Also:

Constant Field Values

FIELD_SEP_CHAR

public static final String FIELD_SEP_CHAR

Character sequence used to separate field values during comparison

See Also:

Constant Field Values

SRC_CLOSED_ERR

public static final String SRC_CLOSED_ERR

Error when source database connection is not open

See Also:

Constant Field Values

TGT_CLOSED_ERR

public static final String TGT_CLOSED_ERR

Error when target database connection is not open

See Also:

Constant Field Values

OBJECT_EXISTS_ERR

public static final String OBJECT_EXISTS_ERR

Error when a given object requested for comparison is not found for the connection given

See Also:

Constant Field Values

SELF_SYNC_ERR

public static final String SELF_SYNC_ERR

Error for when the source and the target are the same (self comparison)

See Also:

Constant Field Values

Constructor Detail

Differences

public Differences()

Creates a new instance of Differences

Method Detail

getDifferences

public HashMap getDifferences(Connection srcConn,
                              Connection tgtConn,
                              String srcOwner,
                              String srcTableName,
                              String tgtOwner,
                              String tgtTableName,
                              String whereClause,
                              String keyColumn,
                              String[] comparisonColumns)
                       throws SQLException

Creates a response HashMap that outlines the differences between objects on two data sources using a limited rowset and specific comparison columns. The HashMap consists of several elements that correspond to the number of orphans in the source, number of orphans in the target, and differing records between the two. If any non-critical problems are detected with the source data, it is reported in the a warnings key, otherwise the warnings key has an empty value. The resulting map contains the counts of differences along with the complete differing set of records contained in various embedded Maps.

Parameters:

srcConn - connection to the source database

tgtConn - connection to the target database

srcOwner - owning schema of the source table or view

srcTableName - source table or view name

tgtOwner - owning schema of the target table

tgtTableName - target table name

whereClause - where clause to limit rowset

keyColumn - field used as the key column

comparisonColumns - array of fields used for the comparison

Returns:

HashMap outlining the differences and warnings found

Throws:

SQLException

getDifferences

public HashMap getDifferences(Connection conn,
                              String srcOwner,
                              String srcTableName,
                              String tgtOwner,
                              String tgtTableName,
                              String keyColumn,
                              String[] comparisonColumns)
                       throws SQLException

Creates a response HashMap that outlines the differences between objects on a single data source using specific comparison columns. The HashMap consists of several elements that correspond to the number of orphans in the source, number of orphans in the target, and differing records between the two. If any non-critical problems are detected with the source data, it is reported in the a warnings key, otherwise the warnings key has an empty value. The resulting map contains the counts of differences along with the complete differing set of records contained in various embedded Maps.

This variant assumes that all columns except the key column are to be compared for equality on the same database.

Parameters:

conn - connection to the source and target database

srcOwner - owning schema of the source table or view

srcTableName - source table or view name

tgtOwner - owning schema of the target table

tgtTableName - target table name

keyColumn - field used as the key column

comparisonColumns - array of fields used for the comparison

Returns:

HashMap outlining the differences and warnings found

Throws:

SQLException

getDifferences

public HashMap getDifferences(Connection conn,
                              String srcOwner,
                              String srcTableName,
                              String tgtOwner,
                              String tgtTableName,
                              String keyColumn)
                       throws SQLException

Creates a response HashMap that outlines the differences between objects on a single data source. The HashMap consists of several elements that correspond to the number of orphans in the source, number of orphans in the target, and differing records between the two. If any non-critical problems are detected with the source data, it is reported in the a warnings key, otherwise the warnings key has an empty value. The resulting map contains the counts of differences along with the complete differing set of records contained in various embedded Maps.

This variant assumes that all columns except the key column are to be compared for equality, all rows are to be compared, and that both the source and target exist within the same database.

Parameters:

conn - connection to the source and target database

srcOwner - owning schema of the source table or view

srcTableName - source table or view name

tgtOwner - owning schema of the target table

tgtTableName - target table name

keyColumn - field used as the key column

Returns:

HashMap outlining the differences and warnings found

Throws:

SQLException