ITPub博客

首页 > 数据库 > 数据库开发技术 > [Sqlite] 使用Java程序、cmd命令行来备份恢复Sqlite数据库

[Sqlite] 使用Java程序、cmd命令行来备份恢复Sqlite数据库

原创 数据库开发技术 作者:mchdba 时间:2014-09-03 23:44:57 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Happy is the man who is living by his hobby.

注册时间:2011-09-05

  • 博文量
    232
  • 访问量
    3704104