Example usage for org.springframework.jdbc.core.namedparam MapSqlParameterSource 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:br.com.asisprojetos.DAO.TBRelatorioDiagnosticoDAO.java

/**
 * Busca cliente e suas filiais//from ww w . j a  va  2s.  c  om
 * @param codCliente
 * @return 
 **/
public String getHierarchyClients(String codCliente) {

    List<String> listCodCliente = Arrays.asList(StringUtils.split(codCliente, ','));

    String SQL = "SELECT" + " GROUP_CONCAT(COD_CLIENTE) " + " FROM " + " SPF_TBCLIENTE " + " WHERE "
            + " COD_MATRIZ IN ( :codCliente ) " + " AND COD_MATRIZ <> COD_CLIENTE ";

    MapSqlParameterSource namedParameters = new MapSqlParameterSource();

    namedParameters.addValue("codCliente", listCodCliente);

    String groupCliente = null;

    try {
        groupCliente = getNamedParameterJdbcTemplate().queryForObject(SQL, namedParameters, String.class);
    } catch (DataAccessException ex) {
        logger.error("Erro ao efetuar busca no banco de dados. Message {}", ex);
    }

    if (groupCliente == null) {
        return codCliente;
    } else {
        return codCliente + "," + getHierarchyClients(groupCliente);
    }

}

---

From source file:com.joliciel.jochre.boundaries.BoundaryDaoJdbc.java

@Override
public List<Split> findSplits(Shape shape) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_SPLIT + " FROM ocr_split WHERE split_shape_id=:split_shape_id"
            + " ORDER BY split_position";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("split_shape_id", shape.getId());

    LOG.debug(sql);//from  ww w  . ja  va  2s.c om
    logParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<Split> splits = jt.query(sql, paramSource, new SplitMapper(this.getBoundaryServiceInternal()));

    return splits;
}

---

From source file:br.com.asisprojetos.DAO.TBRelatorioDiagnosticoDAO.java

public List<Map<String, Object>> getConsolidatedResults(
        List<String> listProtocols/*, List<String> listCodCliente*/ ) {

    List<Map<String, Object>> listResults = null;

    String SQL = " SELECT " + " tf.cor as COR, " + " tf.COD_FORMULA as COD_FORMULA  " + " FROM "
            + " SPF_TBITEM_RESULT tr   " + " INNER JOIN " + " SPF_TBFORMULA tf  "
            + " on tr.COD_FORMULA=tf.COD_FORMULA  " + " INNER JOIN " + " SPF_TBCTRL_PROCESSO  tcp   "
            + "  on tr.NUM_PROT=tcp.NUM_PROT cross  " + " JOIN " + " SPF_TBFORMULA tf2   " + "  WHERE "
            + " tr.COD_FORMULA = tf2.COD_FORMULA  " + " and ( tcp.NUM_PROT in ( :numProtocol ) )  "
            + " and ( tf2.NIVEL_FORMULA not in  (  '4' , '5' )  ) " +
            //" and ( tcp.COD_CLIENTE in ( :codCliente  ) ) " +
            " GROUP BY  tf.COD_FORMULA  ORDER BY tf.cor DESC ";

    MapSqlParameterSource namedParameters = new MapSqlParameterSource();

    namedParameters.addValue("numProtocol", listProtocols);
    //namedParameters.addValue( "codCliente" , listCodCliente );

    try {//from w w  w  . ja v a  2  s .  c  o  m
        listResults = getNamedParameterJdbcTemplate().queryForList(SQL, namedParameters);
    } catch (DataAccessException ex) {
        logger.error("Erro ao efetuar busca no banco de dados. Message {}", ex);
    }

    return listResults;

}

---

From source file:br.com.asisprojetos.DAO.TBRelatorioDiagnosticoDAO.java

public List<Map<String, Object>> getConsolidatedRecurrentResults(
        List<String> listProtocols/*, List<String> listCodCliente*/ ) {

    List<Map<String, Object>> listResults = null;

    String SQL = " SELECT T.COD_FORMULA, " + " T.NOM_FORMULA, " + " COUNT(T.COD_FORMULA) AS RECORRENCIA "
            + " FROM " + " (SELECT P.NUM_PROT, " + " F.NOM_FORMULA, " + " F.COD_FORMULA "
            + " FROM SPF_TBCTRL_PROCESSO P " + " INNER JOIN SPF_TBITEM_RESULT IR ON P.NUM_PROT = IR.NUM_PROT "
            + " INNER JOIN SPF_TBFORMULA F ON IR.COD_FORMULA = F.COD_FORMULA "
            + " WHERE P.NUM_PROT IN ( :numProtocol ) " + "   AND F.NIVEL_FORMULA NOT IN ('4', '5') " +
            //"   AND P.COD_CLIENTE IN ( :codCliente ) " + 
            " GROUP BY P.NUM_PROT, F.COD_FORMULA) AS T " + " GROUP BY T.COD_FORMULA "
            + " ORDER BY COUNT(T.COD_FORMULA) DESC LIMIT 5  ";

    MapSqlParameterSource namedParameters = new MapSqlParameterSource();

    namedParameters.addValue("numProtocol", listProtocols);
    //namedParameters.addValue( "codCliente" , listCodCliente );

    try {//from   w  w w  .j a v a2 s  .c  o m
        listResults = getNamedParameterJdbcTemplate().queryForList(SQL, namedParameters);
    } catch (DataAccessException ex) {
        logger.error("Erro ao efetuar busca no banco de dados. Message {}", ex);
    }

    return listResults;

}

---

From source file:com.joliciel.jochre.boundaries.BoundaryDaoJdbc.java

@Override
public Split loadSplit(int splitId) {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_SPLIT + " FROM ocr_split WHERE split_id=:split_id";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();
    paramSource.addValue("split_id", splitId);

    LOG.debug(sql);// w w w  .  ja va 2 s.  c  om
    logParameters(paramSource);
    Split split = null;
    try {
        split = (Split) jt.queryForObject(sql, paramSource, new SplitMapper(this.getBoundaryServiceInternal()));
    } catch (EmptyResultDataAccessException ex) {
        ex.hashCode();
    }
    return split;
}

---

From source file:br.com.asisprojetos.DAO.TBRelatorioDiagnosticoDAO.java

/**
 * Lista os protocolos para Cod Produto =1 Sped Fiscal , das Matriz e filiais 
 * @param listCodCliente/*  w  ww.  java  2s  . c om*/
 * @param dataIniProc
 * @param dataFimProc
 * @return 
 **/
public List<String> getProtocols(List<String> listCodCliente, String dataIniProc, String dataFimProc) {

    //String dataIniProc="2015-01-01 00:00:00";
    //String dataFimProc="2015-02-01 23:59:59";  
    //String cnpj="12359822000142";
    List<String> listProtocols = null;

    String SQL = "SELECT " + "tp.NUM_PROT  " + "FROM " + " SPF_TBITEM_RESULT tr " + " INNER JOIN "
            + " SPF_TBCTRL_PROCESSO  tp " + " ON tr.NUM_PROT=tp.NUM_PROT " + " WHERE " + " tp.COD_PRODUTO= 1 "
            + " and tp.IS_TRIAL=0 " + " and tp.SITUACAO=4 " + " and tp.IN_ATIVO='A' " + " and ( "
            + " tp.COD_CLIENTE in ( " + " :codCliente " + " ) " + " ) " +
            //" and tp.CNPJ=:cnpj " +  
            " and tp.DATA_INIC_PROC>=:dataIniProc " + " and tp.DATA_FIM_PROC<=:dataFimProc " + " GROUP BY "
            + " tp.NUM_PROT " + " ORDER BY " + " tp.NUM_PROT desc  ";

    MapSqlParameterSource namedParameters = new MapSqlParameterSource();

    namedParameters.addValue("codCliente", listCodCliente);
    namedParameters.addValue("dataIniProc", dataIniProc);
    namedParameters.addValue("dataFimProc", dataFimProc);
    //namedParameters.addValue( "cnpj" , cnpj );

    try {
        listProtocols = getNamedParameterJdbcTemplate().queryForList(SQL, namedParameters, String.class);
    } catch (DataAccessException ex) {
        logger.error("Erro ao efetuar busca no banco de dados. Message {}", ex);
    }

    return listProtocols;

}

---

From source file:com.perry.infrastructure.call.CallDaoServiceImpl.java

@Override
public List<Call> getByIds(List<Long> ids) {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("call_ids", ids);

    String sql = "select * from calls where call_id in (:call_ids)";
    List<Call> callList = namedParameterJdbcTemplate.query(sql, params, new CallRowMapper());

    return callList;
}

---

From source file:com.perry.infrastructure.call.CallDaoServiceImpl.java

@Override
public void unAssignTruck(long callId) {
    String sql = "update calls set truck_id = 0 where call_id = :callId";

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("callId", callId);

    namedParameterJdbcTemplate.update(sql, params);

}

---

From source file:com.perry.infrastructure.call.CallDaoServiceImpl.java

@Override
public void delete(long callId) {
    String sql = "delete from calls where call_id = :callId";

    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("callId", callId);

    namedParameterJdbcTemplate.update(sql, params);

}

---

From source file:org.sakuli.services.forwarder.database.dao.impl.DaoTestCaseImpl.java

@Override
public void saveTestCaseResult(final TestCase testCase) {
    LOGGER.info("Save results for test case \"" + testCase.getId() + "\"");

    //create a map for the sql parameters
    MapSqlParameterSource tcParameters = new MapSqlParameterSource();
    tcParameters.addValue("sakuli_suites_id", testSuite.getDbPrimaryKey());
    tcParameters.addValue("caseID", testCase.getId());
    tcParameters.addValue("result", testCase.getState().getErrorCode());
    tcParameters.addValue("result_desc", testCase.getState());
    tcParameters.addValue("name", testCase.getName());
    tcParameters.addValue("guid", testSuite.getGuid());
    tcParameters.addValue("start", testCase.getStartDateAsUnixTimestamp());
    tcParameters.addValue("stop", testCase.getStopDateAsUnixTimestamp());
    int warningTime = testCase.getWarningTime();
    tcParameters.addValue("warning", (warningTime != 0) ? warningTime : null);
    int criticalTime = testCase.getCriticalTime();
    tcParameters.addValue("critical", (criticalTime != 0) ? criticalTime : null);
    tcParameters.addValue("browser", testSuite.getBrowserInfo());
    tcParameters.addValue("lastpage", testCase.getLastURL());

    //try to save the screenshot
    tcParameters.addValue("screenshot", getScreenshotAsSqlLobValue(testCase), Types.BLOB);
    tcParameters.addValue("duration", testCase.getDuration());
    tcParameters.addValue("msg", testCase.getExceptionMessages(true));

    //generate the sql-statement
    SimpleJdbcInsert insertTCResults = new SimpleJdbcInsert(getDataSource()).withTableName("sakuli_cases")
            .usingGeneratedKeyColumns("id");

    LOGGER.debug("write the following values to 'sakuli_cases': " + tcParameters.getValues()
            + " => now execute ....");

    int dbPrimaryKey = insertTCResults.executeAndReturnKey(tcParameters).intValue();

    LOGGER.info("test case '" + testCase.getId() + "' has been written to 'sahi_cases' with  primaryKey="
            + dbPrimaryKey);/*from   w  w  w. ja  v a2s  . co m*/
    testCase.setDbPrimaryKey(dbPrimaryKey);
}

---

• «
• 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
• 34
• 35
• »