I was looking for a way to import and export sql files to/from a mysql database from within a Java application (i.e. I want to let users upload a .sql file in the application and import this file, similarly I want them to be able to create a dump of the database they uploaded before) . Piece of cake I thought. I couldn´t be more wrong.
A quick look at JDBC showed me that there was no useful method there (sure, you can execute DDL and DML statements but not do a bulk insert from a file). Next logical step was to check whether mysql itself offered a Java library that I could import to do this and other advanced things not covered by JDBC. No luck. No specific library for poor users like me.
Then, I tried to get the get the Java runtime and directly execute the mysql commands for that (e.g. see this source code). This works well in my web server but I don´t have access to these commands in the deployed application in the cloud.
So, it seems that I´m now left with the looming alternative of implementing my own SQL parser to process the files myself and split the instructions to use standard JDBC to execute the SQL (this suggestion of trying to adapt Flyway does not convince me either).
It´s a hard pill to swallow that in this era of programmable web with thousands of APIs , there´s no API that solves this basic need. Please, tell me that I´m wrong and suggest a solution in the comments!!!