Example usage for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate query

List of usage examples for org.springframework.jdbc.core.namedparam NamedParameterJdbcTemplate query

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

Introduction

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

Prototype

@Override
    public <T> List<T> query(String sql, Map<String, ?> paramMap, RowMapper<T> rowMapper)
            throws DataAccessException

Source Link

Usage

From source file:ch.digitalfondue.npjt.QueryType.java

private static List<Object> handleList(String template, NamedParameterJdbcTemplate jdbc,
        SqlParameterSource parameters, Collection<ColumnMapperFactory> columnMapperFactories, Class<Object> c,
        HasRowmapper r) {//from   w  ww  . j  ava  2  s.com
    if (r.present) {
        return jdbc.query(template, parameters, r.rowMapper);
    } else {
        RowMapper<Object> rowMapper = matchToOutput(columnMapperFactories, c);
        if (rowMapper != null) {
            return jdbc.query(template, parameters, rowMapper);
        } else {
            return jdbc.queryForList(template, parameters, c);
        }
    }
}

From source file:org.cloudfoundry.samples.handson.ex6.CopyController.java

private List<Person> fetchPersons(NamedParameterJdbcTemplate template) {
    return template.query("SELECT firstName, lastName, age FROM Persons", //
            new HashMap<String, Object>(), //
            new BeanPropertyRowMapper<Person>(Person.class));
}

From source file:com.ebay.pulsar.analytics.metricstore.db.RDMBSTest.java

@SuppressWarnings("unchecked")
@Test/*from  w w w.  ja va  2s.  c  o  m*/
public void testRDBMS() {
    String driver2 = "com.mysql.jdbc.Driver";
    String url = "jdbc:mysql://locahost:3306/test";
    String userName = "root";
    String userPwd = "";
    BasicDataSource bds = new BasicDataSource();
    bds.setDriverClassName(driver2);
    bds.setUrl(url);
    bds.setUsername(userName);
    bds.setPassword(userPwd);
    DBFactory.setDs(bds);
    List<String> result = new ArrayList<String>();
    result.add("result");
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = Mockito.mock(NamedParameterJdbcTemplate.class);
    when(namedParameterJdbcTemplate.query(Matchers.anyString(), Matchers.any(Map.class),
            Matchers.any(ResultSetExtractor.class))).thenReturn(result);
    RDBMS db = new RDBMS(driver2, url, userName, userPwd);
    db.setDriver(driver2);
    db.setNamedParameterJdbcTemplate(namedParameterJdbcTemplate);
    db.setUrl(url);
    db.setUserName(userName);
    db.setUserPwd(userPwd);
    assertEquals(namedParameterJdbcTemplate, db.getNamedParameterJdbcTemplate());
    Map<String, String> map = new HashMap<String, String>();
    assertTrue(result.equals(db.queryForList("test", map, 10)));

}

From source file:dao.LineageDAO.java

public static void getImpactDatasets(List<String> searchUrnList, int level,
        List<ImpactDataset> impactDatasets) {
    if (searchUrnList != null && searchUrnList.size() > 0) {

        if (impactDatasets == null) {
            impactDatasets = new ArrayList<ImpactDataset>();
        }/* w w  w.  j  a v  a  2  s  . com*/

        List<String> pathList = new ArrayList<String>();
        List<String> nextSearchList = new ArrayList<String>();

        for (String urn : searchUrnList) {
            LineagePathInfo pathInfo = Lineage.convertFromURN(urn);
            if (pathInfo != null && StringUtils.isNotBlank(pathInfo.filePath)) {
                if (!pathList.contains(pathInfo.filePath)) {
                    pathList.add(pathInfo.filePath);
                }
            }
        }

        if (pathList != null && pathList.size() > 0) {
            Map<String, List> param = Collections.singletonMap("pathlist", pathList);
            NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(
                    getJdbcTemplate().getDataSource());
            List<ImpactDataset> impactDatasetList = namedParameterJdbcTemplate
                    .query(GET_ONE_LEVEL_IMPACT_DATABASES, param, new ImpactDatasetRowMapper());

            if (impactDatasetList != null) {
                for (ImpactDataset dataset : impactDatasetList) {
                    dataset.level = level;
                    if (impactDatasets.stream().filter(o -> o.urn.equals(dataset.urn)).findFirst()
                            .isPresent()) {
                        continue;
                    }
                    impactDatasets.add(dataset);
                    nextSearchList.add(dataset.urn);
                }
            }
        }

        if (nextSearchList.size() > 0) {
            getImpactDatasets(nextSearchList, level + 1, impactDatasets);
        }
    }
}

From source file:com.simplymeasured.prognosticator.ThreadedQueryRunnable.java

@Override
public void run() {
    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);

    try {/* ww  w  .ja va 2  s  .  co m*/
        template.query(query, parameters, new RowCallbackHandler() {
            @Override
            public void processRow(ResultSet resultSet) throws SQLException {
                try {
                    Map<String, Object> result = Maps.newHashMap();

                    final ResultSetMetaData metadata = resultSet.getMetaData();

                    for (int i = 1; i <= metadata.getColumnCount(); i++) {
                        String columnTypeName = metadata.getColumnTypeName(i);

                        final Object value;

                        if ("array".equalsIgnoreCase(columnTypeName)) {
                            String stringValue = resultSet.getString(i);

                            if (stringValue != null) {
                                value = objectMapper.readValue(stringValue, List.class);
                            } else {
                                value = null;
                            }
                        } else if ("map".equalsIgnoreCase(columnTypeName)
                                || "struct".equalsIgnoreCase(columnTypeName)) {
                            String stringValue = resultSet.getString(i);

                            if (stringValue != null) {
                                value = objectMapper.readValue(stringValue, Map.class);
                            } else {
                                value = null;
                            }
                        } else {
                            value = resultSet.getObject(i);
                        }

                        result.put(metadata.getColumnName(i), value);
                    }

                    resultQueue.put(result);
                } catch (SQLException se) {
                    LOG.warn("Database error!", se);
                    throw new RuntimeException("Database error!", se);
                } catch (InterruptedException ie) {
                    LOG.warn("Query killed!", ie);
                    throw new RuntimeException("Query killed!", ie);
                } catch (Exception ex) {
                    LOG.warn("Unable to parse row!", ex);
                    throw new RuntimeException("Unable to parse row!", ex);
                }
            }
        });

        resultQueue.put(Collections.<String, Object>emptyMap());
    } catch (DataAccessException dae) {
        try {
            resultQueue.put(Collections.<String, Object>emptyMap());
        } catch (InterruptedException ie) {
            LOG.warn("Queue is dead!", ie);
        }

        LOG.warn("Unable to execute query - attempting to clean up", dae);
    } catch (InterruptedException ie) {
        LOG.warn("Queue is dead!", ie);
    }
}

From source file:cherry.foundation.etl.ExtractorImpl.java

/**
 * ?// w w w  .j a v a2 s  .  c  om
 * 
 * @param dataSource 
 * @param sql SQL
 * @param paramMap ?
 * @param consumer ??
 * @param limiter ?
 * @return ????
 * @throws LimiterException ??
 * @throws IOException ?
 */
@Override
public long extract(DataSource dataSource, String sql, Map<String, ?> paramMap, Consumer consumer,
        Limiter limiter) throws LimiterException, IOException {

    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(dataSource);
    ResultSetExtractor<Long> extractor = new ExtractorResultSetExtractor(consumer, limiter);

    limiter.start();
    try {
        return template.query(sql, paramMap, extractor);
    } catch (IllegalStateException ex) {
        throw (IOException) ex.getCause();
    } finally {
        limiter.stop();
    }
}

From source file:com.springsource.greenhouse.account.JdbcAccountRepository.java

public List<ProfileReference> findProfileReferencesByIds(List<Long> accountIds) {
    NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    Map<String, Object> params = new HashMap<String, Object>(2, 1);
    params.put("accountIds", accountIds);
    return namedTemplate.query(AccountMapper.SELECT_ACCOUNT_REFERENCE + " where id in ( :accountIds )", params,
            accountMapper.getReferenceMapper());
}

From source file:com.phideltcmu.recruiter.server.dao.RecruitListDao.java

@Override
public List<Person> selectAll(List<Category> desiredCategories) {
    checkSingleton();/* w  ww .j  a va2 s . c  om*/

    List<String> list = new ArrayList<String>();
    for (Category c : desiredCategories) {
        list.add(c.getValue());
    }

    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    MapSqlParameterSource parameters = new MapSqlParameterSource();
    parameters.addValue("categories", list);
    return namedParameterJdbcTemplate.query("SELECT * FROM recruitList.infolist WHERE status IN (:categories)",
            parameters, new PersonRowMapper());
}

From source file:org.tradex.jdbc.JDBCHelper.java

/**
 * Issues a named parameter query using numerical binds, starting at 0.
 * @param sql The SQL//  ww w.j  a  v a  2s  . co m
 * @param binds The bind values
 * @return an Object array of the results
 */
public Object[][] templateQuery(CharSequence sql, Object... binds) {
    NamedParameterJdbcTemplate template = new NamedParameterJdbcTemplate(ds);
    final List<Object[]> results = template.query(sql.toString(),
            getBinds(sql.toString().trim().toUpperCase(), binds), new RowMapper<Object[]>() {
                int columnCount = -1;

                @Override
                public Object[] mapRow(ResultSet rs, int rowNum) throws SQLException {
                    if (columnCount == -1)
                        columnCount = rs.getMetaData().getColumnCount();
                    Object[] row = new Object[columnCount];
                    for (int i = 0; i < columnCount; i++) {
                        row[i] = rs.getObject(i + 1);
                    }
                    return row;
                }
            });
    Object[][] ret = new Object[results.size()][];
    int cnt = 0;
    for (Object[] arr : results) {
        ret[cnt] = arr;
        cnt++;
    }
    return ret;
}

From source file:com.joliciel.jochre.security.SecurityDaoJdbc.java

@Override
public List<User> findUsers() {
    NamedParameterJdbcTemplate jt = new NamedParameterJdbcTemplate(this.getDataSource());
    String sql = "SELECT " + SELECT_USER + " FROM ocr_user" + " ORDER BY user_last_name, user_first_name";
    MapSqlParameterSource paramSource = new MapSqlParameterSource();

    LOG.info(sql);//from www .  j ava2  s .c  o  m
    logParameters(paramSource);
    @SuppressWarnings("unchecked")
    List<User> users = jt.query(sql, paramSource, new UserMapper(this.getSecurityServiceInternal()));
    return users;
}