Java MapSqlParameterSource類代碼示例

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
private static void assignData(LineageNodeLite node) {
    List<Map<String, Object>> rows = null;
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("urn", node.urn);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    rows = namedParameterJdbcTemplate.queryForList(GET_DATA_ATTR, parameters);

    for (Map<String, Object> row : rows) {
        // node only knows id, level, and urn, assign all other attributes
        JsonNode prop = Json.parse((String) row.get("properties"));
        node.description = (prop.has("description")) ? prop.get("description").asText() : "null";
        node.source = (String) row.get("source");
        node.storage_type = (String) row.get("dataset_type"); // what the js calls storage_type, the sql calls dataset_type
        node.dataset_type = (String) row.get("dataset_type");

        // check wh_property for a user specified color, use some generic defaults if nothing found
        //node.color = getColor(node.urn, node.node_type);

        //node.abstracted_path = getPostfix(node.urn);

        // set things to show up in tooltip
        node._sort_list.add("abstracted_path");
        node._sort_list.add("storage_type");
    }
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
@Override
@Transactional
public void insertStatsDirLocalSize(Map<Long, Long> dirLocalSize) throws SaodException {
	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

	List<MapSqlParameterSource> batchArgs = new ArrayList<>();

	for (Entry<Long, Long> e : dirLocalSize.entrySet()) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("node_id", e.getKey());
		parameters.addValue("local_size", e.getValue());
		batchArgs.add(parameters);
	}

	String query = sqlQueries.getQuery("insert_stats_dir_local_size.sql");
	jdbcNamesTpl.batchUpdate(query, batchArgs.toArray(new MapSqlParameterSource[dirLocalSize.size()]));
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
/**
 * Executes a given procedure against the datasource. 
 * @param procedureName The name of the procedure to execute.
 * @param parameters The parameters for the procedure.
 * @return The Map of returned values from the procedure.
 */
public Map<String, ?> executeProcedure(String procedureName, Map<String, ?> parameters)
{
    SimpleJdbcCall call = new SimpleJdbcCall(this.datasource).withSchemaName("lodbot").withProcedureName(procedureName);
    SqlParameterSource callParameters = new MapSqlParameterSource(parameters);
    return call.execute(callParameters);
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
private static void assignApp(LineageNodeLite node) {
    List<Map<String, Object>> rows = null;
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("urn", node.urn);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    rows = namedParameterJdbcTemplate.queryForList(GET_DATA_ATTR, parameters);

    for (Map<String, Object> row : rows) {
        // node only knows id, level, and urn, assign all other attributes

        // stored in dict_dataset, so has those fields
        JsonNode prop = Json.parse((String) row.get("properties"));

        // properties is a JsonNode, extract what we want out of it
        node.description = (prop.has("description")) ? prop.get("description").asText() : "null";
        node.app_code = (prop.has("app_code")) ? prop.get("app_code").asText() : "null";

        // check wh_property for a user specified color, use some generic defaults if nothing found
        //node.color = getColor(node.urn, node.node_type);

        // set things to show up in tooltip
        node._sort_list.add("app_code");
        node._sort_list.add("description");
    }
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
private static void assignDB(LineageNodeLite node) {
    List<Map<String, Object>> rows = null;
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("urn", node.urn);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    rows = namedParameterJdbcTemplate.queryForList(GET_DATA_ATTR, parameters);
    // node only knows id, level, and urn, assign all other attributes

    for (Map<String, Object> row : rows) {
        JsonNode prop = Json.parse((String) row.get("properties"));
        node.description = (prop.has("description")) ? prop.get("description").asText() : "null";
        node.jdbc_url = (prop.has("jdbc_url")) ? prop.get("jdbc_url").asText() : "null";
        node.db_code = (prop.has("db_code")) ? prop.get("db_code").asText() : "null";

        // check wh_property for a user specified color, use some generic defaults if nothing found
        //node.color = getColor(node.urn, node.node_type);

        // set things to show up in tooltip
        node._sort_list.add("db_code");
        //node._sort_list.add("last_modified");
    }
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
private static void assignGeneral(LineageNodeLite node) {
    List<Map<String, Object>> rows = null;
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("urn", node.urn);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    rows = namedParameterJdbcTemplate.queryForList(GET_DATA_ATTR, parameters);


    for (Map<String, Object> row : rows) {
        node.name = (String) row.get("name");
        node.schema = (String) row.get("schema");

        // check wh_property for a user specified color, use some generic defaults if nothing found
        node.color = getNodeColor(node.urn, node.node_type);

        // set things to show up in tooltip
        node._sort_list.add("urn");
        node._sort_list.add("name");
    }
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
public <T> T selectByPrimaryKey(Object primaryKey, Class<T> entityClass) {
    Entity entity=getEntity(entityClass);
    Entity.Column primaryKeyColumn=entity.getPrimaryKey();
    if (primaryKey == null) {
        throw new RuntimeException("沒有指定主鍵");
    }
    final StringBuilder sql = new StringBuilder();
    sql.append(SqlHelper.selectFromTable(entity.getTableName()));
    sql.append(SqlHelper.whereClause(Collections.singleton(primaryKeyColumn)));
    System.out.println(sql.toString());
    List<T> resultList=jdbcTemplate.query(sql.toString(), new MapSqlParameterSource(primaryKeyColumn.getName(), primaryKey), new BeanPropertyRowMapper<>(entityClass));
    if (!CollectionUtils.isEmpty(resultList)) {
        return resultList.get(0);
    }
    return null;
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
/**
 * Поставить задачу в очередь на выполнение
 *
 * @param location      местоположение очереди
 * @param enqueueParams данные вставляемой задачи
 * @return идентфикатор (sequence id) вставленной задачи
 */
public long enqueue(@Nonnull QueueLocation location, @Nonnull EnqueueParams<String> enqueueParams) {
    requireNonNull(location);
    requireNonNull(enqueueParams);
    return jdbcTemplate.queryForObject(String.format(
            "INSERT INTO %s(queue_name, task, process_time, log_timestamp, actor) VALUES " +
                    "(:queueName, :task, now() + :executionDelay * INTERVAL '1 SECOND', " +
                    ":correlationId, :actor) RETURNING id",
            location.getTableName()),
            new MapSqlParameterSource()
                    .addValue("queueName", location.getQueueId().asString())
                    .addValue("task", enqueueParams.getPayload())
                    .addValue("executionDelay", enqueueParams.getExecutionDelay().getSeconds())
                    .addValue("correlationId", enqueueParams.getCorrelationId())
                    .addValue("actor", enqueueParams.getActor()),
            Long.class);
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
@Override
public MapSqlParameterSource getParamSource(W segment, Timestamp now) throws SQLException {
	
	MapSqlParameterSource args = super.getParamSource(segment, now);		
	args.addValue("maxSpeedTow", segment.getMaxSpeedTow());
	args.addValue("maxSpeedBkw", segment.getMaxSpeedBkw());
	args.addValue("speedCalcTow", segment.getSpeedCalcTow());
	args.addValue("speedCalcBkw", segment.getSpeedCalcBkw());
	args.addValue("lanesTow", segment.getLanesTow());
	args.addValue("lanesBkw", segment.getLanesBkw());
	args.addValue("frc", segment.getFrc().getValue());
	 if (segment.getFormOfWay() != null) {
		 args.addValue("formOfWay", segment.getFormOfWay().getValue());
   	 } else {
   		 args.addValue("formOfWay", FormOfWay.NOT_APPLICABLE.getValue());
   	 }		 
	Connection con = getConnection();
	args.addValue("accessTow",  convertToArray(con, segment.getAccessTow()));
	args.addValue("accessBkw",  convertToArray(con, segment.getAccessBkw()));
	
	args.addValue("tunnel", segment.isTunnel());
	args.addValue("bridge", segment.isBridge());
	args.addValue("urban", segment.isUrban());
	
	return args;
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
@Override
public MapSqlParameterSource getParamSource(W segment, Timestamp now) throws SQLException {
	MapSqlParameterSource args = new MapSqlParameterSource();
	args.addValue("id", segment.getId());
	args.addValue("geometry","SRID=4326;"+wktWriter.write(segment.getGeometry()));
	args.addValue("name", segment.getName());
	args.addValue("length", segment.getLength());
	args.addValue("streetType", segment.getStreetType());
	args.addValue("wayId", segment.getWayId());
	args.addValue("startNodeId", segment.getStartNodeId());
	args.addValue("startNodeIndex", segment.getStartNodeIndex());
	args.addValue("endNodeId", segment.getEndNodeId());
	args.addValue("endNodeIndex", segment.getEndNodeIndex());
	args.addValue("timestamp", now);
	args.addValue("tags", segment.getTags());		
	return args;
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
@Override
@Transactional(readOnly=false)
public long saveGraph(String graphName) {
	Object[] args = new Object[1];
	args[0] = graphName;
	
	Map<String, Object>  params = new HashMap<String, Object>(); 
	params.put("name", graphName);
	MapSqlParameterSource sqlParameterSource = new MapSqlParameterSource(params);
	KeyHolder keyHolder = new GeneratedKeyHolder();

	getNamedParameterJdbcTemplate().update("INSERT INTO " + schema + "waygraphs (name) VALUES (:name)", 
			sqlParameterSource, keyHolder, new String[] {"id"});

	return Long.class.cast(keyHolder.getKey());
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
private SqlParameterSource getSqlParameterByModel(User user) {

		// Unable to handle List<String> or Array
		// BeanPropertySqlParameterSource

		MapSqlParameterSource paramSource = new MapSqlParameterSource();
		paramSource.addValue("id", user.getId());
		paramSource.addValue("name", user.getName());
		paramSource.addValue("email", user.getEmail());
		paramSource.addValue("login", user.getLogin());
		paramSource.addValue("address", user.getAddress());
		paramSource.addValue("password", user.getPassword());
		paramSource.addValue("newsletter", user.isNewsletter());

		// join String
		paramSource.addValue("framework", convertListToDelimitedString(user.getFramework()));
		paramSource.addValue("sex", user.getSex());
		paramSource.addValue("number", user.getNumber());
		paramSource.addValue("country", user.getCountry());
		paramSource.addValue("skill", convertListToDelimitedString(user.getSkill()));

		return paramSource;
	}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
@Override
public void persist(EventLog eventLog) {
    Timestamp now = toSqlTimestamp(Instant.now());
    MapSqlParameterSource namedParameterMap = new MapSqlParameterSource();
    namedParameterMap.addValue("eventType", eventLog.getEventType());
    namedParameterMap.addValue("eventBodyData", eventLog.getEventBodyData());
    namedParameterMap.addValue("flowId", eventLog.getFlowId());
    namedParameterMap.addValue("created", now);
    namedParameterMap.addValue("lastModified", now);
    namedParameterMap.addValue("lockedBy", eventLog.getLockedBy());
    namedParameterMap.addValue("lockedUntil", eventLog.getLockedUntil());
    GeneratedKeyHolder generatedKeyHolder = new GeneratedKeyHolder();
    jdbcTemplate.update(
        "INSERT INTO " +
            "    nakadi_events.event_log " +
            "    (event_type, event_body_data, flow_id, created, last_modified, locked_by, locked_until) " +
            "VALUES " +
            "    (:eventType, :eventBodyData, :flowId, :created, :lastModified, :lockedBy, :lockedUntil)",
        namedParameterMap,
        generatedKeyHolder
    );

    eventLog.setId((Integer) generatedKeyHolder.getKeys().get("id"));
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
private void insertPrevFireKeys(HistorianEntry entry) {
    if(entry.getPreviousTriggerFireKeys() == null || entry.getPreviousTriggerFireKeys().isEmpty()) {
        return;
    }

    StringBuilder query = new StringBuilder(PREV_FIRE_KEYS_INERT_QUERY);
    MapSqlParameterSource paramMap = new MapSqlParameterSource()
            .addValue("schedName", schedulerName)
            .addValue("contextKey", entry.getContextKey())
            .addValue("fireKey", entry.getFireKey());

    int size = entry.getPreviousTriggerFireKeys().size();
    for(int i = 0; i < size; i++) {
        String prevFireKeyVar = "prevFireKey" + i;
        query.append("(:schedName,:contextKey,:fireKey,:").append(prevFireKeyVar).append("),");
        paramMap.addValue(prevFireKeyVar, entry.getPreviousTriggerFireKeys().get(i));
    }

    query.deleteCharAt(query.length() - 1);

    namedParameterJdbcTemplate.update(query.toString(), paramMap);
}
 

import org.springframework.jdbc.core.namedparam.MapSqlParameterSource; //導入依賴的package包/類
private MapSqlParameterSource getParametersMap(HistorianEntry entry) {
    return new MapSqlParameterSource()
            .addValue("schedName", schedulerName)
            .addValue("schedInstanceId", entry.getSchedulerInstanceId())
            .addValue("contextKey", entry.getContextKey())
            .addValue("fireKey", entry.getFireKey())
            .addValue("triggerName", entry.getTriggerKey().getName())
            .addValue("triggerGroup", entry.getTriggerKey().getGroup())
            .addValue("prevTriggersFireKeys", writeValueAsBytes(entry.getPreviousTriggerFireKeys()))
            .addValue("startTime", Date.from(entry.getStartTime()))
            .addValue("endTime", entry.getEndTime() == null ? null : Date.from(entry.getEndTime()))
            .addValue("input", writeValueAsBytes(entry.getInput()))
            .addValue("output", writeValueAsBytes(entry.getOutput()))
            .addValue("runStatus", entry.getRunStatus().name())
            .addValue("exception", writeValueAsBytes(entry.getException()));
}