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

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

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

Introduction

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

Prototype

@Override
    public SqlRowSet queryForRowSet(String sql, Map<String, ?> paramMap) throws DataAccessException

Source Link

Usage

From source file:com.esa.infocontrol.data.jdbc.BaseDataJDBC.java

public static DataArrayWrapper getList(DataSource dataSource, String query, MapSqlParameterSource params) {
    LOG.debug("QUERY: {}", query);
    if (params != null) {
        LOG.debug("\tPARAMETERS: {}", params.getValues().toString());
    }// w w w. j a v  a 2s.c  o  m
    NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    SqlRowSet rs = jdbcTemplate.queryForRowSet(query, params);
    SqlRowSetMetaData md = rs.getMetaData();
    LOG.debug("\tCOLUMNS: {}", Arrays.toString(md.getColumnNames()));
    List<DataRow> dataList = new ArrayList<>();
    ColumnMetaData[] columnMetaData = new ColumnMetaData[md.getColumnCount()];
    for (int i = 1; i <= md.getColumnCount(); ++i) {
        columnMetaData[i - 1] = new ColumnMetaData(md.getColumnLabel(i), md.getColumnType(i));
    }
    while (rs.next()) {
        DataRow row = new DataRow(md.getColumnCount());
        for (int i = 1; i <= md.getColumnCount(); ++i) {
            row.add(rs.getString(i));
        }
        dataList.add(row);
    }
    return new DataArrayWrapper(dataList, columnMetaData);
}

From source file:com.zousu.mongopresser.MySQLHandler.java

/**
 * Returns a ResultSet of entire table/*from   w  ww .ja  v a  2s .  c o  m*/
 * 
 * @param tableName
 * @return
 */
public SqlRowSet selectAllFromTable(String tableName) {
    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);

    logger.info("Calling: " + selectAllQuery + " " + tableName);

    return template.queryForRowSet(selectAllQuery + " " + tableName, new HashMap<String, String>());
}

From source file:com.simplymeasured.prognosticator.HiveQueryImplTest.java

@Test
public void testRunQuery() throws Exception {
    final String queryString = "SELECT * FROM foo";

    NamedParameterJdbcTemplate template = mock(NamedParameterJdbcTemplate.class);

    HiveQueryImpl query = new HiveQueryImpl(template);

    Map<String, Object> parameterMap = new HashMap<String, Object>() {
        {/*from ww w .  j  a  v a  2s.c  om*/
            put("param1", "string");
            put("param2", 123);
        }
    };

    SqlRowSet rowSet = mock(SqlRowSet.class);
    when(template.queryForRowSet(queryString, parameterMap)).thenReturn(rowSet);

    QueryCursor<Map<String, Object>> cursor = query.runQuery(queryString, parameterMap);

    Assert.assertNotNull(cursor);
}

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

void addParents(List<Term> childTerms) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_TERM + ", term_expansion_id FROM term"
            + " INNER JOIN text ON term_text_id=text_id"
            + " INNER JOIN term_expansions ON term_id = termexp_term_id"
            + " WHERE term_project_id = :term_project_id" + " AND termexp_expansion_id IN (:child_terms)";

    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("term_project_id", this.getCurrentProjectId());
    List<Integer> termIds = new ArrayList<Integer>();
    Map<Integer, PostGresTerm> childTermMap = new HashMap<Integer, PostGresTerm>();
    for (Term childTerm : childTerms) {
        PostGresTerm termInternal = (PostGresTerm) childTerm;
        if (termInternal.getParentsInternal() == null) {
            termIds.add(termInternal.getId());
            termInternal.setParentsInternal(new TreeSet<Term>());
            childTermMap.put(termInternal.getId(), termInternal);
        }//from   www .j  av a2  s. co m
    }
    paramSource.addValue("child_terms", termIds);

    LOG.trace(sql);
    LogParameters(paramSource);

    SqlRowSet rs = jt.queryForRowSet(sql, paramSource);
    TermMapper termMapper = new TermMapper();
    List<Term> parentTerms = new ArrayList<Term>();
    while (rs.next()) {
        Term term = termMapper.mapRow(rs);
        parentTerms.add(term);
        int childId = rs.getInt("termexp_expansion_id");
        PostGresTerm childTerm = childTermMap.get(childId);
        childTerm.getParentsInternal().add(term);
    }
    if (parentTerms.size() > 0) {
        this.addParents(parentTerms);
    }
}

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

public Sentence loadSentence(int sentenceId) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_SENTENCE + ", " + SELECT_PHRASE + " FROM ftb_phrase p, ftb_sentence s"
            + " WHERE sentence_id=:sentence_id AND phrase_id=sentence_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("sentence_id", sentenceId);

    LOG.info(sql);//from w  w  w .  j a  va 2  s .c om
    TreebankDaoImpl.LogParameters(paramSource);
    Sentence sentence = null;
    SqlRowSet rowSet = jt.queryForRowSet(sql, paramSource);

    if (rowSet.next()) {
        PhraseMapper phraseMapper = new PhraseMapper(this.treebankServiceInternal);
        PhraseInternal phrase = (PhraseInternal) phraseMapper.mapRow(rowSet);
        SentenceMapper sentenceMapper = new SentenceMapper(this.treebankServiceInternal, phrase);
        sentence = sentenceMapper.mapRow(rowSet);
    }

    return sentence;
}

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

public Sentence loadFullSentence(int sentenceId) {
    PhraseMapper phraseMapper = new PhraseMapper(this.treebankServiceInternal);

    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_SENTENCE + ", " + SELECT_PHRASE + " FROM ftb_phrase p, ftb_sentence s"
            + " WHERE sentence_id=:sentence_id AND phrase_id=sentence_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("sentence_id", sentenceId);

    LOG.info(sql);//www .  j a  va2s. com
    TreebankDaoImpl.LogParameters(paramSource);
    Sentence sentence = null;
    SqlRowSet rowSet = jt.queryForRowSet(sql, paramSource);

    if (rowSet.next()) {
        PhraseInternal phrase = (PhraseInternal) phraseMapper.mapRow(rowSet);
        SentenceMapper sentenceMapper = new SentenceMapper(this.treebankServiceInternal, phrase);
        sentence = sentenceMapper.mapRow(rowSet);
        this.treebankServiceInternal.getObjectCache().putEntity(Phrase.class, sentence.getId(), sentence);

    }

    sql = "SELECT " + SELECT_PHRASE + " FROM ftb_phrase "
            + " INNER JOIN ftb_phrase_child ON pchild_child_id = phrase_id"
            + " WHERE pchild_phrase_id = :sentence_id" + " ORDER BY phrase_depth, phrase_position";

    paramSource = new MapSqlParameterSource();
    paramSource.addValue("sentence_id", sentenceId);

    LOG.info(sql);
    TreebankDaoImpl.LogParameters(paramSource);

    rowSet = jt.queryForRowSet(sql, paramSource);

    List<PhraseInternal> phrases = new ArrayList<PhraseInternal>();

    while (rowSet.next()) {
        PhraseInternal phrase = (PhraseInternal) phraseMapper.mapRow(rowSet);
        phrase = (PhraseInternal) this.treebankServiceInternal.getObjectCache().getOrPutEntity(Phrase.class,
                phrase.getId(), phrase);
        phrases.add(phrase);
    }

    for (PhraseInternal phrase : phrases) {
        PhraseInternal parent = (PhraseInternal) phrase.getParent();
        if (parent != null) {
            parent.getChildrenDB().add(phrase);
        }
    }

    List<PhraseUnit> phraseUnits = this.findAllPhraseUnits(sentence);

    PhraseInternal sentenceInternal = (PhraseInternal) sentence;

    for (PhraseUnit phraseUnit : phraseUnits) {
        PhraseInternal phrase = (PhraseInternal) phraseUnit.getPhrase();
        PhraseUnitInternal punit = (PhraseUnitInternal) phraseUnit;
        phrase.getPhraseUnitsDB().add(punit);
        sentenceInternal.getAllPhraseUnitsDB().add(phraseUnit);
    }

    this.findAllWordsAndLemmas(sentence, phraseUnits);

    this.treebankServiceInternal.getObjectCache().clearCache(Phrase.class);

    return sentence;
}

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

public List<PhraseUnitInternal> findPhraseUnits(Phrase phrase) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_PHRASE_UNIT + "," + SELECT_PHRASE_SUBUNIT + " FROM ftb_phrase_unit pu"
            + " LEFT JOIN ftb_phrase_subunit psu ON pu.punit_id = psu.psubunit_punit_id"
            + " WHERE punit_phrase_id = :phrase_id" + " ORDER BY punit_position, psubunit_position";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("phrase_id", phrase.getId());

    LOG.info(sql);/* w ww. j  a v  a 2s  .com*/
    TreebankDaoImpl.LogParameters(paramSource);
    SqlRowSet rowSet = jt.queryForRowSet(sql, paramSource);
    PhraseUnitMapper phraseUnitMapper = new PhraseUnitMapper(this.treebankServiceInternal);
    PhraseSubUnitMapper phraseSubUnitMapper = new PhraseSubUnitMapper(this.treebankServiceInternal);

    List<PhraseUnitInternal> phraseUnits = new ArrayList<PhraseUnitInternal>();
    int currentPunitId = 0;
    PhraseUnitInternal currentPhraseUnit = null;
    while (rowSet.next()) {
        int phraseUnitId = rowSet.getInt("punit_id");
        if (phraseUnitId != currentPunitId) {
            currentPhraseUnit = phraseUnitMapper.mapRow(rowSet);
            currentPhraseUnit.setSubunitsInternal(new ArrayList<PhraseSubunit>());
            phraseUnits.add(currentPhraseUnit);
            currentPunitId = phraseUnitId;
        }

        int phraseSubunitId = rowSet.getInt("psubunit_id");
        if (phraseSubunitId != 0) {
            PhraseSubunit psubunit = phraseSubUnitMapper.mapRow(rowSet);
            currentPhraseUnit.getSubunitsInternal().add(psubunit);
        }
    }
    return phraseUnits;
}

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

public List<Sentence> findSentences(TreebankFile treebankFile) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_SENTENCE + ", " + SELECT_PHRASE + " FROM ftb_sentence"
            + " INNER JOIN ftb_phrase ON phrase_id=sentence_id" + " WHERE sentence_file_id=:sentence_file_id"
            + " ORDER BY sentence_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("sentence_file_id", treebankFile.getId());

    LOG.info(sql);//from ww w  .  j  ava 2s  . co m
    TreebankDaoImpl.LogParameters(paramSource);

    List<Sentence> sentences = new ArrayList<Sentence>();
    SqlRowSet rowSet = jt.queryForRowSet(sql, paramSource);

    while (rowSet.next()) {
        PhraseMapper phraseMapper = new PhraseMapper(this.treebankServiceInternal);
        PhraseInternal phrase = (PhraseInternal) phraseMapper.mapRow(rowSet);
        SentenceMapper sentenceMapper = new SentenceMapper(this.treebankServiceInternal, phrase);
        Sentence sentence = sentenceMapper.mapRow(rowSet);
        sentences.add(sentence);
    }
    return sentences;
}

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

public List<PhraseUnit> findAllPhraseUnits(Phrase phrase) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_PHRASE_UNIT + "," + SELECT_PHRASE_SUBUNIT + " FROM ftb_phrase_unit pu"
            + " LEFT JOIN ftb_phrase_subunit psu ON pu.punit_id = psu.psubunit_punit_id"
            + " INNER JOIN ftb_phrase_child pc ON punit_phrase_id = pchild_child_id AND pchild_phrase_id = :pchild_phrase_id"
            + " ORDER BY punit_position, psubunit_position";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("pchild_phrase_id", phrase.getId());

    LOG.info(sql);/*w w  w .  j a v  a2  s  . com*/
    TreebankDaoImpl.LogParameters(paramSource);
    SqlRowSet rowSet = jt.queryForRowSet(sql, paramSource);
    PhraseUnitMapper phraseUnitMapper = new PhraseUnitMapper(this.treebankServiceInternal);
    PhraseSubUnitMapper phraseSubUnitMapper = new PhraseSubUnitMapper(this.treebankServiceInternal);

    List<PhraseUnit> phraseUnits = new ArrayList<PhraseUnit>();
    int currentPunitId = 0;
    PhraseUnitInternal currentPhraseUnit = null;
    while (rowSet.next()) {
        int phraseUnitId = rowSet.getInt("punit_id");
        if (phraseUnitId != currentPunitId) {
            currentPhraseUnit = phraseUnitMapper.mapRow(rowSet);
            currentPhraseUnit.setSubunitsInternal(new ArrayList<PhraseSubunit>());
            phraseUnits.add(currentPhraseUnit);
            currentPunitId = phraseUnitId;
        }

        int phraseSubunitId = rowSet.getInt("psubunit_id");
        if (phraseSubunitId != 0) {
            PhraseSubunit psubunit = phraseSubUnitMapper.mapRow(rowSet);
            currentPhraseUnit.getSubunitsInternal().add(psubunit);
        }
    }
    return phraseUnits;
}

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

@Override
public void findAllWordsAndLemmas(Phrase phrase, List<? extends PhraseUnit> phraseUnits) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT punit_id, w.word_id as w_word_id, w.word_text as w_word_text, w.word_original_text as w_word_original_text"
            + ", l.word_id as l_word_id, l.word_text as l_word_text, l.word_original_text as l_word_original_text"
            + " FROM ftb_phrase_unit pu"
            + " INNER JOIN ftb_phrase_child pc ON punit_phrase_id = pchild_child_id AND pchild_phrase_id = :pchild_phrase_id"
            + " INNER JOIN ftb_word w ON punit_word_id = w.word_id"
            + " INNER JOIN ftb_word l ON punit_lemma_id = l.word_id" + " ORDER BY punit_position";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("pchild_phrase_id", phrase.getId());

    LOG.info(sql);//  w ww. j  a v a  2 s  .com
    TreebankDaoImpl.LogParameters(paramSource);
    SqlRowSet rowSet = jt.queryForRowSet(sql, paramSource);
    WordMapper wordMapper = new WordMapper("w", this.treebankServiceInternal);
    WordMapper lemmaMapper = new WordMapper("l", this.treebankServiceInternal);
    while (rowSet.next()) {
        int phraseUnitId = rowSet.getInt("punit_id");
        Word word = wordMapper.mapRow(rowSet);
        Word lemma = lemmaMapper.mapRow(rowSet);
        for (PhraseUnit phraseUnit : phraseUnits) {
            if (phraseUnit.getId() == phraseUnitId) {
                PhraseUnitInternal iPhraseUnit = (PhraseUnitInternal) phraseUnit;
                iPhraseUnit.setWord(word);
                iPhraseUnit.setLemma(lemma);
                break;
            }
        }
    }
}