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:org.apache.syncope.core.persistence.jpa.content.ContentLoaderHandler.java

private Object[] getParameters(final String tableName, final Attributes attrs) {
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);

    Map<String, Integer> colTypes = jdbcTemplate.query("SELECT * FROM " + tableName + " WHERE 0=1",
            new ResultSetExtractor<Map<String, Integer>>() {

                @Override/*from  www.j a va 2 s  .  c om*/
                public Map<String, Integer> extractData(final ResultSet rs) throws SQLException {
                    Map<String, Integer> colTypes = new HashMap<>();
                    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        colTypes.put(rs.getMetaData().getColumnName(i).toUpperCase(),
                                rs.getMetaData().getColumnType(i));
                    }
                    return colTypes;
                }
            });

    Object[] parameters = new Object[attrs.getLength()];
    for (int i = 0; i < attrs.getLength(); i++) {
        Integer colType = colTypes.get(attrs.getQName(i).toUpperCase());
        if (colType == null) {
            LOG.warn("No column type found for {}", attrs.getQName(i).toUpperCase());
            colType = Types.VARCHAR;
        }

        switch (colType) {
        case Types.INTEGER:
        case Types.TINYINT:
        case Types.SMALLINT:
            try {
                parameters[i] = Integer.valueOf(attrs.getValue(i));
            } catch (NumberFormatException e) {
                LOG.error("Unparsable Integer '{}'", attrs.getValue(i));
                parameters[i] = attrs.getValue(i);
            }
            break;

        case Types.NUMERIC:
        case Types.DECIMAL:
        case Types.BIGINT:
            try {
                parameters[i] = Long.valueOf(attrs.getValue(i));
            } catch (NumberFormatException e) {
                LOG.error("Unparsable Long '{}'", attrs.getValue(i));
                parameters[i] = attrs.getValue(i);
            }
            break;

        case Types.DOUBLE:
            try {
                parameters[i] = Double.valueOf(attrs.getValue(i));
            } catch (NumberFormatException e) {
                LOG.error("Unparsable Double '{}'", attrs.getValue(i));
                parameters[i] = attrs.getValue(i);
            }
            break;

        case Types.REAL:
        case Types.FLOAT:
            try {
                parameters[i] = Float.valueOf(attrs.getValue(i));
            } catch (NumberFormatException e) {
                LOG.error("Unparsable Float '{}'", attrs.getValue(i));
                parameters[i] = attrs.getValue(i);
            }
            break;

        case Types.DATE:
        case Types.TIME:
        case Types.TIMESTAMP:
            try {
                parameters[i] = FormatUtils.parseDate(attrs.getValue(i));
            } catch (ParseException e) {
                LOG.error("Unparsable Date '{}'", attrs.getValue(i));
                parameters[i] = attrs.getValue(i);
            }
            break;

        case Types.BIT:
        case Types.BOOLEAN:
            parameters[i] = "1".equals(attrs.getValue(i)) ? Boolean.TRUE : Boolean.FALSE;
            break;

        case Types.BINARY:
        case Types.VARBINARY:
        case Types.LONGVARBINARY:
            try {
                parameters[i] = Hex.decodeHex(attrs.getValue(i).toCharArray());
            } catch (DecoderException | IllegalArgumentException e) {
                parameters[i] = attrs.getValue(i);
            }
            break;

        case Types.BLOB:
            try {
                parameters[i] = Hex.decodeHex(attrs.getValue(i).toCharArray());
            } catch (DecoderException | IllegalArgumentException e) {
                LOG.warn("Error decoding hex string to specify a blob parameter", e);
                parameters[i] = attrs.getValue(i);
            } catch (Exception e) {
                LOG.warn("Error creating a new blob parameter", e);
            }
            break;

        default:
            parameters[i] = attrs.getValue(i);
        }
    }

    return parameters;
}

From source file:org.apereo.portal.portlets.sqlquery.SqlQueryPortletController.java

@Override
public ModelAndView handleRenderRequest(RenderRequest request, RenderResponse response) throws Exception {

    // find the configured SQL statement
    PortletPreferences preferences = request.getPreferences();
    String sqlQuery = preferences.getValue(SQL_QUERY_PARAM_NAME, null);
    String dsName = preferences.getValue(DATASOURCE_BEAN_NAME_PARAM_NAME,
            BasePortalJpaDao.PERSISTENCE_UNIT_NAME);
    String viewName = preferences.getValue(VIEW_PARAM_NAME, "jsp/SqlQuery/results");

    // Allow substituting attributes from the request and userInfo objects using the SPEL ${} notation..
    String spelSqlQuery = evaluateSpelExpression(sqlQuery, request);

    List<Map<String, Object>> results = null;
    String cacheKey = createCacheKey(spelSqlQuery, dsName);
    Cache cache = getCache(request);// w ww.  j a va  2 s  . c  om
    if (cache != null) {
        Element cachedElement = cache.get(cacheKey);
        if (cachedElement != null) {
            log.debug(
                    "Cache hit. Returning item for query: {}, substituted query: {}, from cache {} for key {}",
                    sqlQuery, spelSqlQuery, cache.getName(), cacheKey);
            results = (List<Map<String, Object>>) cachedElement.getObjectValue();
        }
    }

    if (results == null) {
        // generate a JDBC template for the requested data source
        DataSource ds = (DataSource) getApplicationContext().getBean(dsName);
        JdbcTemplate template = new JdbcTemplate(ds);

        // Execute the SQL query and build a results object.  This result will consist of one
        // rowname -> rowvalue map for each row in the result set
        results = template.query(spelSqlQuery, new ColumnMapRowMapper());
        log.debug("found {} results for query {}", results.size(), spelSqlQuery);

        if (cache != null) {
            log.debug("Adding SQL results to cache {}, query: {}, substituted query: {}", cache.getName(),
                    sqlQuery, spelSqlQuery);
            Element cachedElement = new Element(cacheKey, results);
            cache.put(cachedElement);

        }
    }

    // build the model

    ModelAndView modelandview = new ModelAndView(viewName);
    modelandview.addObject("results", results);
    return modelandview;
}

From source file:org.cloudfoundry.identity.uaa.db.mysql.V1_5_4__NormalizeTableAndColumnNames.java

@Override
public void migrate(JdbcTemplate jdbcTemplate) throws Exception {
    logger.info("[V1_5_4] Running SQL: " + colQuery);
    List<DatabaseInformation1_5_3.ColumnInfo> columns = jdbcTemplate.query(colQuery,
            new DatabaseInformation1_5_3.ColumnMapper());
    for (DatabaseInformation1_5_3.ColumnInfo column : columns) {
        if (processColumn(column)) {
            String sql = column.sql.replaceAll("2001-01-01 .*", "'2001-01-01 01:01:01.000001'");
            logger.info("Renaming column: [" + sql + "]");
            jdbcTemplate.execute(sql);//from www .j a  v a 2 s  .  c o m
        }
    }
}

From source file:org.cloudfoundry.identity.uaa.db.StoreSubDomainAsLowerCase_V2_7_3.java

private List<IdentityZone> retrieveIdentityZones(JdbcTemplate jdbcTemplate) {
    return jdbcTemplate.query(IDENTITY_ZONES_QUERY, mapper);
}

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

private void migrateParameters(JdbcTemplate template, final File root) {
    // find all possible combinations of layer, zoom level, gridset and parameter id
    String query = "select layers.value as layer, gridsets.value as gridset, tiles.z, parameters.value as parameters, parameters_id\n"
            + "from tiles join layers on layers.id = tiles.layer_id \n"
            + "     join gridsets on gridsets.id = tiles.gridset_id\n"
            + "     join parameters on parameters.id = tiles.parameters_id\n"
            + "group by layer, gridset, z, parameters, parameters_id";

    final long total = template.queryForLong("select count(*) from (" + query + ")");
    log.info("Migrating " + total + " parameters from the metastore to the file system");
    template.query(query, new RowCallbackHandler() {

        long count = 0;

        public void processRow(ResultSet rs) throws SQLException {
            String layer = rs.getString(1);
            String gridset = rs.getString(2);
            int z = rs.getInt(3);
            String paramsKvp = rs.getString(4);
            String paramsId = rs.getString(5);

            String sha = getParamsSha1(paramsKvp);

            // move the folders containing params
            File origin = new File(buildFolderPath(root, layer, gridset, z, paramsId));
            File destination = new File(buildFolderPath(root, layer, gridset, z, sha));
            org.geowebcache.util.FileUtils.renameFile(origin, destination);

            count++;/* w w  w.  jav a2s.com*/
            if (count % 1000 == 0 || count >= total) {
                log.info("Migrated " + count + "/" + total
                        + " parameters from the metastore to the file system");
            }
        }

        private String buildFolderPath(final File root, String layer, String gridset, int z, String paramsId) {
            // build the old path
            StringBuilder path = new StringBuilder();
            path.append(root.getPath());
            path.append(File.separatorChar);
            appendFiltered(layer, path);
            path.append(File.separatorChar);
            FilePathUtils.appendGridsetZoomLevelDir(gridset, z, path);
            path.append('_');
            path.append(paramsId);
            path.append(File.separatorChar);

            return path.toString();
        }

        private String getParamsSha1(String paramsKvp) {
            Map<String, String> params = toMap(paramsKvp);
            return FilePathGenerator.getParametersId(params);
        }

        /**
         * Parses the param list stored in the db to a parameter list (since this is coming from
         * the database the assumption is that the contents are sane)
         * 
         * @param paramsKvp
         * @return
         */
        private Map<String, String> toMap(String paramsKvp) {
            // TODO: wondering, shall we URL decode the values??
            Map<String, String> result = new HashMap<String, String>();
            String[] kvps = paramsKvp.split("&");
            for (String kvp : kvps) {
                if (kvp != null && !"".equals(kvp)) {
                    String[] kv = kvp.split("=");
                    result.put(kv[0], kv[1]);
                }
            }

            return result;
        }
    });
}

From source file:org.openflamingo.engine.hive.HiveServiceImpl.java

@Override
public List<String> getDatabases(HiveServer hiveServer) {
    try {/*  ww  w.  j  a  va 2s .com*/
        SimpleDriverDataSource dataSource = new SimpleDriverDataSource();
        dataSource.setDriverClass(org.apache.hive.jdbc.HiveDriver.class);
        dataSource.setUrl(hiveServer.getJdbcUrl());

        final List<String> list = new ArrayList<String>();
        JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
        jdbcTemplate.query("show databases", new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                list.add(resultSet.getString("database_name"));
            }
        });
        return list;
    } catch (Exception ex) {
        throw new ServiceException(message("S_HIVE", "CANNOT_GET_HIVE_DBS"), ex);
    }
}

From source file:org.projectforge.database.DatabaseUpdateDao.java

/**
 * Without access checking./*from   ww  w.j a va2 s. c  om*/
 * @see #fixDBHistoryEntries()
 */
@SuppressWarnings({ "unchecked", "rawtypes" })
public int internalFixDBHistoryEntries() {
    log.info("Fix all broken history entries (if exist).");
    final int counter[] = new int[1];
    counter[0] = 0;
    final JdbcTemplate jdbc = new JdbcTemplate(dataSource);
    try {
        String sql = " from t_history_property_delta where old_value like 'org.projectforge.%' or new_value like 'org.projectforge.%'";
        jdbc.query("select id, old_value, new_value, property_type" + sql, new ResultSetExtractor() {
            @Override
            public Object extractData(final ResultSet rs) throws SQLException, DataAccessException {
                while (rs.next() == true) {
                    final int id = rs.getInt("ID");
                    final String oldValue = rs.getString("OLD_VALUE");
                    final String newValue = rs.getString("NEW_VALUE");
                    final Serializable oldId = getObjectId(oldValue);
                    final Serializable newId = getObjectId(newValue);
                    final String propertyType = rs.getString("PROPERTY_TYPE");
                    final int pos = propertyType.indexOf("_$$_javassist_");
                    final String newPropertyType;
                    if (pos > 0) {
                        newPropertyType = propertyType.substring(0, pos);
                    } else {
                        newPropertyType = null;
                    }
                    if (oldId == null && newId == null) {
                        continue;
                    }
                    final StringBuffer buf = new StringBuffer();
                    boolean first = true;
                    buf.append("update t_history_property_delta set ");
                    if (oldId != null) {
                        buf.append("OLD_VALUE='").append(oldId).append("'");
                        first = false;
                    }
                    if (newId != null) {
                        if (first == false) {
                            buf.append(", ");
                        } else {
                            first = false;
                        }
                        buf.append("NEW_VALUE='").append(newId).append("'");
                    }
                    if (newPropertyType != null) {
                        if (first == false) {
                            buf.append(", ");
                        } else {
                            first = false;
                        }
                        buf.append("PROPERTY_TYPE='").append(newPropertyType).append("'");
                    }
                    buf.append(" where ID=").append(id);
                    final String sql = buf.toString();
                    log.info(sql);
                    jdbc.execute(sql);
                    counter[0]++;
                }
                return null;
            }
        });
        int no = jdbc.queryForInt("select count(*)" + sql);
        if (no > 0) {
            log.warn("" + no + " of data base history entries aren't fixed.");
        }
        sql = " from t_history_property_delta where property_type like '%_$$_javassist_%'";
        jdbc.query("select id, property_type" + sql, new ResultSetExtractor() {
            @Override
            public Object extractData(final ResultSet rs) throws SQLException, DataAccessException {
                while (rs.next() == true) {
                    final int id = rs.getInt("ID");
                    final String propertyType = rs.getString("PROPERTY_TYPE");
                    final int pos = propertyType.indexOf("_$$_javassist_");
                    if (pos < 0) {
                        log.error("Oups, should not occur.");
                        continue;
                    }
                    final String newPropertyType = propertyType.substring(0, pos);
                    final String sql = "update t_history_property_delta set PROPERTY_TYPE='" + newPropertyType
                            + "' where id=" + id;
                    log.info(sql);
                    jdbc.execute(sql);
                    counter[0]++;
                }
                return null;
            }
        });
        no = jdbc.queryForInt("select count(*)" + sql);
        if (no > 0) {
            log.warn("" + no + " of data base history entries aren't fixed.");
        }
    } catch (final Exception ex) {
        log.error(ex.getMessage(), ex);
        return 0;
    }
    return counter[0];
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testStringsWithStaticSql() throws Exception {
    doTestStrings(new JdbcTemplateCallback() {
        public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
            template.query(sql, rch);
        }//from   w  w  w .  ja v a 2s . c o m
    }, false, null, null, null, null);
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testStringsWithStaticSqlAndFetchSizeAndMaxRows() throws Exception {
    doTestStrings(new JdbcTemplateCallback() {
        public void doInJdbcTemplate(JdbcTemplate template, String sql, RowCallbackHandler rch) {
            template.query(sql, rch);
        }/*from  www  .  ja  v  a2 s. c om*/
    }, false, new Integer(10), new Integer(20), new Integer(30), null);
}

From source file:org.springframework.jdbc.core.JdbcTemplateTests.java

public void testLeaveConnectionOpenOnRequest() throws Exception {
    String sql = "SELECT ID, FORENAME FROM CUSTMR WHERE ID < 3";

    MockControl ctrlResultSet = MockControl.createControl(ResultSet.class);
    ResultSet mockResultSet = (ResultSet) ctrlResultSet.getMock();
    ctrlResultSet = MockControl.createControl(ResultSet.class);
    mockResultSet = (ResultSet) ctrlResultSet.getMock();
    mockResultSet.next();//from w ww .java2s.  c  o m
    ctrlResultSet.setReturnValue(false);
    mockResultSet.close();
    ctrlResultSet.setVoidCallable();

    MockControl ctrlStatement = MockControl.createControl(PreparedStatement.class);
    PreparedStatement mockStatement = (PreparedStatement) ctrlStatement.getMock();
    ctrlStatement = MockControl.createControl(PreparedStatement.class);
    mockStatement = (PreparedStatement) ctrlStatement.getMock();
    mockStatement.executeQuery(sql);
    ctrlStatement.setReturnValue(mockResultSet);
    if (debugEnabled) {
        mockStatement.getWarnings();
        ctrlStatement.setReturnValue(null);
    }
    mockStatement.close();
    ctrlStatement.setVoidCallable();

    mockConnection.isClosed();
    ctrlConnection.setReturnValue(false, 2);
    mockConnection.createStatement();
    ctrlConnection.setReturnValue(mockStatement);
    // if close is called entire test will fail
    mockConnection.close();
    ctrlConnection.setDefaultThrowable(new RuntimeException());

    ctrlResultSet.replay();
    ctrlStatement.replay();
    replay();

    SingleConnectionDataSource scf = new SingleConnectionDataSource(mockDataSource.getConnection(), false);
    JdbcTemplate template2 = new JdbcTemplate(scf, false);
    RowCountCallbackHandler rcch = new RowCountCallbackHandler();
    template2.query(sql, rcch);

    ctrlResultSet.verify();
    ctrlStatement.verify();
}