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确实将其作为bytea发送：

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

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驱动程序，当前/旧版和一个新的重写< a href =https://github.com/impossibl/pgjdbc-ng =nofollow noreferrer>下一代一个。还有其他商业驱动因素。

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 方法将UUID移到跨越鸿沟之间Java↔SQL↔Postgres。请参阅上面的示例代码。

"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 doc 说：

如果需要任意参数类型转换，方法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.

顺便说一句......

By the way…

如果我知道如何向JDBC专家请愿组或JSR团队让JDBC知道UUID，我当然会。他们正在为 JSR 310：Date and date中定义的新日期时间类型做到这一点。时间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屋！