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

The following are top voted examples for showing how to use org.springframework.jdbc.core.namedparam.MapSqlParameterSource. These examples are extracted from open source projects. You can vote up the examples you like and your votes will be used in our system to generate more good examples.
Example 1
Project: premier-wherehows   File: LineageDAOLite.java   View source code 7 votes vote down vote up
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");
    }
}
 
Example 2
Project: stats-alfresco-on-database   File: LocalDaoImpl.java   View source code 7 votes vote down vote up
@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()]));
}
 
Example 3
Project: lodbot   File: CoreDao.java   View source code 6 votes vote down vote up
/**
 * 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);
}
 
Example 4
Project: premier-wherehows   File: LineageDAOLite.java   View source code 6 votes vote down vote up
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");
    }
}
 
Example 5
Project: premier-wherehows   File: LineageDAOLite.java   View source code 6 votes vote down vote up
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");
    }
}
 
Example 6
Project: common-dao   File: SelectSupport.java   View source code 6 votes vote down vote up
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;
}
 
Example 7
Project: db-queue   File: QueueDao.java   View source code 6 votes vote down vote up
/**
 * Поставить задачу в очередь на выполнение
 *
 * @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);
}
 
Example 8
Project: graphium   File: WayGraphWriteDaoImpl.java   View source code 6 votes vote down vote up
@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;
}
 
Example 9
Project: graphium   File: WayBaseGraphWriteDaoImpl.java   View source code 6 votes vote down vote up
@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;
}
 
Example 10
Project: graphium   File: WayGraphVersionMetadataDaoImpl.java   View source code 6 votes vote down vote up
@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());
}
 
Example 11
Project: siga   File: UserDaoImpl.java   View source code 6 votes vote down vote up
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;
	}
 
Example 12
Project: nakadi-producer-spring-boot-starter   File: EventLogRepositoryImpl.java   View source code 6 votes vote down vote up
@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"));
}
 
Example 13
Project: taboola-cronyx   File: StdHistorianDAO.java   View source code 6 votes vote down vote up
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);
}
 
Example 14
Project: taboola-cronyx   File: StdHistorianDAO.java   View source code 6 votes vote down vote up
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()));
}
 
Example 15
Project: NGB-master   File: ProjectDao.java   View source code 6 votes vote down vote up
/**
 * Adds an item to a project, specified by ID
 * @param projectId {@code Long} ID of a project
 * @param biologicalItemId {@code Long} ID of an item to add
 */
@Transactional(propagation = Propagation.MANDATORY)
public void addProjectItem(long projectId, long biologicalItemId) {
    Project project = loadProject(projectId);
    Assert.notNull(project, MessageHelper.getMessage(MessagesConstants.ERROR_PROJECT_NOT_FOUND, projectId));

    long newId = daoHelper.createId(projectItemSequenceName);

    Number countNumber = getJdbcTemplate().queryForObject(loadProjectItemsMaxNumberQuery,
                                                          new SingleColumnRowMapper<>(), projectId);
    Integer count = 1;
    if (countNumber != null) {
        count = countNumber.intValue() + 1;
    }

    MapSqlParameterSource params = new MapSqlParameterSource();

    params.addValue(ProjectItemParameters.PROJECT_ITEM_ID.name(), newId);
    params.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId);
    params.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), biologicalItemId);
    params.addValue(ProjectItemParameters.ORDINAL_NUMBER.name(), count);
    params.addValue(ProjectItemParameters.HIDDEN.name(), false);

    getNamedParameterJdbcTemplate().update(addProjectItemQuery, params);
}
 
Example 16
Project: NGB-master   File: ProjectDao.java   View source code 6 votes vote down vote up
/**
 * Hides a project item, specified by ID. Hidden item won't be shown on UI.
 * @param projectId {@code Long} ID of a project
 * @param items a {@code List&lt;ProjectItem&gt;} list of project items to update hidden status
 */
@Transactional(propagation = Propagation.MANDATORY)
public void hideProjectItems(long projectId, List<ProjectItem> items) {
    ArrayList<MapSqlParameterSource> params = new ArrayList<>(items.size());

    for (ProjectItem item : items) {
        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId);
        param.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(),
                getBioDataItemId(item.getBioDataItem()));
        param.addValue(ProjectItemParameters.HIDDEN.name(), item.getHidden() != null && item.getHidden());

        params.add(param);

    }

    getNamedParameterJdbcTemplate().batchUpdate(hideProjectItemQuery, params.toArray(new
            MapSqlParameterSource[items.size()]));
}
 
Example 17
Project: NGB-master   File: BiologicalDataItemDao.java   View source code 6 votes vote down vote up
/**
 * Loads a List of BiologicalDataItem from the database by their IDs
 * @param ids List of IDs of BiologicalDataItem instances
 * @return List of BiologicalDataItem, matching specified IDs
 */
@Transactional(propagation = Propagation.MANDATORY)
public List<BiologicalDataItem> loadBiologicalDataItemsByIds(List<Long> ids) {
    if (ids == null || ids.isEmpty()) {
        return Collections.emptyList();
    }

    Long listId = daoHelper.createTempLongList(ids);

    final MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(BiologicalDataItemParameters.BIO_DATA_ITEM_ID.name(), listId);

    List<BiologicalDataItem> items = getNamedParameterJdbcTemplate().query(loadBiologicalDataItemsByIdsQuery,
            params, getRowMapper());

    daoHelper.clearTempList(listId);

    return items;
}
 
Example 18
Project: NGB-master   File: BiologicalDataItemDao.java   View source code 6 votes vote down vote up
/**
 * Creates a MapSqlParameterSource with common FeatureFile's ancestor's fields for future use in DB queries
 * @param idFieldName a name of ID fields of FeatureFile's ancestor's
 * @param featureFile a FeatureFile's ancestor's entity, which fields to add to parameters
 * @return a MapSqlParameterSource with common FeatureFile's ancestor's fields
 */
public static MapSqlParameterSource getLinkedTableParameters(String idFieldName, FeatureFile featureFile) {
    MapSqlParameterSource params = new MapSqlParameterSource();

    params.addValue(idFieldName, featureFile.getId());
    params.addValue(BiologicalDataItemParameters.BIO_DATA_ITEM_ID.name(), featureFile.getBioDataItemId());
    params.addValue(REFERENCE_GENOME_ID.name(), featureFile.getReferenceId());
    params.addValue(INDEX_ID.name(), featureFile.getIndex() != null ? featureFile.getIndex().getId() : null);
    params.addValue(COMPRESSED.name(), featureFile.getCompressed());

    if (featureFile instanceof GeneFile) {
        GeneFile geneFile = (GeneFile) featureFile;
        params.addValue(EXTERNAL_DB_TYPE_ID.name(), geneFile.getExternalDBType() != null ?
                geneFile.getExternalDBType().getId() : null);
        params.addValue(EXTERNAL_DB_ID.name(), geneFile.getExternalDB() != null ?
                geneFile.getExternalDB().getId() : null);
        params.addValue(EXTERNAL_DB_ORGANISM.name(), geneFile.getExternalDBOrganism());
    }

    return params;
}
 
Example 19
Project: NGB-master   File: SegFileDao.java   View source code 6 votes vote down vote up
/**
 * Saves sample metadata to the database.
 *
 * @param samples   {@code List&lt;Sample&gt;} samples to save
 * @param segFileId {@code long} file ID to save samples for
 */
@Transactional(propagation = Propagation.MANDATORY)
public void createSamples(List<SegSample> samples, long segFileId) {
    List<Long> sampleIds = daoHelper.createIds(segSampleSequenceName, samples.size());
    for (int i = 0; i < samples.size(); i++) {
        samples.get(i).setId(sampleIds.get(i));
    }

    final List<MapSqlParameterSource> params = new ArrayList<>();
    for (SegSample sample : samples) {

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue(SegSampleParameters.SEG_SAMPLE_ID.name(), sample.getId());
        param.addValue(SegSampleParameters.SEG_ID.name(), segFileId);
        param.addValue(SegSampleParameters.SAMPLE_NAME.name(), sample.getName());
        params.add(param);
    }

    getNamedParameterJdbcTemplate().batchUpdate(createSamplesForFileQuery, params.toArray(new
            MapSqlParameterSource[params.size()]));
}
 
Example 20
Project: NGB-master   File: DaoHelper.java   View source code 6 votes vote down vote up
/**
 * Returns {@code List} which contains next values for sequence with the given name.
 *
 * @param sequenceName {@code String} specifies full-qualified name of sequence which
 *                     next values should be returned by a call
 * @param count        int specifies the number of next values are should be retrieved
 * @return {@code List} list of next values for sequence; list.size() == count
 */
@Transactional(propagation = Propagation.MANDATORY)
public List<Long> createIds(final String sequenceName, final int count) {
    Assert.isTrue(StringUtils.isNotBlank(sequenceName));
    if (count == 0) {
        return Collections.emptyList();
    }
    // creates a new temporary list: list.size() == count
    final List<Long> rows = LongStream.range(0L, count)
        .collect(LinkedList::new, LinkedList::add, LinkedList::addAll);
    final Long listId = createTempLongList(rows);
    // generates next values for sequence with the given name
    final MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(HelperParameters.LIST_ID.name(), listId);
    params.addValue(HelperParameters.SEQUENCE_NAME.name(), sequenceName.trim());
    final List<Long> list = getNamedParameterJdbcTemplate().queryForList(createIdsQuery, params, Long.class);
    // clears a temporary list
    clearTempList(listId);
    return list;
}
 
Example 21
Project: NGB-master   File: DaoHelper.java   View source code 6 votes vote down vote up
/**
 * Creates a new temporary list of {@code Long} values. The created temporary list is
 * identified by the given ID. If a list has been created successfully, it will be filled
 * in by {@code Collection} of provided {@code Long} values.
 *
 * @param listId {@code Long} represents unique ID that is used to identify a temporary list
 * @param list   {@code Collection} specifies collection of {@code Long} values that should be
 *               associated with a temporary list if this call is succeeded
 * @return {@code Long} represents unique ID of a temporary list that has been created after
 * this call
 * @throws IllegalArgumentException will be thrown if <tt>listId</tt> or <tt>list</tt> are
 *                                  <tt>null</tt>, or the given <tt>list</tt> is empty
 */
@Transactional(propagation = Propagation.MANDATORY)
public Long createTempLongList(final Long listId, final Collection<Long> list) {
    Assert.notNull(listId);
    Assert.isTrue(CollectionUtils.isNotEmpty(list));
    // creates a new local temporary table if it doesn't exists to handle temporary lists
    getJdbcTemplate().update(createTemporaryListQuery);
    // fills in a temporary list by given values
    int i = 0;
    final Iterator<Long> iterator = list.iterator();
    final MapSqlParameterSource[] batchArgs = new MapSqlParameterSource[list.size()];
    while (iterator.hasNext()) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(HelperParameters.LIST_ID.name(), listId);
        params.addValue(HelperParameters.LIST_VALUE.name(), iterator.next());
        batchArgs[i] = params;
        i++;
    }
    getNamedParameterJdbcTemplate().batchUpdate(insertTemporaryListItemQuery, batchArgs);
    return listId;
}
 
Example 22
Project: NGB-master   File: DaoHelper.java   View source code 6 votes vote down vote up
@Transactional(propagation = Propagation.MANDATORY)
public Long createTempStringList(final Long listId, final Collection<String> list) {
    Assert.notNull(listId);
    Assert.isTrue(CollectionUtils.isNotEmpty(list));
    // creates a new local temporary table if it doesn't exists to handle temporary lists
    getJdbcTemplate().update(createTemporaryStringListQuery);
    // fills in a temporary list by given values
    int i = 0;
    final Iterator<String> iterator = list.iterator();
    final MapSqlParameterSource[] batchArgs = new MapSqlParameterSource[list.size()];
    while (iterator.hasNext()) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(HelperParameters.LIST_ID.name(), listId);
        params.addValue(HelperParameters.LIST_VALUE.name(), iterator.next());
        batchArgs[i] = params;
        i++;
    }
    getNamedParameterJdbcTemplate().batchUpdate(insertTemporaryStringListItemQuery, batchArgs);
    return listId;
}
 
Example 23
Project: NGB-master   File: DaoHelper.java   View source code 6 votes vote down vote up
@Transactional(propagation = Propagation.MANDATORY)
public Long createTempList(final Long listId, final Collection<? extends BaseEntity> list) {
    Assert.notNull(listId);
    Assert.isTrue(CollectionUtils.isNotEmpty(list));
    // creates a new local temporary table if it doesn't exists to handle temporary lists
    getJdbcTemplate().update(createTemporaryListQuery);
    // fills in a temporary list by given values
    int i = 0;
    final Iterator<? extends BaseEntity> iterator = list.iterator();
    final MapSqlParameterSource[] batchArgs = new MapSqlParameterSource[list.size()];
    while (iterator.hasNext()) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(HelperParameters.LIST_ID.name(), listId);
        params.addValue(HelperParameters.LIST_VALUE.name(), iterator.next().getId());
        batchArgs[i] = params;
        i++;
    }
    getNamedParameterJdbcTemplate().batchUpdate(insertTemporaryListItemQuery, batchArgs);
    return listId;
}
 
Example 24
Project: NGB-master   File: BookmarkDao.java   View source code 6 votes vote down vote up
private static MapSqlParameterSource[] getParameters(List<BiologicalDataItem> items, long bookmarkId, DaoHelper
        daoHelper, String bookmarkItemSequenceName) {
    MapSqlParameterSource[] params = new MapSqlParameterSource[items.size()];

    List<Long> ids = daoHelper.createIds(bookmarkItemSequenceName, items.size());

    for (int i = 0; i < items.size(); i++) {
        MapSqlParameterSource param = new MapSqlParameterSource();

        param.addValue(BOOKMARK_ITEM_ID.name(), ids.get(i));
        param.addValue(BOOKMARK_ID.name(), bookmarkId);
        param.addValue(BIOLOGICAL_ITEM_ID.name(), BiologicalDataItem.getBioDataItemId(
                items.get(i)));

        params[i] = param;
    }

    return params;
}
 
Example 25
Project: NGB-master   File: ReferenceGenomeDao.java   View source code 6 votes vote down vote up
/**
 * Persists a {@code Reference} entity in database with a specified ID
 * @param reference to persist
 * @param referenceId ID for the reference
 * @return saved {@code Reference} instance
 */
@Transactional(propagation = Propagation.MANDATORY)
public Reference createReferenceGenome(final Reference reference, final long referenceId) {

    reference.setBioDataItemId(reference.getId());
    reference.setId(referenceId);

    final MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(GenomeParameters.REFERENCE_GENOME_ID.name(), reference.getId());
    params.addValue(GenomeParameters.BIO_DATA_ITEM_ID.name(), reference.getBioDataItemId());
    params.addValue(GenomeParameters.SIZE.name(), reference.getSize());
    params.addValue(GenomeParameters.INDEX_ID.name(), reference.getIndex().getId());
    params.addValue(GenomeParameters.GENE_ITEM_ID.name(), reference.getGeneFile() != null ?
                                                          reference.getGeneFile().getId() : null);

    getNamedParameterJdbcTemplate().update(createReferenceGenomeQuery, params);
    return reference;
}
 
Example 26
Project: NGB-master   File: ReferenceGenomeDao.java   View source code 6 votes vote down vote up
/**
 * Saves {@code List} of {@code Chromosome} entities with a specified ID in the database
 * as one reference
 * @param referenceId for the chromosomes
 * @param chromosomes {@code List} of {@code Chromosome} entities to store int the database
 * @return an array containing the numbers of rows affected by each update in the batch
 */
@Transactional(propagation = Propagation.MANDATORY)
public int[] saveChromosomes(final Long referenceId, final List<Chromosome> chromosomes) {
    final int count = chromosomes.size();
    final List<Long> chromosomeIds = daoHelper.createIds(chromosomeSequenceName, count);
    final MapSqlParameterSource[] batchArgs = new MapSqlParameterSource[count];
    for (int i = 0; i < count; i++) {
        final Chromosome chromosome = chromosomes.get(i);
        chromosome.setId(chromosomeIds.get(i));
        chromosome.setReferenceId(referenceId);
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(GenomeParameters.NAME.name(), chromosome.getName());
        params.addValue(GenomeParameters.SIZE.name(), chromosome.getSize());
        params.addValue(GenomeParameters.PATH.name(), chromosome.getPath());
        params.addValue(GenomeParameters.HEADER.name(), chromosome.getHeader());
        params.addValue(GenomeParameters.CHROMOSOME_ID.name(), chromosome.getId());
        params.addValue(GenomeParameters.REFERENCE_GENOME_ID.name(), chromosome.getReferenceId());
        batchArgs[i] = params;
    }
    return getNamedParameterJdbcTemplate().batchUpdate(createChromosomeQuery, batchArgs);
}
 
Example 27
Project: NGB   File: ProjectDao.java   View source code 6 votes vote down vote up
/**
 * Adds an item to a project, specified by ID
 * @param projectId {@code Long} ID of a project
 * @param biologicalItemId {@code Long} ID of an item to add
 */
@Transactional(propagation = Propagation.MANDATORY)
public void addProjectItem(long projectId, long biologicalItemId) {
    Project project = loadProject(projectId);
    Assert.notNull(project, MessageHelper.getMessage(MessagesConstants.ERROR_PROJECT_NOT_FOUND, projectId));

    long newId = daoHelper.createId(projectItemSequenceName);

    Number countNumber = getJdbcTemplate().queryForObject(loadProjectItemsMaxNumberQuery,
                                                          new SingleColumnRowMapper<>(), projectId);
    Integer count = 1;
    if (countNumber != null) {
        count = countNumber.intValue() + 1;
    }

    MapSqlParameterSource params = new MapSqlParameterSource();

    params.addValue(ProjectItemParameters.PROJECT_ITEM_ID.name(), newId);
    params.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId);
    params.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), biologicalItemId);
    params.addValue(ProjectItemParameters.ORDINAL_NUMBER.name(), count);
    params.addValue(ProjectItemParameters.HIDDEN.name(), false);

    getNamedParameterJdbcTemplate().update(addProjectItemQuery, params);
}
 
Example 28
Project: oma-riista-web   File: PublicHarvestPivotTableFeature.java   View source code 6 votes vote down vote up
private List<PivotTableRow> executeCountQuery(
        final Interval interval, final Optional<GameSpecies> gameSpeciesOpt, final Optional<Organisation> rkaOpt) {

    final String queryStr = nativeQueryForHarvestCounts(gameSpeciesOpt.isPresent(), rkaOpt.isPresent());

    final MapSqlParameterSource queryParams = new MapSqlParameterSource();
    queryParams.addValue("beginTime", interval.getStart().toDate(), Types.TIMESTAMP);
    queryParams.addValue("endTime", interval.getEnd().toDate(), Types.TIMESTAMP);

    if (gameSpeciesOpt.isPresent()) {
        queryParams.addValue("gameSpeciesId", gameSpeciesOpt.get().getId());
    }
    if (rkaOpt.isPresent()) {
        queryParams.addValue("rkaId", rkaOpt.get().getId());
    }

    return jdbcTemplate.query(queryStr, queryParams, (resultSet, i) -> new PivotTableRow(resultSet));
}
 
Example 29
Project: oma-riista-web   File: AreaPrintFeature.java   View source code 6 votes vote down vote up
private GeometryAndSize getZoneFeature(final Long zoneId) {
    final MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("worldBounds", FINNISH_BOUNDS)
            .addValue("zoneId", zoneId);

    // Calculate inverted area
    final String sql = "SELECT computed_area_size, water_area_size, ST_AsGeoJSON(" +
            "ST_Difference(ST_Buffer(ST_Envelope(ST_GeomFromText(:worldBounds, 3067)), 0), geom)) AS geom" +
            " FROM zone WHERE zone_id = :zoneId;";

    return namedParameterJdbcTemplate.queryForObject(sql, params, (rs, i) -> {
        final GeoJsonObject geom = GISUtils.parseGeoJSONGeometry(objectMapper, rs.getString("geom"));
        final double totalAreaSize = rs.getDouble("computed_area_size");
        final double waterAreaSize = rs.getDouble("water_area_size");

        return new GeometryAndSize(geom, totalAreaSize, waterAreaSize);
    });
}
 
Example 30
Project: oma-riista-web   File: GISZoneRepositoryImpl.java   View source code 6 votes vote down vote up
@Override
@Transactional(readOnly = true)
public double[] getBounds(final long zoneId, final GISUtils.SRID srid) {
    final String sql = "WITH extent AS " +
            "(SELECT ST_Extent(ST_Transform(geom, :srid)) AS e FROM zone WHERE zone_id = :zoneId)" +
            " SELECT ST_XMin(e) AS xmin, ST_YMin(e) AS ymin, ST_XMax(e) AS xmax, ST_YMax(e) AS ymax FROM extent";

    return namedParameterJdbcTemplate.queryForObject(sql, new MapSqlParameterSource()
            .addValue("srid", srid.getValue())
            .addValue("zoneId", zoneId), (rs, rowNum) -> new double[]{
            rs.getDouble("xmin"),
            rs.getDouble("ymin"),
            rs.getDouble("xmax"),
            rs.getDouble("ymax")
    });
}
 
Example 31
Project: oma-riista-web   File: GISZoneRepositoryImpl.java   View source code 6 votes vote down vote up
@Override
@Transactional(readOnly = true)
public List<Geometry> loadSplicedGeometries(final Collection<Long> zoneIds) {
    if (zoneIds.isEmpty()) {
        return emptyList();
    }

    final GeometryFactory geometryFactory = GISUtils.getGeometryFactory(GISUtils.SRID.ETRS_TM35FIN);
    final WKBReader wkbReader = new WKBReader(geometryFactory);
    final MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("chunkSize", 2048)
            .addValue("zoneIds", zoneIds);

    return namedParameterJdbcTemplate.query(
            "SELECT ST_AsBinary(ST_SubDivide(geom, :chunkSize)) AS geom FROM zone WHERE zone_id IN (:zoneIds)",
            params, (resultSet, i) -> {
                final byte[] wkb = resultSet.getBytes("geom");

                try {
                    return wkbReader.read(wkb);
                } catch (ParseException e) {
                    throw new RuntimeException(e);
                }
            });
}
 
Example 32
Project: oma-riista-web   File: GetCombinedFeatureCollectionQuery.java   View source code 6 votes vote down vote up
@Nonnull
public FeatureCollection execute(final Set<Long> zoneIds, final GISUtils.SRID srid, final double simplifyAmount) {
    final FeatureCollection featureCollection = new FeatureCollection();
    featureCollection.setCrs(srid.getGeoJsonCrs());

    if (zoneIds.isEmpty()) {
        return featureCollection;
    }

    final MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("zoneIds", zoneIds)
            .addValue("crs", srid.getValue())
            .addValue("simplify", simplifyAmount);

    final List<Feature> features = jdbcOperations.query(SQL, params, (rs, i) -> mapResultToFeature(rs));

    if (features != null) {
        featureCollection.setFeatures(features);
    }

    return featureCollection;
}
 
Example 33
Project: oma-riista-web   File: GetExternalFeatureCollectionQuery.java   View source code 6 votes vote down vote up
public FeatureCollection execute(final long zoneId, final GISUtils.SRID srid) {
    final MapSqlParameterSource params = new MapSqlParameterSource()
            .addValue("zoneId", zoneId)
            .addValue("crs", srid.getValue());

    final List<Feature> features = jdbcOperations.query(SQL, params, (resultSet, i) -> {
        final Feature feature = new Feature();
        feature.setId(Long.toString(zoneId));
        feature.setProperty(GeoJSONConstants.PROPERTY_NUMBER, resultSet.getString("property_identifier"));
        feature.setGeometry(GISUtils.parseGeoJSONGeometry(objectMapper, resultSet.getString("geom")));

        return feature;
    });

    final FeatureCollection featureCollection = new FeatureCollection();

    if (features != null) {
        featureCollection.setFeatures(features);
    }

    return featureCollection;
}
 
Example 34
Project: NGB   File: ReferenceGenomeDao.java   View source code 6 votes vote down vote up
/**
 * Persists a {@code Reference} entity in database with a specified ID
 * @param reference to persist
 * @param referenceId ID for the reference
 * @return saved {@code Reference} instance
 */
@Transactional(propagation = Propagation.MANDATORY)
public Reference createReferenceGenome(final Reference reference, final long referenceId) {

    reference.setBioDataItemId(reference.getId());
    reference.setId(referenceId);

    final MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(GenomeParameters.REFERENCE_GENOME_ID.name(), reference.getId());
    params.addValue(GenomeParameters.BIO_DATA_ITEM_ID.name(), reference.getBioDataItemId());
    params.addValue(GenomeParameters.SIZE.name(), reference.getSize());
    params.addValue(GenomeParameters.INDEX_ID.name(), reference.getIndex().getId());
    params.addValue(GenomeParameters.GENE_ITEM_ID.name(), reference.getGeneFile() != null ?
                                                          reference.getGeneFile().getId() : null);

    getNamedParameterJdbcTemplate().update(createReferenceGenomeQuery, params);
    return reference;
}
 
Example 35
Project: Gargoyle   File: DbUtil.java   View source code 6 votes vote down vote up
/**
 * 첫번쨰로우의 첫번쨰 컬럼값 리턴. <br/>
 * 값이 없는경우 NULL <br/>
 * 
 * @작성자 : KYJ
 * @작성일 : 2017. 11. 27.
 * @param dataSource
 * @param sql
 * @param paramMap
 * @return
 */
public static String selectScala(DataSource dataSource, final String sql, MapSqlParameterSource paramMap) {
	String r = null;
	try {
		noticeQuery(sql);
		NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);

		ResultSetExtractor<String> extr = new ResultSetExtractor<String>() {

			@Override
			public String extractData(ResultSet rs) throws SQLException, DataAccessException {
				if (rs.next()) {
					return rs.getString(1);
				}
				return null;
			}
		};
		r = jdbcTemplate.query(sql, paramMap, extr);

	} catch (Exception e) {
		throw e;
	} finally {
		cleanDataSource();
	}
	return r;
}
 
Example 36
Project: Gargoyle   File: SimpleSQLResultView.java   View source code 6 votes vote down vote up
private List<Map<String, Object>> execute(String sql, MapSqlParameterSource mapSqlParameterSource,
		RowMapper<Map<String, Object>> rowMapper) throws Exception {

	/* Custom DataSource 정보가 존재한다면 Custom DataSource를 활용한다. */

	ResourceLoader instance = ResourceLoader.getInstance();
	String url = instance.get(ResourceLoader.CUSTOM_DAOWIZARD_KEY_JDBC_URL, null);
	if (ValueUtil.isNotEmpty(url)) {
		String driver = instance.get(ResourceLoader.CUSTOM_DAOWIZARD_KEY_JDBC_DRIVER, "");
		String id = instance.get(ResourceLoader.CUSTOM_DAOWIZARD_KEY_JDBC_ID, "");
		String pass = instance.get(ResourceLoader.CUSTOM_DAOWIZARD_KEY_JDBC_PASS, "");

		if (ValueUtil.isNotEmpty(pass))
			pass = DbUtil.decryp(pass);
		DataSource dataSource = DbUtil.getDataSource(driver, url, id, pass);

		return DbUtil.selectLimit(dataSource, sql, mapSqlParameterSource, rowMapper, 100);
	}

	return DbUtil.selectLimit(sql, mapSqlParameterSource, rowMapper, 100);
}
 
Example 37
Project: NGB   File: DaoHelper.java   View source code 6 votes vote down vote up
@Transactional(propagation = Propagation.MANDATORY)
public Long createTempList(final Long listId, final Collection<? extends BaseEntity> list) {
    Assert.notNull(listId);
    Assert.isTrue(CollectionUtils.isNotEmpty(list));
    // creates a new local temporary table if it doesn't exists to handle temporary lists
    getJdbcTemplate().update(createTemporaryListQuery);
    // fills in a temporary list by given values
    int i = 0;
    final Iterator<? extends BaseEntity> iterator = list.iterator();
    final MapSqlParameterSource[] batchArgs = new MapSqlParameterSource[list.size()];
    while (iterator.hasNext()) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(HelperParameters.LIST_ID.name(), listId);
        params.addValue(HelperParameters.LIST_VALUE.name(), iterator.next().getId());
        batchArgs[i] = params;
        i++;
    }
    getNamedParameterJdbcTemplate().batchUpdate(insertTemporaryListItemQuery, batchArgs);
    return listId;
}
 
Example 38
Project: piper   File: JdbcJobRepository.java   View source code 6 votes vote down vote up
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("tags", String.join(",",job.getTags()));
  sqlParameterSource.addValue("priority", job.getPriority());
  sqlParameterSource.addValue("inputs", JsonHelper.writeValueAsString(json,job.getInputs()));
  sqlParameterSource.addValue("outputs", JsonHelper.writeValueAsString(json,job.getOutputs()));
  sqlParameterSource.addValue("webhooks", JsonHelper.writeValueAsString(json,job.getWebhooks()));
  return sqlParameterSource;
}
 
Example 39
Project: NGB   File: DaoHelper.java   View source code 6 votes vote down vote up
@Transactional(propagation = Propagation.MANDATORY)
public Long createTempStringList(final Long listId, final Collection<String> list) {
    Assert.notNull(listId);
    Assert.isTrue(CollectionUtils.isNotEmpty(list));
    // creates a new local temporary table if it doesn't exists to handle temporary lists
    getJdbcTemplate().update(createTemporaryStringListQuery);
    // fills in a temporary list by given values
    int i = 0;
    final Iterator<String> iterator = list.iterator();
    final MapSqlParameterSource[] batchArgs = new MapSqlParameterSource[list.size()];
    while (iterator.hasNext()) {
        MapSqlParameterSource params = new MapSqlParameterSource();
        params.addValue(HelperParameters.LIST_ID.name(), listId);
        params.addValue(HelperParameters.LIST_VALUE.name(), iterator.next());
        batchArgs[i] = params;
        i++;
    }
    getNamedParameterJdbcTemplate().batchUpdate(insertTemporaryStringListItemQuery, batchArgs);
    return listId;
}
 
Example 40
Project: stats-alfresco-on-database   File: LocalDaoImpl.java   View source code 6 votes vote down vote up
@Override
@Transactional
public void upadteDirSumSizeZero(List<Long> parentsid) throws SaodException {
	NamedParameterJdbcTemplate jdbcNamesTpl = new NamedParameterJdbcTemplate(this.jdbcTemplate);

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

	for (Long id : parentsid) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		parameters.addValue("node_id", id);
		parameters.addValue("sum_size", 0);
		batchArgs.add(parameters);
	}

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