Example usage for org.springframework.jdbc.core JdbcTemplate queryForList

List of usage examples for org.springframework.jdbc.core JdbcTemplate queryForList

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

Introduction

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

Prototype

@Override
    public List<Map<String, Object>> queryForList(String sql, @Nullable Object... args) throws DataAccessException

Source Link

Usage

From source file:com.github.springtestdbunit.multiconnection.MultiConnectionTest.java

private void assertSecondDataSourceValues(String... expected) {
    JdbcTemplate jdbc = new JdbcTemplate(this.dataSource);
    List<String> actual = jdbc.queryForList("select value from second", String.class);
    assertEquals(new HashSet<String>(Arrays.asList(expected)), new HashSet<String>(actual));
}

From source file:org.sipfoundry.sipxconfig.homer.HomerDbManager.java

/**
 * Ensure each proxy is listed in homer's hosts table. 
 * NOTE: Records in node table are not removed, it's unclear if an admin would want that --Douglas 
 */// www. ja  va2  s. c o  m
public void syncNodes() {
    if (!m_featureManager.isFeatureEnabled(MySql.FEATURE)) {
        return;
    }
    // sync node info
    String sipxSql = "select fqdn || '@' || ip_address from location l inner join feature_local f on l.location_id = f.location_id and f.feature_id = ?";
    List<String> sipxHosts = m_configJdbcTemplate.queryForList(sipxSql, String.class,
            ProxyManager.FEATURE.getId());

    String homerSql = "select concat(name, '@', host) from homer_hosts";
    JdbcTemplate homerDb = (JdbcTemplate) m_beanFactory.getBean("homerDb");
    List<String> homerHosts = homerDb.queryForList(homerSql, String.class);

    sipxHosts.removeAll(homerHosts);
    if (!sipxHosts.isEmpty()) {
        List<String> add = new ArrayList<String>();
        for (String missingNode : sipxHosts) {
            String[] decode = StringUtils.split(missingNode, '@');
            String name = decode[0];
            String host = decode[1];
            String checkHostSql = "select count(0) from homer_hosts where host = ?";
            boolean hostExists = homerDb.queryForInt(checkHostSql, host) > 0;
            String sql;
            if (hostExists) {
                sql = format("update homer_hosts set name='%s' where host='%s'", name, host);
            } else {
                sql = format("insert into homer_hosts (name, host, status) values ('%s', '%s', 1)", name, host);
            }
            add.add(sql);
        }
        homerDb.batchUpdate(add.toArray(new String[0]));
    }

    // sync db info
    String dbhost = "127.0.0.1";
    int dbport = 3306;
    String dbname = "homer_db";
    String dbuser = "root";
    String dbpass = "";
    String dbtables = "sip_capture";
    String name = "local";
    int status = 1;
    // must have lowest ID to ensure it's the default node for statistics
    String nodeSql = "select 1 from homer_nodes where host = ? and dbport = ? and dbname = ? and dbpassword = ? "
            + "and dbusername = ? and dbtables = ? and name = ? and status = ? and id = 1";
    List<Integer> found = homerDb.queryForList(nodeSql, Integer.class, dbhost, dbport, dbname, dbpass, dbuser,
            dbtables, name, status);
    if (found.size() == 0) {
        String[] hosts = new String[2];
        hosts[0] = "delete from homer_nodes";
        String addNode = "insert into homer_nodes (id, host, dbport, dbname, dbpassword, dbusername, dbtables, name, status) "
                + "values (1, '%s',%d,'%s','%s','%s','%s','%s','%d')";
        hosts[1] = format(addNode, dbhost, dbport, dbname, dbpass, dbuser, dbtables, name, status);
        homerDb.batchUpdate(hosts);
    }
}

From source file:eionet.transfer.dao.MetadataServiceJdbc.java

@Override
public List<String> getExpired(Date expireDate) {
    String query = "SELECT id FROM uploads WHERE expires < ?";
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    List<String> uploadList = new ArrayList<String>();

    Object[] parameters = new Object[] { expireDate };
    List<Map<String, Object>> rows = jdbcTemplate.queryForList(query, parameters);
    for (Map<String, Object> row : rows) {
        String uuidName = new String((String) row.get("id"));
        uploadList.add(uuidName);/* w w  w.ja  v a  2  s .com*/
    }
    return uploadList;
}

From source file:org.opendatakit.persistence.engine.pgres.DatastoreImpl.java

@Override
public void afterPropertiesSet() throws Exception {
    if (dataSource == null) {
        throw new IllegalStateException("dataSource property must be set!");
    }/*from www .  j  a v a2s  . c  o  m*/
    if (schemaName == null) {
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        List<?> databaseNames = jdbcTemplate.queryForList("SELECT current_database()", String.class);
        schemaName = (String) databaseNames.get(0);
    }
}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the ids by open access and lectureseries id.
 *
 * @param lectureseriesId the lectureseries id
 * @param order the order/*from ww w.  j  av a  2s. c om*/
 * @return the ids by open access and lectureseries id
 */
public List<?> getIdsByOpenAccessAndLectureseriesId(Integer lectureseriesId, String order) {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    return select.queryForList(
            "SELECT id FROM video WHERE (openAccess=1 AND lectureseriesId=?) ORDER BY generationDate " + order,
            new Object[] { lectureseriesId });
}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the number videos by lectureseries id and producer.
 *
 * @param lectureseriesId the lectureseries id
 * @param producerId the producer id//from   w  ww .  j a  va  2s . co  m
 * @return the number videos by lectureseries id and producer
 */
public int getNumberVideosByLectureseriesIdAndProducer(int lectureseriesId, int producerId) {
    JdbcTemplate jdbst = new JdbcTemplate(this.getDataSource());
    List<?> retNum = jdbst.queryForList("SELECT id FROM video WHERE ( lectureseriesId = ? AND producerId =?)",
            new Object[] { lectureseriesId, producerId });
    return retNum.size();
}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the first lacture fromlectureseries id.
 *
 * @param lectureseriesId the lectureseries id
 * @return the first lacture fromlectureseries id
 *///from   w  w w .  ja va2  s.c  o  m
public Video getFirstVideoFromlectureseriesId(Integer lectureseriesId) throws NoSuchElementException {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    @SuppressWarnings("rawtypes")
    List vidL = select.queryForList(
            "SELECT id FROM video WHERE (generationDate=(SELECT MIN(generationDate) FROM video WHERE lectureseriesId=? AND openAccess=1) AND lectureseriesId=? AND openAccess=1) ORDER BY id ASC LIMIT 1",
            new Object[] { lectureseriesId, lectureseriesId });
    ListOrderedMap lid = (ListOrderedMap) vidL.listIterator().next();
    Integer id = (Integer) lid.get("id");
    Video vid = this.getByIdNP(id).iterator().next();
    return vid;
}

From source file:de.uhh.l2g.dao.VideoDao.java

/**
 * Gets the latest lacture fromlectureseries id.
 *
 * @param lectureseriesId the lectureseries id
 * @return the latest lacture fromlectureseries id
 *//*from  w w w  .  ja v  a2s.co  m*/
public Video getLatestVideoFromlectureseriesId(Integer lectureseriesId) throws NoSuchElementException {
    JdbcTemplate select = new JdbcTemplate(this.getDataSource());
    @SuppressWarnings("rawtypes")
    List vidL = select.queryForList(
            "SELECT id FROM video WHERE (generationDate=(SELECT MAX(generationDate) FROM video WHERE lectureseriesId=? AND openAccess=1) AND lectureseriesId=? AND openAccess=1) ORDER BY id ASC LIMIT 1",
            new Object[] { lectureseriesId, lectureseriesId });
    ListOrderedMap lid = (ListOrderedMap) vidL.listIterator().next();
    Integer id = (Integer) lid.get("id");
    Video vid = this.getByIdNP(id).iterator().next();
    return vid;
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

@Override
public HashMap<ProfileRecipientFields, ValidatorResults> getDuplicateRecipientsFromNewDataOnly(
        Map<ProfileRecipientFields, ValidatorResults> listOfValidBeans, ImportProfile profile,
        CSVColumnState[] columns, Integer adminID, int datasource_id) {
    final HashMap<ProfileRecipientFields, ValidatorResults> result = new HashMap<ProfileRecipientFields, ValidatorResults>();
    if (listOfValidBeans.isEmpty()) {
        return result;
    }/*from   w  w  w  .  j  a va  2 s .c o  m*/

    final String prefix = "cust_" + adminID + "_tmp_";
    final String tableName = prefix + datasource_id + "_tbl";
    final HashMap<ImportKeyColumnsKey, ProfileRecipientFields> columnKeyValueToTemporaryIdMap = new HashMap<ImportKeyColumnsKey, ProfileRecipientFields>();
    final JdbcTemplate template = getJdbcTemplateForTemporaryTable();
    List parameters = new ArrayList();
    Map<String, List<Object>> parametersMap = new HashMap<String, List<Object>>();
    String columnKeyBuffer = "(";
    for (ProfileRecipientFields profileRecipientFields : listOfValidBeans.keySet()) {
        ImportKeyColumnsKey keyValue = ImportKeyColumnsKey.createInstance(profile, profileRecipientFields,
                columns);
        if (columnKeyValueToTemporaryIdMap.containsKey(keyValue)) {
            result.put(profileRecipientFields, null);
            continue;
        }

        columnKeyBuffer += keyValue.getParametersString();
        keyValue.addParameters(parametersMap);

        columnKeyValueToTemporaryIdMap.put(keyValue, profileRecipientFields);
    }
    columnKeyBuffer = columnKeyBuffer.substring(0, columnKeyBuffer.length() - 1);
    columnKeyBuffer = columnKeyBuffer + ")";
    ImportKeyColumnsKey keyColumnsKey = columnKeyValueToTemporaryIdMap.keySet().iterator().next();
    Iterator<String> keyColumnIterator = keyColumnsKey.getKeyColumnsMap().keySet().iterator();
    StringBuffer sqlQuery = new StringBuffer("SELECT ");
    StringBuffer wherePart = new StringBuffer("");
    int index = 0;
    while (keyColumnIterator.hasNext()) {
        String keyColumnName = keyColumnIterator.next();
        CSVColumnState columnState = keyColumnsKey.getKeyColumnsMap().get(keyColumnName);
        String column = "i.column_duplicate_check_" + index;
        String columnAlias = ImportKeyColumnsKey.KEY_COLUMN_PREFIX + keyColumnName;
        sqlQuery.append(column + " AS " + columnAlias + ",");
        int type = columnState.getType();
        if (AgnUtils.isOracleDB() && (keyColumnName.equals("email") || type == CSVColumnState.TYPE_NUMERIC
                || type == CSVColumnState.TYPE_DATE)) {
            wherePart.append(column);
        } else {
            wherePart.append("LOWER(" + column + ")");
        }
        wherePart.append(" IN " + columnKeyBuffer + " AND ");
        // gather parameters
        List<Object> objectList = parametersMap.get(keyColumnName);
        if (objectList != null) {
            parameters.addAll(objectList);
        }
        index++;
    }

    sqlQuery.delete(sqlQuery.length() - 1, sqlQuery.length());
    sqlQuery.append(" FROM " + tableName + " i  WHERE (");
    sqlQuery.append(wherePart);
    sqlQuery.append("(i.status_type=" + NewImportWizardService.RECIPIENT_TYPE_VALID + " OR i.status_type="
            + NewImportWizardService.RECIPIENT_TYPE_FIXED_BY_HAND + " OR i.status_type="
            + NewImportWizardService.RECIPIENT_TYPE_DUPLICATE_RECIPIENT + "))");

    final List<Map> resultList = template.queryForList(sqlQuery.toString(), parameters.toArray());
    for (Map row : resultList) {
        ImportKeyColumnsKey columnsKey = ImportKeyColumnsKey.createInstance(row);
        ProfileRecipientFields recipientFields = columnKeyValueToTemporaryIdMap.get(columnsKey);
        if (recipientFields != null) {
            result.put(recipientFields, null);
        }
    }
    return result;
}

From source file:org.agnitas.dao.impl.ImportRecipientsDaoImpl.java

@Override
public HashMap<ProfileRecipientFields, ValidatorResults> getDuplicateRecipientsFromExistData(
        Map<ProfileRecipientFields, ValidatorResults> listOfValidBeans, ImportProfile profile,
        CSVColumnState[] columns) {/*from   w  w w  .  j  av  a  2s .c  o m*/
    final HashMap<ProfileRecipientFields, ValidatorResults> result = new HashMap<ProfileRecipientFields, ValidatorResults>();
    if (listOfValidBeans.isEmpty()) {
        return result;
    }
    final HashMap<ImportKeyColumnsKey, ProfileRecipientFields> columnKeyValueToTemporaryIdMap = new HashMap<ImportKeyColumnsKey, ProfileRecipientFields>();
    final JdbcTemplate template = createJdbcTemplate();
    List parameters = new ArrayList();
    Map<String, List<Object>> parametersMap = new HashMap<String, List<Object>>();
    String columnKeyBuffer = "(";
    for (ProfileRecipientFields profileRecipientFields : listOfValidBeans.keySet()) {
        ImportKeyColumnsKey keyValue = ImportKeyColumnsKey.createInstance(profile, profileRecipientFields,
                columns);
        if (columnKeyValueToTemporaryIdMap.containsKey(keyValue)) {
            result.put(profileRecipientFields, null);
            continue;
        }

        columnKeyBuffer += keyValue.getParametersString();
        keyValue.addParameters(parametersMap);

        columnKeyValueToTemporaryIdMap.put(keyValue, profileRecipientFields);
    }
    columnKeyBuffer = columnKeyBuffer.substring(0, columnKeyBuffer.length() - 1);
    columnKeyBuffer = columnKeyBuffer + ")";

    ImportKeyColumnsKey keyColumnsKey = columnKeyValueToTemporaryIdMap.keySet().iterator().next();
    Iterator<String> keyColumnIterator = keyColumnsKey.getKeyColumnsMap().keySet().iterator();
    StringBuffer sqlQuery = new StringBuffer("SELECT customer_id, ");
    StringBuffer wherePart = new StringBuffer("");
    Map<String, Integer> columnTypes = new HashMap<String, Integer>();
    while (keyColumnIterator.hasNext()) {
        String keyColumnName = keyColumnIterator.next();
        CSVColumnState columnState = keyColumnsKey.getKeyColumnsMap().get(keyColumnName);
        String column = keyColumnName;
        String columnAlias = ImportKeyColumnsKey.KEY_COLUMN_PREFIX + keyColumnName;
        sqlQuery.append(column + " AS " + columnAlias + ",");
        int type = columnState.getType();
        if (AgnUtils.isOracleDB() && (keyColumnName.equals("email") || type == CSVColumnState.TYPE_NUMERIC
                || type == CSVColumnState.TYPE_DATE)) {
            wherePart.append(column);
        } else {
            wherePart.append("LOWER(" + column + ")");

        }
        wherePart.append(" IN " + columnKeyBuffer + " AND ");
        // gather parameters
        List<Object> objectList = parametersMap.get(keyColumnName);
        if (objectList != null) {
            parameters.addAll(objectList);
        }
        columnTypes.put(columnAlias, type);
    }

    sqlQuery.delete(sqlQuery.length() - 1, sqlQuery.length());
    wherePart.delete(wherePart.length() - 4, wherePart.length());
    sqlQuery.append(" FROM customer_" + profile.getCompanyId() + "_tbl c WHERE (");
    sqlQuery.append(wherePart);
    sqlQuery.append(")");

    final List<Map> resultList = template.queryForList(sqlQuery.toString(), parameters.toArray());
    for (Map row : resultList) {
        ImportKeyColumnsKey columnsKey = ImportKeyColumnsKey.createInstance(row);
        ProfileRecipientFields recipientFields = columnKeyValueToTemporaryIdMap.get(columnsKey);
        if (recipientFields != null) {
            result.put(recipientFields, null);
            if (profile.getUpdateAllDuplicates() || (recipientFields.getUpdatedIds() == null
                    || recipientFields.getUpdatedIds().size() == 0)) {
                recipientFields.addUpdatedIds(((Number) row.get("customer_id")).intValue());
            }
        }
    }
    return result;
}