软糖

GORM Query

Posted on |

Simple Query

1
2
3
def users = User.where {
  password == "testing" || child.password == "fake"
}.list(sort: "loginId", order: "desc")

Operations could be used in query

  • !=, <, >, <=, >=
  • ==~, =~ (case insensitive)
  • !, &&, ||

Seperate lines are implicitly And together

1
2
3
4
5
6
7
def users = User.where {
  loginId =~"%${loginIdPart}%"
  if (fromDate) {
    dateCreated >= fromDate
    }
  }.list()
}

List() supports Named Arguments

  • max
  • offset
  • sort
  • order
  • ignoreCase
  • fetch
1
2
3
4
5
def users = User.list(sort: 'loginId',
            order: 'desc',
            max: 5,
            offset: 10,
            fetch: [posts: 'eager'])

Magic find and count

findBy() and findAllBy()

1
def users = User.findAllByLoginIdIlike("%${loginId}%")

count()

1
def poorPasswordCount = User.countByPassword("password")

Criteria Query

and() and or()

1
2
3
4
5
6
7
8
9
def fetchUsers(String loginIdPart, Date fromDate = null) {
  def users = User.withCriteria {
    and {
      ilike 'loginId', "%${loginIdPart}%"
      if (fromDate) {
        ge "dateCreated", fromDate
      }
  }
}

TODO: insert Query criteria mappings table

Dynamic queries with criteria

1
2
3
4
5
6
7
8
9
10
11
12
13
def advResults() {
  def profileProps = Profile.metaClass.properties*.name
  def profiles = Profile.withCriteria {
    "${params.queryType}" {
      params.each { field, value ->
        if (profileProps.contains(field) && value) {
          ilike field, "%${value}%"
        }
      }
    }
  }
  return [profiles: profiles]
}

Report-style query projections

1
2
3
4
5
6
7
8
9
def tagList = Post.withCriteria {
  createAlias "tags", "t"
  user { eq "loginId", "phil" }
  
  projections {
    groupProperty "t.name"
    count "t.id"
  }
}

Using HQL directly

  • find()
  • findAll()
  • executeQuery()
  • executeUpdate()
1
User.findAll("from User u where u.userId = ?", ["joe"] )