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

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).

Answers


  1. 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.
  2. In your source code, load the driver: Class.forName("com.mysql.jdbc.Driver").newInstance();
  3. Load your .sql file: String sql = new Scanner(new File("yourfile.sql")).useDelimiter("\Z").next();
  4. Connect to your database: Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/database",username,password);
  5. Execute your sql: Statement st = conn.createStatement();st.execute(sql);

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


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.


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);

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


Need Your Help

determing server response encoding

encoding sockets character-encoding

not java specific, but when I say OutputStream os = sock.getOutputStream();

Sprinkles inside fragment

android android-fragments android-listfragment android-cursor

I have ListFragment (from support package) and I am trying use adapter with Sprinkles library (CursorList). Everything works fine till I change screen orientations. Then I get exception: