Title Requirements
Write a program (in Java and using JDBC) that clones a SQLite database and produces a textual "backup" of the contents. This textual backup should consist of SQL statements that will recreate the contents of the original database i.e. CREATE TABLE and INSERT ... INTO instructions. Ideally, you should be able to recreatLe the original database by using the text files your program produces as input to SQLite. Your program must NOT use the system schema tables found in SQLite; any access to schema information must use the appropriate JDBC methods.
Write a program (using Java and JDBC) to clone a database and generate a "backup" text file. This backup text should consist of sql statements that will recreate the contents of the original database, namely the create table and insert into instructions. Ideally, these statements can be used as input to SQLite to rebuild the database. Should not be used in your program schema to get the content of SQLite. Any content should be obtained through JDBC
Process sorting
Every time you get a seemingly complex requirement, you should fully understand the problem and divide it into several parts that do not interfere with each other, and then analyze and solve the problem in detail for each part.
The main content of this topic is to obtain the content in the database and copy it to a new database. At the same time, it is also necessary to record some sql statements generated.
Replicating a database can be divided into three main steps
- Construction of table structure in database
- Reading of data in table
- The read data is transferred into the new database
To record sql, you need to record it before each sql statement is executed and output it to the text
Let's sort out the technical points further
(special note: some SQLite specific methods will be used, which cannot be used in other database systems)
When reading the table structure, you can use a very useful method
- select * from sqlite_master WHERE type = "table";
This statement can directly obtain the name of the table and the statement of creating the table, that is, you don't need to use metadata to perform complicated operations, and then assemble the fields one by one into sql statements, and you don't need to consider the judgment of primary key, foreign key and joint primary key. And when the table read by this statement does not need to consider the foreign key problem, it will be sorted according to the order in which the table was originally added and can be executed directly.
If you replace the table with index, you can get the indexing statements of all tables, which can also be executed directly. However, it should be noted that if the primary key index is automatically established for the table in SQLite, the queried data will be displayed as sqlite_autoindex_ Table name without displaying specific index statements (because there is no active index at all, which is automatically added by the system)
When reading data, we should insert each read data corresponding to the structure in the table, so we can build two StringBuilder objects to build the structure and data, and only use them when splicing sql statements
- insert into table name (keyString) values(valueString);
And the actual result will be
- insert into table name ('field1 ',' field2 ',' field3 '...); values('data1', 'data2', 'data3'...);
Here, I suggest that each data and field be wrapped in single quotation marks, so that if there are double quotation marks in the data, it does not need to be processed. If there are single quotation marks, it can be escaped with two single quotation marks instead.
When it comes to the output text part, you can record it before or after each statement is executed through the File class and output stream. Here, I choose to use the PrintStream class because it can record the exception information more conveniently (through e.printStackTrace(printStream))
The general process is finished, and now you can start to write code in parts
Step by step implementation
The first is the overall framework
public class CopyDB {
// File class for generating text
private static File file;
// Output stream
private static PrintStream printStream;
public static void main(String[] args){
// SQLite database address
String location = "jdbc:sqlite://e:\\LU\\";
// The name of the database. Please take it with you db
String database = "Chinook.db";
// Prepare two connections, one to the cloned database and one to create a new database
Connection conn, newConn;
// Ready in advance for try catch
PreparedStatement ps;
ResultSet rs;
try{
// Get the database name and remove the last one db
String databaseName = database.substring(0,database.length()-3);
// create a file
file = new File("e://LU//"+databaseName+"Data.txt");
file.createNewFile();
// Bind output stream
printStream = new PrintStream(file);
// Load driver
Class.forName("org.sqlite.JDBC");
// Binding connection
conn = DriverManager.getConnection(location+database);
newConn = DriverManager.getConnection(location + "new" +database);
// Create a table structure in a new connection
ps = conn.prepareStatement("select * from sqlite_master WHERE type = \"table\";");
rs = ps.executeQuery();
createStructure(rs,newConn);
// Create index in new connection
ps = conn.prepareStatement("select * from sqlite_master WHERE type = \"index\";");
rs = ps.executeQuery();
createIndex(rs,newConn);
// This is to get the table name
ps = conn.prepareStatement("select * from sqlite_master WHERE type = \"table\";");
rs = ps.executeQuery();
// Transfer data to all tables in turn
String tableName;
while (rs.next()) {
tableName = rs.getString(2);
ps = conn.prepareStatement("select * from '" +tableName+ "'");
ResultSet datas = ps.executeQuery();
fillData(tableName, datas, newConn);
}
// Be sure to close at the end of the program
rs.close();
ps.close();
conn.close();
newConn.close();
printStream.close();
} catch (ClassNotFoundException e){
e.printStackTrace(printStream);
//e.printStackTrace();
} catch (SQLException e){
e.printStackTrace(printStream);
} catch (IOException e){
e.printStackTrace(printStream);
}
}
In this code, I wrote three methods, createStructure, createIndex, and fillData. Let's finish them in turn
// It must be static, otherwise it cannot be called, and the error will be thrown directly, because we have done exception handling before
public static void createStructure(ResultSet rs, Connection conn) throws SQLException{
String tableName;
PreparedStatement ps;
while (rs.next()) {
tableName = rs.getString(2);
// Delete the table to ensure that there is no data in the table (the inserted data does not conflict)
ps = conn.prepareStatement("drop table if exists '"+tableName+"'");
ps.executeUpdate();
// 5 - > CREATE TABLE statement
String constr = rs.getString(5);
ps = conn.prepareStatement(constr);
ps.executeUpdate();
}
}
On the whole, it is relatively simple and the logic is not complex. Similarly, the method of adding index is the same
public static void createIndex(ResultSet rs, Connection conn) throws SQLException{
while(rs.next()){
String index = rs.getString(2);
if (index.contains("sqlite_autoindex_")){
// If there is autoindex, it can not be processed
} else {
index = rs.getString(5); // 5 - > specific index statement
PreparedStatement ps = conn.prepareStatement(index);
ps.executeUpdate();
}
}
}
It may be difficult to fill in the data, because we will inevitably use metadata for some operations, but it will not be too difficult to follow the steps, because the logic is not complex
public static void fillData(String tablaName, ResultSet datas, Connection conn) throws SQLException{
ResultSetMetaData meta= datas.getMetaData();
// Using HashMap to store field information does not use the characteristics of HashMap
// Just want the function of double value access, which can be easily realized by using array
Map<String,String> columns = new HashMap<>();
// Save the fields and field types in the database
for (int i=1; i<=meta.getColumnCount(); i++){
columns.put(meta.getColumnName(i),meta.getColumnTypeName(i));
}
// Using StringBuilder is more memory friendly
StringBuilder keyString = new StringBuilder();
StringBuilder valueString = new StringBuilder();
// Get the field name and build the keyString. See our analysis earlier
Set<String> keys= columns.keySet();
for (String keyName : keys) {
keyString.append("'");
keyString.append(keyName);
keyString.append("',");
}
// Remember to delete the last comma
keyString.deleteCharAt(keyString.length() - 1);
String temp;
while (datas.next()) {
valueString.setLength(0); // empty
for (String keyName : keys) {
valueString.append("'");
// For each type, a unique reading method is adopted
// Safer and faster, data will not be damaged
switch (columns.get(keyName)){
case "integer": temp = Integer.toString(datas.getInt(keyName));break;
case "blob":
// blob type data should actually be processed with getBlob or getBytes
// However, there are some problems with the new version of SQLite jar package
// As a result, blob and clob data cannot be processed, so getString is used instead
temp = datas.getString(keyName);break;
case "text": temp = datas.getString(keyName);break;
case "timestamp": temp = datas.getTimestamp(keyName).toString();break;
case "date": temp = datas.getDate(keyName).toString();break;
case "bigdecimal": temp = datas.getBigDecimal(keyName).toString();break;
case "byte": temp = Byte.toString(datas.getByte(keyName));break;
case "boolean": temp = datas.getBoolean(keyName)?"true":"false";break;
case "double": temp = Double.toString(datas.getDouble(keyName));break;
case "float": temp = Float.toString(datas.getFloat(keyName));break;
case "long": temp = Long.toString(datas.getLong(keyName));break;
case "short": temp = Short.toString(datas.getShort(keyName));break;
case "": temp = datas.getString(keyName);break;
case "clob":
// Same as blob
temp = datas.getString(keyName);break;
default:
// If it is executed here, it indicates that there is a type not mentioned above
// There may also be a problem with the database
temp = datas.getString(keyName);
}
// Escape with '' substitution '
if (temp != null)
temp = temp.replace("'", "''");
valueString.append(temp);
valueString.append("',");
}
// Delete the last comma
valueString.deleteCharAt(valueString.length() - 1);
// Create final sql statement
String sql = "insert into '"+tablaName+"' ("+ keyString+") values("+valueString+");";
// Displays whether the insertion was successful
PreparedStatement ps = conn.prepareStatement(sql);
boolean flag = ps.executeUpdate()==1;
System.out.println(flag?"insert successfully!":"insert fail!");
}
}
}
So far, all parts have been written separately, but before the final summary, let's sort out what needs to be improved
- You may have noticed that the objects of File class and PrintStream class we created have not been used, that is, we have not output the content to the document
- Can we give some additional information about the output text, such as time
- Do you want to make statistics on the number of tables, indexes and data to quantify the results
Complete code
There will be no detailed comments in the complete code, but some simple comments will be written for the parts that need to be improved before
package jdbc;
import java.io.File;
import java.io.IOException;
import java.io.PrintStream;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
public class CopyDB {
private static File file;
private static PrintStream printStream;
private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss -- "); // Time format
private static int tableCount=0, tableDataCount=0, dataCount=0, indexCount=0; // Count
public static void main(String[] args){
String location = "jdbc:sqlite://e:\\LU\\";
String database = "Chinook.db";
Connection conn, newConn;
PreparedStatement ps;
ResultSet rs;
try{
String databaseName = database.substring(0,database.length()-3);
file = new File("e://LU//"+databaseName+"Data.txt");
file.createNewFile();
printStream = new PrintStream(file);
Class.forName("org.sqlite.JDBC");
conn = DriverManager.getConnection(location+database);
newConn = DriverManager.getConnection(location + "new" +database);
ps = conn.prepareStatement("select * from sqlite_master WHERE type = \"table\";");
rs = ps.executeQuery();
createStructure(rs,newConn);
ps = conn.prepareStatement("select * from sqlite_master WHERE type = \"index\";");
rs = ps.executeQuery();
createIndex(rs,newConn);
ps = conn.prepareStatement("select * from sqlite_master WHERE type = \"table\";");
rs = ps.executeQuery();
// for each table, transfer data
String tableName;
while (rs.next()) {
tableName = rs.getString(2);
ps = conn.prepareStatement("select * from '" +tableName+ "'");
ResultSet datas = ps.executeQuery();
tableDataCount=0;
fillData(tableName, datas, newConn);
System.out.println(tableName + "'s data was transferred ("+tableDataCount+" lines)"); // Output in console
printStream.print(sdf.format(new Date(System.currentTimeMillis()))); // Time information
printStream.println(tableName + "'s data was transferred ("+tableDataCount+" lines)"); // Output to text
}
System.out.println("Clone completely, "+dataCount+" data lines were added");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println("Clone completely, "+dataCount+" data lines were added");
rs.close();
ps.close();
conn.close();
newConn.close();
printStream.close();
} catch (ClassNotFoundException e){
// Describe the exception
System.out.println("JDBC not found, please check the JAR package");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println("JDBC not found, please check the JAR package");
e.printStackTrace(printStream);
} catch (SQLException e){
System.out.println("SQL exception appear, please check the database or SQL lines");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println("SQL exception appear, please check the database or SQL lines");
e.printStackTrace(printStream);
} catch (IOException e){
System.out.println("IO exception appear, please check the file situation");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println("IO exception appear, please check the file situation");
e.printStackTrace(printStream);
}
}
// get all tables
public static void createStructure(ResultSet rs, Connection conn) throws SQLException{
String tableName;
PreparedStatement ps;
while (rs.next()) {
tableName = rs.getString(2);
// drop table if exist, ensure no repeat data row
ps = conn.prepareStatement("drop table if exists '"+tableName+"'");
ps.executeUpdate();
// 5 -> construct table codes
String constr = rs.getString(5);
ps = conn.prepareStatement(constr);
System.out.println(constr);
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println(constr);
ps.executeUpdate();
System.out.println(tableName+" table was added");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println(tableName+" table was added");
tableCount++;
}
System.out.println("Totally "+tableCount+" tables were added");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println("Totally "+tableCount+" tables were added");
}
// add indexes
public static void createIndex(ResultSet rs, Connection conn) throws SQLException{
while(rs.next()){
String index = rs.getString(2);
String tableName = rs.getString(3);
if (index.contains("sqlite_autoindex_")){
System.out.println(tableName+"'s autoindex was added");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println(tableName+"'s autoindex was added");
indexCount++;
} else {
index = rs.getString(5);
System.out.println(index);
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println(index);
PreparedStatement ps = conn.prepareStatement(index);
ps.executeUpdate();
System.out.println(tableName+"'s index was added");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println(tableName+"'s index was added");
indexCount++;
}
}
System.out.println("Totally "+indexCount+" indexes were added");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println("Totally "+indexCount+" indexes were added");
}
// fill data
public static void fillData(String tablaName, ResultSet datas, Connection conn) throws SQLException{
System.out.println("Table name: "+tablaName);
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println("Table name: "+tablaName);
ResultSetMetaData meta= datas.getMetaData();
Map<String,String> columns = new HashMap<>();
// print the tables and columns in tables
for (int i=1; i<=meta.getColumnCount(); i++){
columns.put(meta.getColumnName(i),meta.getColumnTypeName(i));
System.out.println("column:"+meta.getColumnName(i)+" type:"+meta.getColumnTypeName(i));
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println("column:"+meta.getColumnName(i)+" type:"+meta.getColumnTypeName(i));
}
StringBuilder keyString = new StringBuilder();
StringBuilder valueString = new StringBuilder();
// build a String for insert sequence
Set<String> keys= columns.keySet();
for (String keyName : keys) {
keyString.append("'");
keyString.append(keyName);
keyString.append("',");
}
keyString.deleteCharAt(keyString.length() - 1);
String temp;
while (datas.next()) {
valueString.setLength(0);
for (String keyName : keys) {
valueString.append("'");
switch (columns.get(keyName)){
case "integer": temp = Integer.toString(datas.getInt(keyName));break;
case "blob":
temp = datas.getString(keyName);break;
case "text": temp = datas.getString(keyName);break;
case "timestamp": temp = datas.getTimestamp(keyName).toString();break;
case "date": temp = datas.getDate(keyName).toString();break;
case "bigdecimal": temp = datas.getBigDecimal(keyName).toString();break;
case "byte": temp = Byte.toString(datas.getByte(keyName));break;
case "boolean": temp = datas.getBoolean(keyName)?"true":"false";break;
case "double": temp = Double.toString(datas.getDouble(keyName));break;
case "float": temp = Float.toString(datas.getFloat(keyName));break;
case "long": temp = Long.toString(datas.getLong(keyName));break;
case "short": temp = Short.toString(datas.getShort(keyName));break;
case "": temp = datas.getString(keyName);break;
case "clob":
temp = datas.getString(keyName);break;
default:
System.out.println("There is an error when judge column "+keyName+"'s type!");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println("There is an error when judge column "+keyName+"'s type!");
temp = datas.getString(keyName);
}
// should replace ' with ''
if (temp != null)
temp = temp.replace("'", "''");
valueString.append(temp);
valueString.append("',");
}
//remove the last comma
valueString.deleteCharAt(valueString.length() - 1);
//construct sql
String sql = "insert into '"+tablaName+"' ("+ keyString+") values("+valueString+");";
System.out.println(sql);
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println(sql);
// present success or fail
PreparedStatement ps = conn.prepareStatement(sql);
boolean flag = ps.executeUpdate()==1;
System.out.println(flag?"insert successfully!":"insert fail!");
printStream.print(sdf.format(new Date(System.currentTimeMillis())));
printStream.println(flag?"insert successfully!":"insert fail!");
if(flag){dataCount++;tableDataCount++;}
}
}
}
Improvement space
There is still some room for improvement after the code is completed. Here I put forward some places that I think are worth changing, so I won't implement them
- Cloning a large database will take a long time, because our data is inserted one by one. If we insert 10 or 50 data at a time, we can significantly improve the efficiency
(Note: there are some problems with the current version of JDBC jar package of SQLite. You can't use values() () to insert multiple pieces of data, or use the executeLargeUpdate method, and batch processing is not applicable. At present, you may only use the UNION method supported by SQLite to realize this function) - If there are rare data formats in the database, you may need to manually add new case s in the switch. Can you create a Set to collect all the data structures. When a new data structure appears, first store it in Set, and then traverse the Set set and call the corresponding get method when encountering the same data structure.
(hint: the function proposed in the second sentence may be realized by reflection)