List of usage examples for org.hibernate SQLQuery setParameterList
@Override NativeQuery<T> setParameterList(String name, Object[] values);
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; }