Example usage for org.hibernate SQLQuery setParameterList

List of usage examples for org.hibernate SQLQuery setParameterList

Introduction

In this page you can find the example usage for org.hibernate SQLQuery setParameterList.

Prototype

@Override
    NativeQuery<T> setParameterList(String name, Object[] values);

Source Link

Usage

From source file:org.generationcp.middleware.dao.GermplasmListDAO.java

License:Open Source License

/**
 * @param folderIds/*from  ww  w .  jav  a 2 s  . c  om*/
 *            a group of folder ids for which we want to return children
 * @return the resultant map which contains the folder meta data
 */
public Map<Integer, ListMetadata> getGermplasmFolderMetadata(final List<Integer> folderIds) {

    if (folderIds.isEmpty()) {
        return Collections.<Integer, ListMetadata>emptyMap();
    }

    final String folderMetaDataQuery = "SELECT parent.listid AS listId, COUNT(child.listid) AS numberOfChildren FROM listnms parent "
            + "LEFT OUTER JOIN listnms child ON child.lhierarchy = parent.listid "
            + "WHERE parent.listid IN (:folderIds) GROUP BY parent.listid";
    final SQLQuery setResultTransformer = this.getSession().createSQLQuery(folderMetaDataQuery);
    setResultTransformer.setParameterList("folderIds", folderIds);
    setResultTransformer.addScalar("listId", new IntegerType());
    setResultTransformer.addScalar("numberOfChildren", new IntegerType());
    setResultTransformer.setResultTransformer(Transformers.aliasToBean(ListMetadata.class));
    final List<ListMetadata> list = setResultTransformer.list();
    return Maps.uniqueIndex(list, new Function<ListMetadata, Integer>() {
        @Override
        public Integer apply(final ListMetadata folderMetaData) {
            return folderMetaData.getListId();
        }
    });
}

From source file:org.generationcp.middleware.dao.GermplasmListDAO.java

License:Open Source License

/**
 * Verify if the gids are used in more than one list
 *
 * @param gids/*  ww w.  ja  va  2 s  .com*/
 *            gids to check
 * @return Map with GID as key and CSV of list where it is used
 */
public Map<Integer, String> getGermplasmUsedInMoreThanOneList(final List<Integer> gids) {
    final Map<Integer, String> resultMap = new HashMap<>();

    final SQLQuery query = this.getSession()
            .createSQLQuery(GermplasmListDAO.GET_GERMPLASM_USED_IN_MORE_THAN_ONE_LIST);
    query.setParameterList("gids", gids);

    final List<Object[]> results = query.list();
    for (final Object[] result : results) {
        resultMap.put((Integer) result[0], (String) result[1]);
    }
    return resultMap;
}

From source file:org.generationcp.middleware.dao.ims.TransactionDAO.java

License:Open Source License

public List<InventoryDetails> getInventoryDetailsByTransactionRecordId(final List<Integer> recordIds) {
    final List<InventoryDetails> detailsList = new ArrayList<>();

    if (recordIds == null || recordIds.isEmpty()) {
        return detailsList;
    }// w  w  w .  j  a  v a  2  s  .  co  m

    try {
        final Session session = this.getSession();

        final StringBuilder sql = new StringBuilder()
                .append("SELECT lot.lotid, lot.userid, lot.eid, lot.locid, lot.scaleid, ")
                .append("tran.sourceid, tran.trnqty, tran.inventory_id, lot.comments, tran.recordid ")
                .append("FROM ims_transaction tran ").append("LEFT JOIN ims_lot lot ON lot.lotid = tran.lotid ")
                .append("WHERE lot.status = ").append(LotStatus.ACTIVE.getIntValue())
                .append("       AND tran.recordid IN (:recordIds) ");
        final SQLQuery query = session.createSQLQuery(sql.toString());
        query.setParameterList("recordIds", recordIds);

        final List<Object[]> results = query.list();

        if (!results.isEmpty()) {
            for (final Object[] row : results) {
                final Integer lotId = (Integer) row[0];
                final Integer userId = (Integer) row[1];
                final Integer gid = (Integer) row[2];
                final Integer locationId = (Integer) row[3];
                final Integer scaleId = (Integer) row[4];
                final Integer sourceId = (Integer) row[5];
                final Double amount = (Double) row[6];
                final String inventoryID = (String) row[7];
                final String comment = (String) row[8];
                final Integer sourceRecordId = (Integer) row[9];

                final InventoryDetails details = new InventoryDetails(gid, null, lotId, locationId, null,
                        userId, amount, sourceId, null, scaleId, null, comment);
                details.setInventoryID(inventoryID);
                details.setSourceRecordId(sourceRecordId);
                detailsList.add(details);
            }
        }

    } catch (final HibernateException e) {
        final String message = "Error with getInventoryDetailsByTransactionRecordId() query from Transaction: "
                + e.getMessage();
        LOG.error(message, e);
        throw new MiddlewareQueryException(message, e);
    }

    return detailsList;
}

From source file:org.generationcp.middleware.dao.LocationDAO.java

License:Open Source License

public List<LocationDto> getLocationDtoByIds(final Collection<Integer> ids) {
    final List<LocationDto> returnList = new ArrayList<>();
    if (ids == null || ids.isEmpty()) {
        return returnList;
    }//from  w ww  .  j a v a  2 s  .c om
    try {
        final String sql = "SELECT l.lname, prov.lname, c.isoabbr, l.locid" + " FROM location l"
                + " LEFT JOIN location prov ON prov.locid = l.snl1id"
                + " LEFT JOIN cntry c ON c.cntryid = l.cntryid" + " WHERE l.locid in (:ids)";
        final SQLQuery query = this.getSession().createSQLQuery(sql);
        query.setParameterList("ids", ids);
        final List<Object[]> results = query.list();

        if (results != null) {
            for (final Object[] result : results) {
                returnList.add(new LocationDto((Integer) result[3], (String) result[0], (String) result[1],
                        (String) result[2]));
            }
        }

    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocationDtoById", "id",
                ids.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
    }
    return returnList;
}

From source file:org.generationcp.middleware.dao.LocationDAO.java

License:Open Source License

public Map<Integer, String> getLocationNamesMapByGIDs(final List<Integer> gids) {
    final Map<Integer, String> toreturn = new HashMap<>();
    for (final Integer gid : gids) {
        toreturn.put(gid, null);/*  ww w  . jav  a 2 s . c o  m*/
    }

    try {
        final SQLQuery query = this.getSession().createSQLQuery(Location.GET_LOCATION_NAMES_BY_GIDS);
        query.setParameterList("gids", gids);

        final List<Object> results = query.list();
        for (final Object result : results) {
            final Object[] resultArray = (Object[]) result;
            final Integer gid = (Integer) resultArray[0];
            final String location = (String) resultArray[2];
            toreturn.put(gid, location);
        }
    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocationNamesMapByGIDs", "gids",
                gids.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
    }

    return toreturn;
}

From source file:org.generationcp.middleware.dao.LocationDAO.java

License:Open Source License

public Map<Integer, LocationDto> getLocationNamesByGIDs(final List<Integer> gids) {
    final Map<Integer, LocationDto> toreturn = new HashMap<>();
    for (final Integer gid : gids) {
        toreturn.put(gid, null);//from   w  w w .j  av  a2 s .  c  o m
    }

    try {
        final SQLQuery query = this.getSession().createSQLQuery(Location.GET_LOCATION_NAMES_BY_GIDS);
        query.setParameterList("gids", gids);

        final List<Object> results = query.list();
        for (final Object result : results) {
            final Object[] resultArray = (Object[]) result;
            final Integer gid = (Integer) resultArray[0];
            final Integer locid = (Integer) resultArray[1];
            final String locationName = (String) resultArray[2];
            toreturn.put(gid, new LocationDto(locid, locationName));
        }
    } catch (final HibernateException e) {
        throw new MiddlewareQueryException(this.getLogExceptionMessage("getLocationNamesByGIDs", "gids",
                gids.toString(), e.getMessage(), LocationDAO.CLASS_NAME_LOCATION), e);
    }

    return toreturn;
}

From source file:org.generationcp.middleware.dao.LocationDAO.java

License:Open Source License

private void setQueryParameters(final SQLQuery query, final Map<LocationFilters, Object> filters) {
    for (final Map.Entry<LocationFilters, Object> entry : filters.entrySet()) {
        final LocationFilters filter = entry.getKey();
        final Object value = entry.getValue();
        if (value.getClass().isArray()) {
            query.setParameterList(filter.getParameter(), (Object[]) value);
        } else {/*from   w ww .  j av a 2  s .  c om*/
            query.setParameter(filter.getParameter(), value);
        }
    }
}

From source file:org.generationcp.middleware.dao.MethodDAO.java

License:Open Source License

@SuppressWarnings("unchecked")
public List<String> getMethodCodeByMethodIds(final Set<Integer> methodIds) {
    List<String> methodsCodes = new ArrayList<String>();
    final StringBuilder queryString = new StringBuilder();
    queryString.append("SELECT mcode FROM methods WHERE mid  IN (:mids)");
    final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
    query.setParameterList("mids", methodIds);

    methodsCodes.addAll(query.list());/* w w  w  .  j  a  v  a2 s. c om*/
    return methodsCodes;
}

From source file:org.generationcp.middleware.dao.NameDAO.java

License:Open Source License

/**
 * Get the names associated with a GID//w ww . ja va 2s .  c  o m
 *
 * @param gid the gid for which we are getting names
 * @param status the status of the gid. Note if status is null or 0 we will omit deleted values i.e. status will be set to 9
 * @param type a list of name types to retrieve. Note if type is null or empty it will be omited from the query
 * @return
 */
@SuppressWarnings("unchecked")
public List<Name> getByGIDWithListTypeFilters(final Integer gid, final Integer status,
        final List<Integer> type) {
    try {
        if (gid != null) {
            final StringBuilder queryString = new StringBuilder();
            queryString.append("SELECT ");
            queryString.append("CASE n.nstat ");
            queryString.append("   WHEN NOT 1 THEN 9999 ");
            queryString.append("   ELSE n.nstat ");
            queryString.append("END AS 'nameOrdering', ");
            queryString.append("{n.*} from names n WHERE n.gid = :gid ");

            if (status != null && status != 0) {
                queryString.append("AND n.nstat = :nstat ");
            } else {
                queryString.append("AND n.nstat != 9 ");
            }

            if (type != null && !type.isEmpty()) {
                queryString.append("AND n.ntype IN (:ntype) ");
            }

            queryString.append("ORDER BY nameOrdering, n.nval");

            final SQLQuery query = this.getSession().createSQLQuery(queryString.toString());
            query.addEntity("n", Name.class);
            query.setParameter("gid", gid);

            if (status != null && status != 0) {
                query.setParameter("nstat", status);
            }

            if (type != null && !type.isEmpty()) {
                query.setParameterList("ntype", type);
            }

            return query.list();
        }

    } catch (final HibernateException e) {
        final String message = "Error with getByGIDWithFilters(gid=" + gid + ", status=" + status + ", type="
                + type + ") query from Name " + e.getMessage();
        NameDAO.LOG.error(message);
        throw new MiddlewareQueryException(message, e);
    }
    return new ArrayList<>();
}

From source file:org.generationcp.middleware.dao.NameDAO.java

License:Open Source License

/**
 * Retrieves the gId and nId pairs for the given germplasm names
 *
 * @param germplasmNames the list of germplasm names
 * @return the list of GidNidElement (gId and nId pairs) @
 *///w  w  w.  j a v a2 s. c o  m
@SuppressWarnings("rawtypes")
public List<GermplasmNameDetails> getGermplasmNameDetailsByNames(final List<String> germplasmNames,
        final GetGermplasmByNameModes mode) {
    final List<GermplasmNameDetails> toReturn = new ArrayList<>();

    try {

        if (germplasmNames != null && !germplasmNames.isEmpty()) {

            // Default query if mode = NORMAL, STANDARDIZED, SPACES_REMOVED
            SQLQuery query = this.getSession().createSQLQuery(Name.GET_NAME_DETAILS_BY_NAME);

            if (mode == GetGermplasmByNameModes.SPACES_REMOVED_BOTH_SIDES) {
                query = this.getSession().createSQLQuery("SELECT gid, nid, REPLACE(nval, ' ', '') "
                        + "FROM names " + "WHERE nval IN (:germplasmNameList)");
            }

            query.setParameterList("germplasmNameList", germplasmNames);
            final List results = query.list();

            for (final Object o : results) {
                final Object[] result = (Object[]) o;
                if (result != null) {
                    final Integer gId = (Integer) result[0];
                    final Integer nId = (Integer) result[1];
                    final String nVal = (String) result[2];
                    final GermplasmNameDetails element = new GermplasmNameDetails(gId, nId, nVal);
                    toReturn.add(element);
                }
            }
        }
    } catch (final HibernateException e) {
        final String message = "Error with getGermplasmNameDetailsByNames(germplasmNames=" + germplasmNames
                + ") query from Name " + e.getMessage();
        NameDAO.LOG.error(message);
        throw new MiddlewareQueryException(message, e);
    }
    return toReturn;
}