当前位置:Gxlcms > mysql > [Sqlite]--)使用Java程序、cmd命令行来备份恢复Sqlite数据库

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

时间:2021-07-01 10:21:17 帮助过:103人阅读

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. 2,通过.read 语句来

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代码如下:

    import java.io.*;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;

    public class SqlitBackup {
    public String db_source=\"jdbc:sqlite://c:/sqlite/tim.db\";
    public String backup_file=\"c:/sqlite/alldbbackup.sql\";
    public static Connection conn = null;
    public static Statement stat = null;

    /**
    * 构造函数初始化数据源*/
    public SqlitBackup() {
    // TODO Auto-generated constructor stub
    try {
    Class.forName(\"org.sqlite.JDBC\");
    conn = DriverManager.getConnection(db_source);
    stat = conn.createStatement();

    } catch (Exception e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }
    }

    public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {
    // TODO Auto-generated method stub
    SqlitBackup sqlite =new SqlitBackup();
    // 1 ,录入初始化数据
    sqlite.init_data();

    // 2,开始备份
    sqlite.backup();

    // 3,删除原有的数据
    sqlite.dropDb();

    // 4,通过备份文件恢复数据
    sqlite.restore();

    // 5,关闭连接和数据源

    stat.close();
    conn.close();
    }



    /*
    * 恢复sqlite数据库**/
    private void restore() throws IOException, SQLException, ClassNotFoundException{
    Runtime rt = Runtime.getRuntime();
    String cmd=\"c:/sqlite/sqlite3 c:/sqlite/tim.db \\\".read \"+backup_file+\"\\\"\";
    Process process = rt.exec( cmd);
    Class.forName(\"org.sqlite.JDBC\");
    conn = DriverManager.getConnection(db_source);
    stat = conn.createStatement();
    ResultSet rs2 = stat.executeQuery(\"select * from sqlite_master;\"); // 查询数据
    System.out.println(\"4,数据已经恢复数据操作演示:\");
    while (rs2.next()) { // 将查询到的数据打印出来
    System.out.print(\"tbl_name = \" + rs2.getString(\"tbl_name\") + \", \"); // 列属性一
    }
    rs2.close();
    }


    /*
    * 删除表**/
    private void dropDb (){
    try {
    stat.executeUpdate(\"DROP TABLE IF EXISTS COMPANY; \");
    stat.executeUpdate(\"DROP TABLE IF EXISTS t1; \");
    System.out.println(\"3,表已经删除成功\");

    } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }

    }

    /*
    * 备份sqlite数据库*/
    private void backup() throws SQLException, IOException{
    Runtime rt = Runtime.getRuntime();
    String cmd=\"c:/sqlite/sqlite3 c:/sqlite/tim.db .dump\";
    Process process = rt.exec( cmd);
    try{
    InputStream in = process.getInputStream();// 控制台的输出信息作为输入流
    InputStreamReader xx = new InputStreamReader(in, \"utf-8\");
    // 设置输出流编码为utf-8。这里必须是utf-8,否则从流中读入的是乱码
    String inStr;
    StringBuffer sb = new StringBuffer(\"\");
    String outStr = null;
    // 组合控制台输出信息字符串
    BufferedReader br = new BufferedReader(xx);
    while ((inStr = br.readLine()) != null) {
    sb.append(inStr + \"\\r\\n\");
    }
    outStr = sb.toString();
    System.out.println();
    System.out.println(\"2,备份出来的sql文件内容是,outStr:\\r\"+outStr);

    // 要用来做导入用的sql目标文件:
    FileOutputStream fout = new FileOutputStream(backup_file);
    OutputStreamWriter writer = new OutputStreamWriter(fout, \"utf-8\");
    writer.write(outStr);
    writer.flush();
    in.close();
    xx.close();
    br.close();
    writer.close();
    fout.close();
    } catch (Exception e) {
    e.printStackTrace();
    }


    }


    private void init_data(){
    /*初始化建立2张表,录入测试数据*/
    try {
    // System.out.println(init_sql1);
    stat.executeUpdate(\"DROP TABLE IF EXISTS COMPANY; \");
    stat.executeUpdate(\"CREATE TABLE COMPANY(ID INT NOT NULL, NAME VARCHAR(20),AGE INT,ADDRESS VARCHAR(20),SALARY DECIMAL(7,2));\");
    stat.executeUpdate(\"INSERT INTO COMPANY VALUES(2,\'Allen\',25,\'Texas\',15000);\");
    stat.executeUpdate(\"INSERT INTO COMPANY VALUES(3,\'Teddy\',23,\'Norway\',20000); \");

    stat.executeUpdate(\"DROP TABLE IF EXISTS t1; \");
    stat.executeUpdate(\"CREATE TABLE t1(id int);\");
    stat.executeUpdate(\"INSERT INTO t1 VALUES(1);\");
    stat.executeUpdate(\"INSERT INTO t1 VALUES(2);\");

    // stat.executeUpdate(init_sql1);
    ResultSet rs = stat.executeQuery(\"select * from COMPANY;\"); // 查询数据
    System.out.println(\"1,初始化创建表结构录入数据操作演示:\");
    while (rs.next()) { // 将查询到的数据打印出来
    System.out.print(\"name = \" + rs.getString(\"name\") + \", \"); // 列属性一
    System.out.println(\"salary = \" + rs.getString(\"salary\")); // 列属性二

    }
    rs.close();
    } catch (SQLException e) {
    // TODO Auto-generated catch block
    e.printStackTrace();
    }


    }

    }


    4,执行结果如下:

    (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


    5,PS:总结

    有的.dump出来之后只有如下三行记录:

    PRAGMA foreign_keys=OFF;
    BEGIN TRANSACTION;
    COMMIT;

    而没有如下的相应的create建表sql和insert插入数据的记录

    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);

    那是有可能在备份的时候指定的sqlite数据文件的路径不对,没有用全路径,要用全路径才能备份成功,如下所示的c:/sqlite/tim.db

      Runtime rt = Runtime.getRuntime();
      String cmd=\"c:/sqlite/sqlite3 c:/sqlite/tim.db .dump\";
      Process process = rt.exec( cmd);


人气教程排行