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 stubtry {Class.forName(\"org.sqlite.JDBC\");conn = DriverManager.getConnection(db_source);stat = conn.createStatement();} catch (Exception e) {// TODO Auto-generated catch blocke.printStackTrace();}}public static void main(String[] args) throws SQLException, IOException, ClassNotFoundException {// TODO Auto-generated method stubSqlitBackup 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 blocke.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 blocke.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);
本条技术文章来源于互联网,如果无意侵犯您的权益请点击此处反馈版权投诉
本文系统来源:php中文网