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

Introduction

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

Prototype

public NamedParameterJdbcTemplate(JdbcOperations classicJdbcTemplate) 

Source Link

Document

Create a new NamedParameterJdbcTemplate for the given classic Spring org.springframework.jdbc.core.JdbcTemplate .

Usage

From source file:dao.ScriptFinderDAO.java

public static ObjectNode getPagedScripts(JsonNode filterOpt, int page, int size) {
    ObjectNode result = Json.newObject();

    javax.sql.DataSource ds = getJdbcTemplate().getDataSource();
    DataSourceTransactionManager tm = new DataSourceTransactionManager(ds);
    TransactionTemplate txTemplate = new TransactionTemplate(tm);
    String scriptName = null;/*from   w  w w .java 2s .  c o m*/
    String scriptPath = null;
    String scriptType = null;
    String chainName = null;
    String jobName = null;
    String committerName = null;
    String committerEmail = null;
    if (filterOpt != null && (filterOpt.isContainerNode())) {
        if (filterOpt.has("scriptName")) {
            scriptName = filterOpt.get("scriptName").asText();
        }
        if (filterOpt.has("scriptPath")) {
            scriptPath = filterOpt.get("scriptPath").asText();
        }
        if (filterOpt.has("scriptType")) {
            scriptType = filterOpt.get("scriptType").asText();
        }
        if (filterOpt.has("chainName")) {
            chainName = filterOpt.get("chainName").asText();
        }
        if (filterOpt.has("jobName")) {
            jobName = filterOpt.get("jobName").asText();
        }
        if (filterOpt.has("committerName")) {
            committerName = filterOpt.get("committerName").asText();
        }
        if (filterOpt.has("committerEmail")) {
            committerEmail = filterOpt.get("committerEmail").asText();
        }
    }

    final String finalScriptName = scriptName;
    final String finalScriptPath = scriptPath;
    final String finalScriptType = scriptType;
    final String finalChainName = chainName;
    final String finalJobName = jobName;
    final String finalCommitterName = committerName;
    result = txTemplate.execute(new TransactionCallback<ObjectNode>() {
        public ObjectNode doInTransaction(TransactionStatus status) {

            List<Map<String, Object>> rows = null;
            String whereClause = "";
            boolean needAnd = false;
            Map<String, Object> params = new HashMap<String, Object>();
            if (StringUtils.isNotBlank(finalScriptName)) {
                if (StringUtils.isBlank(whereClause)) {
                    whereClause = " WHERE ";
                }
                if (needAnd) {
                    whereClause += " AND ";
                }
                whereClause += " script_name like :scriptname ";
                needAnd = true;
                params.put("scriptname", "%" + finalScriptName + "%");
            }
            if (StringUtils.isNotBlank(finalScriptPath)) {
                if (StringUtils.isBlank(whereClause)) {
                    whereClause = " WHERE ";
                }
                if (needAnd) {
                    whereClause += " AND ";
                }
                whereClause += " script_path like :scriptpath ";
                needAnd = true;
                params.put("scriptpath", "%" + finalScriptPath + "%");
            }
            if (StringUtils.isNotBlank(finalScriptType)) {
                if (StringUtils.isBlank(whereClause)) {
                    whereClause = " WHERE ";
                }
                if (needAnd) {
                    whereClause += " AND ";
                }
                whereClause += " script_type like :scripttype ";
                needAnd = true;
                params.put("scripttype", "%" + finalScriptType + "%");
            }
            if (StringUtils.isNotBlank(finalChainName)) {
                if (StringUtils.isBlank(whereClause)) {
                    whereClause = " WHERE ";
                }
                if (needAnd) {
                    whereClause += " AND ";
                }
                whereClause += " chain_name like :chainname ";
                needAnd = true;
                params.put("chainname", "%" + finalChainName + "%");
            }
            if (StringUtils.isNotBlank(finalJobName)) {
                if (StringUtils.isBlank(whereClause)) {
                    whereClause = " WHERE ";
                }
                if (needAnd) {
                    whereClause += " AND ";
                }
                whereClause += " job_name like :jobname ";
                needAnd = true;
                params.put("jobname", "%" + finalJobName + "%");
            }
            if (StringUtils.isNotBlank(finalCommitterName)) {
                if (StringUtils.isBlank(whereClause)) {
                    whereClause = " WHERE ";
                }
                if (needAnd) {
                    whereClause += " AND ";
                }
                whereClause += " ( committer_ldap like :committername or committer_name like :committername )";
                needAnd = true;
                params.put("committername", "%" + finalCommitterName + "%");
            }
            String query = GET_PAGED_SCRIPTS.replace("$WHERE_CLAUSE", whereClause);
            params.put("index", (page - 1) * size);
            params.put("size", size);
            NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(
                    getJdbcTemplate().getDataSource());
            rows = namedParameterJdbcTemplate.queryForList(query, params);
            long count = 0;
            try {
                count = getJdbcTemplate().queryForObject("SELECT FOUND_ROWS()", Long.class);
            } catch (EmptyResultDataAccessException e) {
                Logger.error("Exception = " + e.getMessage());
            }

            List<ScriptInfo> pagedScripts = new ArrayList<ScriptInfo>();
            for (Map row : rows) {
                int applicationID = (Integer) row.get(ScriptInfoRowMapper.APPLICATION_ID_COLUMN);
                int jobID = (Integer) row.get(ScriptInfoRowMapper.JOB_ID_COLUMN);
                String scriptUrl = (String) row.get(ScriptInfoRowMapper.SCRIPT_URL_COLUMN);
                String scriptPath = (String) row.get(ScriptInfoRowMapper.SCRIPT_PATH_COLUMN);
                String scriptType = (String) row.get(ScriptInfoRowMapper.SCRIPT_TYPE_COLUMN);
                String chainName = (String) row.get(ScriptInfoRowMapper.CHAIN_NAME_COLUMN);
                String jobName = (String) row.get(ScriptInfoRowMapper.JOB_NAME_COLUMN);
                String scriptName = (String) row.get(ScriptInfoRowMapper.SCRIPT_NAME_COLUMN);
                String committerName = (String) row.get(ScriptInfoRowMapper.COMMITTER_NAMES_COLUMN);
                String committerEmail = (String) row.get(ScriptInfoRowMapper.COMMITTER_EMAILS_COLUMN);
                ScriptInfo scriptInfo = new ScriptInfo();
                scriptInfo.applicationID = applicationID;
                scriptInfo.jobID = jobID;
                scriptInfo.scriptUrl = scriptUrl;
                scriptInfo.scriptPath = scriptPath;
                scriptInfo.scriptType = scriptType;
                scriptInfo.scriptName = scriptName;
                scriptInfo.chainName = chainName;
                scriptInfo.jobName = jobName;
                scriptInfo.committerName = committerName;
                scriptInfo.committerEmail = committerEmail;
                pagedScripts.add(scriptInfo);
            }

            ObjectNode resultNode = Json.newObject();
            resultNode.put("count", count);
            resultNode.put("page", page);
            resultNode.put("itemsPerPage", size);
            resultNode.put("totalPages", (int) Math.ceil(count / ((double) size)));
            resultNode.set("scripts", Json.toJson(pagedScripts));

            return resultNode;
        }
    });

    return result;
}

---

From source file:org.surfnet.cruncher.repository.StatisticsRepositoryImpl.java

@Override
public List<LoginEntry> getUnprocessedLoginEntries(int nrOfRecords) {
    Long aggregateStartingPoint = cruncherJdbcTemplate
            .queryForLong("select aggregatepoint from aggregate_meta_data");

    NamedParameterJdbcTemplate namedJdbcTemplate = new NamedParameterJdbcTemplate(ebJdbcTemplate);

    String query = "select * from log_logins where id > :startingPoint order by id LIMIT :batchSize";

    Map<String, Object> parameterMap = new HashMap<String, Object>();
    parameterMap.put("batchSize", nrOfRecords);
    parameterMap.put("startingPoint", aggregateStartingPoint);

    return namedJdbcTemplate.query(query, parameterMap, new RowMapper<LoginEntry>() {
        @Override/*from w ww  .j  ava2  s.c o  m*/
        public LoginEntry mapRow(ResultSet rs, int rowNum) throws SQLException {
            Long id = rs.getLong("id");
            String idpEntityId = rs.getString("idpentityid");
            String idpEntityName = rs.getString("idpentityname");
            Date loginDate = new Date(rs.getTimestamp("loginstamp").getTime());
            String spEntityId = rs.getString("spentityid");
            String spEntityName = rs.getString("spentityname");
            String userId = rs.getString("userid");
            return new LoginEntry(id, idpEntityId, idpEntityName, loginDate, spEntityId, spEntityName, userId);
        }
    });
}

---

From source file:com.joliciel.lefff.LefffDaoImpl.java

public Category loadCategory(int categoryId) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_CATEGORY + " FROM lef_category WHERE category_id=:category_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("category_id", categoryId);

    LOG.info(sql);//  ww w.j  ava2 s .c o m
    LefffDaoImpl.LogParameters(paramSource);
    Category category = null;
    try {
        category = (Category) jt.queryForObject(sql, paramSource,
                new CategoryMapper(this.getLefffServiceInternal()));
    } catch (EmptyResultDataAccessException ex) {
        ex.hashCode();
    }
    return category;
}

---

From source file:dao.AdvSearchDAO.java

public static List<String> getFlowNames(String applications) {
    List<String> flowNames = null;
    if (StringUtils.isNotBlank(applications)) {
        String[] appArray = applications.split(",");
        List<String> appList = Arrays.asList(appArray);
        Map<String, List> param = Collections.singletonMap("apps", appList);
        NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(
                getJdbcTemplate().getDataSource());
        flowNames = namedParameterJdbcTemplate.queryForList(GET_FLOW_NAMES_BY_APP, param, String.class);
    } else {//from   w w  w  .ja  v  a 2s . c  om
        flowNames = getJdbcTemplate().queryForList(GET_FLOW_NAMES, String.class);
    }

    return flowNames;
}

---

From source file:com.yahoo.sql4d.indexeragent.sql.DBAccessor.java

/**
 * A sql statement(select) from which result set is expected.
 * @param params// w w  w.  j a va  2s  .c  o  m
 * @param query
 * @return 
 */
public List<Map<String, Object>> query(Map<String, String> params, String query) {
    List<Map<String, Object>> result = null;
    Tuple2<DataSource, Connection> conn = null;
    try {
        conn = getConnection();
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(conn._1());
        result = jdbcTemplate.queryForList(query, params);
    } catch (Exception ex) {
        Logger.getLogger(DBAccessor.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        returnConnection(conn);
    }
    return result;
}

---

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

public void savePhrase(PhraseInternal phrase) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("phrase_ptype_id", phrase.getPhraseTypeId() == 0 ? null : phrase.getPhraseTypeId());
    paramSource.addValue("phrase_parent_id", phrase.getParent() == null ? null : phrase.getParent().getId());
    paramSource.addValue("phrase_function_id", phrase.getFunctionId() == 0 ? null : phrase.getFunctionId());
    paramSource.addValue("phrase_position", phrase.getPositionInPhrase());
    paramSource.addValue("phrase_depth", phrase.getDepth());

    if (phrase.isNew()) {
        String sql = "SELECT nextval('ftb_phrase_phrase_id_seq')";
        LOG.info(sql);//from   w ww.j  av  a  2 s  . c o  m
        int phraseId = jt.queryForInt(sql, paramSource);
        paramSource.addValue("phrase_id", phraseId);

        sql = "INSERT INTO ftb_phrase (phrase_id, phrase_ptype_id, phrase_parent_id, phrase_function_id, phrase_position, phrase_depth) "
                + "VALUES (:phrase_id, :phrase_ptype_id, :phrase_parent_id, :phrase_function_id, :phrase_position, :phrase_depth)";

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

        phrase.setId(phraseId);
    }
}

---

From source file:com.joliciel.jochre.security.SecurityDaoJdbc.java

@Override
public void saveParametersInternal(ParametersInternal parameters) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    MapSqlParameterSource paramSource = new MapSqlParameterSource();

    paramSource.addValue("param_last_failed_login", parameters.getLastFailedLoginAttempt());
    paramSource.addValue("param_captcha_interval", parameters.getCaptachaIntervalSeconds());
    String sql = null;/* www .  ja v a  2 s .  co  m*/

    paramSource.addValue("param_id", parameters.getId());

    sql = "UPDATE ocr_param" + " SET param_last_failed_login = :param_last_failed_login"
            + ", param_captcha_interval = :param_captcha_interval" + " WHERE param_id = :param_id";

    LOG.info(sql);
    logParameters(paramSource);
    jt.update(sql, paramSource);

}

---

From source file:com.fmguler.ven.Ven.java

/**
 * Set the DataSource to be used to access to the database
 *//*from w ww  . j a  v a2s  . com*/
public void setDataSource(DataSource dataSource) {
    if (dataSource == null)
        throw new RuntimeException("fmgVen - DataSource cannot be null");
    this.template = new NamedParameterJdbcTemplate(dataSource);
    mapper.setDataSource(dataSource);
}

---

From source file:com.fmguler.ven.QueryMapper.java

/**
 * @param dataSource used for accessing database
 *///from   ww w. j a  v a  2s . c  o m
public void setDataSource(DataSource dataSource) {
    if (dataSource == null)
        throw new RuntimeException("fmgVen - DataSource cannot be null");
    this.template = new NamedParameterJdbcTemplate(dataSource);
}

---

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

@Override
public List<Term> getTermsByFrequency(final int frequencyThreshold) {
    MONITOR.startTask("getTermsByFrequency");
    try {/*from   ww  w  . ja va  2 s  .co  m*/
        NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
        String sql = "SELECT " + SELECT_TERM + " FROM term" + " INNER JOIN text ON term_text_id=text_id"
                + " WHERE term_frequency >= :term_frequency" + " AND term_project_id = :term_project_id"
                + " ORDER BY term_frequency DESC, text_text";
        MapSqlParameterSource paramSource = new MapSqlParameterSource();
        paramSource.addValue("term_frequency", frequencyThreshold);
        paramSource.addValue("term_project_id", this.getCurrentProjectId());

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

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

---

• «
• 1
• 2
• 3
• 4
• 5
• 6
• 7
• 8
• 9
• 10
• 11
• 12
• 13
• 14
• 15
• 16
• 17
• 18
• 19
• 20
• 21
• 22
• 23
• 24
• 25
• 26
• 27
• 28
• 29
• 30
• 31
• 32
• 33
• »