| Project: |
SyncTable |
| Client: |
MicroWeb
|
| Keywords: |
Java, JDBC, Database Synchronization & Replication |
| Description: |
SyncTable is a Java based developer or database administrator utility to perform data synchronization between two databases.
|
| Platform: |
Java 2 Runtime Environment (Windows, Mac, Unix, Linux)
|
Problem Summary
Several of our clients have the need to replicate data to remote databases that are used for client and web-based queries.
Although the database systems we utilize (MySQL, SQL Server, Oracle, etc) offer replicatation capabilities, our clients
needed a batch mode process that would upload database changes at the end of the work day.
Example
A car dealer uses a remote MySQL database to list their vehciles for sale on their website. The car dealer's master database
is located behind the corporate firewall. At the end of the work day, the car dealer wants to update the website database
with changes made by sales associates during the day.
Solution
SyncTable was scheduled to run at every evening (using the Windows schedule service), to update the website database
records with changes made that day on the master database. In this solution, the master database table contained a
timestamp field indicating the date/time when the vehicle record was last modified. SyncTable used the timestamp field
to determine which records to update or add to the remote database.
Requirements
- Java 2 runtime environment (J2SE)
- JDBC driver for database that will be updated
Tested Systems
Note: SyncTable does NOT work with the Sun Microsystems jdbc:odbc bridge.
Program Usage
SyncTable makes use of a simple configuration file for each table you want synchronized on the remote database.
The configuration file specifies the database connection properties, table to synchronize, table fields and
selection criteria.
Internally, we utilize a single batch file that runs the program using different configuration files for
each database table that needs updating. The batch file is then scheduled to run using the Windows Schedule Service.
Command Line
C:\MicroWeb\SyncTable>java -jar SyncTable.jar <config_file> [<config_option>]
| <config_file> |
The name of the configuration file
Tip: On windows machines use the forward slash character '/' to deliminate folder names if
your configuration file is not in the same folder as the program.
|
| <config_option> |
The config_option is used to override the filter parameter specified in the configuration file that determines
which records will by synchronized with the remote database. If no filter parameter is specified on command line
or configuration file, all records in the master database table will be synchronized with the remote database table.
|
| offset=daysfromtoday |
This option creates a filter using the table timestamp field to determine which records to update on the remote database.
The table timestamp field is defined in the configuration file.
daysfromtoday = integer specifying the number of days from today.
Example 1: To synchronize records that were updated today, use the following:
offset=0
Example 2: To synchronize records that were updated as of yesterday use the following:
offset=-1
Example 2: To synchronize records that were updated within the last 5 days, use the following:
offset=-5
Note: Specifying this command line option overrides the filter parameter specified in the configuration file
|
| filter="filter_parameter" |
This option creates a filter using the fields you specify to determine which records to update on the remote database.
Example 1: To synchronize records having the showOnline field set to 1, use the following:
filter="showOnline=1"
Note 1: Specifying this command line option overrides the filter parameter specified in the configuration file
Note 2: The filter_parameter must be enclosed in quotes.
|
Configuration File Parameters
| app.debug |
[yes|no] : turns debug messages on/off
|
| db.driver |
The JDBC driver used to access the master database.
Example (MySQL database):
db.driver=com.mysql.jdbc.Driver
|
| db.url |
The JDBC driver connection paramters used to access the master database.
Example (MySQL database):
db.url=jdbc:mysql://127.0.0.1/mydatabase?user=myuser&password=mypassword
|
| db.dateformat |
The date format used by the master database.
Example (MySQL database):
db.dateformat=yyyy-MM-dd
|
| dbsync.driver |
The JDBC driver used to access the remote database.
Example (MySQL database):
db.driver=com.mysql.jdbc.Driver
|
| dbsync.url |
The JDBC driver connection paramters used to access the remote database.
Example (MySQL database):
db.url=jdbc:mysql://myhost.mydomain.com/mydatabase?user=myuser&password=mypassword
|
| sync.table |
The name of the table that will be synchronized. The table name must be the same on the master and remote databases.
Example: sync.table=Vehicle
|
| sync.keyfield |
The name of the table primary key field. This must be the same on the master and remote databases.
Example: sync.keyfield=vin
|
| sync.timestampfield |
The name of the table timestamp field used to record field updates.
Example: sync.timestampfield=vehicleUpdated
|
| sync.filter |
The filter string used to determine which records to synchronize.
Example: (synchronize records where the status field is 'forsale' or 'pending' in the master table):
sync.filter=status IN ('forsale','pending')
Note: This may be over-ridden by the command line option [offset | filter]
|
| sync.field.N.name |
SyncTable can synchronize up to 100 table fields. N is a number from 0 to 99 that indicates a field to synchronize.
If the record does not exist on the remote server it will be added.
Example: (synchronize six fields on the remote server)
sync.field.0.name=Year
sync.field.1.name=Make
sync.field.2.name=Model
sync.field.3.name=Bodystyle
sync.field.4.name=Color
sync.field.5.name=Mileage
sync.field.6.name=Status
sync.field.7.name=
:
:
sync.field.99.name=
Note: The numbers do not need to be in order. For example, to skip the Mileage field above,
remove the text after the = character as shown below.
sync.field.5.name=
|
Sample Batch File (SyncTable.bat)
# Run SyncTable to synchronize remote databases with local master
# Use java classpath option to tell system where file is located
# The SyncTable.jar file is installed in the C:\MicroWeb\SyncTable folder
cd C:\MicroWeb\SyncTable
java -jar SyncTable.jar SyncTable01.ini
java -jar SyncTable.jar SyncTable02.ini
java -jar SyncTable.jar SyncTable03.ini
|
