com.dell.asm.asmcore.asmmanager.db.DeviceGroupDAO.java Source code

Java tutorial

Introduction

Here is the source code for com.dell.asm.asmcore.asmmanager.db.DeviceGroupDAO.java

Source

/**************************************************************************
 *   Copyright (c) 2013 Dell Inc. All rights reserved.                    *
 *                                                                        *
 * DELL INC. CONFIDENTIAL AND PROPRIETARY INFORMATION. This software may  *
 * only be supplied under the terms of a license agreement or             *
 * nondisclosure agreement with Dell Inc. and may not be copied or        *
 * disclosed except in accordance with the terms of such agreement.       *
 **************************************************************************/
package com.dell.asm.asmcore.asmmanager.db;

import com.dell.asm.rest.common.AsmConstants;
import com.dell.asm.usermanager.DBInit;
import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.log4j.Logger;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.criterion.Criterion;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.hibernate.exception.ConstraintViolationException;

import com.dell.asm.asmcore.asmmanager.AsmManagerMessages;
import com.dell.asm.asmcore.asmmanager.client.deviceinventory.DeviceState;
import com.dell.asm.asmcore.asmmanager.client.discovery.DeviceType;
import com.dell.asm.asmcore.asmmanager.client.servicetemplate.ServiceTemplateSettingIDs;
import com.dell.asm.asmcore.asmmanager.db.entity.DeviceGroupEntity;
import com.dell.asm.asmcore.asmmanager.db.entity.DeviceInventoryEntity;
import com.dell.asm.asmcore.asmmanager.exception.AsmManagerCheckedException;
import com.dell.asm.asmcore.asmmanager.exception.AsmManagerInternalErrorException;
import com.dell.asm.asmcore.asmmanager.exception.AsmManagerRuntimeException;
import com.dell.asm.rest.common.util.FilterParamParser;
import com.dell.asm.rest.common.util.PaginationParamParser;
import com.dell.asm.rest.common.util.SortParamParser;
import com.dell.asm.rest.common.util.StringUtils;
import com.google.common.base.Joiner;

public class DeviceGroupDAO {
    // Logger
    private static final Logger logger = Logger.getLogger(DeviceGroupDAO.class);

    // DB access
    private final BaseDAO _dao = BaseDAO.getInstance();

    // Singleton instance
    private static DeviceGroupDAO instance;

    private static final String GLOBAL_POOL_QUERY;
    private static final String SPECIFIC_POOL_QUERY;
    private static final String SPECIFIC_POOL_SYSTEM_USER_QUERY;
    private static final String POOL_GROUP_BY_CLAUSE;
    private static final String POOL_ORDER_BY_CLAUSE;
    private static final String POOL_SERVERS_COUNT;

    static {
        List<DeviceType> serverTypes = DeviceType.getAllServers();
        List<String> serverTypeNames = new ArrayList<>(serverTypes.size());
        for (DeviceType serverType : serverTypes) {
            // NOTE: serverTypeNames are enum names so they cannot contain dangerous sql characters
            // that could result in sql injection attacks.
            serverTypeNames.add("'" + serverType.name() + "'");
        }

        GLOBAL_POOL_QUERY = "SELECT ref_id, service_tag, i.state FROM "
                + "device_inventory AS i LEFT JOIN groups_device_inventory AS gd ON i.ref_id = "
                + "gd.devices_inventory_seq_id WHERE gd.groups_seq_id IS NULL AND device_type IN " + "("
                + Joiner.on(", ").join(serverTypeNames) + ")";

        SPECIFIC_POOL_QUERY = "SELECT ref_id, service_tag, i.state FROM device_inventory "
                + "AS i JOIN groups_device_inventory AS gd ON i.ref_id = "
                + "gd.devices_inventory_seq_id JOIN groups AS g ON gd.groups_seq_id = g.seq_id JOIN "
                + "groups_users AS u ON g.seq_id = u.groups_seq_id AND u.user_seq_id = :userId"
                + " WHERE device_type IN (" + Joiner.on(", ").join(serverTypeNames) + ")";

        // no user check for SYSTEM user - request comes from asm-deployer, return all servers
        SPECIFIC_POOL_SYSTEM_USER_QUERY = "SELECT ref_id, service_tag, i.state FROM device_inventory "
                + "AS i JOIN groups_device_inventory AS gd ON i.ref_id = "
                + "gd.devices_inventory_seq_id JOIN groups AS g ON gd.groups_seq_id = g.seq_id "
                + " WHERE device_type IN (" + Joiner.on(", ").join(serverTypeNames) + ")";

        POOL_GROUP_BY_CLAUSE = " GROUP BY ref_id, service_tag, i.state";
        POOL_ORDER_BY_CLAUSE = " ORDER BY service_tag";
        POOL_SERVERS_COUNT = "SELECT Count(*) FROM device_inventory, groups_device_inventory, groups "
                + "WHERE device_inventory.ref_id = groups_device_inventory.devices_inventory_seq_id "
                + "AND device_inventory.device_type IN (" + Joiner.on(", ").join(serverTypeNames) + ") "
                + "AND groups_device_inventory.groups_seq_id = groups.seq_id " + "AND groups.seq_id = :poolId";
    }

    private DeviceGroupDAO() {
    }

    // Return the single instance
    public static synchronized DeviceGroupDAO getInstance() {
        if (instance == null)
            instance = new DeviceGroupDAO();
        return instance;
    }

    /**
     * Create Device Group 
     * 
     * @param entity - device group entity
     * 
     * @return entity - device group entity
     *
     * @throws AsmManagerCheckedException
     */
    public DeviceGroupEntity createGroupDevice(DeviceGroupEntity entity) throws AsmManagerCheckedException {

        Session session = null;
        Transaction tx = null;
        DeviceGroupEntity duplicateEntity = null;

        try {
            duplicateEntity = getDeviceGroupByName(entity.getName());
        } catch (AsmManagerCheckedException ex) {
            logger.warn("Device Group with same name doesnt exists: " + ex);
        }

        if (null != duplicateEntity) {
            throw new AsmManagerCheckedException(AsmManagerCheckedException.REASON_CODE.DUPLICATE_RECORD,
                    AsmManagerMessages.duplicateDeviceGroupName(entity.getName()));
        }

        // Save the device in the db.
        try {
            session = _dao._database.getNewSession();
            tx = session.beginTransaction();

            logger.info("Creating device group in inventory: ");
            entity.setCreatedDate(new GregorianCalendar());
            entity.setUpdatedDate(new GregorianCalendar());
            entity.setCreatedBy(_dao.extractUserFromRequest());
            entity.setUpdatedBy(_dao.extractUserFromRequest());

            if (null == entity.getDeviceInventories())
                entity.setDeviceInventories(new ArrayList<DeviceInventoryEntity>());

            if (null == entity.getGroupsUsers())
                entity.setGroupsUsers(new HashSet<Long>());

            session.save(entity);

            // Commit transaction.
            tx.commit();
        } catch (ConstraintViolationException cve) {
            logger.warn("Caught exception during device group creation: " + cve);
            try {
                if (tx != null) {
                    tx.rollback();
                }
            } catch (Exception ex) {
                logger.warn("Unable to rollback transaction during create device group: " + ex);
            }
            if (cve.getConstraintName().contains("seqId")) {
                throw new AsmManagerCheckedException(AsmManagerCheckedException.REASON_CODE.DUPLICATE_REFID,
                        AsmManagerMessages.duplicateRefId(cve.getSQLException().getMessage()));
            } else {
                throw new AsmManagerCheckedException(AsmManagerCheckedException.REASON_CODE.DUPLICATE_RECORD,
                        AsmManagerMessages.duplicateRecord(cve.getSQLException().getMessage()));
            }
        } catch (Exception e) {
            logger.warn("Caught exception during device group inventory creation: " + e);
            try {
                if (tx != null) {
                    tx.rollback();
                }
            } catch (Exception ex) {
                logger.warn("Unable to rollback transaction during create device group: " + ex);
            }
            throw new AsmManagerInternalErrorException("Create device group", "DeviceGroupDAO", e);
        } finally {
            try {
                if (session != null) {
                    session.close();
                }
            } catch (Exception ex) {
                logger.warn("Unable to close session during create device group: " + ex);
            }
        }

        return entity;
    }

    /**
     * Retrieve Device Group by its Id
     * 
     * @param seqId - device group id
     * 
     * @return entity - device group entity
     *   
     * @throws AsmManagerCheckedException
     * @throws AsmManagerInternalErrorException
     */
    public DeviceGroupEntity getDeviceGroupById(Long seqId) throws AsmManagerCheckedException {
        Session session = null;
        Transaction tx = null;
        DeviceGroupEntity result = null;
        try {
            session = _dao._database.getNewSession();
            tx = session.beginTransaction();
            Criteria createCriteria = session.createCriteria(DeviceGroupEntity.class);
            Criterion idRest = Restrictions.eq("seqId", seqId);
            createCriteria.add(idRest);
            result = (DeviceGroupEntity) createCriteria.uniqueResult();

            // Commit transaction.
            tx.commit();
        } catch (Exception e) {
            try {
                if (tx != null) {
                    tx.rollback();
                }
            } catch (Exception ex) {
                logger.warn("Unable to rollback transaction during get Device Group: " + ex);
            }
            throw new AsmManagerInternalErrorException("fetch Device Group", "DeviceGroupDAO", e);
        } finally {
            if (session != null) {
                session.close();
            }
        }
        if (result == null) {
            throw new AsmManagerCheckedException(AsmManagerCheckedException.REASON_CODE.RECORD_NOT_FOUND,
                    AsmManagerMessages.deviceGroupNotFound(String.valueOf(seqId)));
        }
        return result;

    }

    /**
     * Retrieve Device Group by its name
     * 
     * @param name - device group name
     * 
     * @return entity - device group entity
     *
     * @throws AsmManagerCheckedException
     * @throws AsmManagerInternalErrorException
     */
    public DeviceGroupEntity getDeviceGroupByName(String name) throws AsmManagerCheckedException {
        Session session = null;
        Transaction tx = null;
        DeviceGroupEntity result = null;
        try {
            session = _dao._database.getNewSession();
            tx = session.beginTransaction();
            Criteria createCriteria = session.createCriteria(DeviceGroupEntity.class);
            Criterion idRest = Restrictions.eq("name", name);
            createCriteria.add(idRest);
            result = (DeviceGroupEntity) createCriteria.uniqueResult();

            // Commit transaction.
            tx.commit();
        } catch (Exception e) {
            try {
                if (tx != null) {
                    tx.rollback();
                }
            } catch (Exception ex) {
                logger.warn("Unable to rollback transaction during get Device Group: " + ex);
            }
            throw new AsmManagerInternalErrorException("fetch Device Group", "DeviceGroupDAO", e);
        } finally {
            if (session != null) {
                session.close();
            }
        }
        if (result == null) {
            throw new AsmManagerCheckedException(AsmManagerCheckedException.REASON_CODE.RECORD_NOT_FOUND,
                    AsmManagerMessages.deviceGroupNotFound(name));
        }
        return result;

    }

    /**
     * Update Device Group
     * 
     * @return updateEntity - updated device group entity
     * 
     * @throws AsmManagerCheckedException
     */
    public DeviceGroupEntity updateGroupDevice(DeviceGroupEntity updateEntity) throws AsmManagerCheckedException {

        if (updateEntity == null) {
            return null;
        }
        // Initialize locals.
        Session session = null;
        Transaction tx = null;
        DeviceGroupEntity entity = null;

        // Save the job history in the db.
        try {
            session = _dao._database.getNewSession();
            tx = session.beginTransaction();

            // Save the new DeviceGroupEntity.
            String hql = "from DeviceGroupEntity where seqId = :id";
            Query query = session.createQuery(hql);
            query.setLong("id", updateEntity.getSeqId());
            entity = (DeviceGroupEntity) query.setMaxResults(1).uniqueResult();

            if (null != entity) {

                if (null != updateEntity.getName() || !"".equals(updateEntity.getName()))
                    entity.setName(updateEntity.getName());

                if (null != updateEntity.getDescription())
                    entity.setDescription(updateEntity.getDescription());

                entity.setUpdatedBy(_dao.extractUserFromRequest());
                entity.setUpdatedDate(new GregorianCalendar());

                if (null != updateEntity.getDeviceInventories())
                    entity.setDeviceInventories(updateEntity.getDeviceInventories());

                if (null != updateEntity.getGroupsUsers())
                    entity.setGroupsUsers(updateEntity.getGroupsUsers());

                session.saveOrUpdate(entity);

                // Commit transaction and clean up.
                tx.commit();
            } else {
                throw new AsmManagerCheckedException(AsmManagerCheckedException.REASON_CODE.INVALID_REQUEST,
                        AsmManagerMessages.updateDeviceGroupError(String.valueOf(updateEntity.getSeqId())));
            }

        } catch (Exception e) {
            logger.warn("Caught exception during updating device group: " + e);
            try {
                if (tx != null) {
                    tx.rollback();
                }
            } catch (Exception ex) {
                logger.warn("Unable to rollback transaction during updating device group: " + ex);
            }
            throw new AsmManagerCheckedException(AsmManagerCheckedException.REASON_CODE.INVALID_REQUEST,
                    AsmManagerMessages.updateDeviceGroupError(String.valueOf(updateEntity.getSeqId())));
        } finally {
            try {
                if (session != null) {
                    session.close();
                }
            } catch (Exception ex) {
                logger.warn("Unable to close session during updating device group: " + ex);
            }
        }
        return entity;
    }

    /**
     * Helper method to get total number of records with filter parameters
     * 
     * @param filterInfos
     *            - List for holding filtering information parsed from filter query parameter value
     * @return int - total number of records
     */
    public Integer getTotalRecords(List<FilterParamParser.FilterInfo> filterInfos) {
        long totalRecords = 0;
        Session session = null;
        Transaction tx = null;
        try {
            session = _dao._database.getNewSession();
            tx = session.beginTransaction();

            Criteria criteria = session.createCriteria(DeviceGroupEntity.class);
            if (filterInfos != null) {
                addFilterCriteria(criteria, filterInfos, DeviceGroupEntity.class);
            }
            totalRecords = (long) criteria.setProjection(Projections.rowCount()).uniqueResult();

            tx.commit();
        } catch (Exception e) {
            if (tx != null)
                try {
                    tx.rollback();
                } catch (Exception e2) {
                    logger.warn("Error during rollback", e2);
                }
        } finally {
            try {
                if (session != null)
                    session.close();
            } catch (Exception e2) {
                logger.warn("Error during session close", e2);
            }
        }

        return (int) totalRecords;
    }

    /**
     * Retrieve all Device Groups
     * 
     * @param sortInfos - column name(s) on which the sorting is done
     * @param filterInfos - column name(s) on which the filtering is done
     * @param paginationInfo - pagination information (offset and limit)
     * 
     * @return all the device groups (paginated list)
     * 
     * @throws AsmManagerCheckedException
     */
    @SuppressWarnings("unchecked")
    public List<DeviceGroupEntity> getAllDeviceGroup(List<SortParamParser.SortInfo> sortInfos,
            List<FilterParamParser.FilterInfo> filterInfos, PaginationParamParser.PaginationInfo paginationInfo)
            throws AsmManagerCheckedException {

        Session session = null;
        Transaction tx = null;
        List<DeviceGroupEntity> result = null;

        try {
            session = _dao._database.getNewSession();
            tx = session.beginTransaction();
            Criteria criteria = session.createCriteria(DeviceGroupEntity.class);

            if (paginationInfo != null) {
                int offset = paginationInfo.getOffset();
                int pageSize = paginationInfo.getLimit();
                criteria.setFirstResult(offset);
                criteria.setMaxResults(pageSize);
            }

            if (sortInfos != null) {
                addSortCriteria(criteria, sortInfos);
            }

            if (filterInfos != null) {
                addFilterCriteria(criteria, filterInfos, DeviceGroupEntity.class);
            }

            result = (List<DeviceGroupEntity>) criteria.list();
            tx.commit();

        } catch (Exception e) {
            logger.warn("Caught exception during getAllDeviceGroup: " + e);
            try {
                if (tx != null) {
                    tx.rollback();
                }
            } catch (Exception ex) {
                logger.warn("Unable to rollback transaction during getAllDeviceGroup: " + ex);
            }
            throw new AsmManagerInternalErrorException("fetch Device Group", "DeviceGroupDAO", e);
        } finally {
            try {
                if (session != null) {
                    session.close();
                }
            } catch (Exception ex) {
                logger.warn("Unable to close session during getAllDeviceGroup: " + ex);
            }
        }

        //if (result == null || result.size() == 0) {
        //    throw new AsmManagerCheckedException(AsmManagerCheckedException.REASON_CODE.RECORD_NOT_FOUND,
        //            AsmManagerMessages.deviceGroupsDataNotFound(""));
        //}

        return result;
    }

    /**
     * Helper method for adding sorting criteria
     * 
     * @param criteria 
     *            - the query criteria
     * @param sortInfos 
     *            - list for sort specifications. Sort info are added in the order in the list
     */
    private void addSortCriteria(Criteria criteria, List<SortParamParser.SortInfo> sortInfos) {
        for (SortParamParser.SortInfo sortInfo : sortInfos) {
            if (SortParamParser.SortOrder.DESC.equals(sortInfo.getSortOrder())) {
                criteria.addOrder(Order.desc(sortInfo.getColumnName()));
            } else {
                criteria.addOrder(Order.asc(sortInfo.getColumnName()));
            }
        }
    }

    /**
     * Helper method for adding filter criteria
     * 
     * @param criteria
     *            - the filter criteria
     * @param filterInfos
     *            - list for filter specifications. filter info are added in the order in the list
     *            
     * @return List<FilterParamParser.FilterInfo>
     *                             - list of added filters
     * 
     * @throws IllegalArgumentException
     */
    @SuppressWarnings("unchecked")
    private List<FilterParamParser.FilterInfo> addFilterCriteria(Criteria criteria,
            List<FilterParamParser.FilterInfo> filterInfos, Class persistentClass) {

        LinkedList<FilterParamParser.FilterInfo> notFound = new LinkedList<>();

        for (FilterParamParser.FilterInfo filterInfo : filterInfos) {
            String columnName = filterInfo.getColumnName();
            List<?> values = filterInfo.getColumnValue();

            //
            // Cast strings to the property type by scanning the persistent class
            //
            Class<?> typeClass = null;
            // try non-boolean-type naming.
            try {
                typeClass = persistentClass.getMethod("get" + StringUtils.capitalize(columnName)).getReturnType();
            } catch (NoSuchMethodException e) {
                logger.info("cannot find a method for " + columnName + " in " + persistentClass.toString());

            }
            // try boolean-type naming.
            if (typeClass == null) {
                try {
                    typeClass = persistentClass.getMethod("is" + StringUtils.capitalize(columnName))
                            .getReturnType();
                } catch (NoSuchMethodException e) {
                    logger.info("cannot find a method for " + columnName + " in " + persistentClass.toString());
                }
            }
            // property not found. skip.
            if (typeClass == null) {
                notFound.add(filterInfo);
                continue;
            }
            if (!typeClass.isAssignableFrom(String.class)) {

                // byte/short/int/long
                if (typeClass == byte.class || typeClass == short.class || typeClass == int.class
                        || typeClass == long.class) {
                    LinkedList<Long> castedValues = new LinkedList<>();
                    for (String stringValue : filterInfo.getColumnValue()) {
                        castedValues.add(Long.valueOf(stringValue));
                    }
                    // Set casted values
                    values = castedValues;

                    // float/double
                } else if (typeClass == float.class || typeClass == double.class) {
                    LinkedList<Double> castedValues = new LinkedList<>();
                    for (String stringValue : filterInfo.getColumnValue()) {
                        castedValues.add(Double.valueOf(stringValue));
                    }
                    // Set casted values
                    values = castedValues;
                    // boolean
                } else if (typeClass == boolean.class) {
                    LinkedList<Boolean> castedValues = new LinkedList<>();
                    for (String stringValue : filterInfo.getColumnValue()) {
                        castedValues.add(Boolean.valueOf(stringValue));
                    }
                    // Set casted values
                    values = castedValues;
                    // char
                } else if (typeClass == char.class) {
                    LinkedList<Character> castedValues = new LinkedList<>();
                    for (String stringValue : filterInfo.getColumnValue()) {
                        castedValues.add(Character.valueOf(stringValue.charAt(0)));
                    }
                    // Set casted values
                    values = castedValues;
                }
            }

            //
            // Translate filters to Hibernate Criteria
            //
            if (values.size() > 1) {
                if (FilterParamParser.FilterOperator.EQUAL.equals(filterInfo.getFilterOperator())) {
                    criteria.add(Restrictions.in(columnName, values));
                } else {
                    throw new IllegalArgumentException(
                            "filter operation '" + filterInfo.getFilterOperator() + "' is not recognized.");
                }
            } else if (values.size() == 1) {
                if (FilterParamParser.FilterOperator.EQUAL.equals(filterInfo.getFilterOperator())) {
                    criteria.add(Restrictions.eq(columnName, values.get(0)));
                } else if (FilterParamParser.FilterOperator.CONTAIN.equals(filterInfo.getFilterOperator())) {

                    // Escape '_', '%', and '\' for Hibernate.
                    String escapedString = values.get(0).toString();
                    escapedString = escapedString.replace("\\", "\\\\").replace("_", "\\_").replace("%", "\\%");

                    criteria.add(Restrictions.like(columnName, escapedString, MatchMode.ANYWHERE));
                } else {
                    throw new IllegalArgumentException(
                            "filter operation '" + filterInfo.getFilterOperator() + "' is not recognized.");
                }
            }
        }

        return notFound;
    }

    /**
     * Delete Device Group
     * 
     * @param deviceGroupEntity - device group entity to be deleted
     * 
     * @throws AsmManagerInternalErrorException
     * 
     */
    public void deleteDeviceGroup(DeviceGroupEntity deviceGroupEntity) {
        logger.info("Deleting Device Group from inventory: " + deviceGroupEntity.getSeqId());
        Session session = null;
        Transaction tx = null;

        try {
            session = _dao._database.getNewSession();
            tx = session.beginTransaction();

            session.delete(deviceGroupEntity);

            tx.commit();
            session.close();
        } catch (Exception e) {
            logger.warn("Caught exception during delete Device Group with the id " + deviceGroupEntity.getSeqId()
                    + " : " + e);
            try {
                if (tx != null) {
                    tx.rollback();
                }
            } catch (Exception ex) {
                logger.warn("Unable to rollback transaction during delete Device Group with the id "
                        + deviceGroupEntity.getSeqId() + " : " + e);
            }
            throw new AsmManagerInternalErrorException("Delete Device Group", "DeviceGroupDAO", e);
        } finally {
            try {
                if (session != null) {
                    session.close();
                }
            } catch (Exception ex) {
                logger.warn("Unable to close session during delete Device Group with the id "
                        + deviceGroupEntity.getSeqId() + " : " + ex);
            }
        }
    }

    /**
     * Helper method for deleting Group Users Association
     * 
     * @param userSeqIds - user's id to be deleted
     * 
     * @throws AsmManagerCheckedException
     *
     */
    // If there is a change in groups_users table structure, then this method should be updated accordingly
    public void deleteGroupUsersAssociation(Set<Long> userSeqIds) throws AsmManagerCheckedException {

        if (userSeqIds == null || userSeqIds.size() <= 0)
            return;

        // Initialize locals.
        Session session = null;
        Transaction tx = null;
        List<BigInteger> userIds = new ArrayList<>();

        for (Long id : userSeqIds) {
            userIds.add(new BigInteger(String.valueOf(id)));
        }

        // Save the job history in the db.
        try {
            session = _dao._database.getNewSession();
            tx = session.beginTransaction();

            // delete the Group Users Association.
            String sql = "delete from groups_users WHERE user_seq_id IN (:userSeqIds)";
            Query query = session.createSQLQuery(sql);
            query.setParameterList("userSeqIds", userIds);
            query.executeUpdate();
            tx.commit();

        } catch (Exception e) {
            logger.warn("Caught exception during deleting Group Users association: " + e);
            try {
                if (tx != null) {
                    tx.rollback();
                }
            } catch (Exception ex) {
                logger.warn("Unable to rollback transaction during deleting Group Users association: " + ex);
            }

        } finally {
            try {
                if (session != null) {
                    session.close();
                }
            } catch (Exception ex) {
                logger.warn("Unable to close session during deleting Group Users association: " + ex);
            }
        }
    }

    public String getPoolName(final String poolId) {
        if (poolId == null) {
            throw new IllegalArgumentException("Pool must not be null");
        } else if (ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_GLOBAL_ID.equals(poolId)) {
            return "Global";
        } else if (ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_ALL_ID.equals(poolId)) {
            return "All Servers";
        } else {
            try {
                DeviceGroupEntity group = getDeviceGroupById(Long.valueOf(poolId));
                if (group == null) {
                    return "Unknown";
                } else {
                    return group.getName();
                }
            } catch (AsmManagerCheckedException e) {
                return "Unknown";
            }
        }
    }

    public static class BriefServerInfo {
        private final String refId;
        private final String serviceTag;
        private final DeviceState state;

        public BriefServerInfo(String refId, String serviceTag, DeviceState state) {
            this.refId = refId;
            this.serviceTag = serviceTag;
            this.state = state;
        }

        public String getRefId() {
            return refId;
        }

        public String getServiceTag() {
            return serviceTag;
        }

        public DeviceState getState() {
            return state;
        }

        @Override
        public String toString() {
            return new ToStringBuilder(this).append("refId", refId).append("serviceTag", serviceTag).toString();
        }
    }

    @SuppressWarnings("unchecked")
    private List<BriefServerInfo> buildBriefServerInfoList(List list) {
        List<BriefServerInfo> ret = new ArrayList<>(list.size());
        for (Object o : list) {
            Object[] row = (Object[]) o;
            String stateString = (String) row[2];
            DeviceState state = DeviceState.valueOf(stateString);
            ret.add(new BriefServerInfo((String) row[0], (String) row[1], state));
        }
        return ret;
    }

    public List<BriefServerInfo> getAccessiblePoolServers(final long userId, final String poolId) {
        try {
            return _dao.doWithSession(new BaseDAO.CallableWithSession<List<BriefServerInfo>>() {
                @Override
                public List<BriefServerInfo> run(Session session) {
                    Map<String, Object> keyValues = new HashMap<>();

                    SQLQuery query;
                    if (ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_GLOBAL_ID.equals(poolId)) {
                        query = session
                                .createSQLQuery(GLOBAL_POOL_QUERY + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                    } else if (ServiceTemplateSettingIDs.SERVICE_TEMPLATE_SERVER_POOL_ALL_ID.equals(poolId)
                            || poolId == null) {

                        // system user has access to all servers in the pool
                        if (userId == DBInit.SYSTEM_USER_ID) {
                            query = session
                                    .createSQLQuery(GLOBAL_POOL_QUERY + " UNION " + SPECIFIC_POOL_SYSTEM_USER_QUERY
                                            + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                        } else {
                            query = session.createSQLQuery(GLOBAL_POOL_QUERY + " UNION " + SPECIFIC_POOL_QUERY
                                    + POOL_GROUP_BY_CLAUSE + POOL_ORDER_BY_CLAUSE);
                            query.setParameter("userId", userId);
                        }
                    } else {
                        StringBuilder sql;
                        if (userId == DBInit.SYSTEM_USER_ID) {
                            sql = new StringBuilder(SPECIFIC_POOL_SYSTEM_USER_QUERY + " AND g.seq_id = :poolId");
                        } else {
                            sql = new StringBuilder(SPECIFIC_POOL_QUERY + " AND g.seq_id = :poolId");
                        }
                        keyValues.put("poolId", Long.valueOf(poolId));

                        sql.append(POOL_GROUP_BY_CLAUSE);
                        sql.append(POOL_ORDER_BY_CLAUSE);

                        query = session.createSQLQuery(sql.toString());
                        if (userId != DBInit.SYSTEM_USER_ID)
                            query.setParameter("userId", userId);
                    }

                    for (Map.Entry<String, Object> entry : keyValues.entrySet()) {
                        query.setParameter(entry.getKey(), entry.getValue());
                    }

                    return buildBriefServerInfoList(query.list());
                }

                @Override
                public List<BriefServerInfo> failed(SQLException e) throws SQLException {
                    logger.error("Failed to look up servers from pool " + poolId + " for user " + userId, e);
                    throw e;
                }
            });
        } catch (SQLException e) {
            throw new AsmManagerRuntimeException(e);
        }
    }

    public List<BriefServerInfo> getAccessibleServers(final long userId, final List<String> serverRefIds) {
        try {
            return _dao.doWithSession(new BaseDAO.CallableWithSession<List<BriefServerInfo>>() {
                private StringBuilder appendServerRefIds(StringBuilder sql, Map<String, Object> keyValues) {
                    if (serverRefIds != null && serverRefIds.size() > 0) {
                        int i = 0;
                        sql.append(" AND i.ref_id IN (");
                        for (String serverRefId : serverRefIds) {
                            String name = "ref" + i++;
                            sql.append(':').append(name).append(", ");
                            keyValues.put(name, serverRefId);
                        }
                        sql.deleteCharAt(sql.length() - 1);
                        sql.deleteCharAt(sql.length() - 1);
                        sql.append(")");
                    }
                    return sql;
                }

                @Override
                public List<BriefServerInfo> run(Session session) {
                    Map<String, Object> keyValues = new HashMap<>();

                    StringBuilder sql = new StringBuilder(GLOBAL_POOL_QUERY);
                    if (userId == DBInit.SYSTEM_USER_ID) {
                        appendServerRefIds(sql, keyValues).append(" UNION ")
                                .append(SPECIFIC_POOL_SYSTEM_USER_QUERY);
                    } else {
                        appendServerRefIds(sql, keyValues).append(" UNION ").append(SPECIFIC_POOL_QUERY);
                    }
                    appendServerRefIds(sql, keyValues).append(POOL_GROUP_BY_CLAUSE).append(POOL_ORDER_BY_CLAUSE);
                    SQLQuery query = session.createSQLQuery(sql.toString());

                    if (userId != DBInit.SYSTEM_USER_ID)
                        query.setParameter("userId", userId);

                    for (Map.Entry<String, Object> entry : keyValues.entrySet()) {
                        query.setParameter(entry.getKey(), entry.getValue());
                    }

                    return buildBriefServerInfoList(query.list());
                }

                @Override
                public List<BriefServerInfo> failed(SQLException e) throws SQLException {
                    logger.error("Failed to look up servers " + serverRefIds + " for user " + userId, e);
                    throw e;
                }
            });
        } catch (SQLException e) {
            throw new AsmManagerRuntimeException(e);
        }
    }

    /**
     * Returns the number of servers available in the pool.
     * 
     * @param poolId the id of the pool whose server count will be returned.
     * @return the number of servers available in the pool.
     */
    public int getNumberOfServersInPool(final String poolId) {
        int numberOfServersInPool = 0;
        if (poolId != null && poolId.trim().length() > 0) {
            // Initialize locals.
            Session session = null;
            Transaction tx = null;
            try {
                session = _dao._database.getNewSession();
                tx = session.beginTransaction();
                SQLQuery query = session.createSQLQuery(POOL_SERVERS_COUNT);
                query.setParameter("poolId", Long.valueOf(poolId));
                numberOfServersInPool = ((BigInteger) query.uniqueResult()).intValue();
                tx.commit();
            } catch (Exception e) {
                logger.warn("Caught exception during getNumberOfServersInPool: " + e);
                try {
                    if (tx != null) {
                        tx.rollback();
                    }
                } catch (Exception ex) {
                    logger.warn("Unable to rollback transaction during getNumberOfServersInPool: " + ex);
                }
            } finally {
                try {
                    if (session != null) {
                        session.close();
                    }
                } catch (Exception ex) {
                    logger.warn("Unable to close session during getNumberOfServersInPool: " + ex);
                }
            }
        }
        return numberOfServersInPool;
    }
}