Java/Mysql How would you inject an entire SQL file to a mysql server?

Question

How would you go about loading a .sql file into a mysql database from a java program? I've tried extracting a sql resource file from the self containted jar, I've try processBuilder to locate and run external shell commands. I've tried about everything and none of which seem like the correct way about doing this.

So to see the problem I'm having simply do this:

on any database do a mysqldump: mysqldump -uroot --routines myDB > ~/Desktop/myDB.sql

Now lets try to recreate this entire database from java/jdbc api. How does one properly and correctly do this with our mydB.sql file?

It might be nice to think you create a nice jdbc statement to loop around your databse file but then it gets ugly and bunk when you do get a section of your dump like this

Problem one will run into below as example:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `DOG`
--

DROP TABLE IF EXISTS `DOG`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `DOG` (
  `DOG_ID` mediumint(9) NOT NULL,
  `OWNER_ID` mediumint(9) NOT NULL,
  PRIMARY KEY (`DOG_ID`),
  KEY `CHANNEL_FK1` (`OWNER_ID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Is this the limitations of the jdbc? Is there some nice api that I'm missing which I can call like mysql.createDatabase(myDB.sql).

did it work for you and you just ask about the correctness of the way or it never worked ? if it didn't, then please tell what was the problematic point: was it locating the file in the jar ? or was it running the file in ProcessBuilder? or both ? — , Apr 13 '12 at 14:53
@A.J.see the edit I just posted. I have fumbled around trying to get this to work but I never feel like its a professional means of doing so. I'm doing a hack job approach and I would like to see the proper way — stackoverflow, Apr 13 '12 at 15:12
possible duplicate of [Running a .sql script using MySQL with JDBC](http://stackoverflow.com/questions/1044194/running-a-sql-script-using-mysql-with-jdbc) — Jeremiah Willcock, Apr 15 '12 at 06:19

Eugene Retunsky · Answer 1 · 2012-04-13T15:40:33.063

0

If it doesn't work via JDBC, then I recommend this.

Usually I use ANT for this sort of tasks (as performing an mysql dump file):

<exec command="mysql -u ${db.user} --password=${db.password} 
                     --database=${db.name}">
    <redirector input="${sql.file}"/>
</exec>

Basically, you can construct and call ant tasks from your code (if you add necessary ant dependencies to your class.

edited Apr 13 '12 at 15:40

answered Apr 13 '12 at 14:40

Eugene Retunsky

13,009
4
52
55

score 0 · Answer 2 · answered Apr 13 '12 at 14:52

Make sure the mysql connector is in your class path, for example java -cp mysql-connector-java-5.1.13-bin.jar or if you build your project with ant or NetBeans, make sure that the mysql connector is included as a library, that way when you build your project, java -jar YourApplication.jar should find lib/mysql-connector-java-5.1.13-bin.jar or what ever.
In your source code, load the driver: Class.forName("com.mysql.jdbc.Driver").newInstance();
Load your .sql file: String sql = new Scanner(new File("yourfile.sql")).useDelimiter("\Z").next();
Connect to your database: Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database",username,password);
Execute your sql: Statement st = conn.createStatement();st.execute(sql);

The above assumes that your .sql file contains SQL statements.

I understand your suggestion but this is not entirely a good solution. some SQL's might have foreign keys to tables that don't exist yet :) they are at the end of the table :) and you can't execute those statements... (you can't even execute those dump files using mysqlworkbench.. you have to use the command line) — Alex, Nov 23 '12 at 10:15

score 0 · Answer 3 · 2012-07-11T00:25:51.950

I am not sure its the best way but it is what worked for me in a similar case where i need to create a database at runtime:

first define the following variable:

private static final String BASE_MYSQL_COMMAND_LINE_STR = "mysql -uUSER -pPASSWORD -h localhost < ";

please replace "USER" and "PASSWORD" with real username and password or just construct the string dynamically if you don't want to hardcode that. just don't have spaces between the -u, -p and the actual values.

define also the following function:

private static void executeCommandInLinuxShell(String command) throws Exception {
            String[] cmd = {"/bin/sh", "-c", command};  
            Process proc = Runtime.getRuntime().exec(cmd);
            BufferedReader bufferedreader = new BufferedReader(new InputStreamReader(proc.getInputStream()));
            String line;
            while ((line = bufferedreader.readLine()) != null);
            bufferedreader.close();
            proc.waitFor();

    }

then, supposing that in your program you have a File object "sqlFile" pointing to the "mydB.sql" db file:

String scmd = BASE_MYSQL_COMMAND_LINE_STR + sqlFile.getAbsolutePath();
executeCommandInLinuxShell(scmd);

Yeah I've done this method too. I thought it was a hack job and felt there had to be an easier way through the jdbc driver — stackoverflow, Apr 13 '12 at 15:41
I don't think there is a way to run a script file in the jdbc driver without parsing the file as the fellow members suggested in the other answers. at least I didn't find back then when i developed the code. yet you can use existing code if you want to do it so that you don't have to deal with all the parsing to know where the commands are. check the accepted answer in the following post: http://stackoverflow.com/questions/1044194/running-a-sql-script-using-mysql-with-jdbc — , Apr 13 '12 at 15:47

Axel Fontaine · Answer 4 · 2012-11-23T08:19:13.480

0

Flyway has an advanced SQL parser and can deal with mysql dumps out of the box. Configuring it for your usecase should be trivial.

edited Nov 23 '12 at 08:19

answered Apr 21 '12 at 18:30

Axel Fontaine

34,542
16
106
137

Java/Mysql How would you inject an entire SQL file to a mysql server?

4 Answers4

Linked