Postgres UUID JDBC 不起作用 [英] Postgres UUID JDBC not working
问题描述
用于 postgres 的最新 Java JDBC 驱动程序声称本机支持 UUID；针对 Postgres 9.2 (mac) 工作.
The latest Java JDBC drivers for postgres claim to support UUIDs natively; working against Postgres 9.2 (mac).
确实，当使用 PreparedStatement 时，我可以单步执行驱动程序代码，甚至可以走通过 AbstractJdbc3gStatement.java 中专门的setUuid"函数.所有迹象表明，它应该正常工作".
Indeed, when a PreparedStatement is used, I can step through the driver code, and even walk through the specialised 'setUuid' function in AbstractJdbc3gStatement.java. By all indications, it should 'just work'.
然而，它不起作用.数据库返回一个错误，我因此收到:
However, it does not work. The database flings back an error, which I receive thus:
Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: uuid = bytea
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 139
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2157) ~[postgresql-9.2-1002.jdbc4.jar:na]
是的，确实，JDBC 驱动程序中的 setUuid 确实将其作为字节发送:
Yes, indeed, setUuid in the JDBC driver does send that as a bytea :
private void setUuid(int parameterIndex, UUID uuid) throws SQLException {
if (connection.binaryTransferSend(Oid.UUID)) {
byte[] val = new byte[16];
ByteConverter.int8(val, 0, uuid.getMostSignificantBits());
ByteConverter.int8(val, 8, uuid.getLeastSignificantBits());
bindBytes(parameterIndex, val, Oid.UUID);
} else {
bindLiteral(parameterIndex, uuid.toString(), Oid.UUID);
}
}
什么给?实际数据库中是否需要一些魔法符文来祝福这种转换?
What gives? Is there some magic rune required in the actual database to bless this conversion ?
推荐答案
tl;dr
myPreparedStatement.setObject(
… ,
java.util.UUID.randomUUID()
)
详情
(a) 向我们展示您的代码.
Details
(a) Show us your code.
PreparedStatement::setObject 在传递 java.util.UUID.您的代码中可能还有其他一些问题.
PreparedStatement::setObject does work when passing a java.util.UUID. You likely have some other issue in your code.
(b) 见我的博文 UUID 值从 JDBC 到 Postgres 一些讨论和示例代码.
(b) See my blog post UUID Values From JDBC to Postgres for a bit of discussion and example code.
// Generate or obtain data to store in database.
java.util.UUID uuid = java.util.UUID.randomUUID(); // Generate a random UUID.
String foodName = "Croissant";
// JDBC Prepared Statement.
PreparedStatement preparedStatement = conn.prepareStatement( "INSERT INTO food_ (pkey_, food_name_ ) VALUES (?,?)" );
int nthPlaceholder = 1; // 1-based counting (not an index).
preparedStatement.setObject( nthPlaceholder++, uuid );
preparedStatement.setString( nthPlaceholder++, foodName );
// Execute SQL.
if ( !( preparedStatement.executeUpdate() == 1 ) ) {
// If the SQL reports other than one row inserted…
this.logger.error( "Failed to insert row into database." );
}
(c) 我不知道你的意思
(c) I'm not sure what you mean by
用于 postgres 的最新 Java JDBC 驱动程序声称本机支持 UUID
The latest Java JDBC drivers for postgres claim to support UUIDs natively
哪个司机?至少有两个用于 Postgres 的开源 JDBC 驱动程序，当前/旧版和一个新的重写
Which driver? There are at least two open-source JDBC drivers for Postgres, the current/legacy one and a new rewrite "next generation" one. And there are other commercial drivers as well.
本地"?您可以链接到您阅读的文档吗?SQL 规范没有用于 UUID(不幸的是 ☹)的数据类型，因此 JDBC 规范 没有 UUID 的数据类型.作为一种变通方法，Postgres 的 JDBC 驱动程序使用 PreparedStatement 上的 setObject 和 getObject 方法在 Java ↔ SQL ↔ Postgres 之间移动 UUID.请参阅上面的示例代码.
"natively"? Can you link to the documentation you read? The SQL spec has no data type for UUID (unfortunately ☹), therefore the JDBC spec has no data type for UUID. As a workaround, the JDBC driver for Postgres uses the setObject and getObject methods on PreparedStatement move the UUID across the chasm between Java ↔ SQL ↔ Postgres. See the example code above.
正如 PreparedStatement JDBC 文档所说:
如果需要任意参数类型转换，则应将方法 setObject 与目标 SQL 类型一起使用.
If arbitrary parameter type conversions are required, the method setObject should be used with a target SQL type.
也许是本机"，您将 Postgres 对 UUID 作为数据类型的本机支持与具有 UUID 数据类型的 JDBC 混淆了.Postgres 确实支持 UUID 作为数据类型，这意味着该值存储为 128 位，而不是多次存储为 ASCII 或 Unicode 十六进制字符串.原生也意味着 Postgres 知道如何在该类型的列上构建索引.
Perhaps by "natively", you confused Postgres' native support for UUID as a data type with JDBC having a UUID data type. Postgres does indeed support UUID as a data type, which means the value is stored as 128-bits rather than multiple times that if it were stored as as ASCII or Unicode hex string. And being native also means Postgres knows how to build an index on a column of that type.
我上面提到的博客文章的重点是，我惊喜地发现在 Java ↔ SQL ↔ Postgres 之间架起鸿沟是多么简单.在我第一次没有受过教育的尝试中，我工作太努力了.
The point of my blog post mentioned above was that I was pleasantly surprised by how simple it is to bridge that chasm between Java ↔ SQL ↔ Postgres. In my first uneducated attempts, I was working too hard.
关于 Postgres 支持 UUID 的另一个说明...... Postgres 知道如何存储、索引和检索现有的 UUID 值.要生成 UUID 值，您必须启用 Postgres 扩展(插件)uuid-ossp.这个扩展包装了一个由OSSP项目提供的库，用于生成各种UUID值.请参阅我的博客以获取说明.
Another note about Postgres supporting UUID… Postgres knows how to store, index, and retrieve existing UUID values. To generate UUID values, you must enable the Postgres extension (plugin) uuid-ossp. This extension wraps a library provided by The OSSP Project for generating a variety of kinds of UUID values. See my blog for instructions.
顺便说一下……
如果我知道如何请求 JDBC 专家组或 JSR 团队让 JDBC 知道 UUID，我当然会.他们正在为 JSR 310: Date and Time 中定义的新日期时间类型这样做API.
If I knew how to petition the JDBC expert group or JSR team to make JDBC aware of UUID, I certainly would. They are doing just that for the new date-time types being defined in JSR 310: Date and Time API.
同样，如果我知道如何向 SQL 标准委员会请求添加 UUID 数据类型，我会的.但显然那个委员会比苏联政治局更神秘，比冰川慢.
Similarly, if I knew how to petition the SQL standards committee to add a data type of UUID, I would. But apparently that committee is more secretive than the Soviet Politburo and slower than a glacier.
这篇关于Postgres UUID JDBC 不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
