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(String sql, RowMapper<T> rowMapper, @Nullable Object... args)
            throws DataAccessException

Source Link

Usage

From source file:com.pivotal.gfxd.demo.services.PredictionService.java

private float getAverageLoad(String sql, int weekDay, int intervalStart, int intervalEnd, int rowWithCount)
        throws SQLException {

    JdbcTemplate template = getJdbcTemplate();
    Object[] params = { weekDay, intervalStart, intervalEnd };

    MyRCH rch = new MyRCH(rowWithCount);
    template.query(sql, params, rch);

    LOG.debug("--->>> weekday=" + weekDay + " start=" + intervalStart + " end=" + intervalEnd + " load="
            + rch.getLoad() + " count=" + rch.getCount());

    return rch.getLoad() / rch.getCount();
}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.BulkLoaderUnclassifier.java

private boolean checkCSCSI() {

    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    Boolean checkCsCsi = (Boolean) jdbcTemplate.query(checkCsCsiQry,
            new Object[] { csName, csVersion, csiName, csiVersion }, new ResultSetExtractor() {

                @Override/*from   www .j  av  a 2s .c  o m*/
                public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                    if (rs.next()) {
                        return new Boolean(true);
                    }
                    return new Boolean(false);
                }
            });
    return checkCsCsi;
}

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

private boolean recordExists(String hashKey) {
    RowMapper rowMapper = new SingleColumnRowMapper(String.class);
    String query = "select canon_dn from " + config.getTable() + " where hash_dn = ? ";
    JdbcTemplate jdbc = new JdbcTemplate(config.getDataSource());
    List<String> dnList = jdbc.query(query, new String[] { hashKey }, rowMapper);
    return (dnList != null && dnList.size() == 1);
}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.BulkLoaderUnclassifier.java

private Double getLatestCSVersion(UnloadProperties unloadProperties) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String csName = unloadProperties.getClassificationSchemeName();

    Double latestVersion = (Double) jdbcTemplate.query(csLatestVersionQry, new Object[] { csName },
            new ResultSetExtractor() {

                @Override//from w  ww.j a  va 2  s.c o  m
                public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                    if (rs.next()) {
                        return new Double(rs.getDouble(1));
                    }
                    return new Double(0.0);
                }
            });

    return latestVersion;
}

From source file:gov.nih.nci.ncicb.cadsr.bulkloader.util.BulkLoaderUnclassifier.java

private Double getLatestCSIVersion(UnloadProperties unloadProperties) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String csName = unloadProperties.getClassificationSchemeName();
    String csiName = unloadProperties.getClassificationSchemeItemName();

    Double latestVersion = (Double) jdbcTemplate.query(csiLatestVersionQry, new Object[] { csiName, csName },
            new ResultSetExtractor() {

                @Override//from   w w  w .j a  va2  s . c  om
                public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                    if (rs.next()) {
                        return new Double(rs.getDouble(1));
                    }
                    return new Double(0.0);
                }
            });

    return latestVersion;
}

From source file:io.cloudslang.engine.queue.repositories.ExecutionQueueRepositoryImpl.java

private <T> List<T> doSelectWithTemplate(JdbcTemplate jdbcTemplate, String sql, RowMapper<T> rowMapper,
        Object... params) {/*from  w  w  w .j av  a  2s.  c o m*/
    logSQL(sql, params);
    try {
        long t = System.currentTimeMillis();
        List<T> result = jdbcTemplate.query(sql, params, rowMapper);
        if (logger.isDebugEnabled())
            logger.debug(
                    "Fetched result: " + result.size() + '/' + (System.currentTimeMillis() - t) + " rows/ms");
        return result;
    } catch (RuntimeException ex) {
        logger.error("Failed to execute query: " + sql, ex);
        throw ex;
    }
}

From source file:com.hexin.core.dao.BaseDaoSupport.java

@Override
public <T> List<T> findList(JdbcTemplate jdbcTemplate, String sql, Class<T> dtoClass, Object... args) {

    long startTime = System.currentTimeMillis();
    long endTime;
    long durTime;

    debugSql(sql, args);/*  w w  w .  j a  v a 2 s.c o m*/

    RowMapper<T> rowMapper = new BeanPropertyRowMapper<T>(dtoClass);

    List<T> result = jdbcTemplate.query(sql, rowMapper, args);

    endTime = System.currentTimeMillis();
    durTime = endTime - startTime;
    logger.debug("This jdbc operation costs time: " + durTime);

    return result;
}

From source file:com.taobao.diamond.server.utils.PaginationHelper.java

/**
 * /*from  w  ww.ja v  a  2 s . c o  m*/
 * 
 * @param jt
 *            jdbcTemplate
 * @param sqlCountRows
 *            SQL
 * @param sqlFetchRows
 *            sql
 * @param args
 *            
 * @param pageNo
 *            
 * @param pageSize
 *            
 * @param rowMapper
 * @return
 */
public Page<E> fetchPage(final JdbcTemplate jt, final String sqlCountRows, final String sqlFetchRows,
        final Object args[], final int pageNo, final int pageSize, final ParameterizedRowMapper<E> rowMapper) {
    if (pageSize == 0) {
        return null;
    }

    // 
    final int rowCount = jt.queryForInt(sqlCountRows, args);
    // 
    int pageCount = rowCount / pageSize;
    if (rowCount > pageSize * pageCount) {
        pageCount++;
    }

    // Page
    final Page<E> page = new Page<E>();
    page.setPageNumber(pageNo);
    page.setPagesAvailable(pageCount);
    page.setTotalCount(rowCount);

    if (pageNo > pageCount)
        return null;
    // 
    final int startRow = (pageNo - 1) * pageSize;
    // TODO  limit
    final String selectSQL = sqlFetchRows + " limit " + startRow + "," + pageSize;
    jt.query(selectSQL, args, new ResultSetExtractor() {
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            final List<E> pageItems = page.getPageItems();
            int currentRow = 0;
            while (rs.next()) {
                pageItems.add(rowMapper.mapRow(rs, currentRow++));
            }
            return page;
        }
    });
    return page;
}

From source file:cn.leancloud.diamond.server.utils.PaginationHelper.java

/**
 * ?/*from   ww  w.ja  v  a  2 s. c o m*/
 * 
 * @param jt
 *            jdbcTemplate
 * @param sqlCountRows
 *            SQL
 * @param sqlFetchRows
 *            ?sql
 * @param args
 *            ?
 * @param pageNo
 *            
 * @param pageSize
 *            ??
 * @param rowMapper
 * @return
 */
public Page<E> fetchPage(final JdbcTemplate jt, final String sqlCountRows, final String sqlFetchRows,
        final Object args[], final int pageNo, final int pageSize, final ParameterizedRowMapper<E> rowMapper) {
    if (pageSize == 0) {
        return null;
    }

    // ?
    final int rowCount = jt.queryForInt(sqlCountRows, args);

    // 
    int pageCount = rowCount / pageSize;
    if (rowCount > pageSize * pageCount) {
        pageCount++;
    }

    // Page
    final Page<E> page = new Page<E>();
    page.setPageNumber(pageNo);
    page.setPagesAvailable(pageCount);
    page.setTotalCount(rowCount);

    if (pageNo > pageCount)
        return null;
    // ????
    final int startRow = (pageNo - 1) * pageSize;
    // TODO ?? limit
    final String selectSQL = sqlFetchRows + " limit " + startRow + "," + pageSize;
    jt.query(selectSQL, args, new ResultSetExtractor() {
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            final List<E> pageItems = page.getPageItems();
            int currentRow = 0;
            while (rs.next()) {
                pageItems.add(rowMapper.mapRow(rs, currentRow++));
            }
            return page;
        }
    });
    return page;
}

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

private final boolean updateRelation(JdbcTemplate jc, CommonFieldsBase relation, String originalStatement) {

    String qs = TableDefinition.TABLE_DEF_QUERY;
    List<?> columns;/* www.  ja v a2 s  .  c  o m*/
    columns = jc.query(qs, new Object[] { relation.getSchemaName(), relation.getTableName() }, tableDef);
    dam.recordQueryUsage(TableDefinition.INFORMATION_SCHEMA_COLUMNS, columns.size());

    if (columns.size() > 0) {
        Map<String, TableDefinition> map = new HashMap<String, TableDefinition>();
        for (Object o : columns) {
            TableDefinition t = (TableDefinition) o;
            map.put(t.getColumnName(), t);
        }

        // we may have gotten some results into columns -- go through the fields
        // and
        // assemble the results... we don't care about additional columns in the
        // map...
        for (DataField f : relation.getFieldList()) {
            TableDefinition d = map.get(f.getName());
            if (d == null) {
                StringBuilder b = new StringBuilder();
                if (originalStatement == null) {
                    b.append(" Retrieving expected definition (");
                    boolean first = true;
                    for (DataField field : relation.getFieldList()) {
                        if (!first) {
                            b.append(K_CS);
                        }
                        first = false;
                        b.append(field.getName());
                    }
                    b.append(")");
                } else {
                    b.append(" Created with: ");
                    b.append(originalStatement);
                }
                throw new IllegalStateException("did not find expected column " + f.getName() + " in table "
                        + relation.getSchemaName() + "." + relation.getTableName() + b.toString());
            }
            if (f.getDataType() == DataField.DataType.BOOLEAN && d.getDataType() == DataField.DataType.STRING) {
                d.setDataType(DataField.DataType.BOOLEAN);
                // don't care about size...
            }

            if (d.getDataType() == DataField.DataType.STRING && f.getMaxCharLen() != null
                    && f.getMaxCharLen().compareTo(d.getMaxCharLen()) > 0) {
                throw new IllegalStateException("column " + f.getName() + " in table "
                        + relation.getSchemaName() + "." + relation.getTableName()
                        + " stores string-valued keys but is shorter than required by Aggregate "
                        + d.getMaxCharLen().toString() + " < " + f.getMaxCharLen().toString());
            }

            if (f.getDataType() == DataField.DataType.URI) {
                if (d.getDataType() != DataField.DataType.STRING) {
                    throw new IllegalStateException(
                            "column " + f.getName() + " in table " + relation.getSchemaName() + "."
                                    + relation.getTableName() + " stores URIs but is not a string field");
                }
                d.setDataType(DataField.DataType.URI);
            }

            if (d.getDataType() != f.getDataType()) {
                throw new IllegalStateException("column " + f.getName() + " in table "
                        + relation.getSchemaName() + "." + relation.getTableName()
                        + " is not of the expected type " + f.getDataType().toString());
            }

            // it is OK for the data model to be more strict than the data store.
            if (!d.isNullable() && f.getNullable()) {
                throw new IllegalStateException("column " + f.getName() + " in table "
                        + relation.getSchemaName() + "." + relation.getTableName()
                        + " is defined as NOT NULL but the data model requires NULL");
            }
            f.setMaxCharLen(d.getMaxCharLen());
            f.setNumericPrecision(d.getNumericPrecision());
            f.setNumericScale(d.getNumericScale());
            f.asDoublePrecision(d.isDoublePrecision());
        }
        return true;
    } else {
        return false;
    }
}