一、直接導出資料庫中的所有表的資料
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
資料庫表結構
版權聲明:本文為CSDN部落客「weixin_34233618」的原創文章,遵循CC 4.0 BY-SA版權協定,轉載請附上原文出處連結及本聲明。
原文連結:https://blog.csdn.net/weixin_34233618/article/details/92377991