问题描述
我使用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文档后,我在 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查询中的外部/重音字符的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!