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

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

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

Introduction

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

Prototype

@Override
    public <T> List<T> query(PreparedStatementCreator psc, RowMapper<T> rowMapper) throws DataAccessException

Source Link

Usage

From source file:gov.nih.nci.ncicb.cadsr.common.persistence.dao.jdbc.JDBCDataElementDAO.java

public Map<String, ValueMeaning> getValueMeanings(Collection<String> vmIds) {
    if (vmIds == null || vmIds.size() < 1) {
        return new HashMap<String, ValueMeaning>();
    }/*from w  w  w.j a v a2  s .c o  m*/
    String qry = "select * from VALUE_MEANINGS_VIEW where VM_IDSEQ in ( ";
    for (String vmId : vmIds) {
        qry += "'" + vmId + "',";
    }
    qry = qry.substring(0, qry.length() - 1) + ")";

    JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
    Map<String, ValueMeaning> vmMap = (Map<String, ValueMeaning>) jdbcTemplate.query(qry,
            new ResultSetExtractor() {
                public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                    Map<String, ValueMeaning> vmMap = new HashMap<String, ValueMeaning>();
                    while (rs.next()) {
                        ValueMeaning vm = new ValueMeaningTransferObject();
                        vm.setPreferredName(rs.getString(1));
                        vm.setPreferredDefinition(rs.getString(2));
                        String vmIdSeq = rs.getString(11);
                        vm.setLongName(rs.getString(14));
                        vm.setDefinitions(getDefinitions(vmIdSeq));
                        vm.setDesignations(getDesignations(vmIdSeq, null));

                        vmMap.put(vmIdSeq, vm);
                    }
                    return vmMap;
                }
            });

    return vmMap;
}

From source file:db.migration.V055__UpdateECTS.java

private int getNextHibernateSequence(JdbcTemplate jdbcTemplate) {
    // Returns next global id
    List<Map> resultSet = jdbcTemplate.query("SELECT nextval('public.hibernate_sequence')",
            new RowMapper<Map>() {
                @Override//ww  w  .  j ava2s.c om
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map r = new HashMap<String, Object>();

                    ResultSetMetaData metadata = rs.getMetaData();
                    for (int i = 1; i <= metadata.getColumnCount(); i++) {
                        String cname = metadata.getColumnName(i);
                        int ctype = metadata.getColumnType(i);

                        switch (ctype) {
                        case Types.BIGINT: // id
                            r.put(cname, rs.getInt(cname));
                            break;

                        default:
                            break;
                        }
                    }

                    return r;
                }
            });

    for (Map m : resultSet) {
        return (int) m.get("nextval");
    }
    return 0;
}

From source file:com.oracle2hsqldb.dialect.Oracle9Dialect.java

/**
 * performance improvement over GenericDialect's getTables()
 *//*w ww.  ja v  a2s  . co  m*/
public Iterator getTables(DataSource dataSource, String schemaName) throws SQLException {
    final List specs = new LinkedList();
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    jdbcTemplate.query("SELECT table_name FROM user_tables ", new RowCallbackHandler() {
        public void processRow(ResultSet result) throws SQLException {
            if (!"TOAD_PLAN_TABLE".equals(result.getString("TABLE_NAME"))) {
                specs.add(new Table.Spec(result.getString("TABLE_NAME"), Table.Type.TABLE.getJdbcName()));
            }
        }
    });
    jdbcTemplate.query("SELECT view_name, text FROM user_views", new RowCallbackHandler() {
        public void processRow(ResultSet result) throws SQLException {
            specs.add(new View.Spec(result.getString("VIEW_NAME"), Table.Type.VIEW.getJdbcName(),
                    result.getString("TEXT")));
        }
    });
    return specs.iterator();
}

From source file:db.migration.V023__UpdateOrganisationToimipisteKoodi.java

public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    LOG.info("migrate()...");

    // Get all organisations
    List<Map> resultSet = jdbcTemplate.query("SELECT * FROM organisaatio o", new RowMapper<Map>() {
        @Override/*from  w  w  w.  jav  a2 s  .  c  om*/
        public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
            Map r = new HashMap<String, Object>();

            ResultSetMetaData metadata = rs.getMetaData();
            for (int i = 1; i <= metadata.getColumnCount(); i++) {
                String cname = metadata.getColumnName(i);
                int ctype = metadata.getColumnType(i);

                switch (ctype) {
                case Types.VARCHAR:
                    r.put(cname, rs.getString(cname));
                    break;

                default:
                    break;
                }
            }

            LOG.debug("  read from db : org = {}", r);

            _organisations.put((String) r.get("oid"), r);
            return r;
        }
    });

    // Generate and update initial values for toimipistekoodis
    for (Map org : resultSet) {
        if (isToimipiste(org, jdbcTemplate)) {
            String tpKoodi = calculateToimipisteKoodi(org, jdbcTemplate);
            updateToimipisteKoodi(org, tpKoodi, jdbcTemplate);
        }
    }

    LOG.info("  Processed {} organisations, updated {} Opetuspistes", _organisations.size(), _numUpdated);

    LOG.info("migrate()... done.");
}

From source file:ca.nrc.cadc.cred.server.CertificateDAO.java

public List<String> getAllHashKeys() {
    Profiler profiler = new Profiler(this.getClass());
    String query = "select hash_dn from " + config.getTable();
    RowMapper rowMapper = new SingleColumnRowMapper(String.class);
    JdbcTemplate jdbc = new JdbcTemplate(config.getDataSource());
    List<String> hashKeyList = jdbc.query(query, rowMapper);
    profiler.checkpoint("getAllHashKeys");
    return hashKeyList;
}

From source file:edu.wisc.my.stats.query.support.JdbcQueryRunner.java

/**
 * @see edu.wisc.my.stats.query.QueryRunner#runQuery(edu.wisc.my.stats.web.command.QueryParameters, edu.wisc.my.stats.domain.QueryInformation)
 *///  www . j  a v a 2 s  .  c o  m
@SuppressWarnings("unchecked")
public Table runQuery(QueryCommand queryCommand, QueryInformation queryInformation) {
    final String sql = this.queryCompiler.compileQuery(queryCommand, queryInformation);

    final JdbcTemplate jdbcTemplate = this.getJdbcTemplate();
    final DataPointRowMapper dataPointRowMapper = new DataPointRowMapper(queryCommand, queryInformation);
    final Table<Long, Fact, Double> results = (Table<Long, Fact, Double>) jdbcTemplate.query(sql,
            dataPointRowMapper);

    return results;
}

From source file:org.jtalks.common.migrations.V6__Update_user_passwords.java

/**
 * {@inheritDoc}//from w  w  w  .  j a v a2 s .c om
 */
@Override
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    MessageDigestPasswordEncoder passwordEncoder = getContext().getBean(MessageDigestPasswordEncoder.class);
    SaltGenerator saltGenerator = getContext().getBean(SaltGenerator.class);

    List<HashMap<String, String>> users = jdbcTemplate
            .query("SELECT `ID`, `PASSWORD` FROM `USERS` WHERE `SALT` = ''", getRowMapper());

    for (HashMap<String, String> user : users) {

        String salt = saltGenerator.generate();
        String encodedPassword = passwordEncoder.encodePassword(user.get("password"), salt);

        jdbcTemplate.update("UPDATE `USERS` SET `PASSWORD` = ?, `SALT` = ? WHERE `ID` = ?", encodedPassword,
                salt, Integer.parseInt(user.get("id")));
    }
}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.dao.read.BulkLoaderReadDAOImpl.java

public boolean sourceExists(String sourceName) {
    String sql = "select * from SOURCES_EXT where SRC_NAME='" + sourceName + "'";
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    Boolean exists = (Boolean) jdbcTemplate.query(sql, new ResultSetExtractor() {
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            if (rs.next()) {
                return new Boolean(true);
            }//  ww w  .ja va  2 s.  c  o m
            return new Boolean(false);
        }
    });

    return exists;
}

From source file:db.migration.V055__UpdateECTS.java

public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    LOG.info("migrate()...");

    // Get all organisaatiometadatas where there are strings to process
    List<Map> resultSet = jdbcTemplate.query(
            "SELECT id,hakutoimistoectsemail,hakutoimistoectsnimi,hakutoimistoectspuhelin,hakutoimistoectstehtavanimike FROM organisaatiometadata WHERE hakutoimistoectsemail<>'' OR hakutoimistoectsnimi<>'' OR hakutoimistoectspuhelin<>'' OR hakutoimistoectstehtavanimike<>''",
            new RowMapper<Map>() {
                @Override//from w  w w  . j  a  va2s .c om
                public Map mapRow(ResultSet rs, int rowNum) throws SQLException {
                    Map r = new HashMap<String, Object>();

                    ResultSetMetaData metadata = rs.getMetaData();
                    for (int i = 1; i <= metadata.getColumnCount(); i++) {
                        String cname = metadata.getColumnName(i);
                        int ctype = metadata.getColumnType(i);

                        switch (ctype) {
                        case Types.VARCHAR: // hakutoimistoectsemail,hakutoimistoectsnimi,hakutoimistoectspuhelin,hakutoimistoectstehtavanimike
                            r.put(cname, rs.getString(cname));
                            break;

                        case Types.BIGINT: // id
                            r.put(cname, rs.getInt(cname));
                            break;

                        default:
                            break;
                        }
                    }

                    LOG.debug("  read from db : organisaatiometadata = {}", r);

                    return r;
                }
            });

    // Move strings to monikielinenteksti_values
    for (Map orgmd : resultSet) {

        handleOrganisaatiometadata(orgmd, jdbcTemplate);

    }

    LOG.info("migrate()... done.");
}

From source file:org.ohmage.cache.KeyValueCache.java

/**
 * Reads the database for the information in the lookup table and populates
 * its map with the gathered information. If there is an issue reading the
 * database, it will just remain with the current lookup table it has.
 * //from   w  ww.j  ava 2 s  . c o  m
 * @complexity O(n) where n is the number of keys in the database.
 */
protected synchronized void refreshMap() {
    // Only one thread should be updating this information at a time. Once
    // other threads enter, they should check to see if an update was just
    // done and, if so, should abort a second update.
    if ((getLastUpdateTimestamp() + getUpdateFrequency()) > System.currentTimeMillis()) {
        return;
    }

    // This is the JdbcTemplate we will use for our query. If there is an
    // issue report it and abort the update.
    JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());

    // Get all the keys and their corresponding values.
    List<KeyAndValue> keyAndValue;
    try {
        keyAndValue = jdbcTemplate.query(sqlForRetrievingValues, new RowMapper<KeyAndValue>() {
            @Override
            public KeyAndValue mapRow(ResultSet rs, int row) throws SQLException {
                return new KeyAndValue(rs.getString(keyColumn), rs.getString(valueColumn));
            }
        });
    } catch (org.springframework.dao.DataAccessException e) {
        LOGGER.error("Error executing SQL '" + sqlForRetrievingValues + "'. Aborting cache refresh.", e);
        return;
    }

    // Create a new Map, populate it, and replace the old one. This allows
    // for concurrent readying while the new Map is being created.
    Map<String, String> keyValueMap = new HashMap<String, String>();
    for (KeyAndValue currStateAndId : keyAndValue) {
        keyValueMap.put(currStateAndId.key, currStateAndId.value);
    }
    this.keyValueMap = keyValueMap;

    setLastUpdateTimestamp(System.currentTimeMillis());
}