Jfinal+SQLite解决MYSQL迁移表未复制索引困难,完善迁移工具

原mysql 表查询存在索引，

查询sql含force index(字段)，SQLite不支持，会报错

解决办法，要么删除索引，要么强制执行索引 换成 INDEXED BY, 

String select sql="select * ";

String fromSql=" from tableName force index (START_TIME_TYPE) where 1=1

if (fromSql.contains("force index")){
   fromSql= fromSql.replaceAll("(?i)\\bFROM\\s+(\\w+)(\\s+\\w+)?\\s+force\\s+index\\s*\\(\\s*(\\w+)\\s*\\)",
         "FROM $1 $2 INDEXED BY $3");
}

这里就需要给sqlite数据库表添加对应索引，不然会报错SQLiteException: no such index: START_TIME_TYPE

生产数据库中，表多，索引比较多，在原先迁移表工具添加索引迁移，暂时未发现问题，需多多测试

package changeDataBase;
import java.sql.*;
import java.util.*;
public class MySQLToSQLiteMigration {
    private static final String MYSQL_URL = "jdbc:mysql://localhost:3306/database";
    private static final String MYSQL_USER = "admin";
    private static final String MYSQL_PASSWORD = "123456";
    private static final String SQLITE_URL = "jdbc:sqlite:D:/database/database.sqlite";
    public static void main(String[] args) {
        try {
            Class.forName("org.sqlite.JDBC");
        } catch (ClassNotFoundException var2) {
            System.err.println("SQLite JDBC driver not found!");
            var2.printStackTrace();
            return;
        }
        List tableNames = getTableNamesFromMySQL();
        migrateTablesToSQLite(tableNames);
    }
    private static List getTableNamesFromMySQL() {
        ArrayList tableNames = new ArrayList<>();
        try (Connection conn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD)) {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet rs = metaData.getTables(null, null, "%", new String[]{"TABLE"});
            while (rs.next()) {
                tableNames.add(rs.getString("TABLE_NAME"));
            }
        } catch (SQLException var6) {
            var6.printStackTrace();
        }
        return tableNames;
    }
    private static void migrateTablesToSQLite(List tableNames) {
        try (Connection mysqlConn = DriverManager.getConnection(MYSQL_URL, MYSQL_USER, MYSQL_PASSWORD);
             Connection sqliteConn = DriverManager.getConnection(SQLITE_URL)) {
            Iterator var3 = tableNames.iterator();
            while (var3.hasNext()) {
                String tableName = var3.next();
                System.out.println("Migrating table: " + tableName);
                migrateTable(mysqlConn, sqliteConn, tableName);
            }
        } catch (SQLException var9) {
            var9.printStackTrace();
        }
    }
    private static void migrateTable(Connection mysqlConn, Connection sqliteConn, String tableName) throws SQLException {
        String selectSql = "SELECT * FROM " + tableName;
        try (Statement stmt = mysqlConn.createStatement();
             ResultSet rs = stmt.executeQuery(selectSql)) {
            String createTableSql = getCreateTableSql(mysqlConn, tableName);
            try (Statement sqliteStmt = sqliteConn.createStatement()) {
                sqliteStmt.execute(createTableSql);
            }
            String insertSql = getInsertSql(rs.getMetaData(), tableName);
            try (PreparedStatement sqlitePstmt = sqliteConn.prepareStatement(insertSql)) {
                while (rs.next()) {
                    for (int i = 1; i  columns = new ArrayList<>();
        String primaryKey = null;
        while (rs.next()) {
            String columnName = rs.getString("COLUMN_NAME");
            String columnType = getSQLiteType(rs.getString("TYPE_NAME"));
            columns.add(columnName + " " + columnType);
        }
        // 获取主键信息
        String primaryKeySql = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ? AND CONSTRAINT_NAME = 'PRIMARY'";
        try (PreparedStatement pstmt = mysqlConn.prepareStatement(primaryKeySql)) {
            pstmt.setString(1, "database");
            pstmt.setString(2, tableName);
            try (ResultSet pkRs = pstmt.executeQuery()) {
                if (pkRs.next()) {
                    primaryKey = pkRs.getString("COLUMN_NAME");
                }
            }
        }
        sb.append(String.join(", ", columns));
        if (primaryKey != null) {
            sb.append(", PRIMARY KEY (").append(primaryKey).append(")");
        }
        sb.append(");");
        return sb.toString();
    }
    private static String getInsertSql(ResultSetMetaData metaData, String tableName) throws SQLException {
        StringBuilder sb = new StringBuilder("INSERT INTO ").append(tableName).append(" (");
        for (int i = 1; i
        Map> indexMap = new LinkedHashMap<>();
        try (PreparedStatement ps = mysqlConn.prepareStatement(sql)) {
            ps.setString(1, tableName);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String idxName = rs.getString("INDEX_NAME");
                    indexMap.computeIfAbsent(idxName, k -> new ArrayList<>())
                            .add("\"" + rs.getString("COLUMN_NAME") + "\"");
                }
            }
        }
        // 2. 逐个在 SQLite 建索引
        for (Map.Entry> e : indexMap.entrySet()) {
            String cols = String.join(", ", e.getValue());
            // 索引名在 SQLite 中保持同名，避免冲突可加前缀
            String createIdx = String.format(
                    "CREATE %s INDEX IF NOT EXISTS \"%s\" ON \"%s\" (%s);",
                    e.getKey().toUpperCase().startsWith("UNIQUE") ? "UNIQUE" : "",
                    e.getKey(), tableName, cols);
            try (Statement st = sqliteConn.createStatement()) {
                st.execute(createIdx);
                System.out.println("    " + createIdx.trim());
            }
        }
    }
}