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:com.arcane.dao.Impl.PatternDaoImpl.java

@Override
public TrippleBottom getTrippleBottom(String id) {
    //return requested triple bottom pattern
    LOG.info("Selecting requested pattern ", id);
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String sql = "SELECT * from tripplebottom where id=" + id;
    List<TrippleBottom> trippleBottom = jdbcTemplate.query(sql, new RowMapper<TrippleBottom>() {

        @Override//w w w . ja  va 2 s.com
        public TrippleBottom mapRow(ResultSet rs, int rowNumber) throws SQLException {
            TrippleBottom trippleBottom1 = new TrippleBottom();
            trippleBottom1.setFirstMinPrice(rs.getDouble("firstMinPrice"));
            trippleBottom1.setSecondMinPrice(rs.getDouble("secondMinPrice"));
            trippleBottom1.setThirdMinPrice(rs.getDouble("thirdMinPrice"));
            trippleBottom1.setBreakPointPrice(rs.getDouble("breakPointPrice"));
            trippleBottom1.setFirstMaxPrice(rs.getDouble("firstMaxPrice"));
            trippleBottom1.setSecondMaxPrice(rs.getDouble("secondMaxPrice"));
            trippleBottom1.setFirstMin(rs.getString("firstMin"));
            trippleBottom1.setSecondMin(rs.getString("secondMin"));
            trippleBottom1.setThirdMin(rs.getString("thirdMin"));
            trippleBottom1.setBreakPoint(rs.getString("breakPoint"));
            trippleBottom1.setFirstMax(rs.getString("firstMax"));
            trippleBottom1.setSecondMax(rs.getString("secondMax"));
            return trippleBottom1;
        }

    });

    return trippleBottom.get(0);
}

From source file:com.arcane.dao.Impl.PatternDaoImpl.java

@Override
public HeadnShoulder getHeadnShoulder(String id) {
    //return requested head and shoulder pattern
    LOG.info("Returning requested head and shoulder pattern", id);
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String sql = "SELECT * from headnshoulder where id=" + id;
    List<HeadnShoulder> headnshoulder = jdbcTemplate.query(sql, new RowMapper<HeadnShoulder>() {

        @Override//from   w w  w  .  j  a va  2  s .  c  o  m
        public HeadnShoulder mapRow(ResultSet rs, int rowNumber) throws SQLException {
            HeadnShoulder headnshoulder1 = new HeadnShoulder();

            headnshoulder1.setBreakPointPrice(rs.getDouble("breakPointPrice"));
            headnshoulder1.setFirstMaxPrice(rs.getDouble("firstMaxPrice"));
            headnshoulder1.setSecondMaxPrice(rs.getDouble("secondMaxPrice"));
            headnshoulder1.setThirdMaxPrice(rs.getDouble("thirdMaxPrice"));
            headnshoulder1.setFirstMinPrice(rs.getDouble("firstMinPrice"));
            headnshoulder1.setSecondMinPrice(rs.getDouble("secondMinPrice"));
            headnshoulder1.setBreakPoint(rs.getString("breakPoint"));
            headnshoulder1.setFirstMax(rs.getString("firstMax"));
            headnshoulder1.setSecondMax(rs.getString("secondMax"));
            headnshoulder1.setThirdMax(rs.getString("thirdMax"));
            headnshoulder1.setFirstMin(rs.getString("firstMin"));
            headnshoulder1.setSecondMin(rs.getString("secondMin"));

            return headnshoulder1;
        }

    });

    return headnshoulder.get(0);
}

From source file:data.DefaultExchanger.java

public void exportData(String dbName, String catalogName, final JsonGenerator generator,
        JdbcTemplate jdbcTemplate) throws IOException {
    generator.writeFieldName(getTable());
    generator.writeStartArray();/*  www  .  ja va2  s .  c o  m*/
    final int[] rowCount = { 0 };
    jdbcTemplate.query(getSelectSql(), new RowCallbackHandler() {
        @Override
        public void processRow(ResultSet rs) throws SQLException {
            try {
                generator.writeStartObject();
                setNode(generator, rs);
                generator.writeEndObject();
                rowCount[0]++;
            } catch (Exception e) {
                e.printStackTrace();
                throw new RuntimeException(e);
            }
        }
    });
    generator.writeEndArray();
    play.Logger.info("exported {{}} {}", rowCount[0], getTable());

    if (hasSequence()) {
        String sequenceName = sequenceName();
        long sequenceValue = 0;
        if (dbName.equalsIgnoreCase("MySQL")) {
            String sql = String.format("SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES "
                    + "WHERE TABLE_SCHEMA = '%s' AND TABLE_NAME = '%s'", catalogName, getTable());
            sequenceValue = jdbcTemplate.queryForObject(sql, Long.class);
        } else if (dbName.equalsIgnoreCase("H2")) {
            sequenceValue = jdbcTemplate.queryForObject("CALL NEXT VALUE FOR " + sequenceName, Long.class);
        }
        generator.writeFieldName(sequenceName);
        generator.writeNumber(sequenceValue);
        play.Logger.info("exported sequence {{}}", sequenceName());
    }
}

From source file:com.arcane.dao.Impl.PatternDaoImpl.java

@Override
public HeadnShoulderBottom getHeadnShoulderBottom(String id) {
    //return requested head and shoulder inverse pattern
    LOG.info("Returning requested head and shoulder inverse pattern", id);
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String sql = "SELECT * from headnshoulderbottom where id=" + id;
    List<HeadnShoulderBottom> headnshoulderbottom = jdbcTemplate.query(sql,
            new RowMapper<HeadnShoulderBottom>() {

                @Override//ww  w . j a va  2 s.co  m
                public HeadnShoulderBottom mapRow(ResultSet rs, int rowNumber) throws SQLException {
                    HeadnShoulderBottom headnshoulderbottom1 = new HeadnShoulderBottom();

                    headnshoulderbottom1.setBreakPointPrice(rs.getDouble("breakPointPrice"));
                    headnshoulderbottom1.setFirstMinPrice(rs.getDouble("firstMinPrice"));
                    headnshoulderbottom1.setSecondMinPrice(rs.getDouble("secondMinPrice"));
                    headnshoulderbottom1.setThirdMinPrice(rs.getDouble("thirdMinPrice"));
                    headnshoulderbottom1.setFirstMaxPrice(rs.getDouble("firstMaxPrice"));
                    headnshoulderbottom1.setSecondMaxPrice(rs.getDouble("secondMaxPrice"));
                    headnshoulderbottom1.setBreakPoint(rs.getString("breakPoint"));
                    headnshoulderbottom1.setFirstMin(rs.getString("firstMin"));
                    headnshoulderbottom1.setSecondMin(rs.getString("secondMin"));
                    headnshoulderbottom1.setThirdMin(rs.getString("thirdMin"));
                    headnshoulderbottom1.setFirstMax(rs.getString("firstMax"));
                    headnshoulderbottom1.setSecondMax(rs.getString("secondMax"));

                    return headnshoulderbottom1;
                }

            });

    return headnshoulderbottom.get(0);
}

From source file:com.arcane.dao.Impl.PatternDaoImpl.java

@Override
public List<Pattern> getAllPatternList(String type) {
    //return pattern
    LOG.info("Returning requested pattern", type);
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String sql = "";
    String pattern = type;/*ww w . j  av a 2 s  .  co m*/

    sql = "SELECT * from " + pattern;
    List<Pattern> templistPattern = jdbcTemplate.query(sql, new RowMapper<Pattern>() {

        @Override
        public Pattern mapRow(ResultSet rs, int rowNumber) throws SQLException {
            Pattern pattern1 = new Pattern();
            pattern1.setId(rs.getInt("id"));
            pattern1.setStock(rs.getString("stock"));
            pattern1.setTimeStamp(rs.getString("breakPoint"));
            pattern1.setName(rs.getMetaData().getTableName(1));
            return pattern1;
        }

    });

    Collections.sort(templistPattern, comparator);
    return templistPattern;
}

From source file:com.ineunet.knife.persist.Jdbc.java

public boolean existsTable(final String tableName, JdbcTemplate jdbcTemplate) {
    return jdbcTemplate.query("show tables", new ResultSetExtractor<Boolean>() {
        @Override//from  w  w w .j  av a2s  .c  om
        public Boolean extractData(ResultSet rs) throws SQLException, DataAccessException {
            while (rs.next()) {
                String tableNameOther = rs.getString(1);
                if (tableName.equalsIgnoreCase(tableNameOther))
                    return true;
            }
            return false;
        }
    });
}

From source file:com.arcane.dao.Impl.PatternDaoImpl.java

@Override
public List<Pattern> getAllPatternList() {
    //select all patterns from specified category
    LOG.info("Selecting all patterns from specific category");
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    List<String> patternNameList = getPatternNames();
    String sql = "";
    List<Pattern> listPattern = new ArrayList<Pattern>();
    for (String pattern : patternNameList) {
        sql = "SELECT * from " + pattern;
        List<Pattern> templistPattern = jdbcTemplate.query(sql, new RowMapper<Pattern>() {

            @Override/*from   www. j av a2 s  .c  o m*/
            public Pattern mapRow(ResultSet rs, int rowNumber) throws SQLException {
                Pattern pattern1 = new Pattern();
                pattern1.setId(rs.getInt("id"));
                pattern1.setStock(rs.getString("stock"));
                pattern1.setTimeStamp(rs.getString("breakPoint"));
                pattern1.setName(rs.getMetaData().getTableName(1));
                return pattern1;
            }

        });
        listPattern.addAll(templistPattern);
    }
    Collections.sort(listPattern, comparator);
    return listPattern;
}

From source file:com.arcane.dao.Impl.PatternDaoImpl.java

@Override
public List<Event> getPatternRealData(String start, String end, String stock) {
    //return events which occurred in given time period in given stock company
    LOG.info("Returning events which occurred in given time period in given stock company ", stock);
    List<Event> events = new ArrayList<Event>();
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    String sql = "";
    Integer start1 = Integer.parseInt(start) - 40;
    Integer end1 = Integer.parseInt(end) + 0;
    sql = "SELECT * from livestream where CONVERT(date, SIGNED INTEGER) BETWEEN " + start1.toString() + " AND "
            + end1 + " ORDER BY CONVERT(date, SIGNED INTEGER)";
    List<Event> tempEventList = jdbcTemplate.query(sql, new RowMapper<Event>() {

        @Override//from  w w  w .j  a v a  2  s.co m
        public Event mapRow(ResultSet rs, int rowNumber) throws SQLException {
            Event event = new Event();
            event.setId(rs.getString("id"));
            event.setStock(rs.getString("stock"));
            event.setDate(rs.getString("date"));
            event.setPrice(rs.getDouble("price"));
            return event;
        }

    });

    return tempEventList;
}

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

public Map<String, ValidValue> getPermissibleValues(Collection<String> deIdSeqs) {
    if (deIdSeqs == null || deIdSeqs.size() < 1) {
        return new HashMap<String, ValidValue>();
    }//w w  w.  j  a v a2s. co m
    JdbcTemplate jdbcTemplate = new JdbcTemplate(getDataSource());
    String qry = "select a.*, c.DE_IDSEQ as cdeIdSeq, sbrext_common_routines.return_number(a.VALUE) display_order from PERMISSIBLE_VALUES_VIEW a, VD_PVS_VIEW b, DATA_ELEMENTS_VIEW c "
            + "where a.PV_IDSEQ=b.PV_IDSEQ and b.VD_IDSEQ=c.VD_IDSEQ and c.de_idseq in ( ";
    for (String deIdSeq : deIdSeqs) {
        qry += "'" + deIdSeq + "',";
    }
    if (qry.lastIndexOf(",") == -1) {
        qry += "'')";
    } else {
        qry = qry.substring(0, qry.length() - 1) + ")";
    }

    qry += " order by display_order, upper(a.VALUE)";

    Map<String, ValidValue> pvMap = (Map<String, ValidValue>) jdbcTemplate.query(qry, new ResultSetExtractor() {
        public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
            final Map<String, List<ValidValue>> pvMap = new HashMap<String, List<ValidValue>>();
            Map<String, List<ValidValue>> vmIdMap = new HashMap<String, List<ValidValue>>();
            while (rs.next()) {
                ValidValue vv = new ValidValueTransferObject();
                vv.setShortMeaningValue(rs.getString(2));
                vv.setShortMeaning(rs.getString(2));
                vv.setShortMeaningDescription(rs.getString(4));
                vv.setBeginDate(rs.getString(5));

                String cdeIdSeq = rs.getString("cdeIdSeq");
                List<ValidValue> vvList = null;

                if (pvMap.get(cdeIdSeq) == null) {
                    vvList = new ArrayList<ValidValue>();
                    pvMap.put(cdeIdSeq, vvList);
                } else {
                    vvList = pvMap.get(cdeIdSeq);
                }
                vvList.add(vv);
                pvMap.put(cdeIdSeq, vvList);

                String vmIdSeq = rs.getString(13);

                List<ValidValue> vmVV = null;
                if (vmIdMap.get(vmIdSeq) == null) {
                    vmVV = new ArrayList<ValidValue>();
                    vmIdMap.put(vmIdSeq, vmVV);
                } else {
                    vmVV = vmIdMap.get(vmIdSeq);
                }
                vmVV.add(vv);
            }

            Map<String, ValueMeaning> vms = getValueMeanings(vmIdMap.keySet());

            for (String vmId : vmIdMap.keySet()) {
                ValueMeaning vm = vms.get(vmId);
                if (vm != null) {
                    for (ValidValue valVal : vmIdMap.get(vmId)) {
                        valVal.setValueMeaning(vm);
                    }
                }
            }
            return pvMap;
        }
    });

    return pvMap;
}

From source file:org.geowebcache.storage.MetastoreRemover.java

private void migrateTileDates(JdbcTemplate template, final FilePathGenerator generator) {
    String query = "select layers.value as layer, gridsets.value as gridset, "
            + "tiles.parameters_id, tiles.z, tiles.x, tiles.y, created, formats.value as format \n"
            + "from tiles join layers on layers.id = tiles.layer_id \n"
            + "join gridsets on gridsets.id = tiles.gridset_id \n"
            + "join formats on formats.id = tiles.format_id \n"
            + "order by layer_id, parameters_id, gridset, z, x, y";

    final long total = template.queryForLong("select count(*) from (" + query + ")");
    log.info("Migrating " + total + " tile creation dates from the metastore to the file system");

    template.query(query, new RowCallbackHandler() {

        int count = 0;

        public void processRow(ResultSet rs) throws SQLException {
            // read the result set
            String layer = rs.getString(1);
            String gridset = rs.getString(2);
            String paramsId = rs.getString(3);
            long z = rs.getLong(4);
            long x = rs.getLong(5);
            long y = rs.getLong(6);
            long created = rs.getLong(7);
            String format = rs.getString(8);

            // create the tile and thus the tile path
            TileObject tile = TileObject.createCompleteTileObject(layer, new long[] { x, y, z }, gridset,
                    format, null, null);
            tile.setParametersId(paramsId);
            try {
                File file = generator.tilePath(tile, MimeType.createFromFormat(format));

                // update the last modified according to the date
                if (file.exists()) {
                    file.setLastModified(created);
                }//from   w ww.  j av a  2 s .c  o m
            } catch (MimeException e) {
                log.error("Failed to locate mime type for format '" + format + "', this should never happen!");
            }

            count++;
            if (count % 10000 == 0 || count >= total) {
                log.info("Migrated " + count + "/" + total
                        + " tile creation dates from the metastore to the file system");
            }
        }
    });
}