Sql/Database Views in Grails

SQL/Database Views in Grails

You can use plain SQL in Grails which is in the case of accessing a view the preferable way (IMO):

For example in your controller:

import groovy.sql.Sql

class MyFancySqlController {

    def dataSource // the Spring-Bean "dataSource" is auto-injected

    def list = {
        def db = new Sql(dataSource) // Create a new instance of groovy.sql.Sql with the DB of the Grails app

        def result = db.rows("SELECT foo, bar FROM my_view") // Perform the query

        [ result: result ] // return the results as model
    }

}

and the view part:

<g:each in="${result}">
    <tr>
        <td>${it.foo}</td>
        <td>${it.bar}</td>
    </tr>
</g:each>

I hope the source is self-explanatory. The Documentation can be found here

Trying to view Table in H2 database While using the Grails as a Framework

your grails app should give you the endpoint '/dbconsole' in dev mode. just give it a try!

Access a SQL Server 2008 View From Grails

You can use SQL, e.g. with groovy.sql.Sql as suggested in the other similar question's answer, but it's also possible in a domain class. If you create a domain class (use any sensible name) and specify its table name as the name of the view, you can select from it. You'll have problems creating and updating of course, but if you only want to read then it'll be fine:

class SomeDomainClass {
   String foo
   Integer bar
   static mapping = {
      table 'my_view'
   }
}

If you name the class MyView then mapping isn't needed since the naming convention applies, but this would be a bad name for the class since using it isn't related to the fact that it's backed by a view.

Note that you'll also have problems when using dbCreate set to "create", "create-drop", or "update" since Hibernate will try to create the table, but it shouldn't cause any real problem and just display an ignorable error like "could not create table 'my_view' since it already exists". And once you move to using database migrations this won't be a problem at all.

Mapping result of a native SQL query to Grails domain class

import com.acme.domain.*

def sessionFactory
sessionFactory = ctx.sessionFactory  // this only necessary if your are working with the Grails console/shell
def session = sessionFactory.currentSession 

def query = session.createSQLQuery("select f.* from Foo where f.id = :filter)) order by f.name");
query.addEntity(com.acme.domain.Foo.class); // this defines the result type of the query
query.setInteger("filter", 88);
query.list()*.name;

Use data in database table (not a domain) in grails

I created a grails domain which would make same database table as the grails-audit-plugin made.

Then I used this newly created domain to retrieve data in the database table populated by the plugin.

The domain I made is as follows.

class AuditLog {

    String actor
    String className
    Date dateCreated
    String eventName
    Date lastUpdated
    String newValue
    String oldValue
    String persistedObjectId
    Long persistedObjectVersion
    String propertyName
    String uri

    static constraints = {
        dateCreated nullable: false
        lastUpdated nullable: false
        newValue nullable: true
        oldValue nullable: true
        persistedObjectVersion nullable: true
    }

    static mapping = {
        version false
    }
}

Grails: Generate SQL CREATE TABLE statements from domain classes

Use the schema-export command for this.

If you're using Grails 3 you'll need to add the plugin as a classpath dependency in buildscript.dependencies. This is done for you in recent Grails 3 versions but in an earlier release you just need to add it yourself:

buildscript {
   ...
   dependencies {        
      ...
      classpath "org.grails.plugins:hibernate:4.3.10.5"
   }
}

---

Related Topics