Java Hibernate：替换所有出现的字符串，而不使用实体Map

我需要替换数据库中出现的所有字符串。我不知道table的名字。列定义。没什么
数据库是WordPress的MySQL数据库，我需要替换所有表中的字符串。如果用户安装一些WordPress插件，它可以创建自己的数据库表。所以我不能使用Hibernate将表Map到POJO。
表格可能看起来像这个图像：

我尝试entityManager.createNativeQuery()获取所有表的名称。它的工作原理：

@PersistenceContext
EntityManager entityManager;
//...
    
String q3 = "SELECT table_name, table_schema FROM information_schema.tables WHERE table_schema = 'wh" + Integer.toString(serverId) + "' AND table_type = 'BASE TABLE' ORDER BY table_name ASC";
System.out.println(q3);
List<Object[]> objects3 = entityManager.createNativeQuery(q3).getResultList();
for (Object[] o : objects3) {
    String table_name   = (String) o[0];
    String table_schema = (String) o[1];
    System.out.println(table_name + " --- " + table_schema);
}

产出：

wh123_commentmeta --- wh123
wh123_comments --- wh123
wh123_links --- wh123
wh123_options --- wh123
wh123_postmeta --- wh123
wh123_posts --- wh123
wh123_term_relationships --- wh123
wh123_term_taxonomy --- wh123
wh123_termmeta --- wh123
wh123_terms --- wh123
wh123_usermeta --- wh123
wh123_users --- wh123
wh123_ve_posts_layer --- wh123

然后找到所有出现的字符串“example.net“。它也可以工作：

// example for one table:
String find = "example.net";
String q4 = "SELECT * FROM wh" + Integer.toString(serverId) + ".wh" + Integer.toString(serverId) + "_options";
System.out.println(q4);
List<Object[]> objects4 = entityManager.createNativeQuery(q4).getResultList();
for (Object[] o : objects4) {
    for (int i = 0; i < o.length; i++) {
        System.out.print(o[i]);
        System.out.print(" - ");
        if (o[i].toString().contains(find)) {
            System.err.println("STRING FOUND: " + o[i].toString());
        }
    }
}

产出：

617 - web_option_license - a:1:{s:7:"license";s:16:"[email protected]";} - STRING FOUND: a:1:{s:7:"license";s:16:"[email protected]";}

现在我需要用（例如）text.com替换example.net，并替换数据库中的原始字符串。但是现在我不知道如何把替换的字符串放回数据库...
PHP数组的序列化（反）将是下一步.

它并不完美，但它有效：
Maven依赖：

<!-- https://mvnrepository.com/artifact/com.xk72/pherialize -->
<dependency>
    <groupId>com.xk72</groupId>
    <artifactId>pherialize</artifactId>
    <version>1.2.4</version>
</dependency>

我的代码：

@Transactional()
public void replaceAll(int serverId, String find, String replace, boolean update) {

    String dbName = String.format("wh%d", serverId);

    // get list of tables:
    String getTablesQuery = String.format(
            "SELECT table_name, table_schema FROM information_schema.tables WHERE table_schema = '%s' AND table_type = 'BASE TABLE' ORDER BY table_name ASC",
            dbName);
    log.debug("getTablesQuery: {}", getTablesQuery);
    List<String> tables = new ArrayList<>();
    List<Object[]> tablesObjects = entityManager.createNativeQuery(getTablesQuery).getResultList();
    for (Object[] o : tablesObjects) {
        String tableName = (String) o[0];
        String tableSchema = (String) o[1];
        String fullName = String.format("%s.%s", tableSchema, tableName);
        tables.add(fullName);
        // System.out.println(fullName);
    }
    log.debug("TABLES_LIST: {}", tables.toString());

    // find all occurences of string:
    for (String table : tables) {
        String query = "SELECT * FROM " + table;
        // https://stackoverflow.com/a/69945073/1974494
        final List<Tuple> queryRows = entityManager.createNativeQuery(query, Tuple.class).getResultList();
        final List<Map<String, Object>> formattedRows = new ArrayList<>();

        queryRows.forEach(row -> {
            final Map<String, Object> formattedRow = new HashMap<>();

            row.getElements().forEach(column -> {
                final String columnName = column.getAlias();
                final Object columnValue = row.get(column);
                if (columnValue.toString().contains(find)) {

                    if (columnValue.toString().startsWith("a:")) { // PHP serialized array
                        log.debug("Table: " + table + "; columnName: " + columnName + "; columnValue: "
                                + columnValue);
                        log.debug("Found PHP serialized array: {}", columnValue.toString());

                        try {
                            // https://docs.jboss.org/hibernate/orm/5.1/userguide/html_single/chapters/query-native/Native.html
                            MixedArray mlist = Pherialize.unserialize(columnValue.toString()).toArray();

                            for (Map.Entry<Object, Object> entry : mlist.entrySet()) {
                                Object key = entry.getKey();
                                Object val = entry.getValue();
                                log.debug("PHP ARRAY - KEY={}; VALUE={}", key, val);

                                if (val.toString().contains(find)) {
                                    String newValue = val.toString().replace(find, replace);
                                    entry.setValue(newValue);
                                }
                            }

                            String updateArray = Pherialize.serialize(mlist);
                            log.debug("PHP ARRAY - UPATED_ARRAY= {}", updateArray);
                            log.debug("PHP ARRAY - REPLACE: {} WITH: {}", columnValue.toString(), updateArray);
                            String updateQuery = String.format("UPDATE %s SET %s = '%s' WHERE %s = '%s'", table,
                                    columnName, updateArray, columnName, columnValue);
                            log.debug(updateQuery);
                            if (update) {
                                try {
                                    int result = entityManager.createNativeQuery(updateQuery).executeUpdate();
                                    log.info("QUERY=\"{}\", RESULT(PHP): {}", updateQuery, result);
                                } catch (Exception e) {
                                    e.printStackTrace();
                                    String msg = String.format("SQL update query error. Query=\"%s\", Error=\"%s\"",
                                            updateQuery, e.toString());
                                    log.error(msg);
                                    throw new ResponseStatusException(HttpStatus.INTERNAL_SERVER_ERROR,
                                            "SQL update query error");
                                }
                            }

                        } catch (Exception e) {
                            e.printStackTrace();
                            log.error("Broken PHP serialized array");
                            throw new ResponseStatusException(HttpStatus.INTERNAL_SERVER_ERROR,
                                    "Broken PHP serialized array");
                        }

                    } else {

                        String newValue = columnValue.toString().replace(find, replace);
                        log.debug("REPLACE: {} WITH: {}", columnValue.toString(), newValue);
                        String updateQuery = String.format("UPDATE %s SET %s = '%s' WHERE %s = '%s'", table,
                                columnName, newValue, columnName, columnValue);
                        log.debug(updateQuery);
                        if (update) {
                            try {
                                int result = entityManager.createNativeQuery(updateQuery).executeUpdate();
                                log.info("QUERY=\"{}\", RESULT(NORMAL): {}", updateQuery, result);
                            } catch (Exception e) {
                                e.printStackTrace();
                                String msg = String.format("SQL update query error. Query=\"%s\", Error=\"%s\"",
                                        updateQuery, e.toString());
                                log.error(msg);
                                throw new ResponseStatusException(HttpStatus.INTERNAL_SERVER_ERROR,
                                        "SQL update query error");
                            }
                        }
                    }
                }
                formattedRow.put(columnName, columnValue);
            });

            formattedRows.add(formattedRow);
        });
    }
}

Java Hibernate：替换所有出现的字符串，而不使用实体Map

1条答案

相关问题

热门标签

最新问答