[Sqlite] 使用Java程式、cmd命令列來備份恢復Sqlite資料庫

mchdba發表於2014-09-03
SQLiteJava命令列資料庫

1，透過命令列使用.dump來備份成sql檔案的方式

命令語句：

C:/sqlite/sqlite3 tim.db .dump >test.sql

.dump ?TABLE? ...      Dump the database in an SQL text format

                         If TABLE specified, only dump tables matching

                         LIKE pattern TABLE.
執行效果如下圖所示，可以看到備份的sql檔案內容：

 

2，透過.read 語句來恢復資料庫

命令語句：

C:/sqlite/sqlite3 tim2.db “.read c:/sqlite/test.sql

        .read FILENAME         Execute SQL in FILENAME
     執行效果如下圖所示：

    


 

3，透過java程式碼實現對sqlite資料庫的備份恢復操作


Java程式碼如下：

  1. import java.io.*;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7.  
  8. public class SqlitBackup {
  9.     public String db_source=\"jdbc:sqlite://c:/sqlite/tim.db\";
  10.     public String backup_file=\"c:/sqlite/alldbbackup.sql\";
  11.     public static Connection conn = null;
  12.     public static Statement stat = null;
  13.  
  14.     /**
  15.      * 建構函式初始化資料來源*/
  16.     public SqlitBackup() {
  17.         // TODO Auto-generated constructor stub
  18.         try {
  19.             Class.forName(\"org.sqlite.JDBC\");
  20.             conn = DriverManager.getConnection(db_source);
  21.             stat = conn.createStatement();
  22.            
  23.         } catch (Exception e) {
  24.             // TODO Auto-generated catch block
  25.             e.printStackTrace();
  26.         }
  27.     }
  28.  
  29.     public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
  30.         // TODO Auto-generated method stub
  31.         SqlitBackup sqlite =new SqlitBackup();
  32.         // 1 ，錄入初始化資料
  33.         sqlite.init_data();
  34.        
  35.         // 2，開始備份
  36.         sqlite.backup();
  37.        
  38.         // 3，刪除原有的資料
  39.         sqlite.dropDb();
  40.        
  41.         // 4，透過備份檔案恢復資料
  42.          sqlite.restore();
  43.        
  44.         // 5，關閉連線和資料來源
  45.  
  46.         stat.close();
  47.         conn.close();
  48.     }
  49.    
  50.    
  51.  
  52.     /*
  53.      * 恢復sqlite資料庫**/
  54.     private void restore() throws IOException, SQLException, ClassNotFoundException{
  55.         Runtime rt = Runtime.getRuntime();
  56.         String cmd=\"c:/sqlite/sqlite3 c:/sqlite/tim.db \\\".read \"+backup_file+\"\\\"\";
  57.         Process process = rt.exec( cmd);
  58.         Class.forName(\"org.sqlite.JDBC\");
  59.         conn = DriverManager.getConnection(db_source);
  60.         stat = conn.createStatement();
  61.         ResultSet rs2 = stat.executeQuery(\"select * from sqlite_master;\"); // 查詢資料
  62.         System.out.println(\"4，資料已經恢復資料操作演示：\");
  63.         while (rs2.next()) { // 將查詢到的資料列印出來
  64.             System.out.print(\"tbl_name = \" + rs2.getString(\"tbl_name\") + \", \"); // 列屬性一
  65.         }
  66.         rs2.close();
  67.     }
  68.        
  69.    
  70.     /*
  71.      * 刪除表**/
  72.     private void dropDb (){
  73.         try {
  74.             stat.executeUpdate(\"DROP TABLE IF EXISTS COMPANY; \");
  75.             stat.executeUpdate(\"DROP TABLE IF EXISTS t1; \");
  76.             System.out.println(\"3，表已經刪除成功\");
  77.            
  78.         } catch (SQLException e) {
  79.             // TODO Auto-generated catch block
  80.             e.printStackTrace();
  81.         }
  82.        
  83.     }
  84.    
  85.     /*
  86.      * 備份sqlite資料庫*/
  87.     private void backup() throws SQLException, IOException{
  88.         Runtime rt = Runtime.getRuntime();
  89.         String cmd=\"c:/sqlite/sqlite3 c:/sqlite/tim.db .dump\";
  90.         Process process = rt.exec( cmd);
  91.         try{
  92.          InputStream in = process.getInputStream();// 控制檯的輸出資訊作為輸入流
  93.            InputStreamReader xx = new InputStreamReader(in, \"utf-8\");
  94.            // 設定輸出流編碼為utf-8。這裡必須是utf-8，否則從流中讀入的是亂碼
  95.            String inStr;
  96.            StringBuffer sb = new StringBuffer(\"\");
  97.            String outStr = null;
  98.            // 組合控制檯輸出資訊字串
  99.            BufferedReader br = new BufferedReader(xx);
  100.            while ((inStr = br.readLine()) != null) {
  101.             sb.append(inStr + \"\\r\\n\");
  102.            }
  103.            outStr = sb.toString();
  104.            System.out.println();
  105.            System.out.println(\"2，備份出來的sql檔案內容是,outStr:\\r\"+outStr);
  106.  
  107.             // 要用來做匯入用的sql目標檔案：
  108.            FileOutputStream fout = new FileOutputStream(backup_file);
  109.            OutputStreamWriter writer = new OutputStreamWriter(fout, \"utf-8\");
  110.            writer.write(outStr);
  111.            writer.flush();
  112.            in.close();
  113.            xx.close();
  114.            br.close();
  115.            writer.close();
  116.            fout.close();
  117.           } catch (Exception e) {
  118.            e.printStackTrace();
  119.           }
  120.  
  121.        
  122.     }
  123.    
  124.  
  125.     private void init_data(){
  126.         /*初始化建立2張表，錄入測試資料*/
  127.         try {
  128.             // System.out.println(init_sql1);
  129.             stat.executeUpdate(\"DROP TABLE IF EXISTS COMPANY; \");
  130.             stat.executeUpdate(\"CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));\");
  131.             stat.executeUpdate(\"INSERT INTO COMPANY VALUES(2,\'Allen\',25,\'Texas\',15000);\");
  132.             stat.executeUpdate(\"INSERT INTO COMPANY VALUES(3,\'Teddy\',23,\'Norway\',20000); \");
  133.            
  134.             stat.executeUpdate(\"DROP TABLE IF EXISTS t1; \");
  135.             stat.executeUpdate(\"CREATE TABLE t1(id int);\");
  136.             stat.executeUpdate(\"INSERT INTO t1 VALUES(1);\");
  137.             stat.executeUpdate(\"INSERT INTO t1 VALUES(2);\");
  138.  
  139.             // stat.executeUpdate(init_sql1);
  140.             ResultSet rs = stat.executeQuery(\"select * from COMPANY;\"); // 查詢資料
  141.             System.out.println(\"1，初始化建立表結構錄入資料操作演示：\");
  142.             while (rs.next()) { // 將查詢到的資料列印出來
  143.                 System.out.print(\"name = \" + rs.getString(\"name\") + \", \"); // 列屬性一
  144.                 System.out.println(\"salary = \" + rs.getString(\"salary\")); // 列屬性二
  145.                
  146.             }
  147.             rs.close();
  148.         } catch (SQLException e) {
  149.             // TODO Auto-generated catch block
  150.             e.printStackTrace();
  151.         }
  152.            
  153.        
  154.     }
  155.  
  156. }


執行結果如下：

(1)，初始化建立表結構錄入資料操作演示：

name = Allen, salary = 15000

name = Teddy, salary = 20000

 

(2)，備份出來的sql檔案內容是,outStr:

PRAGMA foreign_keys=OFF;

BEGIN TRANSACTION;

CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));

INSERT INTO "COMPANY" VALUES(2,'Allen',25,'Texas',15000);

INSERT INTO "COMPANY" VALUES(3,'Teddy',23,'Norway',20000);

CREATE TABLE t1(id int);

INSERT INTO "t1" VALUES(1);

INSERT INTO "t1" VALUES(2);

COMMIT;

 

(3)，表已經刪除成功

 

(4)，資料已經恢復資料操作演示：

name = Allen, salary = 15000

name = Teddy, salary = 20000



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26230597/viewspace-1263263/，如需轉載，請註明出處，否則將追究法律責任。

相關文章