繼前面已經建立Domain class以及基本頁面後，今日要跟大家分享基礎的GORM query語法，個人認為挺有Hibernate的味道，只是語法稍加改變一下而已，另外值一提的是參考書籍迄今仍未提到如何設定datasource之類的設定，是因為Grails預設適用H2這種In-memory式的資料庫，為了要demo簡易的query語法，故在啟動測試專案時，則預先寫入一些資料到資料庫，但重點是作者是在那裡或是說Grails載入的哪個時機點寫入這些資料的，這個問題就回到根本的問題上，Grails的web應用程式依舊是架構在Servlet上，只不過經過Groovy改寫後稱作Groovlet，變數有些相同例如ServletContext等，而網頁稱作GSP，廢話不多說，開始今天跟大家介紹。
servlet初始化的時候會呼叫init方法以及結束時會呼叫destroy方法，在Grails裡也是相同的，而設定Groovlet init及destory方法是定義在conf/BootStrap這個class裡，打開該檔案
class BootStrap {
def init = { servletContext ->
}
def destroy = {
}
就是init跟destroy方法，init方法亦有closure可以對servletContext做初始化，這其實就是Java的調調，只是少掉很多doGet, doPost等方法，那些都被包在Grails Framework裡。
接著看DataSource.groovy這個檔案，預設採用h2資料庫，同時可以注意到預設hibernate的second level cache是enable的，採用的是常見的Ehcache，再來是比較陌生的environments大括號括起來的地方，分成三種develop、test及production，對應到不同的環境，以目前我的程度應該只會用到test跟production，另外也可以自訂environment，其用途就需要再study了，而dbCreate就是代表存取資料庫的權限，update權限最大，可以CRUD，create、create-drop以及validate各自對應到不同權限，但有空再查吧!基本上程式要可以run的起來，dbCreate設定成update就可以進行操作了，production裡面的參數就不在說明囉，程式應該還沒大到要tune資料庫的參數才是，另外資料庫也可以換成mysql，上網google一下就有標準做法囉。
dataSource {
pooled = true
driverClassName = "com.mysql.jdbc.Driver"
username = "root"
password = "LGA2011"
url = "jdbc:mysql://localhost:3306/grails"
}
hibernate {
cache.use_second_level_cache = true
cache.use_query_cache = false
cache.region.factory_class = 'net.sf.ehcache.hibernate.EhCacheRegionFactory'
}
// environment specific settings
environments {
development {
dataSource {
dbCreate = "create-drop" // one of 'create', 'create-drop', 'update', 'validate', ''
}
}
test {
dataSource {
dbCreate = "update"
}
}
production {
dataSource {
dbCreate = "update"
pooled = true
properties {
maxActive = -1
minEvictableIdleTimeMillis=1800000
timeBetweenEvictionRunsMillis=1800000
numTestsPerEvictionRun=3
testOnBorrow=true
testWhileIdle=true
testOnReturn=true
validationQuery="SELECT 1"
}
}
}
}
為了要demo GORM簡單的query語法，最好新增資料的時機就是Goovlet初始化的時候，故environment我選擇production，權限最大，然後做一個判斷，如果資料庫裏面沒有任何Post則呼叫generateSimpleDate，寫入一些資料，裡面包含新增User/Post/Tag，程式碼如下:
import com.JasonMicroBlog.*
class BootStrap {
def init = { servletContext ->
environments{
production{
if(!Post.count()) generateSampleData()
}
}
}
def destroy = {
}
private generateSampleData() {
def now = new Date()
def graeme = new User(
userId: "graeme",
password: "willow",
profile: new Profile(fullName: "Graeme Rocher", email: "graeme@nowhere.net"),
dateCreated: now).save(failOnError: true)
def jeff = new User(
userId: "jeff",
password: "sheldon",
profile: new Profile(fullName: "Jeff Brown", email: "jeff@nowhere.net"),
dateCreated: now).save(failOnError: true)
def burt = new User(
userId: "burt",
password: "mandible",
profile: new Profile(fullName: "Burt Beckwith", email: "burt@nowhere.net"),
dateCreated: now).save(failOnError: true)
def frankie = new User(
userId: "frankie",
password: "testing",
profile: new Profile(fullName: "Frankie Goes to Hollywood", email: "frankie@nowhere.net"),
dateCreated: now).save(failOnError: true)
def sara = new User(
userId: "sara",
password: "crikey",
profile: new Profile(fullName: "Sara Miles", email: "sara@nowhere.net"),
dateCreated: now - 2).save(failOnError: true)
def phil = new User(
userId: "phil",
password: "thomas",
profile: new Profile(fullName: "Phil Potts", email: "phil@nowhere.net"),
dateCreated: now)
def dillon = new User(userId: "dillon",
password: "crikey",
profile: new Profile(fullName: "Dillon Jessop", email: "dillon@nowhere.net"),
dateCreated: now - 2).save(failOnError: true)
phil.addToFollowing(frankie)
phil.addToFollowing(sara)
phil.save(failOnError: true)
phil.addToPosts(content: "Very first post")
phil.addToPosts(content: "Second post")
phil.addToPosts(content: "Time for a BBQ!")
phil.addToPosts(content: "Writing a very very long book")
phil.addToPosts(content: "Tap dancing")
phil.addToPosts(content: "Pilates is killing me")
phil.save(failOnError: true)
sara.addToPosts(content: "My first post")
sara.addToPosts(content: "Second post")
sara.addToPosts(content: "Time for a BBQ!")
sara.addToPosts(content: "Writing a very very long book")
sara.addToPosts(content: "Tap dancing")
sara.addToPosts(content: "Pilates is killing me")
sara.save(failOnError: true)
dillon.addToPosts(content: "Pilates is killing me as well")
dillon.save(failOnError: true, flush: true)
def postsAsList = phil.posts as List
postsAsList[0].addToTags(user: phil, name: "groovy")
postsAsList[0].addToTags(user: phil, name: "grails")
postsAsList[0].dateCreated = now.updated(year: 2004, month: MAY)
postsAsList[1].addToTags(user: phil, name: "grails")
postsAsList[1].addToTags(user: phil, name: "ramblings")
postsAsList[1].addToTags(user: phil, name: "second")
postsAsList[1].dateCreated = now.updated(year: 2007, month: FEBRUARY, date: 13)
postsAsList[2].addToTags(user: phil, name: "groovy")
postsAsList[2].addToTags(user: phil, name: "bbq")
postsAsList[2].dateCreated = now.updated(year: 2009, month: OCTOBER)
postsAsList[3].addToTags(user: phil, name: "groovy")
postsAsList[3].dateCreated = now.updated(year: 2011, month: MAY, date: 1)
postsAsList[4].dateCreated = now.updated(year: 2011, month: DECEMBER, date: 4)
postsAsList[5].dateCreated = now.updated(year: 2012, date: 10)
phil.save(failOnError: true)
postsAsList = sara.posts as List
postsAsList[0].dateCreated = now.updated(year: 2007, month: MAY)
postsAsList[1].dateCreated = now.updated(year: 2008, month: APRIL, date: 13)
postsAsList[2].dateCreated = now.updated(year: 2008, month: APRIL, date: 24)
postsAsList[3].dateCreated = now.updated(year: 2011, month: NOVEMBER, date: 8)
postsAsList[4].dateCreated = now.updated(year: 2011, month: DECEMBER, date: 4)
postsAsList[5].dateCreated = now.updated(year: 2012, month: AUGUST, date: 1)
sara.dateCreated = now - 2
sara.save(failOnError: true)
dillon.dateCreated = now - 2
dillon.save(failOnError: true, flush: true)
}
}
對於上面資料的新增，test-app結果資料沒有寫進資料庫，詳細原因不清楚，debug一段時間後放棄了，直接po文在作者的討論區，看有沒有答案，故先用scaffolding出的的網頁自行新增資料
再來介紹基本的where語法
(domain class Name).where{判斷式(||, &&, >=...}.方法(get(), list())
範例就用一個Integration test介紹，程式碼如下
import grails.plugin.spock.IntegrationSpec
class QueryIntegrationTestSpec extends IntegrationSpec {
def setup() {
}
def cleanup() {
}
void "Simple Query"() {
when:"Specific userId is used as criterion"
def user=User.where {userId =="Jason"}.get()
/*注意domain class的屬性要放在左邊當作判斷式，
*"frankie == userId"則結果會是always true
*single record使用get()
*/
then:"The only one record"
user.userId == "Jason"
}
void "Multiple Criteria"(){
when: "More than one constraints are applied"
def users=User.where{
userId == "Jason" || password=="testing"
}.list(sort:"userId", order: "desc")
/*恰好有兩位的密碼都是crikey，||代表or、&&代表and
* 多筆記錄用list方法，跟上面的get()和在一起，
* 有點Hibernate的影子
*/
then:"Multiple records can be expected"
users*.userId==["Jason", "Bob","Andrew"]
}
void "Query on a certain range"(){
given: "Find out records on the specific range of date"
def now = new Date()
when: "Users are created on these dates"
def users=User.where{
dateCreated in (now -1)..now
// 跟在for迴圈語法相同，in 代表某一區間
}.list(sort:"userId", order: "desc")
//可指定排序asc or desc
then: "Users are retrived from database order by Id"
users*.userId == ["Bob", "Anthony", "Andrew"]
}
}
Integration Test Result:
| Loading Grails 2.2.3
| Configuring classpath.
| Environment set to test.....
| Packaging Grails application.....
| Packaging Grails application.....
| Compiling 1 source files...
| Running 3 spock tests... 1 of 3
| Running 3 spock tests... 2 of 3
| Running 3 spock tests... 3 of 3
| Completed 3 spock tests, 0 failed in 468ms
| Tests PASSED - view reports in D:\groovy\JasonMicroBlog\target\test-reports
從以上不難看出where()就是hibernate中的query()稍加變形而已，當然更複雜的查詢語法，Grails也支援直接下HQL，今天未介紹完的明天再繼續囉
iThome鐵人賽
看影片追技術