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

Grails Script DB Connection parameter storing

Storing Userids / passwords for multiple connections securely in one central file in one’s home directory.



// Load DB settings

def env = System.getenv()

String propfile = "${env['HOME']}/.dbconfig.properties"

def config = new ConfigSlurper().parse(new File(propfile).toURL())

println "Running with DB User ID : " << config.db.snp.dev.userid



def db

if ( Config.DBTOUPDATE == 'prod' ) {

db = Sql.newInstance(config.db.snp.prod.jdbcURL, config.db.snp.prod.userid, config.db.snp.prod.password, config.db

dbgcrd = Sql.newInstance(config.db.gcrd.dev.jdbcURL, config.db.gcrd.dev.userid, config.db.gcrd.dev.password, confi

} else {

db = Sql.newInstance(config.db.snp.dev.jdbcURL, config.db.snp.dev.userid, config.db.snp.dev.password, config.db.sn

dbgcrd = Sql.newInstance(config.db.gcrd.dev.jdbcURL, config.db.gcrd.dev.userid, config.db.gcrd.dev.password, confi

}

The format of the config params is :


[gkowalski@scott:~/projects/snpDatabaseImport]$ ls -l ~/.dbconfig.properties

-rwx------+ 1 gkowalski gkowalski 682 Sep 18 12:13 .dbconfig.properties

[gkowalski]$ cat ~/.dbconfig.properties

// Development Database settings

db.snp.dev.userid="devUserid"

db.snp.dev.password="xxxx"

db.snp.dev.jdbcURL="jdbc:oracle:thin:@server.mcw.edu:1521:SID"

db.snp.dev.jdbcDriver="oracle.jdbc.driver.OracleDriver"

// Production Database settings

db.snp.prod.userid="prodUserId"

db.snp.prod.password="xxxxxx"

db.snp.prod.jdbcURL="jdbc:oracle:thin:@server.com:1521:SID1"

db.snp.prod.jdbcDriver="oracle.jdbc.driver.OracleDriver"


This keeps one from checking in DB passwords into SVN… Also keeps them all in one central spot for updating.