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

List of usage examples for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate queryForList

  1. HOME
  2. Java
  3. org.springframework
  4. org.springframework.jdbc.core.namedparam.*
  5. NamedParameterJdbcTemplate
  6. queryForList

Introduction

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

Prototype

@Override
    public List<Map<String, Object>> queryForList(String sql, Map<String, ?> paramMap) throws DataAccessException

Source Link

Usage

From source file:com.netsteadfast.greenstep.util.ExportData2CsvUtils.java

private static String processCsvText(ExportDataConfig config, NamedParameterJdbcTemplate jdbcTemplate,
        Map<String, Object> sqlParamMap) throws Exception {
    logger.info("export-Id: " + config.getId() + " name: " + config.getName());
    StringBuilder out = new StringBuilder();
    out.append(config.getTitle()).append("\r\n");
    List<Map<String, Object>> results = jdbcTemplate.queryForList(config.getSql(), sqlParamMap);
    for (int i = 0; results != null && i < results.size(); i++) {
        Map<String, Object> dataMap = results.get(i);
        for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
            if (entry.getValue() != null) {
                String str = "";
                if (entry.getValue() instanceof byte[]) { // blob text
                    str = new String((byte[]) entry.getValue(), Constants.BASE_ENCODING);
                } else {
                    str = String.valueOf(entry.getValue());
                }/*  w  w w  .  j  a va  2s  .com*/
                if (config.isEscapeCsv()) {
                    //str = StringEscapeUtils.escapeCsv(str);
                    str = SimpleUtils.escapeCsv(str);
                }
                if (StringUtils.isBlank(str)) {
                    str = " ";
                }
                out.append("\"").append(str).append("\"");
            } else {
                out.append(" ");
            }
            out.append(config.getSeparateSymbol());
        }
        out.append("\r\n");
    }
    return out.toString();
}

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  ww .  j  a v  a 2  s.c om
    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:com.yahoo.sql4d.sql4ddriver.sql.MysqlAccessor.java

public List<Map<String, Object>> query(Map<String, String> params, String query) {
    List<Map<String, Object>> result = null;
    Tuple2<DataSource, Connection> conn = null;
    try {//  w w w  . j a  v a2  s.  c  o m
        conn = getConnection();
        NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(conn._1());
        result = jdbcTemplate.queryForList(query, params);
    } catch (Exception ex) {
        Logger.getLogger(MysqlAccessor.class.getName()).log(Level.SEVERE, null, ex);
    } finally {
        returnConnection(conn);
    }
    return result;
}

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

/**
 * A sql statement(select) from which result set is expected.
 * @param params/*from  ww  w  . j  ava  2s .  co  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:org.smart.migrate.dao.impl.DefaultImportDao.java

@Override
public List<Map<String, Object>> findSourceByPrimaryKeys(TableSetting tableSetting, List<String> primaryKeys) {

    String sql = "SELECT " + SettingUtils.getSourceFields(tableSetting) + " FROM "
            + tableSetting.getSourceTable();
    sql += " WHERE " + tableSetting.getSourcePK() + " in (:ids) ";

    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(sourceJdbcTemplate);
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("ids", primaryKeys);
    return namedParameterJdbcTemplate.queryForList(sql, parameters);

}

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  w  w w  .  jav a 2 s .  c o  m
    }
    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:org.paxml.tag.sql.SqlTag.java

protected Object executeSql(String sql, Context context) {

    return executeSql(sql, new ISqlExecutor() {
        @Override/*from   w w w . ja v a2  s.  co m*/
        public Object update(final String sql) {
            if (param != null) {
                NamedParameterJdbcTemplate t = new NamedParameterJdbcTemplate(jdbcTemplate);
                return t.execute(sql, param, new PreparedStatementCallback<Void>() {

                    @Override
                    public Void doInPreparedStatement(PreparedStatement ps)
                            throws SQLException, DataAccessException {
                        ps.executeUpdate();
                        return null;
                    }

                });
            } else {
                jdbcTemplate.execute(sql);
            }
            return null;
        }

        @Override
        public Object query(String sql, boolean close) {
            if (param != null) {
                NamedParameterJdbcTemplate t = new NamedParameterJdbcTemplate(jdbcTemplate);
                return t.queryForList(sql, param);
            } else {
                return jdbcTemplate.queryForList(sql);
            }
        }

    });

}

From source file:org.paxml.tag.sql.SqlTag.java

private Object executeSql(String sql, ISqlExecutor exe) {

    Object result = null;/*from  ww w  . j a v  a  2  s .  com*/
    List<String> sqlList;
    if (singleStatement) {
        sqlList = Arrays.asList(sql);
    } else {
        sqlList = DBUtils.breakSql(sql);
    }

    final int maxIndex = sqlList.size() - 1;
    for (int i = 0; i <= maxIndex; i++) {

        if (isQuery(sql)) {
            if (list) {
                if (log.isDebugEnabled()) {
                    log.debug("Running sql: " + sql);
                }
                try {
                    if (param != null) {
                        NamedParameterJdbcTemplate t = new NamedParameterJdbcTemplate(jdbcTemplate);
                        result = t.queryForList(sql, param);
                    } else {
                        result = jdbcTemplate.queryForList(sql);
                    }
                } catch (RuntimeException e) {
                    throw new PaxmlRuntimeException("Cannot execute sql: " + sql, e);
                }
            } else {
                if (log.isDebugEnabled()) {
                    log.debug("Running sql: " + sqlList.get(i));
                }
                try {
                    result = exe.query(sqlList.get(i), true);
                } catch (RuntimeException e) {
                    throw new PaxmlRuntimeException("Cannot execute sql: " + sqlList.get(i), e);
                }
            }
        } else {
            if (log.isDebugEnabled()) {
                log.debug("Running sql: " + sqlList.get(i));
            }
            try {
                exe.update(sqlList.get(i));
            } catch (RuntimeException e) {
                throw new PaxmlRuntimeException("Cannot execute sql: " + sqlList.get(i), e);
            }
        }

    }
    return result;

}