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 语句来
.dump ?TABLE? ... Dump the database in an SQL text format
LIKE pattern TABLE.
执行效果如下图所示，可以看到备份的sql文件内容：
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);
