英文:
Postgresql/JDBC fails with or without UTF8 encoding
问题
使用Java 8和Postgres 10服务器，以及v 42.2.16的Postgresql驱动程序，我有一个基本的JDBC查询函数：
public List<Map<String, Object>> Query(String sql) throws Exception {
Connection con = null;
PreparedStatement pstmt;
List<Map<String, Object>> resultSetToList = null;
try {
con = DriverManager.getConnection(ConnectionString, Properties);
pstmt = con.prepareStatement(sql);
pstmt.execute();
ResultSet resultSet = pstmt.getResultSet();
resultSetToList = resultSetToList(resultSet);
pstmt.close();
} catch (Exception e) {
throw e;
} finally {
if (con != null)
con.close();
}
return resultSetToList;
}
我执行类似这样的查询：
Query("SELECT * FROM bi_functions");
但是它抛出了一个异常：
org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0xa3
这个数据库（由第三方提供）的编码是SQL_ASCII。0xA3 是'英镑'字符。
因此，我将查询更改为：
SET LOCAL CLIENT_ENCODING TO 'SQL_ASCII'; SELECT * FROM bi_functions;
但它失败了：
org.postgresql.util.PSQLException: The server's client_encoding parameter was changed to SQL_ASCII. The JDBC driver requires client_encoding to be UTF8 for correct operation.
在不使用其他技术的情况下，有没有一种使用纯JDBC来避免此错误的方法？
英文:
Using Java 8 and a Postgres 10 Server, and the v 42.2.16 Postgresql Driver,
I have a basic JDBC query function:
public List<Map<String, Object>> Query(String sql) throws Exception {
Connection con = null ;
PreparedStatement pstmt;
List<Map<String, Object>> resultSetToList = null;
try {
con = DriverManager.getConnection(ConnectionString, Properties);
pstmt = con.prepareStatement( sql );
pstmt.execute();
ResultSet resultSet = pstmt.getResultSet();
resultSetToList = resultSetToList(resultSet);
pstmt.close();
} catch(Exception e){
throw e;
}
finally {
if (con != null)
con.close();
}
return resultSetToList;
}
I execute a query like this:
Query("SELECT * FROM bi_functions");
But it throws an Exception
> org.postgresql.util.PSQLException: ERROR: invalid byte sequence for encoding "UTF8": 0xa3
The database (provided by 3rd party) has encoding SQL_ASCII. 0xA3 is the 'British Pound' character.
So I change the query to:
SET LOCAL CLIENT_ENCODING TO 'SQL_ASCII'; SELECT * FROM bi_functions;
It fails with:
>org.postgresql.util.PSQLException: The server's client_encoding parameter was changed to SQL_ASCII. The JDBC driver requires client_encoding to be UTF8 for correct operation.
Is there a way using plain JDBC to circumvent this error?
答案1
得分: 1
Java在内部使用Unicode编码，因此您不能在JDBC驱动程序中使用与UTF8不同的client_encoding。
您应该找出数据库的实际编码（可能是ISO 8859之一或Windows编码）。然后使用该编码创建一个数据库，将原始数据库转储并将转储的数据库加载到新数据库中。
否则您将无法使用JDBC与此数据库进行交互。
英文:
Java uses a Unicode encoding internally, so you cannot use a client_encoding different from UTF8 with the JDBC driver.
You should figure out the actual encoding of the database (probably one of the ISO 8859 or a Windows encoding). Then create a database with that encoding and dump the original database and load your dumped database into it.
Otherwise you won't be able to use this database with JDBC.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论