SQL文件导入MySQL数据库
1、MySQL设置环境变量
MYSQL_HOME:C:\Program Files (x86)\MySQL\MySQL Server 5.5
path:%MYSQL_HOME%bin
环境变量设置后,打开cmd命令窗口,输入mysql -u用户名 -p密码
登陆mysql
2、MySQL常用命令
source命令:
#进入MySQL数据库控制台,如mysql -uroot -proot
mysql>user 数据库
#使用source命令后面加脚本文件
mysql>source d:/test.sql
cmd窗口下操作命令:
mysql -u用户名 -p密码 -D数据库
3、实现代码
package com.example.service;
import java.io.*;
import java.util.Properties;
public class ImportData {
private static boolean importSQL(Properties properties) {
String host = properties.getProperty("jdbc.host");
String port = properties.getProperty("jdbc.port");
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
String databasename = properties.getProperty("jdbc.databasename");
String file = properties.getProperty("jdbc.file");
StringBuilder stringBuilder = new StringBuilder();
stringBuilder.append("cmd /C ");
stringBuilder.append(" mysql ").append(" -h").append(host).append(" -p").append(port);
stringBuilder.append(" -u").append(username).append(" -p").append(password);
stringBuilder.append(" --default-character-set=utf8 ").append(databasename);
stringBuilder.append(" < ").append(file);
try {
Process process = Runtime.getRuntime().exec(stringBuilder.toString());
input(process.getInputStream());
if (process.waitFor() == 0) {
return true;
}
} catch (IOException | InterruptedException e) {
e.printStackTrace();
}
return false;
}
private static void input(final InputStream inputStream) {
new Thread(
new Runnable() {
@Override
public void run() {
Reader reader = new InputStreamReader(inputStream);
BufferedReader bf = new BufferedReader(reader);
String line = null;
try {
while ((line = bf.readLine()) != null) {
System.out.println(line);
}
} catch (IOException e) {
e.printStackTrace();
} finally {
try {
reader.close();
bf.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
).start();
}
public static void main(String[] args) {
Properties properties = new Properties();
InputStream is = ImportData.class.getClassLoader().getResourceAsStream("jdbc.properties");
try {
properties.load(is);
} catch (IOException e) {
e.printStackTrace();
}
if (importSQL(properties)) {
System.out.println("导入成功");
} else {
System.out.println("导入失败");
}
}
}
资源文件:
jdbc.host=127.0.0.1
jdbc.port=3306
jdbc.username=root
jdbc.password=root
jdbc.databasename=test
jdbc.file=D\:\\Workspaces\\data.sql