List of usage examples for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate NamedParameterJdbcTemplate
public NamedParameterJdbcTemplate(JdbcOperations classicJdbcTemplate)
From source file:dao.LineageDAO.java
public static void getNodes(LineagePathInfo pathInfo, int level, int upLevel, int downLevel, LineageNode currentNode, List<LineageNode> allSourceNodes, List<LineageNode> allTargetNodes, Map<Long, List<LineageNode>> addedSourceNodes, Map<Long, List<LineageNode>> addedTargetNodes, Map<Long, LineageNode> addedJobNodes, int lookBackTime) { if (upLevel < 1 && downLevel < 1) { return;//from ww w .j a va 2s . c om } if (currentNode != null) { if (StringUtils.isBlank(currentNode.source_target_type)) { Logger.error("Source target type is not available"); Logger.error(currentNode.abstracted_path); return; } else if (currentNode.source_target_type.equalsIgnoreCase("target") && downLevel <= 0) { Logger.warn( "Dataset " + currentNode.abstracted_path + " downLevel = " + Integer.toString(downLevel)); return; } else if (currentNode.source_target_type.equalsIgnoreCase("source") && upLevel <= 0) { Logger.warn("Dataset " + currentNode.abstracted_path + " upLevel = " + Integer.toString(upLevel)); return; } } List<String> nameList = getLiasDatasetNames(pathInfo.filePath); List<Map<String, Object>> rows = null; MapSqlParameterSource parameters = new MapSqlParameterSource(); parameters.addValue("names", nameList); NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate( getJdbcTemplate().getDataSource()); parameters.addValue("days", lookBackTime); if (currentNode != null) { if (currentNode.source_target_type.equalsIgnoreCase("source")) { rows = namedParameterJdbcTemplate.queryForList(GET_UP_LEVEL_JOB, parameters); } else { parameters.addValue("type", currentNode.source_target_type); rows = namedParameterJdbcTemplate.queryForList(GET_JOB_WITH_SOURCE, parameters); } } else { rows = namedParameterJdbcTemplate.queryForList(GET_JOB, parameters); } if (rows != null) { for (Map row : rows) { LineageNode node = new LineageNode(); Object jobExecIdObject = row.get("job_exec_id"); if (jobExecIdObject == null) { continue; } Long jobExecId = ((BigInteger) jobExecIdObject).longValue(); if (addedJobNodes.get(jobExecId) != null) { continue; } node._sort_list = new ArrayList<String>(); node.node_type = "script"; node.job_type = (String) row.get("job_type"); node.cluster = (String) row.get("cluster"); node.job_name = (String) row.get("job_name"); node.job_path = (String) row.get("flow_path") + "/" + node.job_name; node.exec_id = jobExecId; node.operation = (String) row.get("operation"); node.source_target_type = (String) row.get("source_target_type"); node.level = level; node._sort_list.add("cluster"); node._sort_list.add("job_path"); node._sort_list.add("job_name"); node._sort_list.add("job_type"); node._sort_list.add("job_start_time"); node._sort_list.add("job_end_time"); node._sort_list.add("exec_id"); addedJobNodes.put(jobExecId, node); List<LineageNode> sourceNodeList = new ArrayList<LineageNode>(); List<LineageNode> targetNodeList = new ArrayList<LineageNode>(); int applicationID = (int) row.get("app_id"); Long jobId = ((BigInteger) row.get("job_exec_id")).longValue(); List<Map<String, Object>> relatedDataRows = null; if (node.source_target_type.equalsIgnoreCase("source") && node.operation.equalsIgnoreCase("JDBC Read")) { MapSqlParameterSource lassenParams = new MapSqlParameterSource(); lassenParams.addValue("names", nameList); lassenParams.addValue("appid", applicationID); lassenParams.addValue("execid", jobId); relatedDataRows = namedParameterJdbcTemplate.queryForList(GET_DATA_FILTER_OUT_LASSEN, lassenParams); } else { relatedDataRows = getJdbcTemplate().queryForList(GET_DATA, applicationID, jobId); } if (relatedDataRows != null) { for (Map relatedDataRow : relatedDataRows) { String abstractedObjectName = (String) relatedDataRow.get("abstracted_object_name"); if (abstractedObjectName.startsWith("/tmp/")) { continue; } String relatedSourceType = (String) relatedDataRow.get("source_target_type"); LineageNode relatedNode = new LineageNode(); relatedNode._sort_list = new ArrayList<String>(); relatedNode.node_type = "data"; relatedNode.level = level; relatedNode.source_target_type = relatedSourceType; relatedNode.abstracted_path = (String) relatedDataRow.get("abstracted_object_name"); relatedNode.storage_type = ((String) relatedDataRow.get("storage_type")).toLowerCase(); relatedNode.job_start_unix_time = (Long) relatedDataRow.get("job_start_unixtime"); relatedNode.job_end_unix_time = (Long) relatedDataRow.get("job_finished_unixtime"); relatedNode.job_start_time = DateFormat.format(relatedDataRow.get("start_time").toString()); relatedNode.job_end_time = DateFormat.format(relatedDataRow.get("end_time").toString()); node.job_start_unix_time = relatedNode.job_start_unix_time; node.job_end_unix_time = relatedNode.job_end_unix_time; node.job_start_time = relatedNode.job_start_time; node.job_end_time = relatedNode.job_end_time; relatedNode.operation = (String) relatedDataRow.get("operation"); LineagePathInfo info = new LineagePathInfo(); info.filePath = relatedNode.abstracted_path; info.storageType = relatedNode.storage_type; relatedNode.urn = utils.Lineage.convertToURN(info); relatedNode._sort_list.add("abstracted_path"); relatedNode._sort_list.add("storage_type"); relatedNode._sort_list.add("urn"); if (relatedSourceType.equalsIgnoreCase("source")) { if (node.source_target_type.equalsIgnoreCase("target") || utils.Lineage.isInList(nameList, relatedNode.abstracted_path)) { sourceNodeList.add(relatedNode); allSourceNodes.add(relatedNode); } } else if (relatedSourceType.equalsIgnoreCase("target")) { if (node.source_target_type.equalsIgnoreCase("source") || utils.Lineage.isInList(nameList, relatedNode.abstracted_path)) { targetNodeList.add(relatedNode); allTargetNodes.add(relatedNode); } } } if (sourceNodeList.size() > 0) { addedSourceNodes.put(jobExecId, sourceNodeList); } if (targetNodeList.size() > 0) { addedTargetNodes.put(jobExecId, targetNodeList); } } } } if ((allSourceNodes != null) && (allSourceNodes.size() > 0) && (upLevel > 1)) { List<LineageNode> currentSourceNodes = new ArrayList<LineageNode>(); currentSourceNodes.addAll(allSourceNodes); for (LineageNode sourceNode : currentSourceNodes) { LineagePathInfo subPath = new LineagePathInfo(); subPath.storageType = sourceNode.storage_type; subPath.filePath = sourceNode.abstracted_path; if (sourceNode.level == level) { getObjectAdjacentNode(subPath, level + 1, upLevel - 1, 0, sourceNode, allSourceNodes, allTargetNodes, addedSourceNodes, addedTargetNodes, addedJobNodes, lookBackTime); } } } if ((allTargetNodes != null) && (allTargetNodes.size() > 0) && (downLevel > 1)) { List<LineageNode> currentTargetNodes = new ArrayList<LineageNode>(); currentTargetNodes.addAll(allTargetNodes); for (LineageNode targetNode : currentTargetNodes) { LineagePathInfo subPath = new LineagePathInfo(); subPath.storageType = targetNode.storage_type; subPath.filePath = targetNode.abstracted_path; if (targetNode.level == level) { getObjectAdjacentNode(subPath, level - 1, 0, downLevel - 1, targetNode, allSourceNodes, allTargetNodes, addedSourceNodes, addedTargetNodes, addedJobNodes, lookBackTime); } } } }
From source file:com.joliciel.talismane.terminology.postgres.PostGresTerminologyBase.java
String getFileName(int fileId) { String fileName = fileIdMap.get(fileId); if (fileName == null) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); String sql = "SELECT file_name FROM file WHERE file_id=:file_id"; MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("file_id", fileId); LOG.trace(sql);// w ww . j a va2 s . c o m LogParameters(paramSource); fileName = (String) jt.queryForObject(sql, paramSource, String.class); fileIdMap.put(fileId, fileName); } return fileName; }
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 ava2 s . c o m TreebankDaoImpl.LogParameters(paramSource); @SuppressWarnings("unchecked") List<Word> words = jt.query(sql, paramSource, new WordMapper(this.treebankServiceInternal)); return words; }
From source file:com.joliciel.talismane.terminology.postgres.PostGresTerminologyBase.java
int getTextId(String text) { int textId = 0; NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); String sql = "SELECT text_id FROM text WHERE text_text=:text_text"; MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("text_text", text); LOG.trace(sql);//w w w .j av a 2s . c om LogParameters(paramSource); try { textId = jt.queryForInt(sql, paramSource); } catch (EmptyResultDataAccessException ex) { // do nothing } if (textId == 0) { sql = "SELECT nextval('seq_text_id')"; LOG.trace(sql); textId = jt.queryForInt(sql, paramSource); paramSource.addValue("text_id", textId); sql = "INSERT INTO text (text_id, text_text)" + " VALUES (:text_id, :text_text)"; LOG.trace(sql); LogParameters(paramSource); jt.update(sql, paramSource); } return textId; }
From source file:com.joliciel.jochre.graphics.GraphicsDaoJdbc.java
@Override public void deleteJochreImage(JochreImage image) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("image_id", image.getId()); String sql = null;/*from www .ja v a2 s . c om*/ sql = "delete from ocr_split where split_shape_id in (" + " select shape_id from ocr_shape" + " inner join ocr_group on shape_group_id = group_id" + " inner join ocr_row on group_row_id = row_id" + " inner join ocr_paragraph on row_paragraph_id = paragraph_id" + " WHERE paragraph_image_id = :image_id)"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); sql = "delete from ocr_shape where shape_group_id in (" + " select group_id from ocr_group" + " inner join ocr_row on group_row_id = row_id" + " inner join ocr_paragraph on row_paragraph_id = paragraph_id" + " WHERE paragraph_image_id = :image_id)"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); sql = "delete from ocr_group where group_row_id in (" + " select row_id from ocr_row" + " inner join ocr_paragraph on row_paragraph_id = paragraph_id" + " WHERE paragraph_image_id = :image_id)"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); sql = "delete from ocr_row where row_paragraph_id in (" + " select paragraph_id from ocr_paragraph" + " WHERE paragraph_image_id = :image_id)"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); sql = "delete from ocr_paragraph" + " where paragraph_image_id = :image_id"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); sql = "delete from ocr_image" + " WHERE image_id = :image_id"; LOG.debug(sql); logParameters(paramSource); jt.update(sql, paramSource); }
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. ja v a 2 s. c om TreebankDaoImpl.LogParameters(paramSource); @SuppressWarnings("unchecked") List<Word> words = jt.query(sql, paramSource, new WordMapper(this.treebankServiceInternal)); return words; }
From source file:com.joliciel.lefff.LefffDaoImpl.java
@Override public void saveAttributes(LefffEntryInternal entry) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("entatt_entry_id", entry.getId()); String sql = "DELETE FROM lef_entry_attribute WHERE entatt_entry_id = :entatt_entry_id"; LOG.info(sql);/*from www . j a va 2 s .c o m*/ LefffDaoImpl.LogParameters(paramSource); jt.update(sql, paramSource); for (Attribute attribute : entry.getAttributes()) { paramSource = new MapSqlParameterSource(); paramSource.addValue("entatt_entry_id", entry.getId()); paramSource.addValue("entatt_attribute_id", attribute.getId()); sql = "INSERT INTO lef_entry_attribute (entatt_entry_id, entatt_attribute_id)" + " VALUES (:entatt_entry_id, :entatt_attribute_id)"; LOG.info(sql); LefffDaoImpl.LogParameters(paramSource); jt.update(sql, paramSource); } }
From source file:com.joliciel.talismane.terminology.postgres.PostGresTerminologyBase.java
int getFileId(String fileName) { int fileId = 0; Integer fileIdObj = filenameMap.get(fileName); if (fileIdObj == null) { NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource()); String sql = "SELECT file_id FROM file WHERE file_name=:file_name"; MapSqlParameterSource paramSource = new MapSqlParameterSource(); paramSource.addValue("file_name", fileName); LOG.trace(sql);//from w w w.j a v a 2 s . c o m LogParameters(paramSource); try { fileId = jt.queryForInt(sql, paramSource); } catch (EmptyResultDataAccessException ex) { // do nothing } if (fileId == 0) { sql = "SELECT nextval('seq_file_id')"; LOG.trace(sql); fileId = jt.queryForInt(sql, paramSource); paramSource.addValue("file_id", fileId); sql = "INSERT INTO file (file_id, file_name)" + " VALUES (:file_id, :file_name)"; LOG.trace(sql); LogParameters(paramSource); jt.update(sql, paramSource); } filenameMap.put(fileName, fileId); } else { fileId = fileIdObj.intValue(); } return fileId; }
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);/*from w w w.jav a2s . c o m*/ 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.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)"; }// ww w. j a va 2s . co m 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; }