Example usage for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate query

List of usage examples for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate query

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.namedparam.*
  5. NamedParameterJdbcTemplate
  6. query

Introduction

In this page you can find the example usage for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate query.

Prototype

@Override
    public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper)
            throws DataAccessException

Source Link

Usage

From source file:com.joliciel.frenchTreebank.TreebankDaoImpl.java

public List<Word> findWords(String text) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_WORD + " FROM ftb_word w" + " WHERE word_text = :word_text"
            + " ORDER BY word_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("word_text", text);

    LOG.info(sql);/*from  w w  w.  j a va 2 s.co m*/
    TreebankDaoImpl.LogParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<Word> words = jt.query(sql, paramSource, new WordMapper(this.treebankServiceInternal));

    return words;
}

From source file:com.joliciel.frenchTreebank.TreebankDaoImpl.java

@Override
public List<PhraseInternal> findChildren(Phrase phrase) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_PHRASE + " FROM ftb_phrase" + " WHERE phrase_parent_id = :phrase_id"
            + " ORDER BY phrase_position";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("phrase_id", phrase.getId());

    LOG.info(sql);/*from   w  ww  .j  a va  2s .c  o  m*/
    TreebankDaoImpl.LogParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<PhraseInternal> children = jt.query(sql, paramSource, new PhraseMapper(this.treebankServiceInternal));

    return children;
}

From source file:com.joliciel.frenchTreebank.TreebankDaoImpl.java

@SuppressWarnings("unchecked")
@Override//from w w  w  .j  a v a  2s  .  c  o  m
public List<PhraseSubunit> findPhraseSubunits(PhraseUnit phraseUnit) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_PHRASE_SUBUNIT + " FROM ftb_phrase_subunit"
            + " WHERE psubunit_punit_id = :psubunit_punit_id" + " ORDER BY psubunit_position";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("psubunit_punit_id", phraseUnit.getId());

    LOG.info(sql);
    TreebankDaoImpl.LogParameters(paramSource);
    List<PhraseSubunit> phraseSubunits = jt.query(sql, paramSource,
            new PhraseSubUnitMapper(this.treebankServiceInternal));

    return phraseSubunits;
}

From source file:com.joliciel.talismane.terminology.postgres.PostGresTerminologyBase.java

@Override
public List<Term> getTerms(int frequencyThreshold, String searchText, boolean marked,
        boolean markedExpansions) {
    MONITOR.startTask("getTerms");
    try {/*  w w  w .  j  a v a  2  s  . c om*/
        NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
        String sql = "SELECT " + SELECT_TERM + " FROM term" + " INNER JOIN text ON term_text_id=text_id"
                + " WHERE term_project_id = :term_project_id";
        if (marked && markedExpansions) {
            sql += " AND term_marked = :term_marked";
            if (searchText.length() > 0)
                sql += " AND text_text LIKE :term_text";
        } else {
            if (frequencyThreshold > 0)
                sql += " AND term_frequency >= :term_frequency";
            if (searchText.length() > 0)
                sql += " AND text_text LIKE :term_text";
            if (marked)
                sql += " AND term_marked = :term_marked";
        }
        sql += " ORDER BY term_frequency DESC, text_text";
        MapSqlParameterSource paramSource = new MapSqlParameterSource();
        if (frequencyThreshold > 0)
            paramSource.addValue("term_frequency", frequencyThreshold);
        if (searchText.length() > 0)
            paramSource.addValue("term_text", searchText + "%");
        if (marked)
            paramSource.addValue("term_marked", true);
        paramSource.addValue("term_project_id", this.getCurrentProjectId());

        LOG.trace(sql);
        LogParameters(paramSource);
        @SuppressWarnings("unchecked")
        List<Term> terms = jt.query(sql, paramSource, new TermMapper());

        if (marked && markedExpansions) {
            this.addParents(terms);
            List<Term> termsWithFrequency = new ArrayList<Term>();
            for (Term term : terms) {
                int maxAncestorFrequency = this.getMaxAncestorFrequency(term);
                if (maxAncestorFrequency >= frequencyThreshold)
                    termsWithFrequency.add(term);
            }
            terms = termsWithFrequency;
        }

        return terms;
    } finally {
        MONITOR.endTask("getTerms");
    }
}

From source file:com.joliciel.frenchTreebank.TreebankDaoImpl.java

public List<Word> findWords(Phrase phrase) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_WORD + " FROM ftb_word w, ftb_phrase_unit pu, ftb_phrase_child pc"
            + " WHERE word_id=punit_word_id AND punit_phrase_id = pchild_child_id AND pchild_phrase_id = :pchild_phrase_id"
            + " ORDER BY punit_position";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("pchild_phrase_id", phrase.getId());

    LOG.info(sql);/*from   w  ww  . j  a v a 2  s  .  co  m*/
    TreebankDaoImpl.LogParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<Word> words = jt.query(sql, paramSource, new WordMapper(this.treebankServiceInternal));

    return words;
}

From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java

@Override
public List<Shape> findShapesToSplit(Locale locale) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_SHAPE + ", count(split_id) as the_count FROM ocr_shape"
            + " LEFT JOIN ocr_split on shape_id = split_shape_id"
            + " LEFT JOIN ocr_group ON shape_group_id = group_id"
            + " LEFT JOIN ocr_row ON group_row_id = row_id"
            + " LEFT JOIN ocr_paragraph ON row_paragraph_id = paragraph_id"
            + " LEFT JOIN ocr_image ON paragraph_image_id = image_id" + " WHERE length(shape_letter)>1"
            + " AND shape_letter not like '%|'" + " AND shape_letter not like '|%'"
            + " AND shape_letter not in (:dual_character_letters)"
            + " AND image_imgstatus_id in (:image_imgstatus_id)" + " GROUP BY " + SELECT_SHAPE
            + " ORDER BY the_count, shape_letter, shape_id";

    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    Linguistics linguistics = Linguistics.getInstance(locale);

    paramSource.addValue("dual_character_letters", linguistics.getDualCharacterLetters());
    List<Integer> imageStatusList = new ArrayList<Integer>();
    imageStatusList.add(ImageStatus.TRAINING_VALIDATED.getId());
    imageStatusList.add(ImageStatus.TRAINING_HELD_OUT.getId());
    imageStatusList.add(ImageStatus.TRAINING_TEST.getId());
    paramSource.addValue("image_imgstatus_id", imageStatusList);

    LOG.debug(sql);/*from   www.  j  a v  a 2  s .  co  m*/
    logParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<Shape> shapes = jt.query(sql, paramSource, new ShapeMapper(this.getGraphicsServiceInternal()));

    return shapes;
}

From source file:net.urosk.mifss.core.lib.db.PaginationHelper.java

public DataResult fetchPage(final DataSource dataSource, final NamedParameterJdbcTemplate jt,
        final String sqlFetchRows, final MapSqlParameterSource namedParameters, RowMapper rowMapper,
        final long offset, final long limit) {

    String countSql = StringUtils.replace(COUNT_QUERY, ":query", sqlFetchRows);

    final int rowCount = jt.queryForObject(countSql, namedParameters, Integer.class);

    Map<String, String> params = new HashMap<String, String>();
    params.put("query", sqlFetchRows);
    params.put("from", offset + "");
    params.put("to", (offset + limit) + "");
    params.put("limit", (limit) + "");

    // get database product name for paging!
    String dbProductName = getDatabaseProductName(dataSource);
    dbProductName = dbProductName.toLowerCase();

    String pagingSql = "NOT IMPELEMNTED FOR THIS DATABASE! - " + dbProductName;

    if (dbProductName.contains("mysql")) {

        pagingSql = StringUtils.replace(MYSQL_PAGING, ":query", sqlFetchRows);
        pagingSql = StringUtils.replace(pagingSql, ":from", offset + "");
        pagingSql = StringUtils.replace(pagingSql, ":limit", (limit) + "");

    } else if (dbProductName.contains("oracle")) {

        pagingSql = StringUtils.replace(ORACLE_PAGING, ":query", sqlFetchRows);
        pagingSql = StringUtils.replace(pagingSql, ":from", offset + "");
        pagingSql = StringUtils.replace(pagingSql, ":to", (offset + limit) + "");

    } else {//  w  w  w.ja  v a  2 s . com
        logger.error("Paging for selected database is not yet implemented!! + Check paginationHelper for this");

    }

    List list = jt.query(pagingSql, namedParameters, rowMapper);

    // create the page object
    final DataResult dataResult = new DataResult();
    dataResult.setCount(rowCount);
    dataResult.setFrom(offset);
    dataResult.setLimit(limit);
    dataResult.setList(list);

    return dataResult;
}

From source file:org.jumpmind.metl.core.runtime.component.RdbmsReader.java

@Override
public void handle(final Message inputMessage, final ISendMessageCallback callback,
        boolean unitOfWorkBoundaryReached) {

    rowReadDuringHandle = 0;/*from   w  ww .  ja v a 2 s. com*/

    NamedParameterJdbcTemplate template = getJdbcTemplate();

    int inboundRecordCount = 0;
    Iterator<?> inboundPayload = null;
    if (PER_ENTITY.equals(runWhen) && inputMessage instanceof ContentMessage<?>) {
        inboundPayload = ((Collection<?>) ((ContentMessage<?>) inputMessage).getPayload()).iterator();
        inboundRecordCount = ((Collection<?>) ((ContentMessage<?>) inputMessage).getPayload()).size();
    } else if (PER_MESSAGE.equals(runWhen) && !(inputMessage instanceof ControlMessage)) {
        inboundPayload = null;
        inboundRecordCount = 1;
    } else if (PER_UNIT_OF_WORK.equals(runWhen) && inputMessage instanceof ControlMessage) {
        inboundPayload = null;
        inboundRecordCount = 1;
    }

    /*
     * A reader can be started by a startup message (if it has no input
     * links) or it can be started by another component that sends messages
     * to it. If the reader is started by another component, then loop for
     * all records in the input message
     */
    ArrayList<EntityData> outboundPayload = new ArrayList<EntityData>(); // =
                                                                         // null;
    for (int i = 0; i < inboundRecordCount; i++) {
        Object entity = inboundPayload != null && inboundPayload.hasNext() ? inboundPayload.next() : null;
        ResultSetToEntityDataConverter resultSetToEntityDataConverter = new ResultSetToEntityDataConverter(
                inputMessage, callback, unitOfWorkBoundaryReached, outboundPayload);
        if (passInputRowsThrough) {
            outboundPayload.add((EntityData) entity);
        }
        for (String sql : getSqls()) {
            String sqlToExecute = prepareSql(sql, inputMessage, entity);
            Map<String, Object> paramMap = prepareParams(sqlToExecute, inputMessage, entity, runWhen);
            log(LogLevel.INFO, "About to run: %s", sqlToExecute);
            log(LogLevel.INFO, "Passing params: %s", paramMap);
            resultSetToEntityDataConverter.setSqlToExecute(sqlToExecute);
            template.query(sqlToExecute, paramMap, resultSetToEntityDataConverter);
            if (unitOfWork.equalsIgnoreCase(SQL_STATEMENT)) {
                sendLeftOverRows(callback, outboundPayload);
                callback.sendControlMessage();
            }
        }
        if (unitOfWork.equalsIgnoreCase(SQL_SCRIPT)) {
            sendLeftOverRows(callback, outboundPayload);
            callback.sendControlMessage();
        }
    }
    sendLeftOverRows(callback, outboundPayload);
}

From source file:org.springframework.integration.jdbc.store.JdbcChannelMessageStore.java

/**
 * This method executes a call to the DB to get the oldest Message in the
 * MessageGroup which in the context of the {@link JdbcChannelMessageStore}
 * means the channel identifier./*from  ww  w.  j a v  a  2 s .  c  o  m*/
 *
 * @param groupIdKey String representation of message group (Channel) ID
 * @return a message; could be null if query produced no Messages
 */
protected Message<?> doPollForMessage(String groupIdKey) {

    final NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    final MapSqlParameterSource parameters = new MapSqlParameterSource();

    parameters.addValue("region", region);
    parameters.addValue("group_key", groupIdKey);

    final String query;

    final List<Message<?>> messages;

    this.idCacheReadLock.lock();
    try {
        if (this.usingIdCache && !this.idCache.isEmpty()) {
            query = getQuery(this.channelMessageStoreQueryProvider.getPollFromGroupExcludeIdsQuery());
            parameters.addValue("message_ids", idCache);
        } else {
            query = getQuery(this.channelMessageStoreQueryProvider.getPollFromGroupQuery());
        }
        messages = namedParameterJdbcTemplate.query(query, parameters, messageRowMapper);
    } finally {
        this.idCacheReadLock.unlock();
    }

    Assert.isTrue(messages.size() == 0 || messages.size() == 1);
    if (messages.size() > 0) {

        final Message<?> message = messages.get(0);
        final String messageId = message.getHeaders().getId().toString();

        if (this.usingIdCache) {
            this.idCacheWriteLock.lock();
            try {
                boolean added = this.idCache.add(messageId);

                if (logger.isDebugEnabled()) {
                    logger.debug(String.format("Polled message with id '%s' added: '%s'.", messageId, added));
                }
            } finally {
                this.idCacheWriteLock.unlock();
            }
        }

        return message;
    }
    return null;
}

From source file:org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplateTests.java

public void testQueryWithResultSetExtractor() throws SQLException {
    mockResultSet.next();//from   w  ww. java  2s .c  o  m
    ctrlResultSet.setReturnValue(true);
    mockResultSet.getInt("id");
    ctrlResultSet.setReturnValue(1);
    mockResultSet.getString("forename");
    ctrlResultSet.setReturnValue("rod");
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    mockPreparedStatement.setObject(1, new Integer(1), Types.DECIMAL);
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.setString(2, "UK");
    ctrlPreparedStatement.setVoidCallable();
    mockPreparedStatement.executeQuery();
    ctrlPreparedStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockPreparedStatement.getWarnings();
        ctrlPreparedStatement.setReturnValue(null);
    }
    mockPreparedStatement.close();
    ctrlPreparedStatement.setVoidCallable();

    mockConnection.prepareStatement(SELECT_NAMED_PARAMETERS_PARSED);
    ctrlConnection.setReturnValue(mockPreparedStatement);

    replay();

    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(mockDataSource);
    Map params = new HashMap();
    params.put("id", new SqlParameterValue(Types.DECIMAL, new Integer(1)));
    params.put("country", "UK");
    Customer cust = (Customer) jt.query(SELECT_NAMED_PARAMETERS, params, new ResultSetExtractor() {
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            rs.next();
            Customer cust = new Customer();
            cust.setId(rs.getInt(COLUMN_NAMES[0]));
            cust.setForename(rs.getString(COLUMN_NAMES[1]));
            return cust;
        }
    });
    assertTrue("Customer id was assigned correctly", cust.getId() == 1);
    assertTrue("Customer forename was assigned correctly", cust.getForename().equals("rod"));
}