0. 要求
复制数据库到相应文件夹下，要求利用（1）JDBC相关操作；（2）file文件操作（创建并写入数据库、sql文件）
生成：拷贝数据库、sql文件（含用于创建拷贝数据库的所有命令）
1. 需要注意的几点
（1）利用metaData查找primary key, foreign key, index；
参考链接：Extracting Database Metadata Using JDBC | Baeldung
内含Metadata所有操作，本文不再赘述。
（2）根据外键对数据库中的表进行排序避免因外键约束造成表创建失败；
参考链接：https://blog.csdn.net/m0_38014998/article/details/92393256
实现根据外键对表进行排序。
（3）对其中特殊（不常见）字段类型blob和clob的处理：转二进制/十六进制。
2. 源码
程序入口：选择数据库、递归
// 启动器：Launcher.java
public class Launcher {
public static void main(String[] args) {
System.out.println("【system】input the name of db you want to copy(e.g. Chinook)");
new Main(new Scanner(System.in).nextLine());
System.out.println("【system】any one else? y or n");
if (new Scanner(System.in).nextLine().equals("y"))
main(null);
else
System.out.println("bye.");
}
}
功能实现
// 功能实现：Main.java
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.LinkedHashSet;
import java.util.Set;
public class Main {
// connections
private Connection con_sourceDB = null;
private Connection con_backup = null;
// metaData of db
private DatabaseMetaData metaData = null;
// num of column---end with "," or ")"
private int columnNum = 0;
// table in order
private final LinkedHashSet<String> tableInOrder = new LinkedHashSet<>();
// instructions
private StringBuilder createTableSQL = new StringBuilder();
private StringBuilder createIndexSQL = new StringBuilder();
private StringBuilder insertValueSQL = new StringBuilder();
// constructor-inlet
public Main(String dbName) {
connect(dbName);// connect to sqlite
getTablesAndSort();// output: sorted_tables
for (String tableName : tableInOrder) {
System.out.println("【table name: " + tableName + "】");
createTables(tableName);
createIndex(tableName);
insertValues(tableName);
execute(dbName);
createTableSQL = new StringBuilder();
createIndexSQL = new StringBuilder();
insertValueSQL = new StringBuilder();
}
System.out.println("【system】success");
}
// connect to db
private void connect(String dbName) {
try {
Class.forName("org.sqlite.JDBC");
con_sourceDB = DriverManager.getConnection("jdbc:sqlite:SourceDB/" + dbName + ".db"); // connect to the source database
con_backup = DriverManager.getConnection("jdbc:sqlite:Backup/" + dbName + "_backup.db"); // generate the copied database
metaData = con_sourceDB.getMetaData();
} catch (Exception e) {
e.printStackTrace();
}
}
// sort according to foreign keys
private void getTablesAndSort() {
try {
ResultSet tables = metaData.getTables(null, null, "%", new String[]{"TABLE"});
while (tables.next()) {
String tableName = tables.getString("TABLE_NAME");
if (!tableInOrder.contains(tableName)) {
ResultSet tableForeignKey = metaData.getImportedKeys(null, null, tableName);
checkImportedKeys(tableForeignKey, tableInOrder, metaData, tableName);
tableInOrder.add(tableName);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
// sort-single table
private void checkImportedKeys(ResultSet tableForeignKey, Set<String> set, DatabaseMetaData metaData, String tableName) {
try {
while (tableForeignKey.next()) {
String referenceTable = tableForeignKey.getString("PKTABLE_NAME");
if (!set.contains(referenceTable)) {
if (referenceTable.equals(tableName)) set.add(tableName);
else addTable(set, metaData, referenceTable);
}
}
} catch (Exception e) {
e.printStackTrace();
}
}
// add-recursive-checkImportedKeys
private void addTable(Set<String> set, DatabaseMetaData metaData, String tableName) {
try {
ResultSet tableForeignKey = metaData.getImportedKeys(null, null, tableName);
checkImportedKeys(tableForeignKey, set, metaData, tableName);
set.add(tableName);
} catch (Exception e) {
e.printStackTrace();
}
}
// create table...
private void createTables(String tableName) {
try {
//create table
createTableSQL.append("DROP TABLE IF EXISTS " + '"').append(tableName).append('"').append(";
").append("CREATE TABLE ").append('"').append(tableName).append('"').append("(
");
ResultSet rs = metaData.getColumns(null, null, tableName, null);
columnNum = 0;
int columnIndex = 0;
while (rs.next()) columnNum++;
rs = metaData.getColumns(null, null, tableName, null);
while (rs.next()) {
columnIndex++;
String columnName = rs.getString("COLUMN_NAME");
String columnType = rs.getString("TYPE_NAME");
int nullable = rs.getInt("NULLABLE");
createTableSQL.append(columnName).append(' ').append(columnType);
if (nullable == 0)
createTableSQL.append(" NOT NULL");
if (columnIndex < columnNum)
createTableSQL.append(",
");
}
// PK & FK
ResultSet foreignKeys = metaData.getImportedKeys(null, null, tableName);
int FKNum = 0;
while (foreignKeys.next()) FKNum++;
createTableSQL.append(",
");
/* add primary key */
insertPrimaryKey(tableName);
if (FKNum != 0)
createTableSQL.append(",
");
else
createTableSQL.append('
');
/* add foreign key */
insertForeignKey(tableName, FKNum);
createTableSQL.append(");
");
} catch (Exception e) {
System.out.println("fail to create table." + e.toString());
}
}
// primary(key01, key02...)
private void insertPrimaryKey(String tableName) {
try {
ResultSet primaryKeys = metaData.getPrimaryKeys(null, null, tableName);
int PKNum = 0;
while (primaryKeys.next()) PKNum++;
if (PKNum != 0) {
primaryKeys = metaData.getPrimaryKeys(null, null, tableName);
createTableSQL.append("PRIMARY KEY(");
int keyIndex = 0;
while (primaryKeys.next()) {
keyIndex++;
if (keyIndex < PKNum) createTableSQL.append(primaryKeys.getString("COLUMN_NAME")).append(',');
else createTableSQL.append(primaryKeys.getString("COLUMN_NAME")).append(')');
}
}
} catch (Exception e) {
System.out.println("fail to insert primary key. " + e.toString());
}
}
// foreign key(keyName) references tableName(keyName)
private void insertForeignKey(String tableName, int numberOfFk) {
try {
if (numberOfFk != 0) {
ResultSet foreignKeys = metaData.getImportedKeys(null, null, tableName);
int keyIndex = 0;
while (foreignKeys.next()) {
keyIndex++;
String fkColumnName = foreignKeys.getString("FKCOLUMN_NAME");
String pkTableName = foreignKeys.getString("PKTABLE_NAME");
String pkColumnName = foreignKeys.getString("PKCOLUMN_NAME");
createTableSQL.append("FOREIGN KEY(").append(fkColumnName).append(") REFERENCES ").append(pkTableName).append('(').append(pkColumnName).append(')');
if (keyIndex < numberOfFk) createTableSQL.append(",
");
else createTableSQL.append('
');
}
}
} catch (Exception e) {
System.out.println("fail to insert foreign key." + e.toString());
}
}
// create index on...
private void createIndex(String tableName) {
try {
ResultSet rsIndex = metaData.getIndexInfo(null, null, tableName, false, true);
LinkedHashSet<String> indexset = new LinkedHashSet<>();
while (rsIndex.next()) {
String indexName = rsIndex.getString("INDEX_NAME");
String autoIndexName = "sqlite_autoindex_" + tableName + "_1";
if (!indexName.equals(autoIndexName))
indexset.add(rsIndex.getString("INDEX_NAME"));
}
for (String indexName : indexset) {
rsIndex = metaData.getIndexInfo(null, null, tableName, false, true);
int keyIndex = 0;
int IndexColumnNum = 0;
createIndexSQL.append("CREATE ");
while (rsIndex.next()) {
String getindexName = rsIndex.getString("INDEX_NAME");
if (indexName.equals(getindexName)) {
IndexColumnNum++;
if (IndexColumnNum <= 1) {
boolean nonUnique = rsIndex.getBoolean("NON_UNIQUE");
if (!nonUnique)
createIndexSQL.append("UNIQUE INDEX ").append(indexName).append(" ON ").append(tableName).append('(');
else
createIndexSQL.append("INDEX ").append(indexName).append(" ON ").append(tableName).append('(');
}
}
}
rsIndex = metaData.getIndexInfo(null, null, tableName, false, true);
while (rsIndex.next()) {
String getindexName = rsIndex.getString("INDEX_NAME");
String columnName = rsIndex.getString("COLUMN_NAME");
if (indexName.equals(getindexName)) {
keyIndex++;
if (keyIndex < IndexColumnNum) createIndexSQL.append(columnName).append(',');
else createIndexSQL.append(columnName).append(");
");
}
}
}
} catch (Exception e) {
System.out.println("fail to create index." + e.toString());
}
}
// insert into...
private void insertValues(String tableName) {
try {
String query = "SELECT * FROM " + '"' + tableName + '"' + ';';
Statement stmt = con_sourceDB.createStatement();
ResultSet rs = stmt.executeQuery(query);
ResultSetMetaData rsmeta = rs.getMetaData();
while (rs.next()) {
int rowIndex = 0;
insertValueSQL.append("INSERT INTO " + '"').append(tableName).append('"').append(" VALUES").append('(');
ResultSet rsColumns = metaData.getColumns(null, null, tableName, null);
while (rsColumns.next()) {
rowIndex++;
String columnName = rsColumns.getString("COLUMN_NAME");
String typeName = rsmeta.getColumnTypeName(rowIndex);
Object object = rs.getObject(columnName);
String str = "";
if (object != null) str = object.toString();
if (typeName.equals("text") || typeName.equals("blob") || str.equals("\N")) { //*
if (object != null) {
String s = rs.getString(columnName);
if (typeName.equals("blob")){
s = rs.getObject(columnName).toString();
// s = toBinary(s);
s = strToHexadecimal(s);
}
s = s.replace("'", "''");
insertValueSQL.append("'").append(s).append("'");
}
} else insertValueSQL.append(object);
if (rowIndex < columnNum) insertValueSQL.append(',');
else insertValueSQL.append(");
");
}
}
} catch (Exception e) {
System.out.println("fail to insertValue." + e.toString());
}
}
// blob to binary
private String toBinary(String str) {
char[] strChar = str.toCharArray();
StringBuilder result = new StringBuilder();
for (int i = 0; i < strChar.length; i++) {
result.append(Integer.toBinaryString(strChar[i])).append(" ");
}
return result.toString();
}
// blob to hex
public static String strToHexadecimal(String str) {
char[] chars = "0123456789ABCDEF".toCharArray();
StringBuilder sb = new StringBuilder("");
byte[] bs = str.getBytes();
int bit;
for (byte b : bs) {
bit = (b & 0x0f0) >> 4;
sb.append(chars[bit]);
bit = b & 0x0f;
sb.append(chars[bit]);
}
return sb.toString().trim();
}
// save & execute
private void execute(String dbName) {
// write to sql
try {
System.out.print("try to write in sql...");
// file-writer
FileWriter writer = new FileWriter("./Backup/" + dbName + "_backup.sql", true);
writer.write(createTableSQL.toString());
writer.write(createIndexSQL.toString());
writer.write(insertValueSQL.toString());
writer.close();
System.out.println("[done]");
} catch (IOException e) {
System.out.println("fail to write SQL." + e.toString());
}
// generate db_backup
try {
System.out.print("try to generate table...");
Statement backupStatement = con_backup.createStatement();
backupStatement.executeUpdate(createTableSQL.toString());
backupStatement.executeUpdate(createIndexSQL.toString());
backupStatement.executeUpdate(insertValueSQL.toString());
System.out.println("[done]");
} catch (Exception e) {
System.out.println("fail to generate table." + e.toString());
}
}
}
3. 放在最后
感谢“Dr.李”、“李麒麟”和“一几许”的帮助！