List of usage examples for org.springframework.jdbc.core.namedparam MapSqlParameterSource MapSqlParameterSource
public MapSqlParameterSource(String paramName, @Nullable Object value)
From source file:tds.assessment.repositories.impl.AssessmentQueryRepositoryImpl.java
@Override public List<AssessmentInfo> findAssessmentInfoByKeys(final String clientName, final String... assessmentKeys) { final SqlParameterSource parameters = new MapSqlParameterSource("keys", Arrays.asList(assessmentKeys)) .addValue("clientName", clientName); final String SQL = ASSESSMENT_INFO_SELECT + "WHERE \n" + " tp.isselectable = 1 \n" + " AND tp.clientname = :clientName \n" + " AND tool.clientname = :clientName \n" + " AND tool.type = 'Language' \n" + " AND tool.contexttype = 'TEST' \n" + " AND a._key IN (:keys) \n" + "GROUP BY \n" + " assessmentKey, assessmentId, subject, assessmentLabel, maxAttempts"; return jdbcTemplate.query(SQL, parameters, assessmentInfoRowMapper); }
From source file:tds.assessment.repositories.impl.AccommodationsQueryRepositoryImpl.java
@Override public List<Accommodation> findAssessmentAccommodationsByKey(String assessmentKey, Set<String> languageCodes) { MapSqlParameterSource parameters = new MapSqlParameterSource("testKey", assessmentKey).addValue("languages", languageCodes);//ww w. jav a 2 s .c o m String SQL = "( \n" + "SELECT \n" + " distinct SegmentPosition as segment,\n" + " SEG.modekey as segmentKey, \n" + " SEG.segmentId as context, \n" + " TType.DisableOnGuestSession as disableOnGuestSession, \n" + " TType.SortOrder as toolTypeSortOrder , \n" + " TT.SortOrder as toolValueSortOrder, \n" + " TType.TestMode as typeMode, \n" + " TT.TestMode as toolMode, \n" + " TT.Type as accType, \n" + " TT.Value as accValue, \n" + " TT.Code as accCode, \n" + " TT.IsDefault as isDefault, \n" + " TT.AllowCombine as allowCombine, \n" + " TType.IsFunctional as isFunctional, \n" + " TType.AllowChange as allowChange,\n" + " TType.IsSelectable as isSelectable, \n" + " TType.IsVisible as isVisible, \n" + " TType.studentControl as studentControl, \n" + " null as dependsOnToolType, \n" + " (select count(1) from configs.client_testtool TOOL where TOOL.ContextType = 'TEST' and TOOL.Context = MODE.testID and TOOL.clientname = MODE.clientname and TOOL.Type = TT.Type) as ValCount, \n" + " IsEntryControl as isEntryControl\n" + "FROM \n" + " configs.client_testmode MODE \n" + " JOIN configs.client_testtooltype TType ON \n" + " MODE.clientname = TType.clientname\n" + " JOIN configs.client_testtool TT ON \n" + " TT.Type = TType.Toolname AND \n" + " MODE.clientname = TT.clientname\n" + " JOIN configs.client_segmentproperties SEG ON \n" + " MODE.testkey = SEG.modekey AND \n" + " TType.Context = SEG.segmentID AND\n" + " TT.Context = SEG.segmentId\n" + "WHERE \n" + " SEG.parentTest = MODE.testID \n" + " and MODE.testkey = :testKey \n" + " and TType.ContextType = 'SEGMENT' \n" + " and TT.ContextType = 'SEGMENT' \n" + " and (TType.TestMode = 'ALL' AND TT.TestMode = 'ALL') \n" + " or (TType.TestMode = MODE.mode and TT.TestMode = MODE.mode) \n" + ") \n" + "UNION ALL ( \n " + "SELECT \n" + " distinct 0 as segment, \n" + " MODE.testkey as segmentKey, \n" + " MODE.testid as context, \n" + " TType.DisableOnGuestSession as disableOnGuestSession, \n" + " TType.SortOrder as toolTypeSortOrder, \n" + " TT.SortOrder as toolValueSortOrder, \n" + " TType.TestMode as typeMode,\n" + " TT.TestMode as toolMode, \n" + " TT.Type as accType, \n" + " TT.Value as accValue, \n" + " TT.Code as accCode, \n" + " TT.IsDefault as isDefault, \n" + " TT.AllowCombine as allowCombine, \n" + " TType.IsFunctional as isFunctional, \n" + " TType.AllowChange as allowChange, \n" + " TType.IsSelectable as isSelectable, \n" + " TType.IsVisible as isVisible, \n" + " TType.studentControl as studentControl,\n" + " TType.DependsOnToolType as dependsOnToolType, \n" + " (select count(1) from configs.client_testtool TOOL where TOOL.ContextType = 'TEST' and TOOL.Context = MODE.testID and TOOL.clientname = MODE.clientname and TOOL.Type = TT.Type) as ValCount, \n" + " TType.IsEntryControl as isEntryControl\n" + "FROM \n" + " configs.client_testtooltype TType \n" + " JOIN configs.client_testtool TT ON \n" + " TT.Type = TType.Toolname AND \n" + " TT.ClientName = TType.clientname\n" + " JOIN configs.client_testmode MODE ON \n" + " TType.Context = MODE.testid AND \n" + " TT.ClientName = MODE.clientname AND\n" + " TType.ClientName = MODE.clientname\n" + "WHERE\n" + " MODE.testkey = :testKey \n" + " and TType.ContextType = 'TEST' \n" + " and TT.Context = MODE.testid \n" + " and TT.ContextType = 'TEST' \n" + " and (TT.Type <> 'Language' or TT.Code in (:languages)) \n" + " and (TType.TestMode = 'ALL' AND TT.TestMode = 'ALL') \n" + " or (TType.TestMode = MODE.mode and TT.TestMode = MODE.mode) \n" + ") \n" + "UNION ALL \n" + "(\n" + "SELECT \n" + " distinct 0 as segment,\n" + " MODE.testkey as segmentKey, \n" + " MODE.testid as context, \n" + " TType.DisableOnGuestSession as disableOnGuestSession, \n" + " TType.SortOrder as toolTypeSortOrder, \n" + " TT.SortOrder as toolValueSortOrder, \n" + " TType.TestMode as typeMode, \n" + " TT.TestMode as toolMode, \n" + " TT.Type as accType, \n" + " TT.Value as accValue, \n" + " TT.Code as accCode, \n" + " TT.IsDefault as isDefault, \n" + " TT.AllowCombine as allowCombine, \n" + " TType.IsFunctional as isFunctional, \n" + " TType.AllowChange as allowChange, \n" + " TType.IsSelectable as isSelectable, \n" + " TType.IsVisible as isVisible, \n" + " TType.studentControl as studentControl, \n" + " TType.DependsOnToolType as dependsOnToolType, \n" + " (select count(1) from configs.client_testtool TOOL where TOOL.ContextType = 'TEST' and TOOL.Context = '*' and TOOL.clientname = MODE.clientname and TOOL.Type = TT.Type) as ValCount, " + " TType.IsEntryControl as isEntryControl\n" + "FROM \n" + " configs.client_testmode MODE\n" + " JOIN configs.client_testtooltype TType ON\n" + " TType.clientname = MODE.clientname\n" + " JOIN configs.client_testtool TT ON\n" + " TT.Type = TType.Toolname\n" + "WHERE \n" + " MODE.testkey = :testKey \n" + " and TType.ContextType = 'TEST' \n" + " and TType.Context = '*' \n" + " and TT.ContextType = 'TEST' \n" + " and TT.Context = '*' \n" + " and TT.clientname = MODE.clientname \n" + " and (TType.TestMode IN ('ALL', 'online') AND TT.TestMode = 'ALL') \n" + " or (TType.TestMode = MODE.mode and TT.TestMode = MODE.mode) \n" + " and not exists (select * from configs.client_testtooltype Tool where Tool.ContextType = 'TEST' and Tool.Context = MODE.testID and Tool.Toolname = TType.Toolname and Tool.Clientname = MODE.clientname)\n" + ")"; return jdbcTemplate.query(SQL, parameters, accommodationRowMapper); }
From source file:com.eu.evaluation.server.service.impl.eva.UpAndDownEvaluate.java
/** * ????/* w w w. j a v a 2 s. co m*/ * * @param ev * @param instanceID ??ID * @return */ private Long countDown(UpAndDownEvlauateItemHistory ev, AccessSystem accessSystem, String instanceID) { //??or.self = or.parent = ? ObjectRelation or = objectRelationDAO.findByParent(ev.getDownEntity().getInstanceClass(), ev.getObjectDictionary().getInstanceClass()); if (or == null) { throw new RuntimeException( "?? = " + ev.getDownEntity().getDisplayname() + " ; = " + ev.getObjectDictionary().getDisplayname() + "?"); } //sql? String jpql = "select count(*) from {0} t where t.evaluateVersion.id = :evid and t.position = :position and exists (select 1 from {1} p where p.evaluateVersion.id = t.evaluateVersion.id and p.position = t.position and p.id = :id and p.id = t.{2}"; if (or.isSimpleProperty()) {//??p.id = t.field? jpql += ")"; } else {//???p.id = t.field.id? jpql += ".id)"; } jpql = MessageFormat.format(jpql, new Object[] { or.getSelfClass(), or.getRelationClass(), or.getPropertyName() }); MapSqlParameterSource params = new MapSqlParameterSource("id", instanceID); params.addValue("evid", ev.getEvaluateVersion().getId()); params.addValue("position", accessSystem.getCode()); return defaultDAO.findOnylFirst(jpql, params); }
From source file:alfio.manager.AdminReservationRequestManager.java
private Result<String> insertRequest(AdminReservationModification body, Event event, boolean singleReservation, String username) {/*w ww . java 2 s .c o m*/ try { String requestId = UUID.randomUUID().toString(); long userId = userRepository.findIdByUserName(username).orElseThrow(IllegalArgumentException::new); MapSqlParameterSource[] requests = spread(body, singleReservation) .map(res -> new MapSqlParameterSource("userId", userId).addValue("requestId", requestId) .addValue("requestType", AdminReservationRequest.RequestType.IMPORT.name()) .addValue("status", AdminReservationRequest.Status.PENDING.name()) .addValue("eventId", event.getId()).addValue("body", Json.toJson(res))) .toArray(MapSqlParameterSource[]::new); jdbc.batchUpdate(adminReservationRequestRepository.insertRequest(), requests); return Result.success(requestId); } catch (Exception e) { log.error("can't insert reservation request", e); return Result.error(ErrorCode.custom("internal_server_error", e.getMessage())); } }
From source file:io.lavagna.service.CardRepository.java
@Transactional(readOnly = false) public List<Integer> moveCardsToColumn(List<Integer> cardIds, int previousColumnId, int columnId, int userId) { List<Integer> filteredCardIds = Utils.filter(cardIds, queries.findCardIdsInColumnId(cardIds, previousColumnId)); List<SqlParameterSource> params = new ArrayList<>(filteredCardIds.size()); for (int cardId : filteredCardIds) { SqlParameterSource p = new MapSqlParameterSource("cardId", cardId)// .addValue("previousColumnId", previousColumnId)// .addValue("columnId", columnId); params.add(p);//from w w w. ja v a 2 s . c o m } int[] updateResult = jdbc.batchUpdate(queries.moveCardToColumn(), params.toArray(new SqlParameterSource[params.size()])); List<Integer> updated = new ArrayList<>(); for (int i = 0; i < updateResult.length; i++) { if (updateResult[i] > 0) { updated.add(filteredCardIds.get(i)); } } return updated; }
From source file:com.epam.catgenome.dao.reference.ReferenceGenomeDao.java
/** * Loads a persisted {@code Reference} entity from the database by a specified ID * @param referenceId {@code Reference} ID to load * @return loaded {@code Reference} instance */// w w w .java2s .c o m @Transactional(propagation = Propagation.MANDATORY) public Reference loadReferenceGenome(final Long referenceId) { final List<Reference> list = getNamedParameterJdbcTemplate().query(loadReferenceGenomeByIdQuery, new MapSqlParameterSource(GenomeParameters.REFERENCE_GENOME_ID.name(), referenceId), GenomeParameters.getReferenceGenomeMapper()); return CollectionUtils.isNotEmpty(list) ? list.get(0) : null; }
From source file:tds.assessment.repositories.impl.AssessmentQueryRepositoryImpl.java
@Override public List<AssessmentInfo> findAssessmentInfoForGrade(final String clientName, final String grade) { final SqlParameterSource parameters = new MapSqlParameterSource("grade", grade).addValue("clientName", clientName);//from w w w. j a v a2s . c om final String SQL = ASSESSMENT_INFO_SELECT + "WHERE \n" + " tp.isselectable = 1 \n" + " AND tp.clientname = :clientName \n" + " AND tool.clientname = :clientName \n" + " AND tool.type = 'Language' \n" + " AND tool.contexttype = 'TEST' \n" + " AND g.grade = :grade \n" + "GROUP BY \n" + " assessmentKey, assessmentId, subject, assessmentLabel, maxAttempts"; return jdbcTemplate.query(SQL, parameters, assessmentInfoRowMapper); }
From source file:tds.assessment.repositories.impl.ItemQueryRepositoryImpl.java
@Override public Optional<ItemFileMetadata> findItemFileMetadataByItemKey(final String clientName, final long bankKey, final long itemKey) { final SqlParameterSource parameters = new MapSqlParameterSource("clientName", clientName) .addValue("itemKey", createItemKey(bankKey, itemKey)); final String SQL = "SELECT\n" + " I.Filepath,\n" + " I.filename,\n" + " I._key AS id\n" + "FROM\n" + " tblitem I\n" + "JOIN \n" + " tblitembank B ON B._efk_Itembank = I._efk_Itembank\n" + "JOIN \n" + " tblclient C ON B._fk_Client = C._Key\n" + "WHERE\n" + " C.name = :clientName AND I._key = :itemKey "; Optional<ItemFileMetadata> maybeItemFile; try {// w w w.j a va 2 s .c o m maybeItemFile = Optional.of(jdbcTemplate.queryForObject(SQL, parameters, (rs, rowNum) -> ItemFileMetadata.create(ItemFileType.ITEM, rs.getString("id"), rs.getString("filename"), rs.getString("filepath")))); } catch (EmptyResultDataAccessException e) { maybeItemFile = Optional.empty(); } return maybeItemFile; }
From source file:alfio.manager.system.DataMigrator.java
void fixCategoriesSize(Event event) { ticketCategoryRepository.findByEventId(event.getId()).stream().filter(TicketCategory::isBounded) .forEach(tc -> {//from w ww .j ava 2 s . co m Integer result = jdbc.queryForObject( "select count(*) from ticket where event_id = :eventId and category_id = :categoryId and status <> 'INVALIDATED'", new MapSqlParameterSource("eventId", tc.getEventId()).addValue("categoryId", tc.getId()), Integer.class); if (result != null && result != tc.getMaxTickets()) { log.warn("********* updating category size for {} from {} to {} tickets", tc.getName(), tc.getMaxTickets(), result); ticketCategoryRepository.updateSeatsAvailability(tc.getId(), result); } }); }