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

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