Table of contents
  1. HQL: Hibernate Query Language
    1. Select distinct
      1. may equivalently be expressed as follows, using the IN operator:
  2. Find All
    1. With Pagination
    2. Execute Query
    3. With Using Session
      1. Full Example - Full dynamic HQL, with QueryImpl object
  3. Groovy SQL




HQL: Hibernate Query Language

Select distinct

SELECT DISTINCT mag FROM Magazine mag
JOIN mag.articles art
JOIN art.author auth
WHERE auth.lastName = 'Grisham'

may equivalently be expressed as follows, using the IN operator:

SELECT DISTINCT mag FROM Magazine mag,
IN(mag.articles) art
WHERE art.author.lastName = 'Grisham'

Find All

With Pagination

set up paginate

 def getPaginatedUserGroups(def requestedTypes = [UserGroupType.RESULTGROUP], userId, clientSetupId, paginationParams) {
    if (!userId || !clientSetupId || !paginationParams) return null

    def totalUserGroups = Group.countByUserIdAndClientSetupIdAndTypeInList(userId, clientSetupId, requestedTypes, [cache: true])
    def orderByMap = [groupName: 'ug.name', assessmentName: 'im.name', lastUpdated: 'ug.lastUpdated']
    def orderBy = orderByMap.get(paginationParams?.orderBy) ?: 'ug.name'
    def orderByDirection = ['asc', 'desc'].contains(paginationParams?.orderDirection) ? paginationParams?.orderDirection : 'asc'
    def max = paginationParams?.pageSize ?: 5
    def offset = ((paginationParams?.page ?: 1) - 1) * max

    log.debug "Paged UserGroup List for User ${userId} offset: $offset, max: $max, total: $totalUserGroups"

    def groupsList = Group.getGroupsByUser(requestedTypes, userId, clientSetupId, offset, max, orderBy, orderByDirection)

    [groupsList: groupsList, offset: offset, max: max, total: totalUserGroups]
}

query

 static List getGroupsByUser(Long userId, Long clientSetupId, def offset = 0, def max = 0, String orderBy = 'ug.name', String orderByDirection = 'asc') {
    def query = """
            select new Map(ug.id as id, ug.name as name, ug.interviewModelId as interviewModelId,
            im.name as assessmentName,  im.sourceId as sourceId, im.source as source, im.subType as subType,
             ug.visibility as visibility , ug.lastUpdated as lastUpdated , COUNT(ugao.id) as assessmentCount,
             ug.type as type,
             CAST(case when (select count(*) from UserGroupShare as ugs where ug.id = ugs.userGroupId and ugs.revoked = false) < 1 THEN 0 ELSE 1 END as java.lang.Boolean) as shared)
            from Group ug
                     LEFT JOIN UserGroupAssessmentOrder ugao ON ug.id = ugao.userGroupId
                     LEFT JOIN InterviewModel im ON im.id = ug.interviewModelId
            where ug.userId = :userId
              and ug.type in (:type)
              and ug.clientSetupId = :clientSetupId
            GROUP BY ug.id
            order by @orderBy@ @orderByDirection@"""

    def modifiedQuery = query.replaceAll('@orderBy@', orderBy).replaceAll('@orderByDirection@', orderByDirection)

    findAll(modifiedQuery, [userId: userId, type: UserGroupType.RESULTGROUP, clientSetupId:
            clientSetupId], [offset: offset, max: max, cache: true])
}

Execute Query

    def query = """
    select new Map(ug.id as id, ug.name as name, ug.interviewModelId as interviewModelId,
    ug.visibility as visibility, ug.lastUpdated as lastUpdated, COUNT(ugao.id) as assessmentCount )
    from UserGroup ug
    LEFT JOIN UserGroupAssessmentOrder ugao ON ug.id= ugao.userGroupId
    where ug.userId = :userId and ug.type = :type
    GROUP BY ug.id
    ORDER BY ug.name"""

def groups = UserGroup.executeQuery(query, [userId: principalUser?.id, type: UserGroupType.RESULTGROUP])

With Using Session

               User.withSession { uSession ->
    def q = uSession.createQuery($/SELECT DISTINCT new com.talentbank.core.UserMap(user.id,
               user.username, user.clientSetupId,
                              user.email, user.firstName, user.lastName, user.userMetadata, user.lastLogin,
              user.pictureUrl,manager.id, manager.email ,
                              manager.firstName ,manager.lastName ,manager.userMetadata, manager.lastLogin
                             ,manager.pictureUrl
                              ,manager.externalEmployeeCode)
                          FROM $User user
                          LEFT JOIN FETCH $UserRelationship ur on ur.user.id = user.id or ur.manager.id =
                            user.idleft
                          JOIN ur.manager manager
                          WHERE user.firstName like CONCAT('%', $searchString, '%')
                          or user.lastName like CONCAT('%', $searchString, '%')
                          or user.email like CONCAT('%', $searchString, '%')
                          or  manager.firstName like CONCAT('%', $searchString, '%')
                          or manager.lastName like CONCAT('%', $searchString, '%')
                         /$)

    q.maxResults = 8
    q.firstResult = 2
    q.list()
}

Full Example - Full dynamic HQL, with QueryImpl object

Groovy SQL

create groovy sql

DataSource dataSource = Holders.grailsApplication.mainContext.getBean('dataSource')
Sql groovySql = new Sql(dataSource)
List fetchUsersByNameOrManagerName(String searchString, params) {
    if (!params) return null
    def (firstNameSearch, lastNameSearch, rest) = searchString?.tokenize()

    String query = """SELECT DISTINCT user.id, user.username, user.client_setup_id, user.email, user.first_name, user.last_name,
      user.user_metadata,
      user.last_login, user.picture_url,
                                  manager.first_name AS mgr_first_name, manager.last_name AS mgr_last_name
                         FROM user
                               LEFT JOIN user_relationship as userRelationship on userRelationship.user_id = user.id
                               LEFT JOIN user manager on userRelationship.manager_id = manager.id
                             WHERE ((user.first_name LIKE '%${searchString}%' || user.last_name LIKE '%${searchString}%' || user.email LIKE
                         '%${searchString}%') ||
                                     (user.first_name LIKE '%${firstNameSearch}%' && user.last_name LIKE '%${lastNameSearch}%'))
                        UNION
                        SELECT DISTINCT user.id, user.username, user.client_setup_id, user.email, user.first_name, user.last_name,
                        user.user_metadata,
                        user.last_login, user.picture_url,
                              manager.first_name AS mgr_first_name, manager.last_name AS mgr_last_name
                        FROM user
                              LEFT JOIN user_relationship as userRelationship on userRelationship.user_id = user.id
                              LEFT JOIN user manager on userRelationship.manager_id = manager.id
                              WHERE ((manager.first_name LIKE '%${searchString}%' || manager.last_name LIKE '%${searchString}%') ||
                             (manager.first_name LIKE '%${firstNameSearch}%' && manager.last_name LIKE '%${lastNameSearch}%'))
                           """

    groovySql.rows(query, 0, 15)
}