Java Code Examples for org.springframework.jdbc.core.namedparam.MapSqlParameterSource#addValue()

@Deprecated
@Override
protected List<String[]> getAnnotatorDatasetCombinations(String experimentType, String matching) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("experimentType", experimentType);
    params.addValue("matching", matching);
    return this.template.query(GET_LATEST_EXPERIMENT_TASKS, params, new StringArrayRowMapper(new int[] { 1, 2 }));
}
 

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;
}
 

public Map<String, Integer> sumPointsPerUserForFavoriteCountry(Country favoriteCountry) {
	StringBuilder builder = new StringBuilder();
	builder.append("Select a.user_name, sum(a.points) ");
	builder.append("from bet a join matches b on a.match_id = b.match_id ");
	builder.append("where b.country_one = :country or b.country_two = :country ");
	builder.append("group by a.user_name ");

	MapSqlParameterSource params = new MapSqlParameterSource();
	params.addValue("country", favoriteCountry.name());

	Map<String, Integer> userPoints = new HashMap<>();
	namedParameterJdbcOperations.query(builder.toString(), params, (ResultSet rs) -> {
		String userName = rs.getString(1);
		int points = rs.getInt(2);
		if (!FredbetConstants.TECHNICAL_USERNAME.equals(userName)) {
			userPoints.put(userName, points);
		}
	});
	return userPoints;
}
 

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;
}
 

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();
}
 

@Override
public TaskExecution startTaskExecution(long executionId, String taskName,
		Date startTime, List<String> arguments, String externalExecutionId,
		Long parentExecutionId) {
	TaskExecution taskExecution = new TaskExecution(executionId, null, taskName,
			startTime, null, null, arguments, null, externalExecutionId,
			parentExecutionId);

	final MapSqlParameterSource queryParameters = new MapSqlParameterSource()
			.addValue("startTime", startTime, Types.TIMESTAMP)
			.addValue("exitCode", null, Types.INTEGER)
			.addValue("taskName", taskName, Types.VARCHAR)
			.addValue("lastUpdated", new Date(), Types.TIMESTAMP)
			.addValue("parentExecutionId", parentExecutionId, Types.BIGINT)
			.addValue("taskExecutionId", executionId, Types.BIGINT);

	String updateString = START_TASK_EXECUTION_PREFIX;

	if (externalExecutionId == null) {
		updateString += START_TASK_EXECUTION_SUFFIX;
	}
	else {
		updateString += START_TASK_EXECUTION_EXTERNAL_ID_SUFFIX;
		queryParameters.addValue("externalExecutionId", externalExecutionId,
				Types.VARCHAR);
	}

	this.jdbcTemplate.update(getQuery(updateString), queryParameters);
	insertTaskArguments(executionId, arguments);
	return taskExecution;
}
 

@Override
public void setExperimentState(int experimentTaskId, int state) {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("id", experimentTaskId);
    parameters.addValue("state", state);
    java.util.Date today = new java.util.Date();
    parameters.addValue("lastChanged", new java.sql.Timestamp(today.getTime()));
    int rows = this.template.update(SET_TASK_STATE, parameters);
    if (rows == 0) {
        LOGGER.info("Tried to update a task state [taskId={}, state={}]. The update query had no effect.",
                experimentTaskId, state);
    }
}
 

/**
 *
 * 分页查询可继承文件，满足如下条件为可被当前group继承的文件
 * <ol>
 *  <li>非本group下的文件；</li>
 *  <li>本profile或者resources下文件；</li>
 *  <li>没有被本group继承过的文件；</li>
 *  <li>被标记为可继承文件；</li>
 *  <li>没有被删除的文件；</li>
 *  <li>子环境不能继承其他子环境的配置，可以继承父环境和resources下配置</li>
 * </ol>
 */
public List<ConfigMeta> inheritableFile(String groupId, String profile, String term, long start, long offset) {
    final MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("groupId", groupId);
    parameters.addValue("profiledefault", Environment.fromProfile(profile).defaultProfile());
    parameters.addValue("profile", profile);
    parameters.addValue("resources", Environment.RESOURCES.defaultProfile());
    parameters.addValue("term", "%" + term + '%');
    parameters.addValue("inheritmask", PublicType.INHERIT_MASK);
    parameters.addValue("start", start);
    parameters.addValue("offset", offset);
    parameters.addValue("delete_num", "2");
    parameters.addValue("public_status", PublicStatus.DELETE.code());

    String sql = "select a.group_id, a.data_id, b.profile from file_public_status a" +
            " inner join config b on a.group_id = b.group_id and a.data_id = b.data_id" +
            " and (b.profile = :profile or b.profile = :resources or b.profile = :profiledefault)" +
            " and (a.group_id != :groupId) and (a.type & :inheritmask)= :inheritmask" +
            " and (a.group_id like :term or a.data_id like :term)" +
            " and b.public_status != :public_status";

    if (Strings.isNullOrEmpty(term)) {//term 为空的情况
        sql = "select a.group_id, a.data_id, b.profile from file_public_status a" +
                " inner join config b on a.group_id = b.group_id and a.data_id = b.data_id" +
                " and (b.profile = :profile or b.profile = :resources or b.profile = :profiledefault)" +
                " and (a.group_id != :groupId) and (a.type & :inheritmask)= :inheritmask" +
                " and b.public_status != :public_status";

    }
    return namedParameterJdbcTemplate.query(sql, parameters, INHERIT_CONFIG_META_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 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);
}
 

@Override
public List<PropertiesEntryDiff> selectKey(ConfigMeta meta, String key, int page, int pageSize) {
    long offset = PaginationUtil.start(page, pageSize);
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("group", meta.getGroup());
    params.addValue("dataId", meta.getDataId());
    params.addValue("profile", meta.getProfile());
    params.addValue("key", key);
    params.addValue("offset", offset);
    params.addValue("limit", pageSize);
    return namedParameterJdbcTemplate.query(SELECT_BY_KEY, params, ENTRY_ROW_MAPPER);
}
 

@Override
public List<PropertiesEntryDiff> selectByIds(List<Long> ids) {
    if (CollectionUtils.isEmpty(ids)) {
        return ImmutableList.of();
    }
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("ids", ids);
    return namedParameterJdbcTemplate.query(SELECT_BY_IDS, params, ENTRY_ROW_MAPPER);
}
 

protected void addIntResult(int taskId, String resName, int value) throws DataAccessException {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("taskId", taskId);
    parameters.addValue("resName", resName);
    parameters.addValue("value", value);
    int rows = this.template.update(INSERT_INT_RESULT, parameters);
    if (rows == 0) {
        LOGGER.info("Tried to insert a result [taskId={}, resName=\"{}\", value={}]. The update query had no effect.",
                taskId, resName, value);
    }
}
 

@Test
public void testMatchParameterValuesAndSqlInOutParameters() throws Exception {
	final String TABLE = "customers";
	final String USER = "me";
	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getUserName()).willReturn(USER);
	given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);

	List<SqlParameter> parameters = new ArrayList<>();
	parameters.add(new SqlParameter("id", Types.NUMERIC));
	parameters.add(new SqlInOutParameter("name", Types.NUMERIC));
	parameters.add(new SqlOutParameter("customer_no", Types.NUMERIC));

	MapSqlParameterSource parameterSource = new MapSqlParameterSource();
	parameterSource.addValue("id", 1);
	parameterSource.addValue("name", "Sven");
	parameterSource.addValue("customer_no", "12345XYZ");

	context.setProcedureName(TABLE);
	context.initializeMetaData(dataSource);
	context.processParameters(parameters);

	Map<String, Object> inParameters = context.matchInParameterValuesWithCallParameters(parameterSource);
	assertEquals("Wrong number of matched in parameter values", 2, inParameters.size());
	assertTrue("in parameter value missing", inParameters.containsKey("id"));
	assertTrue("in out parameter value missing", inParameters.containsKey("name"));
	assertTrue("out parameter value matched", !inParameters.containsKey("customer_no"));

	List<String> names = context.getOutParameterNames();
	assertEquals("Wrong number of out parameters", 2, names.size());

	List<SqlParameter> callParameters = context.getCallParameters();
	assertEquals("Wrong number of call parameters", 3, callParameters.size());
}
 

@Override
public Integer countPrecedingRunningTasks(int lastTaskId) {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("lastTaskId", lastTaskId);
    parameters.addValue("unfinishedState", TASK_STARTED_BUT_NOT_FINISHED_YET);
    List<Integer> result = this.template.query(GET_RUNNING_EXP_COUNT, parameters, new IntegerRowMapper());
    return result.get(0);
}
 

public List<AuditLog> findAll(int page, int pageSize, String searchValue) {
    String sql = "SELECT id, created_at, type, data "
                + "FROM p_audit_log "
                + "WHERE data LIKE :data "
                + "OR type LIKE :type "
                + "ORDER BY created_at DESC LIMIT :limit OFFSET :offset";

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("data", DaoHelper.getLikeParam(searchValue));
    params.addValue("type", DaoHelper.getLikeParam(searchValue));
    params.addValue("offset", DaoHelper.toOffset(page, pageSize));
    params.addValue("limit", DaoHelper.toLimit(pageSize));

    return npjt.query(sql, params, new AuditLogMapper());
}
 

private void connectToExperiment(String experimentId, Integer taskId) {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("id", experimentId);
    parameters.addValue("taskId", taskId);
    this.template.update(CONNECT_TASK_EXPERIMENT, parameters);
}
 

@Test
public void testMatchInParametersAndSqlTypeInfoWrapping() throws Exception {
	final String TABLE = "customers";
	final String USER = "me";

	ResultSet metaDataResultSet = mock(ResultSet.class);
	given(metaDataResultSet.next()).willReturn(true, false);
	given(metaDataResultSet.getString("TABLE_SCHEM")).willReturn(USER);
	given(metaDataResultSet.getString("TABLE_NAME")).willReturn(TABLE);
	given(metaDataResultSet.getString("TABLE_TYPE")).willReturn("TABLE");

	ResultSet columnsResultSet = mock(ResultSet.class);
	given(columnsResultSet.next()).willReturn(
			true, true, true, true, false);
	given(columnsResultSet.getString("COLUMN_NAME")).willReturn(
			"id", "name", "customersince", "version");
	given(columnsResultSet.getInt("DATA_TYPE")).willReturn(
			Types.INTEGER, Types.VARCHAR, Types.DATE, Types.NUMERIC);
	given(columnsResultSet.getBoolean("NULLABLE")).willReturn(
			false, true, true, false);

	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getDatabaseProductName()).willReturn("1.0");
	given(databaseMetaData.getUserName()).willReturn(USER);
	given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);
	given(databaseMetaData.getTables(null, null, TABLE, null)).willReturn(metaDataResultSet);
	given(databaseMetaData.getColumns(null, USER, TABLE, null)).willReturn(columnsResultSet);

	MapSqlParameterSource map = new MapSqlParameterSource();
	map.addValue("id", 1);
	map.addValue("name", "Sven");
	map.addValue("customersince", new Date());
	map.addValue("version", 0);
	map.registerSqlType("customersince", Types.DATE);
	map.registerSqlType("version", Types.NUMERIC);

	context.setTableName(TABLE);
	context.processMetaData(dataSource, new ArrayList<>(), new String[] {});

	List<Object> values = context.matchInParameterValuesWithInsertColumns(map);

	assertEquals("wrong number of parameters: ", 4, values.size());
	assertTrue("id not wrapped with type info", values.get(0) instanceof Number);
	assertTrue("name not wrapped with type info", values.get(1) instanceof String);
	assertTrue("date wrapped with type info",
			values.get(2) instanceof SqlParameterValue);
	assertTrue("version wrapped with type info",
			values.get(3) instanceof SqlParameterValue);
	verify(metaDataResultSet, atLeastOnce()).next();
	verify(columnsResultSet, atLeastOnce()).next();
	verify(metaDataResultSet).close();
	verify(columnsResultSet).close();
}
 

@Test
public void testMatchInParametersAndSqlTypeInfoWrapping() throws Exception {
	final String TABLE = "customers";
	final String USER = "me";

	ResultSet metaDataResultSet = mock(ResultSet.class);
	given(metaDataResultSet.next()).willReturn(true, false);
	given(metaDataResultSet.getString("TABLE_SCHEM")).willReturn(USER);
	given(metaDataResultSet.getString("TABLE_NAME")).willReturn(TABLE);
	given(metaDataResultSet.getString("TABLE_TYPE")).willReturn("TABLE");

	ResultSet columnsResultSet = mock(ResultSet.class);
	given(columnsResultSet.next()).willReturn(
			true, true, true, true, false);
	given(columnsResultSet.getString("COLUMN_NAME")).willReturn(
			"id", "name", "customersince", "version");
	given(columnsResultSet.getInt("DATA_TYPE")).willReturn(
			Types.INTEGER, Types.VARCHAR, Types.DATE, Types.NUMERIC);
	given(columnsResultSet.getBoolean("NULLABLE")).willReturn(
			false, true, true, false);

	given(databaseMetaData.getDatabaseProductName()).willReturn("MyDB");
	given(databaseMetaData.getDatabaseProductName()).willReturn("1.0");
	given(databaseMetaData.getUserName()).willReturn(USER);
	given(databaseMetaData.storesLowerCaseIdentifiers()).willReturn(true);
	given(databaseMetaData.getTables(null, null, TABLE, null)).willReturn(metaDataResultSet);
	given(databaseMetaData.getColumns(null, USER, TABLE, null)).willReturn(columnsResultSet);

	MapSqlParameterSource map = new MapSqlParameterSource();
	map.addValue("id", 1);
	map.addValue("name", "Sven");
	map.addValue("customersince", new Date());
	map.addValue("version", 0);
	map.registerSqlType("customersince", Types.DATE);
	map.registerSqlType("version", Types.NUMERIC);

	context.setTableName(TABLE);
	context.processMetaData(dataSource, new ArrayList<String>(), new String[] {});

	List<Object> values = context.matchInParameterValuesWithInsertColumns(map);

	assertEquals("wrong number of parameters: ", 4, values.size());
	assertTrue("id not wrapped with type info", values.get(0) instanceof Number);
	assertTrue("name not wrapped with type info", values.get(1) instanceof String);
	assertTrue("date wrapped with type info",
			values.get(2) instanceof SqlParameterValue);
	assertTrue("version wrapped with type info",
			values.get(3) instanceof SqlParameterValue);
	verify(metaDataResultSet, atLeastOnce()).next();
	verify(columnsResultSet, atLeastOnce()).next();
	verify(metaDataResultSet).close();
	verify(columnsResultSet).close();
}