org.springframework.jdbc.core.namedparam.MapSqlParameterSource Java Examples

@Override
public Set<String> findUserIdsConnectedTo(String providerId, Set<String> providerUserIds) {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("providerId", providerId);
    parameters.addValue("providerUserIds", providerUserIds);
    final Set<String> localUserIds = new HashSet<String>();
    return new NamedParameterJdbcTemplate(jdbcTemplate).query("select userId from " + tablePrefix + "UserConnection where providerId = :providerId and providerUserId in (:providerUserIds)", parameters,
            new ResultSetExtractor<Set<String>>() {
                @Override
                public Set<String> extractData(ResultSet rs) throws SQLException, DataAccessException {
                    while (rs.next()) {
                        localUserIds.add(rs.getString("userId"));
                    }
                    return localUserIds;
                }
            });
}
 

private List<LogCollection> getTransactionLogByIds(JdbcTemplate localJdbcTemplate, List<byte[]> transIdList) {
      List<DataBaseTransactionLogDetail> query;
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(localJdbcTemplate);
MapSqlParameterSource paramSource = new MapSqlParameterSource();
paramSource.addValue("ids", transIdList);
query = namedTemplate.query(selectTransDetailsByIds, paramSource,new BeanPropertyRowMapper<DataBaseTransactionLogDetail>(DataBaseTransactionLogDetail.class));
	

List<LogCollection> result = new ArrayList<LogCollection>();
List<DataBaseTransactionLogDetail> currentDoList = new ArrayList<DataBaseTransactionLogDetail>();
List<Content> currentContentList = new ArrayList<Content>();
byte[] currentId = null;
for(DataBaseTransactionLogDetail detailDo:query){
	if(!Arrays.equals(detailDo.getTransLogId(), currentId)){
		addToResult(result, currentDoList, currentContentList);
		currentContentList.clear();
		currentDoList.clear();
		currentId = detailDo.getTransLogId();
	}
	
	currentDoList.add(detailDo);
	currentContentList.addAll(deserializer(detailDo));
}
addToResult(result, currentDoList, currentContentList);
      return result;
  }
 

public List<Statistic> createStatistic() {
	StringBuilder builder = new StringBuilder();
	builder.append("Select b.user_name, a.match_group, sum(b.points) ");
	builder.append("from matches a join bet b on a.match_id = b.match_id ");
	builder.append("where a.goals_team_one is not null  ");
	builder.append("and a.goals_team_two is not null  ");
	builder.append("and b.user_name not like :username ");
	builder.append("group by b.user_name, a.match_group;");

	MapSqlParameterSource params = new MapSqlParameterSource();
	params.addValue("username", FredbetConstants.TECHNICAL_USERNAME);

	final StatisticsCollector statisticsCollector = new StatisticsCollector();

	namedParameterJdbcOperations.query(builder.toString(), params, (ResultSet rs) -> {
		String userName = rs.getString(1);
		String group = rs.getString(2);
		int points = rs.getInt(3);
		statisticsCollector.addValue(userName, group, points);
	});

	return statisticsCollector.getResult();
}
 

private MapSqlParameterSource buildParameterSource( Audit audit )
{
    MapSqlParameterSource parameters = new MapSqlParameterSource();

    parameters.addValue( "auditType", audit.getAuditType() );
    parameters.addValue( "auditScope", audit.getAuditScope() );
    parameters.addValue( "createdAt", audit.getCreatedAt() );
    parameters.addValue( "createdBy", audit.getCreatedBy() );
    parameters.addValue( "klass", audit.getKlass() );
    parameters.addValue( "uid", audit.getUid() );
    parameters.addValue( "code", audit.getCode() );
    parameters.addValue( "data", compress( audit.getData() ) );

    try
    {
        parameters.addValue( "attributes", jsonMapper.writeValueAsString( audit.getAttributes() ) );
    }
    catch ( JsonProcessingException ignored )
    {
    }

    return parameters;
}
 

@Test
public void testAddInvoiceProcWithoutMetaDataUsingMapParamSource() throws Exception {
	initializeAddInvoiceWithoutMetaData(false);
	SimpleJdbcCall adder = new SimpleJdbcCall(dataSource).withProcedureName("add_invoice");
	adder.declareParameters(
			new SqlParameter("amount", Types.INTEGER),
			new SqlParameter("custid", Types.INTEGER),
			new SqlOutParameter("newid",
			Types.INTEGER));
	Number newId = adder.executeObject(Number.class, new MapSqlParameterSource().
			addValue("amount", 1103).
			addValue("custid", 3));
	assertEquals(4, newId.intValue());
	verifyAddInvoiceWithoutMetaData(false);
	verify(connection, atLeastOnce()).close();
}
 

public void rearrangeCategories(String eventName, List<CategoryOrdinalModification> categories, String username) {
    var optionalEvent = getOptionalEventAndOrganizationIdByName(eventName, username);
    if(optionalEvent.isPresent()) {
        int eventId = optionalEvent.get().getId();
        var parameterSources = categories.stream()
            .map(category -> new MapSqlParameterSource("ordinal", category.getOrdinal())
                .addValue("id", category.getId())
                .addValue("eventId", eventId))
            .toArray(MapSqlParameterSource[]::new);
        int[] results = jdbcTemplate.batchUpdate(ticketCategoryRepository.updateOrdinal(), parameterSources);
        Validate.isTrue(IntStream.of(results).sum() == categories.size(), "Unexpected result from update.");
    } else {
        log.warn("unauthorized access to event {}", eventName);
    }
}
 

private MapSqlParameterSource createSqlParameterSource(Job aJob) {
  SimpleJob job = new SimpleJob(aJob);
  Assert.notNull(aJob, "job must not be null");
  Assert.notNull(aJob.getId(), "job status must not be null");
  Assert.notNull(aJob.getCreateTime(), "job createTime must not be null");
  Assert.notNull(aJob.getStatus(), "job status must not be null");
  MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource();
  sqlParameterSource.addValue("id", job.getId());
  sqlParameterSource.addValue("status", job.getStatus().toString());
  sqlParameterSource.addValue("currentTask", job.getCurrentTask());
  sqlParameterSource.addValue("pipelineId", job.getPipelineId());
  sqlParameterSource.addValue("label", job.getLabel());
  sqlParameterSource.addValue("createTime", job.getCreateTime());
  sqlParameterSource.addValue("startTime", job.getStartTime());
  sqlParameterSource.addValue("endTime", job.getEndTime());
  sqlParameterSource.addValue("priority", job.getPriority());
  sqlParameterSource.addValue("inputs", Json.serialize(json,job.getInputs()));
  sqlParameterSource.addValue("outputs", Json.serialize(json,job.getOutputs()));
  sqlParameterSource.addValue("webhooks", Json.serialize(json,job.getWebhooks()));
  sqlParameterSource.addValue("parentTaskExecutionId", job.getParentTaskExecutionId());
  return sqlParameterSource;
}
 

@Override
public List<Application> getApplicationsByAppCodes(List<String> appCodes) {
    if (CollectionUtils.isEmpty(appCodes)) {
        return Collections.emptyList();
    }
    final MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("code", appCodes);
    return namedParameterJdbcTemplate.query(SELECT_BY_APP_CODES, parameters, APPLICATIONS_MAPPER);
}
 

private Page<TaskExecution> queryForPageableResults(Pageable pageable,
		String selectClause, String fromClause, String whereClause,
		MapSqlParameterSource queryParameters, long totalCount) {
	SqlPagingQueryProviderFactoryBean factoryBean = new SqlPagingQueryProviderFactoryBean();
	factoryBean.setSelectClause(selectClause);
	factoryBean.setFromClause(fromClause);
	if (StringUtils.hasText(whereClause)) {
		factoryBean.setWhereClause(whereClause);
	}
	final Sort sort = pageable.getSort();
	final LinkedHashMap<String, Order> sortOrderMap = new LinkedHashMap<>();

	if (sort != null) {
		for (Sort.Order sortOrder : sort) {
			sortOrderMap.put(sortOrder.getProperty(),
					sortOrder.isAscending() ? Order.ASCENDING : Order.DESCENDING);
		}
	}

	if (!CollectionUtils.isEmpty(sortOrderMap)) {
		factoryBean.setSortKeys(sortOrderMap);
	}
	else {
		factoryBean.setSortKeys(this.orderMap);
	}

	factoryBean.setDataSource(this.dataSource);
	PagingQueryProvider pagingQueryProvider;
	try {
		pagingQueryProvider = factoryBean.getObject();
		pagingQueryProvider.init(this.dataSource);
	}
	catch (Exception e) {
		throw new IllegalStateException(e);
	}
	String query = pagingQueryProvider.getPageQuery(pageable);
	List<TaskExecution> resultList = this.jdbcTemplate.query(getQuery(query),
			queryParameters, new TaskExecutionRowMapper());
	return new PageImpl<>(resultList, pageable, totalCount);
}
 

@Override
public int countKey(ConfigMeta meta, String key) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("group", meta.getGroup());
    params.addValue("dataId", meta.getDataId());
    params.addValue("profile", meta.getProfile());
    params.addValue("key", key);
    return namedParameterJdbcTemplate.query(COUNT_BY_KEY, params, COUNT_EXTRACTOR);
}
 

default int[] insert(int groupId, List<GroupMemberModification> members) {
    MapSqlParameterSource[] params = members.stream()
        .map(i -> new MapSqlParameterSource("groupId", groupId).addValue("value", i.getValue().toLowerCase()).addValue("description", i.getDescription()))
        .toArray(MapSqlParameterSource[]::new);

    return getNamedParameterJdbcTemplate().batchUpdate("insert into group_member(a_group_id_fk, value, description) values(:groupId, :value, :description)", params);
}
 

@Test
@DisplayName("create tickets only for the bounded categories")
void createTicketsOnlyForBounded() {
    List<TicketCategory> categories = generateCategoryStream().limit(2).collect(Collectors.toList());
    when(ticketCategoryRepository.findAllTicketCategories(eq(eventId))).thenReturn(categories);
    MapSqlParameterSource[] parameterSources = eventManager.prepareTicketsBulkInsertParameters(ZonedDateTime.now(), event, availableSeats, Ticket.TicketStatus.FREE);
    assertNotNull(parameterSources);
    assertEquals(availableSeats, parameterSources.length);
    assertEquals(4L, Arrays.stream(parameterSources).filter(p -> p.getValue("categoryId") != null).count());
    assertTrue(Arrays.stream(parameterSources).allMatch(ps -> Ticket.TicketStatus.FREE.name().equals(ps.getValue("status"))));
}
 

public long insertUser(String username, String name, String rawTempPassword, String sysRole) {
    String encryptedPassword = PasswordUtils.getMd5Hash(rawTempPassword);
    String sql = "INSERT INTO p_user(username, name, temp_password, sys_role) "
                + "VALUES(:username, :name, :temp_password, :sys_role)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(User.USERNAME, username);
    params.addValue(User.NAME, name);
    params.addValue(User.TEMP_PASSWORD, encryptedPassword);
    params.addValue(User.SYS_ROLE, sysRole);

    KeyHolder keyHolder = new GeneratedKeyHolder();
    npjt.update(sql, params, keyHolder, new String[] { User.ID });
    return keyHolder.getKey().longValue();
}
 

public long insert(String name, String style, String project) {
    String sql = "INSERT INTO p_report(name, style, project) VALUES(:name, :style, :project)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(Report.NAME, name);
    params.addValue(Report.STYLE, style);
    params.addValue(Report.PROJECT, project);

    KeyHolder keyHolder = new GeneratedKeyHolder();
    npjt.update(sql, params, keyHolder, new String[] { Report.ID});
    return keyHolder.getKey().longValue();
}
 

public List<SavedQuery> findAll() {
    String sql = "SELECT id, name, endpoint_name "
                + "FROM p_saved_query "
                + "ORDER BY id DESC";

    MapSqlParameterSource params = new MapSqlParameterSource();

    return npjt.query(sql, params, (rs, i) -> {
        SavedQuery r = new SavedQuery();
        r.setId(rs.getLong(SavedQuery.ID));
        r.setName(rs.getString(SavedQuery.NAME));
        r.setEndpointName(rs.getString(SavedQuery.ENDPOINT_NAME));
        return r;
    });
}
 

/**
 * Creates a {@link MapSqlParameterSource} based on data values from the supplied {@link Visit} instance.
 */
private MapSqlParameterSource createVisitParameterSource(Visit visit) {
    return new MapSqlParameterSource()
        .addValue("id", visit.getId())
        .addValue("visit_date", visit.getDate().toDate())
        .addValue("description", visit.getDescription())
        .addValue("pet_id", visit.getPet().getId());
}
 

@Override
public Integer insertProject(Project project) {
    MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("name", project.getName())
            .addValue("datestarted", project.getDate());

    return insertIntoProject.executeAndReturnKey(params).intValue();
}
 

private Stream<MapSqlParameterSource> generateTicketsForCategory(TicketCategory tc,
                                                                 Event event,
                                                                 Date creationDate,
                                                                 int existing) {
    Optional<TicketCategory> filteredTC = Optional.of(tc).filter(TicketCategory::isBounded);
    int missingTickets = filteredTC.map(c -> Math.abs(c.getMaxTickets() - existing)).orElseGet(() -> eventRepository.countExistingTickets(event.getId()) - existing);
    return generateStreamForTicketCreation(missingTickets)
                .map(ps -> buildTicketParams(event.getId(), creationDate, filteredTC, tc.getSrcPriceCts(), ps));
}
 

@Override
public List<ConfigInfoWithoutPublicStatus> findPublicConfigsInProfileAndResources(Environment environment,
                                                                                  Set<String> groups, String groupLike, String dataIdLike, int page, int pageSize) {
    KeyValuePair<MapSqlParameterSource, String> paramsAndExtraQuery = buildRequestParamsAndExtraQuery(environment, groups, groupLike, dataIdLike, page, pageSize);
    return namedParameterJdbcTemplate.query(String.format(SELECT_PUBLIC_CONFIG_IN_PROFILES_AND_RESOURCE, paramsAndExtraQuery.getValue()),
            paramsAndExtraQuery.getKey(), CONFIG_INFO_MAPPER);
}
 

public List<ApiPermission> queryByGroupIdAndTargetGroupId(String groupId, String targetGroupId) {
    final MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("groupId", groupId);
    parameters.addValue("targetGroupId", targetGroupId);
    String sql = "select c.* from api_groupid_rel a " +
            "left join api_groupid_permission_rel b on a.id = b.groupid_rel_id " +
            "LEFT JOIN api_permission c on b.permission_id = c.id " +
            "where a.groupid=:groupId and a.target_groupid=:targetGroupId";

    return namedParameterJdbcTemplate.query(sql,
            parameters,
            ApiPermissionDaoImpl.API_PERMISSION_MAPPER);
}
 

/**
 * Creates a {@link MapSqlParameterSource} based on data values from the supplied {@link Visit} instance.
 */
private MapSqlParameterSource createVisitParameterSource(Visit visit) {
    return new MapSqlParameterSource()
        .addValue("id", visit.getId())
        .addValue("visit_date", visit.getDate())
        .addValue("description", visit.getDescription())
        .addValue("pet_id", visit.getPet().getId());
}
 

private MapSqlParameterSource createParameterByGroupsOrRefGroups(Set<String> groups, boolean isRef) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    if (isRef) {
        params.addValue("refGroups", groups);
    } else {
        params.addValue("groups", groups);
    }
    params.addValue("status", DELETE_STATUS_CODE);
    params.addValue("type", RefType.REFERENCE.value());

    return params;
}
 

/**
 * Creates a {@link MapSqlParameterSource} based on data values from the
 * supplied {@link Pet} instance.
 */
private MapSqlParameterSource createPetParameterSource(Pet pet) {
	return new MapSqlParameterSource()
		.addValue("id", pet.getId())
		.addValue("name", pet.getName())
		.addValue("birth_date", pet.getBirthDate())
		.addValue("type_id", pet.getType().getId())
		.addValue("owner_id", pet.getOwner().getId());
}
 

public void updateEventPrices(EventAndOrganizationId original, EventModification em, String username) {
    Validate.notNull(em.getAvailableSeats(), "Available Seats cannot be null");
    checkOwnership(original, username, em.getOrganizationId());
    int eventId = original.getId();
    int seatsDifference = em.getAvailableSeats() - eventRepository.countExistingTickets(original.getId());
    if(seatsDifference < 0) {
        int allocatedSeats = ticketCategoryRepository.findAllTicketCategories(original.getId()).stream()
                .filter(TicketCategory::isBounded)
                .mapToInt(TicketCategory::getMaxTickets)
                .sum();
        if(em.getAvailableSeats() < allocatedSeats) {
            throw new IllegalArgumentException(format("cannot reduce max tickets to %d. There are already %d tickets allocated. Try updating categories first.", em.getAvailableSeats(), allocatedSeats));
        }
    }
    validatePaymentProxies(em.getAllowedPaymentProxies(), em.getOrganizationId());
    String paymentProxies = collectPaymentProxies(em);
    BigDecimal vat = em.isFreeOfCharge() ? BigDecimal.ZERO : em.getVatPercentage();
    eventRepository.updatePrices(em.getCurrency(), em.getAvailableSeats(), em.isVatIncluded(), vat, paymentProxies, eventId, em.getVatStatus(), em.getPriceInCents());
    if(seatsDifference != 0) {
        Event modified = eventRepository.findById(eventId);
        if(seatsDifference > 0) {
            final MapSqlParameterSource[] params = generateEmptyTickets(modified, Date.from(ZonedDateTime.now(modified.getZoneId()).toInstant()), seatsDifference, TicketStatus.RELEASED).toArray(MapSqlParameterSource[]::new);
            ticketRepository.bulkTicketInitialization(params);
        } else {
            List<Integer> ids = ticketRepository.selectNotAllocatedTicketsForUpdate(eventId, Math.abs(seatsDifference), singletonList(TicketStatus.FREE.name()));
            Validate.isTrue(ids.size() == Math.abs(seatsDifference), "cannot lock enough tickets for deletion.");
            int invalidatedTickets = ticketRepository.invalidateTickets(ids);
            Validate.isTrue(ids.size() == invalidatedTickets, String.format("error during ticket invalidation: expected %d, got %d", ids.size(), invalidatedTickets));
        }
    }
}
 

@Override
public List<CandidateSnapshot> getSnapshotAfterVersion(ConfigMeta meta, Long version) {
    if (version < 0) {
        return Lists.newArrayList();
    }
    NamedParameterJdbcTemplate nameJdbc = new NamedParameterJdbcTemplate(jdbcTemplate);
    MapSqlParameterSource parameter = new MapSqlParameterSource();
    parameter.addValue("group_id", meta.getGroup());
    parameter.addValue("version", version);
    parameter.addValue("profile", meta.getProfile());
    parameter.addValue("data_id", meta.getDataId());
    return nameJdbc.query(SELECT_VERSION_AFTER_OPERATOR, parameter, SNAPSHOT_MAPPER);
}
 

@Override
protected int getCachedExperimentTaskId(String annotatorName, String datasetName, String experimentType,
        String matching) {
    MapSqlParameterSource params = createTaskParameters(annotatorName, datasetName, experimentType, matching);
    java.util.Date today = new java.util.Date();
    params.addValue("lastChanged", new java.sql.Timestamp(today.getTime() - this.resultDurability));
    params.addValue("errorState", ErrorTypes.HIGHEST_ERROR_CODE);
    List<Integer> result = this.template.query(GET_CACHED_TASK, params, new IntegerRowMapper());
    if (result.size() > 0) {
        return result.get(0);
    } else {
        return EXPERIMENT_TASK_NOT_CACHED;
    }
}
 

@Override
public int selectCount(String key, Set<String> groups, String profile) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("key", key);
    params.addValue("groups", groups);

    final StringBuilder extraQueryParam = new StringBuilder();
    if (!Strings.isNullOrEmpty(profile)) {
        extraQueryParam.append(" AND `profile` like :profile ");
        params.addValue("profile", profile + "%");

    }
    return namedParameterJdbcTemplate.query(String.format(SELECT_COUNT_SQL_TEMPLATE, extraQueryParam), params, SELECT_COUNT_EXTRACTOR);
}
 

/**
 * Creates a {@link MapSqlParameterSource} based on data values from the supplied {@link Visit} instance.
 */
private MapSqlParameterSource createVisitParameterSource(Visit visit) {
    return new MapSqlParameterSource()
            .addValue("id", visit.getId())
            .addValue("visit_date", visit.getDate().toDate())
            .addValue("description", visit.getDescription())
            .addValue("pet_id", visit.getPet().getId());
}
 

public long insert(long userId, long createdAt, String name, String data) {
    String sql = "INSERT INTO p_canned_report(user_id, created_at, name, data) "
                + "VALUES(:user_id, :created_at, :name, :data)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(CannedReport.USER_ID, userId);
    params.addValue(CannedReport.CREATED_AT, createdAt);
    params.addValue(CannedReport.NAME, name);
    params.addValue(CannedReport.DATA, data);

    KeyHolder keyHolder = new GeneratedKeyHolder();
    npjt.update(sql, params, keyHolder, new String[] { CannedReport.ID });
    return keyHolder.getKey().longValue();
}
 

public long insertGroup(String name) {
    String sql = "INSERT INTO p_group(name) VALUES(:name)";
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(Group.NAME, name);

    KeyHolder keyHolder = new GeneratedKeyHolder();
    npjt.update(sql, params, keyHolder, new String[] { Group.ID});
    return keyHolder.getKey().longValue();
}