【原】使用批處理BAT檔案處理Mysql資料庫

在做項目的時候，考慮到項目中很多模組是公用，資料庫也是公用，所以考慮把公用模組的資料庫全部用批處理生產，這樣或多或少提高了一些效率。

處理方法是：

1：用一個txt儲存該項目資料庫名稱，方便建立工程時直接修改為所需的DB名

2：用一個sql檔案儲存建立公用模組資料庫語句

3：用bat檔案實現運行sql，產生DB

具體如下：

//example.sql

CREATE DATABASE databaseName;USE databaseName;CREATE TABLE Register (    userID varchar(255) primary key,    pass_word varchar(255) not null,    register_time datetime);CREATE TABLE Login(userID varchar(255) primary key,nick_name varchar(255) );alter table Register add column aaa varchar(255);

// test.bat

set path=C:\Program Files\MySQL\MySQL Server 5.0\bincd ./set CURR_PATH=%cd%mysql -h localhost -u root --password=123456 < %~dp0%test.sqlpause

bat檔案的路徑需要注意：C:\Program Files\MySQL\MySQL Server 5.0\bin是Mysql的bin檔案目錄。另外引用example.sql需要指定該檔案的目錄（這裡折磨了很久）

下面是java的實現了，直接上代碼不多說了

package cn.eden.gamesever.createdatabase;import java.io.BufferedReader;import java.io.BufferedWriter;import java.io.File;import java.io.FileNotFoundException;import java.io.FileReader;import java.io.FileWriter;import java.io.IOException;/** * <br>operate.txt檔案中輸入需要建立的遊戲資料庫，獲得DBNAME</br>產生Sql檔案 </br>根據DBNAME修改Bat檔案的Sql執行語句</br> * @author lujian */public class RunBat {private static String sqlPath = ".\\sql\\test.sql";private static String batPath = ".\\sql\\create.bat";private static String dataBaseName = "";private static String FileName = ".\\sql\\operate.txt";private static String exampleSqlPath = ".\\sql\\example.sql";/** * 讀工程下的operate.txt檔案獲得Name */public static String DataBaseName() {File file = new File(FileName);try {BufferedReader in = new BufferedReader(new FileReader(file));if ((dataBaseName = in.readLine()) != null) {if (dataBaseName.endsWith(";")) {dataBaseName = dataBaseName.substring(0,dataBaseName.length() - 1);}}in.close();} catch (Exception e) {System.out.println(e);}return dataBaseName;}/** * 修改Sql檔案內容 */public static void writeSql(String dataBaseName) {File exampleFile = new File(exampleSqlPath);File curFile = new File(sqlPath);String content = read(exampleFile);System.out.println(content);content = content.replaceAll("databaseName",dataBaseName);write(content, curFile);System.out.println(content);}/** * 執行bat檔案 * @param databaseName */public static void runBat(){try {Process process = Runtime.getRuntime().exec(batPath);CmdProcess cmdProcess = new CmdProcess(process.getInputStream());cmdProcess.start();} catch (IOException e) {System.err.println("bat檔案不存在或者路徑錯誤");e.printStackTrace();}}public static String read(File src) {StringBuffer res = new StringBuffer();String line = null;//int lineNum = 1;try {BufferedReader reader = new BufferedReader(new FileReader(src));while ((line = reader.readLine()) != null) {//if (lineNum == 1) {//line = line.replaceAll(//line.substring(4, line.length() - 1), dataBaseName);//}//++lineNum;res.append(line + "\n");}reader.close();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}return res.toString();}public static void write(String cont, File dist) {try {BufferedWriter writer = new BufferedWriter(new FileWriter(dist));writer.write(cont);writer.flush();writer.close();} catch (IOException e) {e.printStackTrace();}}//public static String RunBatFile() {////String reslut = ""; //String command = "D:\\j2eeWorkSpace\\TestSql\\sql\\mysqlBatTest.bat";//// String command = "D:\\test.bat";//try {//Process process = Runtime.getRuntime().exec(command);//CmdProcess cmdProcess = new CmdProcess(process.getInputStream());//cmdProcess.start();//reslut = "操作成功！";//} catch (IOException e) {//reslut = "操作失敗！";//e.printStackTrace();//}//return reslut;//}public static void main(String args[]) {DataBaseName();writeSql(dataBaseName);runBat();}public static String getDataBaseName() {return dataBaseName;}public static void setDataBaseName(String dataBaseName) {RunBat.dataBaseName = dataBaseName;}}

package cn.eden.gamesever.createdatabase;import java.io.BufferedReader;import java.io.InputStream;import java.io.InputStreamReader;public class CmdProcess extends Thread{InputStream inputStream;public InputStream getInputStream() {return inputStream;}public void setInputStream(InputStream inputStream) {this.inputStream = inputStream;}public CmdProcess() {super();// TODO Auto-generated constructor stub}public CmdProcess(InputStream inputStream) {super();this.inputStream = inputStream;}public void run() {// TODO Auto-generated method stubtry {InputStreamReader inputStreamReader = new InputStreamReader(inputStream);BufferedReader bufferedReader = new BufferedReader(inputStreamReader);String line = null;while ((line = bufferedReader.readLine()) != null){System.out.println(line);}try{}finally{if(bufferedReader!=null) bufferedReader.close();if(inputStreamReader!=null) inputStreamReader.close();if(inputStream!=null) inputStream.close();}} catch (Exception e) {e.printStackTrace();}}/** * @param args */public static void main(String[] args) {// TODO Auto-generated method stubString command = "C:\\Documents and Settings\\Administrator\\案頭\\aa.bat";try {Process process = Runtime.getRuntime().exec(command);CmdProcess cmdProcess = new CmdProcess(process.getInputStream());cmdProcess.start();process.waitFor();}catch(Exception e){e.printStackTrace();}}}

example.sql用於作為sql語句的模板，便於擴充，每次運行把example.sql的內容先更改資料庫名再複製到test.sql中。bat路徑參考：點這裡