Use Java to import and export MySql Databases

The general idea of using Java for Mysql Database Import and Export is to use Java to call the command window to execute the corresponding commands.

The command for MySql to export a database is as follows:

 
 

  
  
mysqldump -uusername -ppassword -hhost -Pport exportDatabaseName > exportPath 
 
 

Using Java to call the command window to execute commands to import MySql into the database is generally divided into three steps:

Step 1: log on to the Mysql database. You can also specify the database to log on to when logging on to the database. If you specify the database, skip step 2;

Step 2: Switch the database to the target database to be imported

Step 3: Use commands to start importing

When exporting, pay attention to the running environment of the command statement. If you have added the bin in the mysql installation path

In the path variable of the system, you can directly use the command statement during export. Otherwise, you need to execute the command statement

Add the path where the command is located, that is, the mysqldump command under the bin in the mysql installation path.

The basic code is as follows:

 
 

  
  
Import java. io. IOException;
  
  
Import java. io. InputStream;
  
  
Import java. io. OutputStream;
  
  
Import java. io. OutputStreamWriter;
  
  
Import java. util. Properties;
  
  
 
  
  
/**
  
  
* When exporting data, pay attention to the running environment of command statements. If you have added the bin in the mysql installation path
  
  
* In the path variable of the system, you can directly use the command statement during export. Otherwise, you must execute the command statement
  
  
* Add the path where the command is located, that is, the mysqldump command under the bin in the mysql installation path.
  
  
* @ Author andy
  
  
*
  
  
*/
  
  
Public class MySqlImportAndExport {
  
  
 
  
  
Public static void main (String args []) throws IOException {
  
  
InputStream is = MySqlImportAndExport. class. getClassLoader (). getResourceAsStream ("jdbc. properties ");
  
  
Properties properties = new Properties ();
  
  
Properties. load (is );
  
  
// MySqlImportAndExport. export (properties); // I will directly throw an exception
  
  
MySqlImportAndExport. importSql (properties );
  
  
}
  
  

  
  
/**
  
  
* Export the specified database at the specified location to the specified location based on the configuration of the property File
  
  
* @ Param properties
  
  
* @ Throws IOException
  
  
*/
  
  
Public static void export (Properties properties) throws IOException {
  
  
Runtime runtime = Runtime. getRuntime ();
  
  
String command = getExportCommand (properties );
  
  
Runtime.exe c (command); // if there is a simple exception, I will directly throw it
  
  
}
  
  

  
  
/**
  
  
* Import the specified file content at the specified location to the specified database based on the configuration of the property file.
  
  
* Mysql Database Import in the Command window is generally completed in three steps:
  
  
* The first step is to log on to mysql; mysql-uusername-ppassword-hhost-Pport-DdatabaseName; if the database name is specified during login
  
  
* Directly go to the database, so that you can skip step 2 and step 3;
  
  
* Step 2: switch to the imported target database; use importDatabaseName;
  
  
* Step 3: import data from the target file to the target database; source importPath;
  
  
* @ Param properties
  
  
* @ Throws IOException
  
  
*/
  
  
Public static void importSql (Properties properties) throws IOException {
  
  
Runtime runtime = Runtime. getRuntime ();
  
  
// Because mysql Database Import in the Command window is generally divided into three steps, the executed command will appear in the form of a string array
  
  
String cmdarray [] = getImportCommand (properties); // obtain the commands required for Database Import Based on the configuration of the attribute file to form an array
  
  
// Runtime.exe c (cmdarray); // It is also simple to directly throw an exception.
  
  
Process process = runtime.exe c (cmdarray [0]);
  
  
// After the first command is executed, you have logged on to mysql. Therefore, the mysql Command window is used.
  
  
// Code after Process Execution
  
  
OutputStream OS = process. getOutputStream ();
  
  
OutputStreamWriter writer = new OutputStreamWriter (OS );
  
  
// Execute commands 1 and 2 together
  
  
Writer. write (cmdarray [1] + "\ r \ n" + cmdarray [2]);
  
  
Writer. flush ();
  
  
Writer. close ();
  
  
OS. close ();
  
  
}
  
  

  
  
/**
  
  
* Assemble command statements using the configuration provided by the property File
  
  
* Note the following when assembling command statements: Generally, you can directly use the command in the Command window
  
  
* When exporting data, you can simply use ">" to indicate where the data is exported, that is, mysqldump-uusername-ppassword databaseName> exportPath,
  
  
* But in Java, writing like this won't work. You need to use-r to explicitly specify the export location, for example:
  
  
* Mysqldump-uusername-ppassword databaseName-r exportPath.
  
  
* @ Param properties
  
  
* @ Return
  
  
*/
  
  
Private static String getExportCommand (Properties properties ){
  
  
StringBuffer command = new StringBuffer ();
  
  
String username = properties. getProperty ("jdbc. username"); // User Name
  
  
String password = properties. getProperty ("jdbc. password"); // User password
  
  
String exportDatabaseName = properties. getProperty ("jdbc. exportDatabaseName"); // name of the database to be exported
  
  
String host = properties. getProperty ("jdbc. host"); // the host from which the database is exported. If this value is not specified, localhost is used by default.
  
  
String port = properties. getProperty ("jdbc. port"); // The port number used
  
  
String exportPath = properties. getProperty ("jdbc. exportPath"); // export path
  
  

  
  
// Pay attention to spaces and spaces
  
  
Command. append ("mysqldump-u "). append (username ). append ("-p "). append (password) // The password is a small p, while the port is a large P.
  
  
. Append ("-h "). append (host ). append ("-P "). append (port ). append (""). append (exportDatabaseName ). append ("-r "). append (exportPath );
  
  
Return command. toString ();
  
  
}
  
  

  
  
/**
  
  
* According to the configuration of the attribute file, the command required to import data from the target file to the target database is obtained in three steps.
  
  
* If the database name is specified during login
  
  
* Directly go to the database, so that you can skip step 2 and step 3;
  
  
* @ Param properties
  
  
* @ Return
  
  
*/
  
  
Private static String [] getImportCommand (Properties properties ){
  
  
String username = properties. getProperty ("jdbc. username"); // User Name
  
  
String password = properties. getProperty ("jdbc. password"); // password
  
  
String host = properties. getProperty ("jdbc. host"); // host of the imported target database
  
  
String port = properties. getProperty ("jdbc. port"); // The port number used
  
  
String importDatabaseName = properties. getProperty ("jdbc. importDatabaseName"); // name of the imported target database
  
  
String importPath = properties. getProperty ("jdbc. importPath"); // the location of the imported target file
  
  
// Step 1: Obtain the logon command statement
  
  
String loginCommand = new StringBuffer (). append ("mysql-u "). append (username ). append ("-p "). append (password ). append ("-h "). append (host)
  
  
. Append ("-P"). append (port). toString ();
  
  
// Step 2: Obtain the command statement for switching the database to the target database
  
  
String switchCommand = new StringBuffer ("use"). append (importDatabaseName). toString ();
  
  
// Step 3: Obtain the imported command statement
  
  
String importCommand = new StringBuffer ("source"). append (importPath). toString ();
  
  
// Array of command statements to be returned
  
  
String [] commands = new String [] {loginCommand, switchCommand, importCommand };
  
  
Return commands;
  
  
}
  
  

  
  
}
 
 

The jdbc. properties file used above

 
 

  
  
jdbc.username=root  
  
  
jdbc.password=password 
  
  
jdbc.host=localhost  
  
  
jdbc.port=3306  
  
  
jdbc.exportDatabaseName=dbName  
  
  
jdbc.exportPath=d\:\\dbName.sql  
  
  
jdbc.importDatabaseName=test  
  
  
jdbc.importPath=d\:\\dbName.sql