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

The following are Jave code examples for showing how to use addValue() of the org.springframework.jdbc.core.namedparam.MapSqlParameterSource class. You can vote up the examples you like. Your votes will be used in our system to get more good examples.
+ Save this method
Example 1
Project: stats-alfresco-on-database   File: LocalDaoImpl.java   View Source Code Vote up 7 votes
@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 2
Project: NGB-master   File: BiologicalDataItemDao.java   View Source Code Vote up 6 votes
/**
 * 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 3
Project: NGB-master   File: BookmarkDao.java   View Source Code Vote up 6 votes
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 4
Project: graphium   File: WayBaseGraphWriteDaoImpl.java   View Source Code Vote up 6 votes
@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 5
Project: NGB-master   File: DaoHelper.java   View Source Code Vote up 6 votes
@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 6
Project: NGB   File: VcfFileDao.java   View Source Code Vote up 6 votes
/**
 * Saves sample metadata to the database.
 *
 * @param samples   {@code List&lt;Sample&gt;} samples to save
 * @param vcfFileId {@code long} file ID to save samples for
 */
@Transactional(propagation = Propagation.MANDATORY)
public void createSamples(List<VcfSample> samples, long vcfFileId) {
    List<Long> sampleIds = daoHelper.createIds(vcfSampleSequenceName, samples.size());
    for (int i = 0; i < samples.size(); i++) {
        samples.get(i).setId(sampleIds.get(i));
    }

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

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue(SampleParameters.VCF_SAMPLE_ID.name(), sample.getId());
        param.addValue(SampleParameters.VCF_ID.name(), vcfFileId);
        param.addValue(SampleParameters.SAMPLE_NAME.name(), sample.getName());
        param.addValue(SampleParameters.ORDER_INDEX.name(), sample.getIndex());
        params.add(param);
    }

    getNamedParameterJdbcTemplate().batchUpdate(createSamplesForFileQuery, params.toArray(new
            MapSqlParameterSource[params.size()]));
}
 
Example 7
Project: NGB   File: SegFileDao.java   View Source Code Vote up 6 votes
/**
 * 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 8
Project: NGB   File: ReferenceGenomeDao.java   View Source Code Vote up 6 votes
/**
 * 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 9
Project: NGB   File: DaoHelper.java   View Source Code Vote up 6 votes
@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 10
Project: NGB   File: ReferenceGenomeDao.java   View Source Code Vote up 6 votes
/**
 * 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 11
Project: NGB   File: DaoHelper.java   View Source Code Vote up 6 votes
@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 12
Project: NGB-master   File: BiologicalDataItemDao.java   View Source Code Vote up 5 votes
/**
 * Finds files with a specified file name, checks name for strict, case sensitive equality
 * @param name search query
 * @return {@code List} of files with a matching name
 */
@Transactional(propagation = Propagation.MANDATORY)
public List<BiologicalDataItem> loadFilesByNameStrict(final String name) {
    final MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(BiologicalDataItemParameters.NAME.name(), name);
    return getNamedParameterJdbcTemplate().query(loadBiologicalDataItemsByNameStrictQuery,
            params, getRowMapper());
}
 
Example 13
Project: NGB-master   File: ProjectDao.java   View Source Code Vote up 5 votes
static MapSqlParameterSource getParameters(Project project, Long parentId) {
    MapSqlParameterSource params = new MapSqlParameterSource();

    params.addValue(PROJECT_ID.name(), project.getId());
    params.addValue(PROJECT_NAME.name(), project.getName());
    params.addValue(CREATED_BY.name(), project.getCreatedBy());
    params.addValue(CREATED_DATE.name(), project.getCreatedDate());
    params.addValue(PARENT_ID.name(), parentId);

    return params;
}
 
Example 14
Project: NGB-master   File: ProjectDao.java   View Source Code Vote up 5 votes
/**
 * Removes an item from a project, specified by ID
 * @param projectId {@code Long} ID of a project
 * @param biologicalItemId {@code Long} ID of an item to remove
 */
@Transactional(propagation = Propagation.MANDATORY)
public void deleteProjectItem(long projectId, long biologicalItemId) {
    List<Long> currentItemIds = getJdbcTemplate().query(loadProjectItemsIdsQuery, new SingleColumnRowMapper<>(),
            projectId);

    currentItemIds.remove(biologicalItemId);

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId);
    params.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), biologicalItemId);

    getNamedParameterJdbcTemplate().update(deleteProjectItemQuery, params);

    ArrayList<MapSqlParameterSource> updateParams = new ArrayList<>();
    for (int i = 1; i <= currentItemIds.size(); i++) {
        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId);
        param.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), currentItemIds.get(i - 1));
        param.addValue(ProjectItemParameters.ORDINAL_NUMBER.name(), i);

        updateParams.add(param);
    }

    getNamedParameterJdbcTemplate().batchUpdate(updateProjectItemOrderingNumberQuery, updateParams.toArray(
            new MapSqlParameterSource[updateParams.size()]));
}
 
Example 15
Project: NGB-master   File: BookmarkDao.java   View Source Code Vote up 5 votes
private static MapSqlParameterSource getParameters(Bookmark bookmark) {
    MapSqlParameterSource params = new MapSqlParameterSource();

    params.addValue(BOOKMARK_ID.name(), bookmark.getId());
    params.addValue(BOOKMARK_NAME.name(), bookmark.getName());
    params.addValue(START_INDEX.name(), bookmark.getStartIndex());
    params.addValue(END_INDEX.name(), bookmark.getEndIndex());
    params.addValue(REFERRED_CHROMOSOME_ID.name(), bookmark.getChromosome().getId());
    params.addValue(CREATED_BY.name(), bookmark.getCreatedBy());
    params.addValue(CREATED_DATE.name(), bookmark.getCreatedDate());

    return params;
}
 
Example 16
Project: NGB-master   File: VcfFileDao.java   View Source Code Vote up 5 votes
/**
 * Saves sample metadata to the database.
 *
 * @param samples   {@code List&lt;Sample&gt;} samples to save
 * @param vcfFileId {@code long} file ID to save samples for
 */
@Transactional(propagation = Propagation.MANDATORY)
public void createSamples(List<VcfSample> samples, long vcfFileId) {
    List<Long> sampleIds = daoHelper.createIds(vcfSampleSequenceName, samples.size());
    for (int i = 0; i < samples.size(); i++) {
        samples.get(i).setId(sampleIds.get(i));
    }

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

        MapSqlParameterSource param = new MapSqlParameterSource();
        param.addValue(SampleParameters.VCF_SAMPLE_ID.name(), sample.getId());
        param.addValue(SampleParameters.VCF_ID.name(), vcfFileId);
        param.addValue(SampleParameters.SAMPLE_NAME.name(), sample.getName());
        param.addValue(SampleParameters.ORDER_INDEX.name(), sample.getIndex());
        params.add(param);
    }

    getNamedParameterJdbcTemplate().batchUpdate(createSamplesForFileQuery, params.toArray(new
            MapSqlParameterSource[params.size()]));
}
 
Example 17
Project: premier-wherehows   File: LineageDAOLite.java   View Source Code Vote up 5 votes
private static ImpactDataset assignImpactDataset(String idurn) {
    int level = Integer.parseInt(idurn.substring(0, idurn.indexOf("****")));
    String urn = idurn.substring(idurn.indexOf("****") + 4);
    ImpactDataset impD = new ImpactDataset();

    Map<String, Object> row = null;
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("urn", urn);
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

    try {
        row = namedParameterJdbcTemplate.queryForMap(GET_DATA_ATTR, parameters);
    } catch (IncorrectResultSizeDataAccessException irsdae) {
        Logger.error("Incorrect result size ", irsdae);
    } catch (DataAccessException dae) {
        Logger.error("SQL query failed ", dae);
    }

    impD.urn = urn;
    impD.level = level;
    impD.name = (String) row.get("name");
    impD.id = (Long) row.get("id");
    impD.datasetUrl = "#/datasets/" + impD.id;
    JsonNode prop = Json.parse((String) row.get("properties"));
    if (prop.has("valid") && (prop.get("valid").asText()) == "true") {
        impD.isValidDataset = true;
    } else {
        impD.isValidDataset = false;
    }
    return impD;
}
 
Example 18
Project: NGB-master   File: ReferenceGenomeDao.java   View Source Code Vote up 5 votes
@Transactional(propagation = Propagation.MANDATORY)
public Reference createReferenceGenome(final Reference reference) {
    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 19
Project: NGB-master   File: ProjectDao.java   View Source Code Vote up 5 votes
/**
 * Tests if project item from a database is hidden
 * @param projectId {@code Long} ID of a project
 * @param biologicalItemId {@code Long} ID of an item to test
 * @return {@code true} if project item is hidden, {@code false} if not
 */
@Transactional(propagation = Propagation.MANDATORY)
public Boolean isProjectItemHidden(long projectId, long biologicalItemId) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId);
    params.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(), biologicalItemId);

    return getNamedParameterJdbcTemplate().queryForObject(isProjectItemHiddenQuery, params,
            new SingleColumnRowMapper<>());
}
 
Example 20
Project: spring-cloud-dashboard   File: AbstractRdbmsKeyValueRepository.java   View Source Code Vote up 5 votes
@Override
public Iterable<D> findAll(Iterable<String> names) {
	Assert.notNull(names, "names must not be null");
	List<String> listOfNames = new ArrayList<String>();
	for (String name : names) {
		listOfNames.add(name);
	}

	MapSqlParameterSource namedParameters = new MapSqlParameterSource();
	namedParameters.addValue(LIST_OF_NAMES, listOfNames);

	return namedParameterJdbcTemplate.query(findAllWhereInClause, namedParameters, rowMapper);
}
 
Example 21
Project: NGB-master   File: ProjectDao.java   View Source Code Vote up 5 votes
/**
 * Moves a Project, specified by projectId to a parent project, specified by parentId
 * @param projectId an ID of a project, to move
 * @param parentId an ID of a parent project to move to
 */
@Transactional(propagation = Propagation.MANDATORY)
public void moveProjectToParent(long projectId, Long parentId) {
    if (parentId != null) {
        Assert.isTrue(projectId != parentId, "Project can't be it's own parent");
    }

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(ProjectParameters.PROJECT_ID.name(), projectId);
    params.addValue(ProjectParameters.PARENT_ID.name(), parentId);

    getNamedParameterJdbcTemplate().update(moveProjectToParentQuery, params);
}
 
Example 22
Project: NGB-master   File: PersonDao.java   View Source Code Vote up 5 votes
/**
 * Loads a {@code Person} instance from the database specified by it's name and password
 * @param name of a user
 * @param password of a user
 * @return  a loaded {@code Person} instance or
 *          {@code null} if user with a given name and password doesn't exist
 */
@Transactional(propagation = Propagation.MANDATORY)
public Person loadPersonByNameAndPassword(String name, String password) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(PersonParameters.NAME.name(), name);
    params.addValue(PersonParameters.PASSWORD.name(), password);

    List<Person> persons = getNamedParameterJdbcTemplate().query(loadPersonByNameAndPasswordQuery, params,
            PersonParameters.getRowMapper());
    return persons.isEmpty() ? null : persons.get(0);
}
 
Example 23
Project: knowledge-forest-dialogue-recommendation   File: YottaService.java   View Source Code Vote up 5 votes
public List<String> getFragmentContent(List<String> classNames, List<String> termNames) {
    logger.debug(classNames.toString());
    logger.debug(termNames.toString());
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("termNames", termNames);
    parameters.addValue("classNames", classNames);
    return namedParameterJdbcTemplate.query(
            "SELECT a.FragmentContent FROM assemble_fragment a WHERE a.TermName in (:termNames) AND a.ClassName in (:classNames)",
            parameters, (rs, rowNum) -> rs.getString("FragmentContent")
    );
}
 
Example 24
Project: NGB-master   File: BiologicalDataItemDao.java   View Source Code Vote up 5 votes
/**
 * Finds files with names matching a specified file name, performs substring, case insensitive search
 * @param name search query
 * @return {@code List} of files with a matching name
 */
@Transactional(propagation = Propagation.MANDATORY)
public List<BiologicalDataItem> loadFilesByName(final String name) {
    final MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(BiologicalDataItemParameters.NAME.name(), "%" + name.toLowerCase() + "%");
    return getNamedParameterJdbcTemplate().query(loadBiologicalDataItemsByNameQuery,
            params, getRowMapper());
}
 
Example 25
Project: knowledge-forest-dialogue-recommendation   File: ApplicationTests.java   View Source Code Vote up 5 votes
@Test
public void testJDBC() {
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("termName", "二叉树");
    parameters.addValue("facets", Arrays.asList("定义", "摘要"));
    parameters.addValue("className", "数据结构");
    List<String> a = namedParameterJdbcTemplate.query(
            "SELECT a.FragmentContent FROM assemble_fragment a WHERE a.TermName=:termName AND a.FacetName in (:facets) AND a" +
                    ".ClassName=:className",
            parameters, (rs, rowNum) -> rs.getString("FragmentContent")
    );
    a.forEach(System.out::println);
}
 
Example 26
Project: NGB   File: PersonDao.java   View Source Code Vote up 5 votes
/**
 * Loads a {@code Person} instance from the database specified by it's name
 * @param name of a user
 * @return  a loaded {@code Person} instance or
 *          {@code null} if user with a given name doesn't exist
 */
@Transactional(propagation = Propagation.SUPPORTS)
public Person loadPersonByName(String name) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(PersonParameters.NAME.name(), name);

    List<Person> persons = getNamedParameterJdbcTemplate().query(loadPersonByNameQuery, params,
            PersonParameters.getPasswordRowMapper());
    return persons.isEmpty() ? null : persons.get(0);
}
 
Example 27
Project: oma-riista-web   File: PublicBearReportFeature.java   View Source Code Vote up 5 votes
private List<Map<String, Object>> countUsedQuotas(int year, LocalDate currentDate) {
    final MapSqlParameterSource params = getBeginEndParameters(year);
    params.addValue("currentDate", currentDate.toDate(), Types.TIMESTAMP);

    return jdbcTemplate.query(SQL_USED_QUOTA, params, (rs, rowNum) -> {
        int quota = rs.getInt("quota");
        int used = rs.getInt("used");
        boolean seasonOngoing = rs.getBoolean("season_ongoing");

        int remainingQuota = quota - used;
        int remaining = seasonOngoing && remainingQuota > 0 ? remainingQuota : 0;
        return ImmutableMap.of("area_fi", rs.getString("area_fi"), "remaining", remaining);
    });
}
 
Example 28
Project: NGB   File: BookmarkDao.java   View Source Code Vote up 5 votes
@Transactional(propagation = Propagation.SUPPORTS)
public List<Bookmark> searchBookmarks(String searchStr, long userId, int limit) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(BookmarkParameters.BOOKMARK_NAME.name(), searchStr + '%');
    params.addValue(BookmarkParameters.CREATED_BY.name(), userId);
    params.addValue("SEARCH_LIMIT", limit);

    return getNamedParameterJdbcTemplate().query(searchBookmarksQuery, params, BookmarkParameters.getRowMapper());
}
 
Example 29
Project: oma-riista-web   File: PublicWolfReportFeature.java   View Source Code Vote up 5 votes
private static MapSqlParameterSource getSqlParameterSource(int year, String permitTypeCode) {
    final Interval interval = DateUtil.huntingYearInterval(year);
    final MapSqlParameterSource queryParams = new MapSqlParameterSource();
    queryParams.addValue("permitTypeCode", permitTypeCode);
    queryParams.addValue("beginTime", interval.getStart().toDate(), Types.TIMESTAMP);
    queryParams.addValue("endTime", interval.getEnd().toDate(), Types.TIMESTAMP);
    return queryParams;
}
 
Example 30
Project: NGB   File: ProjectDao.java   View Source Code Vote up 5 votes
/**
 * Moves a Project, specified by projectId to a parent project, specified by parentId
 * @param projectId an ID of a project, to move
 * @param parentId an ID of a parent project to move to
 */
@Transactional(propagation = Propagation.MANDATORY)
public void moveProjectToParent(long projectId, Long parentId) {
    if (parentId != null) {
        Assert.isTrue(projectId != parentId, "Project can't be it's own parent");
    }

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(ProjectParameters.PROJECT_ID.name(), projectId);
    params.addValue(ProjectParameters.PARENT_ID.name(), parentId);

    getNamedParameterJdbcTemplate().update(moveProjectToParentQuery, params);
}
 
Example 31
Project: NGB   File: MafFileDao.java   View Source Code Vote up 5 votes
/**
 * Persists {@code MafFile} record to the database
 * @param mafFile a {@code MafFile} instance to be persisted
 */
@Transactional(propagation = Propagation.MANDATORY)
public void createMafFile(MafFile mafFile) {
    MapSqlParameterSource params = BiologicalDataItemDao.FeatureFileParameters
            .getLinkedTableParameters(MafParameters.MAF_ID.name(), mafFile);
    params.addValue(MafParameters.REAL_PATH.name(), mafFile.getRealPath());

    getNamedParameterJdbcTemplate().update(createMafFileQuery, params);
}
 
Example 32
Project: NGB   File: BookmarkDao.java   View Source Code Vote up 5 votes
@Transactional(propagation = Propagation.SUPPORTS)
public int searchBookmarkCount(String searchStr, long userId) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(BookmarkParameters.BOOKMARK_NAME.name(), searchStr + '%');
    params.addValue(BookmarkParameters.CREATED_BY.name(), userId);

    return getNamedParameterJdbcTemplate().queryForObject(searchBookmarkCountQuery, params, Integer.class);
}
 
Example 33
Project: NGB   File: ReferenceGenomeDao.java   View Source Code Vote up 5 votes
@Transactional(propagation = Propagation.MANDATORY)
public Reference createReferenceGenome(final Reference reference) {
    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 34
Project: spring4-understanding   File: CallMetaDataContextTests.java   View Source Code Vote up 5 votes
@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<SqlParameter>();
	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());
}
 
Example 35
Project: NGB   File: ReferenceGenomeDao.java   View Source Code Vote up 5 votes
@Transactional(propagation = Propagation.SUPPORTS)
public List<Long> loadGenomeIdsByAnnotationDataItemId(Long annotationFileBiologicalItemId) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue(
            BiologicalDataItemDao.BiologicalDataItemParameters.BIO_DATA_ITEM_ID.name(),
            annotationFileBiologicalItemId
    );
    return getNamedParameterJdbcTemplate().query(
            loadGenomeIdsByAnnotationDataItemIdQuery, params,
            GenomeParameters.ID_MAPPER
    );
}
 
Example 36
Project: NGB   File: PersonDao.java   View Source Code Vote up 5 votes
static MapSqlParameterSource getParameters(Person person) {
    MapSqlParameterSource params = new MapSqlParameterSource();

    params.addValue(PERSON_ID.name(), person.getId());
    params.addValue(NAME.name(), person.getName());
    params.addValue(PASSWORD.name(), person.getPasswordHash());
    params.addValue(EMAIL.name(), person.getEmail());
    params.addValue(ROLE_ID.name(), person.getRole().getRoleId());

    return params;
}
 
Example 37
Project: NGB   File: ProjectDao.java   View Source Code Vote up 5 votes
/**
 * Adds multiple items to a project, specified by ID
 * @param projectId {@code Long} ID of a project
 * @param items a {@code List&lt;BiologicalDataItem&gt;} of items to add
 */
@Transactional(propagation = Propagation.MANDATORY)
public void addProjectItems(long projectId, List<ProjectItem> items) {
    Project project = loadProject(projectId);
    Assert.notNull(project, MessageHelper.getMessage(MessagesConstants.ERROR_PROJECT_NOT_FOUND, projectId));

    List<Long> newIds = daoHelper.createIds(projectItemSequenceName, items.size());

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

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

        param.addValue(ProjectItemParameters.PROJECT_ITEM_ID.name(), newIds.get(i));
        param.addValue(ProjectItemParameters.PROJECT_ID.name(), projectId);
        param.addValue(ProjectItemParameters.BIO_DATA_ITEM_ID.name(),
                getBioDataItemId(items.get(i).getBioDataItem()));
        param.addValue(ProjectItemParameters.ORDINAL_NUMBER.name(), count);
        param.addValue(ProjectItemParameters.HIDDEN.name(), items.get(i).getHidden() != null &&
                items.get(i).getHidden());

        params.add(param);
    }

    getNamedParameterJdbcTemplate().batchUpdate(addProjectItemQuery,
            params.toArray(new MapSqlParameterSource[items.size()]));
}
 
Example 38
Project: EasyTransaction   File: DataBaseTransactionLogReaderImpl.java   View Source Code Vote up 4 votes
@Override
public List<LogCollection> getUnfinishedLogs(LogCollection locationId,
		int pageSize, Date createTimeFloor) {
	
	JdbcTemplate localJdbcTemplate = getJdbcTemplate();
	
	List<DataBaseTransactionLogDetail> query;
	
	List<String> transIdList = null;
	if(locationId != null){
		String transIdLocation = EasyTransStaticHelper.getTransId(locationId.getAppId(), locationId.getBusCode(), locationId.getTrxId());
		transIdList = localJdbcTemplate.queryForList("select trans_log_id from trans_log_unfinished where create_time <= ? and trans_log_id > ? ORDER BY trans_log_id LIMIT ?", new Object[]{createTimeFloor,transIdLocation,pageSize},String.class);
	}else{
		transIdList = localJdbcTemplate.queryForList("select trans_log_id from trans_log_unfinished where create_time <= ? ORDER BY trans_log_id LIMIT ?", new Object[]{createTimeFloor,pageSize},String.class);
	}
	
	if(transIdList == null || transIdList.size() ==0){
		return new ArrayList<LogCollection>();
	}
	
	NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(localJdbcTemplate);
	MapSqlParameterSource paramSource = new MapSqlParameterSource();
	paramSource.addValue("ids", transIdList);
	query = namedTemplate.query("select * from trans_log_detail where trans_log_id in (:ids)  order by trans_log_id,log_detail_id;", paramSource,new BeanPropertyRowMapper<DataBaseTransactionLogDetail>(DataBaseTransactionLogDetail.class));
		
	
	List<LogCollection> result = new ArrayList<LogCollection>();
	List<DataBaseTransactionLogDetail> currentDoList = new ArrayList<DataBaseTransactionLogDetail>();
	List<Content> currentContentList = new ArrayList<Content>();
	String currentId = null;
	for(DataBaseTransactionLogDetail detailDo:query){
		if(!detailDo.getTransLogId().equals(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;
}
 
Example 39
Project: bdf2   File: EntityPR.java   View Source Code Vote up 4 votes
@SuppressWarnings("unchecked")
@Expose
public Collection<ColumnDef> buildSQLFields(Map<String,Object> map) throws Exception{
	String querySql=(String)map.get("querySql");
	querySql=parseQuerySql(querySql,map);
	final String tableName=(String)map.get("tableName");
	Collection<Map<String,Object>> parameters=(Collection<Map<String,Object>>)map.get("parameters");
	MapSqlParameterSource sqlParameters=new MapSqlParameterSource();
	if(parameters!=null){
		for(Map<String,Object> pmap:parameters){
			sqlParameters.addValue((String)pmap.get("name"), pmap.get("value"));
		}
	}
	final List<String> primaryKeyList=this.loadTablePrimaryKeys(tableName);
	
	DataSource ds=this.getJdbcTemplate().getDataSource();
	Connection con = DataSourceUtils.getConnection(ds);
	Collection<ColumnDef> columns=null;
	try{
		final DatabaseMetaData databaseMetaData=con.getMetaData();
		columns=this.getNamedParameterJdbcTemplate().query(querySql, sqlParameters, new ResultSetExtractor<Collection<ColumnDef>>(){
			public Collection<ColumnDef> extractData(ResultSet rs) throws SQLException,DataAccessException {
				Collection<ColumnDef> result=new ArrayList<ColumnDef>();
				ResultSetMetaData rsmd = rs.getMetaData();
				int count = rsmd.getColumnCount();
				for (int i = 1; i <= count; i++) {
					ColumnDef col=new ColumnDef();
					col.setTableName(rsmd.getTableName(i));
					col.setName(rsmd.getColumnName(i));
					int columnType=rsmd.getColumnType(i);
					String type="String";
					if(columnType==Types.INTEGER || columnType ==Types.BIGINT || columnType==Types.SMALLINT || columnType==Types.TINYINT){
						type="int";
					}else if(columnType==Types.BOOLEAN){
						type="Boolean";
					}else if(columnType==Types.DATE){
						type="Date";
					}else if(columnType==Types.TIMESTAMP){
						type="DateTime";
					}else if(columnType==Types.TIME){
						type="Time";
					}else if(columnType==Types.DECIMAL || columnType==Types.NUMERIC){
						type="BigDecimal";
					}else if(columnType==Types.FLOAT){
						type="Float";
					}else if(columnType==Types.DOUBLE){
						type="double";
					}
					col.setLabel(getColumnRemarks(col.getTableName(),col.getName(),databaseMetaData));
					setPrimaryKey(col,tableName,primaryKeyList);
					col.setType(type);
					result.add(col);
				}
				return result;
			}
		});
		
	}finally{
		JdbcUtils.closeConnection(con);	
	}
	return columns;
}
 
Example 40
Project: jdbc   File: JdbcSinkConfiguration.java   View Source Code Vote up 4 votes
private SqlParameterSourceFactory sqlParameterSourceFactory(
		MultiValueMap<String, Expression> columnExpressionVariations) {


	return new SqlParameterSourceFactory() {

		@Override
		public SqlParameterSource createParameterSource(Object o) {
			if (!(o instanceof Message)) {
				throw new IllegalArgumentException("Unable to handle type " + o.getClass().getName());
			}
			Message<?> message = (Message<?>) o;
			MapSqlParameterSource parameterSource = new MapSqlParameterSource();
			for (String key : columnExpressionVariations.keySet()) {
				List<Expression> spels = columnExpressionVariations.get(key);
				Object value = NOT_SET;
				EvaluationException lastException = null;
				for (Expression spel : spels) {
					try {
						value = spel.getValue(JdbcSinkConfiguration.this.evaluationContext, message);
						break;
					}
					catch (EvaluationException e) {
						lastException = e;
					}
				}
				if (value == NOT_SET) {
					if (lastException != null) {
						logger.info("Could not find value for column '" + key + "': " + lastException.getMessage());
					}
					parameterSource.addValue(key, null);
				}
				else {
					if (value instanceof JsonPropertyAccessor.ToStringFriendlyJsonNode) {
						// Need to do some reflection until we have a getter for the Node
						DirectFieldAccessor dfa = new DirectFieldAccessor(value);
						JsonNode node = (JsonNode) dfa.getPropertyValue("node");
						Object valueToUse;
						if (node == null || node.isNull()) {
							valueToUse = null;
						}
						else if (node.isNumber()) {
							valueToUse = node.numberValue();
						}
						else if (node.isBoolean()) {
							valueToUse = node.booleanValue();
						}
						else {
							valueToUse = node.textValue();
						}
						parameterSource.addValue(key, valueToUse);
					}
					else {
						parameterSource.addValue(key, value);
					}
				}
			}
			return parameterSource;
		}
	};
}