Grails: How to query a database with SQL outside GORM and return an HTML SELECT

Here is an example of how to perform a query outside GORM and pass it to a view for display in a drop down select …



import groovy.sql.Sql

class DbTableListController {

// The datasource for this grails app is injected
// automatically by it's name

def dataSource

def create = {
groovy.sql.Sql sql = new groovy.sql.Sql(dataSource)

def tableNames = []
sql.eachRow("""
SELECT
unique ( table_name )
FROM
ALL_TAB_COLUMNS
WHERE
OWNER = 'CRDEV'
""", { row ->
println "Found " << row.table_name
tableNames << row.table_name
}
)

def dbTableList = new DbTableList()
dbTableList.properties = params
return ['dbTableList':dbTableList, 'tableNames' : tableNames]
}

// Other Methods deleted ...
}

Then it’s displayed in the create.gsp for this controller with this code:

&lt g:select id=”tableName” name=”tableName” from=”${tableNames}” /&gt

Advertisements

2 thoughts on “Grails: How to query a database with SQL outside GORM and return an HTML SELECT

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s