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

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

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

Introduction

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

Prototype

@Override
    public SqlRowSet queryForRowSet(String sql) throws DataAccessException

Source Link

Usage

From source file:flywayspike.Main.java

/**
 * Runs the sample.// w  w  w .ja va 2s.  co  m
 *
 * @param args None supported.
 */
public static void main(String[] args) throws Exception {
    DataSource dataSource = new SimpleDriverDataSource(new org.hsqldb.jdbcDriver(),
            "jdbc:hsqldb:file:db/flyway_sample;shutdown=true", "SA", "");
    Flyway flyway = new Flyway();
    flyway.setDataSource(dataSource);
    flyway.setLocations("flywayspike.migration", "abcd");
    flyway.clean();

    System.out.println("Started Migration");
    flyway.migrate();
    JdbcTemplate jdbcTemplate = new JdbcTemplate(dataSource);
    List<Map<String, Object>> results = jdbcTemplate.queryForList("select name from test_user");
    for (Map<String, Object> result : results) {
        System.out.println("Name: " + result.get("NAME"));
    }

    SqlRowSet rowSet = jdbcTemplate.queryForRowSet("select * from schema_version");
    while (rowSet.next()) {
        System.out.print(rowSet.getObject(1));
        System.out.println("  " + rowSet.getObject(2));
    }
}

From source file:transaction.script.ProjectTrScript.java

/**
 * @param template/*from ww w.j a  va 2 s. co  m*/
 * @param query
 * @param conditionsMapList
 * @return
 * @throws SQLException
 */
public static boolean query(JdbcTemplate template, String query, List<Map<String, Object>> conditionsMapList)
        throws SQLException {
    logger.info("Query to execute is: " + query);

    SqlRowSet set = template.queryForRowSet(query);
    boolean result = true;

    SqlRowSetMetaData mdata = set.getMetaData();
    int columnAmount = mdata.getColumnCount();
    logger.info("Columns: " + columnAmount);
    logger.info("Map size: " + conditionsMapList.size());

    //TODO
    if (set.first()) {
        set.last();
        int rowNum = set.getRow();
        result = (rowNum == conditionsMapList.size());
        set.beforeFirst();
    } else {
        if (!conditionsMapList.get(0).isEmpty()) {
            result = false;
        }
    }

    logger.info("Two maps comparison result is " + result);

    if (result) {
        while (set.next()) {
            int rowNum = set.getRow();

            Map<String, Object> map = conditionsMapList.get(rowNum - 1);

            for (int i = 1; i <= columnAmount; i++) {
                result &= map.containsKey(mdata.getColumnName(i))
                        && map.get(mdata.getColumnName(i)).toString().equals(set.getObject(i).toString());
            }
        }
    }
    return result;
}

From source file:transaction.script.ClearQuestTrScript.java

/**
 *
 * @param projectName// w ww.  ja va 2 s  .c  o m
 * @return
 * @throws SQLException
 */
public HashMap<String, String> getLastBuildSQLDefChanges(String projectName) throws SQLException {
    Checker.checkStringForEmpty("project name", projectName, false);

    String query = dcQueryTemplate.substitute("project", projectName);

    JdbcTemplate cqTemplate = JdbcTemplateFactory.getClearQuestDBTemplate();
    HashMap<String, String> sqlMap = new HashMap<String, String>();

    SqlRowSet set = cqTemplate.queryForRowSet(query);
    while (set.next()) {
        String dcHeadline = set.getString(HEADLINE);
        if (dcHeadline.contains("sql")) {
            sqlMap.put(set.getString(DCNUMBER), dcHeadline);
        }
    }

    return sqlMap;
}

From source file:transaction.script.ClearQuestTrScript.java

/**
 *
 * @param projectName/*from  w  ww . j a  v  a  2 s .c o  m*/
 * @return
 * @throws SQLException
 */
public HashMap<String, String> getLastBuildDQLDefChanges(String projectName) throws SQLException {
    Checker.checkStringForEmpty("project name", projectName, false);

    String query = dcQueryTemplate.substitute("project", projectName);

    JdbcTemplate cqTemplate = JdbcTemplateFactory.getClearQuestDBTemplate();
    HashMap<String, String> dqlmap = new HashMap<String, String>();

    SqlRowSet set = cqTemplate.queryForRowSet(query);
    while (set.next()) {
        String dcHeadline = set.getString(HEADLINE);
        if (dcHeadline.contains("dql")) {
            dqlmap.put(set.getString(DCNUMBER), dcHeadline);
        }
    }

    return dqlmap;
}

From source file:transaction.script.ClearQuestTrScript.java

/**
 * TODO: ?   DC/* ww w . java2  s  . co m*/
 * @param projectName
 * @return
 * @throws SQLException
 */
public HashMap<String, String> getAllBuildDefChanges(String projectName) throws SQLException {
    Checker.checkStringForEmpty("project name", projectName, false);

    String query = dcQueryTemplate.substitute("project", projectName);

    JdbcTemplate template = JdbcTemplateFactory.getClearQuestDBTemplate();

    HashMap<String, String> dcMap = new HashMap<String, String>();

    logger.info("query \n" + query);

    SqlRowSet set = template.queryForRowSet(query);
    logger.info("resultset rows count " + set.getRow());

    while (set.next()) {
        logger.info("resultset rows count " + set.getRow());

        dcMap.put(set.getString(DCNUMBER), set.getString(HEADLINE));
    }

    return dcMap;
}

From source file:com.google.api.ads.adwords.awalerting.sampleimpl.downloader.SqlDbReportDownloader.java

@Override
public List<ReportData> downloadReports(ImmutableAdWordsSession protoSession, Set<Long> clientCustomerIds) {
    Map<Long, ReportData> reportDataMap = new HashMap<Long, ReportData>();

    JdbcTemplate jdbcTemplate = getJdbcTemplate();
    String sqlQuery = getSqlQueryWithReportColumnNames();
    ReportDefinitionReportType reportType = getReportType();
    SqlRowSet rowSet = jdbcTemplate.queryForRowSet(sqlQuery);

    // Get the column index of customer id. 
    int customerIdColumnIndex = rowSet.findColumn(EXTERNAL_CUSTOMER_ID_REPORT_COLUMN_NAME);
    Preconditions.checkState(customerIdColumnIndex >= 0, "You must choose \"%s\" field to generate report data",
            EXTERNAL_CUSTOMER_ID_REPORT_COLUMN_NAME);

    List<String> columnNames = Arrays.asList(rowSet.getMetaData().getColumnNames());
    int columns = columnNames.size();

    // Read result into map.
    int rows = 0;
    while (rowSet.next()) {
        rows++;/*  www .j  a va2 s  .c  om*/
        List<String> row = new ArrayList<String>(columns);
        for (int i = 0; i < columns; i++) {
            row.add(rowSet.getString(i));
        }

        String customerIdStr = row.get(customerIdColumnIndex);
        Long customerId = Long.parseLong(customerIdStr);
        ReportData reportData = reportDataMap.get(customerId);
        if (reportData == null) {
            reportData = new ReportData(customerId, reportType, columnNames);
            reportDataMap.put(customerId, reportData);
        }
        reportData.addRow(row);
    }

    LOGGER.info("Retrieved and parsed {} rows from database.", rows);
    return new ArrayList<ReportData>(reportDataMap.values());
}

From source file:no.dusken.barweb.plugin.duskeninternplugin.admin.ImportPersonController.java

public ModelAndView doImport() {
    Map<String, String> map = new HashMap<String, String>();
    if (isImporting) {
        log.error("import already running");
        map.put("message", "Import already running");
        return new ModelAndView(view, map);

    } else {/*from w  w w  . ja  va 2  s  .  co  m*/
        isImporting = true;
    }

    JdbcTemplate jt = getJdbcTemplate();
    jt.setFetchSize(100);

    Gjeng g = gjengService.getByName("Under dusken");
    if (g == null) {
        g = new Gjeng();
        g.setDefaultGjeng(true);
        g.setName("Under dusken");
        gjengService.saveAndFlush(g);
    }
    // importing persons
    SqlRowSet medarbeidere = jt.queryForRowSet("SELECT * FROM medarbeidere");
    while (medarbeidere.next()) {
        Long externalID = Long.parseLong(medarbeidere.getString("medarb_ref"));
        String username = medarbeidere.getString("brukernavn");
        if (username == null || username.equals("")) {
            continue;
        }
        // use username instead of external id
        BarPerson p = barPersonService.getByUsername(username);
        if (p == null) {
            p = new BarPerson();
            p.setExternalID(externalID);
            p.setUsername(username);
            p.setFirstname(medarbeidere.getString("fornavn"));
            p.setSurname(medarbeidere.getString("etternavn"));
            p.setActive(medarbeidere.getBoolean("aktiv"));
            p.setEmailAddress(username + "@underdusken.no");
            p.setMoney(medarbeidere.getInt("saldo"));
            p.setExternalSource("db_web");
            p.setExternalID(externalID);
            p.setGjeng(g);
            p = barPersonService.save(p);
        }
        log.info("Imported BarPerson: " + p.toString());

    }
    return new ModelAndView(view, map);
}

From source file:gov.nih.nci.ncicb.tcga.dcc.QCLiveTestDataGeneratorSlowTest.java

/**
 * Tests the {@link QCLiveTestDataGenerator#generateTestData(String)} method with a valid archive name
 * and check assertions to verify that CNTL test data was loaded.
 *///from  www  .jav  a2 s.c o m
@Test
public void testLoadCNTLTestDataForNonCNTLArchive() throws IOException, SQLException, ParseException {

    // Retrieve the QCLiveTestDataGenerator from the Spring context and invoke it using a valid archive name
    ((QCLiveTestDataGenerator) testAppCtx.getBean("qcLiveTestDataGenerator"))
            .generateTestData("nationwidechildrens.org_BRCA.bio.Level_1.85.21.0");

    final JdbcTemplate diseaseLocalJdbcTemplate = (JdbcTemplate) testAppCtx.getBean("diseaseLocalJdbcTemplate");
    final SqlRowSet rowSet = diseaseLocalJdbcTemplate.queryForRowSet("select * from data_level");

    assertNotNull(rowSet);
    assertTrue(rowSet.first());
    assertEquals(1, rowSet.getInt("level_number"));
    assertEquals("cAsESeNsItIvE", rowSet.getString("level_definition"));
    assertTrue(rowSet.isLast());
}

From source file:com.emc.ecs.sync.EndToEndTest.java

protected void verifyDb(TestObjectSource testSource, boolean truncateDb) {
    SingleConnectionDataSource ds = new SingleConnectionDataSource();
    ds.setUrl(SqliteDbService.JDBC_URL_BASE + dbFile.getPath());
    ds.setSuppressClose(true);// w  w  w .  j  a  va2  s .com
    JdbcTemplate jdbcTemplate = new JdbcTemplate(ds);

    long totalCount = verifyDbObjects(jdbcTemplate, testSource.getObjects());
    try {
        SqlRowSet rowSet = jdbcTemplate.queryForRowSet("SELECT count(source_id) FROM "
                + DbService.DEFAULT_OBJECTS_TABLE_NAME + " WHERE target_id != ''");
        Assert.assertTrue(rowSet.next());
        Assert.assertEquals(totalCount, rowSet.getLong(1));
        if (truncateDb)
            jdbcTemplate.update("DELETE FROM " + DbService.DEFAULT_OBJECTS_TABLE_NAME);
    } finally {
        try {
            ds.destroy();
        } catch (Throwable t) {
            log.warn("could not close datasource", t);
        }
    }
}

From source file:ome.testing.OMEData.java

/**
 * returns a list of results from the sql statement. if there is more than
 * one column in the result set, a map from column name to Object is
 * returned, else the Object itself.//from   w  w  w. j  ava2s . c om
 * 
 * @param sql
 * @return
 */
List runSql(String sql) {
    JdbcTemplate jt = new JdbcTemplate(ds);
    SqlRowSet rows = jt.queryForRowSet(sql);
    List result = new ArrayList();
    while (rows.next()) {
        SqlRowSetMetaData meta = rows.getMetaData();
        int count = meta.getColumnCount();
        if (count > 1) {
            Map cols = new HashMap();
            String[] names = meta.getColumnNames();
            for (int i = 0; i < names.length; i++) {
                cols.put(names[i], rows.getObject(names[i]));
            }
            result.add(cols);
        } else {
            result.add(rows.getObject(1));
        }
    }
    log.debug("SQL:" + sql + "\n\nResult:" + result);
    return result;
}