logo
down
shadow

Need to sync two tables from different databases in java


Need to sync two tables from different databases in java

By : user9548
Date : November 19 2020, 01:01 AM
will be helpful for those in need Usually to sync a table with in the network I can write a simple sql query and run it to sync the tables, but what if the source database is different(could be db2, mssql, mysql, oracle - from where I can get the data in to destination table). , You can do this with plain JDBC. Code example, not tested:
code :
void migrate() throws SQLException {
        Connection connA = DriverManager.getConnection("jdbc:oracle:thin:@myhost:1521:orcl");
        Connection connB = DriverManager.getConnection("jdbc:mysql://192.168.1.33/test");

        PreparedStatement stmA = connA.prepareStatement("select * from product where 1=1");
        PreparedStatement stmB = connB.prepareStatement("insert into prod values(?,?,?,?)");

        ResultSet rs = stmA.executeQuery();
        while (rs.next()) {
            for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
                stmB.setObject(i + 1, rs.getObject(i + 1));
            }
            stmB.executeUpdate();
        }

        connA.close();
        connB.close();
    }


Share : facebook icon twitter icon
Keep tables in sync between databases on the same server

Keep tables in sync between databases on the same server


By : user2828664
Date : March 29 2020, 07:55 AM
it fixes the issue Personally, I'd go with transactional replication. Your other options would be to do this in code with a synchronous trigger or asynchronously via service broker, which IMHO, is a much bigger pain to set up than replication.
sync two tables from two different PostgreSQL databases

sync two tables from two different PostgreSQL databases


By : user3133155
Date : March 29 2020, 07:55 AM
seems to work fine this example will connect to both the databases, and for each of the first db's authors will update the destination db's author with the same id. Of course you have to set up any necessary check, search and other details before perform and update (or an insert or replace if you prefer), but it fully depends on what you're going to do :)
code :
<?php

if (false !== ($con1 = pg_connect("your source connection string"))) {
  if (false !== ($con2 = pg_connect("your dest connection string"))) {
    if (false !== ($result = pg_query($con1, "SELECT id, author, email FROM authors"))) {
      while (false !== ($row = pg_fetch_assoc($result))) {
        pg_query($con2, "UPDATE authors SET email=".pg_escape_string($con2, $row['email']).
          'WHERE id='.pg_escape_string($con2, $row['id']));
      }
      pg_free_result($result);
    }
    pg_close($con2);
  }
  pg_close($con1);
}

?>
Sync 2 tables of different databases - MySQL

Sync 2 tables of different databases - MySQL


By : user3434094
Date : March 29 2020, 07:55 AM
hop of those help? You may want to use 'SELECT ... INTO OUTFILE' and 'LOAD DATA INFILE INTO TABLE' commands.
Edit: Elaboration...
code :
CREATE TABLE my_local_table (
    id int NOT NULL auto_increment PRIMARY KEY,
    data varchar(20),
    created_on datetime);

CREATE TABLE server_table (
    id int NOT NULL auto_increment PRIMARY KEY,
    data varchar(20),
    created_on datetime,
    local_id int);
INSERT INTO my_local_table (data, created_on) VALUES ('test', now()), ('test2', now());
SELECT id, data, created_on 
    FROM my_local_table
    WHERE created_on >= '2011-08-18'
    INTO OUTFILE '/tmp/t.txt';

-- (and on the server)
LOAD DATA LOCAL INFILE '/tmp/t.txt'
    INTO TABLE server_table
    (local_id, data, created_on);
mysql -e 'SELECT....';
mysql -h remote_server -e 'LOAD DATA...';
How do you sync databases using ms sync framework when tables are using identity column and you need to match on an arbi

How do you sync databases using ms sync framework when tables are using identity column and you need to match on an arbi


By : Harsh Goel
Date : March 29 2020, 07:55 AM
I hope this helps . if you can add an extra column, create a new column based on the "match key" you specified above. Then configure Sync framework to use that column as the PK and ignoring the actual PKs in the table.
the approach should be similar to this post:
Using C# to Sync two tables in different databases

Using C# to Sync two tables in different databases


By : khadija
Date : March 29 2020, 07:55 AM
help you fix your problem There's two solutions to this problem. You could try to be smart and only transfer the changes - but that requires some integration on the source database; I'm sure your db admin can help you with that - potentially triggers that keep track of all touched rows (e.g. using the source table's primary key). This solution scales fairly well, but it's more complicated. I think you should look at a second option: simple brute force.
By the sounds of it, your entire table fits comfortably in 100MB. This just isn't much data. Assuming you can get a transfer rate of 10MB/s (which isn't very outlandish at all), you could transfer everything in just 10 seconds. If, as you say, you only need a few columns, the total data transfer could be considerably less. Going with the 10 second number every five minutes, that'd be on the order of 3% load to keep the data up to date - and it's a trivial query for the source database what will likely not cause much load, especially since it'll all be cached in memory since the data set is so small.
Related Posts Related Posts :
  • Content autocomplete in spring tool suite 4
  • Super Noob Help ~ MouseEvent/Frame
  • Making one project with dependencies stand-alone in Eclipse (Java)
  • Jsoup- getting certain attributes from website
  • MongoDB, how to make a link between collections with JAVA code
  • Overriding an abstract method means class abstract class or not in java?
  • Bukkit teleport - nullPointerException
  • How to check if a Float variable is negative in java
  • Servlet: Cannot forward after response has been committed
  • sql query to select a specific cell in excel
  • How to make new picture when mouse dragged in java
  • Use Constants interface in GWT Project
  • Setting size of a JPanel in GroupLayout
  • Construct a map from two equal size arrays
  • Implementing Read-Write Locks with Double-Checked Locking
  • Disable NO_BUTTON from JOptionPane Java if a condition is true
  • UDP multiple socket
  • Android Threading: This Handler class should be static or leaks might occur
  • The type org.openqa.selenium.firefox.FirefoxDriver is not accessible
  • Limitations (drawbacks) of java programming language?
  • Error: ')' expected Error: illegal start of expression
  • TableRowSorter use LookAndFeel of TableHeader, but own Comparator
  • Difference between @GeneratedValue and @GenericGenerator
  • Design Scenario: Smartphone class and will have derived classes like IPhone,AndroidPhone,WindowsMobilePhone can be even
  • Java date parsing without separators?
  • how to map an array of custom type from postgres to java using hibernate
  • Why hibernate always call "update" statement after using "select" statement in MySQL?
  • An interview topic: What can go wrong with this code?
  • How do I regex remove whitespace and newlines from a text, except for when they are in a json's string?
  • How to turn off Preferences > Compiles > User External Build?
  • how to convert arraylist to string array with all items in double quote
  • PMD+Maven+JAVA Error:: Can't find resource rulesets/comments.xml. Make sure the resource is a valid file or URL or is on
  • How to extend AbstractScheduledService.Scheduler
  • Primefaces tabview: set Active Index after some counting
  • Need help in Regex to exclude splitting string within "
  • Get header from HttpUrlConnection object
  • encoding xlsx as base64 and send it as an attachment
  • How to acess HttpServletRequest in Jaas login module in Tomcat
  • How to implement a basic pointer
  • Java Swing Exit Icon?
  • Why onNext has no effect
  • IntelliJ Idea 2018.2 no option for importing gradle project
  • Remove a negative element from an array w/o array.copy
  • Could this prevent overflow in Java?
  • In Java Swing, can I receive Caret events in real time?
  • I need help not using brute force when using JFrames and DrawWindows
  • How do I refactor ArrayList<MyClass> into MyClassLIst?
  • My program keeps saying that the method cannot be resolved
  • BubbleSort -my code returns random addresses
  • JavaFX components inside HTML?
  • Character in Java game not responding
  • Working around access denied in a FileWalking Tree in Java7
  • How to avoid if/else when using multple suppliers?
  • How to pass object from table cell editor to Table Model?
  • Paypal Sandbox payment state pending
  • sejda-console.bat passing parameters with spaces
  • Multiple Consumers Spring Kafka
  • Bitbucket pipeline: environmental variables in build.gradle file not recognized
  • Java static enum method to return a default enum value
  • What is the complexity of empty for loop?
  • shadow
    Privacy Policy - Terms - Contact Us © ourworld-yourmove.org