插入数据库从grails服务失败 [英] inserts in database failing from grails service

问题描述

我使用oracle11g作为数据库，并尝试从我的grails服务类插入到globalusers表中。
我正在检索grails服务中的数据源，如下所示：

  import org.codehaus.groovy.grails.commons。 ApplicationHolder as AH $ b $ class UserImportService {
 def dataSource = AH.application.mainContext.dataSource 
 def sql = new Sql（dataSource）; 
 insert insert insert into GLOBALUSERS（..）values（..）
 try {
 sql.execute（insertQuery）
} 
 catch（Exception e） {
 
 println无法插入：+ insertQuery 
 println例外：+ e; 
 
} 
} 
  

当我从前端运行服务时，我得到了sql异常说：

 异常是：java.sql.SQLIntegrityConstraintViolationException：ORA-01400：无法将NULL插入（GRA。GLOBALUSERS。ID）
  b 
 $ b 
使用来自其他用户界面的相同数据源..一个一个..它的工作..所以方言工作正常，但批量更新此服务不起作用。
解决方案在 dataSource  bean上执行直接Sql会绕过Hibernate的id生成方法，据我了解，它执行 nextval（）在适当的Oracle序列上。所以你的选择是：
 
 
 
 
 
使用Grails / GORM创建对象，填充和 .save（ ） 
 
 
自己查询序列，这将涉及在序列上执行 nextval（），但它会让你的Sql特定于Oracle 
 
 
在数据库中添加一个Oracle触发器，该触发器测试主键是否已设置，如果不是，则执行 nextval（）序列查询并将其添加到新的数据库行。这将使您的Sql直接与MySql自动增量和Oracle序列兼容。
 
 
 
I am using oracle11g as the database, and trying to do inserts into globalusers table from my grails service class. 
I am retrieving the datasource in grails service like this:
import org.codehaus.groovy.grails.commons.ApplicationHolder as AH 
class UserImportService {
 def dataSource = AH.application.mainContext.dataSource
 def sql = new Sql(dataSource) ;
 String insertQuery="insert into GLOBALUSERS (..) values (..)
                try{
                    sql.execute(insertQuery)
                }
                catch(Exception e){

                    println "Failed to insert : " +insertQuery
                    println "Exception is:" + e;

                }
}
When I run the service from front end I get sql exception saying 
Exception is:java.sql.SQLIntegrityConstraintViolationException: ORA-01400: cannot insert NULL into ("GRA"."GLOBALUSERS"."ID")
Since I am using a Datasource with my custom dialect which handles ID generation and autoincrement this should be handled by grail/hibernate.

When use the same datasource from other UI .. one by one.. it works..so the dialect is working fine. but this service for batch updates is not working.
解决方案
Doing direct Sql on the dataSource bean is going to bypass Hibernate's method of id generation, which, as I understand it, does a nextval() on the appropriate Oracle sequence.  So your options are:


Use Grails/GORM, where you create the object, populate, and .save()
Query the sequence yourself, which would involve doing a nextval() on the sequence, but it would make your Sql Oracle-specific
Add an Oracle trigger to the database, which tests if the primary key is set, and if it isn't does the nextval() sequence query and adds it to the new database row.  This will keep your direct Sql compatible with MySql autoincrement and Oracle sequences.


                        
这篇关于插入数据库从grails服务失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！