Moving tables contents between databases using JDBC

Thread view:

Rafal Solinski - 06 Aug 2006 17:49 GMT

Hi,

I'm working on a simple program, which migrates tables between
different databases. Assuming that table schemas are exactly
the same (from JDBC point of view - using DatabaseMetaData class
I can check tables schemas) how I can move rows between
databases, without knowing each column type? I just want to
get an object representing for example second column and
insert it into the others table second row. Of course one could
analyse the column type, convert it to String and build
INSERT INTO ... sql instruction, but I am hoping for a simplier
solution.

Regards, Rafal

Reply to this Message

Thomas Kellerer - 06 Aug 2006 21:15 GMT

Rafal Solinski wrote on 06.08.2006 18:51:

> Hi,
>
[quoted text clipped - 8 lines]
> INSERT INTO ... sql instruction, but I am hoping for a simplier
> solution.

Do a SELECT on the source, then use a PreparedStatement for the INSERT on the
target. The use getObject() on the ResultSet you obtain from the source, and do
a setObject() for each column on the target INSERT. But be aware that some
drivers do not correctly implement getObject() and it is not guaranteed that the
Object returned by the driver from the source DB will return a class that the
driver from the target can handle.

But there are OpenSource solutions at sourceforge (don't know the name right
now) which have already solved those problems...

Regards
Thomas

Reply to this Message

Tom Cole - 15 Aug 2006 18:09 GMT

Create a CASE statement using the DATA_TYPE value returned in the
getColumns method of DatabaseMetaData. The int in the DATA_TYPE value
of the resulting ResultSet maps to a java.sql.Types value. Use it in
the case to know which setXxx method to call.

> Rafal Solinski wrote on 06.08.2006 18:51:
> > Hi,
[quoted text clipped - 22 lines]
> Regards
> Thomas

Reply to this Message

Moving tables contents between databases using JDBC

Free Magazines