grails 常用查询

 hql查询

1.查询记录个数

    String hql = "select count(1) from User where username like :username"
    long count = (Long) User.executeQuery(hql, [username: "%${username}%"]).get(0)

2.查询id集合

    def hql = "select id from User where username like :username"
    def ids = User.executeQuery(hql,  [username: "%${username}%"])

3.删除id集合

     def hql = "delete from User where id in (:ids)"
     User.executeUpdate(hql, [ids: ids])

4.查询全部记录通过集合

      def hql = "from User where id in (:ids)"
      def users = User.executeQuery(hql, [ids: ids])
      //def users = User.findAll(hql, [ids: ids])

5. not in 集合

       def hql = "select  id from User where id not in (select user.id from User where user.username like :username)"
       def ids = User.executeQuery(hql,  [username: "%${username}%"])

其他查询

 findAllBy 排序

def users = User.findAllByAge(age, [sort: “age”, order: “asc”])

findWhere

def user = User.findWhere([username: “admin”])