java - 我在 mysql 数据库中的表具有 utf-8 排序规则，但我仍然无法导入一些具有拉丁字符的名称

Question

以下是该命令的输出:

show create table persons;

CREATE TABLE `persons` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name_first` char(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '{search_person_txtP_mv}',
  `name_middle` char(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '{search_person_txtP_mv}',
  `name_last` char(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '{search_person_txtP_mv}',
  `name_unstructured` char(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT 'the entire string from {search_person_txtP_mv}, but only if the strong does not follow the "normal" logic',
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name_first`,`name_middle`,`name_last`,`name_unstructured`)
) ENGINE=InnoDB AUTO_INCREMENT=3795096 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Persons are typically authors of documents but also users or editors are persons. '

我看到name_first，name_last配备了处理utf8_unicode_ci。然而，当我尝试导入数据时，我遇到了 java.lang.NullPointerException 错误，其中 e.printStackTrace() 指向 name_last、name_first 。

奇怪的是，没有插入服务器数据库的记录被插入到本地数据库中，没有任何错误。

名称插入代码:

public Long addPersonToDbIfNotExists(JSONDocument document, Person author) throws SQLException {
        PreparedStatement stateAuthorExists = null;
        PreparedStatement stateInsertAuthor = null;
        ResultSet rs = null;
        Long authorKey = null;
        int count = 1;   

        String queryAuthorExists = "SELECT " + constants.getPersonID() + " FROM " + constants.getPersons() + " WHERE "
                + constants.getFirstname() + (firstname == null ? " is null" : " = ? ") + " AND "
                + constants.getMiddlename() + (middlename == null ? " is null" : " = ? ") + " AND "
                + constants.getSurname() + (surname == null ? " is null" : " = ? ") + " AND "
                + constants.getUnstructured() + (unstructured == null ? " is null" : " = ? ");

        try {
            stateAuthorExists = conn.prepareStatement(queryAuthorExists, Statement.RETURN_GENERATED_KEYS);

            // if not null insert true value for the corresponding ? (which is
            // represented by count)
            if (firstname != null)
                stateAuthorExists.setString(count++, firstname);

            if (middlename != null)
                stateAuthorExists.setString(count++, middlename);

            if (author.getFirstname() != null)
                stateAuthorExists.setString(count++, surname);

            if (unstructured != null)
                stateAuthorExists.setString(count++, unstructured);

            rs = stateAuthorExists.executeQuery();

            // if it is a new author not already present in the database
            if (!rs.next()) {
                ResultSet rs2 = null;

                String queryAuthor = "INSERT INTO " + constants.getPersons() + " (" + constants.getFirstname() + ", "
                        + constants.getMiddlename() + ", " + constants.getSurname() + ", " + constants.getUnstructured()
                        + ")" + "VALUES (?, ?, ?, ?)";

                try {
                    stateInsertAuthor = conn.prepareStatement(queryAuthor, Statement.RETURN_GENERATED_KEYS);

                    // handle null values and other preprocessing stuff
                    SetIfNull(document, stateInsertAuthor, author.getFirstname(), 1, "string",
                            constants.getFirstname());
                    SetIfNull(document, stateInsertAuthor, author.getMiddlename(), 2, "string",
                            constants.getMiddlename());
                    SetIfNull(document, stateInsertAuthor, author.getSurname(), 3, "string", constants.getSurname());
                    SetIfNull(document, stateInsertAuthor, author.getUnstructured(), 4, "string",
                            constants.getUnstructured());

                    stateInsertAuthor.executeUpdate();

                    // get the autogenerated key back
                    rs2 = stateInsertAuthor.getGeneratedKeys();
                    if (rs2.next())
                        authorKey = rs2.getLong(1);

                } catch (SQLException e) {
                    System.out.println(document.getDocumentPath() + ": " + document.getIdentifier() + "SetIfNulladdPersonToDB");
                    e.printStackTrace();
                    throw e;
                } finally {
                    rs.close();
                    rs2.close();
                    stateInsertAuthor.close();
                }
            } else

                authorKey = (long) rs.getInt(constants.getPersonID());
        } catch (SQLException sqle) {
            System.out.println(document.getDocumentPath() + ": " + document.getIdentifier() + "addPersonToDB1");
            throw sqle;
        } catch (Exception e) {
            System.out.println(document.getDocumentPath() + ": " + document.getIdentifier() + "addPersonToDB2");
            e.printStackTrace();
            throw e;
        } finally {
            try {
                stateAuthorExists.close();
                if (rs != null)
                    rs.close();
            } catch (SQLException e) {
                throw e;
            }
        }
        return authorKey;
    }

Answer 1

连接需要确定客户端字节为 latin1:将 ?useUnicode=no&characterEncoding=LATIN1 添加到 JDBC URL。

如果您仍然遇到问题，请将一些错误存储的数据转储为十六进制。