Java MS SQL -> mySQL conversion

Question

I am building an application at work and need some advice. I have a somewhat unique problem in which I need to gather data housed in a MS SQL Server, and transplant it to a mySQL Server every 15 mins.

I have done this previously in C# with a DataGrid, but now am trying to build a Java version that I can run on an Ubuntu Server, but I can not find a similar model for Java.

Just to give a little background

When I pull the data from the MS SQL Server, it always has 9 columns, but could have anywhere from 0 - 1000 rows.

Before inserting into the mySQL Server blindly, I do manipulate some of the data.

I convert a time column to CST based on a STATE column
I strip some characters to prevent SQL injection

I tried using the ResultSet, but I am having issues with the "forward only result sets" rules.

What would be the best data structure to hold that information, manipulate it, and then parse it to insert later into mySQL?

Why are you striping characters from data you get from the MS SQL database? If you get them from a database this should have been done before saving it the first time. — Anonymous Coward
– Anonymous Coward, Commented Nov 9, 2010 at 18:47

bogertron · Accepted Answer · 2010-11-09 19:13:58Z

2

This sounds like a job for PreparedStatements!

Defined here: http://download.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html

Quick example: http://download.oracle.com/javase/tutorial/jdbc/basics/prepared.html

PreparedStatements allows you to batch up sets of data before pushing them into the target database. They also allow you use the PreparedStatement.setString method which handles escaping characters for you.

For the time conversion thing, I would retrieve the STATE value from the row and then retrieve the time value. Before calling PreparedStatement.setDate, convert the time to CST if necessary.

I dont think that you would need all the overhead that an ORM tool requires.

answered Nov 9, 2010 at 19:13

bogertron

2,3551 gold badge25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Martijn Verburg Over a year ago

Agreed, my ORM is a fairly heavyweight alternative, but I've used it in the past when I knew I was going to swap between DB vendors a good deal. +1 :-)

Justin Standard · Accepted Answer · 2010-11-09 21:34:12Z

1

You could consider using an ORM technology like Hibernate. This might seem a little heavyweight at first, but it means you can maintain the various table mappings for various databases with ease as well as having the power of Java's RegEx lib for any manipulation requirements.

So you'd have a Java class that represents the source table (with its Hibernate mapping) and another Java class that represents the target table and lastly a conversion utility class that does any manipulation of that data. Hibernate takes care of the CRUD SQL for you, so no need to worry about Database specific SQL (as long as you get the mapping correct).

It also lessens the SQL injection problem

edited Nov 9, 2010 at 21:34

Justin Standard

21.5k22 gold badges84 silver badges90 bronze badges

answered Nov 9, 2010 at 18:13

Martijn Verburg

3,31523 silver badges26 bronze badges

1 Comment

Martijn Verburg Over a year ago

Let me know if you want more concrete details

Collectives™ on Stack Overflow

Java MS SQL -> mySQL conversion

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related