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

浏览数：85 / 时间：2015年06月12日

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