在Grails / Hibernate中使用Oracle的GUID（）生成的ID [英] Using Oracle's GUID()-generated ID's in Grails/Hibernate

问题描述

我尝试使用Grails Scaffolding在一些遗留数据库表中一起扔出一个快速CRUD应用程序。它是一个Oracle数据库，并且主键值将由Oracle的 GUID（）函数填充。

基于此早期的StackOverflow问题，我尝试在Grails领域类中为此列指定guid作为Hibernate生成器：

  ... 
静态映射= {
表名：OWNER
版本false 
列{
 id列：OWNER_OID，生成器：guid
名称列： NAME
 ... 
} 
} 
 ... 
  

当我运行我的Grails应用程序时，查看甚至编辑记录工作得很好。但是，当我尝试创建新记录时，Oracle错误消息 ORA-02289：序列不存在导致爆炸。

我为我的数据源启用了SQL日志记录，并且在保存操作过程中看到Grails / Hibernate尝试执行以下内容：

 从
中选择hibernate_sequence.nextval  

这看起来并不正确，而且与上面链接的早期StackOverflow问题中生成的SQL不匹配。有没有人看到我在这里失踪的东西，或者知道如何让Grails / Hibernate使用Oracle GUID值填充主键列？

解决方案

呃......在与此搏斗的另一天之后，我想我已经拥抱了这件事。这个答案涵盖了比原始问题描述更多的内容，但那是因为我在发现Hibernate生成器问题后发现了更多问题。

问题＃1： Oracle GUID（） value

正如Adam Hawkes的回答所涵盖的那样，guidHibernate生成器没有维护，只适用于旧版本的Oracle方言。

然而，如果您使用Hibernate生成器assigned（意味着您想手动设置主键而不是拥有Hibernate自动生成它们），那么你可以插入从Oracle SYS_GUID（）调用拉取的值。

Hibernate的新版Oracle语言不支持guid，他们仍然理解生成这些值所需的SQL。如果您位于控制器中，则可以使用以下命令获取该SQL查询：

 字符串guidSQL = grailsApplication.getMainContext（） .sessionFactory.getDialect（）。getSelectGUIDString（）
  

如果您位于域类相反，你仍然可以这样做......但是你需要先注入一个对grailsApplication的引用。您可能想在控制器中执行此操作，尽管...下面详细介绍了这一点。

如果您好奇，对于Oracle）是：

 从双元
中选择rawtohex（sys_guid（）） 

您可以执行此SQL并像这样获取生成的ID值：

  String guid = grailsApplication.getMainContext（）。sessionFactory.currentSession.createSQLQuery（guidSQL）.list（）。get（0）
  

问题2：实际在Grails域对象中使用此值

要在Grails域类中实际使用此GUID值，您需要使用Hibernate生成器分配。如前所述，这表明你想手动设置自己的ID，而不是让Grails / GORM / Hibernate自动生成它们。比较这个修改过的代码片段和我上面原始问题中的代码片段：

  ... 
 static mapping = {
表名：OWNER
版本false 
 id列：OWNER_OID，生成器：赋值
名称列：NAME
 ... 
 
 
 $ / code> 

在我的域类中，我将guid更改为分配。我还发现，我需要消除列{} 分组，并将我所有的列信息提升到某个级别（奇怪）。

现在，无论哪个Controller创建这些域对象...如上所述生成GUID，并将其插入到对象的 id 中领域。在由Grails Scaffolding自动生成的Controller中，函数将是 save（）：

  def save（）{
 def ownerInstance = new Owner（params）
 String guidSQL = grailsApplication.getMainContext（）。sessionFactory.getDialect（）。getSelectGUIDString（）
 ownerInstance .id = grailsApplication.getMainContext（）。sessionFactory.currentSession.createSQLQuery（guidSQL）.list（）。get（0）
 
 if（！ownerInstance.save（flush：true，insert：true）） {
 render（view：create，model：[ownerInstance：ownerInstance]）
 return 
} 
 
 flash.message = message（code：'default。 created.message'，args：[message（code：'owner.label'，default：'Owner'），ownerInstance.id]）
 redirect（action：show，id：ownerInstance.id）
 
 
 
 $ b 你可能会想把这个逻辑直接放在域对象中，在 code> beforeInsert（）函数。这肯定会更清洁和更优雅，但是有一些Grails已知的错误会阻止ID在< beforeInsert（）中正确设置。令人遗憾的是，您必须将此逻辑保留在Controller级别。
 
 
 
问题＃3：让Grails / GORM / Hibernate正确存储它
 
 
简单的事实是，Grails主要用于处理新的应用程序，并且它对遗留数据库的支持是非常多见的（公平，不过，它比其他动态我试过的框架）。即使您使用分配生成器，Grails有时会在持久化域对象时感到困惑。
 
 
 
其中一个问题是 .save（）调用有时会在执行INSERT时尝试执行UPDATE。请注意，在上面的Controller代码片段中，我添加了 insert：true 作为参数给 .save（） 
 
 
所有星星和行星必须保持一致才能正常工作。如果您的域类 static mapping {} 块没有将Hibernate生成器设置为分配，并且设置版本false ，那么Grails / GORM / Hibernate仍然会感到困惑，并尝试发出UPDATE而不是INSERT。  
 
 
如果您使用的是自动生成的Grails脚手架控制器，那么使用 insert：true 是安全的。在控制器的 save（）函数中，因为该函数仅在第一次保存新对象时被调用。当用户编辑现有对象时，将使用控制器的 update（）函数。但是，如果您在自己的自定义代码中执行自己的操作......在创建 .save（）之前，检查域对象是否已存在于数据库中将很重要 调用，并且只传递 insert：true 参数，如果它是第一次插入的话。
 
 
 
问题＃4：在Grails / GORM / Hibernate中使用自然键
 
 
 
最后一个注意事项，与Oracle GUID值无关，但与这些Grails问题有关。假设在传统数据库（例如我正在处理的那个数据库）中，某些表使用自然键作为主键。假设您有 OWNER_TYPE 表，其中包含 OWNER 的所有可能类型，并且 > NAME 列既是人类可读的标识符，也是主键。
 
 
 
你必须做一些其他的事情使这个工作与Grails脚手架。首先，当用户创建新对象时，自动生成的视图不会在屏幕上显示ID字段。您必须将一些HTML插入相关的视图才能为该ID添加字段。如果你给这个字段命名为 id ，那么自动生成的控制器的save（）函数将接收这个值为 params .id 。
 
 
其次，您必须确保自动生成的控制器的 save（）函数正确插入ID值。首次生成时，通过从View传递的CGI参数中实例化一个域对象开始save（）：
  def ownerTypeInstance = new OwnerType.get（params）
  
但是，这并不处理您添加的ID字段到您的视图。您仍然需要手动设置。如果在View上给了HTML字段名称 id ，那么它将在 save（）as params.id ：
  ... 
 ownerTypeInstance = new OwnerType（）
 ownerTypeInstance.id = params.id 
 //继续执行.save（）步骤，确保传入insert：true
 ... 
  
一块蛋糕，是吧？也许问题＃5就是要弄清楚为什么要让自己完成所有这些痛苦，而不是仅仅用Spring Web MVC（甚至是vanilla JSP）手工编写你的CRUD接口！ ：） 
I trying to use Grails Scaffolding to throw a quick CRUD application together around some legacy database tables.  It is an Oracle database, and the primary key value is intended to be populated by Oracle's GUID() function.

Based on this earlier StackOverflow question, I tried specifying "guid" as the Hibernate generator for this column in my Grails domain class:
...
static mapping = {
    table name: "OWNER"
    version false
    columns {
        id column: "OWNER_OID", generator: "guid"
        name column: "NAME"
        ...
    }
}
...
When I run my Grails app, viewing and even editing records works just fine.  However, when I try to create a new record, things blow up with the Oracle error message "ORA-02289: sequence does not exist".

I enabled SQL logging for my datasource, and see Grails/Hibernate trying to execute the following during a save operation:
select hibernate_sequence.nextval from dual
This doesn't look right at all, and doesn't match the generated SQL from that earlier StackOverflow question linked above.  Does anyone see something I am missing here, or otherwise know how to make Grails/Hibernate populate a primary key column with Oracle GUID values?
解决方案
Whew... after another day of wrestling with this, I think I have my arms around the thing.  This answer covers a bit more ground than the original question description, but that's because I found yet more problems after getting past the Hibernate generator issue.

Issue #1: Getting an Oracle GUID() value

As covered by Adam Hawkes' answer, the "guid" Hibernate generator is unmaintained and only works for older versions of the Oracle dialect.

However, if you use the Hibernate generator "assigned" (meaning that you want to set primary keys manually rather than have Hibernate auto-generate them), then you can insert values pulled from an Oracle SYS_GUID() call.

Even though Hibernate's newer Oracle dialects don't support "guid" seamlessly, they still understand the SQL necessary to generate these values.  If you are inside of a Controller, you can fetch that SQL query with the following:
String guidSQL = grailsApplication.getMainContext().sessionFactory.getDialect().getSelectGUIDString()
If you are inside of a domain class instead, you can still do this... but you will need to first inject a reference to grailsApplication.  You probably want to do this in a Controller, though... more on this below.

If you're curious, the actual String returned here (for Oracle) is:
select rawtohex(sys_guid()) from dual
You can execute this SQL and fetch the generated ID value like this:
String guid = grailsApplication.getMainContext().sessionFactory.currentSession.createSQLQuery(guidSQL).list().get(0)


Issue #2: Actually using this value in a Grails domain object

To actually use this GUID value in your Grails domain class, you need to use the Hibernate generator "assigned".  As mentioned earlier, this declares that you want to set your own ID's manually, rather than letting Grails/GORM/Hibernate generate them automatically.  Compare this modified code snippet to the one in my original question above:
...
static mapping = {
    table name: "OWNER"
    version false
    id column: "OWNER_OID", generator: "assigned"
    name column: "NAME"
    ...
}
...
In my domain class, I changed "guid" to "assigned".  I also found that I needed to eliminate the "columns {}" grouping block, and move all my column information up a level (weird).

Now, in whichever Controller is creating these domain objects... generate a GUID as described above, and plug it into the object's "id" field.  In a Controller generated automatically by Grails Scaffolding, the function will be "save()":
def save() {
    def ownerInstance = new Owner(params)
    String guidSQL = grailsApplication.getMainContext().sessionFactory.getDialect().getSelectGUIDString()
    ownerInstance.id = grailsApplication.getMainContext().sessionFactory.currentSession.createSQLQuery(guidSQL).list().get(0)

    if (!ownerInstance.save(flush: true, insert: true)) {
        render(view: "create", model: [ownerInstance: ownerInstance])
        return
    }

    flash.message = message(code: 'default.created.message', args: [message(code: 'owner.label', default: 'Owner'), ownerInstance.id])
    redirect(action: "show", id: ownerInstance.id)
}
You might think to try putting this logic directly inside the domain object, in a "beforeInsert()" function.  That would definitely be cleaner and more elegant, but there are some known bugs with Grails that prevent ID's from being set in "beforeInsert()" properly.  Sadly, you'll have to keep this logic at the Controller level.

Issue #3: Make Grails/GORM/Hibernate store this properly

The plain truth is that Grails is primarily intended for virgin-new applications, and its support for legacy databases is pretty spotty (in fairness, though, it's a bit less spotty than other "dynamic" frameworks I've tried).  Even if you use the "assigned" generator, Grails sometimes gets confused when it goes to persist the domain object.

One such problem is that a ".save()" call sometimes tries to do an UPDATE when it should be doing an INSERT.  Notice that in the Controller snippet above, I have added "insert: true" as a parameter to the ".save()" call.  This tells Grails/GORM/Hibernate explicitly to attempt an INSERT operation rather than an UPDATE one.  

All of the stars and planets must be in alignment for this to work right.  If your domain class "static mapping {}" block does not set the Hibernate generator to "assigned", and also set "version false", then Grails/GORM/Hibernate will still get confused and try to issue an UPDATE rather than an INSERT. 

If you are using auto-generated Grails Scaffolding controllers, then it is safe to use "insert: true" in the Controller's "save()" function, because that function in only called when saving a new object for the first time.  When a user edits an existing object, the Controller's "update()" function is used instead.  However, if you are doing your own thing in your own custom code somewhere... it will be important to check on whether a domain object is already in the the database before you make a ".save()" call, and only pass the "insert: true" parameter if it really is a first-time insert.

Issue #4: Using natural keys with Grails/GORM/Hibernate

One final note, not having to do with Oracle GUID values, but related to these Grails issues in general.  Let's say that in a legacy database (such as the one I've been dealing with), some of your tables use a natural key as their primary key.  Say you have an OWNER_TYPE table, containing all the possible "types" of OWNER, and the NAME column is both the human-readable identifier as well as the primary key.

You'll have to do a couple of other things to make this work with Grails Scaffolding.  For one thing, the auto-generated Views do not show the ID field on the screen when users are creating new objects.  You will have to insert some HTML to the relevant View to add a field for the ID.  If you give the field a name of "id", then the auto-generated Controller's "save()" function will receive this value as "params.id".

Secondly, you have to make sure that the auto-generated Controller's "save()" function properly inserts the ID value.  When first generated, a "save()" starts off by instantiating a domain object from the CGI parameters passed by the View:
def ownerTypeInstance = new OwnerType.get( params )
However, this does not handle the ID field you added to your View.  You will still need to set that manually.  If on the View you gave the HTML field a name of "id", then it will be available in "save()" as "params.id":
...
ownerTypeInstance = new OwnerType()
ownerTypeInstance.id = params.id 
// Proceed to the ".save()" step, making sure to pass "insert: true"
...
Piece of cake, huh?  Perhaps "Issue #5" is figuring out why you put yourself through all this pain, rather than just writing your CRUD interface by hand with Spring Web MVC (or even vanilla JSP's) in the first place!  :)

                        
这篇关于在Grails / Hibernate中使用Oracle的GUID（）生成的ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！