春季到Oracle UTF-8字符截断 [英] Spring to Oracle UTF-8 character truncation

问题描述

我遇到一个问题，即Oracle数据库中出现无效字符. ¿"或上下颠倒的问号.我知道它是由于将UTF8编码放入Oracle数据库的编码中引起的.我期望的字符示例是'–'，它看起来像普通的连字符，但不是，以及'''，它应该是普通的单引号.

I have an issue with invalid characters appearing in an Oracle database. The "¿" or upside-down question mark. I know its caused by UTF8 encoding's being put into the encoding of the Oracle database. Examples of characters I am expecting are ' – ' which looks like a normal hyphen but isnt, and ' ’ ' which should be a normal single quote.

输入:"2020-08-31 – 2020-12-31" -看起来像普通的连字符，但不是 输出:"2020-08-31-2020-12-31"

Input: "2020-08-31 – 2020-12-31" - looks like normal hyphen but not Output: "2020-08-31 ¿ 2020-12-31"

我知道字符的主要来源是从Office程序(如word)复制并粘贴，并将其转换为智能引号和内容.让用户关闭此功能不是可行的解决方案.

I know the primary source of the characters are copy and paste from office programs like word and its conversion to smart quotes and stuff. Getting the users to turn off this feature is not a viable solution.

有一个很好的文章关于处理此问题，他们提供了许多解决方案:

There was a good article about dealing with this and they offered a number of solutions:

• 将数据库切换为UTF8-由于某些原因无法做到
• 将varchar2更改为nvarchar2-我尝试了此解决方案，因为它是最适合我的需要的方法，但是在测试时，我仍然以无效字符结尾，所以并不是唯一的问题.
• 使用blob/clob-尝试过并导致页面崩溃
• 过滤器-吸引力不大，因为有很多地方需要更新.

• switch database to UTF8 - cant do it due to reasons
• change varchar2 to nvarchar2 - I tried this solution as it would be the best for my needs, but when tested I was still ending up with invalid characters, so its not the only point.
• use blob/clob - tried and got page crashes
• filter - not attractive as there are a lot of places that would need to be updated.

因此，所使用的技术是Eclipse，Spring，JDBC 4.2版和Oracle 12.

So the technologies being used are Eclipse, Spring, JDBC version 4.2, and Oracle 12.

因此，将信息输入到表单中，将表单保存起来，将其从控制器传递到DAO，并且在此处对其进行检查时，信息是正确的.当它传递到这里的JDBC中时，我就看不到它了，一旦它进入数据库，我就无法确定它是否已更改，或者那是在哪里发生的，但是数据库存储了无效字符.

So the information is entered into the form, the form gets saved, it passes from the controller into the DAO and when its checked here, the information is correct. Its when it passes here into the JDBC where I lose sight of it, and once it enters the database I cant tell if its already changed or if thats where its happening, but the database stores the invalid character.

因此，在数据库的插入语句之间的某处发生了这种情况.因此，它要么是JDBC要么是数据库，但是如何分辨和如何修复呢?我已经更改了存储信息的字段，它最初是一个varchar2，但是我尝试了nvarchar2，但它仍然无效.

So somewhere between the insert statement to the database this is happening. So its either the JDBC or the database, but how to tell and how to fix? I have changed the field where the information is being stored, its originally a varchar2 but I tried nvarchar2 and its still invalid.

我知道这个问题正在询问同一主题，但是答案对我没有帮助.

I know that this question is asking about the same topic, but the answers do not help me.

SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

PARAMETER                                                        VALUE                                                                CON_ID
---------------------------------------------------------------- ---------------------------------------------------------------- ----------
NLS_CHARACTERSET                                                 WE8ISO8859P15                                                             0 
NLS_NCHAR_CHARACTERSET                                           AL16UTF16                                                                 0 

因此在评论中，我得到了一个链接可能会有所帮助，但这给了我一个错误.

So in the comments I was given a link to something that might help but its giving me an error.

@Override public Long createComment(Comment comment) {
        return jdbcTemplate.execute((Connection connection) -> {
            PreparedStatement ps = connection.prepareStatement("INSERT INTO COMMENTS (ID, CREATOR, CREATED, TEXT) VALUES (?,?,?,?)", new int[] { 1 });
            ps.setLong(1, comment.getFileNumber());
            ps.setString(2, comment.getCreator());
            ps.setTimestamp(3, Timestamp.from(comment.getCreated()));
            ((OraclePreparedStatement) ps).setFormOfUse(4, OraclePreparedStatement.FORM_NCHAR);
            ps.setString(4, comment.getText());
            if(ps.executeUpdate() != 1) return null;
            ResultSet rs = ps.getGeneratedKeys();
            return rs.next() ? rs.getLong(1) : null;
        });

发生异常:org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement无法转换为oracle.jdbc.OraclePreparedStatement

An exception occurred: org.apache.tomcat.dbcp.dbcp2.DelegatingPreparedStatement cannot be cast to oracle.jdbc.OraclePreparedStatement

推荐答案

字符集WE8ISO8859P15(对应于ISO 8859-15)不包含–或’，因此您不能将它们存储在数据字段.

Character set WE8ISO8859P15 (resp. ISO 8859-15) does not contain – or ’, so you cannot store them in a VARCHAR2 data field.

将数据库迁移到Unicode(通常为AL32UTF8)或为此列使用NVARCHAR2数据类型.

Either you migrate the database to Unicode (typically AL32UTF8) or use NVARCHAR2 data type for this column.

我对Java不熟悉，但是我想您必须使用getNString.链接的文档应提供解决此问题的提示.

I am not familiar with Java but I guess you have to use getNString. The linked document should provide some hints to solve the issue.

这篇关于春季到Oracle UTF-8字符截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！