尝试检索插入的行时插入失败 [英] Insert fails when trying to retrieve the inserted row

问题描述

我有一个实体，其ID是SQL Server数据库中生成的GUID(这是我无法更改其架构的第三方数据库)，因此该字段已定义；

I have an entity whose id is a generated GUID in a SQL Server db (it's a third-party db for which I can't change the schema) and the field is defined thus;

@Id
@GenericGenerator(name="generator", strategy="guid", parameters = {})
@GeneratedValue(generator ="generator")
@Column(name="FarmID")
public String getId() {
    return id;
}

当我创建一个新实体并将其保存时，该ID首先由GUIDGenerator调用 newid()正确生成，然后使用生成的ID作为参数值插入该行.但是，在插入之后，Hibernate尝试检索新插入的行但失败，并且事实证明它正在使用空白的id值-这是跟踪日志；

When I create a new entity and save it, the id is first correctly generated by the GUIDGenerator calling newid(), then the row is inserted using the generated id as a parameter value. However, after the insertion Hibernate tries to retrieve the newly inserted row but fails and it turns out that it's using a blank id value - here's the trace log;

[main]  AbstractBatcher                DEBUG about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[main]  SQL                            DEBUG 
select
    newid()
[main]  AbstractBatcher                TRACE preparing statement
[main]  GUIDGenerator                  DEBUG GUID identifier generated: 5B5495B4-FFE3-4112-B079-4FB799320BA7
[main]  AbstractBatcher                DEBUG about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[main]  AbstractBatcher                TRACE closing statement
[main]  ConnectionManager              TRACE registering flush begin
[main]  AbstractBatcher                DEBUG about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[main]  SQL                            DEBUG 
insert 
into
    Farm
    (FarmBusinessID, FarmName, PortalFarm, FarmID) 
values
    (?, ?, ?, ?)
[main]  AbstractBatcher                TRACE preparing statement
[main]  BasicBinder                    TRACE binding parameter [1] as [VARCHAR] - F2542B84-82E4-4DF6-BDBB-E830BFE5DDC1
[main]  BasicBinder                    TRACE binding parameter [2] as [VARCHAR] - XXXXXX XXXXX (XXX)
[main]  BasicBinder                    TRACE binding parameter [3] as [BIT] - true
[main]  BasicBinder                    TRACE binding parameter [4] as [VARCHAR] - 5B5495B4-FFE3-4112-B079-4FB799320BA7
[main]  AbstractBatcher                DEBUG about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[main]  AbstractBatcher                TRACE closing statement
[main]  ConnectionManager              TRACE registering flush end
[main]  HQLQueryPlan                   TRACE find: select id from agronomy.model.hub.Farm where id = :id
[main]  AbstractBatcher                DEBUG about to open PreparedStatement (open PreparedStatements: 0, globally: 0)
[main]  SQL                            DEBUG 
select
    farm0_.FarmID as col_0_0_ 
from
    Farm farm0_ 
where
    farm0_.FarmID=?
[main]  AbstractBatcher                TRACE preparing statement
[main]  BasicBinder                    TRACE binding parameter [1] as [VARCHAR] - 
[main]  AbstractBatcher                DEBUG about to open ResultSet (open ResultSets: 0, globally: 0)
[main]  AbstractBatcher                DEBUG about to close ResultSet (open ResultSets: 1, globally: 1)
[main]  AbstractBatcher                DEBUG about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[main]  AbstractBatcher                TRACE closing statement
[main]  JDBCExceptionReporter          WARN  SQL Error: 8169, SQLState: S0002
[main]  JDBCExceptionReporter          ERROR Conversion failed when converting from a character string to uniqueidentifier.

我不知道我在明确调用最后一个SELECT(失败)，所以我不确定在哪里可以看到它为什么失败. SQL错误大致翻译为您不能使用空字符串作为uniqueidentifier值"，这很有意义.

I'm not aware that I'm explicitly invoking that last SELECT (that fails), so I'm not sure where I can look to see why it's failing. THE SQL error roughly translates as "you can't use an empty string as a uniqueidentifier value", which rather makes sense.

推荐答案

我永远无法深入浅出，但是幸运的是，我错误地认为这是第三方数据库给了我这个问题，实际上是架构在我们的控制之下.

I could never get to the bottom of this, but as luck would have it, I'd incorrectly thought it was a third-party db which was giving me this problem, when in fact the schema was under our control.

只需将表上的主键更改为BIGINT(设置为标识列)并将实体ID的数据类型更改为Long，就可以重新运行测试代码，并将实体正确地持久保存到数据库中.一切都按预期进行，我无法停止倾听和发誓.

Simply by changing the primary keys on the tables to BIGINT (set as identity columns) and changing the datatypes of the entity ids to Long, I was able to re-run the test code and the entities were correctly persisted to the database. Everything worked as expected and I could stop tearing my hear out and swearing.

深入了解GUID为何不起作用的原因是很高兴的，但是在这种情况下，寿命太短了，可以接受.对于将来遇到类似问题的任何人，祝您好运.

It would have been nice to get to the bottom of why GUIDs weren't working, but in this instance life is simply too short and the alternative is acceptable. Best of luck to anyone who runs up against a similar problem in the future.

这篇关于尝试检索插入的行时插入失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！