1. Use. dump in the command line to back up SQL files. Command statement: C: sqlitesqlite3tim. db. dumptest. SQL. dump? TABLE ?... DumpthedatabaseinanSQLtextformatIfTABLEspecified, onlydumptablesmatchingLIKEpatternTABLE.2, through the. read statement
1. Use. dump in the command line to back up SQL files. Command statement: 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, through the. read statement
1. Use. dump on the command line to back up SQL files.
Command statement:
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.
Shows the execution result. The backup SQL file is displayed:
2. Use the. read statement to restore the database.
Command statement:
C:/sqlite/sqlite3 tim2.db ". read c:/sqlite/test. SQL
. Read FILENAME Execute SQL in FILENAME
Shows the execution result:
3. Back up and restore the sqlite database using java code
The Java code is as follows:
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;
/**
* Constructor initializes the data source */
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. Input initialization data
Sqlite. init_data ();
// 2. Start backup
Sqlite. backup ();
// 3. Delete the original data
Sqlite. dropDb ();
// 4. Restore data through backup files
Sqlite. restore ();
// 5. Close the connection and Data Source
Stat. close ();
Conn. close ();
}
/*
* Restore the sqlite database **/
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.exe c (cmd );
Class. forName (\ "org. sqlite. JDBC \");
Conn = DriverManager. getConnection (db_source );
Stat = conn. createStatement ();
ResultSet rs2 = stat.exe cuteQuery (\ "select * from sqlite_master; \"); // query data
System. out. println (\ "4, data recovery Demonstration :\");
While (rs2.next () {// print the queried data
System. out. print (\ "tbl_name = \" + rs2.getString (\ "tbl_name \") + \ ", \"); // column attribute 1
}
Rs2.close ();
}
/*
* Delete A Table **/
Private void dropDb (){
Try {
Stat.exe cuteUpdate (\ "drop table if exists company ;\");
Stat.exe cuteUpdate (\ "drop table if exists t1 ;\");
System. out. println (\ "3, the table has been deleted successfully \");
} Catch (SQLException e ){
// TODO Auto-generated catch block
E. printStackTrace ();
}
}
/*
* Back up the sqlite database */
Private void backup () throws SQLException, IOException {
Runtime rt = Runtime. getRuntime ();
String cmd = \ "c:/sqlite/sqlite3 c:/sqlite/tim. db. dump \";
Process process = rt.exe c (cmd );
Try {
InputStream in = process. getInputStream (); // the output information of the console is used as the input stream.
InputStreamReader xx = new InputStreamReader (in, \ "UTF-8 \");
// Set the output stream encoding to UTF-8. It must be UTF-8. Otherwise, garbled characters are read from the stream.
String inStr;
StringBuffer sb = new StringBuffer (\"\");
String outStr = null;
// Combine the console output string
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, the backup SQL file content is, outStr: \ r \" + outStr );
// The SQL target file to be used for import:
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 (){
/* Initialize and create two tables to enter Test Data */
Try {
// System. out. println (init_sql1 );
Stat.exe cuteUpdate (\ "drop table if exists company ;\");
Stat.exe cuteUpdate (\ "create table company (id int not null, name varchar (20), age int, address varchar (20), salary decimal (7, 2 ));\");
Stat.exe cuteUpdate (\ "insert into company values (2, \ 'allen \ ', 25, \ 'texas \', 15000 );\");
Stat.exe cuteUpdate (\ "insert into company values (3, \ 'Teddy \ ', 23, \ 'norway \', 20000 );\");
Stat.exe cuteUpdate (\ "drop table if exists t1 ;\");
Stat.exe cuteUpdate (\ "create table t1 (id int );\");
Stat.exe cuteUpdate (\ "insert into t1 VALUES (1 );\");
Stat.exe cuteUpdate (\ "insert into t1 VALUES (2 );\");
// Stat.exe cuteUpdate (init_sql1 );
ResultSet rs = stat.exe cuteQuery (\ "select * from COMPANY; \"); // query data
System. out. println (\ "1, initialization Create Table Structure Input data operation Demonstration :\");
While (rs. next () {// print the queried data
System. out. print (\ "name = \" + rs. getString (\ "name \") + \ ", \"); // column attribute 1
System. out. println (\ "salary = \" + rs. getString (\ "salary \"); // column attribute 2
}
Rs. close ();
} Catch (SQLException e ){
// TODO Auto-generated catch block
E. printStackTrace ();
}
}
}
4. The execution result is as follows:
(1) demo of initializing and creating a table structure to input data:
Name = Allen, salary = 15000.
Name = Teddy, salary = 20000.
(2) The SQL file backed up is 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) The table has been deleted successfully.
(4) data recovery operation Demonstration:
Name = Allen, salary = 15000.
Name = Teddy, salary = 20000.
5. PS: Summary
Some. After dump, there are only the following three rows of records:
PRAGMA foreign_keys = OFF;
Begin transaction;
COMMIT;
The following table creation SQL statements and insert data records do not exist:
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 );
It is possible to specify sqlite during Backup.The data file path is incorrect.The full path is not used. The backup can be successful only when the full path is used, as shown below:C:/sqlite/tim. db:
Runtime rt = Runtime. getRuntime ();
String cmd = \ "c:/sqlite/sqlite3 c:/sqlite/tim. db. dump \";
Process process = rt.exe c (cmd );
