List of usage examples for org.springframework.jdbc.core.namedparam MapSqlParameterSource MapSqlParameterSource
public MapSqlParameterSource(String paramName, @Nullable Object value)
From source file:com.exploringspatial.dao.impl.ConflictDaoImpl.java
@Override public List<String> findDistinctAdministrativeRegions(String country) { final String sql = "SELECT DISTINCT ADMIN1 FROM CONFLICT WHERE COUNTRY_LOWERCASE = LOWER(:country) ORDER BY ADMIN1"; final MapSqlParameterSource params = new MapSqlParameterSource("country", country); return namedParameterJdbcTemplate.queryForList(sql, params, String.class); }
From source file:org.mulima.internal.freedb.FreeDbJdbcDaoImpl.java
/** * Gets the CDDB ids for the disc id./* www . ja va2s. com*/ * @param id the id of the disc * @return a list of CDDB ids */ private List<String> getCddbIdsForDisc(int id) { final String sql = "SELECT `cddb_id` FROM `cddb_ids` WHERE `disc_id`=:id"; SqlParameterSource parms = new MapSqlParameterSource("id", id); return this.getNamedParameterJdbcTemplate().queryForList(sql, parms, String.class); }
From source file:tds.assessment.repositories.impl.ItemQueryRepositoryImpl.java
@Override public List<ItemProperty> findActiveItemsProperties(final String assessmentKey) { final SqlParameterSource parameters = new MapSqlParameterSource("key", assessmentKey); final String SQL = "SELECT \n" + " _fk_item AS itemId, \n" + " propname AS name, \n" + " propvalue AS value, \n" + " propdescription AS description \n" + "FROM \n" + " itembank.tblitemprops P \n" + "JOIN " + " itembank.tblsetofadminsubjects segments \n" + " ON segments._key = P._fk_adminsubject \n" + "WHERE \n" + " segments.virtualtest = :key \n" + " OR segments._key = :key \n" + "AND\n" + " isActive = 1"; return jdbcTemplate.query(SQL, parameters, (rs, row) -> new ItemProperty(rs.getString("name"), rs.getString("value"), rs.getString("description"), rs.getString("itemId"))); }
From source file:com.exploringspatial.dao.impl.ConflictDaoImpl.java
@Override public List<Conflict> findByCountryAdministrativeRegion(String country, String administrativeRegion) { final String sql = selectSql.concat( " WHERE COUNTRY_LOWERCASE = LOWER(:country) AND ADMIN1_LOWERCASE = LOWER(:administrativeRegion) ORDER BY EVENT_ID_NO_CNTY"); final MapSqlParameterSource params = new MapSqlParameterSource("country", country) .addValue("administrativeRegion", administrativeRegion); return namedParameterJdbcTemplate.query(sql, params, new ConflictRowMapper()); }
From source file:tds.assessment.repositories.impl.AssessmentWindowQueryRepositoryIntegrationTests.java
@Test public void shouldFindAssessmentFormWindowsWithSegments() { DateTime startTime = new DateTime(2016, 8, 10, 19, 2, 43, DateTimeZone.UTC); DateTime endTime = new DateTime(2017, 8, 10, 19, 2, 43, DateTimeZone.UTC); SqlParameterSource parameters = new MapSqlParameterSource("startTime", mapJodaInstantToTimestamp(startTime.toInstant())).addValue("endTime", mapJodaInstantToTimestamp(endTime.toInstant())); String clientTestFormPropertiesInsertSQL = "INSERT INTO configs.client_testformproperties (clientname,_efk_testform,startdate,enddate,language,formid,testid,testkey,clientformid,accommodations) \n" + "VALUES ('SBAC_PT','187-534',NULL,NULL,'ENU','PracTest::MG11::S1::SP14','SBAC-SEG1-MATH-11','(SBAC_PT)SBAC-SEG1-MATH-11-Spring-2013-2015',NULL,NULL),\n" + " ('SBAC_PT','187-535',NULL,NULL,'ENU-Braille','PracTest::MG11::S1::SP14::Braille','SBAC-SEG1-MATH-11','(SBAC_PT)SBAC-SEG1-MATH-11-Spring-2013-2015',NULL,NULL),\n" + " ('SBAC_PT','187-536',NULL,NULL,'ESN','PracTest::MG11::S1::SP14::ESN','SBAC-SEG1-MATH-11','(SBAC_PT)SBAC-SEG1-MATH-11-Spring-2013-2015',NULL,NULL);"; String clientSegmentPropertiesInsertSQL = "INSERT INTO configs.client_segmentproperties (ispermeable,clientname,entryapproval,exitapproval,itemreview,segmentid,segmentposition,parenttest,ftstartdate,ftenddate,label,modekey,restart,graceperiodminutes) \n" + "VALUES (1,'SBAC_PT',0,0,0,'SBAC-SEG1-MATH-11',1,'SBAC-Mathematics-11',NULL,NULL,'Grade 11 MATH segment','(SBAC_PT)SBAC-Mathematics-11-Spring-2013-2015',NULL,NULL),\n" + " (1,'SBAC_PT',0,0,0,'SBAC-SEG2-MATH-11',2,'SBAC-Mathematics-11',NULL,NULL,'Grade 11 MATH segment','(SBAC_PT)SBAC-Mathematics-11-Spring-2013-2015',NULL,NULL);"; String clientTestPropertiesInsertSQL = "INSERT INTO configs.client_testproperties (clientname,testid,maxopportunities,handscoreproject,prefetch,datechanged,isprintable,isselectable,label,printitemtypes,scorebytds,batchmodereport,subjectname,origin,source,maskitemsbysubject,initialabilitybysubject,startdate,enddate,ftstartdate,ftenddate,accommodationfamily,sortorder,rtsformfield,rtswindowfield,windowtideselectable,requirertswindow,reportinginstrument,tide_id,forcecomplete,rtsmodefield,modetideselectable,requirertsmode,requirertsmodewindow,deleteunanswereditems,abilityslope,abilityintercept,validatecompleteness,gradetext,initialabilitytestid,proctoreligibility,category) \n" + "VALUES ('SBAC_PT','SBAC-Mathematics-11',3,NULL,2,NULL,0,1,'Grade 11 MATH','',1,0,'MATH',NULL,NULL,1,1,NULL,NULL,NULL,NULL,'MATH',NULL,'tds-testform','tds-testwindow',0,1,NULL,NULL,1,'tds-testmode',0,0,0,0,1,0,0,'Grade 11',NULL,0,NULL);"; String clientTestModeInsertSQL = "INSERT INTO configs.client_testmode (clientname,testid,mode,algorithm,formtideselectable,issegmented,maxopps,requirertsform,requirertsformwindow,requirertsformifexists,sessiontype,testkey,_key) " + "VALUES ('SBAC_PT','SBAC-Mathematics-11','online','virtual',0,1,50,0,0,1,0,'(SBAC_PT)SBAC-Mathematics-11-Spring-2013-2015',UNHEX('0431F6515F2D11E6B2C80243FCF25EAB'));"; String clientTestWindowInsertSQL = "INSERT INTO configs.client_testwindow (clientname,testid,window,numopps,startdate,enddate,origin,source,windowid,_key,sessiontype,sortorder)" + "VALUES ('SBAC_PT','SBAC-Mathematics-11',1,3,:startTime,:endTime,NULL,NULL,'ANNUAL',UNHEX('043A37525F2D11E6B2C80243FCF25EAB'),-1,1);"; jdbcTemplate.update(clientTestFormPropertiesInsertSQL, new MapSqlParameterSource()); jdbcTemplate.update(clientSegmentPropertiesInsertSQL, new MapSqlParameterSource()); jdbcTemplate.update(clientTestPropertiesInsertSQL, new MapSqlParameterSource()); jdbcTemplate.update(clientTestModeInsertSQL, new MapSqlParameterSource()); jdbcTemplate.update(clientTestWindowInsertSQL, parameters); List<AssessmentWindow> assessmentWindows = repository.findCurrentAssessmentFormWindows("SBAC_PT", "SBAC-Mathematics-11", 0, 0, 0, 0); assertThat(assessmentWindows).hasSize(3); AssessmentWindow window = assessmentWindows.get(0); assertThat(window.getWindowMaxAttempts()).isEqualTo(3); assertThat(window.getModeSessionType()).isEqualTo(0); assertThat(window.getMode()).isEqualTo("online"); assertThat(window.getStartTime()).isEqualByComparingTo(startTime.toInstant()); assertThat(window.getEndTime()).isEqualByComparingTo(endTime.toInstant()); assertThat(window.getFormKey()).isEqualTo("187-534"); assertThat(window.getWindowId()).isEqualTo("ANNUAL"); }
From source file:alfio.manager.AdminReservationRequestManager.java
private MapSqlParameterSource buildParameterSource(Long id, Result<Triple<TicketReservation, List<Ticket>, Event>> result) { boolean success = result.isSuccess(); return new MapSqlParameterSource("id", id) .addValue("status", success ? AdminReservationRequest.Status.SUCCESS.name() : AdminReservationRequest.Status.ERROR.name()) .addValue("reservationId", success ? result.getData().getLeft().getId() : null) .addValue("failureCode", success ? null : ofNullable(result.getFirstErrorOrNull()).map(ErrorCode::getCode).orElse(null)); }
From source file:io.lavagna.service.CardRepository.java
@Transactional(readOnly = false) public void moveCardToColumn(int cardId, int previousColumnId, int columnId) { SqlParameterSource param = new MapSqlParameterSource("cardId", cardId).addValue("columnId", columnId) .addValue("previousColumnId", previousColumnId); int affected = jdbc.update(queries.moveCardToColumn(), param); Validate.isTrue(1 == affected, "moveCardToColumn: must affect exactly one row"); }
From source file:io.lavagna.service.CardDataRepository.java
/** * Order the action list. Additionally, the ids are filtered. * * @param cardId/*from www . ja v a2s .c om*/ * @param data */ @Transactional(readOnly = false) public void updateActionListOrder(int cardId, List<Integer> data) { // we filter out wrong ids List<Integer> filtered = Utils.filter(data, queries.findAllCardDataIdsBy(data, cardId, CardType.ACTION_LIST.toString())); // SqlParameterSource[] params = new SqlParameterSource[filtered.size()]; for (int i = 0; i < filtered.size(); i++) { params[i] = new MapSqlParameterSource("order", i + 1).addValue("id", filtered.get(i)).addValue("cardId", cardId); } jdbc.batchUpdate(queries.updateOrder(), params); }
From source file:tds.assessment.repositories.impl.ItemQueryRepositoryImpl.java
@Override public Optional<ItemFileMetadata> findItemFileMetadataByStimulusKey(final String clientName, final long bankKey, final long stimulusKey) { final SqlParameterSource parameters = new MapSqlParameterSource("clientName", clientName) .addValue("stimulusKey", createItemKey(bankKey, stimulusKey)); final String SQL = "SELECT \n" + " S.FilePath, \n" + " S.FileName, \n" + " S._key AS id \n" + "FROM\n" + " tblstimulus S \n" + "JOIN \n" + " tblitembank B ON B._efk_Itembank = S._efk_Itembank \n" + "JOIN \n" + " tblclient C ON B._fk_Client = C._Key \n" + "WHERE\n" + " C.name = :clientName AND S._key = :stimulusKey"; Optional<ItemFileMetadata> maybeItemFile; try {// w w w .j a v a2 s. c om maybeItemFile = Optional.of(jdbcTemplate.queryForObject(SQL, parameters, (rs, rowNum) -> ItemFileMetadata.create(ItemFileType.STIMULUS, 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 fixStuckTickets(int eventId) { List<Integer> ticketIds = jdbc.queryForList( "select a.id from ticket a, tickets_reservation b where a.event_id = :eventId and a.status = 'PENDING' and a.tickets_reservation_id = b.id and b.status = 'CANCELLED'", new MapSqlParameterSource("eventId", eventId), Integer.class); if (!ticketIds.isEmpty()) { int toBeFixed = ticketIds.size(); log.warn("********* reverting {} stuck tickets ({}) for event id {}", toBeFixed, ticketIds, eventId); int[] results = jdbc.batchUpdate( "update ticket set status = 'RELEASED'," + RESET_TICKET + " where id = :ticketId", ticketIds.stream().map(id -> new MapSqlParameterSource("ticketId", id)) .toArray(MapSqlParameterSource[]::new)); int result = Arrays.stream(results).sum(); Validate.isTrue(result == toBeFixed, "Error while fixing stuck tickets: expected " + toBeFixed + ", got " + result); }/*from ww w .ja v a 2s . c o m*/ }