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.
执行效果如下图所示，可以看到备份的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 + "rn");
}
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,2));

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

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

CREATE TABLE t1(id int);

INSERT INTO "t1" VALUES(1);

INSERT INTO "t1" VALUES(2);

COMMIT;

(3)，表已经删除成功

(4)，数据已经恢复数据操作演示：

name = Allen,salary = 20000




5，PS：总结

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

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
COMMIT;


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

CREATE TABLE COMPANY(ID INT NOT NULL,2));
INSERT INTO "COMPANY" VALUES(2,15000);
INSERT INTO "COMPANY" VALUES(3,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);

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!