Map an Oracle database view to a grails domain class

Question

Is it possible to map a domain class to a Oracle database view?

Brand new project created with Grails 2.3.4, and mapped to a view, only using 2 columns. Soon as we try to run it, we get the following error message:

Message: ORA-00904: "THIS_"."STUSTAT_STUDENTID": invalid identifier

Line | Method
->>  112 | throwSqlException    in oracle.jdbc.driver.DatabaseError
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
|    331 | processError         in oracle.jdbc.driver.T4CTTIoer
|    288 | processError . . . . in     ''
|    743 | receive              in oracle.jdbc.driver.T4C8Oall
|    216 | doOall8 . . . . . .  in oracle.jdbc.driver.T4CPreparedStatement
|    799 | executeForDescribe   in     ''
|   1037 | executeMaybeDescribe in oracle.jdbc.driver.OracleStatement
|    839 | executeMaybeDescribe in oracle.jdbc.driver.T4CPreparedStatement
|   1132 | doExecuteWithTimeout in oracle.jdbc.driver.OracleStatement
|   3316 | executeInternal      in oracle.jdbc.driver.OraclePreparedStatement
|   3361 | executeQuery . . . . in     ''
|     55 | <init>               in grails.orm.PagedResultList
|     15 | $tt__index . . . . . in test.StustatController
|    195 | doFilter             in grails.plugin.cache.web.filter.PageFragmentCachingFilter
|     63 | doFilter . . . . . . in grails.plugin.cache.web.filter.AbstractFilter
|   1145 | runWorker            in java.util.concurrent.ThreadPoolExecutor
|    615 | run . . . . . . . .  in java.util.concurrent.ThreadPoolExecutor$Worker
^    724 | run                  in java.lang.Thread

Seems like this should be doable, but maybe GORM or Hibernate can't map to a view? I know we could write a Groovy SQL statement in the controller logic to display the contents, which I've done to verify I have read access. So where is the issue?

Here's the domain class with mapping to the Oracle view. Edit:

class Stustat {
String id
String firstName

static mapping = {
    sort "id"
    version false
    table 'stustat'
    columns{
            id column: 'stustat_studentid'
            firstname column: 'stustat_firstname'

    }
}

}

Can you please add your Domain class and the statement you use to create the view? Maybe this can help you mscharhag.com/2014/01/using-database-views-in-grails.html — micha

Martin Hauner Martin Hauner · Accepted Answer · 2014-08-08T06:29:57

Domain classes expect specific table and column names. If your view does use different names you can tell grails using static mapping. The domain class also needs a primary key. If you don’t have one you can create a key using composite id.

Map an Oracle database view to a grails domain class

1 Answers