sql查询中的外部/重音字符 [英] Foreign/accented characters in sql query
问题描述
我使用Java和Spring的JdbcTemplate类来构建一个查询Postgres数据库的Java查询。但是，我在执行包含外来/重音字符的查询时遇到问题。
I'm using Java and Spring's JdbcTemplate class to build an SQL query in Java that queries a Postgres database. However, I'm having trouble executing queries that contain foreign/accented characters.
例如（修剪）代码：
JdbcTemplate select = new JdbcTemplate( postgresDatabase );
String query = "SELECT id FROM province WHERE name = 'Ontario';";
Integer id = select.queryForObject( query, Integer.class );
将检索省ID，但如果改为 name ='Québec '，则查询无法返回任何结果（此值在数据库中，所以问题不是它缺失）。
will retrieve the province id, but if instead I did name = 'Québec' then the query fails to return any results (this value is in the database so the problem isn't that it's missing).
相信问题的根源是我需要使用的数据库的默认客户端编码设置为SQL_ASCII，其根据此防止自动字符集转换。 （Java环境编码设置为'UTF-8'，而我被告知数据库使用'LATIN1'/'ISO-8859-1'）
I believe the source of the problem is that the database I am required to use has the default client encoding set to SQL_ASCII, which according to this prevents automatic character set conversions. (The Java environments encoding is set to 'UTF-8' while I'm told the database uses 'LATIN1' / 'ISO-8859-1')
能够在resultSets包含具有外部字符的值时手动指示编码，作为对具有类似性质的先前问题的解决方案。
I was able to manually indicate the encoding when the resultSets contained values with foreign characters as a solution to a previous problem with a similar nature.
例如：
String provinceName = new String ( resultSet.getBytes( "name" ), "ISO-8859-1" );
但是现在外来字符是查询本身的一部分，这种方法还没有成功。 （我想，因为查询必须保存在一个字符串之前，无论如何，将其分解为字节，然后更改的编码只会混淆字符进一步。）
But now that the foreign characters are part of the query itself this approach hasn't been successful. (I suppose since the query has to be saved in a String before being executed anyway, breaking it down into bytes and then changing the encoding only muddles the characters further.)
有没有办法解决这个问题，而不必更改数据库的属性或重建它？
Is there a way around this without having to change the properties of the database or reconstruct it?
PostScript：我发现这个函数在StackOverflow当组成一个标题，它似乎不工作（我可能没有正确使用它，但即使它没有工作它似乎不是最好的解决方案。）：
PostScript: I found this function on StackOverflow when making up a title, it didn't seem to work (I might not have used it correctly, but even if it did work it doesn't seem like it could be the best solution.):
编辑：我已经选择了我自己的答案，因为这将是我的使用现在;但是，正如下面的评论中提到的，我将很乐意看看可能更好的其他建议，只要我能访问数据库。
I have selected my own answer for this, since it will be what I am using for now; however, as mentioned in a comment below I would be happy to look at other suggestions that may be better, for as long as I have access to the database.
推荐答案
没错，在通过postgreSQL文档后，我在 String Functions and Operators section。
Hmm okay, after slugging through the postgreSQL documentation, I found a solution in the String Functions and Operators section.
我使用 convert ，src_encoding名称，dest_encoding名称）函数并管理以获取魁北克省的省ID。
I used the convert(string bytea, src_encoding name, dest_encoding name) function and managed to get the province id for Québec.
例如
String query = "SELECT id FROM province WHERE name = convert( 'Québec', 'UTF-8', 'ISO-8859-1' );";
这篇关于sql查询中的外部/重音字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
