Example usage for org.springframework.jdbc.core.namedparam MapSqlParameterSource addValue

List of usage examples for org.springframework.jdbc.core.namedparam MapSqlParameterSource addValue

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.namedparam.*
  5. MapSqlParameterSource
  6. addValue

Introduction

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

Prototype

public MapSqlParameterSource addValue(String paramName, @Nullable Object value)

Source Link

Document

Add a parameter to this parameter source.

Usage

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

public void savePhraseSubunit(PhraseSubunitInternal phraseSubunit) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("psubunit_punit_id",
            phraseSubunit.getPhraseUnit() == null ? null : phraseSubunit.getPhraseUnit().getId());
    paramSource.addValue("psubunit_word_id", phraseSubunit.getWordId() == 0 ? null : phraseSubunit.getWordId());
    paramSource.addValue("psubunit_position", phraseSubunit.getPosition());
    paramSource.addValue("psubunit_cat_id",
            phraseSubunit.getCategoryId() == 0 ? null : phraseSubunit.getCategoryId());
    paramSource.addValue("psubunit_subcat_id",
            phraseSubunit.getSubCategoryId() == 0 ? null : phraseSubunit.getSubCategoryId());
    paramSource.addValue("psubunit_morph_id",
            phraseSubunit.getMorphologyId() == 0 ? null : phraseSubunit.getMorphologyId());

    if (phraseSubunit.isNew()) {
        String sql = "SELECT nextval('ftb_phrase_subunit_psubunit_id_seq')";
        LOG.info(sql);//from ww  w.ja  v a2s.c  o  m
        int phraseSubunitId = jt.queryForInt(sql, paramSource);
        paramSource.addValue("psubunit_id", phraseSubunitId);

        sql = "INSERT INTO ftb_phrase_subunit (psubunit_id, psubunit_punit_id, psubunit_word_id, psubunit_position"
                + ", psubunit_cat_id, psubunit_subcat_id, psubunit_morph_id) "
                + "VALUES (:psubunit_id, :psubunit_punit_id, :psubunit_word_id, :psubunit_position"
                + ", :psubunit_cat_id, :psubunit_subcat_id, :psubunit_morph_id)";

        LOG.info(sql);
        TreebankDaoImpl.LogParameters(paramSource);
        jt.update(sql, paramSource);

        phraseSubunit.setId(phraseSubunitId);
    } else {
        paramSource.addValue("psubunit_id", phraseSubunit.getId());
        String sql = "UPDATE ftb_phrase_subunit" + " SET psubunit_punit_id=:psubunit_punit_id"
                + ", psubunit_word_id=:psubunit_word_id" + ", psubunit_position=:psubunit_position"
                + ", psubunit_cat_id=:psubunit_cat_id" + ", psubunit_subcat_id=:psubunit_subcat_id"
                + ", psubunit_morph_id=:psubunit_morph_id " + " WHERE psubunit_id = :psubunit_id";

        LOG.info(sql);
        TreebankDaoImpl.LogParameters(paramSource);
        jt.update(sql, paramSource);
    }
}

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

public void savePhraseUnit(PhraseUnitInternal phraseUnit) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("punit_word_id", phraseUnit.getWordId() == 0 ? null : phraseUnit.getWordId());
    paramSource.addValue("punit_phrase_id", phraseUnit.getPhraseId() == 0 ? null : phraseUnit.getPhraseId());
    paramSource.addValue("punit_position", phraseUnit.getPositionInSentence());
    paramSource.addValue("punit_lemma_id", phraseUnit.getLemmaId() == 0 ? null : phraseUnit.getLemmaId());
    paramSource.addValue("punit_cat_id", phraseUnit.getCategoryId() == 0 ? null : phraseUnit.getCategoryId());
    paramSource.addValue("punit_subcat_id",
            phraseUnit.getSubCategoryId() == 0 ? null : phraseUnit.getSubCategoryId());
    paramSource.addValue("punit_morph_id",
            phraseUnit.getMorphologyId() == 0 ? null : phraseUnit.getMorphologyId());
    paramSource.addValue("punit_compound", phraseUnit.isCompound());
    paramSource.addValue("punit_pos_in_phrase", phraseUnit.getPositionInPhrase());
    paramSource.addValue("punit_compound_next",
            phraseUnit.getNextCompoundPartId() == 0 ? null : phraseUnit.getNextCompoundPartId());
    paramSource.addValue("punit_guessed_postag_id",
            phraseUnit.getGuessedPosTagId() == 0 ? null : phraseUnit.getGuessedPosTagId());
    if (phraseUnit.isNew()) {
        String sql = "SELECT nextval('ftb_phrase_unit_punit_id_seq')";
        LOG.info(sql);//from   w w w.j av  a  2s. c  o  m
        int phraseUnitId = jt.queryForInt(sql, paramSource);
        paramSource.addValue("punit_id", phraseUnitId);

        sql = "INSERT INTO ftb_phrase_unit (punit_id, punit_word_id, punit_phrase_id, punit_position, punit_lemma_id, punit_cat_id"
                + ", punit_subcat_id, punit_morph_id, punit_compound, punit_pos_in_phrase, punit_compound_next, punit_guessed_postag_id) "
                + "VALUES (:punit_id, :punit_word_id, :punit_phrase_id, :punit_position, :punit_lemma_id, :punit_cat_id"
                + ", :punit_subcat_id, :punit_morph_id, :punit_compound, :punit_pos_in_phrase, :punit_compound_next, :punit_guessed_postag_id)";

        LOG.info(sql);
        TreebankDaoImpl.LogParameters(paramSource);
        jt.update(sql, paramSource);

        phraseUnit.setId(phraseUnitId);
    } else {
        paramSource.addValue("punit_id", phraseUnit.getId());
        String sql = "UPDATE ftb_phrase_unit" + " SET punit_word_id = :punit_word_id"
                + ", punit_phrase_id=:punit_phrase_id" + ", punit_position=:punit_position"
                + ", punit_lemma_id=:punit_lemma_id" + ", punit_cat_id=:punit_cat_id"
                + ", punit_subcat_id=:punit_subcat_id" + ", punit_morph_id=:punit_morph_id"
                + ", punit_compound=:punit_compound" + ", punit_pos_in_phrase=:punit_pos_in_phrase"
                + ", punit_compound_next=:punit_compound_next"
                + ", punit_guessed_postag_id=:punit_guessed_postag_id" + " WHERE punit_id=:punit_id";
        LOG.info(sql);
        TreebankDaoImpl.LogParameters(paramSource);
        jt.update(sql, paramSource);
    }
}

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);/*from w  ww .  j a va2 s.c  om*/
    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

@Override
public void deleteTextItem(int textItemId) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    String sql = "DELETE FROM ftb_text WHERE text_id = :text_id";
    paramSource.addValue("text_id", textItemId);
    LOG.info(sql);/*w ww. j a  v  a2  s  .  com*/
    TreebankDaoImpl.LogParameters(paramSource);
    jt.update(sql, paramSource);
}

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

public void deleteCategory(int categoryId) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    String sql = "DELETE FROM ftb_category WHERE cat_id = :cat_id";
    paramSource.addValue("cat_id", categoryId);
    LOG.info(sql);/*  www  . ja  v a 2  s  .c  o m*/
    TreebankDaoImpl.LogParameters(paramSource);
    jt.update(sql, paramSource);
}

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

@SuppressWarnings("unchecked")
@Override//from   ww  w  .  j ava  2 s  .c o m
public List<Integer> findSentenceIds(TreebankSubSet treebankSubSet, int numIds, int startId) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    String sql = "SELECT sentence_id FROM ftb_sentence" + " WHERE sentence_id >= :min_id";
    paramSource.addValue("min_id", startId);

    String andWord = " AND ";
    for (String whereClause : this.getWhereClausesForSubSet(treebankSubSet, paramSource)) {
        sql += andWord + whereClause;
    }

    sql += " ORDER BY sentence_id";
    if (numIds > 0) {
        sql += " LIMIT :maxResults";
        paramSource.addValue("maxResults", numIds);
    }
    LOG.info(sql);
    TreebankDaoImpl.LogParameters(paramSource);
    List<Integer> sentenceIds = jt.queryForList(sql, paramSource, Integer.class);

    return sentenceIds;
}

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

public Word loadWord(String text, String originalText) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();

    if (text == null)
        text = "";
    String sql = "SELECT " + SELECT_WORD + " FROM ftb_word" + " WHERE word_text=:word_text"
            + " AND word_original_text=:word_original_text";
    paramSource.addValue("word_text", text);
    paramSource.addValue("word_original_text", originalText);

    LOG.info(sql);/*from ww w . ja va  2 s.  com*/
    TreebankDaoImpl.LogParameters(paramSource);
    Word word = null;
    try {
        word = (Word) jt.queryForObject(sql, paramSource, new WordMapper(this.treebankServiceInternal));
    } catch (EmptyResultDataAccessException ex) {
        ex.hashCode();
    }
    return word;
}

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

private List<String> getWhereClausesForSubSet(TreebankSubSet treebankSubSet, MapSqlParameterSource paramSource,
        String prefix) {//from w ww . j  a  v a 2s. c  o m
    List<String> whereClauses = new ArrayList<String>();
    if (treebankSubSet.getFileNumbersToInclude().length > 0) {
        int i = 0;
        String includeClause = "(";
        boolean firstOne = true;
        for (int id : treebankSubSet.getFileNumbersToInclude()) {
            if (!firstOne)
                includeClause += " OR ";
            includeClause += "sentence_file_id % 10 = :" + prefix + "includeId" + i;
            paramSource.addValue(prefix + "includeId" + i, id);
            firstOne = false;
            i++;
        }
        includeClause += ")";
        whereClauses.add(includeClause);
    }
    if (treebankSubSet.getFileNumbersToExclude().length > 0) {
        int i = 0;
        for (int id : treebankSubSet.getFileNumbersToExclude()) {
            whereClauses.add("sentence_file_id % 10 != :" + prefix + "excludeId" + i);
            paramSource.addValue(prefix + "excludeId" + i, id);
            i++;
        }
    }
    return whereClauses;
}

From source file:com.p5solutions.core.jpa.orm.EntityPersisterImpl.java

/**
 * Process.//from   ww  w.  java 2  s  .c  o m
 * 
 * @param entityClass
 *          the entity class
 * @param entity
 *          the entity
 * @return the map sql parameter source
 * @see com.p5solutions.core.jpa.orm.EntityPersister#process(java.lang.Class, java.lang.Object)
 */
@Override
public MapSqlParameterSource process(Class<?> entityClass, Object entity) {

    // TODO needs serious cleaning up... some sort of state-machine type
    // processing.

    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    EntityDetail<?> entityDetail = getEntityUtility().getEntityDetail(entityClass);
    List<ParameterBinder> pbs = entityDetail.getParameterBinders();

    boolean isDebug = logger.isDebugEnabled();
    logger.debug("** Mapping values from entity -> " + entity.getClass() + " to DML parameter source.");

    for (ParameterBinder pb : pbs) {
        Object value = null;
        String bindingPath = pb.getBindingPath();
        String bindingPathSQL = pb.getBindingPathSQL();
        String debugMessage = "     ";

        if (pb.isPrimaryKey()) {
            if (isDebug) {
                debugMessage += "[* Id] ";
            }
            // TODO probably a good idea to implement @EmbeddedId
            // TODO probably a good idea to implement @IdClass

            // let the below code handle the value retrieval.
            // value = mapUtility.get(pb, entity, bindingPath);
        }

        if (pb.isColumn()) {
            value = mapUtility.get(pb, entity, bindingPath);
        } else if (pb.isEmbedded()) {
            value = mapUtility.get(pb, entity, bindingPath);
        } else if (pb.isJoinColumn()) {
            value = getJoinColumnValue(entity, pb);
        }

        if (isDebug) {
            debugMessage = "Binding parameter [" + pb.toString() + "]";
        }

        // check if the value is a generated value
        if (pb.isGeneratedValue() && value == null) {
            String sequenceName = pb.getSequenceName();
            value = getTransactionTemplate().getSequenceValue(sequenceName);

            // Map the sequence value to the entity's parameter
            mapUtility.map(pb, entity, value, bindingPath);

            if (isDebug) {
                debugMessage += " <Generated> using sequence name " + sequenceName;
            }
        }

        if (isDebug) {
            if (value != null) {
                debugMessage += " with value of " + value;
            } else {
                debugMessage += " with value of <DBNull>";
            }

            logger.debug(debugMessage);
        }

        // use the binding path as the binding name of the sql paramater
        // source since embedded, or join objects can be multi-level depths.
        paramSource.addValue(bindingPathSQL, value);
    }
    return paramSource;
}

From source file:org.ojbc.adapters.analyticsstaging.custody.dao.AnalyticalDatastoreDAOImpl.java

private void deleteCustodyStatusChanges(Integer bookingId) {

    jdbcTemplate.update("DELETE FROM CustodyStatusChangeCharge  " + "WHERE CustodyStatusChangeArrestID IN "
            + "   (SELECT csca.CustodyStatusChangeArrestID from CustodyStatusChangeArrest csca "
            + "    RIGHT JOIN CustodyStatusChange csc ON csc.CustodyStatusChangeID = csca.CustodyStatusChangeID "
            + "      WHERE csc.bookingID = ? )", bookingId);

    jdbcTemplate.update("DELETE FROM CustodyStatusChangeArrest " + "WHERE CustodyStatusChangeID IN "
            + "   (SELECT CustodyStatusChangeID from CustodyStatusChange " + "      WHERE bookingID = ? )",
            bookingId);//from www .  j  a va  2 s.  c om

    jdbcTemplate.update("DELETE FROM BehavioralHealthEvaluation " + "WHERE BehavioralHealthAssessmentID IN "
            + "   (SELECT bha.BehavioralHealthAssessmentID from BehavioralHealthAssessment bha "
            + "      RIGHT JOIN Person p ON p.PersonId = bha.PersonId "
            + "      LEFT JOIN CustodyStatusChange csc ON csc.personId = p.personId "
            + "      WHERE csc.bookingId = ? )", bookingId);

    jdbcTemplate
            .update("DELETE FROM BehavioralHealthAssessmentCategory " + "WHERE BehavioralHealthAssessmentID IN "
                    + "   (SELECT bha.BehavioralHealthAssessmentID from BehavioralHealthAssessment bha "
                    + "      RIGHT JOIN Person p ON p.PersonId = bha.PersonId "
                    + "      LEFT JOIN CustodyStatusChange csc ON csc.personId = p.personId "
                    + "      WHERE csc.bookingId = ? )", bookingId);

    jdbcTemplate.update("DELETE FROM PrescribedMedication " + "WHERE BehavioralHealthAssessmentID IN "
            + "   (SELECT bha.BehavioralHealthAssessmentID from BehavioralHealthAssessment bha "
            + "      LEFT JOIN Person p ON p.PersonId = bha.PersonId "
            + "      LEFT JOIN CustodyStatusChange csc ON csc.personId = p.personId "
            + "      WHERE csc.bookingId = ? )", bookingId);

    jdbcTemplate.update("DELETE FROM Treatment " + "WHERE BehavioralHealthAssessmentID IN "
            + "   (SELECT bha.BehavioralHealthAssessmentID from BehavioralHealthAssessment bha "
            + "      LEFT JOIN Person p ON p.PersonId = bha.PersonId "
            + "      LEFT JOIN CustodyStatusChange csc ON csc.personId = p.personId "
            + "      WHERE csc.bookingId = ? )", bookingId);

    jdbcTemplate.update("DELETE FROM BehavioralHealthAssessment " + "WHERE PersonId IN "
            + "   (SELECT p.PersonId FROM Person p "
            + "      LEFT JOIN CustodyStatusChange csc ON csc.personId = p.personId "
            + "      WHERE csc.bookingId = ? )", bookingId);

    List<Integer> personIds = jdbcTemplate.queryForList(
            "SELECT PersonID from CustodyStatusChange WHERE BookingID = ?", Integer.class, bookingId);
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("personIds", personIds);

    jdbcTemplate.update("DELETE FROM CustodyStatusChange WHERE bookingId = ? ", bookingId);

    if (personIds.size() > 0) {
        namedParameterJdbcTemplate.update("DELETE FROM Person " + "WHERE PersonID IN (:personIds) ",
                parameters);
    }

}