Java - MySQL - Temporary Tables

Question

Hi all,

I periodically receive data that I use to update my database with. The external structure differs from my internal structure so what I end up doing is running the import and then running alter table commands. I do this manually. After I format it to my liking, I export the data and then import it into my existing schema.

My questions are: 1. How can I isolate the external SQL so that it does not adversely affect my database? Ideally, I would like to run it as another user in another database / workspace. Should I create a database temporarily and then drop it once this operation is complete?

1. Should I connect directly using JDBC to run all these queries since there will be a large sum of data? I am using Hibernate along with C3P0 to manage the primary connection.

2. Lastly, is there an API to automate/simplify exporting to SQL? If I go the JDBC route, I can iterate through each row and create the insert statements from that.

Any ideas?

Thanks, Walter

Answer 1

IMO, its better to do that outside of Hibernate, using simple JDBC. Just create a connection for this thing, and execute all SQL statements. In the end close the connection. This way its handy to make a connection to another temporary database, if you choose this route. You will not need to configure all that into your Hibernate configuration.

Other way is to go with Hibernate and let it create the schema for you using entity objects and their mappings. This way you don't need to manually come up with the database structure required, it will be automatically created by Hibernate.