Send database data through socket connection

Question

I have a distributed java application and I want to send the database data between two databases which are in seperate computer systems or android devices. Which is the best way to transfer tha table data throw a socket connection?I think about send table rows as a text lines, or xml elements or create a class(implements the Serializable inteface) for every table and tranfer an arraylist of that objects for each table. Which is in your opinion the more effetive solution? There is any other way to do that? the databases may are different like mysql, sqlite, h2database so I can't use any database native function to syncronize(here while I serach for somthing similar I read something about orable database, have a function like this).

Thank you very much!!

Squadron

can you rely on a server to conduct the synchronization? – Claudication

When you say "synchronize" do you mean that the two databases must look the same afterwards or that you need to push data from one database to another? – Searby

@Claudication Yes, for example what I want to do is like have a java application in an pc and an android application after sync I want the application in the pc to have a copy of the database data from the android device and vice versa. – Squadron

@Searby You have right, I just edit my question. I just want to push the database data, and because i'ts my first time doing something like this, I asked which is the more effective solution. – Squadron

@javment: Ok, i answered assuming cross-synchronizing. I'd use the same mechanism for one-way as well - just have the receiving machine being the only subscriber on that topic. – Searby

Two way sync? This area is not for fainted hearts! Simple question: if the same record got updated on both Android and PC, what to do? – Olla

Answer 1

Personally I consider XML too verbose for transferring data, you might end up with half the amount of data for content and half for structure.

<record>
   <name>John</name>
   <age>30</age>
</record>

Most of the space is lost in defining the structure, little is left for the data you want (John, 30).

Plain text lines are too limited, at least consider comma seperated values if you must.

Name;Age
John;30

The first row is just for labels, if you're pretty sure that will never change you can remove it.

Serialization of objects can be too dangerous to transfer data, since you might create incompatibilities over time that make deserialization fail. For example, introducing or removing a field is enough.

Have a look at JSON, it's not as verbose as XML and more structured than just lines. The important part is of course that you handle your data properly at both ends, but given you know the structure sent you can transform it properly.

Example:

{name: John, age: 30 }

Sophi

Answer 2

Just some ideas for the steps i would take if i was doing this.

First read the data from the databases into some common format on each machine or device. Using JPA to do would be easiest.

Because the two devices need to be in sync you need a common spot to compare. Then, if there are discrepancies in the data you need to decide which copy is correct. Or, if this is a single table, simply take a union of both copies and then push that back to each machine.

So, i would use a queuing server to do this. On each machine, read the data using JPA and post it using Gson to the queuing server (for which i would use HornetQ). The data from both machines arrives on an input queue.

Then have one reader for this input queue which munges the data together discarding duplicates and coming up with the canonical view of the data. Finally, this data is then pushed (again with Gson) to a JMS topic to which both (or all) devices are subscribed.

Then each device gets each row of the database and can add or update accordingly. You would need some special log on each device to record deletes if you ever wanted to remove data everywhere as well.

That's just a starter...! :)

Searby

This seems a more complete answer, although I would disagree with software deciding what an appropriate merge is in case of a conflict. It might be wise to add a version column and reject updates based on whether the change was made against the latest version or an older one. Maybe just consider sending a diff to the server. A row id + key/value pairs changed – Sophi