一、直接导出数据库中的所有表的数据
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Properties;
import com.util.ExportSQLUtil;
/**
* 测试类导出SQL到本地
* @author willdas
*
*/
public class ExportSQL {
public static void main(String args[]) {
FileInputStream in = null;
try {
in = new FileInputStream("src\\db.properties");
// 读取数据库配置文件
Properties properties = new Properties();
properties.load(in);
// 导出路径
String exportPath = "H:\\test.sql";
String stringSql = ExportSQLUtil.getExportSQL(properties,exportPath);
// 运行导出命令
String os = System.getProperty("os.name"); // 判断系统
if(os.toLowerCase().startsWith("win")){
Process process = Runtime.getRuntime().exec(new String[]{"cmd","/c",stringSql}); // windows
process.waitFor();
}else if(os.toLowerCase().startsWith("linux")){
Process process = Runtime.getRuntime().exec(new String[]{"/bin/sh","-c",stringSql}); //linux
process.waitFor();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* @Description: TODO(导出SQL工具包)
* @author willdas
*/
public class ExportSQLUtil {
public static String getExportSQL(Properties properties,String exportPath){
StringBuffer sbf = new StringBuffer();
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
String host = properties.getProperty("jdbc.host");
String port = properties.getProperty("jdbc.port");
String dataBaseName = properties.getProperty("jdbc.dataBaseName");
sbf.append("mysqldump");
sbf.append(" -h").append(host); // 主机
sbf.append(" -P").append(port); // 端口号*大写P
sbf.append(" -u").append(username); // 用户名
sbf.append(" -p").append(password); // 密码
sbf.append(" ").append(dataBaseName); // 数据库名
sbf.append(" > ");
sbf.append(exportPath); // 导出路径
return sbf.toString();
}
}
配置文件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/show
jdbc.username=root
jdbc.password=root
jdbc.host=127.0.0.1
jdbc.port=3366
jdbc.dataBaseName=mytest
二、按查询条件导出多张表的数据
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Properties;
import com.util.ExportSQLUtil;
/**
* 测试类导出SQL到本地
* 根据关联id 导出数据
* @author willdas
*
*/
public class ExportSQL {
public static void main(String args[]) {
FileInputStream in = null;
try {
in = new FileInputStream("src\\db.properties");
// 读取数据库配置文件
Properties properties = new Properties();
properties.load(in);
// 导出路径
String exportPath = "H:\\test.sql";
//查询条件
String classId = "102";
String stringSql = ExportSQLUtil.getExportSQL(properties,exportPath,classId);
// 运行导出命令
String os = System.getProperty("os.name"); // 判断系统名称
if(os.toLowerCase().startsWith("win")){
Process process = Runtime.getRuntime().exec(new String[]{"cmd","/c",stringSql}); // windows
process.waitFor();
}else if(os.toLowerCase().startsWith("linux")){
Process process = Runtime.getRuntime().exec(new String[]{"/bin/sh","-c",stringSql}); //linux
process.waitFor();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}finally {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
/**
* @Description: TODO(导出SQL工具包)
* @author willdas
*/
public class ExportSQLUtil {
public static String getExportSQL(Properties properties,String exportPath,String classId){
String username = properties.getProperty("jdbc.username");
String password = properties.getProperty("jdbc.password");
String host = properties.getProperty("jdbc.host");
String port = properties.getProperty("jdbc.port");
String dataBaseName = properties.getProperty("jdbc.dataBaseName");
StringBuffer sbf = new StringBuffer();
sbf.append("mysqldump -h"+host+" -P"+port+" -u"+username+" -p"+password+" "+dataBaseName);
sbf.append(" class "); // 班级表
sbf.append(" teacher "); // 教师表
sbf.append(" student "); // 学生表
sbf.append("--where=\"class_id="+classId+"\""); // 查询条件
sbf.append(" >> ");
sbf.append(exportPath); // 导出路径
return sbf.toString();
}
}
配置问件
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://127.0.0.1:3306/show
jdbc.username=root
jdbc.password=root
jdbc.host=127.0.0.1
jdbc.port=3366
jdbc.dataBaseName=mytest
数据库表结构