List of usage examples for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate queryForRowSet
@Override
public SqlRowSet queryForRowSet(String sql, Map<String, ?> paramMap) throws DataAccessException
From source file:com.joliciel.lefff.LefffDaoImpl.java
@Override public Map<String, List<LexicalEntry>> findEntryMap(List<String> categories) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); String sql = "SELECT " + SELECT_ENTRY + "," + SELECT_WORD + "," + SELECT_LEMMA + "," + SELECT_CATEGORY + "," + SELECT_PREDICATE + "," + SELECT_ATTRIBUTE + " FROM lef_entry" + " INNER JOIN lef_word ON entry_word_id = word_id" + " INNER JOIN lef_lemma ON entry_lemma_id = lemma_id" + " INNER JOIN lef_category ON entry_category_id = category_id" + " INNER JOIN lef_predicate ON entry_predicate_id = predicate_id" + " INNER JOIN lef_attribute ON entry_morph_id = attribute_id" + " WHERE entry_status < 3"; if (categories != null && categories.size() > 0) { sql += " AND category_code in (:categoryCodes)"; }/* w w w. ja va 2s .c om*/ sql += " ORDER BY entry_status, entry_id"; MapSqlParameterSource paramSource = new MapSqlParameterSource(); if (categories != null && categories.size() > 0) { paramSource.addValue("categoryCodes", categories); } LOG.info(sql); LefffDaoImpl.LogParameters(paramSource); double requiredCapacity = 500000; Map<String, List<LexicalEntry>> entryMap = new HashMap<String, List<LexicalEntry>>( ((int) Math.ceil(requiredCapacity / 0.75))); EntryMapper entryMapper = new EntryMapper(this.lefffServiceInternal); WordMapper wordMapper = new WordMapper(this.lefffServiceInternal); CategoryMapper categoryMapper = new CategoryMapper(this.lefffServiceInternal); LemmaMapper lemmaMapper = new LemmaMapper(this.lefffServiceInternal); PredicateMapper predicateMapper = new PredicateMapper(this.lefffServiceInternal); AttributeMapper attributeMapper = new AttributeMapper(this.lefffServiceInternal); SqlRowSet rowSet = jt.queryForRowSet(sql, paramSource); Map<Integer, Category> categoryMap = new HashMap<Integer, Category>(); Map<Integer, Predicate> predicateMap = new HashMap<Integer, Predicate>(); Map<Integer, Attribute> attributeMap = new HashMap<Integer, Attribute>(); Map<Integer, Lemma> lemmaMap = new HashMap<Integer, Lemma>(); while (rowSet.next()) { LefffEntryInternal entry = entryMapper.mapRow(rowSet); WordInternal word = wordMapper.mapRow(rowSet); entry.setWord(word); int categoryId = rowSet.getInt("category_id"); Category category = categoryMap.get(categoryId); if (category == null) { category = categoryMapper.mapRow(rowSet); categoryMap.put(categoryId, category); } entry.setCategory(category); int predicateId = rowSet.getInt("predicate_id"); Predicate predicate = predicateMap.get(predicateId); if (predicate == null) { predicate = predicateMapper.mapRow(rowSet); predicateMap.put(predicateId, predicate); } entry.setPredicate(predicate); int lemmaId = rowSet.getInt("lemma_id"); Lemma lemma = lemmaMap.get(lemmaId); if (lemma == null) { lemma = lemmaMapper.mapRow(rowSet); lemmaMap.put(lemmaId, lemma); } entry.setLemma(lemma); int attributeId = rowSet.getInt("attribute_id"); Attribute attribute = attributeMap.get(attributeId); if (attribute == null) { attribute = attributeMapper.mapRow(rowSet); attributeMap.put(attributeId, attribute); } entry.setMorphology(attribute); List<LexicalEntry> entries = entryMap.get(word.getText()); if (entries == null) { entries = new ArrayList<LexicalEntry>(); entryMap.put(word.getText(), entries); } entries.add(entry); } for (String word : entryMap.keySet()) { List<LexicalEntry> entries = entryMap.get(word); ArrayList<LexicalEntry> entriesArrayList = (ArrayList<LexicalEntry>) entries; entriesArrayList.trimToSize(); } return entryMap; }
From source file:com.joliciel.frenchTreebank.TreebankDaoImpl.java
public List<List<Entity>> findStuff(List<String> tablesToReturn, List<String> tables, List<String> conditions, List<String> orderBy) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); MapSqlParameterSource paramSource = new MapSqlParameterSource(); List<String> tableNames = new ArrayList<String>(); List<String> aliases = new ArrayList<String>(); for (String tableToReturn : tablesToReturn) { StringTokenizer st = new StringTokenizer(tableToReturn, " ", false); String tableName = st.nextToken().trim(); st.nextElement(); // skip the word "as" String alias = st.nextToken().trim(); tableNames.add(tableName);//w w w .j a v a 2s.co m aliases.add(alias); } String sql = "SELECT DISTINCT "; boolean firstOne = true; int i = 0; for (String tableName : tableNames) { String alias = aliases.get(i++); List<String> columns = null; if (tableName.equals("ftb_phrase")) columns = DaoUtils.getSelectArray(SELECT_PHRASE, alias); else if (tableName.equals("ftb_phrase_unit")) columns = DaoUtils.getSelectArray(SELECT_PHRASE_UNIT, alias); else if (tableName.equals("ftb_word")) columns = DaoUtils.getSelectArray(SELECT_WORD, alias); else if (tableName.equals("ftb_sentence")) columns = DaoUtils.getSelectArray(SELECT_SENTENCE, alias); else throw new TreebankException("Unsupported table for findStuff: " + tableName); for (String column : columns) { if (firstOne) { sql += column; firstOne = false; } else sql += ", " + column; } } firstOne = true; for (String table : tables) { if (firstOne) { sql += " FROM " + table; firstOne = false; } else sql += ", " + table; } firstOne = true; for (String condition : conditions) { if (firstOne) { sql += " WHERE " + condition; firstOne = false; } else { sql += " AND " + condition; } } if (orderBy.size() > 0) { firstOne = true; for (String column : orderBy) { if (firstOne) { sql += " ORDER BY " + column; firstOne = false; } else { sql += ", " + column; } } } LOG.info(sql); SqlRowSet rowSet = jt.queryForRowSet(sql, paramSource); List<List<Entity>> stuff = new ArrayList<List<Entity>>(); while (rowSet.next()) { List<Entity> oneRow = new ArrayList<Entity>(); i = 0; for (String tableName : tableNames) { String alias = aliases.get(i++); Entity entity = null; if (tableName.equals("ftb_phrase")) { PhraseMapper phraseMapper = new PhraseMapper(alias, this.treebankServiceInternal); Phrase phrase = phraseMapper.mapRow(rowSet); entity = phrase; } else if (tableName.equals("ftb_phrase_unit")) { PhraseUnitMapper phraseUnitMapper = new PhraseUnitMapper(alias, this.treebankServiceInternal); PhraseUnit phraseUnit = phraseUnitMapper.mapRow(rowSet); entity = phraseUnit; } else if (tableName.equals("ftb_word")) { WordMapper wordMapper = new WordMapper(alias, this.treebankServiceInternal); Word word = wordMapper.mapRow(rowSet); entity = word; } oneRow.add(entity); } i = 0; for (String tableName : tableNames) { String alias = aliases.get(i++); if (tableName.equals("ftb_sentence")) { // need to replace the phrase already created with a sentence int sentenceId = rowSet.getInt(alias + "_sentence_id"); PhraseInternal sentencePhrase = null; for (Entity entity : oneRow) { if (entity instanceof PhraseInternal) { if (entity.getId() == sentenceId) { sentencePhrase = (PhraseInternal) entity; break; } } } if (sentencePhrase == null) throw new TreebankException("Cannot return ftb_sentence without associated ftb_phrase"); SentenceMapper sentenceMapper = new SentenceMapper(alias, this.treebankServiceInternal, sentencePhrase); Sentence sentence = sentenceMapper.mapRow(rowSet); oneRow.remove(sentencePhrase); oneRow.add(sentence); } } stuff.add(oneRow); } return stuff; }